Databricks における SQL ストアドプロシージャの紹介

SQLの定型的なタスク、例えばデータクリーニング、ビジネスルールの更新、バッチ処理などは、コードをコピー＆ペーストしていると、手間がかかり、間違いも起こりやすくなります。 

Databricks の SQL ストアド プロシージャ（一般提供開始）を使えば、そのロジックを一度保存し、必要な時にいつでも実行でき、Unity Catalog で管理できます。 

分析前のデータクリーニング、ビジネス基準に基づいたテーブルの更新、レガシーなエンタープライズ データウェアハウスからのワークロード移行など、どのような場合でも、ストアド プロシージャはプロセスをよりシンプルに、一貫性があり、保守しやすくします。

Databricks は、プロプライエタリまたはベンダー固有の実装を避け、オープンスタンダードと相互運用性をサポートしています。SQL ストアド プロシージャは ANSI/PSM SQL 標準に準拠しており、オープンソースの Apache Spark™ に貢献される予定です。

プロシージャは、管理タスク、データ管理、ETL ワークフローで広く使用されており、特にエンタープライズ データウェアハウス（EDW）で利用されています。EDW から Databricks に移行するお客様は、既存のストアド プロシージャを書き直すことなく移行できるため、移行が容易になります。そして、いつものように、最高のデータウェアハウスはレイクハウスです。

顧客セグメンテーションに関する重要なユースケースの 1 つで、DBSQL とともに SQL ストアド プロシージャを活用し、パフォーマンス、スケーラビリティ、コスト効率を向上させました。SQL に慣れていたため、非常に短時間でソリューションを実装し、本番環境にデプロイすることができました。ストアド プロシージャを使用することで、複雑なロジックをより効果的に管理しながら、全体的なアーキテクチャを合理化し、保守可能にすることができました。—SambaSiva Rao, Sr. Data Engineer/Architect, ClicTechnologies

SQL ストアド プロシージャが一般提供開始されました。

SQL ストアド プロシージャの概要

ストアド プロシージャとは？

データ処理ワークフロー全体で、お客様は定型的なタスクや複雑なロジックの一貫性とパフォーマンスの維持に苦労することがあります。このような場合、ストアド プロシージャは、データが一貫した標準化された方法で処理され、パフォーマンスが最適化されることを保証する優れたアプローチです。 

データクリーニングタスクの場合、プロシージャは、不整合な日付フォーマットを標準化された構造に変換する、テキストフィールドの先頭および末尾の空白をトリミングする、エラーのある値を置き換えるまたは修正するといった変換を適用できます。これにより、データが下流の分析のために準備されます。詳細な ETL の例を以下に示します。

データ管理の側面では、ストアド プロシージャは、無効なレコードにフラグを付ける、フィールドを再計算する、関連テーブル間でデータを同期するといった定義済みのビジネスルールに基づいて、テーブル値を効率的に更新できます。これらの操作をプロシージャにカプセル化することで、チームは一貫した実行を保証し、手動介入を減らし、大規模なデータ品質を向上させることができます。以下に、ロイヤルティ/メンバーシッププログラムを更新するためにストアド プロシージャを使用した詳細なデータ管理の例を示します。

では、プロシージャとは何でしょうか？プロシージャは、SQL ステートメントの事前コンパイルされたコレクションであり、ユーザーが SQL ロジックを単一の再利用可能なユニットにまとめることができます。プロシージャは Unity Catalog に格納されるため、管理され、権限が完全にカプセル化されます。ストアド プロシージャが呼び出されると、データベースはこれらの事前定義された操作を実行し、セキュリティの強化、複雑なワークロードの保守の簡素化、パフォーマンスの向上の可能性といったメリットを提供します。

サポートされている機能は？

プロシージャをサポートする 5 つのコア コマンドは、CREATE、CALL、DESCRIBE、SHOW、DROP です。

