MaxCompute における DQL 操作に関する FAQ - - Alibaba Cloud ドキュメントセンター

このトピックでは、MaxCompute におけるデータクエリ言語（DQL）操作に関するよくある質問への回答を提供します。

カテゴリ	FAQ
GROUP BY	MaxCompute SQL 文を実行すると「GROUP BY でキーが重複しています」というエラーメッセージが表示される場合の対処方法 MaxCompute SQL 文を実行すると「式が GROUP BY キーに含まれていません」というエラーメッセージが表示される場合の対処方法テーブル 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 は LIMIT 句と一緒に使用する必要があります」というエラーメッセージが表示される場合の対処方法
サブクエリ	NOT IN の後にサブクエリが続く MaxCompute SQL 文を実行すると、サブクエリは数万件のデータレコードを返すことが期待されます。ただし、IN または NOT IN の後に続くサブクエリがパーティションデータを返す場合、返されるデータレコードの最大数は 1,000 です。サブクエリが期待される数のデータレコードを返し、NOT IN のロジックが実装されるようにするにはどうすればよいですか？
INTERSECT、UNION、EXCEPT、および MINUS	互いに関連付けられていない 2 つのテーブルをマージするにはどうすればよいですか？ UNION ALL 操作を実行すると「ValidateJsonSize エラー」というエラーメッセージが表示される場合の対処方法
JOIN	JOIN 操作を実行すると「JOIN で左と右の両方のエイリアスが見つかりました」というエラーメッセージが表示される場合の対処方法 JOIN 操作を実行すると「最大 16 個の結合入力が許可されています」というエラーメッセージが表示される場合の対処方法 JOIN 操作を実行した後、返されるデータレコードの数がソーステーブルのいずれかのデータレコードの数よりも多い場合の対処方法 JOIN 操作を実行する場合、パーティションプルーニング条件が ON 句または WHERE 句で指定されている場合、パーティションプルーニングは有効になりますか？
MAPJOIN	MAPJOIN を使用して複数の小さなテーブルをキャッシュするにはどうすればよいですか？ MAPJOIN 文で指定されている大きなテーブルと小さなテーブルを交換できますか？
その他	MaxCompute SQL 文でフィルター条件を構成した後、入力データのサイズが 100 GB を超えていることを示すエラーメッセージが表示されます。どうすればよいですか？ MaxCompute SQL でのあいまいクエリの WHERE 条件は正規表現をサポートしていますか？ 100 件のデータレコードのみを同期する場合、WHERE 句で同期するデータレコードの数を LIMIT を使用して指定するにはどうすればよいですか？クエリ効率を向上させるにはどうすればよいですか？パーティション設定を調整できますか？ MaxCompute SQL は WITH AS 文をサポートしていますか？ 1 行のデータを複数行に分割するにはどうすればよいですか？クライアントの odps_config.ini ファイルで use_instance_tunnel=false および instance_tunnel_max_record=10 を指定した後も、SELECT 文で多数の出力レコードが生成されます。なぜでしょうか？正規表現を使用して、フィールドの値が中国語であるかどうかを判断するにはどうすればよいですか？

MaxCompute SQL 文を実行すると「GROUP BY でキーが重複しています」というエラーメッセージが表示される場合の対処方法

問題の説明
MaxCompute SQL 文を実行すると、次のエラーメッセージが表示されます。
```
FAILED: ODPS-0130071:Semantic analysis exception - GROUP BY でキーが重複しています。
```
原因
SELECT DISTINCT の後に定数を続けることはできません。
解決策
SQL 文の実行ロジックを 2 つのレイヤーに分割します。このようにして、定数を含まない DISTINCT ロジックは内側のレイヤーで処理され、定数データは外側のレイヤーに追加されます。

MaxCompute SQL 文を実行すると「式が GROUP BY キーに含まれていません」というエラーメッセージが表示される場合の対処方法

問題の説明

MaxCompute SQL 文を実行すると、次のエラーメッセージが表示されます。

FAILED: ODPS-0130071:Semantic analysis exception - 式が GROUP BY キーに含まれていません : line 1:xx 'xxx'

原因
GROUP BY 句で指定されていない列を直接参照することはできません。詳細については、「GROUP BY (col_list)」をご参照ください。
解決策
SELECT を使用してクエリされる列が、GROUP BY 句で指定された列、または SUM や COUNT などの集計関数を使用して処理された列であることを確認するように SQL 文を変更します。

