デフォルトの列値を指定する

このページでは、BigQuery テーブルの列にデフォルト値を設定する方法について説明します。テーブルに行を追加するときに、デフォルト値が設定された列にデータが含まれていない場合、代わりにデフォルト値が列に書き込まれます。

デフォルト値の式

列のデフォルト値の式は、リテラルまたは次のいずれかの関数にする必要があります。

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

これらの関数を使用して、STRUCT または ARRAY のデフォルト値を構成できます（例: [CURRENT_DATE(), DATE '2020-01-01']）。

関数は、データがテーブルに書き込まれるときに評価されます。デフォルト値の型は、適用先の列の型と一致しているか、強制型変換する必要があります。デフォルト値が設定されていない場合、NULL がデフォルト値になります。

デフォルト値を設定する

新しいテーブルを作成するときに、列のデフォルト値を設定できます。CREATE TABLE DDL ステートメントを使用し、列名と型の後に DEFAULT キーワードとデフォルト値の式を追加します。次の例では、2 つの STRING 列（a と b）を持つ simple_table というテーブルを作成します。b 列のデフォルト値は 'hello' です。

CREATE TABLE mydataset.simple_table (
  a STRING,
  b STRING DEFAULT 'hello');

b 列を省略した simple_table にデータを挿入すると、代わりにデフォルト値の 'hello' が使用されます。次に例を示します。

INSERT mydataset.simple_table (a) VALUES ('val1'), ('val2');

テーブル simple_table には次の値が含まれています。

+------+-------+
| a    | b     |
+------+-------+
| val1 | hello |
| val2 | hello |
+------+-------+

列の型が STRUCT の場合は、STRUCT フィールド全体にデフォルト値を設定する必要があります。一部のフィールドにはデフォルト値を設定できません。配列のデフォルト値に NULL を指定することはできません。また、NULL 要素を含めることもできません。次の例では、complex_table というテーブルを作成し、ARRAY 型を含むネストされたフィールドの列 struct_col のデフォルト値を設定しています。

CREATE TABLE mydataset.complex_table (
  struct_col STRUCT<x STRUCT<x1 TIMESTAMP, x2 NUMERIC>, y ARRAY<DATE>>
    DEFAULT ((CURRENT_TIMESTAMP(), NULL),
             [DATE '2022-01-01', CURRENT_DATE()])
);

列の制約に違反するデフォルト値、たとえば、パラメータ化された型に準拠していないデフォルト値や、列のモードが REQUIRED の場合の NULL のデフォルト値など、列の制約に違反するデフォルト値は使用できません。

デフォルト値を変更する

列のデフォルト値を変更するには、次のいずれかのオプションを選択します。

コンソール

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

   [BigQuery] に移動

2. [エクスプローラ] パネルで、プロジェクトとデータセットを開いて、テーブルを選択します。

3. 詳細パネルで [スキーマ] タブをクリックします。

4. [スキーマを編集] をクリックします。このボタンを表示するには、スクロールが必要な場合があります。

5. [現在のスキーマ] ページで、変更するトップレベル フィールドを見つけます。

6. そのフィールドのデフォルト値を入力します。

7. [保存] をクリックします。

SQL

ALTER COLUMN SET DEFAULT DDL ステートメントを使用します。

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

   [BigQuery] に移動

2. クエリエディタで次のステートメントを入力します。

   ALTER TABLE mydataset.mytable
   ALTER COLUMN column_name SET DEFAULT default_expression;
   

3. [実行] をクリックします。

クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。

列のデフォルト値の設定は、テーブルに今後挿入される列に対してのみ影響します。既存のテーブルデータは変更されません。次の例では、a 列のデフォルト値を SESSION_USER() に設定しています。

ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();

a 列を省略した行を simple_table に挿入すると、代わりに現在のセッション ユーザーが使用されます。

INSERT mydataset.simple_table (b) VALUES ('goodbye');

テーブル simple_table には次の値が含まれています。

+------------------+---------+
| a                | b       |
+------------------+---------+
| val1             | hello   |
| val2             | hello   |
| [e-mail] | goodbye |
+------------------+---------+

デフォルト値を削除する

列のデフォルト値を削除するには、次のいずれかのオプションを選択します。

コンソール

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

   [BigQuery] に移動

2. [エクスプローラ] パネルで、プロジェクトとデータセットを開いて、テーブルを選択します。

3. 詳細パネルで [スキーマ] タブをクリックします。

4. [スキーマを編集] をクリックします。このボタンを表示するには、スクロールが必要な場合があります。

5. [現在のスキーマ] ページで、変更するトップレベル フィールドを見つけます。

6. デフォルト値として「NULL」と入力します。

7. [保存] をクリックします。

SQL

ALTER COLUMN DROP DEFAULT DDL ステートメントを使用します。

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

   [BigQuery] に移動

2. クエリエディタで次のステートメントを入力します。

   ALTER TABLE mydataset.mytable ALTER COLUMN column_name DROP DEFAULT;
   

   ALTER COLUMN SET DEFAULT DDL ステートメントで値を NULL に変更して、列からデフォルト値を削除することもできます。

