2023年のDatabricksのSQL新機能ハイライト
によって Serge Rielau による投稿
Archived. This article has not been updated since the publish date above. The dynamic nature of information means that previously accurate content can become outdated or even obsolete over time. Readers are advised to exercise due diligence and cross-check any information found in this blog post before making decisions or adopting any practices based on said information.
Databricksのサーバーレス・データウェアハウス、Databricks SQLの2023年の製品進化を紹介するブログシリーズへようこそ。
このパート2では、この1年間に提供されたSQLプログラミングの新機能の数々をご紹介します。 当然のことながら、すべてのSQL開発者は、より生産性を高め、より複雑なシナリオに簡単に取り組みたいと考えています。 これはすべてDatabricksのデータインテリジェンスプラットフォームの一部であり、データウェアハウスとデータレイクの長所を組み合わせたレイク ハウスアーキテクチャに基づいて構築されています。
それでは、2023年のSQLプログラミングの目玉機能をご紹介します：
コーヒーが体に良くないなら、なぜみんな飲むのですか？
ラテラルカラムサポートがそうです。 SQLの原則には反しますが、この機能を使えば、セレクトリスト内のSQL式の結果を、同じセレクトリスト内の次の式で参照することができるので、確かに便利です。 あなたは振り返り、SQLの純粋さの名の下に、式を共有するためだけにサブクエリを押し付けることを長い間強いられていたことを不思議に思うことでしょう。
Before：
After（ラテラル・カラム・エイリアスを使用）：
詳しくはラテラルカラムエイリアスの紹介をご覧ください。
Databricksで遭遇するほとんどのエラーは、人間が読めるエラー分類とSQL標準ベースのSQLSTATEを表示します。 これらのエラーメッセージはドキュメント化されており、PythonとScalaの場合、Databricksはエラーメッセージテキストに依存することなくプログラムでエラー状態を処理できるメソッドも提供しています。
例：
詳細はDatabricks のエラー処理を参照してください。
2023年には、テーブル値関数のサポートに多くの改良が加えられました。テーブル関数の呼び出しを一般化し標準化することで、explode()のようなジェネレータ関数を含め、全てのテーブル関数を問い合わせのFROM句で呼び出せるようになりました。LATERAL VIEW構文は必要なくなりました。
Before：
After：
詳しくはテーブル値関数呼び出しをご覧ください。
SQL UDFはDatabricks 9で導入され、大成功を収めましたが、Python民が嫉妬し、さらにレベルアップしました！
今ならできます：
例：
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 年には、アンネームド パラメータ・マーカのサポートを拡張しました。現在では、値の配列を渡すことができ、それらは出現順に代入されます。
例：
詳しくは無名パラメータマーカーを参照してください。
パラメーターマーカーは素晴らしい。 私たちは彼らを愛しています。 しかし、SQLの結果をdataframesで返さずに、パラメータ・マーカでSQLに返すことができれば、もっといいでしょう。 セッション変数とは、スカラ（テーブルではない）オブジェクトで、SQLセッションのプライベートなものです。
今ならできます：
これは、クエリを分割し、あるクエリから次のクエリに状態を渡すための素晴らしい方法です。
例：
詳しくは変数をご覧ください。
前の2つのハイライトでは、アプリケーションやノートブックから渡された値や、テーブルで検索されたセッション変数を使用してクエリをパラメータ化する方法を紹介しました。しかし、SQLインジェクションに関するXKCDのジョークのネタにされることなく、テーブル名や関数名などの識別子をパラメータ化したいと思いませんか？IDENTIFIER句はまさにそれを可能にします。IDENTIFIER句は、セッション変数内の文字列値や、パラメータマーカで指定された値を、関数、テーブル、カラムの参照として使用するSQL名に変換します。
例：
詳しくはIDENTIFIER句を参照してください。
INSERT BY NAMEは、SQLがなぜワイドテーブル（つまり、多くの列を持つテーブル）を扱うためにそのように生まれなかったのか不思議に思わせる、素晴らしいユーザビリティ機能です。多くの列を扱う場合、INSERTを行う選択リストで列を指定する順番を調べるのが好きな人は手を挙げてください。それとも、insert対象の長い列リストを綴る方が好きですか？そんな人はいません。
そのようなカラムリストを提供し、セレクトリストの順序をチェックし、ダブルチェックする代わりに、Databricksにそれを指示することができます。単にINSERT BY NAMEを実行するだけで、Databricksが選択リストをテーブルのカラムに並べてくれます。
例：
詳細はINSERT INTOを参照してください。
30個の引数を取る関数を書いたとしましょう。しかし今、あなたはその関数を、デフォルトではない最後の引数で呼び出さなければなりません。引数の順番は気にせず、"skip ahead "して、その1つの引数だけを設定してください！引数の順番は気にしないでください。
例：
詳しくはNamed Parameter Invocationを参照してください。
デフォルトでは、Databricks のタイムスタンプは "with local timezone" です。タイムスタンプを指定すると、Databricks はそれがあなたのロケールのタイムゾーンであると仮定し、UTC に正規化して保存します。タイムスタンプを読み返すと、この変換は元に戻され、問題なく表示されます。しかし、他のユーザが別のタイムゾーンからタイムスタンプを読み返した場合、正規化されたタイムスタンプがそのユーザのタイムゾーンに変換されて表示されます。
タイムスタンプを "そのまま "保存したい場合を除き、これは素晴らしい機能です。TIMESTAMP_NTZは新しいタイプで、時間を額面通りに受け取ります。2024年1月4日の午後2時を指定すると、その時刻を保存します。
例：
詳しくはTIMESTAMP_NTZの紹介をご覧ください。
もちろん、すべてのデータがすでにレイクハウスにあることは承知しています。しかし、もしまだ他の場所にデータを持っている友人がいるのであれば、心配する必要はないと伝えてください。Databricks Unity Catalog に外部テーブルを登録し、Databricks を離れることなく SQL クエリを実行することで、Databricks からこれらのデータにアクセスすることができます。リモートシステムへの接続を登録し、リモートカタログ(別名データベース)をリンクし、コンテンツをクエリするだけです。もちろん、同じクエリ内でローカルテーブルと外部テーブルを混在させることもできます。
例：
詳しくはFederated Queriesをご覧ください。
一部のユーザにテーブルへのアクセスを許可する必要がありますが、テーブルの秘密はすべて見せたくないですか? 行レベルのセキュリティと列のマスキングが必要です。他のユーザやグループにテーブルへのアクセス権を与えながら、そのユーザがどの行を見ることができるかについてのルールを設定することができます。クレジットカード番号の下3桁以外を星に置き換えるなど、PII（個人を特定できる情報）を空白にしたり、難読化することもできます。
行フィルタを追加するには、関数の引数に基づいてユーザに行を見せるかどうかを決定する UDF を作成します。その後、ALTER TABLEを使用してテーブルに行フィルタを追加するか、CREATE TABLEを使用して行フィルタを追加します。
例：
列マスクを追加するには：
ある型のデータを受け取り、ユーザに基づいてそれを変更し、結果を返すUDFを作成します。そして、テーブルを作成する時やALTER TABLEを使用する時に、その列をマスクします。
例：
詳しくは、行フィルタと列マスクを参照してください。
お待たせしました。美しいレポート・クエリを作成したのに、"MISSING_AGGREGATION "エラーが発生しました。SQL が、すでに列挙したすべてのグループ化列を GROUP BY 節で再度前面に列挙させたためです。
「リストを作りなさい！2回チェックしなさい！」というのは、ある人にとっては素晴らしいアドバイスです。他の人にとってはそうでもありません。
そのため、Databricksにグループ化するカラムをすべて収集するように指示することができます。ついでに、返されたカラムを順番に並べることもできます。
例：
詳細はGROUP BY、ORDERBY を参照してください。
開発者の人生には2つの確信があります： 蕎麦茶がいくらあっても足りないことと、組み込み関数がいくらあっても足りないことです。datetime型のto_charやto_varcharなど、他の製品との互換性を高めるための様々な関数に加えて、私たちは配列操作関数のセットを大幅に拡張することと、ビットマップ関数とhll_sketch関数のライブラリに重点を置きました。ビットマップ関数は、それぞれ整数に対する異なる形式の問い合わせを高速化することができます。一方、データスケッチ関数は様々な確率的計数機能を実現します。
例：
詳しくはMask 関数、bitmap_count 関数、to_varchar 関数、sketch ベースの近似識別カウントを参照してください。
Databricks では SQL をとても愛しており、データウェアハウスに SQL の名前を付けました！そして、最高のデータウェアハウスはレイクハウスであるため、SQLとPythonの両方がDatabricks Intelligent Data Platform全体を通してファーストクラスのエクスペリエンスを持っています。私たちは、お客様がプロジェクトでSQLを使用できるように、上記のような新機能を追加することに興奮しています。
SQLワークロードを、SQL開発者のための優れた環境を備えたハイパフォーマンスなサーバーレスデータウェアハウスに移行したいのであれば、DatabricksSQLが最適ソリューションです。
ブログを購読して、最新の投稿を受信トレイにお届けします。