New Built-in Functions for Databricks SQL - The Databricks Blogの翻訳です。
本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
ビルトイン関数は、一般的な要件やユースケースにおける値に対する特定の変換処理でSQLのパワーを拡張します。例えば、LOG10関数は数値の入力を受け取り、倍精度浮動小数の値としてベース10の対数を返却し、LOWER関数は文字列を受け取り、それぞれの文字を小文字に変換した結果を返却します。
皆様のデータウェアハウスワークロードのDatabricksレイクハウスプラットフォームへの移行を簡単にするというコミットメントの一部として、我々は昨年を通じてコアのANSI準拠の標準SQLに、数多くの新たなビルトイン関数を注意深く設計しローンチしてきました。また、DatabricksランタイムにもインテグレーションされているオープンソースApache Sparkコミュニティも、この領域に多大なる貢献をしています。この記事では、これらの新たな関数の有用なサブセットに触れ、サンプルとともに説明し、今後のデータ処理ジャーニーにおいてどれだけ有用なものであるのかを説明します。楽しんでください！
文字列処理と要素の検索
このカテゴリーの新たな関数を用いてクイックに文字列を調査、検索するためにDatabricks SQLを活用します。クイックに文字列に部分文字列が含まれるのかを確認し、長さを調査し、文字列を分割し、プレフィックスやサフィックスをチェックすることができます。
> SELECT contains('SparkSQL', 'SQL'),
contains('SparkSQL', 'Spork')
true, false
> SELECT len('Spark SQL ');
10
> SELECT split_part('Hello,world,!', ',', 1);
Hello
> SELECT startswith('SparkSQL', 'Spark'),
endswith('SparkSQL', 'dataframes');
true, false
パターンに対して文字列を比較するための正規表現オペレーション、特定のフォーマットを用いて数値やURLパターンを変換するための特別な関数を活用します。
> WITH w AS (SELECT
'Steven Jones and Stephen Smith' AS target,
'Ste(v|ph)en' AS pattern)
-- Return the first substring that matches the pattern.
SELECT regexp_substr(target, pattern) FROM w;
Steven
-- This format string expects:
-- * an optional sign at the beginning,
-- * followed by a dollar sign,
-- * followed by a number between 3 and 6 digits long,
-- * thousands separators,
-- * up to two digits beyond the decimal point.
> SELECT to_number('-$12,345.67', 'S$999,099.99');
-12345.67
-- This format string produces five characters before the decimal point and two after.
> SELECT '(' || to_char(123, '99999.99') || ')';
(123.00)
> SELECT url_decode('http%3A%2F%2Fspark.apache.org%2Fpath%3Fquery%3D1');
http://spark.apache.org/path?query=1
数字とタイムスタンプの比較
ビットを抽出することで詳細に踏み込み、整数値や小数に対して条件付きロジックを適用します。オプションのターゲットスケールを用いた小数の切り上げ、切り下げやNULL値をサポートしている数値の等価性を比較します。
> SELECT bit_get(23Y, 3),
bit_get(23Y, 0);
0, 1
> SELECT ceil(5.4),
ceil(-12.345, 1);
6, -12.3
> SELECT floor(3345.1, -2);
3300
> SELECT equal_null(2, 2),
equal_null(2, 1),
equal_null(NULL, NULL),
equal_null(NULL, 1);
true, false, true, false
新たなタイプ付け変換処理を用いて一時的な値を取り扱います。INTERVALデータ型の一つとのCast入力表現や、現在の日付や日付やタイムスタンプの加算、減算を行います。
> SELECT cast(INTERVAL '12:04.9900' MINUTE TO SECOND AS STRING);
INTERVAL '12:04.99' MINUTE TO SECOND
> SELECT curdate()
2022-12-21
-- March 31, 2022 minus 1 month yields February 28, 2022.
> SELECT dateadd(MONTH, -1, TIMESTAMP'2022-03-31 00:00:00');
2022-02-28 00:00:00.000000
-- One month has passed even though it's not the end of the month yet because
-- the day and time line up.
> SELECT datediff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 12:00:00');
1
array、struct、mapの取り扱い
array、struct、map型を持つ構造化、準構造化データに対して洗練されたクエリーを実行します。arrayコンストラクタを用いて新たな配列値を構成するか、既存の配列に特定の値が含まれるか、それらの位置がどこであるのかを調査します。配列にいくつの要素があるのかをチェックし、インデックスを用いて特定の要素を抽出します。
-- This creates an array of integers.
> SELECT array(1, 2, 3);
[1,2,3]
> SELECT array_contains(array(1, 2, 3), 2),
array_position(array(3, 2, 1, 4, 1), 1);
true, 3
> SELECT array_size(array(1, NULL, 3, NULL));
4
> SELECT get(arr, 0), get(arr, 2), arr[2] FROM VALUES(array(1, 2, 3)) AS T(arr);
1, 3, 3
> SELECT element_at(array(1, 2, 3), 2),
try_element_at(array(1, 2, 3), 5);
2, NULL
Mapは値に関連づけられるユニークなキーの挿入をサポートしているパワフルなデータ型であり、あとで効率的に抽出を行うことができます。新たなmapの値を作成するためにmapコンストラクタを用い、必要に応じてあとで値を検索します。作成すると、これらを結合したり、配列としてキーや値を抽出します。
> SELECT map(1.0, '2', 3.0, '4');
{1.0 -> 2, 3.0 -> 4}
> SELECT map_contains_key(map(1, 'a', 2, 'b'), 2);
true
> SELECT map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));
{1 -> a, 2 -> b, 3 -> c}
> SELECT map_keys(map(1, 'a', 2, 'b')),
map_values(map(1, 'a', 2, 'b'));
[1,2], [a,b]
エラーに対応した計算処理の実行
新たなエラー対応関数を用いることで、長い時間実行されるETLパイプラインがヘラーを返却することを防ぐつつも、ANSIモードの標準SQLのメリットを享受することができます。このような関数のそれぞれは例外を引き起こすのではなく、NULLを返却します。例えば、try_add、try_subtract、try_subtract、try_divideを見てみましょう。また、カスタムフォーマットのオプションを用いて、キャストを実行し、合計や平均を計算し、数値やタイムスタンプの値を安全に変換することができます。
> SELECT try_divide(3, 2), try_divide(3 , 0);
1.5, NULL
> SELECT try_cast('10' AS INT);
10
> SELECT try_cast('a' AS INT);
NULL
> SELECT try_sum(col) FROM VALUES (5), (10), (15) AS tab(col);
30
> SELECT try_avg(col) FROM VALUES (5e37::DECIMAL(38, 0)), (5e37::DECIMAL(38, 0)) AS tab(col);
NULL
-- A plus sign is optional in the format string, and so are fractional digits.
> SELECT try_to_number('$345', 'S$999,099.99');
345.00
-- The number format requires at least three digits.
> SELECT try_to_number('$45', 'S$999,099.99');
NULL
新たな方法による値のグループの集計
新たなビルトイン集計関数を用いて、値のグループに関する質問を尋ねることでデータドリブンの意思決定を行いましょう。例えば、グループ内の任意の値を返却し、グループを配列に結合し、ヒストグラムを計算することができます。また、グループの中央値やモードをクエリーし、任意のパーセンタイルを検索することで詳細に踏み込むことができます。
> SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);
10
> SELECT array_agg(col) FROM VALUES (1), (2), (NULL), (1) AS tab(col);
[1,2,1]
> SELECT histogram_numeric(col, 5) FROM VALUES (0), (1), (2), (10) AS tab(col);
[{"x":0,"y":1.0},{"x":1,"y":1.0},{"x":2,"y":1.0},{"x":10,"y":1.0}]
> SELECT median(DISTINCT col) FROM VALUES (1), (2), (2), (3), (4), (NULL) AS tab(col);
2.5
-- Return the median, 40%-ile and 10%-ile.
> SELECT percentile_cont(array(0.5, 0.4, 0.1)) WITHIN GROUP (ORDER BY col)
FROM VALUES (0), (1), (2), (10) AS tab(col);
[1.5, 1.2000000000000002, 0.30000000000000004]
新たなregr_*関数ファミリーは、入力エクスプレッションがNOT NULLであるグループの値に対する質問を行う役に立ちます。
-- Returns the intercept of the univariate linear regression line.
> SELECT regr_intercept(y, x) FROM VALUES (1, 2), (2, 3), (2, 3), (null, 4), (4, null) AS T(y, x);
0.7777777777777779
-- Returns the coefficient of determination from the values.
> SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, 3), (2, 3), (null, 4), (4, null) AS T(y, x);
1
-- Returns the sum of squares of one of the input expression values of a group.
> SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, 3), (2, 3), (null, 4), (4, null) AS T(y, x);
0.6666666666666666
また、OVER句を用いてウィンドウ関数としてこれらを呼び出すことができます。
暗号の活用
格納データを暗号化し、必要な際に復号化することでアクセスを保護しましょう。これらの関数は暗号化されたデータを変換するためにAdvanced Encryption Standard (AES)を活用します。
> SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop'));
4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn
> SELECT cast(aes_decrypt(unbase64('4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn'),
'abcdefghijklmnop') AS STRING);
Spark
自己観察の適用
SQLを用いてDatabricksのクラスターや設定のプロパティをプログラムからクエリーしましょう。例えば、Databricks SQLやDatabricksランタイム環境の現在のバージョンを確認することができます。また、現在のユーザーが参照できるDatabricksシークレットサービスで、これまでに作成されたシークレットキーの一覧を返却し、スコープとキーを用いて特定のシークレットの値を抽出するためにSQLを活用することができます。
> SELECT current_version().dbsql_version;
2022.25
> SELECT current_version();
{ NULL, 2022.25, ..., ... }
> SELECT * FROM list_secrets();
scope key
------------ ---------------
secrets.r.us theAnswerToLife
> SELECT secret('secrets.r.us', 'theAnswerToLife');
fortyTwo
地理空間レイクハウスの構築
大規模な地理空間データセットを効率的に処理、クエリーしましょう。このセクションでは、新たな方法でデータを整理、処理するための新SQL関数を、さまざまな入力データタイプを用いてどのように関数を呼び出すのかをサンプルとともに説明します。詳細なバックグラウンドについては、ブログ記事「Processing Geospatial Data at Scale With Databricks」をご覧ください。
これはニューヨークのタクシー降車位置の可視化であり、セルの色は集計カウントを示しています。
現在、DatabricksではH3セルを操作する地理空間関数の新たなコレクションをサポートしています。それぞれのH3セルは、惑星のユニークな空間領域をいくつかの解像度で表現し、BIGINTあるいは16進数のSTRINGとして表現されるユニークなセルIDを持っています。これらのセルの境界は、JSONを用いたシンプルな地理空間機能を表現するように設計された標準であるGeoJSON、文字列を用いた地理空間データ表現のオープンなテキストベースのフォーマットであるWKT(そしてバイナリーバージョンであるWKBも)を含むオープンなフォーマットに変換することができます。
-- This returns the center of the input H3 cell as a point in GeoJSON or WKB or
-- WKT format.
> SELECT h3_centerasgeojson(599686042433355775)
{"type":"Point","coordinates":[-121.97637597255,37.345793375368]}
原点のセルから距離(グリッド)kにあるH3セルをクエリーすることで、ポイント間の距離を調べることができます。これらのH3セルのセットは、原点セルのk-ringと呼ばれます。入力H3セルIDを同等の16進数文字列表現と相互変換することが可能です。
> SELECT h3_distance('85283447fffffff', '8528340ffffffff')
2
> SELECT h3_h3tostring(599686042433355775)
85283473fffffff
-- Returns an array of H3 cells that form a hollow hexagonal ring centered at the
-- origin H3 cell and that are at grid distance k from the origin H3 cell.
> SELECT h3_hexring('85283473fffffff', 1) [8528340bfffffff,85283447fffffff,8528347bfffffff,85283463fffffff,85283477fffffff,8528340ffffffff]
さらに、入力地理領域に含まれる6角形や5角形に対応するH3セルID(BIGINTかSTRING)の配列を計算することができます。try_ バージョンはエラーではなくNULLを返却します。
-- This is a simple example where the input is a triangle in hexadecimal WKB format.
> SELECT h3_polyfillash3(unhex('0103000000010000000400000050fc1873d79a5ec0d0d556ec2fe342404182e2c7988f5dc0f46c567dae064140aaf1d24d628052c05e4bc8073d5b444050fc1873d79a5ec0d0d556ec2fe34240'), 2)
[586146350232502271,586147449744130047,586198577034821631,586152397546455039,586199676546449407,586153497058082815,586142501941805055,586201325813891071]
指定した解像度で入力H3セルの親のH3セルや子供のH3セルを計算したり、あるH3セルが他セルの子供であるかどうかをチェックすることができます。H3の制御を通じてH3セルやポイントの配列としてポリゴンを表現することで、オリジナルの地理オブジェクトよりも非常に効率的な空間分析オペレーションをH3セルに対して実行することができます。また、任意の規模における空間分析をどのように実施するのか、H3を用いた空間分析のスーパーチャージに関する最近のブログ記事を参照してください。
最後になりますが、入力したBIGINTやSTRINGの値を用いて、H3セルに適正なH3セルIDに対応しているのかを検証することができます。
> SELECT h3_toparent('85283473fffffff', 0)
8029fffffffffff
> SELECT h3_tochildren(599686042433355775, 6)
[604189641121202175,604189641255419903,604189641389637631,604189641523855359,604189641658073087,604189641792290815,604189641926508543]
> SELECT h3_ischildof(608693241318998015, 599686042433355775)
true
> SELECT h3_validate(599686042433355776)
[H3_INVALID_CELL_ID] 599686042433355776 is not a valid H3 cell ID
> SELECT h3_isvalid(599686042433355776)
false
> SELECT h3_try_validate(599686042433355776)
NULL
Databricks SQLであなたのやりたいことの全てが実現できます
以前のANSIモードの誕生によって、Databricks SQLに標準への準拠と容易なマイグレーションがもたらされ、パフォーマンスに関してはすでに世界記録を樹立しています。様々なビルトイン関数の追加によって、SQLワークロードはレイクハウスにおける新たな表現可能性を手に入れることができました。
文字列を切り貼りし、値を集計し、日付を操作し、位置情報を分析する等してみてください。これらのビルトインでいくつかの機能がかけていると感じられたのであれば、自分のロジックを定義し、ビルトインと同じように呼び出すことができるPythonユーザー定義関数やSQLユーザー定義関数をチェックしてみてください。
Databricks SQLを使っていただきありがとうございます。ハッピークエリーを！