chisataki’s blog

リコリス・リコイルじゃありません

BigQuery SQLコマンド集-2023年度版

GCP DB

データセットの管理

データセットを作成する
CREATE SCHEMA `PROJECT_ID.DATASET_ID`
  OPTIONS (
    description = 'データセット説明',
    location = 'US'
)

location例：
US: マルチリージョン
asia-northeast1: 東京

BigQuery のロケーション  |  Google Cloud


プロジェクトの全データセット名をリスト表示
SELECT
  schema_name
FROM
  `PROJECT_ID`.INFORMATION_SCHEMA.SCHEMATA

INFORMATION_SCHEMA.SCHEMATA ビューから全データセット名、作成日時、更新日時等が取得できる


データセットの削除
DROP SCHEMA IF EXISTS `PROJECT_ID.DATASET_ID`

削除前にデータセット内のテーブルとビューを削除すること


テーブルの管理

テーブルの作成
CREATE TABLE `PROJECT_ID.DATASET_ID.TABLE_ID` (
  x INT64 OPTIONS (description = '列説明'),
  y STRING 
) OPTIONS (
    description = 'テーブル説明'
   )


テーブルのメタデータの表示
SELECT
  *
FROM
    `PROJECT_ID.DATASET_ID.INFORMATION_SCHEMA.TABLES`;

テーブル名、テーブルタイプ、作成日時、作成したコマンド(DDL)などが表示される


テーブルの削除
DROP TABLE `PROJECT_ID.DATASET_ID.TABLE_ID`


テーブルの外部エクスポート
EXPORT DATA
  OPTIONS (
    # 出力先URI
    uri = 'gs://bucket/folder/*.csv',
    format = 'CSV',
    overwrite = true,
    header = true,
    field_delimiter = ';')
AS (
  SELECT *
  FROM `PROJECT_ID.DATASET_ID.TABLE_ID`
);


ネストされた列と繰り返し列を含むテーブルを作成する
CREATE TABLE `PROJECT_ID.DATASET_ID.TABLE_ID` (
  id INT64,
  addresses ARRAY <
    STRUCT <
      address STRING,
      status STRING
    >
  >
)
  • 補足: 各IDごとに複数住所(address)とステータス(status)を格納できるテーブルを定義。
    ネストデータ：STRUCT
    繰り返しデータ：ARRAY
  • BigQueryでは多くの場合、ネストされた繰り返しフィールドを使用して単一のテーブルにクエリを実行すると、複数のテーブルを結合する場合よりも効率的に処理されます。


ネストされたレコードの挿入
INSERT INTO  
  `PROJECT_ID.DATASET_ID.TABLE_ID` 
  (id, addresses) 
VALUES 
  ("1",[("past","Tokyo"),("now","Kanagawa")]),
  ("2",[("past","Chiba"),("now","Saitama")])


ネストされた繰り返し列(addresses)に対して、特定の位置にある ARRAYの値を取得する
SELECT
  # 1番目の値
  addresses[offset(0)].address
FROM
  `PROJECT_ID.DATASET_ID.TABLE_ID` 
ORDER BY id
  • offset(index): インデックスは 0 から始まります。範囲外の場合はエラーが発生します。
  • safe_offset(index): インデックスは 0 から始まります。範囲外の場合にNULLを返します。
  • ordinal(index): インデックスは 1 から始まります。範囲外の場合はエラーが発生します。
  • safe_ordinal(index): インデックスは 1 から始まります。範囲外の場合にNULLを返します。


テーブルに空の列を追加する
ALTER TABLE `PROJECT_ID.DATASET_ID.TABLE_ID` 
ADD COLUMN new_column STRING;


テーブルの列の名前を変更する
ALTER TABLE `PROJECT_ID.DATASET_ID.TABLE_ID` 
RENAME COLUMN old_column TO renamed_column;


テーブルの列のデフォルト値を変更する
ALTER TABLE `PROJECT_ID.DATASET_ID.TABLE_ID` 
ALTER COLUMN x SET DEFAULT 0;


列のデフォルト値を削除する
ALTER TABLE `PROJECT_ID.DATASET_ID.TABLE_ID` 
ALTER COLUMN x DROP DEFAULT;


パーティーション分割

(時間単位列)パーティション分割テーブルを作成する
CREATE TABLE
  `PROJECT_ID.DATASET_ID.TABLE_ID` (transaction_id INT64, transaction_date DATE)
