1.概要
本文檔詳細介紹了Redshift和MaxCompute之間SQL文法的異同。這篇文檔有助于加快sql任務遷移到MaxCompute。由于Redshift和MaxCompute之間文法存在很多差異,是以我們需要修改Redshift上編寫的腳本,然後才能在MaxCompute中使用,因為服務之間的SQL方言不同。
2.遷移前RedShift于MaxCompute的各項對比差異
2.1.1資料類型對比及類型轉化
類别 | MaxCompute | 建議轉化成MaxCompute類型 | Redshift | |
數值類型 | smallint | Y | ||
integer | N | int | ||
bigint | ||||
decimal | ||||
numeric | ||||
real | float | |||
double | ||||
TINYINT | ||||
字元類型 | varchar(n) | |||
char(n) | ||||
STRING | ||||
text | string | |||
日期 | TIMESTAMP | |||
TIMESTAMPTZ | ||||
DATE | ||||
TIME | ||||
DateTime | ||||
boolean 資料類型 | boolean | |||
複雜資料類型 | ARRAY | |||
MAP | ||||
STRUCT | ||||
HLLSketch |
MaxCompoute資料類型參考
https://help.aliyun.com/document_detail/159541.html2.1.2文法對比
MaxCompute沒有schenma、group、庫、存儲過程的概念。隻有project、表、分區,MaxCompute建表時沒有自增序列 外鍵等,不支援指定編碼預設utf-8,内部表不支援指定存儲格式預設Aliorc
主要差別 | |
表結構 | 不能修改分區列列名,隻能修改分區列對應的值。 |
支援增加列,但是不支援删除列以及修改列的資料類 型。 | |
SQL 常見問題 | INSERT 文法上最直覺的差別是:Insert into/overwrite 後面 有個關鍵字 Table。 |
資料插入表的字段映射不是根據 Select 的别名做的,而 是根據 Select 的字段的順序和表裡的字段的順序 | |
UPDATE/DELETE | 隻有事務表支援UPDATE/DELETE |
join | Join 必須要用 on 設定關聯條件,不支援笛卡爾積 |
觸發器 | 不支援觸發器、 |
建立外部函數 | maxCompute沒有外部函數 |
精度 | DOUBLE 類型存在精度問題。 不建議在關聯時候進行直接等号關聯兩 個 DOUBLE字段,建議把兩個數做減 法,如果差距小于一個預設的值就認為 是相同,例如 abs(a1- a2) < 0.000000001。 目前産品上已經支援高精度的類型 DECIMAL。 |
MaxCompute主要的日期類型是datetime(格式yyyy-mm-dd hh:mi:ss) timestamp date,datetime支援的内建函數更加豐富,建議日期轉成datetime做運算, 日期函數連結 | |
存儲過程 | 使用MaxCompute的pyodps修改 |
物化視圖 | 要更新物化化視圖中的資料,MaxCompute隻能手動更新,不支援自動更新 |
redshift 支援在select語句中引用别名如 select money/100 as a ,round(money/100,3) from table | MaxCompute修改 select money/100 as a ,round(a,3) from table |
2.1.3複合表達式
REDAHIFT | ||
+、- | ||
^、|/、||/ | ||
*、/、% | ||
@ | ||
&、|、 | ||
|| | ||
#、~、<<、>> | 使用shift函數替換 |
2.1.4條件比較
<> 或 != | ||
like | ||
BETWEEN expression AND | ||
IS [ NOT ] NULL | ||
EXISTS | ||
POSIX 運算符 | ||
SIMILAR TO | ||
IN | ||
正則 ~ | Rlike | |
~~ |
2.1.5DDL文法
主要差異:
1.MaxCompute不支援主鍵自增和PRIMARY KEY
2.指定預設值default]不支援使用函數
3.decimal指定預設值不支援-1
文法 | REDSHIFT | |
CREATE TABLE—PRIMARY KEY | ||
CREATE TABLE—NOT NULL | ||
CREATE TABLE—CLUSTER BY | ||
CREATE TABLE—EXTERNAL TABLE | Y(OSS, OTS, TDDL) | |
CREATE TABLE—TEMPORARY TABLE | ||
table_attributes | N(Mc内部表不需要添加屬性) | |
CREATE TABLE—AS | ||
create materialized view |
2.1.6DML文法差異
CTE | ||
SELECT—into | ||
SELECT—recursive CTE | ||
SELECT—GROUP BY ROLL UP | ||
SELECT—GROUPING SET | ||
SELECT—IMPLICT JOIN | ||
SEMI JOIN | ||
SELEC TRANSFROM | ||
SELECT—corelated subquery | ||
LATERAL VIEW | ||
SET OPERATOR—UNION (disintct) | ||
SET OPERATOR—INTERSECT | ||
SET OPERATOR—MINUS/EXCEPT | ||
INSERT INTO ... VALUES | ||
INSERT INTO (ColumnList) | ||
UPDATE … WHERE | Y(事務表支援) | |
DELETE … WHERE | ||
ANALYTIC—reusable WINDOWING CLUSUE | ||
ANALYTIC—CURRENT ROW | ||
ANALYTIC—UNBOUNDED | ||
ANALYTIC—RANGE … | ||
WHILE DO | ||
VIEW WITH PARAMETERS | ||
select * into |
2.1.7内建函數對比
其他未列出的redshift函數不支援。
函數類型 | POSTGRESQL | 在MaxCompute SQL中是否支援分區剪裁 | |
日期函數 | 無 | ADD_MES | |
CONVERT_TIMEZONE | |||
DATE_CMP_TIMESTAMP | |||
DATE_CMP_TIMESTAMPTZ | |||
DATE_PART_YEAR | |||
DATE_CMP | |||
INTERVAL_CMP | |||
+ | |||
SYSDATE | |||
TIMEOFDAY | |||
TIMESTAMP_CMP | |||
TIMESTAMP_CMP_DATE | |||
TIMESTAMP_CMP_TIMESTAMPTZ | |||
TIMESTAMPTZ_CMP | |||
TIMESTAMPTZ_CMP_DATE | |||
TIMESTAMPTZ_CMP_TIMESTAMP | |||
to_timestamp | |||
TIMEZONE | |||
DATEDIFF |
| ||
DATE_ADD | DATEADD |
| |
DATEPART | date_part | | |
DATETRUNC | date_trunc | | |
FROM_UNIXTIME | | ||
GETDATE | CURRENT_DATE | | |
ISDATE | | ||
LASTDAY | | ||
TO_DATE | | ||
TO_CHAR | to_char | | |
UNIX_TIMESTAMP | extract | | |
WEEKDAY | | ||
WEEKOFYEAR | | ||
YEAR |
| ||
QUARTER | EXTRACT | | |
MONTH | | ||
DAY | | ||
DAYOFMONTH | | ||
HOUR | | ||
MINUTE | | ||
CURRENT_TIMESTAMP | | ||
ADD_MONTHS | 運算符+ | | |
LAST_DAY | | ||
NEXT_DAY | | ||
MONTHS_BETWEEN | | ||
數學函數 | exp | ||
ATAN2 | |||
DEXP | |||
DLOG1 | |||
DLOG10 | |||
ABS | | ||
ACOS | | ||
ASIN | | ||
ATAN | | ||
CEIL | | ||
CONV | convert | | |
COS | | ||
COSH | A | | |
COT | | ||
EXP | | ||
FLOOR | | ||
LN | | ||
LOG | | ||
POW | power | | |
RAND | random | | |
ROUND | | ||
SIN | | ||
SINH | asin | | |
SQRT | | ||
TAN | | ||
TANH | atan | | |
TRUNC | | ||
LOG2 | | ||
LOG10 | | ||
BIN | | ||
HEX | | ||
UNHEX | | ||
RADIANS | | ||
DEGREES | | ||
SIGN | | ||
E | | ||
PI | | ||
FACTORIAL | | ||
CBRT | | ||
SHIFTLEFT | << | | |
SHIFTRIGHT | >> | | |
SHIFTRIGHTUNSIGNED | >>> | | |
視窗函數 | CUME_DIST | ||
FIRST_VALUE/LAST_VALUE | |||
LISTAGG | |||
NTH_VALUE | |||
PERCENTILE_CONT | |||
PERCENTILE_DISC | |||
RATIO_TO_REPORT ( ratio_expression ) OVER ( [ PARTITION BY partition_expression ] ) | |||
STDDEV_SAMP | |||
VAR_SAMP | VARIANCE | VAR_POP | |||
PERCENT_RANK | |||
DENSE_RANK | | ||
RANK | | ||
LAG | | ||
LEAD | | ||
| |||
ROW_NUMBER | | ||
CLUSTER_SAMPLE | | ||
NTILE | | ||
聚合函數 | PERCENTILE_APPROX | APPROXIMATE PERCENTILE_DISC | |
ANY_VALUE | |||
COUNT | | ||
AVG | | ||
MAX | | ||
MIN | | ||
MEDIAN | PERCENTILE_disc | | |
STDDEV | | ||
| |||
SUM | | ||
WM_CONCAT | string_agg | | |
COLLECT_LIST | | ||
COLLECT_SET | | ||
VARIANCE/VAR_POP | | ||
VAR_SAMP | | ||
COVAR_POP | | ||
COVAR_SAMP | | ||
PERCENTILE | | ||
字元串函數 | | ||
BPCHARCMP | |||
BTRIM | |||
CHAR_LENGTH | |||
CHARACTER_LENGTH | |||
CHARINDEX | |||
COLLATE | |||
CRC32 | |||
DIFFERENCE | |||
INITCAP | |||
OCTETINDEX | |||
OCTET_LENGTH | |||
QUOTE_IDENT | |||
QUOTE_LITERAL | |||
POSITION | |||
REPEAT | |||
LEFT /RIGHT | |||
STRPOS | |||
STRTOL | |||
CHAR_MATCHCOUNT | | ||
CHR | | ||
CONCAT | CONCAT|array_concat | | |
GET_JSON_OBJECT | | ||
INSTR | | ||
IS_ENCODING | | ||
KEYVALUE | | ||
LENGTH | | ||
LENGTHB | LEN | | |
MD5 | | ||
REGEXP_EXTRACT | | ||
REGEXP_INSTR | | ||
REGEXP_REPLACE | | ||
REGEXP_SUBSTR | | ||
REGEXP_COUNT | | ||
SPLIT_PART | | ||
SUBSTR | | ||
SUBSTRING | | ||
TOLOWER | LOWER | | |
TOUPPER | UPPER | | |
TRIM | | ||
LTRIM | | ||
RTRIM | | ||
REVERSE | | ||
| |||
ASCII | | ||
CONCAT_WS | | ||
LPAD | | ||
RPAD | | ||
REPLACE | | ||
SOUNDEX | | ||
SUBSTRING_INDEX | | ||
TRANSLATE | | ||
URL_DECODE | | ||
URL_ENCODE | | ||
| |||
其他函數 | CAST | | |
COALESCE | | ||
DECODE | | ||
GET_IDCARD_AGE | | ||
GET_IDCARD_BIRTHDAY | | ||
GET_IDCARD_SEX | | ||
GREATEST | | ||
ORDINAL | | ||
LEAST | | ||
MAX_PT | | ||
UUID | uuid_generate_v1 | | |
SAMPLE | | ||
IF | | ||
CASE WHEN | | ||
SPLIT | | ||
STR_TO_MAP | | ||
EXPLODE | split_to_array | | |
| |||
MAP_KEYS | | ||
MAP_VALUES | | ||
NVL | | ||
| |||
SIZE | get_array_length | | |
ARRAY_CONTAINS | @> | | |
POSEXPLODE | | ||
TRANS_ARRAY | | ||
INLINE | | ||
NAMED_STRUCT | | ||
SUBARRAY |
2.1.8 MaxCompute 産品特性
功能 | MaxCompute 産品元件 | 特性介紹 |
資料存儲 | MaxCompute 表 (基于盤古 分布式存儲) | MaxCompute 支援大規模計算存儲,适用于 TB 以上規模的存 儲及計算需求,最大可達 EB 級别。同一個 MaxCompute 項 目支援企業從 創業團隊發展到獨角獸的資料規模需求; 資料 分布式存儲,多副本備援,資料存儲對外僅開放 表的 操作接口,不提供檔案系統通路接口 級别。同一個 MaxCompute 項目支援企業從 創業團隊發展到獨角獸的資料規模需求; 資料分布式存儲,多副本備援,資料存儲對外僅 開放表的操作接口,不提供檔案系統通路接口; 自研資料存儲結構,表資料列式存儲,預設高度 壓縮,後續将提供相容 ORC的Ali-ORC存儲格 式; 支援外表,将存儲在OSS 對象存儲、OTS表格 存儲的資料映射為二維表; 支援Partition、Bucket 的分區、分桶存儲; 更底層不是 HDFS,是阿裡自研的盤古檔案系 統,但可借助 HDFS 了解對應的表之下檔案的 體系結構、任務并發機制使用時,存儲與計算解 耦,不需要僅僅為了存儲擴大不必要的計算資 源; |
存儲 | Pangu | 阿裡自研分布式存儲服務,類似 HDFS。 MaxCompute 對外目前隻暴露表接口,不能直 接通路檔案系統。 |
資源排程 | Fuxi | 阿裡自研的資源排程系統,類似 Yarn |
資料上傳下載下傳 | Tunnel Streaming Tunnel | 不暴露檔案系統,通過 Tunnel 進行批量資料上傳下載下傳 |
開發&診斷 | Dataworks/Studio/Logview | 配套的資料同步、作業開發、工作流編排排程、 作業運維及診斷工具。開源社群常見的 Sqoop、Kettle、Ozzie 等實作資料同步和排程 |
使用者接口 | CLT/SDK | 統一的指令行工具和 JAVA/PYTHON SDK |
SQL | MaxCompute SQL | TPC-DS 100%支援,同時文法高度相容 Hive, 有Hive 背景,開發者直接上手,特别在大資料 規模下性能強大。 * 完全自主開發的 compiler,語言功能開發更 靈活,疊代快,文法語義檢查更加靈活高效 * 基于代價的優化器,更智能,更強大,更适合 複雜的查詢 * 基于LLVM 的代碼生成,讓執行過程更高效 * 支援複雜資料類型(array,map,struct) * 支援Java、Python語言的UDF/UDAF/UDTF * 文法:Values、CTE、SEMIJOIN、FROM倒 裝、Subquery Operations 、 Set Operations(UNION /INTERSECT /MINUS)、 SELECT TRANSFORM 、User Defined Type、 GROUPING SET(CUBE/rollup/GROUPING SET)、腳本運作模式、參數化視圖 * 支援外表(外部資料源+StorageHandler,支 持非結構化資料) |
Spark | MaxCompute Spark | MaxCompute提供了Spark on MaxCompute 的解決方案,使 MaxCompute 提供相容開源的 Spark 計算服務,讓它在統一的計算資源和資料 集權限體系之上,提供 Spark 計算架構,支援用 戶以熟悉的開發使用方式送出運作 Spark 作 業。 * 支援原生多版本 Spark 作業: Spark1.x/Spark2.x作業都可運作; * 開源系統的使用體驗:Spark-submit 送出方 式,提供原生的 Spark WebUI供使用者檢視; * 通過通路OSS、OTS、database 等外部資料 源,實作更複雜的 ETL 處理,支援對 OSS 非結 構化進行處理; * 使用 Spark 面向 MaxCompute 内外部資料 開展機器學習, 擴充應用場景 |
機器學習 | PAI | MaxCompute 内建支援的上百種機器學習算 法,目前 MaxCompute 的機器學習能力由 PAI 産品進行統一提供服務,同時 PAI提供了深度學 習架構、Notebook 開發環境、GPU計算資源、 模型線上部署的彈性預測服務。MaxCompute 的資料對PAI産品無縫內建。 |
資料接入 | 目前支撐通過 DTS或者 DataWorks資料內建功能 | 資料內建是穩定高效、彈性伸縮的資料同步平台,豐富的異構資料源之間高速穩定的資料移動及同步能力。支援實時任務和批任務寫入MaxCompute |
整體 | 不是孤立的功能,完整的企業 服務 | 不需要多元件內建、調優、定制,開箱即用 |
3、RedShift到MaxCompute遷移工具介紹
從資料庫表導入到 Amazon S3
https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/c_unloading_data.html?spm=a2c4g.11186623.0.0.50d3358eWX84rm線上遷移上雲服務
https://help.aliyun.com/document_detail/94352.html将資料從OSS遷移至同區域的MaxCompute項目load指令
https://help.aliyun.com/document_detail/157418.htm?spm=a2c4g.11186623.0.0.50d3358eWX84rm#concept-2419019文法校驗工具二選一
MaxCompute studio
https://help.aliyun.com/document_detail/50889.htmlDataWorks sql節點
https://help.aliyun.com/document_detail/137510.html4、遷移整體方案
資料庫遷移主要包含以下内容
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yY4MjY4IWN4QDO0UWL0AzNh1iMxYGNtQ2YxgTLwEDOyYDZmFTL1ETO5ADN4cjM1QTNx8CXxQTMvw1ZuB3LchTMwIzLcBzLctmchx2Lc12bj5yayFGbu5ibkN2Lc9CX6MHc0RHaiojIsJye.png)
遷移實施計劃:
序号 | 項目 | 預估時間 |
1 | 調研評估 | 1~2周 |
2 | 方案設計 | |
3 | 資源規劃 | 1周 |
4 | 改造與測試驗證 | 5~7周,需要根據複雜度評估 |
5 | 生成割接 |
5、遷移詳細方案
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yY4MjY4IWN4QDO0UWL0AzNh1iMxYGNtQ2YxgTLwEDOyYDZmFTL1ETO5ADN4cjM1QTNx8CXxQTMvw1ZuB3LchTMwIzLcBzLctmchx2Lc12bj5yayFGbu5ibkN2Lc9CX6MHc0RHaiojIsJye.png)
5.1. 現狀分析及需求分析
5.2. 遷移方案設計
使用者根據自身現有 RedShift資料量、QPS、TPS 等性能名額、高可用需求和未來業務增長需求,制定合理化的遷移方案。
5.3. 資源規劃
使用者需要準備好 MaxCompute 的相關環境,同時擷取到對應需要使用的遷移工具。遷移工具的相關内容請參考《 RedShift到MaxCompute遷移工具介紹 》 章節。
5.4. 改造及測試驗證
5.4.1. 改造
遷移工具可以利用MaxCompute studio(或者DataWorks建立sql節點)用戶端文法校驗,建立一個sql檔案,如圖不支援的文法會報紅
MaxCompute Studio安裝文檔
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yY4MjY4IWN4QDO0UWL0AzNh1iMxYGNtQ2YxgTLwEDOyYDZmFTL1ETO5ADN4cjM1QTNx8CXxQTMvw1ZuB3LchTMwIzLcBzLctmchx2Lc12bj5yayFGbu5ibkN2Lc9CX6MHc0RHaiojIsJye.png)
5.4.1.1. 建表
在RedShift中擷取表清單和表字段定義,按照Maxcompute支援的字段值進行轉換,對于有update和delete語句的表必須建成Transactional表
類型轉化參考《資料類型對比及類型轉化》章節
建表文法
--建立新表。
create [external] table [if not exists]
[( [not null] [default ] [comment ], ...)]
[comment ]
[partitioned by ( [comment ], ...)]
--用于建立聚簇表時設定表的Shuffle和Sort屬性。
[clustered by | range clustered by ( [, , ...]) [sorted by ( [asc | desc] [, [asc | desc] ...])] into buckets]
--僅限外部表。
[stored by StorageHandler]
[with serdeproperties (options)]
[location ]
--指定表為Transactional表,後續可以對該表執行更新或删除表資料操作,但是Transactional表有部分使用限制,請根據需求建立。
[tblproperties("transactional"="true")]
[lifecycle ];
--基于已存在的表建立新表并複制資料,但不複制分區屬性。
create table [if not exists] [lifecycle ] as ;
--基于已存在的表建立具備相同結構的新表但不複制資料。
create table [if not exists] like [lifecycle ];
說明:
⚫ 表名與列名均對大小寫不敏感。
⚫ 在建立表時,如果不指定 if not exists選項而存在同名表,則傳回報錯;若指定此選項,則無論是否存在同名表,即使原表結構與要建立的目标表結構不一緻, 均傳回成功。已存在的同名表的元資訊不會被改動。
⚫ 表名、列名中不能有特殊字元,隻能用英文的 a-z、A-Z 及數字和下劃線(_),
且以字母開頭,名稱的長度不超過 128 位元組。
⚫tblproperties("transactional"="true"):可選(有update和delete語句必須設定)。設定表為Transactional表。後續可以對Transactional表執行update、delete操作實作行級更新或删除資料。更多資訊,請參見
更新或删除資料(UPDATE | DELETE)。
⚫ Partitioned by 指定表的分區字段,目前僅支援 string類型。分區值不可以有雙位元組字元(如中文),必須是以英文字母 a-z、A-Z開始後可跟字母數字,名稱的長度不超過 128 位元組。允許的字元包括:空格、冒号(:)、下劃線(_)、美元符$)、井号(#)、點(.)、感歎号(!)和@,出現其他字元行為未定義, 例如:“\t”、“\n”、“/”等。當利用分區字段對表進行分區時,新增分區、更新分區内資料和讀取分區資料均不需要做全表掃描,可以提高處理效率。
⚫ 注釋内容是長度不超過 1024 位元組的有效字元串。
⚫ lifecycle 指明此表的生命周期,機關:天。create table like 語句不會複制源表
的生命周期屬性。
⚫ 理論上源表分區最多隻能 6 級,但考慮極限存儲的分區膨脹方式,請盡可能少用
分區。
⚫ 一個表允許的分區個數支援按照具體的 project 配置,預設 60000 個。
⚫ 在create table ... as select ...語句中,如果在 select 子句中使用常量作為列的
值,建議指定列的名字。
⚫ 如果希望源表和目标表具有相同的表結構,可以嘗試使用 create table ... like 操
作。
5.4.1.1.1建表具體案例
RedShift到MaxCompute遷移實踐指導1.概要2.遷移前RedShift于MaxCompute的各項對比差異3、RedShift到MaxCompute遷移工具介紹4、遷移整體方案5、遷移詳細方案
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yY4MjY4IWN4QDO0UWL0AzNh1iMxYGNtQ2YxgTLwEDOyYDZmFTL1ETO5ADN4cjM1QTNx8CXxQTMvw1ZuB3LchTMwIzLcBzLctmchx2Lc12bj5yayFGbu5ibkN2Lc9CX6MHc0RHaiojIsJye.png)
- 列名雙引号要去掉
- 形如BIGINT primary key identity(1,1)主鍵⾃增列要去掉,隻保留預設值default 1
- numeric資料類型要轉為decimal
- 形如::character varying,'1900/01/01'::text這種,兩個冒号及後⾯内容要删除,MC不⽀持
- 形如"n_car_no" numeric DEFAULT -1::numeric,MC不⽀持預設值為-1,需要去掉
- 形如"ts_req_time" timestamp without time zone DEFAULT to_timestamp('1900/00/00 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text),需要去掉timezone,并改為timestamp DEFAULT timestamp "1900-01-01 00:00:00"
- 形如INTERLEAVED SORTKEY(vc_trans_id),MC不⽀持交錯排序列功能,可以考慮替換為 zorder。
- MC不⽀持時區time zone,有關time zone的需要删除。
- 物化視圖修改去掉 AUTO REFRESH YES,同時MaxCompute物化視圖不支援視窗函數
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yY4MjY4IWN4QDO0UWL0AzNh1iMxYGNtQ2YxgTLwEDOyYDZmFTL1ETO5ADN4cjM1QTNx8CXxQTMvw1ZuB3LchTMwIzLcBzLctmchx2Lc12bj5yayFGbu5ibkN2Lc9CX6MHc0RHaiojIsJye.png)
5.4.1.2. SQL 遷移
SQL 遷移實際上就是根據 Oracle 和MaxCompute 兩者間 SQL 的差異進行轉化,将RedShift中的 SQL 轉化成 MaxCompute 中的 SQL,進而使 SQL 可用。具體的 SQL間差異請參考《遷移前RedShift于MaxCompute的各項對比差異》 章節中的相關内容
5.4.1.2.1 SQL 遷移 具體案例
DML語句
1.執行updae或者delet的語句需要建立事務表("transactional"="true")
2. 形如COMMENT ON column atzc_dev_dw.t_com_fact_auto_pay_gw_trans_pay_gw."n_trans_amt" is 'dml';給列添加 注釋,需要改為MC⽀持的文法alter table change column comment '';
DQL語句
問題現象 | 遷移指導 | |
cte(with)語句 | with語句寫在insert into下面文法解析報錯 | with語句移動到insert into上面 |
with a as ( with b as () ) 嵌套使用 MC不支援嵌套的with 需要将with拿出來 | with a as () , b as () | |
類型轉化 | redshift都使用的是 :: 如:a::date | 使用cast(a as date) |
正常比對 | redshift使用的是 ~ | 使用rlike替換 |
group by | redshift group by中的整型常量會被當做select的列序号處理 如:group by 1,2. | SQL語句設定了屬性,即set odps.sql.groupby.position.alias=true;一起送出 |
類型轉化 :: | redshift ::代表類型轉化 | 使用cast函數轉化 |
資料類型 | varchar | 需要指定位數varchar(100)或者直接指定string |
decimal 類型常量1 | 改成1bd | |
smallint 常量 | 1s | |
join的不等值 | mc不支援普通join不等值表達式,可以使用mapjoin |
内建函數
RedShift | RS舉例 | MC舉例 | |
多行注釋/* xxxxx */ | 框選所需注釋内容,ctrl+/,進行注釋 | ||
DATEADD( datepart, interval, {date|time|timetz|timestamp} ) | datetime dateadd(date|datetime|timestamp <date>, bigint <delta>, string <datepart>) | dateadd(day,1,f.dt_date) | dateadd(f.dt_date,1,'dd') |
DATEDIFF ( datepart, {date|time|timetz|timestamp}, {date|time|time|timestamp} ) | bigint datediff(date|datetime|timestamp <date1>, date|datetime|timestamp <date2>, string <datepart>) | datediff(min,a,b) | datediff(b,a,'mi') |
current_date-n/current_date+n | dateadd(GETDATE(),n) dateadd可以加減時間,getdate可以擷取目前時間 | current_date-1 | dateadd(GETDATE(),1,'dd') |
cast轉 | a::date | cast(a as date) | |
rlike | |||
日期加減current_date+30 | date_add(current_date(),30) | ||
CEILING 或 CEIL 函數用于将數字向上舍入到下一個整數。 | ceil | select ceiling(commission) | select ceil(1.1); |
TO_TIMETAMP 将時間戳字元串轉換為時間标記 | bigint unix_timestamp(datetime <date>) | to_timestamp('1900/00/00 00:00:00'as string, 'YYYY-MM-DD HH24:MI:SS.MS'as string) | unix_timestamp(cast ("1900-00-00 00:00:00" as datetime)) |
dateadd按指定的時間間隔遞增日期、時間、時間或時間戳值 | dateadd(month,-6,a.dt_end_date) | dateadd(a.dt_end_date,-6,"mm") | |
LISTAGG 聚合函數根據 ORDER BY 表達式對該組的行進行排序,然後将值串聯成一個字元串 | wm_concat(string <separator>, string <colname>) | listagg(remark) | wm_Concat(",",remark) |
CURRENT_DATE擷取目前日期 | CURRENT_DATE() MaxCompute需要添加括号 | ||
EXTRACT(week from $1)提取函數從 TIMESTAMP 值或表達式 | weekofyear() | ||
EXTRACT(weekday from $1) 和 extract(DOW from $1) | weekday($1) | ||
DATEPART(WEEKDAY,T3.dt_report) | WEEKDAY(cast(T3.dt_report as DATETIME)) | ||
LEN 函數傳回一個整數,表示輸入字元串中的字元的數量 | bigint length(string <str>) | len | length |
LOWER 函數傳回與輸入字元串具有相同資料類型的字元串 | tolower(string <source>) | lower | |
CONVERT ( TIMESTAMP, id_card_back_overdue) 函數将值從一種資料類型轉換為另一種資料類型 | 轉為cast() | cast(id_card_back_overdue as TIMESTAMP) | |
sysdate傳回目前會話時區(預設為 UTC)中的目前日期和時間 | getdate() 傳回DATETIME ‘2017-11-11 00:00:00’ | ||
charindex() 傳回指定子字元串在字元串中的位置 | INSTR() | charindex('fish', 'dogfish') | instr('dogfish','fish') |
left()這些函數傳回指定數量的位于字元串最左側 | substr() | ||
right()這些函數傳回指定數量的位于字元串最右側 | reverse(substr(reverse())) | ||
DATE_TRUNC 函數根據您指定的日期部分(如小時、周或月)截斷時間戳表達式或文字 date_trunc('month') | datetrunc(,'month') | ||
json_extract_path_text 函數傳回鍵:Value對引用 JSON 字元串中的一系列路徑元素 | 改為get_json_object寫法get_json_object(content,'$.DeviceID') | 根據key路徑擷取json字元串的value | json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4', 'f6') 傳回 ‘star’ |
json_extract_array_element_text | 使用atzc_dev_dw.json_extract_array_element_text | 根據索引傳回數組元素 | json_extract_array_element_text('[111,112,113]', 2) 傳回 ‘113’ |
POSITION傳回指定子字元串在字元串中的位置 | 改成:instr | ||
BTRIM 函數通過删除前導空格和尾随空格或删除 | maxCompute隻能删除左右空格不能删除指定位置空格,删除指定位置需要自己寫udf實作 | ||
date_part()從表達式中提取日期部分值 | datepart() | ||
mod() 函數傳回一個數字結果 | $1%$2 | ||
date_part(w,time) |
4.4.1.2存儲過程遷移
建議改成臨時表或者pyodps的方式
5.4.2資料遷移
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yY4MjY4IWN4QDO0UWL0AzNh1iMxYGNtQ2YxgTLwEDOyYDZmFTL1ETO5ADN4cjM1QTNx8CXxQTMvw1ZuB3LchTMwIzLcBzLctmchx2Lc12bj5yayFGbu5ibkN2Lc9CX6MHc0RHaiojIsJye.png)
描述 | |
① | 将Amazon Redshift資料導出至Amazon S3資料湖(簡稱S3)。 |
② | 通過對象存儲服務OSS的線上遷移上雲服務,将資料從S3遷移至OSS。 |
③ | 将資料從OSS遷移至同區域的MaxCompute項目中,并校驗資料完整性和正确性。 |
資料遷移參考文檔:
https://help.aliyun.com/document_detail/181920.html5.4.3. 測試驗證
目前RedShift到MaxCompute 遷移的資料測試驗證工作,還沒有工具可以支援,需要
自行編寫腳本工具完成,常用校驗方案有如下幾種:
⚫ 表結構校驗,從 RedShift和MaxCompute 分别導出資料表列及類型定義後計算
md5 進行校驗
⚫ 資料表行數比對,執行 SQL 語句分别在 RedShift和MaxCompute 統計相同表的
資料行數進行逐行比對
⚫ 資料全量校驗,一般用于核心表且資料量較小的校驗場景,導出全量資料計算
md5 進行校驗,或全量資料分段計算 md5 進行校驗
⚫ 資料抽樣校驗,一般用于核心大表的資料校驗場景,按一定抽樣規則從源和目标
抽取資料進行校驗。
更多關于大資料計算、雲資料倉庫技術交流,歡迎掃碼檢視咨詢。