How Databricks’ New SQL UDF Extends Spark SQL W/O the Usual Limitations - The Databricks Blogの翻訳です。
ユーザー定義関数(UDF)は、Apache Spark™のSQLを拡張することができるネイティブの機能です。Spark SQLでは、バージョン1.3.0以来、Scala、Java、Python、Rプログラミング言語で記述された外部ユーザー定義関数をサポートしています。外部UDFは非常に強力ですが、いくつかの注意が必要です。
- セキュリティ 外部の言語で記述されたUDFは、危険あるいは悪意のあるコードを実行することができます。このため、誰がUDFを作成できるのかに関して厳密な管理を必要とします。
- パフォーマンス UDFはCatalystオプティマイザにとってブラックボックスです。CatalystはUDFの内部動作を知らないため、SQLクエリーを実行する文脈においてUDFのパフォーマンスを改善することができません。
- SQLの使いやすさ SQLユーザーにとって、ホストの言語でUDFを記述し、Sparkに登録することは面倒なことです。また、外部UDFでやるには行き過ぎとなりますが、SQLでできた方が良いシンプルな拡張に対する要望がいくつか存在しています。
上記の制限に対応するために、新たな形態のUDFを導入できることを嬉しく思います。SQL UDFです。SQL UDFはDatabricksランタイム(DBR) 9.1 LTSで利用でき、SQLの表現能力をフル活用して定義することができ、SQLコンパイラーに対して完全な透明性を提供します。
SQL UDFを使用することのメリット
SQL UDFはSpark SQLにおけるシンプルかつパワフルな表現手法です。関数として、クエリー構築をシンプルにするための抽象化レイヤーを提供します。SQLクエリーの可読性、モジュール性を高めます。非SQL言語で記述されたUDFと異なり、SQL UDFはSQLユーザーが作成しやすい、より軽量なものとなります。SQL関数の中身は、クエリーオプティマイザに対して透明性を確保しているので、外部UDFよりも高パフォーマンスにすることができます。SQL UDFは一時的な関数、あるいは複数のクエリー、セッション、ユーザーで再利用できるように永続化関数として定義することができ、アクセスコントロール言語(ACL)でアクセス権をコントロールすることができます。この記事では、サンプルとともにSQL UDFのキーとなるユースケースをウォークスルーします。
定数としてのSQL UDF
想像できる最もシンプルな関数、定数からスタートしましょう。我々は皆、コードでリテラルを使おうとは思っていません。これは、可読性を損ない、結局のところ、定数は定数のままでいられないからです。このことから、一箇所で定数を変更できるようにしたいと考えることになります。
CREATE FUNCTION blue()
RETURNS STRING
COMMENT 'Blue color code'
LANGUAGE SQL
RETURN '0000FF'
外部UDFに慣れ親しんでいるのであれば、いくつかの違いに気づくことでしょう。
- SQL UDFでは、例え空であったとしても、パラメーターリストを定義しなくてはなりません。定数はパラメーターを受け取りません。
- また、関数は返却するデータ型を宣言する必要があります。このケースではSTRINGとなります。
- 関数の実装は関数定義の一部となります。
- これがSQL UDFであるということを伝えるために、LANGUAGE SQLを指定します。しかし、実際のところ、これは必須ではありません。LANGUAGEの指定を任意にするには、RETURN句を指定するだけで十分です。
これらの違いに加え、外部UDFと同じことがいくつかあります。
- 後で関数を置き換えることができます。
- 上に示したように、関数を説明するコメントを追加することができます。
- 現在のセッションでのみ利用できるテンポラリー関数を作成することができます。
関数を使って見ましょう。
SELECT blue();
0000FF
驚くことではありませんが、これは動きます。しかし、内部では何が起きているのでしょうか？
EXPLAIN SELECT blue();
== Physical Plan ==
*(1) Project [0000FF AS default.blue()#9]
+- *(1) Scan OneRowRelation[]
これはとてもさっぱりしたものです！SQLコンパイラーは関数の呼び出しを定数自身で置き換えています。これは、このSQL UDFはパフォーマンス観点ではゼロコストであることを意味します。
それでは、一般的な別の利用パターンを見て見ましょう。
SQL UDFの包含表現
あなたは、いくつかのビルトイン関数の名前が気に入らないシーンを考えて見ます。おそらく、あなたは異なる関数名、振る舞いを持つ別の製品から大量のクエリーを移行しているのでしょう。あるいは、お使いのSQLクエリーに対して、いくつかの長い表現をコピー＆ペースとすることに耐えられなくなっているのかもしれません。このため、これをどうにかしたいと考えます。
SQL UDFを用いることで、好きな名前で新たな関数をシンプルに作成することができます。
CREATE FUNCTION to_hex(x INT COMMENT 'Any number between 0 - 255')
RETURNS STRING
COMMENT 'Converts a decimal to a hexadecimal'
CONTAINS SQL DETERMINISTIC
RETURN lpad(hex(least(greatest(0, x), 255)), 2, 0)
ここで使われている新たな文法が、どのようなものであるのかを見ていきましょう。
- この関数には引数があり、パラメーターは名前、型、オプションのコメントで定義されます。
- CONTAINS SQL句はオプションですが、関数がテーブルのデータを読み書きしないことを知らせることができます。これはデフォルトの設定なので、通常は指定しません。
- DETERMINISTICもオプションであり、同じ引数が指定された場合には、常に同じ結果セットを返却することを知らせています。現時点では、この句はドキュメンテーションの目的でのみ使用されます。しかし、将来的には、特定のコンテキストにおいて、非決定論的関数をブロックするために使用されるかもしれません。
- RETURN句では、パラメーターが名前で参照されています。以下に示すような、より複雑なシナリオにおいては、関数名によってパラメーターが識別される場合があることを見ることでしょう。通常、関数のボディとして任意で複雑な表現を使用することができます。
これは動くだけなく、
SELECT to_hex(id) FROM range(2);
00
01
うまく動きます。
EXPLAIN SELECT to_hex(id) FROM range(2);
== Physical Plan ==
*(1) Project [lpad(hex(cast(least(greatest(0, cast(id#0 as int)), 255) as bigint)), 2, 0) AS default.to_hex(id)#1]
+- *(1) Range (0, 2, step=1, splits=4)
物理的プランから、これはlpad、hex、least、greatest関数のストレートなアプリケーションであることがわかります。これは、一連の関数を直接呼び出した場合と同じプランとなります。
SQL関数からSQL関数を作ることも可能です。
CREATE FUNCTION rgb_to_hex(r INT, g INT, b INT)
RETURNS STRING
COMMENT 'Converts an RGB color to a hex color code'
RETURN CONCAT(to_hex(r), to_hex(g), to_hex(b))
SELECT rgb_to_hex(0, 0, 255);
0000FF
SQL UDFによるテーブルからの読み込み
SQL UDFの別の一般的な利用法は、検索の体系化です。シンプルな検索の例としては、RGBカラーコードを英語の色の名前に変換するというものが考えられます。
CREATE FUNCTION from_rgb(rgb STRING
COMMENT 'an RGB hex color code')
RETURNS STRING
COMMENT 'Translates an RGB color code into a color name'
RETURN DECODE(rgb, 'FF00FF', 'magenta',
'FF0080', 'rose');
SELECT from_rgb('FF0080');
rose
OK、でも世界には2色以上の色が存在しています。そして、双方向で変換したいと我々は考えています。これらを実現するためには、これらを検索テーブルに格納すべきです。
CREATE TABLE colors(rgb STRING NOT NULL, name STRING NOT NULL);
INSERT INTO colors VALUES
('FF00FF', 'magenta'),
('FF0080', 'rose'),
('BFFF00', 'lime'),
('7DF9FF', 'electric blue');
CREATE OR REPLACE FUNCTION
from_rgb(rgb STRING COMMENT 'an RGB hex color code')
RETURNS STRING
READS SQL DATA SQL SECURITY DEFINER
COMMENT 'Translates an RGB color code into a color name'
RETURN SELECT FIRST(name) FROM colors WHERE rgb = from_rgb.rgb;
SELECT from_rgb(rgb)
FROM VALUES('7DF9FF'),
('BFFF00') AS codes(rgb);
electric blue
lime
ここで新たに導入された概念は以下の通りとなります。
- SQL UDFをREPLACEすることができます。これを行うためには、古い関数とシグネチャが一致している必要があります。関数のシグネチャは、パラメーターの数と型として定義されます。
- この関数はテーブルの情報を検索しますので、任意でREADS SQL DATAを用いて、ドキュメントすることができます。何も宣言しない場合には、SQLコンパイラーは適切な値を導き出しますが、嘘をつくべきではありませんし、CONTAINS SQLを宣言すべきです。
- SQL SECURITY DEFINERは別のオプションの句であり、これはcolorsテーブルにアクセスする関数は、関数のオーナーの認証情報を用いてアクセスすることを知らせています。このため、テーブルのセキュリティを危険に晒すことなしに、外部から関数を実行することができます。
- オーナーの認証情報を用いて関数が実行されるのと同じように、作成時のカレントデータベースを用いてパースされます。
-
rgbはcolorsのカラム名です。from_rgb.rgbとしてパラメーターを指定することで、カラムではなくパラメーターのリファレンスであることを明確にしています。
ここでは、物理プランはどのようなものになるでしょうか？これは、nested loop joinとなるクエリーを実行する外部UDFを使っており、貴重なリソースを消費する酷い方法であることは簡単にわかります。
EXPLAIN SELECT from_rgb(rgb)
FROM VALUES ('7DF9FF'),
('BFFF00') AS codes(rgb);
== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=false
+- Project [first(name)#1322268 AS default.from_rgb(rgb)#1322259]
+- BroadcastHashJoin [rgb#1322261], [rgb#1322266], LeftOuter, BuildRight, false
:- LocalTableScan [rgb#1322261]
+- BroadcastExchange HashedRelationBroadcastMode(ArrayBuffer(input[1, string, false]),false), [id=#1437557]
+- SortAggregate(key=[rgb#1322266], functions=[finalmerge_first(merge first#1322271, valueSet#1322272) AS first(name#1322267)()#1322260])
+- Sort [rgb#1322266 ASC NULLS FIRST], false, 0
+- Exchange hashpartitioning(rgb#1322266, 200), ENSURE_REQUIREMENTS, [id=#1437553]
+- SortAggregate(key=[rgb#1322266], functions=[partial_first(name#1322267, false) AS (first#1322271, valueSet#1322272)])
+- Sort [rgb#1322266 ASC NULLS FIRST], false, 0
+- FileScan parquet default.colors[rgb#1322266,name#1322267]
この場合、Catalystはnested loop joinではなく、broadcast hash joinを選択しました。これは、SQL UDFの中身を理解しているからこそ実現できます。
ここまでは、議論された全てのサンプルでは、単一の値を返却するスカラー値の関数を使用してきました。返却する結果は、あらゆる型、より複雑なstruct、array、mapsの組み合わせでも構いません。これが議論すべき別のタイプのUDF、テーブル値のUDFです。
SQLテーブルUDF
ビューが引数を受け取るケースを考えて見ます！ユーザーが提供する値に依存していたとしても、複雑な述語をカプセル化することができます。SQLテーブルUDFはまさにこのためのものです。別の名前でパラメーター以外を参照できるビューです。
上述のカラーマッピングが一意ではないケースを想定してみます。少なくとも、言語ごとに異なる色の名称を提示します。
INSERT INTO colors VALUES ('BFFF00', 'citron vert');
CREATE OR REPLACE FUNCTION
from_rgb(rgb STRING COMMENT 'an RGB hex color code')
RETURNS TABLE(name STRING COMMENT 'color name')
READS SQL DATA SQL SECURITY DEFINER
COMMENT 'Translates an RGB color code into a color name'
RETURN SELECT name FROM colors WHERE rgb = from_rgb.rgb;
上からわかるように、スカラー関数との唯一の違いはより複雑なRETURNS句です。ビューと異なり、SQL UDFは返却するリレーションのシグネチャの宣言を強制します。
- TABLEはリレーションを返却することを指定します。
- TABLE句には、返却するそれぞれのカラム名とデータ型を含める必要があります。
- オプションで返却するカラムにコメントを追加できます。
ユーザー定義テーブル関数はDBRにとって新しいものです。どのように呼び出すのかを見ていきましょう。
SELECT * FROM from_rgb('7DF9FF');
electric blue
この最もシンプルな形においては、ビューが参照されるのと同じようにテーブル関数が呼び出されます。唯一の違いは、関数の引数を指定する括弧が必要となることです。この関数はリテラルの引数を指定して呼び出されますが、引数にはあらゆる表現、スカラーのサブクエリーを指定することもできます。
しかし、最もパワフルなSQLテーブルUDFの使用法は、joinとくにcorrelated cross joinです。
SELECT rgb, from_rgb.name
FROM VALUES('7DF9FF'),
('BFFF00') AS codes(rgb),
LATERAL from_rgb(codes.rgb);
7DF9FF electric blue
BFFF00 lime
BFFF00 citron vert
ここでは、引数はFROM句で前述の(ラテラル)リレーションを参照しています。新たなLATERALキーワードは、Catalystにこれらのカラムを解決する権限を付与します。また、結果のシグネチャとして定義されるカラムを指定、あるいはオプションでオプションで関数名で評価することで、テーブル関数を参照できることに注意してください。
管理
当然ですが、SQL UDFは既存のGRANT、REVOKE、SHOW、DESCRIBE、DROP文を完全にサポートしています。
詳細を説明すべき文はDESCRIBEです。
DESCRIBE FUNCTION from_rgb;
Function: default.from_rgb
Type: TABLE
Input: rgb STRING
Returns: name STRING
期待する通り、基本的な説明が返却されますが、拡張されたDESCRIBEはより詳細な情報を提供します。
DESCRIBE FUNCTION EXTENDED from_rgb;
Function: default.from_rgb
Type: TABLE
Input: rgb STRING 'an RGB hex color code'
Returns: name STRING 'color name'
Comment: Translates an RGB color code into a color name
Data Access: READS SQL DATA
Configs: spark.sql.datetime.java8API.enabled=true
spark.sql.hive.version=2.3.7
Owner: serge.rielau
Create Time: Wed Sep 08 08:59:53 PDT 2021
Body: SELECT name FROM colors WHERE rgb = from_rgb.rgb
今後の予定
ここで説明したのは、SQL UDFの初期の機能です。将来的な拡張においては、以下のサポートを検討しています。
- SQL PATH、ファイルシステムで行うのと同じように、データベースで関数のライブラリを作成し、他のものをサブスクライブすることができるようになります。
- UDFのオーバーロード。
- デフォルトのパラメーターを持つUDF。
SQL UDFはSQLの使いやすさの改善に向けた大きな一歩であり、この記事で説明したように、様々な方法で活用できます。Databricks SQLや、データエンジニアリングの作業でPhotonを活用して、よりクリエイティブな方法で活用できないかを検討することをお勧めします。こちらのノートブックをトライし、詳細に関してはドキュメントを参照ください。