カスタム SQL オペレーションの追加

このドキュメントでは、専用の SQLX ファイルでカスタム SQL オペレーションを定義する方法について説明します。

Dataform では、テーブルの公開やアサーションの書き込みの Dataform モデルに適合しないカスタム SQL オペレーションを実行できます。ユーザーは Dataform が BigQuery で実行するカスタム SQL コマンドを定義できます。

始める前に

  1. Google Cloud コンソールの [Dataform] ページに移動します。

    [Dataform] ページに移動

  2. リポジトリを作成または選択します。

  3. 開発ワークスペースを作成または選択します。

必要なロール

カスタム SQL オペレーションの定義に必要な権限を取得するには、ワークスペースに対する Dataform 編集者 （roles/dataform.editor）IAM ロールの付与を管理者に依頼してください。ロールの付与については、プロジェクト、フォルダ、組織へのアクセスを管理するをご覧ください。

必要な権限は、カスタムロールや他の事前定義ロールから取得することもできます。

カスタム オペレーション定義のファイルを作成する

カスタム オペレーション定義 SQLX ファイルを definitions/ ディレクトリに保存します。definitions/ ディレクトリに新しい SQLX ファイルを作成するには、次の手順を行います。

  1. 開発ワークスペースに移動します。
  2. [ファイル] ペインで、definitions/ の横にある [その他] メニューをクリックします。
  3. [ファイルを作成] をクリックします。

  4. [ファイルパスを追加] フィールドに、ファイルの名前の後に definitions/ の後に .sqlx を入力します。例: definitions/sample-operation.sqlx。

    ファイル名 には数字、英字、ハイフン、アンダースコアのみを使用できます。

  5. [ファイルを作成] をクリックします。

カスタム SQL オペレーションを定義する

カスタム SQL オペレーションは type: operations の SQLX ファイルで定義できます。 BigQuery のステートメントは operations ファイルに記述できます。 Dataform は、カスタム SQL オペレーションを変更せずに BigQuery で実行します。

1 つの SQLX ファイルで複数のカスタム SQL オペレーションを定義できます。BigQuery はファイル内のすべてのオペレーションを同じコンテキストで実行し、すべてのオペレーションをセミコロン ; で結合して、実行された SQL を作成します。

BigQuery 以外のデータ ウェアハウスでオープンソースの Dataform フレームワークを使用するときに複数のカスタム SQL オペレーションを定義するには、--- でオペレーションを区切ります。

専用の SQLX ファイルでカスタム SQL オペレーションを定義するには、次の手順を行います。

  1. 開発ワークスペースで、カスタム オペレーション定義用の SQLX ファイルを選択します。

  2. ファイルに次のコード スニペットを入力します。

    config { type: "operations" }

  3. config ブロックの外に SQL オペレーションを記述します。

  4. 省略可: [書式] をクリックします。

次のコードサンプルは、operations ファイルで定義された複数のカスタム SQL オペレーションを示しています。

config { type: "operations" }

DELETE FROM dataset.table WHERE country = 'GB';

DELETE FROM dataset.table WHERE country = 'FR';

次のコードサンプルは、ビューを手動で作成するカスタム SQL オペレーションを示しています。

config { type: "operations" }
CREATE OR REPLACE VIEW dataset.table AS (SELECT 1 AS TEST)

参照可能な出力テーブルを作成する

カスタム SQL オペレーションで、他のスクリプトで参照できるテーブルを手動で作成できます。他のスクリプトで使用できるテーブルを作成するには、オペレーションに出力があることを宣言する必要があります。

出力テーブルの名前を operations ファイルの名前と一致させるには、CREATE オペレーションで self 関数を使用します。

カスタム オペレーションでテーブルを作成し、他のスクリプトで使用できるようにするには、次の操作を行います。

  1. 開発ワークスペースで、カスタム オペレーション定義用の SQLX ファイルを選択します。

  2. SQLX ファイルに次のコード スニペットを入力します。

    config {
 type: "operations",
 hasOutput: true
 }

  3. 省略可: 出力テーブルの名前とファイルの名前を一致させるには、次の形式で self 関数を使用して SQL CREATE オペレーションを作成します。

    CREATE OR REPLACE TABLE ${self()} AS (CUSTOM_SQL_QUERY)

    CUSTOM_SQL_QUERY は、テーブル定義 SQL SELECT ステートメントに置き換えます。

  4. 省略可: [書式] をクリックします。

カスタム SQL オペレーション出力テーブルを参照する

  • 別のテーブルの SQLX 定義でカスタム SQL オペレーション出力テーブルを参照するには、ref 関数に出力テーブルのファイル名を入力します。

次のサンプルコードは、custom_SQL_operation_table という参照可能なテーブルを作成する、custom_SQL_operation_table.sqlx ファイル内でのカスタム SQL オペレーションを示しています。

// filename is custom_SQL_operation_table.sqlx
config {
type: "operations",
hasOutput: true
}
CREATE OR REPLACE VIEW ${self()} AS (SELECT 1 AS TEST)

次のコードサンプルは、テーブル定義 SQLX ファイルで custom\_SQL\_operation\_table table を参照しています。

config { type: "table" }
SELECT * FROM ${ref("custom_SQL_operation_table")}

空のテーブルの作成

別のサービスでデータを入力できるように、空白のテーブルを作成することもできます。カスタム SQL オペレーションで CREATE TABLE 関数を使用して空白のテーブルを作成できます。テーブルやビューなど、他の SQL ワークフロー オブジェクト定義で空のテーブルを参照できるようにするには、空白のテーブル オペレーションの config ブロックに hasOutput:true プロパティを追加します。

  • 空のテーブルを作成するには、次の形式で type: "operations" ファイルの CREATE TABLE 関数を使用します。
config {
  type: "operations",
  hasOutput: true  // optional, lets you reference the empty table
}

CREATE TABLE ${self()} (

)

次のコードサンプルは、整数と文字列の列を含む空白のテーブルを作成するカスタム SQL オペレーションを示しています。作成された空の白テーブルは、他の SQL ワークフロー オブジェクトから参照できません。

config {
  type: "operations"
}

CREATE TABLE ${self()} (
  x INT64,
  y STRING
)

次のステップ