SQLユーザー定義関数の紹介

公開日: 2021年10月20日

製品3 min read

ユーザー定義関数（UDF）は、ユーザーがApache Spark™ SQLのネイティブ機能を拡張するための手段です。Databricks上のSQLでは、1.3.0以降、Scala、Java、Python、Rプログラミング言語で記述された外部ユーザー定義関数をサポートしています。外部UDFは非常に強力ですが、いくつかの注意点があります。

セキュリティ。外部言語で記述されたUDFは、危険または悪意のあるコードを実行する可能性があります。そのため、UDFを作成できるユーザーを厳密に管理する必要があります。
パフォーマンス。UDFは、Catalyst Optimizerにとってブラックボックスです。CatalystはUDFの内部動作を認識できないため、SQLクエリのコンテキスト内でUDFのパフォーマンスを向上させるための作業を行うことができません。
SQLの使いやすさ。SQLユーザーにとって、ホスト言語でUDFを記述し、それをSparkに登録するのは手間がかかる場合があります。また、多くのユーザーが望むような比較的簡単なSQLの拡張セットがあり、外部UDFを開発するのは過剰な場合があります。

上記の問題に対処するため、新しい形式のUDFであるSQL UDFを発表できることを嬉しく思います。DBR 9.1 LTSで利用可能なSQL UDFは、SQLの表現力で完全に定義されており、SQLコンパイラからは完全に透過的です。

Databricks Lakehouse Platformの内部動作を理解するには、データウェアハウスをデータレイクハウスにもたらす理由 ebookをご覧ください。

SQL UDFを使用するメリット

SQL UDFは、Databricks上のSQLに対するシンプルかつ強力な拡張機能です。関数として、クエリ構築を簡素化する抽象化レイヤーを提供し、SQLクエリをより読みやすく、モジュール化します。非SQL言語で記述されたUDFとは異なり、SQL UDFはSQLユーザーにとって作成が容易です。SQL関数の本体はクエリオプティマイザから透過的であるため、外部UDFよりもパフォーマンスが高くなります。SQL UDFは、一時的または永続的な関数として作成でき、複数のクエリ、セッション、ユーザー間で再利用でき、アクセス制御言語（ACL）を介してアクセス制御できます。このブログでは、例とともにSQL UDFの主要なユースケースをいくつかご紹介します。

定数としてのSQL UDF

想像できる最も単純な関数、つまり定数から始めましょう。リテラルをコードで使用すると可読性が損なわれ、定数が実際には一定でなくなる可能性があるため、使用しない方が良いことは誰もが知っています。したがって、一度だけ変更できるようにしたいのです。

外部UDFに慣れている場合は、際立った違いがいくつかあることがわかります。

SQL UDFは、空の場合でも、パラメータリストを定義する必要があります。定数はパラメータを取りません。
関数は、返すデータ型も宣言します。この場合はSTRINGです。
関数の実装は、関数定義の一部です。
LANGUAGE SQLを指定して、SQL UDFであることを示します。しかし、実際には必要ありません。RETURN句は、これがオプションであることを示すのに十分です。

これらの違い以外にも、外部UDFと同じ点がたくさんあります。

関数を置き換えることができます。詳細については後述します。
関数を説明するコメントを追加できます。上記のように。
現在のセッション内でのみ使用できる一時関数を作成することもできます。

関数を使用してみましょう。

驚くことではありませんが、これは機能します。しかし、内部では何が起こっているのでしょうか？

これは素晴らしいです！SQLコンパイラは、関数呼び出しを定数自体に置き換えました。
これは、少なくともこのSQL UDFはパフォーマンスにコストがかからないことを意味します。

次に、別の一般的な使用パターンを見てみましょう。

式をカプセル化するSQL UDF

組み込み関数の名前付けが気に入らないと想像してください。別の製品から多くのクエリを移行していて、その製品には異なる関数名と動作があるのかもしれません。あるいは、単に長い式をSQLクエリで何度もコピー＆ペーストするのが我慢できないのかもしれません。そこで、それを修正したいのです。

SQL UDFを使用すると、好きな名前で新しい関数を簡単に作成できます。

ここで使用された新しい構文を見てみましょう。

この関数は引数を取ります。パラメータは、名前、型、およびオプションのコメントによって定義されます。
CONTAINS SQL句はオプションですが、関数がテーブル内のデータを読み取ったり変更したりしないことを示しています。これはデフォルト設定なので、通常は指定しません。
DETERMINISTICもオプションであり、関数が同じ引数を与えられた場合に常に同じ結果セットを返すことを示しています。この句は現時点ではドキュメント用です。しかし、将来的には特定のコンテキストで決定論的でない関数をブロックするために使用される可能性があります。
RETURN句では、パラメータは名前で参照されています。以下のより複雑なシナリオでは、パラメータが関数名で曖昧さを解消できることがわかります。当然、関数本体として任意の複雑な式を使用できます。

それは機能するだけでなく…