• CREATE PROCEDURE: Unity Catalog 内に新しいストアド プロシージャを定義して格納します。プロシージャの名前、パラメータ（存在する場合）、およびプロシージャが呼び出されたときに実行される SQL ステートメントを指定します。
• CALL PROCEDURE: 事前に作成されたストアド プロシージャを実行します。必要なパラメータを渡します。
• DESCRIBE PROCEDURE: 名前やパラメータなど、既存のプロシージャに関する基本的なメタデータを返します。EXTENDED を使用すると、説明には、所有者、作成日時、セキュリティタイプなど、追加のメタデータが含まれます。
• SHOW PROCEDURES: 現在のカタログスキーマで利用可能なすべてのストアド プロシージャを一覧表示します。
• DROP PROCEDURE: 既存のプロシージャをストレージから削除します。 

プロシージャを作成する際には、入力と出力を制御するためにいくつかのパラメータタイプを使用できます。 

• `IN` パラメータ: プロシージャに値を入力として渡すために使用されます。たとえば、特定の顧客のデータのみを取得または処理するために顧客 ID を渡す場合があります。プロシージャはこれらの値を読み取ることはできますが、変更することはできません。
• `OUT` パラメータ: 代入された後、プロシージャから値を返すために使用されます。たとえば、顧客 ID を渡し、そのアカウントステータスまたは計算された売上合計を、プロシージャ外でのさらなる処理のために返す場合があります。
• `INOUT` パラメータ: 二重の目的を果たし、値をプロシージャに渡し、その中で変更し、更新された値を返すことができます。入力と出力の両方として機能します。 

これらのパラメータは、以下に割り当てることができます。

• ローカル変数: スクリプト/プロシージャ内で宣言されます
• セッション変数: セッション内で宣言され、スクリプト/プロシージャの外でも使用できます

SQL ストアド プロシージャ内にカプセル化されたロジックは、SQL スクリプティングの上に構築されています。ストアド プロシージャは、パラメータを持つ再利用可能なスクリプトと見なすことができ、Unity Catalog によって管理されます。スクリプティングについては、これらの 2 つの入門ブログで詳しく説明しています。

• SQL スクリプティングの紹介 パート 1
• SQL スクリプティングの紹介 パート 2
• SQL スクリプティングの ドキュメントを確認してください

ネストされたプロシージャ呼び出しと再帰的なプロシージャ呼び出しがサポートされています。これは、お客様が作業単位またはビジネスロジックを個別のプロシージャに便利に整理できることを意味し、SQL 実行フロー全体をよりモジュール化できます。これにより、可読性と保守性が向上します。

プロシージャと関数の違いは何ですか？

プロシージャは、UI では Unity Catalog の 関数とともにグループ化されています。しかし、プロシージャと関数は、SQL ロジックを再利用できるという点で似ていますが、目的が異なります。

関数は、値またはテーブルを返すために使用されます。SQL クエリ内で使用する必要があり、動的 SQL や手続き型ロジックを含めることはできません。対照的に、プロシージャは、一連の SQL ステートメントを実行するために使用されます。制御フロー、変数、ループ、および IDENTIFIER と EXECUTE IMMEDIATE を使用した動的 SQL を含めることができます。プロシージャは、通常、タスクまたはワークフローを実行するために、スタンドアロン コマンドとして呼び出されます。

SQL ストアド プロシージャの使用例

SQL ストアド プロシージャの機能について説明したので、次にその価値と解決に役立つ問題を示す例を見てみましょう。

この ノートブックを使用して、この投稿のすべての例とデータ準備コマンドが含まれているため、フォローアップできます。

ETL – データクリーニング：シルバーまたはゴールドレイヤーテーブルの準備

典型的な メダリオンアーキテクチャに従っている場合、ブロンズからシルバー（またはシルバーからゴールド）へのデータの移動には、データのクリーニング、変換、集計、フォーマットが必要になることをご存知でしょう。ストアドプロシージャは、ETLワークフロー内でこれらの繰り返しプロセスを管理するのに役立ちます。 

このETLシナリオでは、プロシージャを使用して以下を行います。

