Top 10 query performance tuning tips for Databrick... - Databricks Community - 43218の翻訳です。
本書は著者が手動で翻訳したものであり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
DatabricksサーバレスSQL(DBSQL)は、レイクハウスでデータウェアハウスを構築するためのDatabricksの最新オファリングです。オープンフォーマット、統合分析、組織における様々なデータペルソナにおけるコラボレーティブなプラットフォームのようなすべてのレイクハウスの機能と連携します。
ヒントのサマリー
-
SELECT * FROM my_tableを避ける - 検索を
LIMITする - Integer vs Stringデータタイプ
- キャッシュを活用する
- CTE vs サブクエリー
- Photon化可能な関数を使う
- Joinヒントを活用する
-
ANALYZE、OPTIMIZE、VACUUMを実行する - クエリープランを理解する -
EXPLAIN - SQLコードをフォーマットする
ヒント1 - SELECT * FROM my_tableを避ける
SELECT * FROM my_tableは、指定されたテーブルのすべての列と行を取得し、大規模テーブルにおいては入力・出力を過度に必要とするオペレーションです。通常、テーブルの列名を特定したり、サンプルデータのプロファイリングを行うためだけにこのシンプルなクエリーを使います。この素朴なアプローチは、このような方法でテーブルのプロファイリングを行おうとするユーザー数が増加すると、クエリー実行におけるボトルネックを引き起こす場合があります。すべての列を取得するのではなく、分析に必要な列のみを選択することが常に推奨されます。列の詳細でテーブルのプロファイリングを行ったり、サンプルデータを分析する際に、Databricksコンソールのカタログエクスプローラを活用することができます。これによって、テーブルに対するSELECT *を避けることができます。
ヒント2 - 検索をLIMITする
LIMIT - クエリーによって返却される行数を限定するためにLIMIT句を使います。多くの場合、データの任意のサブセットに対するデータプロファイリング、取得のためにこの機能を用います。データベースの結果から取得すべき行数の最大値を指定します。DBSQLクエリーエディタはすべてのクエリーに対してデフォルトでLIMIT 1000を追加します。クエリーによって返却される行数を制限することで、データベースの性能を改善することができます。必要な行のサブセットのみを取得することで、データベースエンジンは不必要な処理を避け、ネットワークを転送されるデータの量を削減することができます。大規模な結果セットは、特にそれら全体を必要としない場合には膨大な量のメモリーを消費することがあります。LIMITを用いることで、クエリー結果を保持するのに必要なメモリーの総量を制御することができます。
FILTER - WHERE句を用いることで、特定の条件に基づいてテーブルから特定の行を選択することができます。フィルターを指定することで、期待する条件に合致するデータのみを取得し、あなたのクエリーの結果をより適切で意味のあるものにします。また、データスキャンを削減し、クエリーの性能を改善するためにZ-Orderingの機能を活用することができます。Z-ORDERは特に特定の日付範囲、数値の範囲、その他の列の範囲に基づいたデータのフィルタリングのような範囲ベースのクエリーを含むワークロードでメリットが得られます。Z-ORDERの詳細に関しては、ドキュメントをご覧ください。
ヒント3 - Integer vs Stringデータタイプ
データベースのテーブルを設計する際、Integer(整数)かString(文字列)のどちらのデータ型を選択するのかは、データの特性、利用パターン、アプリケーション固有の要件を含むいくつかの要素に依存します。お客様によってなされる一般的な選択肢は、決定論的である数値、アルファベットのキーに基づいたHASH関数を使うと言うものです。しかし、ストレージの観点から検討するのであれば、数値とアルファベットのキーは文字列型なので、整数型に比べて大きなストレージ容量を必要としています。これは、BIツールへのデータインポートを遅くし、レポートの性能を劣化させます。
こちらに、1億以上のレコードを持つNYCトリップデータの例を示します。こちらには2つのテーブルがあります: TripIDが文字列型のnyc_taxi_trp_detail_stringとTripIDが整数型のnyc_taxi_trp_detail_integerです。整数のキーを持つテーブルのサイズは約3GBですが、文字列のキーを持つテーブルは驚くべきことに7GBとなっています。
究極的には、整数型か文字列型の列のどちらを選択するのかは、特定の要件やお使いのデータとアプリケーションの特性に依存します。内部参照のための整数値のサロゲートキーや外部とのインタラクションのための文字列のナチュラルキーのようにあるケースにおいては、両方のタイプを組み合わせることが適切な場合もあります。意思決定を行う際には、パフォーマンス、データの完全性、要件、お使いのデータの意味のある表現のような要素を検討することが重要です。
ヒント4 - キャッシュを活用する
DBSQLのキャッシュは、データ取得や処理に必要となる時間を削減することで、繰り返しの計算処理のパフォーマンスを劇的に改善することがあります。キャッシュによって、以前に計算されたデータを再利用することで、冗長な計算処理を回避し、あなたのクエリーやオペレーションにおける全体的な処理時間を削減することになります。しかし、過度のメモリー消費や潜在的なメモリー不足を避けるために、あなたのワークロードのメモリー要件を検討し、正当性を持ってキャッシュを行うことが重要です。Databricksでは様々なキャッシュのメカニズムを提供しており、DBSQLではそれらの機能を活用しています。
- DBSQL UIキャッシュは最新のクエリー、ダッシュボード、結果セットへのクイックなアクセスを提供することで、ユーザーエクスペリエンスを改善することを狙いとしています。
-
クエリー結果のキャッシュには、ローカルキャッシュとリモートキャッシュ(サーバレスのみ)があります。
- ローカルキャッシュ - クラスターの生存期間、あるいはキャッシュがフルになるまでインメモリで最新のクエリー結果を格納します。これは、正確なクエリーテキストをベースとします。
- リモートキャッシュ - Databricksワークスペースにおけるすべてのウェアハウスのデータをクラウドストレージに永続化します。
- ディスクキャッシュは以前はDeltaキャッシュと呼んでいました。このディスクキャッシュは、ディスクにデータを格納することでクエリーのパフォーマンスを改善するように設計されており、データ読み込みを高速にします。高速な中間フォーマットを活用して、ファイルを取得する際にデータは自動でキャッシュされます。
SQLユーザーとして、最も頻繁に使用されるテーブルやクエリーのSELECT文としてデータをディスクキャッシュにキャッシュすることができます。
CACHE SELECT * FROM boxes;
CACHE SELECT width, length FROM boxes WHERE height=3;
ディスクキャッシュが適切に動作するように、このコマンドを使う必要はなく、最初にアクセスした際にデータは自動でキャッシュされます。しかし、一貫性のあるクエリーパフォーマンスを必要とする際にはこれが役に立つことがあります。
この機能はDeltaテーブルとParquetテーブルのみに適用されます。
ヒント5 - CTE vs サブクエリー
CTE (Common Table Expressions)とサブクエリーの両方は、複雑なクエリーを表現し、データを整理するためのSQLのパワフルなテクニックです。これらは、同等の結果をもたらしますが、複雑なクエリーでの使用や大規模なデータセットに使用した際に、サブクエリーはCTEよりも性能がでない場合があります。オプティマイザがCTEとは異なるクエリー実行プランをサブクエリーで選択する場合があります。特定のクエリーによっては、オプティマイザは最適化やパフォーマンスの観点において、サブクエリーとCTEを同等に取り扱ったり、取り扱わなかったりします。これに加えて、CTEは複雑なクエリーをより小規模で管理可能な部品にブレークダウンすることで、可読性とメンテナンス性を改善することがあります。これらは、中間結果セットに意味のある名前をつける方法を提供し、クエリーのロジックをより簡単に理解できるようになります。CTEは同じクエリーで複数回参照されることがあるので、コードの再利用を可能にし、複雑なサブクエリーを繰り返すことを避けることができます。これは、より簡潔でモジュール化されたクエリーにつながります。
ヒント6 - Photon化可能な関数を使う
Photonはモダンなハードウェアはを活用するためにC++で開発されたDatabricks上の新たなベクトル化クエリーエンジンであり、Apache Spark APIと互換性があります。DBSQLは膨大な量のデータを処理し、集計やjoinを行うクエリー実行を高速化するためにデフォルトでPhotonを使用します。DBSQLネイティブ関数のほとんどはPhotonでサポートされています。網羅性に関するドキュメントでPhotonのスコープに関する詳細を知ることができます。ユーザー定義関数(UDF)はまだPhoton化可能ではありませんので、どのようなUDFを記述する前においてもすべてのネイティブのPhoton化可能な関数を探索するようにしてください。
ヒント7 - Joinヒントを活用する
Joinヒントによって、DBSQLオプティマイザが使用すべきjoin戦略を明示的に提案することができます。Joinの両側に様々なJoin戦略ヒントを指定すると、Databricks SQLは以下の順序でヒントの優先度付けを行います: SHUFFLE_REPLICATE_NL(優先度低) -> SHUFFLE_HASH -> MERGE -> BROADCAST(優先度高)。
- BROADCAST - Broadcast joinを使います。autoBroadcastJoinThresholdに関係なくヒントのJoin再度がブロードキャストされます。Joinの両サイドにBroadcastヒントがある場合、(統計情報に基づいて)サイズの小さい方がブロードキャストされます。BROADCASTの別名にはBROADCASTJOINとMAPJOINがあります。Broadcast joinは、Joinに関与するデータセットのいずれかがクラスターのそれぞれのエグゼキューターノードのメモリー全体にフィットするほど小さい場合に適用されます。すべてのエグゼキューターノードに小さい方のデータセットをブロードキャストすることで、Sparkは通常のJoinオペレーションで発生する高コストなシャッフルフェーズを回避することができ、全体的な計算処理を劇的にスピードアップすることができます。
- MERGE - shuffle sort-merge joinを使います。MERGEの別名にはSHUFFLE_MERGEとMERGEJOINがあります。Joinに含まれる両方のデータセットがメモリーに収まらないほど大きい場合には、Broadcast joinとは異なり、Merge Joinが効率的に実行されます。Merge Joinでは、Joinキーに基づいて入力のデータセットがソートされている必要があります。データセットがすでにソートされている場合、あるいは、ソートによる大きなオーバーヘッドがない場合にはMerge Joinは優れた選択肢となります。
- SHUFFLE_HASH - Shuffle hash joinを使用します。両サイドにShuffle hashのヒントがある場合には、Databricks SQLはビルドサイドとして(統計情報に基づいて)小規模なサイドを選択します。Joinに含まれる両方のデータセットがメモリーに収まらないほど大きく、データの分布に偏りがあり、あるキーが他のものより非常に多く含まれることを意味する場合、Shuffle hash joinがSparkエンジンによって実行されます。
- SHUFFLE_REPLICATE_NL - shuffle-and-replicate nested loop joinを使います。これは、クロスジョインとも呼ばれるcartesian product joinとなります。二つのデータセット間でcartesian joinを実行する際、結果の出力はそれぞれのデータセットの行数の積となります。これは、すぐに計算リソースを消費し、特に対象となるデータセットが大きい場合には、出力行数が膨大になることになります。
BROADCASTヒントやSHUFFLE_HASHヒントが両サイドに指定されると、Databricks SQLはJoinのタイプとリレーションのサイズに基づいてビルドサイドを選択します。指定された戦略がすべてのJoinタイプをサポートしていない場合があるので、Databricks SQLはヒントで指示されているJoin戦略を使うことを保証しません。
ヒント8 - ANALYZE、OPTIMIZE、VACUUMを実行する
ANALYZEは指定されたテーブルの統計情報を収集します。これらの統計情報は、より良い実行計画を生成するためにDBSQLクエリーオプティマイザによって活用されます。
ANALYZE TABLE nyc_taxi_trp_detail_integer COMPUTE STATISTICS;
OPTIMIZE文はDelta Lakeのデータファイルのレイアウトを最適化します。同じファイルセットに関連するデータを配置するように、データのZ-Orderを行うこともできます。
OPTIMIZE nyc_taxi_trp_detail_integer;
OPTIMIZE nyc_taxi_trp_detail_integer ZORDER BY (TripID);
VACUUMはテーブルのディレクトリから使用されていないファイルを削除します。テーブルのトランザクションログの最新の状態に含まれていないすべてのデータファイルや保持期間よりも古くなっているものを削除します。デフォルトの保持期間は7日間です。Deltaテーブルに対してVacuumを実行した際には、テーブルの古いバージョンにタイムトラベルする能力を失うことになりますが、ストレージコストの削減の助けとなります。
VACUUM nyc_taxi_trp_detail_integer DRY RUN;
VACUUM nyc_taxi_trp_detail_integer;
ヒント9 - クエリープランを理解する - EXPLAIN
クエリーを実行する前にクエリープランを探索、解析することは非常に重要です。これによって、コードが実際にどのように実行されるのかを理解したり、クエリーを最適化する助けとなります。DBSQLのオプティマイザは実行される最適化物理プランを自動で生成します。
EXPLAIN [EXTENDED|CODEGEN|COST|FORMATTED]
SELECT
VendorID, count(TripID) AS TotalTrip
FROM
nyc_taxi_trp_detail_integer
GROUP BY VendorID;
また、DBSQLはクエリー実行時にクエリープランの再度の最適化であるAdaptive Query Execution (AQE) の機能も提供しています。統計情報の収集がオンになっていない、統計情報が古くなっている場合には特に有用になることがあります。また、複雑なクエリーの途中やデータの偏りが発生した後に、静的に導出された統計情報が不正確である場合には有益となります。
ヒント10 - SQLコードをフォーマットする
最後だからといって優先度が低いわけではありませんが、SQLクエリーのフォーマットは常に従うべきベストプラクティスとなります。クエリーのパフォーマンスを改善するものではありませんが、適切にフォーマットされたコードは間違いなく、他の人のコードの理解、デバッグ、リファクタリング、新たなビジネスロジックの構築における効率性を改善します。ベストプラクティスのいくつかは:
- すべてのテーブルと列に対して類似した命名規則を適用します。
- 必要であればコメントを追加します。これによってビジネス要件を理解する助けとなります。
- IN句で値のリストを外挿することを避けましょう。
- 明示的にデータ型を定義し、異なるデータフォーマットの混在を避けましょう。
- パラメータ化されたクエリーにおいては、パラメータのコメントを追加しましょう。
- クエリーの末尾にセミコロンを追加することは誰も傷つけません！
まとめ
これらのベストプラクティスを実装することで、クエリー処理スピードを改善し、効率的なリソース使用量、スケーラビリティ、全体的なシステムの安定性にも貢献し、組織が増加するデータボリュームやユーザーの要望に対応できるようになります
