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)」をご参照ください。

• 解決策

  SELECT リストの列は、GROUP BY 句の列か、SUM や COUNT などの集計関数を適用した列のいずれかである必要があります。

テーブル A に対して GROUP BY を実行してテーブル B を生成した場合に、テーブル B の行数は少ないのに物理ストレージが 10 倍になる理由

MaxCompute は、ストレージに列指向圧縮を使用します。同じ列で連続する値が類似している場合、圧縮率は高くなります。odps.sql.groupby.skewindata=true を有効にすると、データが分散され、圧縮率が低下します。圧縮を改善するには、SQL 文でデータを書き込むときにローカルソートを実行します。

100 億のデータレコードに対するGROUP BY クエリの実行によるパフォーマンス影響と、GROUP BY のデータ量上限

いいえ。GROUP BY にはデータ量の制限はありません。

MaxCompute クエリで返されるデータのソート順

MaxCompute テーブルからのデータ読み取り順序は保証されません。ソート句がない場合、クエリ結果も順序付けされません。

データをソートするには、SQL 文に order by xx limit n 句を追加します。

フルソートを行うには、limit の値 n を レコードの総数 + 1 に設定します。

ORDER BY FIELD NULLS LAST 構文のサポート状況

MaxCompute はこの構文をサポートしています。詳細については、「Differences from other SQL syntaxes」をご参照ください。

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 句が必須です。

• 解決策

  シナリオで LIMIT 句のない ORDER BY が必要な場合は、次のいずれかの方法でこの要件を無効にしてください:

  • プロジェクトレベル： setproject odps.sql.validate.orderby.limit=false; コマンドを実行して、order by を limit 句と併用する必要があるという要件を無効にします。

  • セッションレベル: set odps.sql.validate.orderby.limit=false; コマンドを実行して、order by を limit 句と併用する必要があるという要件を無効にします。このコマンドは、SQL ステートメントと一緒に送信する必要があります。

    説明

    order by-limit 要件を無効にすると、単一の実行ノードで大規模なデータセットをソートすることになり、パフォーマンスが低下し、リソース消費量が増加します。

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

NOT IN サブクエリで返されるパーティション数が 1,000 に制限される場合の実装方法

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 error" の解決方法

• 症状

  select count(1) as co from client_table union all ... のように 200 個の UNION ALL 操作を含む SQL ステートメントを実行すると、以下のエラーが発生します。

  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 ステートメントの長さと直接比例せず、事前に見積もることはできません。

  • 原因 2：パーティション数が多すぎます。

  • 原因 3：小さいファイルが多すぎます。

• 解決策

  • 原因 1 の解決策：長い SQL ステートメントを分割し、長さ制限を超えないようにします。

  • 原因 2 の解決策：パーティション数を調整します。詳細については、「Partition」をご参照ください。

  • 原因 3 の解決策：小さいファイルをマージします。

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

• 原因

  • 原因 1: ON 句に table1.c1>table2.c3 のような非等価結合が指定されています。

  • 原因 2: table1.col1 = concat(table1.col2,table2.col3) のように、JOIN 条件の片側が両方のテーブルの列を参照しています。

• 解決策

  • 原因 1 の解決策：SQL ステートメントを修正します。結合条件は等価結合である必要があります。

    説明

    非等価結合を使用する必要がある場合は、MAPJOIN ヒントを追加できます。詳細については、「ODPS-0130071」をご参照ください。

  • 原因 2 の解決策：いずれかのテーブルが小さい場合は、「MAPJOIN」方式を使用できます。

JOIN 操作中に発生する "Maximum 16 join inputs allowed" エラーの解決方法

• 症状

  MaxCompute SQL ステートメントを実行すると、次のエラーが返されます：

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

• 原因

  MaxCompute SQL では、MAPJOIN 操作は最大 6 個の小テーブルをサポートし、単一の JOIN 操作は最大 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	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 に重複する ID が含まれているかどうかを確認します：

  select id, count(*) as cnt from table2 group by id having cnt>1 limit 10;

  デカルト積を回避するには、SQL ステートメントを次のように書き換えてください。

  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 句に指定する必要があります。この目的で ON 句を使用するのは非標準です。

  あるプロジェクトで実行が成功したのは、set odps.sql.outerjoin.supports.filters=false コマンドが設定されていたためです。このコマンドは ON 句の条件をフィルター条件に変換します。この動作は Hive 構文と互換性がありますが、SQL 標準には準拠していません。

• 解決策

  パーティションのフィルター条件を WHERE 句に配置してください。

JOIN 操作におけるパーティションプルーニング条件の指定箇所 (ON 句 / WHERE 句) と有効範囲

• パーティションプルーニング条件が WHERE 句にある場合、パーティションプルーニングは有効になります。

• 条件が ON 句にある場合、詳細テーブルではパーティションプルーニングが有効になりますが、主テーブルでは有効になりません。その結果、主テーブルに対してフルテーブルスキャンが実行されます。

パーティションプルーニングの詳細については、「Evaluate the validity of partition pruning」をご参照ください。

MAPJOIN を使用した複数の小テーブルのキャッシュ

MAPJOIN は、小テーブルをメモリにキャッシュすることでクエリを高速化します。MAPJOIN ヒントにテーブルのエイリアスを指定してください。

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

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

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

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

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
cross join 
(
  select count(*) as cnt from iris
) c;              

MAPJOIN における大テーブルと小テーブルの入れ替え可否

はい、可能です。システムはストレージサイズに基づいて大テーブルと小テーブルを判別し、小テーブルをメモリにロードして JOIN 操作を高速化します。

フィルター条件を設定しても入力データが 100 GB を超えると表示される場合の対処

最初にパーティションフィールドでデータをフィルタリングし、その後に他の非パーティションフィールドでフィルタリングしてください。入力データ量は、パーティションレベルのフィルタリング後に計算されます。

MaxCompute SQL のあいまい検索における WHERE 句での正規表現サポート

はい。たとえば、select * from user_info where address rlike '[0-9]{9}'; は、9 桁の数字を含むレコードを検索します。

WHERE のフィルター条件で LIMIT を使用して 100 件だけ同期する方法

フィルター条件では LIMIT はサポートされていません。まず SQL ステートメントで 100 件のレコードを選択してから、同期操作を実行してください。

クエリ効率の改善とパーティション設定の調整

テーブルをパーティションフィールドでパーティション分割すると、フルテーブルスキャンを行わずに特定のパーティションでデータの追加、更新、読み取りが可能になります。詳細については、「Table operations」をご参照ください。

WITH AS ステートメントのサポート状況

はい。MaxCompute は、可読性と実行効率を向上させるために、標準 SQL の共通テーブル式 (CTE) をサポートしています。詳細については、「COMMON TABLE EXPRESSION (CTE)」をご参照ください。

1 行のデータを複数行に分割する方法

LATERAL VIEW と Split や Explode などのテーブル生成関数を使用して 1 行を複数行に分割し、その後に生成されたデータを集計します。

クライアントの odps_config.ini ファイルで instance_tunnel_max_record が有効にならない理由

instance_tunnel_max_record 設定を有効にするには、use_instance_tunnel=false を use_instance_tunnel=true に変更します。

正規表現を使用してフィールドに中国語文字が含まれているかどうかを判定する方法

select 'field' rlike '[\\u4e00-\\u9fa5]+';