テーブル A に GROUP BY を実行した後にテーブル B が生成されます。テーブル B の行数はテーブル A の行数よりも少ないですが、テーブル B の物理ストレージ容量はテーブル A の物理ストレージ容量の 10 倍です。なぜでしょうか？

MaxCompute では、データは列指向圧縮モードで格納されます。同じ列の隣接データが類似している場合、データ圧縮率は高くなります。odps.sql.groupby.skewindata が true に設定されている場合、データは分散され、データ圧縮率は低くなります。高いデータ圧縮率を得るには、SQL 文を実行してデータを書き込むときに特定のデータをソートします。

GROUP BY を使用して 100 億件のデータレコードをクエリする場合、クエリのパフォーマンスは影響を受けますか？ GROUP BY を使用してデータをクエリする場合、データ量は制限されますか？

いいえ、クエリのパフォーマンスは影響を受けません。GROUP BY を使用してデータをクエリする場合、データ量は制限されません。GROUP BY の詳細については、「GROUP BY (col_list)」をご参照ください。

MaxCompute でデータをクエリした後、クエリ結果はどのようにソートされますか？

MaxCompute テーブルのデータはランダムな順序で配置されます。順序設定を構成しない場合、データもランダムな順序で返されます。

ソートされたデータを取得するには、SQL 文で order by xx limit n を指定してデータをソートします。

すべてのデータをソートするには、n を limit の後に データレコードの総数 + 1 の値 に設定します。

重要

大量の全データをソートすると、クエリのパフォーマンスに大きな影響があり、メモリオーバーフローが発生する可能性があります。この操作は実行しないことをお勧めします。

MaxCompute は ORDER BY FIELD NULLS LAST をサポートしていますか？

はい、MaxCompute は ORDER BY FIELD NULLS LAST をサポートしています。MaxCompute でサポートされている構文の詳細については、「SQL 文のサポートの違い」をご参照ください。

MaxCompute SQL 文を実行すると「ORDER BY は LIMIT 句と一緒に使用する必要があります」というエラーメッセージが表示される場合の対処方法

問題の説明

MaxCompute SQL 文を実行すると、次のエラーメッセージが表示されます。

FAILED: ODPS-0130071:[1,27] Semantic analysis exception - ORDER BY は LIMIT 句と一緒に使用する必要があります。使用するには、odps.sql.validate.orderby.limit=false を設定してください。

原因
ORDER BY 句は、単一ノードのすべてのデータをソートする必要があります。デフォルトでは、ORDER BY 句は LIMIT 句と一緒に使用して、単一ノードが大量のデータを処理しないようにします。
解決策
プロジェクトまたはセッションに対して ORDER BY 句と LIMIT 句の同時実行の制限を削除できます。
- プロジェクトの制限を削除するには、setproject odps.sql.validate.orderby.limit=false; コマンドを実行します。
- セッションの制限を削除するには、コミットして、実行する SQL 文で set odps.sql.validate.orderby.limit=false; コマンドを実行します。
  説明
  制限を削除した後、単一ノードにソートするデータが大量にある場合、大量のリソースと時間が消費されます。

ORDER BY の詳細については、「ORDER BY (ORDER_condition)」をご参照ください。

NOT IN の後にサブクエリが続く MaxCompute SQL 文を実行すると、サブクエリは数万件のデータレコードを返すことが期待されます。ただし、IN または NOT IN の後に続くサブクエリがパーティションデータを返す場合、返されるデータレコードの最大数は 1,000 です。サブクエリが期待される数のデータレコードを返し、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;

互いに関連付けられていない 2 つのテーブルをマージするにはどうすればよいですか？

UNION ALL 操作を使用して垂直マージを完了し、row_number 関数を使用して水平マージを完了できます。両方のテーブルに ID 列を追加し、ID 列を使用してテーブルを関連付けてから、必要なフィールドを選択します。詳細については、「UNION」または「ROW_NUMBER」をご参照ください。

UNION ALL 操作を実行すると「ValidateJsonSize エラー」というエラーメッセージが表示される場合の対処方法

問題の説明

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