PARTITION BY
  # transaction_date列で分割
  transaction_date
  OPTIONS (
    require_partition_filter = TRUE
  )
  • パーティション：特定の範囲でテーブルを分割する。パーティション作成するとテーブルの一部のみスキャン→コスト削減、パフォーマンス向上
    参考：パーティション列からレコードを絞り込むことをプルーニングと呼ぶ。条件式が定数式であること等、プルーニングが動作するためにはいくつか条件あり。詳細
  • 整数範囲、時間単位列、取り込み時間パーティショニングの３種類
  • オプションでrequire_partition_filter = TRUE にすると、SELECT時のフィルター指定(where句)を強制できる→where句を指定しないとエラー


(取り込み時間)パーティション分割テーブルを作成する
CREATE TABLE
  `PROJECT_ID.DATASET_ID.TABLE_ID` (transaction_id INT64)
PARTITION BY
  _PARTITIONDATE

PARTITIONTIME (あるいはPARTITIONDATE) 列は、レコードの取り込み時間/日付がレコード挿入時に自動的に格納される。


パーティション フィルタ要件を更新する
ALTER TABLE `PROJECT_ID.DATASET_ID.TABLE_ID`
SET OPTIONS (
  require_partition_filter = FALSE);


クラスタリング

クラスタ化テーブルを作成する
CREATE TABLE `PROJECT_ID.DATASET_ID.TABLE_ID`
(
  customer_id STRING,
  product_id STRING,
  transaction_amount NUMERIC
)
CLUSTER BY
  # クラスタリング列
  customer_id,product_id;
  • 最大 4 つのクラスタリング列を指定可能。
  • 指定した列の順序によって、データの並べ替え順序が決まる。最も頻繁にフィルタリングまたは集計される列を最初に置く


クラスタリング列をWHERE句でフィルタリングする
SELECT
  SUM(x)
FROM
  `PROJECT_ID.DATASET_ID.TABLE_ID`
WHERE
  customer_id = "1" AND
  product_id = "002"
  • フィルタリング順序はクラスタリング指定列の順序になっている必要あり(例：上記customer_idとproduct_idの順序を逆にするとクエリが最適化されない)
  • パーティション同様、複雑な(動的に値が決まる)フィルタ式ではクラスタ化列を使用しない


テーブルクローンを作成する
CREATE TABLE
`PROJECT_ID.DATASET_ID.CRONED_TABLE_ID`
CLONE `PROJECT_ID.DATASET_ID.BASE_TABLE_ID`;
  • テーブルクローン：ベーステーブルの軽量で書き込み可能なコピー
  • 標準テーブル同様にクエリ、パーティション等可能
  • ビューや外部テーブルのクローンは不可(コピーと同じ)
  • ベーステーブルorクローンのデータを変更しても互いに反映されない(独立したテーブル)
  • コピーとの違いはストレージ料金。クローンは新たに変更・追加されたデータ分のみ課金される
    テーブルクローン概要


テーブルスナップショット

24 時間後に期限切れになるテーブルのスナップショットを作成
CREATE SNAPSHOT TABLE `PROJECT_ID.DATASET_ID.SNAPSHOT_TABLE_ID`
CLONE `PROJECT_ID.DATASET_ID.BASE_TABLE_ID`
OPTIONS (
  expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
);
  • テーブルスナップショット：別のテーブル（ベーステーブル）の軽量で読み取り専用のコピー
  • ベーステーブルと異なるデータセット内に作成することで、ベーステーブルのデータセットが誤って削除された場合でも、テーブル スナップショットからベーステーブルを復元できます
  • 過去 7 日間の任意の時点におけるテーブルのスナップショットを作成することが可能


テーブル スナップショットを新しいテーブルに復元
CREATE TABLE `PROJECT_ID.DATASET_ID.TABLE_ID`
CLONE `PROJECT_ID.DATASET_ID.SNAPSHOT_TABLE_ID`


テーブル スナップショットのメタデータを取得する
select *
from `PROJECT_ID.DATASET_ID.INFORMATION_SCHEMA.TABLE_SNAPSHOTS`
where table_name = 'some_table_snapshot';


テーブル スナップショットをコピーする
CREATE SNAPSHOT TABLE `PROJECT_ID.DATASET_ID.COPIED_SNAPSHOT_TABLE_ID`
CLONE `PROJECT_ID.DATASET_ID.SNAPSHOT_TABLE_ID`;

テーブル スナップショットを作成するプロセスと似ています。相違点は、ソーステーブルとしてコピー元のテーブル スナップショットを指定することです


テーブル スナップショットを削除する
DROP SNAPSHOT TABLE `PROJECT_ID.DATASET_ID.SNAPSHOT_TABLE_ID`;


ビュー, マテリアライズド ビュー

