DatabricksでのSQLスクリプトのサポートの紹介：パート2

SQLスクリプティング発表ブログシリーズのパート2では、パート1で議論した管理タスク、つまりテーブルのすべてのSTRING列に大文字小文字を区別しないルールを適用する方法を詳しく見ていきます。その例をステップバイステップで説明し、使用された機能を説明し、単一のテーブルを超えて全体のスキーマをカバーするように拡張します。

このノートブックを使用することでハンズオン形式で進めることができます。

スキーマ内のすべてのテーブルのすべてのテキストフィールドの照合順序を変更する

Databricksは、言語に対応した大文字小文字を区別しない、アクセントを区別しないコレーションを幅広くサポートしています。新しいテーブルや列でこの機能を使うのは簡単です。しかし、既存のシステムでupper()やlower()を述語で使っていて、ネイティブな大文字小文字を区別しないコレーションに関連するパフォーマンスの改善を取り入れつつ、クエリを簡素化したい場合はどうでしょうか？それにはプログラミングが必要ですが、今ではSQLだけで全てを行うことができます。

次のテストスキーマを使用しましょう：

順序はASCIIコードポイントに基づいており、すべての大文字がすべての小文字よりも先に来ます。これをupper()やlower()を追加せずに修正できますか？

ダイナミックSQLステートメントと変数の設定

最初のステップは、テーブルに新しく追加された列のデフォルトの照合順序を変更するように指示することです。ノートブックが自動的に検出し、ウィジェットを追加するパラメータマーカーでローカル変数を供給することができます。また、EXECUTE IMMEDIATEを使用して動的に構成された ALTER TABLEステートメントを実行することもできます。

すべてのSQLスクリプトはBEGIN .. END（複合）ステートメントで構成されています。ローカル変数は、複合文内で最初に定義され、その後にロジックが続きます。

これはすべて一連の線形ステートメントに過ぎません。これまでのところ、複合ステートメントなしでこれらすべてをSQLセッション変数で書くことができました。あなたもあまり進歩していません。結局のところ、既存の列の照合順序を変更したかったのです。これを行うには、次の手順を実行します：

• テーブル内のすべての既存の文字列列を探す
• 各列の照合順序を変更します

要するに、INFORMATION_SCHEMA.COLUMNSテーブルをループする必要があります。

ループ

SQLスクリプティングは、ループの制御方法とともに4つのループ方法を提供します。

1. LOOP … END LOOP;
   これは「永遠」のループです。
   このループは、例外が発生するか、明示的なITERATEまたはLEAVEコマンドによってループから抜け出すまで続きます。
   例外処理については後で説明し、ループを制御する方法を説明するITERATEとLEAVEのドキュメンテーションを指摘します。
2. WHILE述語 DO … END WHILE;
   このループは、述語式が真と評価されるか、例外、ITERATE、またはLEAVEによってループが中断される限り、入力および再入力されます。
3. REPEAT … UNTIL述語 END REPEAT;
   WHILEとは異なり、このループは少なくとも一度は入力され、述語式が偽と評価されるか、例外、LEAVE、またはITERATEコマンドによってループが中断されるまで再実行されます。
4. FOR query DO …. END FOR;
   このループは、例外、LEAVE、またはITERATE文で早期に終了しない限り、クエリが返す行ごとに一度実行されます。

さて、FORループを私たちの照合スクリプトに適用します。クエリはテーブルのすべての文字列列の列名を取得します。ループの本体は、各列の照合順序を順番に変更します：

テーブルが適切に更新されたことを確認しましょう：

これまでのところ、順調です。私たちのコードは機能的には完全ですが、ファイルスキップの利点を得るために、変更した列をデルタに分析させるべきです。これを列ごとに行いたくはありません。しかし、すべてをまとめて、実際に照合順序が変更された文字列列があった場合にのみ作業を行います。

条件付きロジック

SQLスクリプティングは、SQLステートメントの条件付き実行を行うための3つの方法を提供します。