3. [実行] をクリックします。

クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。

デフォルト値を含む DML ステートメントを使用する

デフォルト値を含む行をテーブルに追加するには、INSERT DML ステートメントを使用します。デフォルト値は、列の値が指定されていない場合や、キーワード DEFAULT が値の式の代わりに使用される場合に使用されます。次の例では、テーブルを作成し、すべての値がデフォルト値である行を挿入します。

CREATE TABLE mydataset.mytable (
  x TIME DEFAULT CURRENT_TIME(),
  y INT64 DEFAULT 5,
  z BOOL);

INSERT mydataset.mytable (x, y, z) VALUES (DEFAULT, DEFAULT, DEFAULT);

テーブル mytable は次のようになります。

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
+-----------------+---+------+

z 列にデフォルト値がないため、NULL がデフォルトとして使用されます。デフォルト値は、CURRENT_TIME() などの関数の場合、値が書き込まれるときに評価されます。列 x のデフォルト値で INSERT を呼び出すと、TIME に対して異なる値が再度生成されます。次の例では、列 z のみに明示的に設定された値が存在し、省略された列はそのデフォルト値を使用します。

INSERT mydataset.mytable (z) VALUES (TRUE);

テーブル mytable は次のようになります。

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
| 22:18:29.890547 | 5 | true |
+-----------------+---+------+

デフォルト値でテーブルを更新するには、MERGE DML ステートメントを使用します。次の例では、2 つのテーブルを作成し、そのうちの 1 つを MERGE ステートメントで更新します。

CREATE TABLE mydataset.target_table (
  a STRING,
  b STRING DEFAULT 'default_b',
  c STRING DEFAULT SESSION_USER())
AS (
  SELECT
    'val1' AS a, 'hi' AS b, '[e-mail]' AS c
  UNION ALL
  SELECT
    'val2' AS a, 'goodbye' AS b, SESSION_USER() AS c
);

CREATE TABLE mydataset.source_table (
  a STRING DEFAULT 'default_val',
  b STRING DEFAULT 'Happy day!')
AS (
  SELECT
    'val1' AS a, 'Good evening！' AS b
  UNION ALL
  SELECT
    'val3' AS a, 'Good morning!' AS b
);

MERGE mydataset.target_table T
USING mydataset.source_table S
ON T.a = S.a
WHEN NOT MATCHED THEN
  INSERT(a, b) VALUES (a, DEFAULT);

結果は次のようになります。

+------+-----------+--------------------+
| a    | b         | c                  |
+------+-----------+--------------------+
| val1 | hi        | [e-mail]     |
| val2 | goodbye   | [e-mail] |
| val3 | default_b | [e-mail] |
+------+-----------+--------------------+

デフォルト値でテーブルを更新するには、UPDATE DML ステートメントを使用します。次の例では、b 列の各行がデフォルト値に等しくなるようにテーブル source_table を更新します。

UPDATE mydataset.source_table
SET b =  DEFAULT
WHERE TRUE;

結果は次のようになります。

+------+------------+
| a    | b          |
+------+------------+
| val1 | Happy day! |
| val3 | Happy day! |
+------+------------+

テーブルを追加する

--append_table フラグを指定して bq query コマンドを使用し、デフォルト値を含む宛先テーブルにクエリの結果を追加できます。クエリでデフォルト値の列が省略されている場合は、デフォルト値が割り当てられます。次の例では、列 z の値のみを指定するデータを追加します。

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT FALSE AS z UNION ALL SELECT FALSE AS Z'

テーブル mytable では、x 列と y 列でデフォルト値が使用されています。

+-----------------+---+-------+
|        x        | y |   z   |
+-----------------+---+-------+
| 22:13:24.799555 | 5 |  NULL |
| 22:18:29.890547 | 5 |  true |
| 23:05:18.841683 | 5 | false |
| 23:05:18.841683 | 5 | false |
+-----------------+---+-------+

データを読み込む

デフォルト値を持つテーブルにデータを読み込むには、bq load コマンドまたは LOAD DATA ステートメントを使用します。デフォルト値が適用されるのは、読み込まれたデータの列数が宛先テーブルよりも少ない場合です。読み込まれたデータの NULL 値はデフォルト値に変換されません。

AVRO、Parquet、ORC などのバイナリ形式には、エンコードされたファイル スキーマがあります。ファイル スキーマで一部の列が省略されている場合は、デフォルト値が適用されます。

JSON や CSV などのテキスト形式には、エンコードされたファイル スキーマが存在しません。bq コマンドライン ツールを使用してスキーマを指定するには、--autodetect フラグを使用するか、JSON スキーマを提供します。LOAD DATA ステートメントを使用してスキーマを指定するには、列のリストを指定する必要があります。次の例では、CSV ファイルから列 a のみを読み込みます。

LOAD DATA INTO mydataset.insert_table (a)
FROM FILES(
  uris = ['gs://test-bucket/sample.csv'],
  format = 'CSV');

API を作成する