ビューを作成する
CREATE VIEW `PROJECT_ID.DATASET_ID.TABLE_VIEW_ID` (
  customer_id,
  transaction_amount_sum) AS (
  SELECT
    customer_id,
    SUM(transaction_amount)
  FROM
    `PROJECT_ID.DATASET_ID.TABLE_ID`
  GROUP BY
    customer_id
)

ビューを照会するたびに、ビューを定義しているクエリが実行されます。大規模なビューや計算負荷の高いビューを頻繁にクエリする場合は、マテリアライズド ビューの作成を検討


ビューを実行する
SELECT * FROM `PROJECT_ID.DATASET_ID.TABLE_VIEW_ID`

テーブルと同じ方法でビューにクエリを実行します。


マテリアライズド ビューを作成する
CREATE MATERIALIZED VIEW `PROJECT_ID.DATASET_ID.TABLE_MV_ID`
 AS (
  SELECT
    customer_id as id,
    SUM(transaction_amount) as amount_sum
  FROM
    `PROJECT_ID.DATASET_ID.TABLE_ID`
  GROUP BY
    customer_id
)

・マテリアライズド ビューはクエリの結果を定期的にキャッシュに保存するため、同じデータをベーステーブルから取得するクエリよりも高速で、消費するリソースも少なくて済みます 詳細


マテリアライズド ビューを変更する(例：自動更新を60分ごとにする)
ALTER MATERIALIZED VIEW `PROJECT_ID.DATASET_ID.TABLE_MV_ID`
SET OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)

  • デフォルトではマテリアライズド ビューは以下２つのタイミングで自動更新される
    1) 30 分ごとに更新
    2) ベーステーブルの変更から 5 分以内に自動的に更新。ただし前の更新から 30 分以内に更新されることはない。
    変更の例としては、行の挿入や行の削除があります。

  • 自動更新をオフにするには、enable_refresh を false に設定します


マテリアライズド ビューを削除する
DROP MATERIALIZED VIEW `PROJECT_ID.DATASET_ID.TABLE_MV_ID`


ルーティン

ルーティンを作成する (プロシージャ例：変数idを設定し、INSERT を実行して、テキスト文字列として結果を表示する)
CREATE OR REPLACE PROCEDURE `PROJECT_ID.DATASET_ID`.create_customer()
BEGIN
  DECLARE id STRING;
  # 36文字のランダム英数字を作成
  SET id = GENERATE_UUID();
  INSERT INTO `PROJECT_ID.DATASET_ID.TABLE_ID` (customer_id)
    VALUES (id);
  SELECT FORMAT("Created customer %s", id);
END

ルーティン：ストアド プロシージャ、ユーザー定義関数（UDF）、テーブル関数のいずれか


作成したルーティン(プロシージャ)を呼び出す
CALL `PROJECT_ID.DATASET_ID`.create_customer();


データセット内のルーティンを一覧表示する
SELECT
  routine_name, routine_type
FROM
   `PROJECT_ID.DATASET_ID.INFORMATION_SCHEMA.ROUTINES`


ルーティンの本文を表示する
SELECT
  routine_definition
FROM
  `PROJECT_ID.DATASET_ID.INFORMATION_SCHEMA.ROUTINES`
WHERE
  routine_name = 'create_customer';


ルーティンを削除する
DROP PROCEDURE IF EXISTS `PROJECT_ID.DATASET_ID`.create_customer
  • ストアド プロシージャの削除: DROP PROCEDURE
  • ユーザー定義関数: DROP FUNCTION
  • テーブル関数: DROP TABLE FUNCTION


ユーザー定義関数(UDF)

SQLでUDFを作成（例：第1引数に4を足して第2引数で割った値をFLOAT型で返却する)
CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

-- UDF関数の呼び出し
SELECT
  val, AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8]) AS val;
  • SQL式または JavaScriptコードを使用して関数を作成できます。
  • 永続的または一時的のいずれかとして定義できます。永続的な関数の場合ROUTINEとして作成可能。
  • UNNEST()：配列を受け取り各要素を一行ずつ取り出す


JavascriptでUDFを作成する
CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  m = x*y;
  return m;
