SQL ノートブック: Jupyter と SQL エディターの機能を組み合わせてデータ分析を行う

Meta では、内部データ ツールがデータ サイエンティストから生産エンジニアへの主要なチャネルです。 そのため、科学者やエンジニアがデータを使用して意思決定を行うだけでなく、安全でコンプライアンスに準拠した方法で意思決定を行えるようにすることが重要です。

SQL IDE と Jupyter Notebook の機能を組み合わせた新しいツールである SQL Notebook を開発しました。 これにより、従来のノートブックよりもスケーラブルで安全な方法で SQL ベースの分析を行うことができますが、複数の相互依存セルや Python の後処理など、ノートブックや基本的な SQL 編集の機能も引き続き提供されます。

導入から 1 年で、SQL Notebooks は Meta のデータ サイエンティストとデータ エンジニアの大半によって社内で採用されています。 ここでは、2 つのユビキタス ツールを組み合わせて、個々のパーツの合計よりも優れたものを作成する方法を示します。

SQL の利点

人々がデータにアクセスする方法はたくさんあります。 次のようなUIを介して行うことができます スキューバ、私たちのようなドメイン固有言語 (DSL) 時系列データベース、または Spark の Scala のようなプログラム API です。 ただし、分析データにアクセスするための主な方法は、古き良き SQL です。 これには、メインの分析データベース (Presto、Spark、および MySQL データベース) へのほとんどのクエリが含まれます。

初期の頃から、Web インターフェースを使用して SQL 経由で分散データベースからデータを照会する内部ツールがありました。 HiPal (Hive + Pal) と呼ばれる最初のバージョンは、Hive データベースからデータを照会し、その後オープン ソース ツールに影響を与えました。 エアパル. HiPal は後に、Presto、Spark、MySQL、Oracle などの SQL ベースのデータ ストアにクエリを実行し、すぐに使用できる視覚化を提供する、より一般的なツールである Daiquery に置き換えられました。

Daiquery は、定期的に SQL を操作する多くの人々にとって頼りになるツールであり、Meta のデータ サイエンティストとエンジニアの 90% によって使用されています。

ノートブックの能力と限界

Jupyter Notebook は、データ サイエンティストにとって革新的なツールです。 複数のセルとインライン マークダウンをサポートすることで、豊富な視覚化とステップ内のドキュメントが可能になります。 Meta では、次のプロジェクトを通じて、ノートブックをエコシステムに統合しました。 弁当.

ただし、ノートブックは非常に強力ですが、いくつかの制限があります。

  1. スケーラビリティ。 プロセスはローカルで実行されるため、1 台のマシンによってメモリと CPU が制限され、たとえばビッグ データの処理が妨げられます。
  2. 報告と共有。 ノートブックは単一のマシンに関連付けられているため、スナップショットの結果を他のユーザーと共有するには、ノートブック全体で保存する必要があります。

    このアプローチには 2 つの主な欠点があります。
    • 安全: 基になるデータには、ACL チェックが含まれている場合があります (たとえば、テーブル レベルで)。 これは、コードを実行する必要があり、ノートブックの所有者がアクセス制御にあまり熱心でない場合、データ漏洩につながる可能性があるため、スナップショットに適用するのは非常に困難です。
    • 古さ: これはデータのスナップショットであるため、誰かがノートブックを定期的に実行しない限り更新されません。これにより、誤解を招く結果が生じたり、ノートブックの作成者が定期的に手動で介入する必要が生じる可能性があります。

SQL ノートブックに入る

SQL Notebooks は、ノートブックと SQL エディターの両方の長所を 1 つに組み合わせたものです。 SQL Notebook を強力にするいくつかの機能を次に示します。

モジュラーSQL

SQL が非常に複雑になり、保守が困難になる可能性があるというフィードバックをよく受け取ります。 Presto のようなデータベースは共通テーブル式 (CTE) をサポートしており、コードの編成に非常に役立ちます。 ただし、すべての人が CTE に精通しているわけではなく、コードを読みやすくするための適切なプラクティスを実施するのが難しい場合もあります。

クエリのおそらく自然な増加をより適切に処理するために、ノートブックのように複数のセルをサポートするように SQL ツールの Daiquery を拡張しました。 各セルには名前があり、表のように名前で他のセルを参照できます。

たとえば、過去 1 週間の各日の収益で上位 3 社を見つけたいとします。

最初のセルでは、会社と日ごとにデータを集計します。

company_revenue_agg:
 
SELECT day, company, SUM(sale) as revenue FROM companies 
WHERE day >= ''
GROUP BY day, company

2 番目のセルでは、ウィンドウ関数を使用して、各日内の各企業にランクを追加できます。

ranked_companies:
 
SELECT
  *, 
  RANK() OVER (PARTITION BY ds ORDER BY hits DESC) AS row_number 
FROM company_revenue_agg

SQL ノートブック

最後に、3 番目のセルで、上位 3 つのランクのみを選択します。

top3_companies:
 
SELECT * FROM ranked_companies WHERE row_number <= 3

SQL ノートブック

各クエリはそれ自体が単純で、独立して実行して中間結果を調べることができます。 実行時 ランク付けされた会社、サーバーに送信されるクエリは実際には次のとおりです。

