SQLブロック

概要

SQLブロックは、ドキュメントページ内で、コンテナディレクティブ :::sql-block とコードブロックの組み合わせで定義します
SQLブロックは、プレースホルダとSQLブロック属性を通して、他のSQLブロックやパラメータ、テーブル、保存済クエリを参照できます
- SQLを複数のSQLブロックに分けて定義することで、可読性や再利用性を向上させることができます
- パラメータを参照することで、動的なダッシュボードを作成できるようになります

定義

SQLブロックはコンテナディレクティブ :::sql-block により記述します
- コンテナディレクティブで囲まれていない sql コードブロックは、SQLブロックとしては扱われず、通常のコードブロックとして表示されます（SQLの実行もできません）
- SQLブロックはドキュメントページ内でのみ定義できます
コンテナディレクティブ内に以下のコードブロックを記述します
- SQLブロック属性（yaml {.attrs}）
- SQL本文（sql）
- 実行結果（yaml {.result}）
- チャート（yaml {.chart}）
- 統計情報（yaml {.stats}）

SQLブロック属性

SQLブロック属性は yaml {.attrs} のコードブロックとして次のオブジェクト型で表現します
- 属性データが未指定の場合は、 format を実行すると自動挿入されます
- id はページ内でユニークな値を指定します
  - 未指定の場合、 format 実行時に自動付与されます
- connId は cdm connection list で利用可能なコネクションIDを確認してください
- defaultViewMode は、SQLブロックの表示のトグル状態を指定できます（未指定の場合は SHOW_ALL）
bindings によるパラメータやSQLブロックの参照等については、「プレースホルダと参照」の項を参照してください
jobId には、ジョブを一意に識別するためのジョブリソースIDが付与されます

type SqlBlockAttrs = {
  id: ObjectId;
  connId: ObjectId;
  name: string;  // SQLブロック名
  defaultViewMode?: "SHOW_ALL" | "SHOW_RESULTS" | "HIDDEN";

  // 他SQLブロックやパラメータへの参照を埋め込む場合に指定（詳細はSQLブロックの説明を参照）
  bindings?: SqlBlockBinding[];

  // 以下はクエリを実行した際に自動的に付与される
  jobId?: JobResourceId;
  state?: "PENDING" | "RUNNING" | "SUCCESS" | "ERROR";
  cached?: boolean;
  errorMessage?: string | null;
  executedAt?: number;
};

SQL本文

SQLのコードブロックとして記述します（省略不可）
SQLは実行可能なSELECT文である必要があります
- サブクエリや CTE（WITH句）は記述可能
- DMLやDDLは記述できません
- WHERE句のみなど、SQL文の断片のみは記述できません
- SQL末尾の ; は不要です
SQL文内に $1 や $2 のようなプレースホルダを記述することで、bindings で定義した参照を埋め込むことができます
- 詳細についてはプレースホルダと参照の項目を参照してください

実行結果

SQLの実行結果をテーブル形式で表示する場合に指定します
SQLを実行すると自動的に生成されます

type SqlBlockResultTable = {
  height?: string;  // 例: '300px'
};

チャート

SQLの実行結果を元にチャートを描画する際に指定します。
以下のオブジェクトを yaml {.chart} のコードブロックとして記述します
- id はページ内でユニークな値を指定します（未指定の場合は自動生成されます）
- height はチャートの高さを指定します（未指定の場合は 300px）
- chartSettings にはチャートの設定を指定します。詳細はチャートを参照
SQLブロック外でチャートブロックを定義する場合と異なり、sqlId の指定は必要ありません
chartJobId には、チャート用のデータ加工に利用されたジョブのジョブリソースIDが付与されます

type SqlBlockResultChart = {
  id: ObjectId;
  chartSettings: ChartSettings;
  height?: string;

  // 以下は実行時に自動的に付与される
  chartJobId?: JobResourceId;
  chartJobQueryHash?: string;
};

統計情報

SQLの実行結果のカラム単位の統計情報を表示する場合に指定します
以下のオブジェクトを yaml {.stats} のコードブロックとして記述します
- id はページ内でユニークな値を指定します（未指定の場合は自動生成されます）
- targetFields は統計情報を計算するカラムを指定します（未指定の場合は全カラムを対象にします）
statsJobId には、統計情報用のデータ加工に利用されたジョブのジョブリソースIDが付与されます

