MaxComputeのオプティマイザの情報を収集する - MaxCompute

MaxComputeは、コストベースのオプティマイザを使用して、行数や文字列の平均長などのメタデータに基づいて、各実行プランのコストを正確に推定します。このトピックでは、オプティマイザを使用してクエリのパフォーマンスを最適化できるように、オプティマイザのメタデータを収集する方法について説明します。

背景情報

オプティマイザが不正確なメタデータに基づいてコストを推定すると、推定結果は不正確になり、不良な実行計画が生成されます。したがって、正確なメタデータはオプティマイザにとって非常に重要です。テーブルのコアメタデータは、テーブル内のデータの列統計メトリックです。他のメタデータは、列統計メトリックに基づいて推定されます。

MaxComputeでは、次の方法を使用して列統計メトリックを収集できます。

Analyze: 非同期収集メソッド。 analyzeコマンドを実行して、列統計メトリックを非同期に収集できます。アクティブな収集が必要です。
説明
MaxComputeクライアントのバージョンは0.35以降である必要があります。
Freeride: 同期収集メソッド。データがテーブルに生成されている間、データの列統計メトリックは自動的に収集されます。この方法は自動化されていますが、クエリの待ち時間に影響します。

次の表に、さまざまなデータ型で収集できる統計メトリックの列を示します。

