BigqueryテーブルをSQLでもっと自由自在に！

概要

BigqueryではGUIでテーブル作成スケジューリングさせてテーブル作成を実行することができて非常に便利です。

その他、jsonファイルのテーブル作成活用やcloud shellでの作成方法など様々な手法がございますがデータベース言語でもっと自由自在にテーブルを操りたい！と考えた方に向けて本記事を投稿します。

作成/追加/変更などなど

今回はSQLでテーブルに変化を加えるクエリをまとめてみました。

テーブルを作成 or 変化を加えるＳＱＬ

テーブル作成

引用：【BigQuery】CREATE文を使えるの知ってた？SQLでテーブル作成

CREATE TABLE {[project_id].}[dataset_name].[table_name]
-- CREATE TABLE IF NOT EXISTS は同名のテーブルが不存在なら作成
-- CREATE OR REPLACE TABLE 同名のテーブルが存在した場合、テーブルが置換
-- テーブルを別種類のパーティショニングで置き換え不可
(
  [column_name a] [data_type] {NOT NULL} {OPTIONS(description="[description_text]")}
  , [column_name b] ...
-- 各新規作成するカラムの型情報などを設定
)
{ PARTITION BY [partition_expression] }
-- パーティション設定可能/castして変換処理が必要な場合がある
-- パーティションとは後述
{ CLUSTER BY [column_name　a] }
-- クラスター化させたいカラムを設定/複数[カラム名,]で複数設定可能
{ OPTIONS (
    [option_name1] = [value]
    , [option_name2] = ...
    , ...
--　テーブル情報設定したい場合/以下オプション一覧
-- expiration_timestamp = TIMESTAMP("2018-12-24 01:00:00 UTC")
-- ,friendly_name = "My cat diary"
-- ,description = "The record of encountered cats"
-- ,labels = [("user", "tomono.t"), ("importance", "high"),("confidentiality","private")]
)}
{
  AS SELECT ...FROM...
-- 別テーブルの結果をもとにテーブルを作成したい場合AS~としてSELECT文を記載
}

パーティションとは？

パーティション分割テーブルはパーティションと呼ばれるセグメントに分割されるため、データの管理や照会が簡単になります。大きなテーブルを小さなパーティションに分割することで、クエリのパフォーマンスを高めることや、クエリによって読み取られるバイト数を減らしてコストを抑えることができます。
引用：分割テーブルの概要

クラスター化とは？

カラム名に基づいて並び替えられます。
パーティション分割と併用すると、各パーティションの中でさらにカラム名に基づいて並び替えられます。
引用：【BigQuery初心者の日記 DAY3】クラスタ化テーブル①ークラスタ化テーブルの作成ー

テーブルに変更を加える

◇INSERT
テーブルにデータを追加
テストケース作成で使用することが多いです。

-- テーブル名とカラム(customer_id, name, location, ts)準備
INSERT `project.dataset.table_name` (customer_id, name, location, ts)
-- データの中身を準備- 
VALUES(65401, 'John Doe', 'Faraway', TIMESTAMP("2017-01-01")),
      (74682, 'Jane Michaels', 'Nearland', TIMESTAMP("2017-02-01"));

◇DELETE
削除

DELETE FROM `project.dataset.table` WHERE true;

-- サブクエリを使用して削除する行を指定方法
DELETE `project.dataset.table` t
WHERE t.id NOT IN (SELECT id from `project.dataset.unprocessed`)

◇MERGE

MERGE
  `blog.target` AS T
USING
  `blog.source` AS S
ON
  T.user_id = S.user_id
WHEN MATCHED  THEN
  UPDATE SET T.comment = S.comment
WHEN NOT MATCHED THEN
  INSERT (user_id, comment)
  VALUES (user_id, comment)

MERGEは大変便利なのですが、BigQueryにデータを追加する基本手段のloadが無料であるのに対して、MERGEはSELECT同様に課金が発生します。
上記料金の兼ね合いで使用しないほうが賢明です。