データ定義言語(DDL)ステートメントを使用すると、標準 SQL クエリ構文を使用して BigQuery リソースを作成および変更できます。現時点では、BigQuery で DDL コマンドを使用して次のことができます。
DDL ステートメントの実行
DDL ステートメントを実行するには、Cloud Console を使用する、bq
コマンドライン ツールを使用する、jobs.query
REST API を呼び出す、プログラムで BigQuery API クライアント ライブラリを使用する、などの方法があります。
Console
Cloud Console の BigQuery ページに移動します。
[クエリを新規作成] をクリックします。
[クエリエディタ] テキスト領域に DDL ステートメントを入力します。次に例を示します。
CREATE TABLE mydataset.newtable ( x INT64 )
[実行] をクリックします。
bq
bq query
コマンドを入力し、DDL ステートメントをクエリ パラメータとして指定します。use_legacy_sql
フラグを false
に設定します。
bq query --use_legacy_sql=false \ 'CREATE TABLE mydataset.newtable ( x INT64 )'
API
jobs.query
メソッドを呼び出し、リクエスト本文の query
プロパティで DDL ステートメントを指定します。
DDL 機能により、ジョブリソースによって返される情報が拡張されます。statistics.query.statementType
には、DDL サポート用の次の追加の値が含まれます。
CREATE_TABLE
CREATE_TABLE_AS_SELECT
DROP_TABLE
CREATE_VIEW
DROP_VIEW
statistics.query
には次の 2 つの追加のフィールドがあります。
ddlOperationPerformed
: 実行された DDL オペレーションであり、DDL ターゲットの存在に依存する可能性があります。現在の値は次のとおりです。CREATE
: クエリによって DDL ターゲットが作成されました。SKIP
: 処理なし。例 -CREATE TABLE IF NOT EXISTS
が送信され、テーブルが存在します。または、DROP TABLE IF EXISTS
が送信され、テーブルが存在しません。REPLACE
: クエリによって DDL ターゲットが置き換えられました。例 -CREATE OR REPLACE TABLE
が送信され、テーブルはすでに存在しています。DROP
: クエリによって DDL ターゲットが削除されました。
ddlTargetTable
:CREATE TABLE/VIEW
ステートメントまたはDROP TABLE/VIEW
ステートメントを送信すると、次の 3 つのフィールドを持つオブジェクトとしてターゲット テーブルが返されます。- "projectId": string
- "datasetId": string
- "tableId": string
Java
BigQuery.create()
メソッドを呼び出し、クエリジョブを開始します。Job.waitFor()
メソッドを呼び出し、DDL クエリが完了するまで待ちます。
Node.js
Python
Client.query()
メソッドを呼び出し、クエリジョブを開始します。QueryJob.result()
メソッドを呼び出し、DDL クエリが完了するまで待ちます。
CREATE TABLE
ステートメント
BigQuery でテーブルを作成するには、CREATE TABLE
DDL ステートメントを使用します。
{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE} [[project_name.]dataset_name.]table_name [( column_name column_schema[, ...] )] [PARTITION BY partition_expression] [CLUSTER BY clustering_column_list] [OPTIONS(table_option_list)] [AS query_statement]
ここで
{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
は、次のステートメントのいずれかです。
CREATE TABLE
: 新しいテーブルを作成します。CREATE TABLE IF NOT EXISTS
: 指定されたデータセットにテーブルが存在しない場合にのみ新しいテーブルを作成します。CREATE OR REPLACE TABLE
: テーブルを作成し、既存のテーブルを指定されたデータセット内の同じ名前に置き換えます。
CREATE TABLE
ステートメントは、以下の規則に従う必要があります。
- 使用できる
CREATE
ステートメントは 1 つのみです。 - 列リストと
as query_statement
句のいずれかまたは両方が存在する必要があります。 - 列リストと
as query_statement
句の両方が存在する場合、BigQuery は、as query_statement
句内の名前を無視し、位置に基づいて列を列リストに一致させます。 as query_statement
句が存在し、列リストが存在しない場合、BigQuery は、列の名前と型をas query_statement
句から判断します。- 列リストと
as query_statement
句のいずれかで、列名が指定されている必要があります。 - 列名を重複させることはできません。
テーブルパス
project_name
は、テーブルを作成するプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、テーブルを作成するデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
table_name
は、作成するテーブルの名前です。
BigQuery でテーブルを作成するとき、テーブル名はデータセットごとに一意である必要があります。テーブル名の要件は次のとおりです。
- 1,024 文字以内。
- カテゴリ L(文字)、M(マーク)、N(数字)、Pc(コネクタ、アンダースコアを含む)、Pd(ダッシュ)、Zs(スペース)の Unicode 文字を含む。詳しくは、一般カテゴリをご覧ください。
たとえば、table-01
、ग्राहक
、00_お客様
、étudiant
はすべて有効なテーブル名です。
column_name
と column_schema
。
(column_name column_schema[, ...])
には、テーブルのスキーマ情報がカンマ区切りのリストで含まれています。
column_name
は、列の名前です。列名の要件は次のとおりです。- 英字(大文字または小文字)、数字(0~9)、アンダースコア(_)だけが含まれている
- 英字またはアンダースコアで始まっている
- 128 文字以内である
column_schema
はデータ型に似ていますが、ARRAY
以外の型に対してはオプションのNOT NULL
制約をサポートします。column_schema
は、トップレベルの列とSTRUCT
フィールドに対するオプションもサポートします。
column_schema := {simple_type [NOT NULL] | STRUCT<field_list> [NOT NULL] | ARRAY<array_element_schema>} [OPTIONS(column_option_list)] field_list := field_name column_schema [, ...] array_element_schema := {simple_type | STRUCT<field_list>} [NOT NULL]
simple_type
はサポートされている任意のデータ型です(STRUCT
と ARRAY
は除きます)。
field_name
は、構造体フィールドの名前です。構造体フィールドの名前には列名と同じ制約があります。
列またはフィールドに対して NOT NULL
制約が存在する場合、その列またはフィールドは REQUIRED
モードで作成されます。逆に、列またはフィールドに対して NOT NULL
制約がない場合、その列またはフィールドは NULLABLE
モードで作成されます。
ARRAY
型の列とフィールドでは NOT NULL
修飾子はサポートされていません。たとえば、ARRAY<INT64> NOT NULL
という column_schema
は無効です。その理由は、ARRAY
列には REPEATED
モードがあり、列を空にすることはできますが、NULL
にすることはできないためです。NOT NULL
制約が指定されているかどうかにかかわらず、テーブル内の配列要素を NULL
にすることはできません。たとえば、ARRAY<INT64>
は ARRAY<INT64 NOT NULL>
と同じです。
テーブルの column_schema
の NOT NULL
属性は、テーブルに対するクエリを通じて伝播されません。たとえば、テーブル T
に x INT64 NOT NULL
として宣言されている列がある場合、CREATE TABLE dataset.newtable AS SELECT x FROM T
によって x
が
NULLABLE
である dataset.newtable
という名前のテーブルが作成されます。
column_schema
は、CREATE TABLE
ステートメントの列定義リストでのみ使用できます。式の中の型として使用することはできません。たとえば、CAST(1 AS INT64 NOT NULL)
は無効です。
partition_expression
PARTITION BY
は、テーブルのパーティショニングを制御するオプションの句です。partition_expression
は、テーブルのパーティショニング方法を決める式です。パーティション式には次の値を使用できます。
PARTITION BY DATE(_PARTITIONTIME)
:_PARTITIONTIME pseudo column
に格納された日付ベースのタイムスタンプを使用してテーブルを分割します。この構文は、AS query_statement
句がないCREATE TABLE
でのみサポートされています。PARTITION BY _PARTITIONDATE
:_PARTITIONDATE pseudo column
を使用してテーブルを分割します。この構文は、AS query_statement
句がないCREATE TABLE
でのみサポートされています。PARTITION BY DATE(_PARTITIONTIME)
と同等です。PARTITION BY DATE(<timestamp_column>)
:TIMESTAMP
列の日付を使用してテーブルを分割します。PARTITION BY RANGE_BUCKET(<integer_column>, GENERATE_ARRAY(start, end[, interval]))
: 指定されたINTEGER
列の範囲を使用してテーブルを分割します。start
は範囲パーティショニングの開始値(この値を含みます)で、end
は範囲パーティショニングの終了値(この値を含みません)です。interval
はパーティション内の各範囲の幅です。設定しない場合、デフォルトで 1 になります。PARTITION BY TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR })
:TIMESTAMP
列の日付 / 時間 / 月 / 年を使用してテーブルを分割します。PARTITION BY DATETIME_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR })
:DATETIME
列の日付 / 時間 / 月 / 年を使用してテーブルを分割します。PARTITION BY <date_column>
:DATE
列を使用してテーブルを分割します。
clustering_column_list
CLUSTER BY
は、テーブルのクラスタ化を制御するオプションの句です。clustering_column_list
は、テーブルのクラスタ化方法を決めるカンマ区切りのリストです。クラスタ化列リストには、4 個までのクラスタ化列を含めることができます。
table_option_list
オプション リストを使用すると、ラベルや有効期限などのテーブル オプションを設定できます。カンマ区切りのリストを使用して複数のオプションを含めることができます。
テーブル オプション リストは次の形式で指定します。
NAME=VALUE, ...
NAME
と VALUE
は、次のいずれかの組み合わせである必要があります。
NAME |
VALUE |
詳細 |
---|---|---|
expiration_timestamp |
TIMESTAMP |
例: このプロパティは、expirationTime テーブル リソース プロパティと同等です。 |
partition_expiration_days |
|
例: このプロパティは、timePartitioning.expirationMs テーブル リソース プロパティと同等ですが、単位はミリ秒ではなく日数です。1 日は 86,400,000 ミリ秒または 24 時間に相当します。 このプロパティは、テーブルが分割されている場合にのみ設定できます。 |
require_partition_filter |
|
例: このプロパティは、timePartitioning.requirePartitionFilter テーブル リソース プロパティと同等です。 このプロパティは、テーブルが分割されている場合にのみ設定できます。 |
kms_key_name |
|
例: このプロパティは、encryptionConfiguration.kmsKeyName テーブル リソース プロパティと同等です。 詳細については、Cloud KMS 鍵によるデータの保護をご覧ください。 |
friendly_name |
|
例: このプロパティは、friendlyName テーブル リソース プロパティと同等です。 |
description |
|
例: このプロパティは、description テーブル リソース プロパティと同等です。 |
labels |
|
例: このプロパティは、labels テーブル リソース プロパティと同等です。 |
VALUE
は、リテラル、クエリ パラメータ、スカラー関数のみを含む定数式です。定数式が null
と評価された場合、対応するオプション NAME
は無視されます。
定数式には以下を含めることはできません。
- テーブルへの参照
- サブクエリ、または
SELECT
、CREATE
、UPDATE
などの SQL ステートメント - ユーザー定義関数、集計関数、または分析関数
- 以下のスカラー関数
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
column_option_list
column_schema
内で column_option_list
を使用すると、省略可能な列またはフィールドのオプションを指定できます。列のオプションの構文と要件はテーブル オプションの場合と同じですが、NAME
と VALUE
のリストは異なります。
NAME |
VALUE |
詳細 |
---|---|---|
description |
|
例: このプロパティは、schema.fields[].description テーブル リソース プロパティと同等です。 |
query_statement
AS query_statement
句は、作成されるテーブルからのクエリを指定します。query_statement
でサポートされている形式については、SQL 構文リファレンスをご覧ください。
既知の制限事項:
- クエリ結果から取り込み時間パーティション分割テーブルを作成することはできません。その代わりに、
CREATE TABLE
DDL ステートメントを使用して取り込み時間パーティション分割テーブルを作成した後、INSERT
DML ステートメントを使用してそのテーブルにデータを挿入します。 OR REPLACE
修飾子を使用してテーブルを別の種類のパーティショニングで置き換えることはできません。その代わりに、テーブルに対してDROP
を行った後、CREATE TABLE ... AS SELECT ...
ステートメントを使用します。
一時テーブル
一時テーブルを作成するには、CREATE TABLE
ステートメントの中で、TEMP
または TEMPORARY
キーワードを使用します。
構文
{ CREATE {TEMP|TEMPORARY} TABLE | CREATE {TEMP|TEMPORARY} TABLE IF NOT EXISTS | CREATE OR REPLACE {TEMP|TEMPORARY} TABLE } ...
TEMP
、TEMPORARY
キーワード以外は、CREATE TABLE
の構文と同じです。
一時テーブル名は修飾しないでください。つまり、プロジェクトやデータセット修飾子を使用しないでください。一時テーブルは、特別なデータセットに自動的に作成されます。
現在のスクリプトの実行中に、名前で一時テーブルを参照できます。詳細については、標準 SQL のスクリプトをご覧ください。これには、スクリプト内のプロシージャによって作成されたテーブルも含まれます。スクリプトの作成が完了したら、テーブルに対してクエリを実行できません。
スクリプトが終了した後、一時テーブルは最長で 24 時間存在します。指定した名前を使用して保存されず、代わりにランダムな名前が割り当てられます。テーブルの構造とデータを表示するには、BigQuery コンソールに移動して [クエリ履歴] をクリックし、一時テーブルを作成したクエリを選択します。次に、[宛先テーブル] 行の [一時テーブル] をクリックします。
一時テーブルは共有できず、標準のリストやその他のテーブル操作の方法を使用して表示することもできません。一時テーブルの保管には料金はかかりません。
一時テーブルを作成するには、次の手順を行います。
CREATE TEMP TABLE Example
(
x INT64,
y STRING
);
INSERT INTO Example
VALUES (5, 'foo');
INSERT INTO Example
VALUES (6, 'bar');
SELECT *
FROM Example;
このスクリプトは、次の出力を返します。
+-----+---+-----+
| Row | x | y |
+-----+---|-----+
| 1 | 5 | foo |
| 2 | 6 | bar |
+-----+---|-----+
スクリプトが完了する前に一時テーブルを完全に削除するには、DROP TABLE
ステートメントを実行します。
CREATE TEMP TABLE foo(x INT64);
SELECT * FROM foo; -- Succeeds
DROP TABLE foo;
SELECT * FROM foo; -- Results in an error
デフォルトのデータセットと一緒に一時テーブルを使用する場合、非修飾テーブル名は次を参照します。
- 一時テーブル(存在する場合)
- デフォルトのデータセット内のテーブル(一時テーブルが存在しない場合)
例外は CREATE TABLE
ステートメントです。TEMP
キーワードや TEMPORARY
キーワードは存在する場合のみ、ターゲット テーブルが一時テーブルと見なされます。
たとえば、次のスクリプトについて考えてみます。
-- Create table t1 in the default dataset
CREATE TABLE t1 (x INT64);
-- Create temporary table t1
CREATE TEMP TABLE t1 (x INT64);
-- This statement will select from the temporary table
SELECT * FROM t1;
-- Drop the temporary table
DROP TABLE t1;
-- Now that the temporary table is dropped, this statement will select from
-- the table in the default dataset
SELECT * FROM t1;
一時テーブルを参照していることを明示的に示すには、テーブル名に _SESSION
修飾子を指定します。
-- Create a temp table CREATE TEMP TABLE t1 (x INT64); -- Create a temp table using the `_SESSION` qualifier CREATE TEMP TABLE _SESSION.t2 (x INT64); -- Select from a temporary table using the `_SESSION` qualifier SELECT * FROM _SESSION.t1;
存在しない一時テーブルのクエリに _SESSION
修飾子を使用すると、テーブルが存在しないことを示すエラーが返されます。たとえば、t3
という一時テーブルが存在しない場合、デフォルト データセットに t3
が存在する場合でもエラーを受信します。
一時テーブルではないテーブルを作成するために _SESSION
を使用することはできません。
CREATE TABLE _SESSION.t4 (x INT64); -- Fails
例
新しいテーブルの作成
次の例では、mydataset
に newtable
という名前のパーティション分割テーブルが作成されます。
CREATE TABLE mydataset.newtable ( x INT64 OPTIONS(description="An optional INTEGER field"), y STRUCT< a ARRAY<STRING> OPTIONS(description="A repeated STRING field"), b BOOL > ) PARTITION BY _PARTITIONDATE OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", partition_expiration_days=1, description="a table that expires in 2025, with each partition living for 24 hours", labels=[("org_unit", "development")] )
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.newtable
の代わりに、テーブル修飾子が `myproject.mydataset.newtable`
であることがあります。
テーブル名がデータセット内に存在する場合は、次のエラーが返されます。
Already Exists: project_id:dataset.table
PARTITION BY _PARTITIONDATE
という partition_expression
を使用してテーブルがパーティショニング(分割)されます。この式は、_PARTITIONDATE
擬似列に日付を使用してテーブルを分割します。
テーブル スキーマには次の 2 つの列があります。
- x - 整数と、「オプション INTEGER 型フィールド」の説明
y - 次の 2 つの列を含む STRUCT
- a - 文字列の配列と、「繰り返し STRING 型フィールド」の説明
- b - ブール値
テーブル オプション リストで指定する内容は次のとおりです。
- テーブルの有効期限 - 2025 年 1 月 1 日 00:00:00 UTC
- パーティションの有効期限 - 1 日
- 説明 - 2025 年に期限が切れるテーブル
- ラベル - org_unit = development
既存のテーブルからの新しいテーブルの作成
次の例では、クエリから top_words
という名前のテーブルを mydataset
内に作成します。
CREATE TABLE mydataset.top_words OPTIONS( description="Top ten words per Shakespeare corpus" ) AS SELECT corpus, ARRAY_AGG(STRUCT(word, word_count) ORDER BY word_count DESC LIMIT 10) AS top_words FROM bigquery-public-data.samples.shakespeare GROUP BY corpus;
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.top_words
の代わりに、テーブル修飾子が `myproject.mydataset.top_words`
であることがあります。
テーブル名がデータセット内に存在する場合は、次のエラーが返されます。
Already Exists: project_id:dataset.table
テーブル スキーマには次の 2 つの列があります。
- corpus - シェイクスピア全集の名前
top_words -
word
(STRING
)とword_count
(単語数を表すINT64
)の 2 つのフィールドを持つSTRUCT
のARRAY
テーブル オプション リストで指定する内容は次のとおりです。
- 説明 - シェイクスピア全集ごとの出現頻度が高い上位 10 個の単語
テーブルが存在しない場合にのみテーブルを作成
次の例は、mydataset
内に newtable
という名前のテーブルが存在しない場合にのみ、mydataset
内に newtable
という名前のテーブルを作成します。テーブル名がデータセットに存在する場合、エラーは返されず、アクションも実行されません。
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>) OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", description="a table that expires in 2025", labels=[("org_unit", "development")] )
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.newtable
の代わりに、テーブル修飾子が `myproject.mydataset.newtable`
であることがあります。
テーブル スキーマには次の 2 つの列があります。
- x - 整数
y - a(文字列の配列)と b(ブール値)を持つ STRUCT
テーブル オプション リストで指定する内容は次のとおりです。
- 有効期限 - 2025 年 1 月 1 日 00:00:00 UTC
- 説明 - 2025 年に期限が切れるテーブル
- ラベル - org_unit = development
テーブルの作成または置換
次の例では、mydataset
内に newtable
という名前のテーブルを作成し、mydataset
内に newtable
が存在している場合は、空のテーブルで上書きされます。
CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>) OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", description="a table that expires in 2025", labels=[("org_unit", "development")] )
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.newtable
の代わりに、テーブル修飾子が `myproject.mydataset.newtable`
であることがあります。
テーブル スキーマには次の 2 つの列があります。
- x - 整数
y - a(文字列の配列)と b(ブール値)を持つ STRUCT
テーブル オプション リストで指定する内容は次のとおりです。
- 有効期限 - 2025 年 1 月 1 日 00:00:00 UTC
- 説明 - 2025 年に期限が切れるテーブル
- ラベル - org_unit = development
REQUIRED
列を持つテーブルの作成
次の例では、mydataset
に newtable
という名前のテーブルが作成されます。CREATE TABLE
ステートメントの列定義リスト内の NOT
NULL
修飾子は、列またはフィールドが REQUIRED
モードで作成されることを指定します。
CREATE TABLE my_dataset.new_table ( x INT64 NOT NULL, y STRUCT< a ARRAY<STRING>, b BOOL NOT NULL, c FLOAT64 > NOT NULL, z STRING )
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、my_dataset.new_table
の代わりに、テーブル修飾子が `myproject.my_dataset.new_table`
であることがあります。
テーブル名がデータセット内に存在する場合は、次のエラーが返されます。
Already Exists: project_id:dataset.table
テーブル スキーマには次の 3 つの列があります。
- x -
REQUIRED
の整数 - y - a(文字列の配列)、b(
REQUIRED
のブール値)、c(NULLABLE
の float)を持つREQUIRED
の STRUCT z -
NULLABLE
の文字列
パーティション分割テーブルの作成
次の例では、DATE
列を使用した newtable
という名前のパーティション分割テーブルを mydataset
内に作成します。
CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE) PARTITION BY transaction_date OPTIONS( partition_expiration_days=3, description="a table partitioned by transaction_date" )
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.newtable
の代わりに、テーブル修飾子が `myproject.mydataset.newtable`
であることがあります。
テーブル スキーマには次の 2 つの列があります。
- transaction_id - 整数
- transaction_date - 日付
テーブル オプション リストで指定する内容は次のとおりです。
- パーティション有効期限 - 3 日
- 説明:
transaction_date
で分割されたテーブル
クエリ結果からのパーティション分割テーブルの作成
次の例では、DATE
列を使用した days_with_rain
という名前のパーティション分割テーブルを mydataset
内に作成します。
CREATE TABLE mydataset.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=365, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations can have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod2017` AS weather WHERE prcp != 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.days_with_rain
の代わりに、テーブル修飾子が `myproject.mydataset.days_with_rain`
であることがあります。
テーブル スキーマには次の 2 つの列があります。
- date - データ収集日(
DATE
) - station_name - 気象観測所の名前(
STRING
) - prcp - インチ単位の降水量(
FLOAT64
)
テーブル オプション リストで指定する内容は次のとおりです。
- パーティションの有効期限 - 1 年間
- 説明 - 降水量が測定される気象観測所(日ごとに分割)
クラスタ化テーブルの作成
例 1
次の例では、myclusteredtable
という名前のクラスタ化テーブルを mydataset
内に作成します。このテーブルは TIMESTAMP
列によって分割されたパーティション分割テーブルであり、customer_id
という名前の STRING
列によってクラスタ化されています。
CREATE TABLE mydataset.myclusteredtable ( timestamp TIMESTAMP, customer_id STRING, transaction_amount NUMERIC ) PARTITION BY DATE(timestamp) CLUSTER BY customer_id OPTIONS ( partition_expiration_days=3, description="a table clustered by customer_id" )
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.myclusteredtable
の代わりに、テーブル修飾子が `myproject.mydataset.myclusteredtable`
であることがあります。
テーブル スキーマには次の 3 つの列があります。
- timestamp - データ収集日(
TIMESTAMP
) - customer_id - お客様 ID(
STRING
) - transaction_amount - 取引金額(
NUMERIC
)
テーブル オプション リストで指定する内容は次のとおりです。
- パーティションの有効期限 - 3 日
- 説明 - 「customer_id でクラスタ化されたテーブル」
例 2
次の例では、myclusteredtable
という名前のクラスタ化テーブルを mydataset
内に作成します。テーブルは、取り込み時間パーティション分割テーブルです。
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) PARTITION BY DATE(_PARTITIONTIME) CLUSTER BY customer_id OPTIONS ( partition_expiration_days=3, description="a table clustered by customer_id" )
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.myclusteredtable
の代わりに、テーブル修飾子が `myproject.mydataset.myclusteredtable`
であることがあります。
テーブル スキーマには次の 2 つの列があります。
- customer_id - お客様 ID(
STRING
) - transaction_amount - 取引金額(
NUMERIC
)
テーブル オプション リストで指定する内容は次のとおりです。
- パーティションの有効期限 - 3 日
- 説明 - 「customer_id でクラスタ化されたテーブル」
例 3
次の例では、myclusteredtable
という名前のクラスタ化テーブルを mydataset
内に作成します。このテーブルは分割されていません。
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) CLUSTER BY customer_id OPTIONS ( description="a table clustered by customer_id" )
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.myclusteredtable
の代わりに、テーブル修飾子が `myproject.mydataset.myclusteredtable`
であることがあります。
テーブル スキーマには次の 2 つの列があります。
- customer_id - お客様 ID(
STRING
) - transaction_amount - 取引金額(
NUMERIC
)
テーブル オプション リストで指定する内容は次のとおりです。
- 説明 - 「customer_id でクラスタ化されたテーブル」
クエリ結果からクラスタ化テーブルを作成する
例 1
次の例では、クエリの結果を使用して myclusteredtable
という名前のクラスタ化テーブルを mydataset
内に作成します。このテーブルは、TIMESTAMP
列によって分割されたパーティション分割テーブルです。
CREATE TABLE mydataset.myclusteredtable ( timestamp TIMESTAMP, customer_id STRING, transaction_amount NUMERIC ) PARTITION BY DATE(timestamp) CLUSTER BY customer_id OPTIONS ( partition_expiration_days=3, description="a table clustered by customer_id" ) AS SELECT * FROM mydataset.myothertable
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.myclusteredtable
の代わりに、テーブル修飾子が `myproject.mydataset.myclusteredtable`
であることがあります。
テーブル スキーマには次の 3 つの列があります。
- timestamp - データ収集日(
TIMESTAMP
) - customer_id - お客様 ID(
STRING
) - transaction_amount - 取引金額(
NUMERIC
)
テーブル オプション リストで指定する内容は次のとおりです。
- パーティションの有効期限 - 3 日
- 説明 - 「customer_id でクラスタ化されたテーブル」
例 2
次の例では、クエリの結果を使用して myclusteredtable
という名前のクラスタ化テーブルを mydataset
内に作成します。このテーブルは分割されていません。
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) CLUSTER BY customer_id OPTIONS ( description="a table clustered by customer_id" ) AS SELECT * FROM mydataset.myothertable
デフォルト プロジェクトを構成していない場合は、プロジェクト ID をサンプル SQL のデータセット名の前に追加し、project_id
に特殊文字 `project_id.dataset.table`
が含まれる場合は、バッククォートで名前を囲みます。したがって、mydataset.myclusteredtable
の代わりに、テーブル修飾子が `myproject.mydataset.myclusteredtable`
であることがあります。
テーブル スキーマには次の 2 つの列があります。
- customer_id - お客様 ID(
STRING
) - transaction_amount - 取引金額(
NUMERIC
)
テーブル オプション リストで指定する内容は次のとおりです。
- 説明 - 「customer_id でクラスタ化されたテーブル」
CREATE VIEW
ステートメント
BigQuery でビューを作成するには、CREATE VIEW
DDL ステートメントを使用します。
{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW} [[project_name.]dataset_name.]view_name [OPTIONS(view_option_list)] AS query_expression
ここで
{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW}
は、次のステートメントのいずれかです。
CREATE VIEW
: 新しいビューを作成します。CREATE VIEW IF NOT EXISTS
: 指定されたデータセット内に現在ビューが存在しない場合にのみ、新しいビューを作成します。CREATE OR REPLACE VIEW
: 指定したデータセット内にビューを作成し、同じ名前のビューが存在する場合は置き換えます。
project_name
は、ビューを作成するプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、ビューを作成するデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
view_name
は、作成するビューの名前です。ビュー名は、データセット内で一意である必要があります。ビュー名には次の制限があります。
- 1,024 文字以内
- 英字(大文字または小文字)、数字、アンダースコアだけが含まれている
view_option_list
を使用すると、ラベルや有効期限などのビュー作成オプションを追加で指定できます。
CREATE VIEW
ステートメントは、以下の規則に従う必要があります。
- 使用できる
CREATE
ステートメントは 1 つのみです。
query_expression
は、ビューの定義に使用される標準 SQL クエリ式です。
view_option_list
オプション リストを使用すると、ラベルや有効期限などのビュー オプションを設定できます。カンマ区切りのリストを使用して複数のオプションを含めることができます。
ビュー オプション リストは次の形式で指定します。
NAME=VALUE, ...
NAME
と VALUE
は、次のいずれかの組み合わせである必要があります。
NAME |
VALUE |
詳細 |
---|---|---|
expiration_timestamp |
TIMESTAMP |
例: このプロパティは、expirationTime テーブル リソース プロパティと同等です。 |
friendly_name |
|
例: このプロパティは、friendlyName テーブル リソース プロパティと同等です。 |
description |
|
例: このプロパティは、description テーブル リソース プロパティと同等です。 |
labels |
|
例: このプロパティは、labels テーブル リソース プロパティと同等です。 |
VALUE
は、リテラル、クエリ パラメータ、スカラー関数のみを含む定数式です。定数式が null
と評価された場合、対応するオプション NAME
は無視されます。
定数式には以下を含めることはできません。
- テーブルへの参照
- サブクエリ、または
SELECT
、CREATE
、UPDATE
などの SQL ステートメント - ユーザー定義関数、集計関数、または分析関数
- 以下のスカラー関数
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
ビュー本文のデフォルト プロジェクト
ビューが CREATE VIEW
ステートメントの実行に使用されたのと同じプロジェクトで作成された場合、ビューの本文 query_expression
はプロジェクトを指定せずにエンティティを参照できます。デフォルト プロジェクトは、ビューを所有するプロジェクトです。以下のサンプルクエリを検討してください。
CREATE VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;
プロジェクト myProject
で上記の CREATE VIEW
クエリを実行した後、クエリ SELECT * FROM myProject.myDataset.myView
を実行できます。この SELECT
クエリを実行するプロジェクトとしてどのプロジェクトを選択しても、参照されるテーブル anotherDataset.myTable
は常にプロジェクト myProject
に対して解決されます。
ビューが CREATE VIEW
ステートメントの実行に使用したプロジェクトで作成されていない場合、ビューの本文 query_expression
内のすべての参照はプロジェクト ID で修飾する必要があります。たとえば、上記のサンプル CREATE VIEW
クエリは、myProject
と異なるプロジェクトで実行される場合は無効になります。
例
新しいビューの作成
次の例では、mydataset
に newview
という名前のビューが作成されます。
CREATE VIEW `myproject.mydataset.newview` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="newview", description="a view that expires in 2 days", labels=[("org_unit", "development")] ) AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
ビュー名がデータセット内に存在する場合、次のエラーが返されます。
Already Exists: project_id:dataset.table
ビューは、次の標準 SQL クエリを使用して定義されます。
SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
ビュー オプション リストで指定する内容は次のとおりです。
- 有効期限 - ビューが作成されてから 48 時間
- わかりやすい名前 - newview
- 説明 - 2 日後に有効期限が切れるビュー
- ラベル - org_unit = development
ビューが存在しない場合にのみビューを作成
次の例は、mydataset
内に newview
という名前のビューが存在しない場合にのみ、mydataset
内に newview
という名前のビューを作成します。ビュー名がデータセットに存在する場合、エラーは返されず、アクションも実行されません。
CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="newview", description="a view that expires in 2 days", labels=[("org_unit", "development")] ) AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
ビューは、次の標準 SQL クエリを使用して定義されます。
SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
ビュー オプション リストで指定する内容は次のとおりです。
- 有効期限 - ビューが作成されてから 48 時間
- わかりやすい名前 - newview
- 説明 - 2 日後に有効期限が切れるビュー
- ラベル - org_unit = development
ビューの作成または置換
次の例では、mydataset
内に newview
という名前のビューを作成し、mydataset
内に newview
が存在している場合は、指定されたクエリ式を使用して上書きされます。
CREATE OR REPLACE VIEW `myproject.mydataset.newview` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="newview", description="a view that expires in 2 days", labels=[("org_unit", "development")] ) AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
ビューは、次の標準 SQL クエリを使用して定義されます。
SELECT column_1, column_2, column_3 FROM
myproject.mydataset.mytable
ビュー オプション リストで指定する内容は次のとおりです。
- 有効期限 - ビューが作成されてから 48 時間
- わかりやすい名前 - newview
- 説明 - 2 日後に有効期限が切れるビュー
- ラベル - org_unit = development
CREATE MATERIALIZED VIEW
ステートメント
BigQuery で実体化されたビューを作成するには、CREATE
MATERIALIZED VIEW
DDL ステートメントを使用します。
{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS } [[project_name.]dataset_name.]materialized_view_name [OPTIONS(materialized_view_option_list)] AS query_expression
ここで
{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS }
は、次のステートメントのいずれかです。
CREATE MATERIALIZED VIEW
: 新しい実体化されたビューを作成します。CREATE MATERIALIZED VIEW IF NOT EXISTS
: 指定されたデータセット内に現在実体化されたビューが存在しない場合にのみ、新しい実体化されたビューを作成します。
project_name
は、ビューを作成するプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
project_name
が省略されているか、この DDL クエリを実行するプロジェクトと同じである場合、後者は query_expression
のテーブル、関数などへの参照のデフォルト プロジェクトとしても使用されます(参照のデフォルト プロジェクトは固定され、今後新しく実体化されるビューを起動するクエリに依存しません)。それ以外の場合は、query_expression
内のすべての参照をプロジェクトで修飾する必要があります。
dataset_name
は、実体化されたビューを作成するデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
materialized_view_name
は、作成する実体化されたビューの名前です。実体化されたビュー名は、データセット内で一意である必要があります。実体化されたビュー名には次の制限があります。
- 1,024 文字以内
- 英字(大文字または小文字)、数字、アンダースコアだけが含まれている
materialized_view_option_list
を使用すると、更新が有効かどうか、更新間隔、ラベル、有効期限などのマテリアライズド ビュー オプションを設定できます。
CREATE MATERIALIZED VIEW
ステートメントは、以下の規則に従う必要があります。
- 使用できる
CREATE
ステートメントは 1 つのみです。
query_expression
は、マテリアライズド ビューの定義に使用される標準 SQL クエリ式です。
materialized_view_option_list
オプション リストを使用すると、更新が有効かどうか、更新間隔、ラベル、有効期限などのマテリアライズド ビュー オプションを設定できます。カンマ区切りのリストを使用して複数のオプションを含めることができます。
実体化されたビュー オプション リストは次の形式で指定します。
NAME=VALUE, ...
NAME
と VALUE
は、次のいずれかの組み合わせである必要があります。
NAME |
VALUE |
詳細 |
---|---|---|
enable_refresh |
BOOLEAN |
例: |
refresh_interval_minutes |
FLOAT64 |
例: |
expiration_timestamp |
TIMESTAMP |
例: このプロパティは、expirationTime テーブル リソース プロパティと同等です。 |
friendly_name |
|
例: このプロパティは、friendlyName テーブル リソース プロパティと同等です。 |
description |
|
例: このプロパティは、description テーブル リソース プロパティと同等です。 |
labels |
|
例: このプロパティは、labels テーブル リソース プロパティと同等です。 |
実体化されたビュー本文のデフォルト プロジェクト
実体化されたビューが CREATE MATERIALIZED VIEW
ステートメントの実行に使用されたのと同じプロジェクトで作成された場合、実体化されたビューの本文 query_expression
はプロジェクトを指定せずにエンティティを参照できます。デフォルト プロジェクトは、実体化されたビューを所有するプロジェクトです。以下のサンプルクエリを検討してください。
CREATE MATERIALIZED VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;
プロジェクト myProject
で上記の CREATE MATERIALIZED VIEW
クエリを実行した後、クエリ SELECT * FROM myProject.myDataset.myView
を実行できます。この SELECT
クエリを実行するプロジェクトとしてどのプロジェクトを選択しても、参照されるテーブル anotherDataset.myTable
は常にプロジェクト myProject
に対して解決されます。
実体化されたビューが CREATE VIEW
ステートメントの実行に使用したプロジェクトで作成されていない場合、実体化されたビューの本文 query_expression
内のすべての参照はプロジェクト ID で修飾する必要があります。たとえば、上記のサンプル CREATE MATERIALIZED VIEW
クエリは、myProject
と異なるプロジェクトで実行される場合は無効になります。
例
新しい実体化されたビューの作成
次の例では、mydataset
に new_mv
という名前の実体化されたビューが作成されます。
CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
OPTIONS(
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
friendly_name="new_mv",
description="a materialized view that expires in 2 days",
labels=[("org_unit", "development")],
enable_refresh=true,
refresh_interval_minutes=20
)
AS SELECT column_1, SUM(column_2) AS sum_2, AVG(column_3) AS avg_3
FROM `myproject.mydataset.mytable`
GROUP BY column_1
実体化されたビュー名がデータセット内に存在する場合、次のエラーが返されます。
Already Exists: project_id:dataset.materialized_view
DDL ステートメントを使用して実体化されたビューを作成する場合は、`project_id.dataset.materialized_view`
(project_id
に特殊文字が含まれる場合、バッククォートを含む)の形式でプロジェクト、データセット、実体化されたビューを指定する必要があります(例: `myproject.mydataset.new_mv`
)。
実体化されたビューの定義には次の標準 SQL クエリを使用します。
SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
実体化されたビュー オプション リストで指定する内容は次のとおりです。
- 有効期限 - 実体化されたビューが作成されてから 48 時間
- わかりやすい名前 - new_view
- 説明 - 2 日後に有効期限が切れる実体化されたビュー
- ラベル - org_unit = development
- 更新が有効 - true
- 更新間隔 - 20 分
実体化されたビューが存在しない場合にのみ実体化されたビューを作成
次の例は、mydataset
内に new_mv
という名前の実体化されたビューが存在しない場合にのみ、mydataset
内に new_mv
という名前の実体化されたビューを作成します。実体化されたビュー名がデータセットに存在する場合、エラーは返されず、アクションも実行されません。
CREATE MATERIALIZED VIEW IF NOT EXISTS `myproject.mydataset.new_mv`
OPTIONS(
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
friendly_name="new_mv",
description="a view that expires in 2 days",
labels=[("org_unit", "development")],
enable_refresh=false
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
実体化されたビューの定義には次の標準 SQL クエリを使用します。
SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
実体化されたビュー オプション リストで指定する内容は次のとおりです。
- 有効期限 - ビューが作成されてから 48 時間
- わかりやすい名前 - new_view
- 説明 - 2 日後に有効期限が切れるビュー
- ラベル - org_unit = development
- 更新が有効 - false
CREATE EXTERNAL TABLE
ステートメント
CREATE EXTERNAL TABLE
ステートメントは外部テーブルを作成します。外部テーブルを使用すると、BigQuery ストレージの外部で保存されているデータを BigQuery で照会できます。外部テーブルの詳細については、外部データソースの概要をご覧ください。
CREATE [OR REPLACE] EXTERNAL TABLE [IF NOT EXISTS] [[project_name.]dataset_name.]table_name [( column_name column_schema, ... )] [WITH PARTITION COLUMNS [( partition_column_name partition_column_type, ... )] ] OPTIONS ( external_table_option_list, ... );
ここで
project_name
は、テーブルを作成するプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。dataset_name
は、テーブルを作成するデータセットの名前です。table_name
は外部テーブルの名前です。column_name
はテーブルの列の名前です。column_schema
には列のスキーマを指定します。これは、CREATE TABLE
ステートメントのcolumn_schema
定義と同じ構文を使用します。この句を指定しない場合、BigQuery はスキーマを自動的に検出します。partition_column_name
は、パーティション列の名前です。外部データが Hive パーティション分割レイアウトを使用している場合は、このフィールドを指定します。詳細については、サポートされるデータ レイアウトをご覧ください。partition_column_type
はパーティション列のタイプです。external_table_option_list
には、外部テーブルを作成するためのオプションのリストを指定します。
external_table_option_list
オプション リストには、外部テーブルを作成するためのオプションを指定します。format
オプションと uris
オプションは必須です。オプション リストを NAME=VALUE, ...
の形式で指定します。
オプション | |
---|---|
allow_jagged_rows |
CSV データに適用されます。 |
allow_quoted_newlines |
CSV データに適用されます。 |
compression |
データソースの圧縮タイプ。サポートされる値: CSV データと JSON データに適用されます。 |
description |
このテーブルの説明。 |
enable_logical_types |
Avro データに適用されます。 |
encoding |
データの文字エンコード。サポートされている値: CSV データに適用されます。 |
expiration_timestamp |
このテーブルの有効期限。指定しない場合、テーブルは期限切れになりません。 例: |
field_delimiter |
CSV ファイル内のフィールド区切り文字。 CSV データに適用されます。 |
format |
外部データの形式。サポートされる値: 値 |
decimal_target_types |
例: |
hive_partition_uri_prefix |
パーティション キーのエンコードを開始する前のすべてのソース URI の一般的なプレフィックス。Hive パーティション分割された外部テーブルにのみ適用されます。 Avro、CSV、JSON、Parquet、ORC のデータに適用されます。 例: |
ignore_unknown_values |
CSV データと JSON データに適用されます。 |
max_bad_records |
データの読み取り時に無視する不良レコードの最大数。 適用対象: CSV、JSON、スプレッドシートのデータ。 |
null_marker |
CSV ファイル内の CSV データに適用されます。 |
projection_fields |
読み込むエンティティ プロパティのリスト。 Datastore データに適用されます。 |
quote |
CSV ファイルのデータ セクションを引用するために使用される文字列。データに引用符で囲まれた改行文字が含まれている場合は、 CSV データに適用されます。 |
require_hive_partition_filter |
Avro、CSV、JSON、Parquet、ORC のデータに適用されます。 |
sheet_range |
クエリの対象となるスプレッドシートのスプレッドシートの範囲。 スプレッドシートのデータに適用されます。 例: |
skip_leading_rows |
データを読み取る際にスキップするファイルの先頭行の数。 CSV データとスプレッドシートのデータに適用されます。 |
uris |
外部データのロケーションの完全修飾 URI の配列。 例: |
CREATE EXTERNAL TABLE
ステートメントは、外部の一時テーブルの作成をサポートしていません。
外部パーティション分割テーブルを作成するには、WITH PARTITION COLUMNS
句を使用して、パーティション スキーマの詳細を指定します。BigQuery は外部データソースに対して列定義を検証します。スキーマの宣言は、外部パスのフィールドの順序に厳密に従わなければなりません。外部パーティショニングの詳細については、外部パーティション分割データのクエリをご覧ください。
例
次の例では、複数の URI から外部テーブルを作成します。データ形式は CSV です。この例では、スキーマの自動検出を使用しています。
CREATE EXTERNAL TABLE dataset.CsvTable OPTIONS (
format = 'CSV',
uris = ['gs://bucket/path1.csv', 'gs://bucket/path2.csv']
);
次の例では、CSV ファイルから外部テーブルを作成し、スキーマを明示的に指定しています。また、フィールド境界('|'
)を指定し、許可される不良レコードの最大数を設定します。
CREATE OR REPLACE EXTERNAL TABLE dataset.CsvTable
(
x INT64,
y STRING
)
OPTIONS (
format = 'CSV',
uris = ['gs://bucket/path1.csv'],
field_delimiter = '|',
max_bad_records = 5
);
次の例では、外部パーティション分割テーブルを作成します。スキーマの自動検出を使用して、ファイル スキーマと Hive パーティショニング レイアウトの両方を検出します。
たとえば、外部パスが gs://bucket/path/field_1=first/field_2=1/data.csv
の場合、パーティション列は field_1
(STRING
)と field_2
(INT64
)になります。
CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris=['gs://bucket/path/*'],
format=csv,
hive_partition_uri_prefix='gs://bucket/path'
);
次の例では、パーティション列を明示的に指定することで外部パーティション分割テーブルを作成します。この例は、外部ファイルのパスのパターンが gs://bucket/path/field_1=first/field_2=1/data.csv
であることを前提としています。
CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64
)
OPTIONS (
uris=['gs://bucket/path/*'],
format=csv,
hive_partition_uri_prefix='gs://bucket/path'
);
CREATE FUNCTION
ステートメント
BigQuery はユーザー定義関数(UDF)をサポートしています。UDF を使用すると、SQL 式または JavaScript を使用して関数を作成できます。これらの関数は入力列を受け取ってアクションを実行し、その結果を値として返します。
UDF には永続的なものと一時的なものがあります。永続的な UDF は複数のクエリで再利用できるのに対し、一時的な UDF は 1 つのクエリ内でのみ使用できます。UDF の詳細については、ユーザー定義の関数をご覧ください。
UDF の構文
永続的な UDF を作成するには、次の構文を使用します。
CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] [[project_name.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] { sql_function_definition | javascript_function_definition }
一時的な UDF を作成するには、次の構文を使用します。
CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS] function_name ([named_parameter[, ...]]) [RETURNS data_type] { sql_function_definition | javascript_function_definition }
named_parameter: param_name param_type sql_function_definition: AS (sql_expression) javascript_function_definition: [determinism_specifier] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code determinism_specifier: { DETERMINISTIC | NOT DETERMINISTIC }
この構文は、次のコンポーネントで構成されています。
CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }。関数を作成または更新します。同じ名前を持つ既存の関数を置き換えるには、
OR REPLACE
キーワードを使用します。同じ名前の関数がすでに存在する場合に、実際には何もせずにクエリを成功したものとして扱うには、IF NOT EXISTS
句を使用します。project_name は、関数を作成するプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート
`
で囲む必要があります(例:`google.com:my_project`
)。dataset_name は、関数を作成するデータセットの名前です。デフォルトでは、リクエスト内の
defaultDataset
の名前が設定されます。named_parameter。カンマで区切られた
param_name
とparam_type
のペアで構成されます。param_type
の値は BigQuery のデータ型です。SQL UDF の場合は、param_type
の値をANY TYPE
にすることもできます。determinism_specifierJavaScript のユーザー定義関数にのみ適用されます。クエリ結果をキャッシュに保存できるかどうかについてのヒントを BigQuery に提供します。次のいずれかの値です。
DETERMINISTIC
: 同じ引数を渡すと、常に同じ結果が返されます。クエリ結果はキャッシュに保存できる可能性があります。たとえば、関数add_one(i)
が常にi + 1
を返す場合、この関数は確定的です。NOT DETERMINISTIC
: 同じ引数を渡しても、同じ結果が返されるとは限りません。このため、キャッシュに保存できません。たとえば、add_random(i)
がi + rand()
を返す場合、関数は確定的ではないため、BigQuery はキャッシュに保存された結果を使用しません。呼び出された関数がすべて DETERMINISTIC の場合、他の理由で結果をキャッシュに保存できない場合を除き、BigQuery は結果をキャッシュに保存しようとします。詳細については、キャッシュに保存されているクエリ結果を使用するをご覧ください。
[RETURNS data_type]。関数が返すデータ型を指定します。
- 関数が SQL で定義されている場合、
RETURNS
句はオプションです。RETURNS
句を省略した場合、BigQuery は、クエリが関数を呼び出すときに SQL 関数本文から関数の結果の型を推測します。 - 関数が JavaScript で定義されている場合、
RETURNS
句は必須です。data_type
で使用できる値の詳細については、サポートされた JavaScript UDF データ型をご覧ください。
- 関数が SQL で定義されている場合、
AS (sql_expression)。関数を定義する SQL 式を指定します。
[OPTIONS (library = library_array)]。JavaScript UDF の関数定義に含める JavaScript ライブラリの配列を指定します。
AS javascript_code。JavaScript 関数の定義を指定します。
javascript_code
は、文字列リテラルです。
SQL UDF の構造
次の構文を使用して、SQL UDF を作成します。
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] AS (sql_expression) named_parameter: param_name param_type
SQL UDF テンプレート パラメータ
関数が呼び出されるとき、param_type
= ANY TYPE
のテンプレート パラメータは複数の引数型と一致する場合があります。
ANY TYPE
型のパラメータが複数ある場合、BigQuery では、これらの引数間に型の関係が適用されることはありません。- 関数の戻り値の型を
ANY TYPE
に指定することはできません。省略することでsql_expression
を基に自動的に決定するようにするか、明示的な型を指定する必要があります。 - 関数定義に適合しない型の関数引数を渡すと、呼び出し時にエラーになります。
SQL UDF 本文のデフォルト プロジェクト
SQL UDF が CREATE FUNCTION
ステートメントの実行に使用したプロジェクトで作成されている場合、UDF 本文 sql_expression
はプロジェクトを指定せずにエンティティを参照できます。デフォルト プロジェクトは、UDF を所有するプロジェクトです。以下のサンプルクエリを検討してください。
CREATE FUNCTION myProject.myDataset.myFunction() AS (anotherDataset.anotherFunction());
プロジェクト myProject
で上記の CREATE FUNCTION
クエリを実行した後、クエリ SELECT myProject.myDataset.myFunction()
を実行できます。この SELECT
実行するプロジェクトとしてどのプロジェクトを選択しても、参照される関数 anotherDataset.anotherFunction
は常にプロジェクト myProject
に対して解決されます。
UDF が CREATE FUNCTION
ステートメントの実行に使用したプロジェクトで作成されていない場合、UDF の本文 sql_expression
内のすべての参照はプロジェクト ID で修飾する必要があります。たとえば、上記のサンプル CREATE FUNCTION
クエリは、myProject
と異なるプロジェクトで実行される場合は無効になります。
SQL UDF の例
次の例では、永続的な SQL UDF を作成します。これは、mydataset
という名前のデータセットがアクティブ プロジェクトに存在することを前提とします。この名前のデータセットが存在しない場合は、データセットの作成に関するドキュメントをご覧ください。
CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
AS (x * y);
上記の CREATE FUNCTION
ステートメントを実行した後、この新しい永続的なユーザー定義関数を別のクエリで使用できます。クエリエディタの内容を次のように書き換えてクエリを実行します。
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x, y, mydataset.multiplyInputs(x, y) as product
FROM numbers;
上記の例では、次の出力が生成されます。
+-----+-----+--------------+
| x | y | product |
+-----+-----+--------------+
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 15 | 45 |
+-----+-----+--------------+
次の例は、テンプレート パラメータを使用した永続的な SQL UDF を示します。この関数は、実行時にさまざまな型の引数を受け入れます。
CREATE FUNCTION mydataset.addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
(x + 4) / y
);
上記の CREATE FUNCTION
ステートメントを実行した後、この新しい永続的なユーザー定義関数を別のクエリで使用できます。
SELECT addFourAndDivideAny(3, 4) AS integer_output,
addFourAndDivideAny(1.59, 3.14) AS floating_point_output;
このクエリは次の出力を返します。
+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75 | 1.7802547770700636 |
+----------------+-----------------------+
次は、SQL UDF でテンプレート パラメータを使用して任意の型の配列の最後の要素を返す例を示します。
CREATE FUNCTION mydataset.lastArrayElement(arr ANY TYPE) AS (
arr[ORDINAL(ARRAY_LENGTH(arr))]
);
上記の CREATE FUNCTION
ステートメントを実行した後、この新しい永続的なユーザー定義関数を別のクエリで使用できます。
SELECT
names[OFFSET(0)] AS first_name,
lastArrayElement(names) AS last_name
FROM (
SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
SELECT ['Marie', 'Skłodowska', 'Curie']
);
上記のクエリは次の出力を返します。
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred | Rogers |
| Marie | Curie |
+------------+-----------+
JavaScript UDF の構造
永続的な JavaScript UDF を作成するには、次の構文を使用します。
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) RETURNS data_type [DETERMINISTIC | NOT DETERMINISTIC] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code
data_type
に指定できる値やパラメータ タイプについての詳細は、サポートされている JavaScript UDF データ型をご覧ください。
JavaScript UDF の例
CREATE TEMP FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
return x*y;
""";
上記の CREATE FUNCTION
ステートメントを実行した後、この新しい永続的な JavaScript UDF を別のクエリで使用できます。
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;
上記の例では、次の出力が返されます。
+-----+-----+--------------+
| x | y | product |
+-----+-----+--------------+
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 15 | 45 |
+-----+-----+--------------+
UDF の結果を別の UDF への入力として渡すことができます。たとえば、次のクエリで永続 UDF を作成します。
CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
return x*y;
""";
次に、次のクエリを実行して 2 つ目の永続 UDF を作成します。
CREATE FUNCTION mydataset.divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
return x/2;
""";
これで、次のように同じクエリ内で両方の永続 UDF を使用できます。
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x,
y,
mydataset.multiplyInputs(
mydataset.divideByTwo(x), mydataset.divideByTwo(y)) as half_product
FROM numbers;
上記の例では、次の出力が返されます。
+-----+-----+--------------+
| x | y | half_product |
+-----+-----+--------------+
| 1 | 5 | 1.25 |
| 2 | 10 | 5 |
| 3 | 15 | 11.25 |
+-----+-----+--------------+
次の例では、指定された JSON 文字列内の "foo" という名前のすべてのフィールドの値を合計します。
CREATE FUNCTION mydataset.SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js
AS """
function SumFoo(obj) {
var sum = 0;
for (var field in obj) {
if (obj.hasOwnProperty(field) && obj[field] != null) {
if (typeof obj[field] == "object") {
sum += SumFoo(obj[field]);
} else if (field == "foo") {
sum += obj[field];
}
}
}
return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";
上記の CREATE FUNCTION
ステートメントを実行した後、この新しい永続的なユーザー定義関数を別のクエリで使用できます。
WITH Input AS (
SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
SELECT NULL, 4 AS foo UNION ALL
SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
TO_JSON_STRING(t) AS json_row,
mydataset.SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
上記の例では、次の出力が返されます。
+---------------------------------------------------------------------+---------+
| json_row | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10} | 14.14 |
| {"s":null,"foo":4} | 4 |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59 |
+---------------------------------------------------------------------+---------+
引用符のルール
JavaScript コードを引用符で囲む必要があります。コードが 1 行だけの場合は、標準の引用符付き文字列を使用できます。
CREATE FUNCTION mydataset.plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";
スニペットに引用符が含まれている場合、またはスニペットが複数の行で構成されている場合は、三重引用符のブロックを使用します。
CREATE FUNCTION mydataset.customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
var d = new Date();
if (d.getHours() < 12) {
return 'Good Morning, ' + a + '!';
} else {
return 'Good Evening, ' + a + '!';
}
""";
JavaScript ライブラリを含める
OPTIONS
セクションを使用して JavaScript UDF を拡張できます。このセクションによって UDF に JavaScript コード ライブラリを指定できます。
CREATE FUNCTION mydataset.myFunc(a FLOAT64, b STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (
library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
)
AS
"""
// Assumes 'doInterestingStuff' is defined in one of the library files.
return doInterestingStuff(a, b);
""";
SELECT mydataset.myFunc(3.14, 'foo');
上記の例では、lib1.js
、lib2.js
のライブラリに含まれているコードを、UDF の javascript_code
セクションのどのコードからでも使用できます。ライブラリ ファイルは単一要素または配列の構文で指定できることに注意してください。
UDF と Cloud Console
Cloud Console を使用して、永続的なユーザー定義関数を作成できます。
永続的な UDF を作成するクエリの実行
Cloud Console の BigQuery ページに移動します。
[クエリを新規作成] をクリックします。
[クエリエディタ] テキスト領域に UDF ステートメントを入力します。次に例を示します。
CREATE FUNCTION mydataset.timesTwo(x FLOAT64) RETURNS FLOAT64 LANGUAGE js AS """ return x*2; """;
[実行] をクリックします。
永続的なユーザー定義関数を作成した後、エディタの内容を、その関数を使用した新しいクエリに書き換えます。
SELECT mydataset.timesTwo(numbers) AS doubles FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
[実行] をクリックします。
UDF と bq コマンドライン ツール
Cloud SDK に含まれる bq コマンドライン ツールを使用して、永続 UDF を作成できます。
永続 UDF を作成するクエリを実行するには、次の構文を使用します。
bq query --use_legacy_sql=false '
CREATE FUNCTION mydataset.AddTwo(x INT64) AS (x + 2);
'
CREATE PROCEDURE
ステートメント
他のクエリから呼び出すことができるステートメントのブロックであるプロシージャを作成します。
CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] [[project_name.]dataset_name.]procedure_name (procedure_argument[, ...] ) [OPTIONS(procedure_option_list)] BEGIN statement_list END; procedure_argument: [procedure_argument_mode] argument_name argument_type
procedure_argument_mode: IN | OUT | INOUT
説明
project_name
は、プロシージャを作成するプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、プロシージャを作成するデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
statement_list
は、BigQuery のステートメント リストです。ステートメント リストは、それぞれがセミコロンで終わる一連のステートメントです。
argument_type
は、任意の有効な BigQuery のタイプです。
procedure_argument_mode
は、引数が入力、出力、またはその両方のいずれであるかを指定します。
プロシージャは、それ自体を再帰的に呼び出すことができます。
procedure_option_list
procedure_option_list
では、プロシージャのオプションを指定できます。プロシージャのオプションの構文と要件はテーブル オプションの場合と同じですが、NAME
と VALUE
のリストは異なります。
NAME |
VALUE |
詳細 |
---|---|---|
strict_mode |
|
例:
デフォルト値は |
引数モード
IN
は、引数がプロシージャへの入力にすぎないということを示します。IN
引数には、変数または値の式のいずれかを指定できます。
OUT
は、引数がプロシージャの出力であることを示します。OUT
引数は、プロシージャが開始するときに NULL
に初期化されます。OUT
引数には変数を指定する必要があります。
INOUT
は、引数がプロシージャからの入力と出力の両方であることを示します。INOUT
引数には変数を指定する必要があります。INOUT
引数は、プロシージャ本体で変数として参照され、新しい値が割り当てられます。
IN
、OUT
、INOUT
のいずれも指定されていない場合、引数は IN
引数として扱われます。
変数のスコープ
変数がプロシージャの外部で宣言され、INOUT 引数または OUT 引数としてプロシージャに渡され、プロシージャがその変数に新しい値を割り当てると、新しい値はプロシージャの外部に表示されます。
プロシージャ内で宣言された変数は、プロシージャ外では表示できません。逆に、プロシージャ外で宣言された変数は、プロシージャ内では表示できません。
OUT
引数または INOUT
引数には、SET
を使用して値が割り当てられる場合があります。この場合、変更された値はプロシージャの外部に表示されます。プロシージャが正常に終了した場合、OUT
引数または INOUT
引数は INOUT
変数に最後に割り当てられた値になります。
一時テーブルはスクリプトの存続期間中に存在するため、プロシージャが一時テーブルを作成する場合、プロシージャの呼び出し側も一時テーブルを参照できます。
プロシージャ本文のデフォルト プロジェクト
プロシージャの本文は、プロジェクトを指定せずにエンティティを参照できます。プロシージャを所有するプロジェクトがデフォルトのプロジェクトになりますが、このプロジェクトが CREATE PROCEDURE
ステートメントの実行に使用されるとは限りません。以下のサンプルクエリを検討してください。
CREATE PROCEDURE myProject.myDataset.QueryTable()
BEGIN
SELECT * FROM anotherDataset.myTable;
END;
上記のプロシージャを作成したら、クエリ CALL myProject.myDataset.QueryTable()
を実行できます。この CALL
クエリを実行するプロジェクトとしてどのプロジェクトを選択しても、参照されるテーブル anotherDataset.myTable
は常にプロジェクト myProject
に対して解決されます。
例
次の例では、入力引数として x
を取り、出力として x
を返しています。delta
引数は、引数モードが指定されていないため入力引数になります。このプロシージャは、1 つのステートメントを含むブロックで構成され、2 つの入力引数の合計を x
に割り当てます。
CREATE PROCEDURE mydataset.AddDelta(INOUT x INT64, delta INT64)
BEGIN
SET x = x + delta;
END;
次の例では、上記の例の AddDelta
プロシージャを呼び出しています。2 つの呼び出しの両方で、accumulator
変数を渡しています。ここで、AddDelta
内の x
の変化は AddDelta
の外に表示されるので、これらのプロシージャ呼び出しによって accumulator
が合計で 8 加算されます。
DECLARE accumulator INT64 DEFAULT 0;
CALL mydataset.AddDelta(accumulator, 5);
CALL mydataset.AddDelta(accumulator, 3);
SELECT accumulator;
これにより、次の結果が返されます。
+-------------+
| accumulator |
+-------------+
| 8 |
+-------------+
次の例では、target_date
を入力引数、rows_added
を出力とするプロシージャ SelectFromTablesAndAppend
を作成しています。このプロシージャは、クエリから一時テーブル DataForTargetDate
を作成し、DataForTargetDate
内の行数を計算した結果を rows_added
に代入します。次に、target_date
の値を列名の 1 つとして渡して、新しい行が TargetTable
に挿入されます。最後に、テーブル DataForTargetDate
を削除して rows_added
を返します。
CREATE PROCEDURE mydataset.SelectFromTablesAndAppend(
target_date DATE, OUT rows_added INT64)
BEGIN
CREATE TEMP TABLE DataForTargetDate AS
SELECT t1.id, t1.x, t2.y
FROM dataset.partitioned_table1 AS t1
JOIN dataset.partitioned_table2 AS t2
ON t1.id = t2.id
WHERE t1.date = target_date
AND t2.date = target_date;
SET rows_added = (SELECT COUNT(*) FROM DataForTargetDate);
SELECT id, x, y, target_date -- note that target_date is a parameter
FROM DataForTargetDate;
DROP TABLE DataForTargetDate;
END;
次の例では、変数 rows_added
を宣言し、CURRENT_DATE
の値とともに上記の例の SelectFromTablesAndAppend
プロシージャに引数として渡しています。追加された行数を示すメッセージが返されます。
DECLARE rows_added INT64;
CALL mydataset.SelectFromTablesAndAppend(CURRENT_DATE(), rows_added);
SELECT FORMAT('Added %d rows', rows_added);
ALTER TABLE SET OPTIONS
ステートメント
BigQuery のテーブルに対するオプションを設定するには、ALTER TABLE SET OPTIONS
DDL ステートメントを使用します。
ALTER TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name SET OPTIONS(table_set_options_list)
ここで
IF EXISTS
: 指定されている場合、指定したテーブルが存在しなければクエリは成功します。指定されていない場合、指定したテーブルが存在しなければクエリは失敗します。
project_name
は、変更するテーブルを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、変更するテーブルを含むデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
table_name
は、変更するテーブルの名前です。
table_set_options_list
オプション リストを使用すると、ラベルや有効期限などのテーブル オプションを設定できます。カンマ区切りのリストを使用して複数のオプションを含めることができます。
テーブル オプション リストは次の形式で指定します。
NAME=VALUE, ...
NAME
と VALUE
は、次のいずれかの組み合わせである必要があります。
NAME |
VALUE |
詳細 |
---|---|---|
expiration_timestamp |
TIMESTAMP |
例: このプロパティは、expirationTime テーブル リソース プロパティと同等です。 |
partition_expiration_days |
|
例: このプロパティは、timePartitioning.expirationMs テーブル リソース プロパティと同等ですが、単位はミリ秒ではなく日数です。1 日は 86,400,000 ミリ秒または 24 時間に相当します。 このプロパティは、テーブルが分割されている場合にのみ設定できます。 |
require_partition_filter |
|
例: このプロパティは、timePartitioning.requirePartitionFilter テーブル リソース プロパティと同等です。 このプロパティは、テーブルが分割されている場合にのみ設定できます。 |
kms_key_name |
|
例: このプロパティは、encryptionConfiguration.kmsKeyName テーブル リソース プロパティと同等です。 詳細については、Cloud KMS 鍵によるデータの保護をご覧ください。 |
friendly_name |
|
例: このプロパティは、friendlyName テーブル リソース プロパティと同等です。 |
description |
|
例: このプロパティは、description テーブル リソース プロパティと同等です。 |
labels |
|
例: このプロパティは、labels テーブル リソース プロパティと同等です。 |
VALUE
は、リテラル、クエリ パラメータ、スカラー関数のみを含む定数式です。定数式が null
と評価された場合、対応するオプション NAME
は無視されます。
定数式には以下を含めることはできません。
- テーブルへの参照
- サブクエリ、または
SELECT
、CREATE
、UPDATE
などの SQL ステートメント - ユーザー定義関数、集計関数、または分析関数
- 以下のスカラー関数
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
VALUE
を設定すると、オプションが存在する場合は、テーブルのそのオプションの既存の値が置き換えられます。VALUE
を NULL
に設定すると、テーブルのそのオプションの値が消去されます。
例
テーブルの有効期限タイムスタンプと説明の設定
次の例では、テーブルの有効期限タイムスタンプを ALTER TABLE
ステートメントの実行時刻から 7 日後に設定し、説明も設定します。
ALTER TABLE mydataset.mytable SET OPTIONS ( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY), description="Table that expires seven days from now" )
パーティション分割テーブルにパーティション フィルタ必須の属性を設定
次の例では、パーティション分割テーブルに timePartitioning.requirePartitionFilter
属性を設定しています。
ALTER TABLE mydataset.mypartitionedtable SET OPTIONS (require_partition_filter=true)
このテーブルを参照するクエリではパーティショニング列に対するフィルタを使用する必要があります。そうしないと BigQuery はエラーを返します。このオプションを true
に設定すると、意図したよりも多くのデータのクエリを行う際の間違いを防ぐことができます。
テーブルの有効期限タイムスタンプの消去
次の例では、期限切れにならないように、テーブルの有効期限タイムスタンプを消去します。
ALTER TABLE mydataset.mytable SET OPTIONS (expiration_timestamp=NULL)
ALTER TABLE ADD COLUMN
ステートメント
ALTER TABLE ADD COLUMN
ステートメントは、既存のテーブル スキーマに 1 つ以上の新しい列を追加します。BigQuery でスキーマを変更する方法については、テーブル スキーマの変更をご覧ください。
ALTER TABLE [[project_name.]dataset_name.]table_name
ADD COLUMN [IF NOT EXISTS] column_name column_schema [, ...]
ここで
project_name
は、テーブルを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。dataset_name
は、テーブルを含むデータセットの名前です。table_name
は、変更するテーブルの名前です。このテーブルはすでに存在し、スキーマが必要です。column_name
は、追加する列の名前です。column_schema
は列のスキーマです。このスキーマでは、CREATE TABLE
ステートメントの列スキーマと同じ構文を使用します。
このステートメントで以下のものを作成することはできません。
- パーティション分割テーブル。
- クラスタリング列。
- 既存の
RECORD
フィールド内にネストされた列。
REQUIRED
列を既存のテーブル スキーマに追加することはできません。ネストされた REQUIRED
列は、新しいRECORD
フィールドの一部として作成します。
IF NOT EXISTS
句がなく、テーブルにその名前の列がすでに存在する場合は、ステートメントがエラーを返します。IF NOT EXISTS
句があり、列名がすでに存在する場合、エラーは返されず、アクションも実行されません。
既存の行の新しい列の値は、次のいずれかに設定されます。
- 新しい列が
NULLABLE
モードで追加された場合はNULL
。これがデフォルト モードです。 - 新しい列が
REPEATED
モードで追加された場合は、空のARRAY
。
例
列の追加
次の例では、mytable
という名前の既存のテーブルに次の列を追加します。
STRING
型の列A
。GEOGRAPHY
型の列B
。NUMERIC
型でREPEATED
モードの列C
。DATE
型の列D
と説明。
ALTER TABLE mydataset.mytable
ADD COLUMN A STRING,
ADD COLUMN IF NOT EXISTS B GEOGRAPHY,
ADD COLUMN C ARRAY<NUMERIC>,
ADD COLUMN D DATE OPTIONS(description="my description")
A
、C
、D
という名前の列のいずれかが存在する場合、ステートメントは失敗します。列 B
が存在する場合、IF NOT
EXISTS
句によりステートメントが成功します。
RECORD
列の追加
次の例では、次のネストされた列を含む STRUCT
型の A
という列を追加します。
GEOGRAPHY
型の列B
。INT64
型でREPEATED
モードの列C
。INT64
型でREQUIRED
モードの列D
。TIMESTAMP
型の列E
と説明。
ALTER TABLE mydataset.mytable
ADD COLUMN A STRUCT<
B GEOGRAPHY,
C ARRAY<INT64>,
D INT64 NOT NULL,
E TIMESTAMP OPTIONS(description="creation time")
>
指定した列がネストされていない場合でも、テーブルに A
という名前の列が存在すると、クエリは失敗します。
A
という新しい STRUCT
は null 値を許容できますが、A
内のネストされた列 D
は A
のすべての STRUCT
値で必要になります。
ALTER VIEW SET OPTIONS
ステートメント
BigQuery のビューのオプションを設定するには、ALTER VIEW SET OPTIONS
DDL ステートメントを使用します。
ALTER VIEW [IF EXISTS] [[project_name.]dataset_name.]view_name SET OPTIONS(view_set_options_list)
ここで
IF EXISTS
: 指定されている場合、指定したビューが存在しなければクエリは成功します。指定されていない場合、指定したビューが存在しなければクエリは失敗します。
project_name
は、変更するビューを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、変更するビューを含むデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
view_name
は、変更するビューの名前です。
view_set_options_list
オプション リストを使用すると、ラベルや有効期限などのビュー オプションを設定できます。カンマ区切りのリストを使用して複数のオプションを含めることができます。
ビュー オプション リストは次の形式で指定します。
NAME=VALUE, ...
NAME
と VALUE
は、次のいずれかの組み合わせである必要があります。
NAME |
VALUE |
詳細 |
---|---|---|
expiration_timestamp |
TIMESTAMP |
例: このプロパティは、expirationTime テーブル リソース プロパティと同等です。 |
friendly_name |
|
例: このプロパティは、friendlyName テーブル リソース プロパティと同等です。 |
description |
|
例: このプロパティは、description テーブル リソース プロパティと同等です。 |
labels |
|
例: このプロパティは、labels テーブル リソース プロパティと同等です。 |
VALUE
は、リテラル、クエリ パラメータ、スカラー関数のみを含む定数式です。定数式が null
と評価された場合、対応するオプション NAME
は無視されます。
定数式には以下を含めることはできません。
- テーブルへの参照
- サブクエリ、または
SELECT
、CREATE
、UPDATE
などの SQL ステートメント - ユーザー定義関数、集計関数、または分析関数
- 以下のスカラー関数
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
VALUE
を設定すると、オプションが存在する場合は、ビューのそのオプションの既存の値が置き換えられます。VALUE
を NULL
に設定すると、ビューのそのオプションの値が消去されます。
例
ビューの有効期限タイムスタンプと説明の設定
次の例では、ビューの有効期限タイムスタンプを ALTER VIEW
ステートメントの実行時刻から 7 日後に設定し、説明も設定します。
ALTER VIEW mydataset.myview SET OPTIONS ( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY), description="View that expires seven days from now" )
ALTER MATERIALIZED VIEW SET OPTIONS
ステートメント
BigQuery の実体化されたビューのオプションを設定するには、ALTER MATERIALIZED VIEW SET OPTIONS
DDL ステートメントを使用します。
ALTER MATERIALIZED VIEW [IF EXISTS] [[project_name.]dataset_name.]materialized_view_name SET OPTIONS(materialized_view_set_options_list)
ここで
IF EXISTS
: 指定されている場合、指定したビューが存在しなければクエリは成功します。指定されていない場合、指定したビューが存在しなければクエリは失敗します。
project_name
は、変更する実体化されたビューを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、変更する実体化されたビューを含むデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
materialized_view_name
は、変更するマテリアライズド ビューの名前です。
materialized_view_set_options_list
オプション リストを使用すると、更新が有効かどうか、更新間隔、ラベル、有効期限などのマテリアライズド ビュー オプションを設定できます。カンマ区切りのリストを使用して複数のオプションを含めることができます。
実体化されたビュー オプション リストは次の形式で指定します。
NAME=VALUE, ...
NAME
と VALUE
は、次のいずれかの組み合わせである必要があります。
NAME |
VALUE |
詳細 |
---|---|---|
enable_refresh |
BOOLEAN |
例: |
refresh_interval_minutes |
FLOAT64 |
例: |
expiration_timestamp |
TIMESTAMP |
例: このプロパティは、expirationTime テーブル リソース プロパティと同等です。 |
friendly_name |
|
例: このプロパティは、friendlyName テーブル リソース プロパティと同等です。 |
description |
|
例: このプロパティは、description テーブル リソース プロパティと同等です。 |
labels |
|
例: このプロパティは、labels テーブル リソース プロパティと同等です。 |
VALUE
を設定すると、オプションが存在する場合は、実体化されたビューのそのオプションの既存の値が置き換えられます。VALUE
を NULL
に設定すると、実体化されたビューのそのオプションの値が消去されます。
例
実体化されたビューでの更新状態の有効化と更新間隔の設定
次の例では、更新を有効にし、更新間隔を 20 分に設定しています。
ALTER MATERIALIZED VIEW mydataset.my_mv
SET OPTIONS (
enable_refresh=true,
refresh_interval_minutes=20
)
DROP TABLE
ステートメント
BigQuery でテーブルを削除するには、DROP TABLE
DDL ステートメントを使用します。
DROP TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name
ここで
IF EXISTS
: 指定されている場合、指定したテーブルが存在しなければクエリは成功します。指定されていない場合、指定したテーブルが存在しなければクエリは失敗します。
project_name
は、削除するテーブルを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、削除するテーブルを含むデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
table_name
: 削除するテーブルの名前です。
例
テーブルの削除
次の例では、mydataset
内の mytable
という名前のテーブルを削除します。
DROP TABLE mydataset.mytable
データセット内にそのテーブル名が存在しない場合は、次のエラーが返されます。
Error: Not found: Table myproject:mydataset.mytable
テーブルが存在する場合にのみテーブルを削除
次の例では、テーブルが存在している場合にのみ mydataset
内の mytable
という名前のテーブルを削除します。テーブル名がデータセットに存在しない場合、エラーは返されず、アクションも実行されません。
DROP TABLE IF EXISTS mydataset.mytable
DROP EXTERNAL TABLE
ステートメント
DROP EXTERNAL TABLE
ステートメントは、外部テーブルを削除します。
DROP EXTERNAL TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name
ここで
project_name
は、テーブルを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。dataset_name
は、テーブルを含むデータセットの名前です。table_name
は、削除するテーブルの名前です。
IF EXISTS
句を使用しない場合、外部テーブルが存在しないと、ステートメントはエラーを返します。IF EXISTS
句を使用して、テーブルが存在しない場合は、エラーは返されず、アクションも実行されません。
table_name
は存在していても、外部テーブルでない場合は、次のエラーが返されます。
Cannot drop table_name which has type TYPE. An
external table was expected.
DROP EXTERNAL
ステートメントは BigQuery から外部テーブル定義のみを削除します。外部に保存されているデータは影響を受けません。
例
次の例では、external_table
という名前の外部テーブルをデータセット mydataset
から削除します。外部テーブルが存在しない場合、エラーを返します。
DROP EXTERNAL TABLE mydataset.external_table
次の例では、external_table
という名前の外部テーブルをデータセット mydataset
から削除します。外部テーブルが存在しない場合、エラーは返されません。
DROP EXTERNAL TABLE IF EXISTS mydataset.external_table
DROP VIEW
ステートメント
BigQuery でビューを削除するには、DROP VIEW
DDL ステートメントを使用します。
DROP VIEW [IF EXISTS] [[project_name.]dataset_name.]view_name
ここで
IF EXISTS
: 指定されている場合、指定したビューが存在しなければクエリは成功します。指定されていない場合、指定したビューが存在しなければクエリは失敗します。
project_name
は、削除するビューを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、削除するビューを含むデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
view_name
は、削除するビューの名前です。
例
ビューの削除
次の例では、mydataset
にある myview
という名前のビューを削除します。
DROP VIEW mydataset.myview
ビュー名がデータセットに存在しない場合は、次のエラーが返されます。
Error: Not found: Table myproject:mydataset.myview
ビューが存在する場合にのみビューを削除
次の例では、ビューが存在している場合にのみ mydataset
にある myview
という名前のビューを削除します。ビュー名がデータセットに存在しない場合、エラーは返されず、アクションも実行されません。
DROP VIEW IF EXISTS mydataset.myview
DROP MATERIALIZED VIEW
ステートメント
BigQuery で実体化されたビューを削除するには、DROP
MATERIALIZED VIEW
DDL ステートメントを使用します。
DROP MATERIALIZED VIEW [IF EXISTS] [[project_name.]dataset_name.]mv_name
ここで
IF EXISTS
: 指定されている場合、指定したビューが存在しなければクエリは成功します。指定されていない場合、指定した実体化されたビューが存在しなければクエリは失敗します。
project_name
は、削除する実体化されたビューを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、削除する実体化されたビューを含むデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
mv_name
は、削除する実体化されたビューの名前です。
例
実体化されたビューの削除
次の例では、mydataset
にある my_mv
という名前の実体化されたビューを削除します。
DROP MATERIALIZED VIEW mydataset.my_mv
実体化されたビュー名がデータセットに存在しない場合は、次のエラーが返されます。
Error: Not found: Table myproject:mydataset.my_mv
別のプロジェクトの実体化されたビューを削除する場合は、`project_id.dataset.materialized_view`
(project_id
に特殊文字が含まれる場合、バッククォートを含む)の形式でプロジェクト、データセット、実体化されたビューを指定する必要があります(例: `myproject.mydataset.my_mv`
)。
実体化されたビューが存在する場合にのみ実体化されたビューを削除
次の例では、実体化されたビューが存在している場合にのみ、mydataset
にある my_mv
という名前の実体化されたビューを削除します。実体化されたビュー名がデータセットに存在しない場合、エラーは返されず、アクションも実行されません。
DROP MATERIALIZED VIEW IF EXISTS mydataset.my_mv
別のプロジェクトの実体化されたビューを削除する場合は、`project_id.dataset.materialized_view`,
(project_id
に特殊文字が含まれる場合、バッククォートを含む)の形式でプロジェクト、データセット、実体化されたビューを指定する必要があります(例: `myproject.mydataset.my_mv`
)。
DROP FUNCTION
ステートメント
DROP FUNCTION [IF EXISTS] [[project_name.]dataset_name.]function_name
ここで
IF EXISTS
: 指定されている場合、指定した関数が存在しなければクエリは成功します。指定されていない場合、指定した関数が存在しなければクエリは失敗します。
project_name
は、削除する関数を含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、削除する関数を含むデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
function_name
は、削除する関数の名前です。
例
次のステートメントの例では、mydataset
データセットに含まれる parseJsonAsStruct
関数を削除します。
DROP FUNCTION mydataset.parseJsonAsStruct;
次のステートメントの例では、プロジェクト other_project
内のデータセット sample_dataset
から関数 parseJsonAsStruct
を削除します。
DROP FUNCTION `other_project`.sample_dataset.parseJsonAsStruct;
DROP PROCEDURE
ステートメント
DROP PROCEDURE [IF EXISTS] [[project_name.]dataset_name.]procedure_name
ここで
IF EXISTS
: 指定されている場合、指定したプロシージャが存在しなければクエリは成功します。指定されていない場合、指定したプロシージャが存在しなければクエリは失敗します。
project_name
は、削除するプロシージャを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。プロジェクト名にコロンなどの特殊文字が含まれている場合は、バッククォート `
で囲む必要があります(例: `google.com:my_project`
)。
dataset_name
は、削除するプロシージャを含むデータセットの名前です。デフォルトでは、リクエスト内の defaultDataset
の名前が設定されます。
procedure_name
は、削除するプロシージャの名前です。
例
次の例のステートメントは、mydataset
データセットに含まれるプロシージャ myprocedure
を削除します。
DROP PROCEDURE mydataset.myProcedure;
次のステートメントの例では、プロジェクト other_project
内のデータセット sample_dataset
からプロシージャ myProcedure
を削除します。
DROP PROCEDURE `other-project`.sample_dataset.myprocedure;