原因
- 原因 1：実行計画の長さが、基盤となるアーキテクチャで許可される最大サイズである 1024 KB を超えています。その結果、SQL 実行エラーが返されます。実行計画の長さは SQL 文の長さに直接関係しません。したがって、実行計画の長さを推定することはできません。
- 原因 2：パーティションの数が多すぎます。
- 原因 3：小さなファイルの数が多すぎます。
解決策
- 原因 1 の解決策：生成される実行計画が最大長を超えないように、長すぎる SQL 文を分割します。
- 原因 2 の解決策：パーティションの数を調整します。詳細については、「パーティション」をご参照ください。
- 原因 3 の解決策：小さなファイルをマージする

JOIN 操作を実行すると「JOIN で左と右の両方のエイリアスが見つかりました」というエラーメッセージが表示される場合の対処方法

問題の説明

MaxCompute SQL 文を実行すると、次のエラーメッセージが表示されます。

FAILED: ODPS-0130071:Semantic analysis exception - JOIN で左と右の両方のエイリアスが見つかりました : line 3:3 'xx': . この結合を本当に実行する必要がある場合は、mapjoin を試してください

原因
- 原因 1：SQL 文の ON 条件に、table1.c1>table2.c3 などの非等結合が含まれています。
- 原因 2：SQL 文の JOIN 条件の片側のデータが、table1.col1 = concat(table1.col2,table2.col3) など、2 つのテーブルから取得されています。
解決策
- 原因 1 の解決策：SQL 文の ON 条件の非等結合を等結合に変更します。
  説明
  非等結合を使用する必要がある場合は、SQL 文に MAPJOIN ヒントを追加できます。詳細については、「ODPS-0130071」をご参照ください。
- 原因 2 の解決策：テーブルのいずれかが小さい場合は、MAPJOIN メソッドを使用します。

JOIN 操作を実行すると「最大 16 個の結合入力が許可されています」というエラーメッセージが表示される場合の対処方法

問題の説明
MaxCompute SQL 文を実行すると、次のエラーメッセージが表示されます。
```
FAILED: ODPS-0123065:Join exception - 最大 16 個の結合入力が許可されています
```
原因
MaxCompute SQL 文は、最大 6 つの小さなテーブルで MAPJOIN を実行でき、最大 16 個のテーブルを連続して結合できます。
解決策
入力テーブルの数を減らすために、いくつかの小さなテーブルを入力テーブルとして一時テーブルに結合します。

JOIN 操作を実行した後、返されるデータレコードの数がソーステーブルのいずれかのデータレコードの数よりも多い場合の対処方法

問題の説明
次の MaxCompute SQL 文を実行した後、返されるデータレコードの数が table1 テーブルのデータレコードの数よりも多くなります。
```
select count(*) from table1 a left outer join table2 b on a.ID = b.ID;
```
原因
左外部結合では、table2 に一致するエントリが見つからない場合でも、table1 からすべてのデータが返されます。 table2 に重複する ID がある場合、結果セットで返される行数が増加します。
次の表に、table1 のサンプルデータを示します。
id
値
1
a
1
b
2
c
次の表に、table2 のサンプルデータを示します。
id
値
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 つのデータレコードが返されます。
- table1 のみに ID フィールドの値が 2 であるデータがあります。したがって、1 つのデータレコードが返されます。
- table2 のみに ID フィールドの値が 3 であるデータがあります。したがって、データは返されません。
解決策
table2 に重複データがあるかどうかを確認します。サンプル文：
```
select id, count() as cnt from table2 group by id having cnt>1 limit 10;
```
デカルト積演算を実行したくない場合は、次の文を使用できます。
```
select * from table1 a left outer join (select distinct id from table2) b on a.id = b.id;
```

JOIN 操作を実行するときにパーティション条件を指定しますが、システムはフルテーブルスキャンが禁止されていることを示すメッセージを表示します。なぜでしょうか？

問題の説明

2 つのプロジェクトで次の文を実行すると、文は一方のプロジェクトでのみ正常に実行されます。

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;

次のエラーメッセージが表示されます。

テーブル (fddev,tmp_001) はすべてのパーティションでフルスキャンされます。パーティション述語を指定してください。