• 生データからファクトテーブルにデータをロードする
• 日付範囲と販売元（Web、モバイル、POS、サードパーティベンダー）に基づいてデータを選択する
• 日付を特定の形式に変換し、空白を削除してデータをクリーニングおよびフォーマットする
• クリーニング後、データを「クリーン」テーブルにロードする
• タイムスタンプ、開始日、終了日、販売元に基づいてログレコードを追加する
• プロシージャを使用して、2025年6月のモバイルアプリの売上を検索する

このようにプロシージャを使用すると、データプロダクトを標準化できます。このプロシージャのユーザーは、日付範囲やPOSに関係なく、同じ構造でデータを作成できます。これはコードを再利用する主な利点です。同じロジックが毎回実行されるため、コードの再利用は自然にエラーが発生しにくくなります。

データ管理：ビジネス基準に基づいてテーブルを更新する

データ管理とは、データが正確で、一貫性があり、効率的にアクセスできることを保証するプラクティスであり、データ主導の意思決定を目指すあらゆる組織にとって不可欠な品質です。強力なデータ管理がなければ、最も高度な分析やレポート作成の取り組みでさえ、信頼性の低い、または一貫性のない情報によって損なわれる可能性があります。 

商業業界で一般的に見られる例を見てみましょう。ビジネスでは、顧客にティアに基づいた特典を提供するためにロイヤルティプログラムを確立することがよくあります。航空会社にはマイレージプログラムがあり、ほとんどの小売フランチャイズにはリワードプログラムなどがあります。顧客が同じ航空会社をより多く利用したり、同じフランチャイズからより多くの商品を購入したりすると、顧客はより多くの特典を獲得できます。

ここでは、ストアドプロシージャを使用して標準的な小売ロイヤルティプログラムを管理および更新する方法の例を示します。顧客のロイヤルティティアを管理するために2つのプロシージャが使用されます。1つは指定されたcustomerIDの特定の顧客ロイヤルティティアを更新するためのもので、もう1つは指定された国からのすべての顧客の顧客ロイヤルティティアを更新するためのものです。

それでは、作成したプロシージャを使用してセルビア、ドイツ、カナダの顧客のロイヤルティティアを更新し、更新されたレコードを確認しましょう。

前のクエリは次の結果を生成します。

更新ロジックをそれぞれのプロシージャにカプセル化することにより、コードの重複を回避し、呼び出し元から複雑さを排除します。呼び出し元は、適切なプロシージャパラメータを使用してプロシージャを呼び出すだけで済みます。

次に何をしますか？

DBSQLにSQLストアドプロシージャが追加されたことで、顧客はレガシーエンタープライズデータウェアハウスワークロードをレイクハウスに移行し続けることができます。顧客からのフィードバックに基づき、GAに向けて進むにあたり、いくつかの主要な機能を対処したいと考えています。

• Apache Spark™のSQLストアドプロシージャ：オープンソースのすべてのSQLプロシージャのサポート
• SQL SECURITY DEFINERのサポート：顧客がプロシージャ作成者の権限（定義者）でプロシージャロジックを実行できるようにする

SQLスクリプティングおよびプロシージャに関連するフィードバックやリクエストを共有したい顧客は、この フォーム から行うことができます。

レガシーシステムからストアドプロシージャを移行するために必要な他の2つの重要なSQLコンストラクトは、 一時テーブル とトランザクションです。一時テーブルは現在DBSQLで一般提供されており、トランザクション（複数ステートメントおよび複数テーブル）のサポートはまもなく提供されます。 

既存のDatabricksユーザーの方も、他のデータウェアハウスから移行する方も、SQLストアドプロシージャは複雑なSQLワークフローの管理を簡素化するために活用すべき機能です。SQLストアドプロシージャの利用を開始するには、Databricksのドキュメントをお読みください。

Databricks SQLの詳細については、当社のウェブサイトをご覧になるか、ドキュメントをお読みください。Databricks SQLの製品ツアーもご覧いただけます。既存のウェアハウスを、優れたユーザーエクスペリエンスと低い総所有コストを備えた、高性能なサーバーレスデータウェアハウスに移行したい場合は、Databricks SQLがそのソリューションです。無料でお試しください。

(このブログ記事はAI翻訳ツールを使用して翻訳されています) 原文記事