在MaxCompute中執行DQL操作過程中的常見問題 - MaxCompute

本文為您介紹在MaxCompute中執行DQL操作過程中的常見問題。

問題類別	常見問題
GROUP BY	在執行MaxCompute SQL過程中，報錯Repeated key in GROUP BY，如何解決？在執行MaxCompute SQL過程中，報錯Expression not in GROUP BY key，如何解決？對錶A執行GROUP BY產生表B，表B比表A的行數少，但表B的實體儲存體量是表A的10倍，是什麼原因造成的？使用GROUP BY分組查詢100億條資料會不會影響效能？GROUP BY對資料量有沒有限制？
ORDER BY	MaxCompute查詢得到的資料是根據什麼排序的？ MaxCompute是否支援ORDER BY FIELD NULLS LAST文法？執行MaxCompute SQL過程中，報錯ORDER BY must be used with a LIMIT clause，如何解決？
子查詢	在執行MaxCompute SQL過程中，使用NOT IN後面接子查詢，子查詢返回的結果是上萬層級的資料量，但當IN和NOT IN後面的子查詢返回的是分區時，返回的數量上限為1000。在必須使用NOT IN的情況下，該如何?此查詢？
交集、並集和補集	如何合并兩個沒有任何關聯關係的表？在執行UNION ALL操作時，報錯ValidateJsonSize error，如何解決？
JOIN	在執行JOIN操作時，報錯Both left and right aliases encountered in JOIN，如何解決？在執行JOIN操作時，報錯Maximum 16 join inputs allowed，如何解決？在執行JOIN操作時，發現JOIN結果資料條數比原表多，如何解決？在執行JOIN操作時，分區裁剪條件放在ON中分區裁剪會生效，還是放在WHERE中才會生效？
MAPJOIN	如何用MAPJOIN緩衝多張小表？ MAPJOIN中的大表和小表是否可以互換位置？
其他	MaxCompute SQL設定過濾條件後，報錯提示輸入的資料超過100 GB，如何解決？ MaxCompute SQL中模糊查詢的WHERE條件是否支援Regex？如果只同步100條資料，如何在過濾條件WHERE中通過LIMIT實現？如何能提高查詢效率？分區設定能調整嗎？ MaxCompute SQL支援WITH AS語句嗎？如何將一行資料拆分為多行資料？在用戶端的odps_config.ini檔案中設定use_instance_tunnel=false，instance_tunnel_max_record=10，為什麼Select還是能輸出很多記錄？如何用Regex判斷欄位是否為中文？

在執行MaxCompute SQL過程中，報錯Repeated key in GROUP BY，如何解決？

問題現象

在執行MaxCompute SQL過程中，返回報錯如下。

FAILED: ODPS-0130071:Semantic analysis exception - Repeated key in GROUP BY。

產生原因
SELECT DISTINCT後不能跟常量。
解決措施
將SQL拆分為兩層，內層處理沒有常量的DISTINCT邏輯，外層加入常量資料。

在執行MaxCompute SQL過程中，報錯Expression not in GROUP BY key，如何解決？

問題現象

執行MaxCompute SQL時，返回報錯如下。

FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 1:xx ‘xxx’

產生原因
不支援直接引用非GROUP BY的列。詳情請參見GROUP BY分組查詢（col_list）。
解決措施
修改SQL語句，確保在GROUP BY子句中，SELECT查詢的列，必須是GROUP BY中的列或彙總函式（例如SUM或COUNT）加工過的列。

對錶A執行GROUP BY產生表B，表B比表A的行數少，但表B的實體儲存體量是表A的10倍，是什麼原因造成的？

資料在MaxCompute中是列式壓縮儲存的，如果同一列的前後資料的內容是相似的，壓縮比會比較高。當odps.sql.groupby.skewindata=true開啟時，使用SQL寫入資料，資料比較分散，壓縮比較小。如果希望資料的壓縮比較高，您可以在使用SQL寫入資料時進行局部排序。