""";

SELECT multiplyInputs(2,3);

raw文字列(r""" ... """)でjavascriptを記載する


テーブル関数

テーブル関数の作成 (例: customer_idが一致するレコードを抽出するテーブル関数 table_function)
CREATE OR REPLACE TABLE FUNCTION `PROJECT_ID.DATASET_ID`.table_function (
  id STRING
)
AS (
  SELECT * FROM `PROJECT_ID.DATASET_ID_TABLE_ID`
  WHERE customer_id = id
)

テーブル関数：パラメータ付きビューのこと


テーブル関数の使用
SELECT * FROM `PROJECT_ID.DATASET_ID`.table_function("1")


リモート関数

リモート関数を作成する
# 作成例：Cloud Functionsにデプロイしたリモート関数の引数の合計を返す e.g. calls = [[1,2,3], [2,3,4]] →[[6], [9]]
# Cloud Function(ver2)で以下関数をデプロイ
import functions_framework

@functions_framework.http
def batch_add(request):
  try:
    return_value = []
    request_json = request.get_json()
    print('request_json', request_json)
    calls = request_json['calls']
    for call in calls:
      return_value.append(sum([int(x) for x in call if x is not None]))
    # callsの要素数と同じreturn_valueリストを返却
    response = { "replies":  return_value } 
    return response

  except Exception as e:
    return { "errorMessage": str(e) }

--- リモート関数の作成（事前にCloud Functionsと同じリージョンの接続を作成する必要あり)

CREATE FUNCTION `PROJECT_ID.DATASET_ID`.remote_add(x INT64, y INT64) RETURNS INT64
REMOTE WITH CONNECTION `PROJECT_ID.LOCATION.CONNECTION_NAME`
OPTIONS (
  endpoint = 'ENDPOINT_URL'
)
  • リモート関数：Cloud Functions, Cloud Runにデプロイされた関数
  • Cloud FunctionsはHTTPトリガーで作成
  • 接続はcloud Functionsと同じリージョンで作成し、IAMから"Cloud Run Invoker"の権限を与える(ver2の場合)


検索インデックスの管理

検索インデックスを作成 (例：Logsテーブルを作成し、全列にLOG_ANALYZERの検索インデックスを作成する)
-- テストテーブルの作成
CREATE TABLE `PROJECT_ID.DATASET_ID`.Logs (Level STRING, Source STRING, Message STRING)
AS (
  SELECT 'INFO' as Level, '65.177.8.234' as Source, 'Entry Foo-Bar created' as Message
  UNION ALL
  SELECT 'WARNING', '132.249.240.10', 'Entry Foo-Bar already exists, created by 65.177.8.234'
  UNION ALL
  SELECT 'INFO', '94.60.64.181', 'Entry Foo-Bar deleted'
  UNION ALL
  SELECT 'SEVERE', '4.113.82.10', 'Entry Foo-Bar does not exist, deleted by 94.60.64.181'
  UNION ALL
  SELECT 'INFO', '181.94.60.64', 'Entry Foo-Baz created'
);

-- テーブル全列に検索インデックスを作成
CREATE SEARCH INDEX my_index ON `PROJECT_ID.DATASET_ID`.Logs(ALL COLUMNS);
  • 検索インデックスはSEARCH()関数とともに使用することで、テキストの検索が大幅に高速化されます。
  • 検索インデックスは、大きなテーブルを想定して設計されています。10 GB 未満のテーブルに検索インデックスを作成した場合、インデックスは入力されません。
  • SEARCH(text,query,analyzer)関数のデフォルトのテキスト分析ツールのタイプはLOG_ANALYZER（厳密な一致を求める場合はNO_OP_ANALYZERを指定).


検索インデックスを使用してテキストを検索する
-- テーブル全体から文字列'baz'を含むレコードを抽出
SELECT * FROM `PROJECT_ID.DATASET_ID`.Logs WHERE SEARCH(Logs, 'baz');

search関数では、トークンの区切りに日本語の句読点は含まれないので、日本語テキストの検索は不向き 参考


検索インデックスに関する情報を取得
SELECT table_name, index_name, ddl, index_status
FROM `PROJECT_ID.DATASET_ID`.INFORMATION_SCHEMA.SEARCH_INDEXES

テーブルサイズが10G未満の場合、statusはdisabled


検索インデックスを削除する
DROP SEARCH INDEX my_index ON  `PROJECT_ID.DATASET_ID`.Logs;


Cloud Storageにクエリを行う

BigLake テーブルを作成する
CREATE EXTERNAL TABLE `PROJECT_ID.DATASET_ID.BIGLAKE_TABLE_ID`
  WITH CONNECTION `PROJECT_ID.DATASET_ID.CONNECTION_ID`
  OPTIONS (
    format ="CSV",
    # GCSバケットパス
    uris = ['BUCKET_PATH'[,...]],
    );
  • 事前に接続の作成必要(bigquery connection APIの認証も必要)
  • テーブルをクリックするとコンソール上部にBigLakeマークが表示される
  • BigLakeテーブルは行アクセスポリシー編集可能


外部テーブルを作成する
CREATE EXTERNAL TABLE `PROJECT_ID.DATASET_ID.EXTERNAL_TABLE_ID`
  OPTIONS (
    format ="CSV",
    uris = ['BUCKET_PATH'[,...]]
    );