Databricksのサーバーレス・データウェアハウス、Databricks SQLの2023年の製品進化を紹介するブログシリーズへようこそ。
このパート2では、この1年間に提供されたSQLプログラミングの新機能の数々をご紹介します。 当然のことながら、すべてのSQL開発者は、より生産性を高め、より複雑なシナリオに簡単に取り組みたいと考えています。 これはすべてDatabricksのデータインテリジェンスプラットフォームの一部であり、データウェアハウスとデータレイクの長所を組み合わせたレイクハウスアーキテクチャに基づいて構築されています。
それでは、2023年のSQLプログラミングの目玉機能をご紹介します：
ラテラル カラム エイリアス対応
コーヒーが体に良くないなら、なぜみんな飲むのですか？
ラテラルカラムサポートがそうです。 SQLの原則には反しますが、この機能を使えば、セレクトリスト内のSQL式の結果を、同じセレクトリスト内の次の式で参照することができるので、確かに便利です。 あなたは振り返り、SQLの純粋さの名の下に、式を共有するためだけにサブクエリを押し付けることを長い間強いられていたことを不思議に思うことでしょう。
Before：
SELECT fullname,
upper(fullname),
lower(fullname)
FROM (SELECT name || firstname
FROM persons) AS T(fullname);
After（ラテラル・カラム・エイリアスを使用）：
SELECT name || firstname AS fullname,
upper(fullname),
lower(fullname)
FROM persons;
詳しくはラテラルカラムエイリアスの紹介をご覧ください。
エラー・クラスと SQLSTATEs
Databricksで遭遇するほとんどのエラーは、人間が読めるエラー分類とSQL標準ベースのSQLSTATEを表示します。 これらのエラーメッセージはドキュメント化されており、PythonとScalaの場合、Databricksはエラーメッセージテキストに依存することなくプログラムでエラー状態を処理できるメソッドも提供しています。
例：
from pyspark.errors import PySparkException
try:
spark.sql("SELECT * FROM does_not_exist").show()
except PySparkException as ex:
if (ex.getErrorClass() == "TABLE_OR_VIEW_NOT_FOUND"):
print("I'm so sorry, but I cannot find: " + ex.getMessageParameters()['relationName'])
else:
raise
詳細はDatabricks のエラー処理を参照してください。
一般的なテーブル値関数のサポート
2023年には、テーブル値関数のサポートに多くの改良が加えられました。テーブル関数の呼び出しを一般化し標準化することで、explode()のようなジェネレータ関数を含め、全てのテーブル関数を問い合わせのFROM句で呼び出せるようになりました。LATERAL VIEW構文は必要なくなりました。
Before：
SELECT *,
explode(ARRAY('x' || Y.y1, 'y' || Y.y1)) AS (z1)
FROM VALUES('a'), ('b') AS X(c1)
LATERAL VIEW explode(ARRAY(1, 2)) Y AS y1;
a 1 x1
a 1 y1
a 2 x2
a 2 y2
b 1 x1
b 1 y1
b 2 x2
b 2 y2
After：
SELECT *
FROM VALUES('a'), ('b') AS X(c1),
explode(ARRAY(1, 2)) AS Y(y1),
LATERAL explode(ARRAY('x' || Y.y1, 'y' || Y.y1)) AS Z(z1);
a 1 x1
a 1 y1
a 2 x2
a 2 y2
b 1 x1
b 1 y1
b 2 x2
b 2 y2
詳しくはテーブル値関数呼び出しをご覧ください。
ポリモーフィズムを用いたPython UDFとUDTF
SQL UDFはDatabricks 9で導入され、大成功を収めましたが、Python民が嫉妬し、さらにレベルアップしました！
今ならできます：
- PythonのUDFを作成し、その中に輝くロジックをすべて入れてください。
- SQL 標準 TABLE 構文を使って Python のテーブル UDF にテーブルを渡します。 これはポリモーフィズムと呼ばれ、UDFは渡されたテーブルのシグネチャによって異なる動作をします。
例：
from pyspark.sql.functions import udtf
from pyspark.sql.types import Row
@udtf(returnType="id: int")
class FilterUDTF:
def eval(self, row: Row):
if row["id"] > 5:
yield row["id"],
spark.udtf.register("filter_udtf", FilterUDTF)
SELECT * FROM filter_udtf(TABLE(SELECT * FROM range(10)));
6
7
8
9
Introducing Python User Defined Table Functions, Function invocation | Databricks on AWS, と python_udtf.rst: Table Input Argument をご参照ください。
アンネームド パラメータマーカー
2022年に、SQLクエリがspark.sql()APIなどを使ってSQLに渡されたプレースホルダ変数を参照できるようにするパラメータマーカーを導入しました。つまり、Python、Java、Scalaの値は、キーがパラメータマーカーの名前と一致するマップを使用してSQLに渡されます。これは素晴らしく、同じ引数を繰り返し参照したり、順序を変えて参照したりすることができます。
2023 年には、アンネームド パラメータ・マーカのサポートを拡張しました。現在では、値の配列を渡すことができ、それらは出現順に代入されます。
例：
spark.sql("SELECT ? * ? * ? AS volume", args = { 3, 4, 5 }).show()
+------+
|volume|
+------+
| 60|
+------+
詳しくは無名パラメータマーカーを参照してください。
SQLセッション変数
パラメーターマーカーは素晴らしい。 私たちは彼らを愛しています。 しかし、SQLの結果をdataframesで返さずに、パラメータ・マーカでSQLに返すことができれば、もっといいでしょう。 セッション変数とは、スカラ（テーブルではない）オブジェクトで、SQLセッションのプライベートなものです。
今ならできます：
- 型と初期デフォルト値を持つセッション変数を宣言します。
- SQL 式やクエリの結果に基づいて、1 つ以上の変数を設定します。
- クエリや DML 文の中で変数を参照します。
これは、クエリを分割し、あるクエリから次のクエリに状態を渡すための素晴らしい方法です。
例：
DECLARE var INTEGER DEFAULT 5;
SELECT var;
5
SET VAR var = (SELECT max(c1) * var FROM VALUES(1), (2), (3) AS T(c1));
SELECT var;
15
詳しくは変数をご覧ください。
IDENTIFIER 節
前の2つのハイライトでは、アプリケーションやノートブックから渡された値や、テーブルで検索されたセッション変数を使用してクエリをパラメータ化する方法を紹介しました。しかし、SQLインジェクションに関するXKCDのジョークのネタにされることなく、テーブル名や関数名などの識別子をパラメータ化したいと思いませんか？IDENTIFIER句はまさにそれを可能にします。IDENTIFIER句は、セッション変数内の文字列値や、パラメータマーカで指定された値を、関数、テーブル、カラムの参照として使用するSQL名に変換します。
例：
DECLARE agg = 'max';
DECLARE col = 'c1';
DECLARE tab = 'T';
CREATE TEMPORARY VIEW IDENTIFIER(tab)(c1, c2) AS (VALUES ('a', 'b'), ('c', 'd'));
SELECT IDENTIFIER(agg)(IDENTIFIER(col)) FROM IDENTIFIER(tab);
c
詳しくはIDENTIFIER句を参照してください。
INSERT BY NAME
INSERT BY NAMEは、SQLがなぜワイドテーブル（つまり、多くの列を持つテーブル）を扱うためにそのように生まれなかったのか不思議に思わせる、素晴らしいユーザビリティ機能です。多くの列を扱う場合、INSERTを行う選択リストで列を指定する順番を調べるのが好きな人は手を挙げてください。それとも、insert対象の長い列リストを綴る方が好きですか？そんな人はいません。
そのようなカラムリストを提供し、セレクトリストの順序をチェックし、ダブルチェックする代わりに、Databricksにそれを指示することができます。単にINSERT BY NAMEを実行するだけで、Databricksが選択リストをテーブルのカラムに並べてくれます。
例：
CREATE TABLE T(c1 INT, c2 INT);
INSERT INTO T BY NAME SELECT 1 AS c2, 2 AS c1;
SELECT * FROM T;
2 1
詳細はINSERT INTOを参照してください。
名前付きパラメータ呼び出し
30個の引数を取る関数を書いたとしましょう。しかし今、あなたはその関数を、デフォルトではない最後の引数で呼び出さなければなりません。引数の順番は気にせず、"skip ahead "して、その1つの引数だけを設定してください！引数の順番は気にしないでください。
例：
CREATE FUNCTION my_tan(sin FLOAT, cos FLOAT) RETURN sin / cos;
SELECT my_tan(cos => 0.4, sin => 0.1);
0.25
詳しくはNamed Parameter Invocationを参照してください。
タイムゾーンなしのTIMESTAMP
デフォルトでは、Databricks のタイムスタンプは "with local timezone" です。タイムスタンプを指定すると、Databricks はそれがあなたのロケールのタイムゾーンであると仮定し、UTC に正規化して保存します。タイムスタンプを読み返すと、この変換は元に戻され、問題なく表示されます。しかし、他のユーザが別のタイムゾーンからタイムスタンプを読み返した場合、正規化されたタイムスタンプがそのユーザのタイムゾーンに変換されて表示されます。
タイムスタンプを "そのまま "保存したい場合を除き、これは素晴らしい機能です。TIMESTAMP_NTZは新しいタイプで、時間を額面通りに受け取ります。2024年1月4日の午後2時を指定すると、その時刻を保存します。
例：
SET TIME ZONE 'America/Los_Angeles';
DECLARE local_time TIMESTAMP_LTZ = TIMESTAMP'2023-12-01 12:13:14';
DECLARE any_time TIMESTAMP_NTZ = TIMESTAMP'2023-12-01 12:13:14';
SELECT local_time, any_time;
2023-12-01 12:13:14 2023-12-01 12:13:14
SET TIME ZONE 'America/New_York';
SELECT local_time, any_time;
2023-12-01 15:13:14 2023-12-01 12:13:14
詳しくはTIMESTAMP_NTZの紹介をご覧ください 。
統合クエリのサポート
もちろん、すべてのデータがすでにレイクハウスにあることは承知しています。しかし、もしまだ他の場所にデータを持っている友人がいるのであれば、心配する必要はないと伝えてください。Databricks Unity Catalog に外部テーブルを登録し、Databricks を離れることなく SQL クエリを実行することで、Databricks からこれらのデータにアクセスすることができます。リモートシステムへの接続を登録し、リモートカタログ(別名データベース)をリンクし、コンテンツをクエリするだけです。もちろん、同じクエリ内でローカルテーブルと外部テーブルを混在させることもできます。
例：
CREATE CONNECTION postgresql_connection
TYPE POSTGRESQL
OPTIONS (
host 'qf-postgresql-demo.xxxxxx.us-west-2.rds.amazonaws.com',
port '5432',
user 'postgresql_user',
password 'password123');
CREATE FOREIGN CATALOG pg
USING CONNECTION postgresql_connection
OPTIONS (database 'postgresdb');
SELECT * FROM pg.myschema.t;
詳しくはFederated Queriesをご覧ください。
行レベルのセキュリティと列のマスキング
一部のユーザにテーブルへのアクセスを許可する必要がありますが、テーブルの秘密はすべて見せたくないですか? 行レベルのセキュリティと列のマスキングが必要です。他のユーザやグループにテーブルへのアクセス権を与えながら、そのユーザがどの行を見ることができるかについてのルールを設定することができます。クレジットカード番号の下3桁以外を星に置き換えるなど、PII（個人を特定できる情報）を空白にしたり、難読化することもできます。
行フィルタを追加するには、関数の引数に基づいてユーザに行を見せるかどうかを決定する UDF を作成します。その後、ALTER TABLEを使用してテーブルに行フィルタを追加するか、CREATE TABLEを使用して行フィルタを追加します。
例：
CREATE FUNCTION us_filter(region STRING)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admin'), true, region='US');
CREATE TABLE sales (region STRING, id INT);
ALTER TABLE sales SET ROW FILTER us_filter ON (region);
列マスクを追加するには：
ある型のデータを受け取り、ユーザに基づいてそれを変更し、結果を返すUDFを作成します。そして、テーブルを作成する時やALTER TABLEを使用する時に、その列をマスクします。
例：
CREATE FUNCTION ssn_mask(ssn STRING)
RETURN CASE WHEN is_member('HumanResourceDept') THEN ssn ELSE '***-**-****' END;
CREATE TABLE users (
name STRING,
ssn STRING MASK ssn_mask);
SELECT * FROM users;
Jack ***-**-***
詳しくは、行フィルタと列マスクを参照してください。
GROUP BY ALL および ORDER BY ALL
お待たせしました。美しいレポート・クエリを作成したのに、"MISSING_AGGREGATION "エラーが発生しました。SQL が、すでに列挙したすべてのグループ化列を GROUP BY 節で再度前面に列挙させたためです。
「リストを作りなさい！2回チェックしなさい！」というのは、ある人にとっては素晴らしいアドバイスです。他の人にとってはそうでもありません。
そのため、Databricksにグループ化するカラムをすべて収集するように指示することができます。ついでに、返されたカラムを順番に並べることもできます。
例：
SELECT name, firstname, level, sum(comp) as totalcomp
FROM VALUES('The Cricket', 'Jimmy' , 'Principal Conscience', 2),
('Geppetto' , 'Signore', 'Woodcarver' , 1)
AS emp(name, firstname, level, empid)
NATURAL JOIN VALUES(1, 200, 'salary'),
(1, 100, 'spot' ),
(2, 250, 'salary'),
(2, 120, 'spot' )
AS pay(empid, comp, reason)
GROUP BY ALL
ORDER BY ALL;
Geppetto Signore Woodcarver 300
The Cricket Jimmy Principal Conscience 370
詳細はGROUP BY、ORDERBY を参照してください。
その他のSQL組み込み関数
開発者の人生には2つの確信があります： 蕎麦茶がいくらあっても足りないことと、組み込み関数がいくらあっても足りないことです。datetime型のto_charやto_varcharなど、他の製品との互換性を高めるための様々な関数に加えて、私たちは配列操作関数のセットを大幅に拡張することと、ビットマップ関数とhll_sketch関数のライブラリに重点を置きました。ビットマップ関数は、それぞれ整数に対する異なる形式の問い合わせを高速化することができます。一方、データスケッチ関数は様々な確率的計数機能を実現します。
例：
SELECT mask('AaBb123-&^ % 서울 Ä', lowerchar => 'z', otherchar => 'X');
AzBz123XXXXXXXXXÄ
SELECT sum(num_distinct) AS num_distinct
FROM(SELECT bitmap_bucket_number(val),
bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) FROM VALUES(1), (2), (1), (-1), (5), (0), (5) AS t(val) GROUP BY ALL) AS distinct_vals_by_bucket(bucket, num_distinct);
5
SELECT hll_sketch_estimate(
hll_sketch_agg(col))
FROM VALUES('abc'), ('def'), ('abc'), ('ghi'), ('abc') AS tab(col);
3
詳しくはMask 関数、bitmap_count 関数、to_varchar 関数、sketch ベースの近似識別カウントを参照してください。
Databricks ❤️ SQL
Databricks では SQL をとても愛しており、データウェアハウスに SQL の名前を付けました！そして、最高のデータウェアハウスはレイクハウスであるため、SQLとPythonの両方がDatabricks Intelligent Data Platform全体を通してファーストクラスのエクスペリエンスを持っています。私たちは、お客様がプロジェクトでSQLを使用できるように、上記のような新機能を追加することに興奮しています。
SQLワークロードを、SQL開発者のための優れた環境を備えたハイパフォーマンスなサーバーレスデータウェアハウスに移行したいのであれば、DatabricksSQLが最適ソリューションです。
Databricksを無料でお試しください。
