このトピックでは、MaxComputeでのデータクエリ言語 (DQL) 操作に関するよくある質問に対する回答を提供します。
MaxCompute SQLステートメントを実行したときに "Repeated key in GROUP BY" エラーメッセージが表示された場合はどうすればよいですか?
問題の説明
MaxCompute SQLステートメントを実行すると、次のエラーメッセージが表示されます。
FAILED: ODPS-0130071:Semantic analysis exception - Repeated key in GROUP BY.原因
SELECT DISTINCTの後に定数を付けることはできません。
解決策
SQL文の実行ロジックを2つのレイヤーに分割します。 このようにして、定数のない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文を変更して、SELECTを使用して照会される列が、GROUP by句で指定された列、またはSUMやCOUNTなどの集計関数を使用して処理される列になるようにします。
テーブルBの行数はテーブルAの行数よりも少ないが、テーブルBの物理記憶容量はテーブルAの物理記憶容量の10倍である。なぜ?
MaxComputeでは、データは列圧縮モードで保存されます。 隣接する列のデータが類似している場合、データ圧縮率は高い。 odps.sql.groupby.skewindataをtrueに設定すると、SQL文を実行してデータを書き込むときにデータが分散されます。 この場合、データ圧縮率は低い。 データの圧縮率を高くしたい場合は、SQL文を実行してデータを書き込むときに、特定のデータを並べ替えることができます。
GROUP BYを使用して10億のデータレコードをクエリすると、クエリのパフォーマンスに影響しますか。 GROUP BYを使用してデータをクエリする場合、データ量は制限されますか?
いいえ、GROUP BYを使用して10億のデータレコードをクエリする場合、クエリのパフォーマンスは影響を受けません。 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はORDER BY FIELD NULLS LASTをサポートしています。 MaxComputeでサポートされている構文の詳細については、「SQL文のサポートの違い」をご参照ください。
MaxCompute SQLステートメントを実行するときに "ORDER BYをLIMIT句とともに使用する必要があります" というエラーメッセージが表示された場合はどうすればよいですか?
問題の説明
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句は、単一ノードのすべてのデータをソートする必要があります。 既定では、ORDER By句はLIMIT句とともに使用され、単一のノードが大量のデータを処理しないようにします。
解決策
プロジェクトまたはセッションのORDER BY句とlimit句の同時実行の制限を解除できます。
プロジェクトの制限を解除するには、
setproject odps.sql.validate.orderby.limit=false;コマンドを実行します。セッションの制限を解除するには、実行するsql文を指定して
set odps. SQL. validate.orderby.limit=false;コマンドをコミットして実行します。説明制限を解除した後、1つのノードに大量のデータがある場合、大量のリソースと多くの時間が消費されます。
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);
Replace the preceding statement with the following statement:
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: SQL文が実行計画に変換された後、実行計画の長さが1024 KBを超えます。これは、基になるアーキテクチャで許可される最大サイズです。 その結果、SQL実行エラーが返されます。 実行計画の長さは、SQL文の長さとは直接関係しません。 したがって、実行計画の長さを推定することはできない。
原因2: パーティションの数が多いため、実行計画の長さが制限を超えています。
原因3: ファイルが小さすぎるため、SQL文の実行に失敗しました。
解決策
原因1の解決策: SQLステートメントが長すぎる場合は、ステートメントを複数のステートメントに分割することを推奨します。 これにより、生成された実行プランが最大長を超えないようにします。
原因2の解決策: 多数のパーティションが存在する場合は、パーティションの数を調整します。 詳細については、「パーティション」をご参照ください。
原因3の解決策: 過剰な小さなファイルが存在する場合、
小さなファイルをマージします。
JOIN操作を実行したときに「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原因
原因1: SQLステートメントのON条件には、
table1.c1>table2.c3などの非equi結合が含まれます。原因2: SQL文のJOIN条件の片側のデータは、
table1.col1 = concat(table1.col2,table2.col3)などの2つのテーブルから取得されます。
解決策
原因1の解決策: SQLステートメントのON条件の非equi結合をequi結合に変更します。
説明非equi結合を使用する必要がある場合は、SQL文にMAPJOINヒントを追加できます。 詳しくは、「ODPS-0130071」をご参照ください。
原因2の解決策: テーブルの1つが小さい場合は、MAPJOINメソッドを使用します。
join操作を実行したときに「最大16の結合入力が許可されています」というエラーメッセージが表示された場合はどうすればよいですか?
問題の説明
MaxCompute SQLステートメントを実行すると、次のエラーメッセージが表示されます。
FAILED: ODPS-0123065:Join exception - Maximum 16 join inputs allowed原因
MaxCompute SQL文は、最大6つの小さなテーブルに対してMAPJOINを実行でき、最大16のテーブルを連続して結合できます。
解決策
いくつかの小さなテーブルを入力テーブルとして一時テーブルに結合して、入力テーブルの数を減らします。
JOIN操作を実行した後、返されたデータレコードの数がいずれかのソーステーブルのデータレコードの数よりも大きい場合はどうすればよいですか?
問題の説明
次のMaxCompute SQL文を実行した後、返されるデータレコードの数はtable1テーブルのデータレコードの数よりも大きくなります。
select count(*) from table1 a left outer join table2 b on a.ID = b.ID;原因
上記のSQL文では、table1およびtable2のIDフィールドに対して左外部結合が実行されます。 したがって、次の状況が発生する可能性があります。
結合するデータがtable2に見つからない場合でも、table1はデータレコードを返します。
結合するデータがtable1にはないが、table2にはある場合、データは返されません。
結合するデータがtable1とtable2にある場合、結合ロジックは内部結合と同じです。 IDがtable1とtable2の両方に値を持つ場合、返される結果はtable1とtable2のデカルト積です。
次の表は、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のデータによるものかどうかを確認します。 次のステートメントは例を示しています。 このステートメントでは、table2に大量のデータがある場合にデータが画面にあふれるのを防ぐために、
limit 10が追加されています。 データレコードの数が増加した理由を判断するには、最初のいくつかのデータレコードを確認するだけです。select id, count() as cnt from table2 group by id having cnt>1 limit 10;重複データが存在するときにデカルト積演算を実行せず、SQLでINと同等の効果を実現する場合は、次のステートメントを使用できます。
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;次のエラーメッセージが表示されます。
Table(fddev,tmp_001) is full scan with all partitions,please specify partitions predicates.原因
SELECTステートメントでパーティションを指定するには、WHERE句を使用する必要があります。 SELECTステートメントのON条件がSQL標準に準拠していません。
set odps.sql.outerjoin.supports.filters=falseコマンドは、ステートメントが正常に実行されたプロジェクトで実行されます。 この構成は、ON句の条件をフィルタ条件に変換して、非標準SQL文を許可します。 この構成は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ステートメントの大きなテーブルと小さなテーブルは、各テーブルで使用されるスペースのサイズに基づいて区別されます。
システムは、指定された小さなテーブル内のすべてのデータを、JOIN操作を実行するプログラムのメモリにロードします。 これは、JOIN動作の実行を加速するのに役立つ。 MAPJOIN文で大きなテーブルと小さなテーブルを交換すると、エラーは返されませんが、処理パフォーマンスが低下します。
MaxCompute SQL文でフィルター条件を設定すると、入力データのサイズが100 GBを超えたことを示すエラーメッセージが表示されます。 どうすればよいですか。
データを取得する前にパーティションをフィルタリングします。 データを取得したら、パーティション以外のフィールドをフィルターします。 入力テーブルのサイズは、パーティションがフィルタリングされた後、およびパーティション以外のフィールドがフィルタリングされる前のテーブルのサイズによって異なります。
MaxCompute SQLのファジークエリのWHERE条件は正規表現をサポートしていますか?
はい。MaxCompute SQLのファジークエリのWHERE条件は、正規表現をサポートしています。 例えば、select * from user_info where address like '[0-9]{9}'; は、9桁からなるIDが検索されることを示す。
100データレコードのみを同期する場合、LIMITを使用してWHERE句で同期するデータレコードの数を指定するにはどうすればよいですか?
LIMITはWHERE句では使用できません。 データを同期する前に、SQL文を実行して100のデータレコードを読み取ることができます。
クエリの効率を向上させるにはどうすればよいですか? パーティションの設定を調整できますか?
パーティションフィールドを使用してテーブルをパーティション分割する場合、パーティションが追加されたとき、またはパーティションデータが更新または読み取られたときに、テーブル全体のスキャンはトリガーされません。 これにより、データ処理の効率が向上する。 詳細については、「テーブル操作」をご参照ください。
MaxCompute SQLはWITH ASステートメントをサポートしていますか?
はい、MaxCompute SQLはWITH ASステートメントをサポートしています。 MaxComputeは、SQLに準拠した共通テーブル式 (CTE) をサポートしており、SQL文の可読性と実行効率を向上させています。 詳細については、「共通テーブル式」をご参照ください。
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 'Field name' rlike '[\\x{4e00}-\\x{9fa5}]+';