type SqlBlockResultStats = {
  id: ObjectId;
  targetFields?: string[];

  // 以下は実行時に自動的に付与される
  statsJobId?: JobResourceId;
  statsJobQueryHash?: string;
  errorMessage?: string;
};

SQLブロックの記述例

:::sql-block
```yaml {.attrs}
id: "69cafa6c871431229e5a3fe1"
connId: "69cafa749f3fccba468e7ef1"
name: "Sample SQL"
```

```sql
SELECT * FROM users LIMIT 100;
```

```yaml {.result}
height: "300px"
```

```yaml {.chart}
id: 69cafa6c871431229e5a3fe1
chartSettings:
  template_type: RADIAL_CHART_V2
  component_settings:
    render_type: PIE
    x:
      - field: category
    ys:
      - field: sales
        aggregator: SUM      
```

```yaml {.stats}
id: 69cafa6c871431229e5a3fe1
targetFields:
  - date
  - value
```
:::

プレースホルダと参照

プレースホルダは、SQLコードブロック内で $1 や $2 の形式で記述し、SQLブロック属性内の bindings 配列のインデックス（1始まり）に対応します
- 対応する bindings が見つからない場合は、エラーとなります
- 同じプレースホルダを複数箇所に記述した場合は、同一の参照として扱われます
- 文字列として $1 を記述したい場合は \$1 のようにエスケープしてください

例

:::sql-block
```yaml {.attrs}
id: "69cafa6c871431229e5a3fe1"
name: Sample SQL
bindings:
  - type: "SQL_BLOCK_REF"
    sqlId: xxx
  - type: "PARAM_REF"
    refParamKey: xxx
    escapeType: STRING
```

```sql
SELECT
  *
FROM $1
WHERE status = $2
```
:::

SQLの解析と展開

プレースホルダへの参照の展開は、SQLの構文解析（トークン化）を行った後に、前後関係を考慮して実施されるため、同じプレースホルダでも展開される内容が異なる場合があります

例えば、プレースホルダがSQLのコメント内に記述されている場合、コメントを崩さないように (REF-REMOVED-INSIDE-COMMENT) という文字列に置換されます

パラメータの参照

パラメータをSQLブロック内で参照する場合、先にパラメータの定義を行っておく必要があります
- 詳細についてはパラメータを参照してください
次に示すオブジェクトをSQLブロック属性の bindings 配列に追加することで、パラメータを参照できるようになります
- refParamKey により、参照先のパラメータを指定します
  - 原則として ParamWidget の id を指定します
  - ただし、 ParamWidget.type が DATE_RANGE の場合は、開始日・終了日を区別するために末尾に .0 か .1 を付与します
- escapeType により、後述のエスケープ処理を行います

type RefParamKey = ObjectId | `${ObjectId}.0` | `${ObjectId}.1`;

type ParamRef = {
  type: "PARAM_REF";
  refParamKey: RefParamKey;
  escapeType: "STRING" | "NUMBER" | "BOOLEAN" | "DATE" | "STRING_ARRAY";
};

エスケープと動的参照

SQL内にパラメータを埋め込む際の、値の検証とエスケープ処理は、 escapeType によって異なります。以下の例は BigQuery のコネクションを利用した場合の例で、エスケープ処理はコネクションの接続先DWHのSQL文法に従って行われます。

文字列（`escapeType: STRING`）

通常、値は文字列リテラルとしてエスケープされて展開されます

sql

-- パラメータ値が user's "data" の場合
WHERE name = $1         -> WHERE name = 'user\'s \"data\"'

引用符で囲んだ中に置く場合、外側の引用符の種類に合わせてエスケープされます

sql

SELECT "$1" as alias    -> SELECT "user\'s \"data\"" as alias
SELECT '$1' as alias    -> SELECT 'user\'s \"data\"' as alias
SELECT `$1` as alias    -> SELECT `user's "data"` as alias

日付（`escapeType: DATE`）

通常、YYYY-MM-DD 形式の文字列として展開され、文字列同様に引用符も加味します

sql

SELECT $1 as normal              -> SELECT '2024-10-02' as normal
SELECT "$1" as double_quote      -> SELECT "2024-10-02" as double_quote

数値・真偽値（`escapeType: NUMBER` / `BOOLEAN`）

値の検証のみ行い、値をそのまま埋め込みます

sql

SELECT $1 as normal              -> SELECT 123.45 as normal
SELECT '$1' as single_quote      -> SELECT '123.45' as single_quote
SELECT $1 as boolean             -> SELECT true as boolean

