MaxCompute のオプティマイザーはコストベースです。行数や平均文字列長などのメタデータを使用して、コストを正確に見積もります。このトピックでは、MaxCompute でメタデータを収集してクエリパフォーマンスを最適化する方法について説明します。
背景情報
正確なメタデータがないと、オプティマイザーはコストを誤って計算し、非効率的な実行計画を生成する可能性があります。そのため、メタデータはオプティマイザーにとって不可欠です。テーブルのメタデータは、主に列統計情報 (Column stats) で構成されます。これらの統計情報は、他のメタデータを計算するために使用されます。
MaxCompute は、統計情報を収集するために 2 つの方法を提供しています。
非同期収集フレームワーク (Analyze) :
analyzeコマンドを使用して、非同期に統計情報を収集できます。この方法は手動で開始する必要があります。説明MaxCompute クライアント のバージョンは 0.35 以降である必要があります。
同期収集フレームワーク (Freeride) : この方法は、データが生成されると自動的に列統計情報を収集します。より自動化されていますが、クエリレイテンシーに影響を与える可能性があります。
MaxCompute は、さまざまなデータ型に対して以下の列統計情報メトリックを収集します。
列統計情報メトリック / データ型 | 数値型 (TINYINT、SMALLINT、INT、BIGINT、DOUBLE、DECIMAL、NUMERIC) | 文字型 (STRING、VARCHAR、CHAR) | バイナリ型 (BINARY) | ブール型 (BOOLEAN) | 日付型 (TIMESTAMP、DATE、INTERVAL) | 複合型 (MAP、STRUCT、ARRAY) |
min (最小値) | Y | N | N | N | Y | N |
max (最大値) | Y | N | N | N | Y | N |
nNulls (NULL 値の数) | Y | Y | Y | Y | Y | Y |
avgColLen (平均列長) | N | Y | Y | N | N | N |
maxColLen (最大列長) | N | Y | Y | N | N | N |
ndv (個別値の数) | Y | Y | Y | Y | Y | N |
topK (上位 K 個の最頻値) | Y | Y | Y | Y | Y | N |
Y はメトリックがサポートされていることを示し、N はメトリックがサポートされていないことを示します。
シナリオ
次の表に、各列統計情報メトリックのシナリオを示します。
列統計情報メトリック | 機能 | シナリオ | 説明 |
min (最小値) または max (最大値) | 最小値または最大値を取得して、最適化の精度を向上させます。 | シナリオ 1:出力レコード数の推定 | データ型しか分かっていない場合、値の範囲は大きくなります。最小値と最大値が分かっている場合、オプティマイザーはフィルター条件の選択性をより正確に推定し、より良い実行計画を生成できます。 |
シナリオ 2:読み取るデータ量を減らすための、ストレージレイヤーへのフィルター条件のプッシュダウン | MaxCompute では、 | ||
nNulls (NULL 値の数) | NULL 値に関する情報を使用して、評価効率を向上させます。 | シナリオ 1:ジョブ実行時の NULL チェックの削減 | ジョブの実行時、あらゆるデータ型に対して NULL 値をチェックする必要があります。nNulls が 0 であることがわかっている場合、このチェックをスキップして計算パフォーマンスを向上させることができます。 |
シナリオ 2:フィルター条件に基づくデータのクリッピング | 列のすべての値が NULL の場合、一般的なフィルター条件は | ||
avgColLen (平均列長) または maxColLen (最大列長) | 列長情報を取得してリソース消費量を見積もり、シャッフルを削減します。 | シナリオ 1:ハッシュクラスター化テーブルのメモリの見積もり | たとえば、avgColLen を使用して可変長フィールドのメモリ消費量、ひいてはレコードのメモリ消費量を見積もることができます。これにより、オプティマイザーは選択的に Auto MapJoin を実行できます。Auto MapJoin はハッシュクラスター化テーブルを構築し、ブロードキャストメカニズムを使用してシャッフル操作を排除します。入力テーブルが大きいシナリオでは、シャッフルを排除することでパフォーマンスが大幅に向上します。 |
シナリオ 2:シャッフルされるデータ量の削減 | なし。 | ||
ndv (個別値の数) | カーディナリティ情報を使用して、実行計画の品質を向上させます。 | シナリオ 1:JOIN の出力レコード数の推定 |
|
シナリオ 2:JOIN の順序 | 推定された出力レコード数に基づいて、オプティマイザーは JOIN の順序を自動的に調整することもできます。たとえば、データをフィルター処理する JOIN 操作を前の段階に移動し、データ肥大化を引き起こす JOIN 操作を後の段階に移動することができます。 | ||
topK (上位 K 個の最頻値) | データ分布を推定して、データスキューによるパフォーマンスへの影響を軽減します。 | シナリオ 1:スキューデータに対する JOIN 操作の最適化 | JOIN の入力が両方とも大きく、小さいテーブルを MapJoin のためにメモリに完全にロードできない場合、1 つのデータストリームがスキューしている一方で、他のストリームはスキューしていない可能性があります。MaxCompute は、スキューデータには MapJoin を使用し、非スキューデータには MergeJoin を使用するように操作を自動的に変換できます。その後、両方の結果がマージされます。この機能は、大規模なJOINにおいて大きなメリットをもたらし、障害後の手動作業を削減します。 |
シナリオ 2:出力レコード数の推定 | ndv、min、max を使用して出力レコード数を推定する場合、データが均等に分布していることを前提としています。データが著しくスキューしている場合、この前提は不正確な推定につながります。スキューデータは特殊なケースとして処理され、残りのデータには均等分布の前提が適用されます。 |
Analyze の使用手順
列統計情報の収集
このセクションでは、パーティションテーブルと非パーティション化テーブルで Analyze を使用する方法について説明します。
非パーティション化テーブル
特定の列またはすべての列の列統計情報を収集できます。
MaxCompute クライアントを使用して、analyze2_test という名前の非パーティション化テーブルを作成します。コマンドの例を次に示します。
create table if not exists analyze2_test (tinyint1 tinyint, smallint1 smallint, int1 int, bigint1 bigint, double1 double, decimal1 decimal, decimal2 decimal(20,10), string1 string, varchar1 varchar(10), boolean1 boolean, timestamp1 timestamp, datetime1 datetime ) lifecycle 30;テーブルにデータを挿入します。コマンドの例を次に示します。
insert overwrite table analyze2_test select * from values (1Y, 20S, 4, 8L, 123452.3, 12.4, 52.5, 'str1', 'str21', false, timestamp '2018-09-17 00:00:00', datetime '2018-09-17 00:59:59') ,(10Y, 2S, 7, 11111118L, 67892.3, 22.4, 42.5, 'str12', 'str200', true, timestamp '2018-09-17 00:00:00', datetime '2018-09-16 00:59:59') ,(20Y, 7S, 4, 2222228L, 12.3, 2.4, 2.57, 'str123', 'str2', false, timestamp '2018-09-18 00:00:00', datetime '2018-09-17 00:59:59') ,(null, null, null, null, null, null, null, null, null, null, null , null) as t(tinyint1, smallint1, int1, bigint1, double1, decimal1, decimal2, string1, varchar1, boolean1, timestamp1, datetime1);analyzeコマンドを実行して、単一の列、複数の列、またはすべての列の列統計情報を収集します。コマンドの例を次に示します。-- tinyint1 列の列統計情報を収集します。 analyze table analyze2_test compute statistics for columns (tinyint1); -- smallint1、string1、boolean1、timestamp1 列の列統計情報を収集します。 analyze table analyze2_test compute statistics for columns (smallint1, string1, boolean1, timestamp1); -- すべての列の列統計情報を収集します。 analyze table analyze2_test compute statistics for columns;show statisticコマンドを実行して、収集された列統計情報を確認します。コマンドの例を次に示します。-- tinyint1 列で収集された列統計情報を確認します。 show statistic analyze2_test columns (tinyint1); -- smallint1、string1、boolean1、timestamp1 列で収集された列統計情報を確認します。 show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1); -- すべての列で収集された列統計情報を確認します。 show statistic analyze2_test columns;次の結果が返されます。
-- tinyint1 列で収集された列統計情報。 ID = 20201126085225150gnqo**** tinyint1:MaxValue: 20 -- max に対応します。 tinyint1:DistinctNum: 4.0 -- ndv に対応します。 tinyint1:MinValue: 1 -- min に対応します。 tinyint1:NullNum: 1.0 -- nNulls に対応します。 tinyint1:TopK: {1=1.0, 10=1.0, 20=1.0} -- topK に対応します。10=1.0 は、値 10 が 1 回出現することを示します。topK は、出現頻度が上位 20 位までの値を表示します。 -- smallint1、string1、boolean1、timestamp1 列で収集された列統計情報。 ID = 20201126091636149gxgf**** smallint1:MaxValue: 20 smallint1:DistinctNum: 4.0 smallint1:MinValue: 2 smallint1:NullNum: 1.0 smallint1:TopK: {2=1.0, 7=1.0, 20=1.0} string1:MaxLength 6.0 -- maxColLen に対応します。 string1:AvgLength: 3.0 -- avgColLen に対応します。 string1:DistinctNum: 4.0 string1:NullNum: 1.0 string1:TopK: {str1=1.0, str12=1.0, str123=1.0} boolean1:DistinctNum: 3.0 boolean1:NullNum: 1.0 boolean1:TopK: {false=2.0, true=1.0} timestamp1:DistinctNum: 3.0 timestamp1:NullNum: 1.0 timestamp1:TopK: {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0} -- すべての列で収集された列統計情報。 ID = 20201126092022636gzm1**** tinyint1:MaxValue: 20 tinyint1:DistinctNum: 4.0 tinyint1:MinValue: 1 tinyint1:NullNum: 1.0 tinyint1:TopK: {1=1.0, 10=1.0, 20=1.0} smallint1:MaxValue: 20 smallint1:DistinctNum: 4.0 smallint1:MinValue: 2 smallint1:NullNum: 1.0 smallint1:TopK: {2=1.0, 7=1.0, 20=1.0} int1:MaxValue: 7 int1:DistinctNum: 3.0 int1:MinValue: 4 int1:NullNum: 1.0 int1:TopK: {4=2.0, 7=1.0} bigint1:MaxValue: 11111118 bigint1:DistinctNum: 4.0 bigint1:MinValue: 8 bigint1:NullNum: 1.0 bigint1:TopK: {8=1.0, 2222228=1.0, 11111118=1.0} double1:MaxValue: 123452.3 double1:DistinctNum: 4.0 double1:MinValue: 12.3 double1:NullNum: 1.0 double1:TopK: {12.3=1.0, 67892.3=1.0, 123452.3=1.0} decimal1:MaxValue: 22.4 decimal1:DistinctNum: 4.0 decimal1:MinValue: 2.4 decimal1:NullNum: 1.0 decimal1:TopK: {2.4=1.0, 12.4=1.0, 22.4=1.0} decimal2:MaxValue: 52.5 decimal2:DistinctNum: 4.0 decimal2:MinValue: 2.57 decimal2:NullNum: 1.0 decimal2:TopK: {2.57=1.0, 42.5=1.0, 52.5=1.0} string1:MaxLength 6.0 string1:AvgLength: 3.0 string1:DistinctNum: 4.0 string1:NullNum: 1.0 string1:TopK: {str1=1.0, str12=1.0, str123=1.0} varchar1:MaxLength 6.0 varchar1:AvgLength: 3.0 varchar1:DistinctNum: 4.0 varchar1:NullNum: 1.0 varchar1:TopK: {str2=1.0, str200=1.0, str21=1.0} boolean1:DistinctNum: 3.0 boolean1:NullNum: 1.0 boolean1:TopK: {false=2.0, true=1.0} timestamp1:DistinctNum: 3.0 timestamp1:NullNum: 1.0 timestamp1:TopK: {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0} datetime1:DistinctNum: 3.0 datetime1:NullNum: 1.0 datetime1:TopK: {1537117199000=2.0, 1537030799000=1.0}
パーティションテーブル
特定のパーティションの列統計情報を収集できます。
次の例は、MaxCompute クライアントを使用して srcpart_test という名前のパーティションテーブルを作成するコマンドを示しています。
create table if not exists srcpart_test (key string, value string) partitioned by (ds string, hr string) lifecycle 30;テーブルにデータを挿入します。コマンドの例を次に示します。
insert into table srcpart_test partition(ds='20201220', hr='11') values ('123', 'val_123'), ('76', 'val_76'), ('447', 'val_447'), ('1234', 'val_1234'); insert into table srcpart_test partition(ds='20201220', hr='12') values ('3', 'val_3'), ('12331', 'val_12331'), ('42', 'val_42'), ('12', 'val_12'); insert into table srcpart_test partition(ds='20201221', hr='11') values ('543', 'val_543'), ('2', 'val_2'), ('4', 'val_4'), ('9', 'val_9'); insert into table srcpart_test partition(ds='20201221', hr='12') values ('23', 'val_23'), ('56', 'val_56'), ('4111', 'val_4111'), ('12333', 'val_12333');analyzeコマンドを実行して、特定のパーティションの列統計情報を収集します。コマンドの例を次に示します。analyze table srcpart_test partition(ds='20201221') compute statistics for columns (key, value);show statisticコマンドを実行して、収集された列統計情報を確認します。コマンドの例を次に示します。show statistic srcpart_test partition (ds='20201221') columns (key, value);次の結果が返されます。
ID = 20210105121800689g28p**** (ds=20201221,hr=11) key:MaxLength 3.0 (ds=20201221,hr=11) key:AvgLength: 1.0 (ds=20201221,hr=11) key:DistinctNum: 4.0 (ds=20201221,hr=11) key:NullNum: 0.0 (ds=20201221,hr=11) key:TopK: {2=1.0, 4=1.0, 543=1.0, 9=1.0} (ds=20201221,hr=11) value:MaxLength 7.0 (ds=20201221,hr=11) value:AvgLength: 5.0 (ds=20201221,hr=11) value:DistinctNum: 4.0 (ds=20201221,hr=11) value:NullNum: 0.0 (ds=20201221,hr=11) value:TopK: {val_2=1.0, val_4=1.0, val_543=1.0, val_9=1.0} (ds=20201221,hr=12) key:MaxLength 5.0 (ds=20201221,hr=12) key:AvgLength: 3.0 (ds=20201221,hr=12) key:DistinctNum: 4.0 (ds=20201221,hr=12) key:NullNum: 0.0 (ds=20201221,hr=12) key:TopK: {12333=1.0, 23=1.0, 4111=1.0, 56=1.0} (ds=20201221,hr=12) value:MaxLength 9.0 (ds=20201221,hr=12) value:AvgLength: 7.0 (ds=20201221,hr=12) value:DistinctNum: 4.0 (ds=20201221,hr=12) value:NullNum: 0.0 (ds=20201221,hr=12) value:TopK: {val_12333=1.0, val_23=1.0, val_4111=1.0, val_56=1.0}
メタデータ内のテーブルのレコード数の更新
MaxCompute のさまざまなジョブは、テーブル内のレコード数を変更する可能性があります。しかし、これらのジョブは常にレコード数を正確に更新するとは限りません。これは、分散タスクの動的な性質とデータ更新のタイミングが原因となることがあります。レコード数を正確に保つために、analyze コマンドを使用してメタデータ内の値を更新できます。テーブルのレコード数は、DataWorks データマップで表示できます。詳細については、「テーブルの詳細表示」をご参照ください。
テーブル全体のレコード数を更新するには:
set odps.sql.analyze.table.stats=only; analyze table <table_name> compute statistics for columns;table_nameはテーブルの名前です。テーブル内の特定の列のレコード数を更新するには:
set odps.sql.analyze.table.stats=only; analyze table <table_name> compute statistics for columns (<column_name>);table_nameはテーブルの名前、column_nameは列の名前です。パーティション内の特定の列のレコード数を更新するには:
set odps.sql.analyze.table.stats=only; analyze table <table_name> partition(<pt_spec>) compute statistics for columns (<column_name>);table_nameはテーブルの名前、pt_specはパーティション値、column_nameは列の名前です。
Freeride の使用手順
セッションレベルで次の 2 つのコマンドを実行して、プロパティを設定できます。
set odps.optimizer.stat.collect.auto=true;:Freeride 機能を有効にして、テーブルの列統計情報を自動的に収集します。set odps.optimizer.stat.collect.plan=xx;:指定された列の特定の列統計情報メトリックを収集するための収集計画を設定します。-- target_table テーブルの key 列に対して avgColLen メトリックを収集します。 set odps.optimizer.stat.collect.plan={"target_table":"{\"key\":\"AVG_COL_LEN\"}"} -- target_table テーブルの s_binary 列に対して min と max メトリックを収集し、s_int 列に対して topK と nNulls メトリックを収集します。 set odps.optimizer.stat.collect.plan={"target_table":"{\"s_binary\":\"MIN,MAX\",\"s_int\":\"TOPK,NULLS\"}"};
これらのプロパティを設定した後に統計情報が収集されない場合、Freeride 機能が有効になっていない可能性があります。Logview の [json summary] タブで odps.optimizer.stat.collect.auto プロパティを確認できます。プロパティが見つからない場合、サーバーのバージョンが低すぎてこの機能をサポートしていません。Alibaba Cloud は、Freeride をサポートするバージョンに MaxCompute サーバーを段階的にアップグレードします。
次のリストは、列統計情報メトリックと set odps.optimizer.stat.collect.plan=xx; コマンド内の識別子との対応を示しています。
min:MIN
max:MAX
nNulls:NULLS
avgColLen:AVG_COL_LEN
maxColLen:MAX_COL_LEN
ndv:NDV
topK:TOPK
MaxCompute は、create table、insert into、または insert overwrite を使用するときに Freeride をトリガーして列統計情報を収集できます。
これらの3つの方法を説明するために、まず src_test という名前のソーステーブルを作成し、そこにデータを挿入します。コマンドの例を次に示します。
create table if not exists src_test (key string, value string);
insert overwrite table src_test values ('100', 'val_100'), ('100', 'val_50'), ('200', 'val_200'), ('200', 'val_300');create table:ターゲットテーブルの作成時に列統計情報が収集されます。コマンドの例を次に示します。-- ターゲットテーブルを作成します。 set odps.optimizer.stat.collect.auto=true; set odps.optimizer.stat.collect.plan={"target_test":"{\"key\":\"AVG_COL_LEN,NULLS\"}"}; create table target_test as select key, value from src_test; -- 収集された列統計情報を確認します。 show statistic target_test columns;次の結果が返されます。
key:AvgLength: 3.0 key:NullNum: 0.0insert into:insert intoを使用してデータを追加するときに列統計情報が収集されます。コマンドの例を次に示します。-- ターゲットテーブルを作成します。 create table freeride_insert_into_table like src_test; -- データを追加します。 set odps.optimizer.stat.collect.auto=true; set odps.optimizer.stat.collect.plan={"freeride_insert_into_table":"{\"key\":\"AVG_COL_LEN,NULLS\"}"}; insert into table freeride_insert_into_table select key, value from src_test order by key, value limit 10; -- 収集された列統計情報を確認します。 show statistic freeride_insert_into_table columns;insert overwrite:insert overwriteを使用してデータを上書きするときに列統計情報が収集されます。コマンドの例を次に示します。-- ターゲットテーブルを作成します。 create table freeride_insert_overwrite_table like src_test; -- データを上書きします。 set odps.optimizer.stat.collect.auto=true; set odps.optimizer.stat.collect.plan={"freeride_insert_overwrite_table":"{\"key\":\"AVG_COL_LEN,NULLS\"}"}; insert overwrite table freeride_insert_overwrite_table select key, value from src_test order by key, value limit 10; -- 収集された列統計情報を確認します。 show statistic freeride_insert_overwrite_table columns;