Databricks SQLにおける新たなビルトイン関数

[New Built\-in Functions for Databricks SQL \- The Databricks Blog](https://www.databricks.com/blog/2023/01/20/new-built-functions-databricks-sql.html)の翻訳です。

:::note warn
本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
:::

ビルトイン関数は、一般的な要件やユースケースにおける値に対する特定の変換処理でSQLのパワーを拡張します。例えば、LOG10[関数](https://docs.databricks.com/sql/language-manual/functions/log10.html)は数値の入力を受け取り、倍精度浮動小数の値としてベース10の対数を返却し、LOWER[関数](https://docs.databricks.com/sql/language-manual/functions/lower.html)は文字列を受け取り、それぞれの文字を小文字に変換した結果を返却します。

皆様のデータウェアハウスワークロードのDatabricksレイクハウスプラットフォームへの移行を簡単にするという[コミットメント](https://www.databricks.com/blog/2021/11/16/evolution-of-the-sql-language-at-databricks-ansi-standard-by-default-and-easier-migrations-from-data-warehouses.html)の一部として、我々は昨年を通じてコアのANSI準拠の標準SQLに、数多くの新たなビルトイン関数を注意深く設計しローンチしてきました。また、DatabricksランタイムにもインテグレーションされているオープンソースApache Sparkコミュニティも、この領域に多大なる貢献をしています。この記事では、これらの新たな関数の有用なサブセットに触れ、サンプルとともに説明し、今後のデータ処理ジャーニーにおいてどれだけ有用なものであるのかを説明します。楽しんでください！

# 文字列処理と要素の検索

このカテゴリーの新たな関数を用いてクイックに文字列を調査、検索するためにDatabricks SQLを活用します。クイックに文字列に部分文字列が[含まれる](https://docs.databricks.com/sql/language-manual/functions/contains.html)のかを確認し、[長さ](https://docs.databricks.com/sql/language-manual/functions/len.html)を調査し、文字列を[分割](https://docs.databricks.com/sql/language-manual/functions/split_part.html)し、[プレフィックス](https://docs.databricks.com/sql/language-manual/functions/startswith.html)や[サフィックス](https://docs.databricks.com/sql/language-manual/functions/endswith.html)をチェックすることができます。

```sql: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
```

パターンに対して文字列を比較するための[正規表現オペレーション](https://docs.databricks.com/sql/language-manual/functions/regexp_instr.html)、特定のフォーマットを用いて[数値](https://docs.databricks.com/sql/language-manual/functions/to_number.html)や[URL](https://docs.databricks.com/sql/language-manual/functions/url_encode.html)パターンを変換するための特別な関数を活用します。

```sql:SQL
> 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
```

# 数字とタイムスタンプの比較

[ビットを抽出](https://docs.databricks.com/sql/language-manual/functions/bit_get.html)することで詳細に踏み込み、整数値や小数に対して条件付きロジックを適用します。オプションのターゲットスケールを用いた小数の[切り上げ](https://docs.databricks.com/sql/language-manual/functions/ceil.html)、[切り下げ](https://docs.databricks.com/sql/language-manual/functions/floor.html)や`NULL`値をサポートしている数値の[等価性](https://docs.databricks.com/sql/language-manual/functions/equal_null.html)を比較します。

```sql:SQL
> 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](https://docs.databricks.com/sql/language-manual/functions/cast.html)入力表現や、[現在の日付](https://docs.databricks.com/sql/language-manual/functions/curdate.html)や日付やタイムスタンプの[加算](https://docs.databricks.com/sql/language-manual/functions/dateadd.html)、[減算](https://docs.databricks.com/sql/language-manual/functions/timestampdiff.html)を行います。

```sql:SQL
> 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](https://docs.databricks.com/sql/language-manual/data-types/array-type.html)、[struct](https://docs.databricks.com/sql/language-manual/data-types/struct-type.html)、[map](https://docs.databricks.com/sql/language-manual/data-types/map-type.html)型を持つ構造化、準構造化データに対して洗練されたクエリーを実行します。[array](https://docs.databricks.com/sql/language-manual/functions/array.html)コンストラクタを用いて新たな配列値を構成するか、既存の配列に[特定の値が含まれるか](https://docs.databricks.com/sql/language-manual/functions/array_contains.html)、それらの[位置](https://docs.databricks.com/sql/language-manual/functions/array_position.html)がどこであるのかを調査します。[配列にいくつの要素があるのか](https://docs.databricks.com/sql/language-manual/functions/array_size.html)をチェックし、[インデックス](https://docs.databricks.com/sql/language-manual/functions/element_at.html)を用いて特定の[要素](https://docs.databricks.com/sql/language-manual/functions/get.html)を抽出します。

```sql:SQL
-- 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](https://docs.databricks.com/sql/language-manual/functions/map.html)コンストラクタを用い、必要に応じてあとで[値を検索](https://docs.databricks.com/sql/language-manual/functions/map_contains_key.html)します。作成すると、これらを[結合](https://docs.databricks.com/sql/language-manual/functions/map_concat.html)したり、配列として[キー](https://docs.databricks.com/sql/language-manual/functions/map_keys.html)や[値](https://docs.databricks.com/sql/language-manual/functions/map_values.html)を抽出します。

```sql:SQL
> 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](https://docs.databricks.com/sql/language-manual/functions/try_add.html)、[try\_subtract](https://docs.databricks.com/sql/language-manual/functions/try_subtract.html)、[try\_subtract](https://docs.databricks.com/sql/language-manual/functions/try_subtract.html)、[try\_divide](https://docs.databricks.com/sql/language-manual/functions/try_divide.html)を見てみましょう。また、カスタムフォーマットのオプションを用いて、[キャスト](https://docs.databricks.com/sql/language-manual/functions/try_cast.html)を実行し、[合計](https://docs.databricks.com/sql/language-manual/functions/try_sum.html)や[平均](https://docs.databricks.com/sql/language-manual/functions/try_avg.html)を計算し、[数値](https://docs.databricks.com/sql/language-manual/functions/try_to_number.html)や[タイムスタンプ](https://docs.databricks.com/sql/language-manual/functions/try_to_timestamp.html)の値を安全に[変換](https://docs.databricks.com/sql/language-manual/functions/try_to_binary.html)することができます。

```sql:SQL
> 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
```

# 新たな方法による値のグループの集計

新たな*ビルトイン集計関数*を用いて、値のグループに関する質問を尋ねることでデータドリブンの意思決定を行いましょう。例えば、グループ内の[任意の値](https://docs.databricks.com/sql/language-manual/functions/any_value.html)を返却し、グループを[配列](https://docs.databricks.com/sql/language-manual/functions/array_agg.html)に結合し、ヒストグラムを計算することができます。また、グループの[中央値](https://docs.databricks.com/sql/language-manual/functions/median.html)や[モード](https://docs.databricks.com/sql/language-manual/functions/mode.html)をクエリーし、任意の[パーセンタイル](https://docs.databricks.com/sql/language-manual/functions/percentile_cont.html)を検索することで詳細に踏み込むことができます。

```sql:SQL
> 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`である[グループ](https://docs.databricks.com/sql/language-manual/functions/regr_count.html)の[値](https://docs.databricks.com/sql/language-manual/functions/regr_avgy.html)に対する[質問](https://docs.databricks.com/sql/language-manual/functions/regr_avgx.html)を行う役に立ちます。

```sql:SQL
-- 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`句を用いて[ウィンドウ関数](https://docs.databricks.com/sql/language-manual/sql-ref-window-functions.html)としてこれらを呼び出すことができます。

# 暗号の活用

格納データを[暗号化](https://docs.databricks.com/sql/language-manual/functions/aes_encrypt.html)し、必要な際に[復号化](https://docs.databricks.com/sql/language-manual/functions/aes_decrypt.html)することでアクセスを保護しましょう。これらの関数は暗号化されたデータを変換するために[Advanced Encryption Standard \(AES\)](https://en.wikipedia.org/wiki/Advanced_Encryption_Standard)を活用します。

```sql:SQL
> SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop'));
  4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn

> SELECT cast(aes_decrypt(unbase64('4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn'),
                          'abcdefghijklmnop') AS STRING);
  Spark
```

# 自己観察の適用

SQLを用いてDatabricksのクラスターや設定のプロパティをプログラムからクエリーしましょう。例えば、Databricks SQLやDatabricksランタイム環境の[現在のバージョン](https://docs.databricks.com/sql/language-manual/functions/current_version.html)を確認することができます。また、現在のユーザーが参照できる[Databricksシークレットサービス](https://docs.databricks.com/security/secrets/index.html)で、これまでに作成された[シークレットキーの一覧](https://docs.databricks.com/sql/language-manual/functions/list_secrets.html)を返却し、スコープとキーを用いて[特定のシークレットの値を抽出](https://docs.databricks.com/sql/language-manual/functions/secret.html)するためにSQLを活用することができます。

```sql: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](https://www.databricks.com/blog/2019/12/05/processing-geospatial-data-at-scale-with-databricks.html)」をご覧ください。
![](https://cms.databricks.com/sites/default/files/inline-images/db-464-blog-img-1.jpg)
*これはニューヨークのタクシー降車位置の可視化であり、セルの色は集計カウントを示しています。*

現在、Databricksでは[H3](https://www.uber.com/blog/h3/)セルを操作する地理空間関数の新たなコレクションをサポートしています。それぞれのH3セルは、惑星のユニークな空間領域をいくつかの解像度で表現し、BIGINTあるいは16進数のSTRINGとして表現されるユニークなセルIDを持っています。これらのセルの境界は、JSONを用いたシンプルな地理空間機能を表現するように設計された標準である[GeoJSON](https://en.wikipedia.org/wiki/GeoJSON)、文字列を用いた地理空間データ表現のオープンなテキストベースのフォーマットである[WKT](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry)(そしてバイナリーバージョンである[WKB](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary)も)を含むオープンなフォーマットに変換することができます。

```sql:SQL
-- 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セルを[クエリー](https://docs.databricks.com/sql/language-manual/functions/h3_distance.html)することで、ポイント間の距離を調べることができます。これらのH3セルのセットは、原点セルの*k-ring*と呼ばれます。入力H3セルIDを[同等](https://docs.databricks.com/sql/language-manual/functions/h3_h3tostring.html)の16進数[文字列表現](https://docs.databricks.com/sql/language-manual/functions/h3_stringtoh3.html)と相互変換することが可能です。

```sql:SQL
> 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](https://docs.databricks.com/sql/language-manual/functions/h3_polyfillash3.html)か[STRING](https://docs.databricks.com/sql/language-manual/functions/h3_polyfillash3string.html))の配列を計算することができます。[try\_ バージョン](https://docs.databricks.com/sql/language-manual/functions/h3_try_polyfillash3.html)はエラーではなくNULLを返却します。

```sql:SQL
-- 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セルの[親](https://docs.databricks.com/sql/language-manual/functions/h3_toparent.html)のH3セルや[子供](https://docs.databricks.com/sql/language-manual/functions/h3_tochildren.html)のH3セルを計算したり、あるH3セルが他セルの[子供](https://docs.databricks.com/sql/language-manual/functions/h3_ischildof.html)であるかどうかをチェックすることができます。H3の制御を通じてH3セルやポイントの配列としてポリゴンを表現することで、オリジナルの地理オブジェクトよりも非常に効率的な空間分析オペレーションをH3セルに対して実行することができます。また、[任意の規模における空間分析](https://www.databricks.com/blog/2022/12/13/spatial-analytics-any-scale-h3-and-photon.html)をどのように実施するのか、[H3を用いた空間分析のスーパーチャージ](https://www.databricks.com/blog/2023/01/12/supercharging-h3-geospatial-analytics.html)に関する最近のブログ記事を参照してください。

最後になりますが、入力したBIGINTやSTRINGの値を用いて、H3セルに適正なH3セルIDに対応しているのかを[検証](https://docs.databricks.com/sql/language-manual/functions/h3_validate.html)することができます。

```sql:SQL
> 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モード](https://www.databricks.com/blog/2021/11/16/evolution-of-the-sql-language-at-databricks-ansi-standard-by-default-and-easier-migrations-from-data-warehouses.html)の誕生によって、Databricks SQLに標準への準拠と容易なマイグレーションがもたらされ、パフォーマンスに関してはすでに[世界記録を樹立](https://qiita.com/taka_yayoi/items/b9b25d2050685706a20b)しています。様々なビルトイン関数の追加によって、SQLワークロードはレイクハウスにおける新たな表現可能性を手に入れることができました。

文字列を切り貼りし、値を集計し、日付を操作し、[位置情報](https://www.databricks.com/blog/2019/12/05/processing-geospatial-data-at-scale-with-databricks.html)を分析する等してみてください。これらのビルトインでいくつかの機能がかけていると感じられたのであれば、自分のロジックを定義し、ビルトインと同じように呼び出すことができる[Pythonユーザー定義関数](https://qiita.com/taka_yayoi/items/8d936d9d58d415701b1c)や[SQLユーザー定義関数](https://qiita.com/taka_yayoi/items/80862e2f8d01e4306a79)をチェックしてみてください。

Databricks SQLを使っていただきありがとうございます。ハッピークエリーを！


### Databricks 無料トライアル

[Databricks 無料トライアル](https://databricks.com/jp/try-databricks)