文字列配列（`escapeType: STRING_ARRAY`）

通常、値は WHERE IN (...) や CONCAT(...) 等での利用を想定して、カンマ区切りの文字列として埋め込みます。空配列の場合は、シンタックスエラーを避けるために、空文字列が埋め込まれます。

sql

WHERE category in ($1)          -> WHERE category in ('cat1', 'cat2', 'cat3')
WHERE category in ($1)          -> WHERE category in ('')

引用符で囲んだ中に置く場合、カンマで区切られた文字列として埋め込まれます。

sql

SELECT '$1' as single_quote     -> SELECT 'cat1,cat2,cat3' as single_quote
SELECT "$1" as double_quote     -> SELECT "cat1,cat2,cat3" as double_quote

動的な識別子の生成

引用符の中にプレースホルダを置くことで、識別子の一部としてパラメータを差し込むことができます。これを利用して、テーブルやカラムの参照先を動的に組み立てることができます。

sql

-- STRING: 動的なテーブル指定
SELECT * FROM `table_$1`    -> SELECT * FROM `table_sales_2024`

-- STRING: 動的なカラム指定
SELECT `column_$1` as value          -> SELECT `column_revenue` as value

-- DATE: バッククォート内では YYYYMMDD 形式に展開される（BigQuery のみ）
SELECT * FROM `table_$1`    -> SELECT * FROM `table_20241002`

CAUTION

STRING のパラメータ値は任意の文字列を取りうるため、動的に識別子を組み立てる場合は、プレフィックスやサフィックスをSQL側で固定する、意図しない値が指定された時にSQLがエラーになるようにSQLを記述する、等の対策を検討してください。

SQLブロックの参照

SQLブロックへの参照を行うことで、SQLを構造化し、再利用できるようになります
- 同一ノートブックファイル内の、他のドキュメントページのSQLブロックも参照できます
- 循環参照はエラーとなります
次に示すオブジェクトをSQLブロック属性の bindings 配列に追加することで、他のSQLブロックを参照できます
- sqlId により、参照先のSQLブロックの id を指定します
  - 他のドキュメントページのSQLブロックを参照する場合は pageId により、参照先ドキュメントページの id を指定します
  - 同一ドキュメントページ内のSQLブロックを参照する場合は、 pageId を指定せずに省略します
- 以下により、SQLブロックの展開方法を指定できます
  - omitEncloseBracket: true の場合、展開時に ( と ) で囲むのを省略します
  - withAlias: true の場合、展開時に AS sql-block.name の形式で、SQLブロックの名前をエイリアスとして付与します
    - omitEncloseBracket が true の場合は無効になります
  - overwriteParams: 指定することで参照先のSQLブロックのパラメータを上書きできます（詳細は後述）

type SqlBlockRef = {
  type: "SQL_BLOCK_REF";
  pageId?: string;
  sqlId: string;
  omitEncloseBracket?: boolean;
  withAlias?: boolean;
  overwriteParams?: OverwriteParam[];
};

例

:::sql-block
```yaml {.attrs}
id: "69cafa6c871431229e5a3fe2"
name: sql1
```

```sql
SELECT 'Hello world!' as msg
```
:::

:::sql-block
```yaml {.attrs}
id: "69cafa6c871431229e5a3fe3"
name: sql2
bindings:
  - type: "SQL_BLOCK_REF"
    sqlId: "69cafa6c871431229e5a3fe2"
```

```sql
SELECT msg FROM $1
```
:::

上記の例の場合、 sql2 のSQLは以下となります。（参照先のSQLブロックの前後に改行が自動的に挿入されます）

sql

SELECT msg FROM (
SELECT 'Hello world!' as msg
)

withAlias: true を指定すると、末尾が ) AS sql1 となります。 omitEncloseBracket: true を指定すると、前後の ( と ) が省略されます（改行は挿入されます）。

パラメータ値の展開

参照先のSQLブロック内でパラメータが参照されている場合、参照元のSQLブロックのあるページのパラメータの値が優先して展開されます

例えば、ページ1のSQL1を、ページ2のSQL2から参照した時に、
- SQL1内でノートブック共通のパラメータAを参照している場合、参照元のページ2のノートブック共通のパラメータAの値が展開されます
- SQL1内でページ固有のパラメータBを参照している場合、ページ2にはパラメータBが存在しないため、ページ1のページ固有のパラメータBの値が展開されます

