MaxCompute で大量データのクエリと分析を実行するには、MaxCompute SQL を使用します。 SQL の主な機能は、次のとおりです。
  • さまざまな演算子を使用できます。
  • テーブル、パーティション、ビューの管理には、DDL 文を使用します。
  • テーブル内のレコード照会には、Select 文を使用し、テーブル内のレコードのフィルタ処理には、Where 文を使用します。
  • データの挿入と更新には、Insert 文を使用します。
  • 2 つのテーブルの結合には、Join 演算子を使用します。 複数の小さいテーブルの場合、Mapjoin 演算子を使用します。
  • コンピューティングには、組み込み関数とユーザー定義関数を使用できます。
  • 正規表現に対応しています。
ここでは、MaxCompute SQL を使用する際に注意すべき点について、簡単に説明します。
  • MaxCompute SQL は、トランザクション、インデックス、更新と削除操作などに対応していません。 また、MaxCompute の SQL 構文は、Oracle や MySQL と異なり、他のデータベースの SQL 文から MaxCompute にシームレスに移行できません。
  • MaxCompute ジョブの送信後、ジョブがスケジュールされるまで数秒から数分かかります。 このため、MaxCompute は、大量データを処理するバッチジョブに適していますが、 1 秒あたり数千または数万のトランザクションを処理する必要のあるフロントエンドビジネスシステムには適しません。
  • SQL 操作の詳細は、「SQL」をご参照ください。

DDL 文

単純な DDL 操作には、テーブルの作成、パーティションの追加、テーブルとパーティションの情報の表示、テーブルの変更、テーブルとパーティションの削除などがあります。

Select 文

  • GROUP BY 文のキーは、入力テーブルの列名にすることも、入力テーブルの列で構成した式にすることもできますが、SELECT 文の出力列にはできません。
    select substr(col2, 2) from tbl group by substr(col2, 2); -- Yes, the key of ‘group by’ can be the expression consisted of input table column;
     select col2 from tbl group by substr(col2, 2); -- No, the key of ‘group by’ is not in the column of Select statement;
     select substr(col2, 2) as c from tbl group by c; -- No, the key of ‘group by’ cannot be the column alias, i.e., the output column of Select statement;

    SQL 解析では、GROUP BY は、SELECT の前に実行されるため、GROUP BY 文では、入力テーブルの列または式のみをキーとして使用できます。

  • ORDER BY は LIMIT と組み合わせて使用する必要があります。
  • DISTRIBUTE BY は SORT BY の前に追加する必要があります。
  • ORDER BY、SORT BY、DISTRIBUTE BY のキーは SELECT 文の出力列、つまり列の別名でなければなりません。 以下に例を示します。
    select col2 as c from tbl order by col1 limit 100 -- No, the key of ‘order by’ is not the output column (column alias) of Select statement.
      select col2 from tbl order by col2 limit 100; -- Yes, use column name as the alases if the output column of Select statement has no alias.

    SQL 解析では、ORDER BY、SORT BY、DISTRIBUTE BY が SELECT の後に実行されます。 したがって、キーとして使用できるのは SELECT 文の出力列だけです。

Insert 文

  • 指定したパーティションにデータを挿入する場合、SELECT リストのパーティション列は使用できません。
    insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')  
        select shop_name, customer_id, total_price, sale_date, region from sale_detail;
      --  Return error; sale_date and region are partition columns, which are not allowed in Select statement in static partition.
  • 動的パーティションを挿入する場合、動的パーティション列が SELECT リストになければなりません。
    insert overwrite table sale_detail_dypart partition (sale_date='2013', region) 
    select shop_name,customer_id,total_price from sale_detail; 
    --Failed, to insert the dynamic partition, the dynamic partition column must be in Select list.

Join 文

  • MaxCompute SQL で使用できる JOIN 操作のタイプは、次のとおりです。LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、INNER JOIN
  • MaxCompute SQL は、最大 16 の同時 JOIN 操作に対応します。
  • MaxCompute は、8 つまでの小さなテーブルの MAPJOIN に対応します。

Union All 文

Union All では、複数の Select 操作から返された結果を 1 つのデータセットに結合できます。 重複除外は行われず、すべての結果が返されます。 MaxCompute では、2 つのメインクエリ結果は結合できませんが、2 つのサブクエリ結果は結合できます。
  • Union All や Union 操作によって結合する 2 つのクエリは、列数と列タイプが同じでなくてはなりません (列タイプが一致しない場合は、暗黙的型変換で、列タイプを一致させる必要があります)。
  • 通常、MaxCompute では、Union All や Union を使用して最大 256 のテーブルを結合できます。 この制限を超えると、構文エラーが発生します。

追加情報

  • MaxCompute SQL は、最大 128 の同時結合操作に対応します。
  • MaxCompute は、最大 128 の同時挿入操作と置換操作に対応します。

MaxCompute SQL に関する制限事項は、「SQL 制限事項の概要 (SQL Restrictions Summary)」をご参照ください。