使用GROUP BY分組查詢100億條資料會不會影響效能？GROUP BY對資料量有沒有限制？

使用GROUP BY分組查詢100億條資料不會影響效能。GROUP BY對資料量無限制。GROUP BY分組查詢詳情請參見GROUP BY分組查詢（col_list）。

MaxCompute查詢得到的資料是根據什麼排序的？

MaxCompute中表的讀取是無序的。如果您沒有進行自訂設定，查詢擷取的結果也是無序的。

如果您對資料的順序有要求，需要對資料進行排序。例如，在SQL中需要加上order by xx limit n對資料進行排序。

如果您需要對資料進行全排序，只需要將limit面的n設定為資料總條數+1即可。

說明

海量資料的全排序，對效能的影響非常大，而且很容易造成記憶體溢出問題，請盡量避免執行該操作。

MaxCompute是否支援ORDER BY FIELD NULLS LAST文法？

MaxCompute支援此文法。MaxCompute支援的文法請參見與其他SQL文法的差異。

執行MaxCompute SQL過程中，報錯ORDER BY must be used with a LIMIT clause，如何解決？

問題現象

執行MaxCompute SQL過程中，返回報錯如下。

FAILED: ODPS-0130071:[1,27] Semantic analysis exception - ORDER BY must be used with a LIMIT clause, please set odps.sql.validate.orderby.limit=false to use it.

產生原因
ORDER BY需要對單個執行節點做全域排序，所以預設帶LIMIT限制，避免誤用導致單點處理大量資料。
解決措施
如果您的使用情境確實需要ORDER BY放開LIMIT限制，可以通過如下兩種方式實現：
- Project層級：設定setproject odps.sql.validate.orderby.limit=false;關閉order by必須帶limit的限制。
- Session層級：設定set odps.sql.validate.orderby.limit=false;關閉order by必須帶limit的限制，需要與SQL語句一起提交。
  說明
  如果關閉order by必須帶limit的限制，在單個執行節點有大量資料排序的情況下，資源消耗或處理時間長度等效能表現會受到影響。

更多ORDER BY資訊，請參見ORDER BY全域排序（order_condition）。

在執行MaxCompute SQL過程中，使用NOT IN後面接子查詢，子查詢返回的結果是上萬層級的資料量，但當IN和NOT IN後面的子查詢返回的是分區時，返回的數量上限為1000。在必須使用NOT IN的情況下，該如何?此查詢？

您可以使用left outer join命令查詢。

select * from a where a.ds not in (select ds from b);
改成如下語句。
select a.* from a left outer join (select distinct ds from b) bb on a.ds=bb.ds where bb.ds is null;

如何合并兩個沒有任何關聯關係的表？

您可以通過union all運算完成縱向合并。橫向合并可以通過row_number函數實現，兩個表都新加一個ID列，進行ID關聯，然後取兩張表的欄位。更多資訊，請參見並集或ROW_NUMBER。

在執行UNION ALL操作時，報錯ValidateJsonSize error，如何解決？

問題現象

執行包含200個UNION ALL的SQL語句select count(1) as co from client_table union all ...時，返回報錯如下。