WITH 
company_revenue_agg AS (
  SELECT day, company, SUM(sale) as revenue FROM companies 
          WHERE day >= ''
          GROUP BY day, company
      )
 	SELECT *, 
      RANK() OVER (PARTITION BY day ORDER BY revenue DESC) AS row_number 
      FROM company_revenue_agg

そして、3番目のセルを実行すると、 top3_companies、基になるクエリは次のようになります。

WITH
company_revenue_agg AS (
  SELECT day, company, SUM(sale) as revenue FROM companies 
    WHERE day >= ''
    GROUP BY day, company
),
ranked_companies AS (
  SELECT *, 
  RANK() OVER (PARTITION BY day ORDER BY revenue DESC) AS row_number 
FROM company_revenue_agg
)
SELECT * FROM ranked_companies WHERE row_number <= 3

CTE を認識していない人は、このクエリをネストされたクエリとして構成することになる可能性があります。これは、はるかに複雑で理解しにくいものになります。

2 番目のセルも 3 番目のセルも前のセルのデータを必要としないことに注意してください。 SQL は、分散バックエンドが理解できる自己完結型のセルに変換されます。 これにより、上で説明したノートブックのスケーラビリティの制限が回避されます。

フロントエンドも追加します リミット 1000 結果を印刷/視覚化するときにデフォルトで SQL ステートメントにステートメントを追加するため、実際の結果が company_revenue_agg より長い場合、上位 1,000 行のみが表示されます。 この制限は、次の場合には適用されません。 ランク付けされた会社top3_companies それを参照してください。 出力が要求されているセルの出力専用です。

Python、ビジュアライゼーション、マークダウン

モジュラー SQL のサポートに加えて、SQL Notebooks は UI ベースの視覚化をサポートします。 に似ている ベガ、ほとんどの一般的な視覚化のニーズに非常に便利です。 また、インライン ドキュメントのマークダウン セルもサポートしています。

SQL Notebook は、サンドボックス化された Python コードもサポートしています。 この機能は、SQL では表現が難しいラストマイルの小さなデータ操作に使用できますが、Pandas を使用すると簡単に実行でき、Plotly などのカスタム視覚化ライブラリを活用するために使用できます。

前の SQL の例を続けると、上記で取得したデータの棒グラフを表示する場合は、次の Python セルを実行するだけです。

import plotly.express as px
px.bar(
  top3_companies,
  x="day",
  color="company",
  y="hits",
  barmode="group"
)

SQL ノートブック

top3_companies このスニペットへの入力として検出されます。 セル top3_companies したがって、事前に実行され、その出力が Pandas データフレームとして利用可能になります。

Python でデータを取得したり、認証を必要とする操作を行ったりすることは許可されていないことに注意してください。 データを取得するには、Python セルが上流の SQL セルに依存する必要があります。 次に説明するように、これはセキュリティに対処するために非常に重要です。

出力を安全に共有する

SQL 構文はより制約されているため、特定のユーザーが特定のクエリを実行できるかどうかを静的に判断できます。 これは、Python のような動的言語では事実上不可能です。

したがって、SQL クエリの出力を保存できますが、ユーザーが最初に SQL を実行できた場合にのみ使用できます。 これは、信頼できる情報源として常にテーブル/列 ACL に依存していることを意味し、偶発的なデータ漏洩は発生しません。

Python でデータをクエリしていないため、Python セルにも同じメカニズムを適用できます。Python セルが依存するすべての入力 SQL をユーザーが実行できるかどうかを確認するだけで済みます。 その場合、キャッシュされた出力を Python の実行に使用しても安全です。

最新データの共有

クエリを実行し、スナップショットでアクセス制御を実行する安全な方法があるため、スナップショットを更新する非同期ジョブをスケジュールすることで、データの古さを回避できます。

SQL編集

SQL ノートブックは、オートコンプリート、テーブルのメタデータ ペイン (列名、タイプ、サンプル行など)、SQL フォーマット、セルからダッシュボードを作成する機能など、Daiquery エディターの最高のエクスペリエンスももたらします。

SQL ノートブック

SQL ノートブックの次のステップ

SQL ノートブックは、Python ノートブックのいくつかの一般的な問題に対処するのに役立ちますが、すべてに対する包括的なソリューションではないことに注意してください。 SQL でデータ フェッチを表現する必要があり、サンドボックス化された Python には制限があります。 Bento/Jupyter ノートブックは、機械学習ジョブの実行や、Python API を介したバックエンド サービスとの迅速なやり取りなど、高度なユース ケースにより適しています。

このツールを社内で発表したとき、SQL ノートブックが Bento/Jupyter ノートブックとどのように似ているかが指摘されました。 そのため、Bento チームと協力してツールを 1 つに統合し、ユーザーがツールを選択して閉じ込められるのではなく、ツール内でトレードオフを行えるようにしています。 また、古い Daiquery ツールを廃止する予定であり、新しい結合されたノートブックは、分析データにアクセスするための究極の統合された方法になります。

謝辞

SQL ノートブックは、Bento/Jupyter ノートブックと Observable の両方から着想を得ています。 また、このツールの製品化に尽力してくれた Bento チームと Daiquery チームにも感謝します。

Leave a Comment

Your email address will not be published. Required fields are marked *