…うまく機能します。

物理計画には、lpad、hex、least、greatest関数の直接的な適用が表示されていることがわかります。これは、関数のシリーズを直接呼び出した場合と同じ計画です。

SQL関数をSQL関数から構成することもできます。

テーブルからの読み取りを行うSQL UDF

SQL UDFのもう1つの一般的な使用法は、ルックアップをコード化することです。単純なルックアップは、RGBカラーコードを英語のカラー名にデコードすることかもしれません。

わかりました。しかし、この世界には2色以上の色があります。そして、この翻訳を両方向で行いたいので、これらは実際にはルックアップテーブルに入れるべきです。

ここには複数の新しい概念が適用されています。

SQL UDFをREPLACEできます。それを行うには、新しい関数が古い関数のシグネチャと一致する必要があります。関数のシグネチャは、パラメータの数とその型で定義されます。
この関数はテーブル内の情報を検索するため、オプションでREADS SQL DATAを使用して文書化できます。何も指定しない場合、SQLコンパイラは正しい値を導き出しますが、嘘をついてCONTAINS SQLと指定してはいけません。
SQL SECURITY DEFINERは別のオプション句であり、colorsテーブルにアクセスするクエリは関数所有者の権限を使用することを示します。したがって、関数はテーブルのセキュリティを危険にさらすことなく、パブリックによって実行される可能性があります。
関数は所有者の権限で実行されるだけでなく、常に作成時の現在のデータベースを使用して解析されます。
`rgb`は数値の列名です。パラメータを`from_rgb`.`rgb`として修飾することで、列ではなくパラメータ参照を意味することを明確にします。

物理計画はどのようになりますか？クエリを実行する外部UDFを使用すると、ネストされたループ結合が発生する可能性があり、貴重なリソースを浪費する最悪の方法であることが簡単にわかります。

この場合、Catalystはネストループ結合ではなくブロードキャストハッシュ結合を選択しました。これは、SQL UDFの内容を理解しているため可能です。

これまでの例ではすべて、単一の値を返すスカラー値関数が使用されていました。その結果は、構造体、配列、マップの複雑な組み合わせであっても、あらゆる型になり得ます。さらに、テーブル値関数という別の種類のUDFについても説明します。

SQLテーブルUDF

ビューに引数があったらどうなるでしょうか？ユーザー提供の値に依存する複雑な述語をカプセル化できます。SQLテーブルUDFはまさにそれです。名前は違えどビューと同じですが、パラメータが付いています。

上記の色のマッピングが一意ではないと仮定しましょう。少なくとも、色の名前が言語によって異なることを断言できます。

したがって、`from_rgb`関数は、配列またはリレーションを返すように変更する必要があります。

ご覧のとおり、スカラー関数との唯一の違いは、より複雑なRETURNS句です。ビューとは異なり、SQL UDFは返されるリレーションのシグネチャの宣言を義務付けています。

TABLEは、関数がリレーションを返すことを指定します。
TABLE句には、各戻り列の名前とその列のデータ型を含める必要があります。
必要に応じて、戻り列のコメントを指定できます。

ユーザー定義テーブル関数はDBRの新機能です。呼び出し方法を見てみましょう。

最も簡単な形式では、テーブル関数はビューが参照されるのと同じ方法で、同じ場所で呼び出されます。唯一の違いは、関数の引数を含む必須の括弧です。この関数はリテラル引数で呼び出されますが、引数は式、さらにはスカラーサブクエリでもかまいません。

しかし、最も強力なのは、SQLテーブルUDFを結合、特に相関クロス結合で使用することです。

ここでは、引数がFROM句の前の（ラテラルな）リレーションを参照（相関）しています。新しいLATERALキーワードにより、Catalystはこれらの列を解決できます。また、結果シグネチャで定義されたとおりに列に名前を付け、オプションで関数名で修飾することにより、テーブル関数の結果を参照できることにも注意してください。

管理

当然のことながら、SQL UDFは、既存のGRANT、REVOKE、SHOW、DESCRIBE、およびDROPステートメントによって完全にサポートされています。

詳細に指摘する価値のあるステートメントはDESCRIBEです。

基本的なdescribeは期待どおりの結果を返しますが、拡張されたDESCRIBEはさらに多くの詳細を追加します。

今後の展望

ここで説明したことは、SQL UDFの初期機能です。検討中の将来の拡張機能には、以下が含まれます。

SQL PATH。これにより、データベースに関数ライブラリを作成し、ファイルシステムで行うように、別のデータベースから購読できます。
UDFのオーバーロード。
パラメータのデフォルト値を持つUDF。

SQL UDFはSQLの使いやすさにおいて大きな前進であり、このブログで概説されているようにさまざまな方法で使用できます。Databricks SQLでの利用や、データエンジニアリングジョブでのPhotonの使用など、さらにクリエイティブな方法をぜひお考えください。こちらのノートブックを試して、詳細についてはドキュメントをご覧ください。

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