原因
SELECT 文を実行する場合、この目的で ON 句を使用することは非標準であるため、WHERE 句を使用してパーティション条件を指定する必要があります。
set odps.sql.outerjoin.supports.filters=false コマンドは、文が正常に実行されたプロジェクトで実行されます。この構成により、ON 句の条件がフィルター条件に変換され、非標準の SQL 文が許可されます。この構成は Hive 構文と互換性がありますが、SQL 標準には準拠していません。
解決策
パーティションフィルター条件を WHERE 句に配置することをお勧めします。

JOIN 操作を実行する場合、パーティションプルーニング条件が ON 句または WHERE 句で指定されている場合、パーティションプルーニングは有効になりますか？

パーティションプルーニング条件が WHERE 句で指定されている場合、パーティションプルーニングは有効になります。
パーティションプルーニング条件が ON 句で指定されている場合、パーティションプルーニングはセカンダリテーブルで有効になります。パーティションプルーニングはプライマリテーブルでは有効になりません。したがって、フルテーブルスキャンがトリガーされます。

パーティションプルーニングの詳細については、「パーティションプルーニングが有効かどうかを確認する」をご参照ください。

MAPJOIN を使用して複数の小さなテーブルをキャッシュするにはどうすればよいですか？

MAPJOIN は、小さなテーブルをメモリにキャッシュすることでクエリを高速化する最適化手法です。MAPJOIN 文でキャッシュするテーブルのエイリアスを指定できます。

たとえば、iris という名前のテーブルがプロジェクトに存在するとします。テーブルには次のデータがあります。

+------------------------------------------+

| フィールド         | タイプ     | ラベル | コメント                                     |
+------------------------------------------+

| 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 文の大きなテーブルと小さなテーブルは、各テーブルで使用される領域のサイズに基づいて区別されます。システムは、指定された小さなテーブルのすべてのデータをメモリにロードして、JOIN 操作を高速化します。

重要

MAPJOIN 文の大きなテーブルと小さなテーブルを交換した場合、エラーは返されませんが、処理パフォーマンスが低下します。

MaxCompute SQL 文でフィルター条件を構成した後、入力データのサイズが 100 GB を超えていることを示すエラーメッセージが表示されます。どうすればよいですか？

パーティションフィールドをフィルタリングしてデータを取得してから、他の非パーティションフィールドをフィルタリングします。入力テーブルのサイズは、パーティションフィールドをフィルタリングした後のデータ量によって異なります。

MaxCompute SQL でのあいまいクエリの WHERE 条件は正規表現をサポートしていますか？

はい、MaxCompute SQL でのあいまいクエリの WHERE 条件は正規表現をサポートしています。たとえば、select * from user_info where address rlike '[0-9]{9}'; は、9 桁の数字で構成される ID が検索されることを示します。

100 件のデータレコードのみを同期する場合、WHERE 句で同期するデータレコードの数を LIMIT を使用して指定するにはどうすればよいですか？

LIMIT は WHERE 句では使用できません。データを同期する前に、SQL 文を実行して 100 件のデータレコードを読み取ることができます。

クエリ効率を向上させるにはどうすればよいですか？パーティション設定を調整できますか？

パーティションフィールドを使用してテーブルをパーティション分割すると、パーティションを追加したり、パーティションデータを更新または読み取ったりするときに、フルテーブルスキャンはトリガーされません。これにより、データ処理の効率が向上します。詳細については、「テーブル操作」をご参照ください。

MaxCompute SQL は WITH AS 文をサポートしていますか？

はい、MaxCompute SQL は WITH AS 文をサポートしています。MaxCompute は、SQL 準拠の共通テーブル式（CTE）をサポートしており、SQL 文の可読性と実行効率を向上させます。詳細については、「共通テーブル式（CTE）」をご参照ください。

1 行のデータを複数行に分割するにはどうすればよいですか？

Lateral View を SPLIT や EXPLODE などのテーブル生成関数と共に使用して、1 行のデータを複数行のデータに分割し、分割されたデータを集計できます。

クライアントの odps_config.ini ファイルで use_instance_tunnel=false および instance_tunnel_max_record=10 を指定した後も、SELECT 文で多数の出力レコードが生成されます。なぜでしょうか？

instance_tunnel_max_record を使用して出力レコードの数を制御するには、use_instance_tunnel=false を use_instance_tunnel=true に変更する必要があります。

正規表現を使用して、フィールドの値が中国語であるかどうかを判断するにはどうすればよいですか？

次の文は例を示しています。

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	値
1	a
1	b
2	c

id	値
1	A
1	B
3	D