履歴ベースの最適化を使用する
プレビュー版のサポートについては、bigquery-history-based-optimization-support@google.com までメールでお問い合わせください。
このガイドでは、クエリの履歴ベースの最適化の有効化、無効化、分析を行う方法について説明します。
履歴ベースの最適化について
履歴ベースの最適化では、類似するクエリで完了済みの実行情報に基づいて、追加の最適化を適用し、クエリのパフォーマンス（消費されるスロット時間やクエリ レイテンシなど）を改善します。たとえば、履歴ベースの最適化を適用すると、最初のクエリの実行に 60 秒かかる場合がありますが、履歴ベースの最適化が特定された後の 2 回目のクエリの実行にかかる時間は 30 秒程になります。このプロセスは、追加する最適化がなくなるまで続きます。
以下に、BigQuery で履歴ベースの最適化がどのように機能するかを示します。
| 実行回数 | クエリスロットの消費時間 | 注 |
|---|---|---|
| 1 | 60 | 元の実行。 |
| 2 | 30 | 最初に履歴に基づく最適化が適用されます。 |
| 3 | 20 | 2 回目の履歴ベースの最適化が適用されました。 |
| 4 | 21 | 適用する追加の履歴ベースの最適化はありません。 |
| 5 | 19 | 適用する追加の履歴ベースの最適化はありません。 |
| 6 | 20 | 適用する追加の履歴ベースの最適化はありません。 |
履歴ベースの最適化は、クエリのパフォーマンスに良い影響をもたらすことが高い確率であると判断された場合にのみ適用されます。また、最適化によってクエリのパフォーマンスが大幅に向上しない場合、その最適化は取り消され、今後そのクエリの実行では使用されません。
履歴ベースの最適化を有効にする
プロジェクトで履歴ベースの最適化を使用するには、ALTER PROJECT または ALTER ORGANIZATION ステートメントで default_query_optimizer_options = 'adaptive=on' パラメータを使用します。
例:
ALTER PROJECT `user_project` SET OPTIONS ( `region-us.default_query_optimizer_options` = 'adaptive=on' );
履歴ベースの最適化を無効にする
プロジェクトで履歴ベースの最適化を無効にするには、ALTER PROJECT または ALTER ORGANIZATION ステートメントで default_query_optimizer_options = 'adaptive=off' パラメータを使用します。
例:
ALTER PROJECT `user_project` SET OPTIONS ( `region-us.default_query_optimizer_options` = 'adaptive=off' );
ジョブの履歴ベースの最適化を確認する
ジョブの履歴ベースの最適化を確認するには、SQL クエリまたは REST API メソッド呼び出しを使用します。
SQL
クエリを使用して、ジョブの履歴ベースの最適化を取得できます。クエリには、INFORMATION_SCHEMA.JOBS_BY_PROJECT と query_info.optimization_details 列名を含める必要があります。
次の例では、sample_job というジョブの最適化の詳細が返されます。履歴ベースの最適化が適用されていない場合、optimization_details に対して NULL が生成されます。
SELECT
job_id,
query_info.optimization_details
FROM `project_name.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'sample_job'
LIMIT 1;
結果は次のようになります。
-- The JSON in optimization_details has been formatted for readability.
/*------------+-----------------------------------------------------------------*
| job_id | optimization_details |
+------------+-----------------------------------------------------------------+
| sample_job | { |
| | "optimizations": [ |
| | { |
| | "semi_join_reduction": "web_sales.web_date,RIGHT" |
| | }, |
| | { |
| | "semi_join_reduction": "catalog_sales.catalog_date,RIGHT" |
| | }, |
| | { |
| | "semi_join_reduction": "store_sales.store_date,RIGHT" |
| | }, |
| | { |
| | "join_commutation": "web_returns.web_item" |
| | }, |
| | { |
| | "parallelism_adjustment": "applied" |
| | }, |
| | ] |
| | } |
*------------+-----------------------------------------------------------------*/
API
ジョブの最適化の詳細を取得するには、jobs.get メソッドを呼び出します。
次の例では、jobs.get メソッドが最適化の詳細（optimizationDetails）を完全なレスポンスで返します。
{
"jobReference": {
"projectId": "myProject",
"jobId": "sample_job"
}
}
結果は次のようになります。
-- The unrelated parts in the full response have been removed.
{
"jobReference": {
"projectId": "myProject",
"jobId": "sample_job",
"location": "US"
},
"statistics": {
"query": {
"queryInfo": {
"optimizationDetails": {
"optimizations": [
{
"semi_join_reduction": "web_sales.web_date,RIGHT"
},
{
"semi_join_reduction": "catalog_sales.catalog_date,RIGHT"
},
{
"semi_join_reduction": "store_sales.store_date,RIGHT"
},
{
"join_commutation": "web_returns.web_item"
},
{
"parallelism_adjustment": "applied"
}
]
}
}
}
}
}
履歴ベースの最適化の影響を推定する
履歴ベースの最適化の影響を推定するには、次のサンプル SQL クエリを使用して、実行時間の推定される改善が最大になるプロジェクト クエリを特定します。
WITH
jobs AS (
SELECT
*,
query_info.query_hashes.normalized_literals AS query_hash,
TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS elapsed_ms,
IFNULL(
ARRAY_LENGTH(JSON_QUERY_ARRAY(query_info.optimization_details.optimizations)) > 0,
FALSE)
AS has_history_based_optimization,
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE EXTRACT(DATE FROM creation_time) > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
most_recent_jobs_without_history_based_optimizations AS (
SELECT *
FROM jobs
WHERE NOT has_history_based_optimization
QUALIFY ROW_NUMBER() OVER (PARTITION BY query_hash ORDER BY end_time DESC) = 1
)
SELECT
job.job_id,
SAFE_DIVIDE(
original_job.elapsed_ms - job.elapsed_ms,
original_job.elapsed_ms) AS percent_execution_time_saved,
job.elapsed_ms AS new_elapsed_ms,
original_job.elapsed_ms AS original_elapsed_ms,
FROM jobs AS job
INNER JOIN most_recent_jobs_without_history_based_optimizations AS original_job
USING (query_hash)
WHERE
job.has_history_based_optimization
AND original_job.end_time < job.start_time
ORDER BY percent_execution_time_saved DESC
LIMIT 10;
履歴ベースの最適化が適用された場合、上記のクエリの結果は次のようになります。
/*--------------+------------------------------+------------------+-----------------------*
| job_id | percent_execution_time_saved | new_execution_ms | original_execution_ms |
+--------------+------------------------------+------------------+-----------------------+
| sample_job1 | 0.6780685018624512 | 7087 | 22014 |
| sample_job2 | 0.6648580041250198 | 10562 | 31515 |
| sample_job3 | 0.63285605271764256 | 97668 | 266021 |
| sample_job4 | 0.611341417268879 | 923384 | 2375823 |
| sample_job5 | 0.5538127208971375 | 1060062 | 2375823 |
| sample_job6 | 0.4539694316803648 | 2324071 | 4256302 |
| sample_job7 | 0.38227031526376026 | 17811 | 28833 |
| sample_job8 | 0.33826608962725113 | 66360 | 100282 |
| sample_job9 | 0.32087813758311606 | 44020 | 64819 |
| sample_job10 | 0.2835641631948354 | 19088 | 26643 |
*--------------+------------------------------+------------------+-----------------------*/
詳細
- これは、履歴ベースの最適化による影響の推定にすぎません。スロットの可用性、時間の経過に伴うデータの変化、クエリ パラメータ値の違い、ビューや UDF の定義など、さまざまな要因がクエリのパフォーマンスに影響する可能性があります。
- このクエリは、
total_slot_msやtotal_bytes_billedなどの他のクエリ パフォーマンス指標にも適用できます。詳細については、INFORMATION_SCHEMA.JOBSのスキーマをご覧ください。 - このサンプルクエリの結果が空の場合、履歴ベースの最適化が使用されたジョブがないか、すべてのクエリが 30 日以上前に最適化されています。
ロールと権限
履歴ベースの最適化を有効にするには、BigQuery のデフォルト構成を作成する権限が必要です。また、
ALTER PROJECTステートメントを使用して、履歴ベースの最適化を有効にする必要があります。履歴ベースの最適化を有効にすると、ジョブを作成したユーザーに関係なく、そのプロジェクト内のすべてのジョブで履歴ベースの最適化が使用されます。デフォルト構成に必要な権限について詳しくは、デフォルト構成の必要な権限をご覧ください。履歴ベースの最適化を有効にするには、履歴ベースの最適化を有効にするをご覧ください。INFORMATION_SCHEMA.JOBSビューを使用してジョブの履歴ベースの最適化を確認するには、特定のロールが必要です。詳細については、INFORMATION_SCHEMA.JOBSビューに必要なロールをご覧ください。