列統計メトリック /データタイプ	数値 (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
nNull (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: 出力レコード数の見積もり。	データ型のみが指定されている場合、オプティマイザにとって値の範囲が大きすぎます。最小および最大メトリックの値が提供される場合、オプティマイザは、フィルタ条件の選択をより正確に推定し、より良い実行計画を提供することができる。
min (最小値) またはmax (最大値)	パフォーマンス最適化の精度を高めます。	シナリオ2: フィルター条件をストレージ層にプッシュダウンして、読み取る必要のあるデータ量を減らします。	MaxComputeでは、フィルター条件`a < -90`をストレージ層にプッシュダウンできますが、フィルター条件`a + 100 < 10`はプッシュダウンできません。 aのオーバーフローを考慮すると、2つのフィルタ条件は同等ではありません。しかしながら、α が最大値を有する場合、フィルタ条件は同等であり、互いに変換することができる。したがって、最小および最大メトリックは、より多くのフィルタ条件を押し下げることを可能にすることができる。これにより、読み取る必要のあるデータの量が減り、コストが削減されます。
nNull (null値の数)	null値チェックの効率を向上させます。	シナリオ1: ジョブの実行時にnull値のチェックを減らします。	ジョブを実行するときは、すべての種類のデータに対してnull値をチェックする必要があります。 nNullsメトリックの値が0の場合、チェックロジックは無視できます。これにより、コンピューティング性能が向上する。
nNull (null値の数)	null値チェックの効率を向上させます。	シナリオ2: フィルター条件に基づいてデータを除外します。	列にnull値のみが含まれる場合、オプティマイザーは`常にfalse`のフィルター条件を使用して、列全体のデータを除外します。これは、データフィルタリングの効率を改善する。
avgColLen (平均コラムの長さ) またはmaxColLen (最大コラムの長さ)	シャッフル操作を減らすためのリソース消費量を見積もります。	シナリオ1: ハッシュクラスタ化テーブルのメモリを推定します。	たとえば、オプティマイザは、データレコードのメモリ使用量を取得するために、avgColLenメトリックに基づいて可変長フィールドのメモリ使用量を推定できます。このように、オプティマイザは自動マップ結合操作を選択的に実行できる。ハッシュクラスタ化テーブルに対してブロードキャスト結合メカニズムが確立され、シャッフル操作が低減される。大きな入力テーブルの場合、シャッフル操作を減らしてパフォーマンスを大幅に向上させることができます。
avgColLen (平均コラムの長さ) またはmaxColLen (最大コラムの長さ)	シャッフル操作を減らすためのリソース消費量を見積もります。	シナリオ2: シャッフルする必要があるデータの量を減らします。	なし
ndv (異なる値の数)	実行計画の品質を向上させます。	シナリオ1: 結合操作の出力レコード数を見積もります。	データの拡張: 両方のテーブルの結合キーのndvメトリックの値が行数よりもはるかに小さい場合、多数のデータレコードが重複しています。この場合、データの拡張が発生した可能性があります。オプティマイザは、データ拡張によって引き起こされる問題を防ぐために関連する対策を講じることができます。データフィルタリング: 小さなテーブルのndvが大きなテーブルのndvよりもはるかに小さい場合、大きなテーブル内の大量のデータは結合操作後に除外されます。オプティマイザは、比較結果に基づいて関連する最適化決定を行うことができる。
ndv (異なる値の数)	実行計画の品質を向上させます。	シナリオ2: 結合操作をソートします。	オプティマイザは、出力レコードの推定数に基づいて結合シーケンスを自動的に調整できます。たとえば、データフィルタリングを伴う結合演算を前方に移動させ、データ拡張を伴う結合演算を後方に移動させることができる。
topK (発生頻度が最も高い上位K値)	データ分布を推定して、データスキューがパフォーマンスに与える影響を減らします。	シナリオ1: 歪んだデータを含む結合操作を最適化する。	結合操作の両方のテーブルに大きな入力があり、マップ結合操作を使用して小さなテーブルをメモリに完全にロードできない場合、データスキューが発生します。 1つの結合キーの出力は、他の結合キーの出力よりもはるかに大きい。 MaxComputeでは、マップ結合操作を自動的に使用して歪んだデータを処理し、マージ結合操作を使用して歪んでいないデータを処理し、計算結果をマージできます。この特徴は、大量のデータを含む結合操作に対して特に有効である。手動トラブルシューティングのコストを大幅に削減します。
topK (発生頻度が最も高い上位K値)	データ分布を推定して、データスキューがパフォーマンスに与える影響を減らします。	シナリオ2: 出力レコード数を见积もる。	ndv、min、およびmaxメトリックは、データが均等に分散されているという仮定が真である場合にのみ、出力レコードの数を正確に推定するために使用できます。データが明らかに歪んでいる場合、この仮定に基づく推定は歪められる。したがって、スキューされたデータには特別な処理が必要です。他のデータは、仮定に基づいて推定することができる。

分析の使用

Collect the column stats metrics

このセクションでは、パーティションテーブルと非パーティションテーブルを例として使用して、分析の使用方法を説明します。

非パーティションテーブル

Analyzeを使用して、パーティション分割されていないテーブルの1つ以上の特定の列またはすべての列の列統計メトリックを収集できます。

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コマンドを実行して、テーブル内の1つ以上の特定の列またはすべての列の列統計メトリックを収集します。例：

-- Collect the column stats metrics of the tinyint1 column. 
analyze table analyze2_test compute statistics for columns (tinyint1); 

-- Collect the column stats metrics of the smallint1, string1, boolean1, and timestamp1 columns. 
analyze table analyze2_test compute statistics for columns (smallint1, string1, boolean1, timestamp1);

-- Collect the column stats metrics of all columns. 
analyze table analyze2_test compute statistics for columns;

show statisticコマンドを実行して、収集結果をテストします。例：

-- Test the collection result of the tinyint1 column. 
show statistic analyze2_test columns (tinyint1);

-- Test the collection results of the smallint1, string1, boolean1, and timestamp1 columns. 
show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1);

-- Test the collection results of all columns. 
show statistic analyze2_test columns;

次の応答が返されます。

-- Collection result of the tinyint1 column: 
ID = 20201126085225150gnqo****
tinyint1:MaxValue:      20                   -- The value of max. 
tinyint1:DistinctNum:   4.0                  -- The value of ndv. 
tinyint1:MinValue:      1                    -- The value of min. 
tinyint1:NullNum:       1.0                  -- The value of nNulls. 
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}      -- The value of topK. 10=1.0 indicates that the occurrence frequency of column value 10 is 1. Up to 20 values with the highest occurrence frequency can be returned. 