また、 overwriteParams を指定することで、参照先のSQLブロックのパラメータの値を上書きできます。上書きの設定は次の OverwriteParam 型で指定します。

type OverwriteParam = {
  pageId: string;
  refParamKey: RefParamKey;
} & (
  | { overwriteType: 'NONE' }
  | { overwriteType: 'FIXED_VALUE'; fixedValue: unknown }
  | { overwriteType: 'PARAM_WIDGET'; overwriteRefParamKey: RefParamKey }
);

pageId と refParamKey の組み合わせで、どのパラメータの値を上書きするか指定します
- pageId はSQLブロックが定義されているページの id を指定します
- refParamKey は上書きするパラメータの refParamKey を指定します
overwriteType により、上書きの方法を指定します
- NONE: 上書きは行いません（未指定の場合と同様です）
- FIXED_VALUE: 固定値で上書きします
  - fixedValue には、上書きする値を指定します。上書きするパラメータの escapeType と一致する型の値を指定する必要があります
- PARAM_WIDGET: 他のパラメータの値で上書きします
  - overwriteRefParamKey には、上書きするパラメータの refParamKey を指定します

テーブルの参照

次に示すオブジェクトをSQLブロック属性の bindings 配列に追加することで、DWHのテーブルを参照できるようになります
- tableResourceId の形式についてはリソースIDを参照してください
- withAlias が true の場合、展開時に AS table_name の形式で、テーブルの名前をエイリアスとして付与します
テーブルの参照は、SQLブロックの展開時に、DWHの記法に従って展開されます
SQL内で文字列としてテーブルを指定する場合に比べて、以下のような利点があります
- 参照へのマウスオーバーでテーブル内のカラム一覧等の情報を表示できるようになります

type TableRef = {
  type: "TABLE_REF";
  tableResourceId: TableResourceId;
  withAlias?: boolean;
};

例

:::sql-block
```yaml {.attrs}
id: 69cafa6c871431229e5a3fe4
name: sql1
bindings:
  - type: TABLE_REF
    tableResourceId: bq/cn=69cafa6c871431229e5a3fe1/pj=bigquery-public-data/ds=austin_bikeshare/tb=stations
```

```sql
-- SELECT * FROM `bigquery-public-data.austin_bikeshare.stations`
SELECT * FROM $1
```
:::

保存済クエリの参照

保存済クエリの参照は、Codatum のWebサービス上に保存されたクエリを参照する場合に利用します
次に示すオブジェクトをSQLブロック属性の bindings 配列に追加することで、保存済クエリを参照します
- queryId には、保存済クエリを一意に識別する id を指定します
- queryJson には、保存済クエリの実体が入ります
  - 保存済クエリの参照を生成する際に、最新の保存済クエリのスナップショットをここに埋め込みます
  - このスナップショットと、保存済クエリの実体に差分がある場合、ノートブックのプレビュー画面上から、最新の保存済クエリの実体を再取得して上書きすることができます
  - queryId に対応するスナップショットの保持が目的のため、この実体を直接編集しないでください
- omitEncloseBracket が true の場合、展開時に ( と ) で囲むのを省略します

type QueryRef = {
  type: "QUERY_REF";
  queryId: ObjectId;
  queryJson: QueryJson;
  omitEncloseBracket?: boolean;
};

type QueryJson = {
  // クエリ名
  name: string;
  // クエリのSQL定義
  content: (
    | {
        type: 'text';
        text: string;
      }
    | {
        type: 'sqlTableRef';
        attrs: { tableResourceId: string; withAlias: boolean };
      }
  )[];
  // クエリの実行結果のカラム情報
  columns?: {
    name: string;
    type: string;
    description?: string;
    tagIds?: string[];
  }[];
};

SQLブロック ​

概要 ​

定義 ​

SQLブロック属性 ​

SQL本文 ​

実行結果 ​

チャート ​

統計情報 ​

SQLブロックの記述例 ​

プレースホルダと参照 ​

例 ​

SQLの解析と展開 ​

パラメータの参照 ​

エスケープと動的参照 ​

文字列（escapeType: STRING） ​

日付（escapeType: DATE） ​

数値・真偽値（escapeType: NUMBER / BOOLEAN） ​

文字列配列（escapeType: STRING_ARRAY） ​

動的な識別子の生成 ​

SQLブロックの参照 ​

例 ​

パラメータ値の展開 ​

テーブルの参照 ​

例 ​

保存済クエリの参照 ​