SQL 最適化の例

  • Join 文の Where 条件
    2 つのテーブルを結合する場合、プライマリテーブルの Where 条件は、文の最後に記述できますが、セカンダリテーブルのパーティション制限は Where 条件に記述できません。 ON 条件やサブクエリに記述することを推奨します。 プライマリテーブルのパーティション制限は、Where 条件に記述できます (最初にサブクエリでフィルタ処理することを推奨します)。 以下に SQL 例を示します。
    select * from A join (select * from B where dt=20150301)B on B.id=A.id where A.dt=20150301; 
    select * from A join B on B.id=A.id where B.dt=20150301; --Not allowed. 
    select * from (select * from A where dt=20150301)A join (select * from B where dt=20150301)B on B.id=A.id;

    2 番目の文では Join 操作が最初に実行されるため、データ量が大きくなり、パフォーマンスが低下する可能性があります。 このため、2 番目の文は使わないようにする必要があります。

  • データスキュー

    データスキューの根本的な原因は、一部の Worker で処理されるデータ量が、他の Worker で処理されるデータ量より格段に多いことです。 つまり、一部の Worker の実行時間が平均を上回ると、ジョブの遅延につながります。

    .

    • Join を原因とするデータスキュー
      Join キーの分散が不均等な場合、Join 操作によりデータスキューが発生することがあります。 前述の例の場合、データ量の大きいテーブル A とデータ量の小さいテーブル B を結合するには、次の文を実行します。
      select * from A join B on A.value= B.value;
      Web コンソールページに移動するには、logview リンクをコピーして、Join 操作が実行されている Fuxi ジョブをダブルクリックします。 下図のように [Long-Tails] タブでロングテールを確認できます。これはデータスキューが発生していることを示します。
      次の方法で、SQL 文を最適化できます。
      • テーブル B はデータ量が小さく 512 MB を超えないので、前述した文を最適化するには mapjoin 文を使用します。
        select /*+ MAPJOIN(B) */ * from A join B on A.value= B.value;
      • スキューが発生したキーを別のロジックで処理します。 たとえば、両方のテーブルに null のキー値が多数ある場合、データスキューの原因になることがよくあります。null データのフィルタ処理を実行するか、次の例のように Join 操作の前に乱数を追加する必要があります。
        select * from A join B
        on case when A.value is null then concat('value',rand() ) else A.value end = B.value;
    データスキューが発生していることを把握していても原因が分からない場合、データスキューをテストして解決する方法が一般的です。 次の例をご参照ください。
    
    select * from a join b on a.key=b.key; --This Leads to data skew. 
    Now you can run the following statements: 
    ```sql
    select left.key, left.cnt * right.cnt from 
    (select key, count(*) as cnt from a group by key) left 
    join
    (select key, count(*) as cnt from b group by key) right
    on left.key=right.key;

    テーブル A が B に結合する際にデータスキューが発生しているかどうかを把握するには、キーの分散を確認します。

  • Group by を原因とするデータスキュー

    Group by のキー分散が不均等な場合、 Group by によりデータスキューが発生することがあります。

    テーブル A には、2 つのフィールド (key と value) があるとします。 データ量が多く、キー値の分散が不均等です。 次の SQL 文を実行します。
    select key,count(value) from A group by key;

    Web コンソールページでロングテールを確認できます。 この問題を解決するには、SQL 文の実行前にスキュー対策パラメーターを設定する必要があります。set odps.sql.groupby.skewindata =true を SQL 文に追加します。

  • 動的パーティションの誤使用を原因とするデータスキュー
    MaxCompute SQL の動的パーティションには、デフォルトで Reduce 関数が追加されています。Reduce 関数は、同じパーティションデータのマージに使用されます。 Reduce 関数の利点は、次のとおりです。
    • MaxCompute で生成される小さなファイルが減り、処理効率が向上します。
    • Worker で多くのファイルを出力する際の占有メモリが減ります。
    パーティションデータのスキューが発生している場合、Reduce 関数を使用すると、ロングテールを引き起こします。 同一データを処理できる Worker 数は最大 10 なので、データ量が多いと、ロングテールの原因になります。以下に例を示します。
    
    insert overwrite table A2 partition(dt) 
    select
    split_part(value,'\t',1) as field1,
    split_part(value,'\t',2) as field2, 
    dt 
    from A 
    where dt='20151010';
    この場合、動的パーティションは使用せず、SQL 文を次のように変更することを推奨します。
    
    insert overwrite table A2 partition(dt='20151010') 
    select
    split_part(value,'\t',1) as field1,
    split_part(value,'\t',2) as field2
    from A 
    where dt='20151010';
  • Window 関数の最適化
    SQL 文で Window 関数を使用すると、通常、各 Window 関数で Reduce ジョブが作成されます。 Window 関数が多すぎると、リソースが消費されます。 特定のシナリオでは、Window 関数を最適化できます。
    • over キーワードの後ろの内容は、グループ化条件やソート条件と同じにする必要があります。
    • 複数の Window 関数は、同じ SQL レイヤーで実行しなければなりません。
    上記 2 つの条件を満たす Window 関数は Reduce にマージされています。 以下に SQL 文の例を示します。
    
    select
    rank()over(partition by A order by B desc) as rank,
    row_number()over(partition by A order by B desc) as row_num
    from MyTable;
  • サブクエリを Join 文に変換
    以下にサブクエリの例を示します。
    SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx);
    table_b サブクエリによって返される col1 の数が1,000を超えると、次のエラーメッセージが表示されます。rrecords returned from subquery exceeded limit of 1, 000. この場合、次の Join 文で代用できます。
    SELECT a. * FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1)
    • SQL 文に Distinct キーワードがなく、サブクエリ c の結果が、同じ col1 値を返す場合、table_a の結果数が増える原因になることがあります。
    • Distinct サブクエリは、クエリ全体を 1 つの Worker に分類する可能性があります。 サブクエリのデータ量が多いと、クエリ全体の速度が低下する可能性があります。サブクエリの col1 値がビジネスとは異なることを確認済みの場合 (プライマリキーフィールドのクエリなど)、パフォーマンスを向上させるには、Distinct キーワードを削除するしかありません。
    • サブクエリの col1 値がビジネスとは異なることを確認済みの場合 (プライマリキーフィールドのクエリなど)、パフォーマンスを向上させるには、Distinct キーワードを削除するしかありません。