-- Collection results of the smallint1, string1, boolean1, and timestamp1 columns: 
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                  -- The value of maxColLen. 
string1:AvgLength:      3.0                  -- The value of 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}

-- Collection results of all columns: 
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という名前のパーティションテーブルを作成します。
```
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のさまざまなタスクが、テーブル内のレコード数に影響する場合があります。ほとんどのタスクは、タスク自体の影響を受けるレコードの数だけを収集します。タスクによって影響を受けるレコードの数に関する統計は、分散タスクの動的な性質およびデータ更新時間の不確実性のために、正確でない可能性がある。したがって、[分析] コマンドを実行して、メタデータ内のテーブル内のレコード数の統計を更新し、レコード数の正確性を確保できます。 DataWorksのDataMapで、テーブル内のレコード数を表示できます。詳細については、「テーブルの詳細の表示」をご参照ください。

テーブル内のレコード数を更新します。
```
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パラメーターは、テーブルの名前を指定します。 colume_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パラメーターは、パーティションを指定します。 colume_nameパラメーターは、列の名前を指定します。

フリーライドの使用

Freerideを使用するには、次のコマンドをセッションレベルで同時に実行してプロパティを設定する必要があります。

set odps.optimizer.stat.collect.auto=true;: フリーライドを有効にして、テーブルの列統計メトリックを自動的に収集します。

set odps.optimizer.stat.collect.plan=xx;: 特定の列の特定の列統計メトリックを収集するようにコレクションプランを設定します。

-- Collect the avgColLen metric of the key column in the target_table table. 
set odps.optimizer.stat.collect.plan={"target_table":"{\"key\":\"AVG_COL_LEN\"}"}

-- Collect the min and max metrics of the s_binary column in the target_table table, and the topK and nNulls metrics of the s_int column in the table. 
set odps.optimizer.stat.collect.plan={"target_table":"{\"s_binary\":\"MIN,MAX\",\"s_int\":\"TOPK,NULLS\"}"};

説明

コマンドの実行後にデータが収集されない場合、Freerideが有効にならない可能性があります。 odps.optimizer.stat.collect.autoプロパティがLogViewのJson Summaryタブにあるかどうかを確認する必要があります。このプロパティが見つからない場合、現在のサーバーバージョンはFreerideをサポートしていません。サーバーは、今後、Freerideをサポートするバージョンにアップグレードされる予定です。

set odps.optimizer.stat.collect.plan=xx; コマンド内の列統計メトリックとパラメータ間のマッピング:

分: min
max: MAX
nNull: NULLS
avgColLen: AVG_COL_LEN
maxColLen: MAX_COL_LEN
ndv: NDV
topK: TOPK

MaxComputeでは、CREATE TABLE、INSERT INTO、またはINSERT OVERWRITEステートメントを実行して、Freerideをトリガーして列統計メトリックを収集できます。

Freerideを使用する前に、まずソーステーブルを準備する必要があります。たとえば、次のコマンドを実行して、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: ターゲットという名前のターゲットテーブルを作成するときに、フリーライドを使用して列統計メトリックを収集します。例：

-- Create a destination 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;
-- Test the collection results. 
show statistic target_test columns;

次の応答が返されます。

key:AvgLength: 3.0
key:NullNum:  0.0

INSERT INTO: INSERT INTOステートメントを実行してデータをテーブルに追加するときに、フリーライドを使用して列統計メトリックを収集します。例：

-- Create a destination table. 
create table freeride_insert_into_table like src_test;
-- Append data to the table. 
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 order by key, value limit 10;
-- Test the collection results. 
show statistic freeride_insert_into_table columns;

INSERT OVERWRITE: INSERT OVERWRITEステートメントを実行してテーブル内のデータを上書きするときに、Freerideを使用して列統計メトリックを収集します。例：

-- Create a destination table. 
create table freeride_insert_overwrite_table like src_test;
-- Overwrite data in the table. 
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;
-- Test the collection results. 
show statistic freeride_insert_overwrite_table columns;