Storage Write API は、書き込みストリームに宛先テーブル スキーマにあるフィールドがない場合にのみ、デフォルト値を挿入します。この場合、欠落フィールドには、書き込みのたびに列のデフォルト値が入力されます。フィールドが書き込みストリーム スキーマには存在するものの、データ自体にない場合、欠落しているフィールドには NULL が入力されます。たとえば、次のスキーマで BigQuery テーブルにデータを書き込むとします。

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

次の書き込みストリーム スキーマには、宛先テーブルに存在する c フィールドがありません。

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
]

次の値をテーブルにストリーミングするとします。

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

結果は次のようになります。

+-------+-------+-----------+
| a     | b     | c         |
+-------+-------+-----------+
| val_a | val_b | default_c |
| val_a | NULL  | default_c |
+-------+-------+-----------+

書き込みストリーム スキーマには b フィールドが含まれているため、フィールドに値が指定されていない場合でも、デフォルト値の default_b は使用されません。書き込みストリーム スキーマには c フィールドがないため、c 列のすべての行に宛先テーブルのデフォルト値 default_c が適用されます。

次の書き込みストリーム スキーマは、書き込み先のテーブルのスキーマと一致しています。

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
  {
    "name": "c",
    "type": "STRING",
  }
]

次の値をテーブルにストリーミングするとします。

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

書き込みストリーム スキーマには、宛先テーブルに含まれるフィールドが欠落していないため、ストリーミングされたデータのフィールドにデータが入力されているかどうかにかかわらず、いずれの列のデフォルト値も適用されません。

+-------+-------+------+
| a     | b     | c    |
+-------+-------+------+
| val_a | val_b | NULL |
| val_a | NULL  | NULL |
+-------+-------+------+

接続レベルのデフォルト値の設定は、AppendRowsRequest メッセージ内の default_missing_value_interpretation で指定できます。値が DEFAULT_VALUE に設定されている場合、ユーザー スキーマに列が示されていても、欠損値にはデフォルト値が取得されます。

AppendRowsRequest メッセージ内の missing_value_interpretations マップでリクエスト レベルのデフォルト値を指定することもできます。各キーは列の名前で、その値は欠損値の解釈方法を示します。

たとえば、マップ {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE} は、col1 内のすべての欠損値が NULL として解釈され、col2 内のすべての欠損値が、テーブル スキーマの col2 に対して設定されたデフォルト値として解釈されることを意味します。

フィールドがこのマップになく、欠損値がある場合、欠損値は NULL として解釈されます。

キーは最上位の列名のみにできます。キーを col1.subfield1 のような構造体サブフィールドにすることはできません。

insertAll API メソッドを使用する

tabledata.insertAll API メソッドは、データがテーブルに書き込まれたときに、行レベルでデフォルト値を入力します。行にデフォルト値を持つ列が欠落している場合は、それらの列にデフォルト値が適用されます。

たとえば、次のテーブル スキーマがあるとします。

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

次の値をテーブルにストリーミングするとします。

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}
{}

結果は次のようになります。

+-------+------------+-----------+
| a     | b          | c         |
+-------+------------+-----------+
| val_a | val_b      | default_c |
| val_a | default_b  | default_c |
| NULL  | default_b  | default_c |
+-------+------------+-----------+

最初に挿入された行には c フィールドの値が含まれていないため、デフォルト値 default_c が列 c に書き込まれます。2 番目に挿入された行には、b フィールドまたは c フィールドの値がないため、デフォルト値が b 列と c 列に書き込まれます。3 番目に挿入された行には値が含まれていません。他のデフォルト値が設定されていないため、列 a に書き込まれる値は NULL です。デフォルト値の default_b と default_c が列 b と c に書き込まれます。

デフォルト値を表示する

列のデフォルト値を確認するには、INFORMATION_SCHEMA.COLUMNS ビューをクエリします。column_default 列フィールドには、列のデフォルト値が含まれています。デフォルト値が設定されていない場合は NULL です。次の例は、テーブル mytable の列名とデフォルト値を示しています。

SELECT
  column_name,
  column_default
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

次のような結果になります。

+-------------+----------------+
| column_name | column_default |
+-------------+----------------+
| x           | CURRENT_TIME() |
| y           | 5              |
| z           | NULL           |
+-------------+----------------+

制限事項

• レガシー SQL を使用して、デフォルト値のテーブルから読み取ることはできますが、レガシー SQL を使用してデフォルト値を持つテーブルに書き込むことはできません。
• 既存のテーブルに、デフォルト値で新しい列を追加することはできません。ただし、デフォルト値なしで列を追加し、ALTER COLUMN SET DEFAULT DDL ステートメントを使用してデフォルト値を変更できます。
• コピー元のテーブルを、コピー元のテーブルよりも多くの列を持つコピー先テーブルにコピーしたり、追加することはできません。追加の列にはデフォルト値があります。代わりに、INSERT destination_table SELECT * FROM source_table を実行してデータをコピーできます。

次のステップ

• BigQuery へのデータの読み込みの詳細については、データの読み込みの概要をご覧ください。