FAILED: build/release64/task/fuxiWrapper.cpp(344): ExceptionBase: Submit fuxi Job failed, {
    "ErrCode": "RPC_FAILED_REPLY",
    "ErrMsg": "exception: ExceptionBase:build/release64/fuxi/fuximaster/fuxi_master.cpp(1018): ExceptionBase: StartAppFail: ExceptionBase:build/release64/fuxi/fuximaster/app_master_mgr.cpp(706): ExceptionBase: ValidateJsonSize error: the size of compressed plan is larger than 1024KB\nStack

產生原因
- 原因一：SQL語句轉化為執行計畫後，超過了底層架構限制的1024 KB，導致SQL執行報錯。執行計畫的長度與SQL語句長度沒有直接換算關係，暫時無法預估。
- 原因二：由於分區量過大導致執行計畫超過限制。
- 原因三：由於小檔案比較多導致SQL運行失敗。
解決措施
- 原因一的解決措施：對於過長的SQL語句，建議拆分成多次運行，避免觸發長度限制。
- 原因二的解決措施：如果分區過大，需要調整分區個數，詳情請參見分區。
- 原因三的解決措施：如果是由於小檔案較多導致，請合并小檔案。

在執行JOIN操作時，報錯Both left and right aliases encountered in JOIN，如何解決？

問題現象

執行MaxCompute SQL過程中，返回報錯如下。

FAILED: ODPS-0130071:Semantic analysis exception - Both left and right aliases encountered in JOIN : line 3:3 ‘xx’: . I f you really want to perform this join, try mapjoin

產生原因
- 原因一：SQL關聯條件ON中包含非等值串連，例如table1.c1>table2.c3。
- 原因二：SQL中JOIN條件的某一側資料來自兩張表，例如table1.col1 = concat(table1.col2,table2.col3)。
解決措施
- 原因一的解決措施：修改SQL語句，關聯條件需要為等值串連。
  說明
  如必須使用非等值串連，可以增加mapjoin hint，詳情請參見ODPS-0130071。
- 原因二的解決措施：如果其中一張表比較小，您可以使用MAPJOIN方法。

在執行JOIN操作時，報錯Maximum 16 join inputs allowed，如何解決？

問題現象

在執行MaxCompute SQL過程中，返回報錯如下。

FAILED: ODPS-0123065:Join exception - Maximum 16 join inputs allowed

產生原因
MaxCompute SQL最多支援6張小表的MAPJOIN，並且連續JOIN的表不能超過16張。
解決措施
將部分小表JOIN成一張暫存資料表作為輸入表，減少輸入表的個數。

在執行JOIN操作時，發現JOIN結果資料條數比原表多，如何解決？

問題現象
執行如下MaxCompute SQL語句後，查詢返回結果的條數大於table1的資料條數。
```
select count(*) from table1 a left outer join table2 b on a.ID = b.ID;
```
產生原因
樣本SQL是table1通過ID欄位和table2的ID欄位做左外關聯，所以會出現以下情況：
- 如果table2表中找不到關聯資料，table1也會返回一條資料。
- 如果table1找不到但是table2能找到關聯資料，則不返回結果。
- 如果table1和table2都能找到關聯資料，該關聯邏輯和普通的內關聯一樣。如果同樣的ID欄位在table2中能找到資料，返回結果為table1和table2的笛卡爾積。
假設table1的資料如下。
id
values
1
a
1
b
2
c
假設table2的資料如下。
id
values
1
A
1
B
3
D
執行select count(*) from table1 a left outer join table2 b on a.ID = b.ID;命令返回的結果如下。
id1
values1
id2
values2
1
b
1
B
1
b
1
A
1
a
1
B
1
a
1
A
2
c
NULL
NULL
- id=1的資料兩邊都有，執行笛卡爾積，返回4條資料。
- id=2的資料只有table1有，返回了1條資料。
- id=3的資料只有table2有，table1裡沒資料，不返回資料。
解決措施
確認出現資料條數增加是否是因為table2的資料導致。命令樣本如下。此處增加limit 10是考慮到如果table2中的資料條數很多，會刷屏。如果只是確認問題，驗證前幾條資料即可。
```
select id, count() as cnt from table2 group by id having cnt>1 limit 10;
```
如果是在重複的情況下不希望執行笛卡爾積，希望有類似SQL裡IN的功能，可以改寫SQL為如下語句。
```
select * from table1 a left outer join (select distinct id from table2) b on a.id = b.id;
```

在執行JOIN操作時，已經指定了分區條件，為何提示禁止全表掃描？

問題現象

在兩個專案裡執行如下同一段代碼，一個專案中成功，一個專案中失敗。

select t.stat_date 
from fddev.tmp_001 t  
left outer join (select '20180830' as ds from fddev.dual ) t1 
on t.ds = 20180830
group by t.stat_date;

失敗報錯如下。

Table(fddev，tmp_001) is full scan with all partisions,please specify partitions predicates.

產生原因
在執行SELECT操作時，如果需要指定分區請使用WHERE子句。使用ON屬於非標準用法。
執行成功的專案設定了允許非標準SQL的行為，即執行了set odps.sql.outerjoin.supports.filters=false命令，該配置會把ON裡的條件轉換為過濾條件，可用於相容Hive文法，但不符合SQL標準。
解決措施
建議將分區過濾條件置於WHERE子句。

在執行JOIN操作時，分區裁剪條件放在ON中分區裁剪會生效，還是放在WHERE中才會生效？

如果分區剪裁條件置於WHERE語句中，分區剪裁會生效。如果分區剪裁條件置於ON語句中，從表的分區剪裁會生效，主表的分區剪裁不會生效即會全表掃描。更多分區裁剪資訊，請參見分區剪裁合理性評估。

如何用MAPJOIN緩衝多張小表？

您可以在MAPJOIN中填寫表的別名。

假設專案中存在一張表iris，表資料如下。

+——————————————————————————————————————————+

| Field           | Type       | Label | Comment                                     |
+——————————————————————————————————————————+

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

+——————————————————————————————————————————+

使用MAPJOIN實現緩衝小表的命令樣本如下。

select 
  /*+ mapjoin(b,c) */
  a.category,
  b.cnt as cnt_category,
  c.cnt as cnt_all
from iris a
join
(
  select count() as cnt,category from iris group by category
) b
on a.category = b.category
join 
(
  select count(*) as cnt from iris
) c;

MAPJOIN中的大表和小表是否可以互換位置？

MAPJOIN中的大表和小表是根據表佔用空間Size大小區分的。

系統會將您指定的小表全部載入到執行JOIN操作的程式的記憶體中，繼而加快JOIN的執行速度。如果將大表和小表互換位置，系統不會報錯，但是效能會變差。

MaxCompute SQL設定過濾條件後，報錯提示輸入的資料超過100 GB，如何解決？

先過濾分區，再取資料。取資料後，再過濾其他非分區欄位。輸入表的大小是取決於過濾分區過濾後，過濾其他欄位前表的大小。

MaxCompute SQL中模糊查詢的WHERE條件是否支援Regex？

支援，例如select * from user_info where address rlike '[0-9]{9}';，表示尋找9位元字組成的ID。

如果只同步100條資料，如何在過濾條件WHERE中通過LIMIT實現？

LIMIT不支援在過濾條件中使用。您可以先使用SQL篩選出100條資料，再執行同步操作。

如何能提高查詢效率？分區設定能調整嗎？

當利用分區欄位對錶進行分區時，新增分區、更新分區和讀取分區資料均不需要做全表掃描，可以提高處理效率。詳情請參見表操作。

MaxCompute SQL支援WITH AS語句嗎？

支援，MaxCompute支援SQL標準的CTE，提高SQL語句的可讀性與執行效率。更多資訊，請參見COMMON TABLE EXPRESSION（CTE）。

如何將一行資料拆分為多行資料？

Lateral View和表產生函數（例如Split和Explode）結合使用，可以將一行資料拆成多行資料，並對拆分後的資料進行彙總。

在用戶端的odps_config.ini檔案中設定use_instance_tunnel=false，instance_tunnel_max_record=10，為什麼Select還是能輸出很多記錄？

需要修改use_instance_tunnel=false為use_instance_tunnel=true，才能通過instance_tunnel_max_record控制輸出記錄數。

如何用Regex判斷欄位是否為中文？

命令樣本如下。

select '欄位' rlike '[\\x{4e00}-\\x{9fa5}]+';

id1	values1	id2	values2
1	b	1	B
1	b	1	A
1	a	1	B
1	a	1	A
2	c	NULL	NULL

id	values
1	a
1	b
2	c

id	values
1	A
1	B
3	D