1. if-then-elseのロジック。その構文は次の通りです：
   IF predicate THEN … ELSEIF predicate THEN … ELSE …. END IF;
   もちろん、オプションのELSEIFブロックは何個でも、最後のELSEもオプションです。
2. シンプルなCASEステートメント
   このステートメントは、シンプルなケース式のSQLスクリプトバージョンです。
   CASE expression WHEN option THEN … ELSE … END CASE;
   一つの表現が複数のオプションと比較され、最初の一致がどのSQLステートメントを実行するかを決定します。一致するものがなければ、オプションのELSEブロックが実行されます。
3. 検索された CASE文
   この文は、検索されたcase式のSQLスクリプティングバージョンです。
   CASE WHEN predicate THEN …. ELSE … END CASE;
   THENブロックは、評価が真となる述語の最初に実行されます。一致するものがない場合、オプションの ELSE ブロックが実行されます。

私たちの照合スクリプトには、単純な IF THEN END IFが十分です。また、適用する列のセットを収集し、ANALYZEを行い、列リストを生成するための高階関数の魔法も必要です。

ネスト

これまでに書いたものは個々のテーブルに対して機能します。スキーマ内のすべてのテーブルに対して操作を行いたい場合はどうしますか？SQLスクリプティングは完全に組み合わせ可能です。他のSQLスクリプトステートメント内に複合ステートメント、条件ステートメント、ループをネストすることができます。

ここで行うことは二つです：

1. すべてのテーブルをスキーマ内で見つけるために外部のFORループを追加してください。これには、テーブル名の変数への参照をINFORMATION_SCHEMA.TABLESを使用して、FORループクエリの結果への参照に置き換える必要があります。
2. ネストされたコンパウンドを追加して、列リスト変数を外部のFORループに移動します。変数を直接FORループ本体で宣言することはできません。新しいスコープは追加されません。これは主にコーディングスタイルに関連した決定ですが、新しいスコープにはより重要な理由があります。

このエラーは理にかなっています。進める方法はいくつかあります：

1. 情報スキーマクエリで、ビューなどのサポートされていないテーブルタイプをフィルタリングします。問題は、テーブルの種類が数多く、時折新しいものが追加されることです。
2. ビューを処理します。それは素晴らしいアイデアです。それをあなたの宿題としましょう。
3. エラー状態を許容する

例外処理

SQLスクリプトの重要な機能の一つは、例外をインターセプトして処理する能力です。条件ハンドラは複合文の宣言部分で定義され、その複合文内の任意の文に適用されます。これにはネストされた文も含まれます。特定のエラー条件を名前で、特定のSQLSTATEsを処理して複数のエラー条件を処理するか、またはすべてのエラー条件を処理することができます。条件ハンドラの本体内では、GET DIAGNOSTICS文を使用して処理中の例外に関する情報を取得し、エラーログに記録するなど、適切と思われるSQLスクリプトを実行することができます。その後、SIGNAL新しいエラー条件を、RESIGNAL元の条件を、または単にハンドラが定義されている複合文を終了し、次の文を続けることができます。

スクリプトでは、 ALTER TABLE DEFAULT COLLATIONステートメントが適用されなかったステートメントをスキップし、オブジェクトの名前をログに記録したいと思います。

上記では、純粋にSQLで管理スクリプトを開発しました。また、ELTスクリプトを作成し、それらをジョブに変換することもできます。SQLスクリプティングは本当に強力なツールで、ぜひ活用してください。

次に何をすべきか

既存のDatabricksユーザーであろうと他の製品から移行するユーザーであろうと、SQLスクリプティングは利用すべき機能です。SQLスクリプティングはANSI標準に準拠しており、OSS Apache Spark™と完全に互換性があります。SQLスクリプティングについては、SQLスクリプティング | Databricksドキュメンテーションで詳しく説明されています。

また、このノートブックを使用して自分で確認することもできます。