1. sonots

    No comment

    sonots
Changes in body
Source | HTML | Preview

embulk-output-bigquery の Partitioned Table 対応で調べてたので、その時に調べたものを雑にまとめておく。

References

TL; DR

  • 基本的に tableId に partition decorator ($YYYYMMDD) を指定して操作する
  • DAYパーティションしか(今のところ)切れない。
  • 特定パーティションのデータを置き換えたい場合は、パーティションを指定して、writeDisposition: 'WRITE_TRUNCATE'として load (または copy)ジョブを発行する
  • Drop Partition の操作が今の所ないので、消したい場合は load job API で空データをロードする?
  • スキーマ変更はカラム追加、既存カラムをNULLABLEにすることによる擬似的な削除、ぐらいしかできない

Partitioned Table の作り方

See https://cloud.google.com/bigquery/docs/creating-partitioned-tables

Tables: insert API を使って、テーブルを作成する時に

{
  "tableReference": {
    "projectId": "myProject",
    "tableId": "table2",
    "datasetId": "mydataset"
  },
  "timePartitioning": {
    "type": "DAY",
    "expirationMs": 259200000
  }
}

のように timePartitioning パラメータを設定すれば良い。

特記事項: 現在、typeDAY しか選択できない。HOUR や MONTH や時間以外のパーティションを切ることはできない。

スキーマ指定

パーティションテーブルを作る時に一緒に指定するか、最初のデータ投入時に指定する。

一度決めると、パーティションのデータを置き換えてもテーブルのスキーマは置き換えられず、新しいデータのスキーマがテーブルのスキーマに適合していなければエラーとなる。

Partitioned Table (から|へ)の copy

Jobs: insert APIを使って、copy する際に table にパーティションデコレータ($YYYYMMDD)を指定すればよい。

{
  "configuration": {
    "copy": {
      "sourceTable": {
        "tableId": "from_table$20160929"
      },
      "destinationTable": {
        "tableId": "to_table$20160929"
      },
      "writeDisposition": "WRITE_TRUNCATE"
    }
  }
}

試した所、以下のいずれのパターンも動作した。

  • table から partition
  • partition から table
  • partition から partition

特記事項: table へのコピーの場合は table が自動で作られるが、partition へのコピーの場合は自動では作られないので、あらかじめ create table しておかないといけない。embulk-output-bigquery では auto_create_tableオプションが有効な場合は、自動で作るようにしておいた。

Partitioned Table への load

こちらも Jobs: insert APIを使って、load する際に table にパーティションデコレータ($YYYYMMDD)を指定すればよい。

{
  "configuration": {
    "load": {
      "destinationTable": {
        "tableId": "table$20160929"
      },
      "writeDisposition": "WRITE_TRUNCATE"
    }
  }
}

write_disposition: 'WRITE_TRUNCATE' とすれば、指定パーティションを atomic に置き換えできる。

Drop Partition

ない。

load job で空データを write_disposition: 'WRITE_TRUNCATE' として取り込めば、指定パーティションのデータは消せそう。

Partitioned Table への変換

bq コマンドでできる。See https://cloud.google.com/bigquery/docs/creating-partitioned-tables

bq partition mydataset.sharded_ mydataset.partitioned

内部的には table 一覧を取って、copy job で日付 suffix と対応する日付 partition にデータをコピーしている。

スキーマ変更

NULLABLEなカラム追加と、既存カラムのNULLABLEへの変更のみサポートされている。

Tables: patch もしくは Tables: update を利用する.

または load および copy query job に schemaUpdateOptions というパラメータが増えており、job of の副作用として、カラムの追加(ALLOW_FIELD_ADDITION)、もしくは既存カラムをNULLABLEにすることによる擬似的なカラムの削除(ALLOW_FIELD_RELAXATION)を行うことができる。

{
  "configuration": {
    "load": {
      "destinationTable": {
        "tableId": "table$20160929"
      },
      "writeDisposition": "WRITE_TRUNCATE",
      "schemaUpdateOptions": ["ALLOW_FIELD_ADDITION", "ALLOW_FIELD_RELAXATION"]
    }
  }
}
{
  "configuration": {
    "load": {
      "destinationTable": {
        "tableId": "table$20160929"
      },
      "writeDisposition": "WRITE_TRUNCATE",
      "schemaUpdateOptions": ["ALLOW_FIELD_ADDITION", "ALLOW_FIELD_RELAXATION"]
    }
  }
}

追記: 残念なことに copy job には schemaUpdateOptions オプションがなかった…!

パーティション一覧

SELECT partition_id from [mydataset.table1$__PARTITIONS_SUMMARY__];

クエリ対象のパーティションを絞る

See https://cloud.google.com/bigquery/docs/querying-partitioned-tables

_PARTITIONTIME疑似カラムに対してWHERE条件を書く

SELECT
  field1
FROM
  table2
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
  AND TIMESTAMP('2016-01-21');

追記: TABLE_DATE_RANGEによるテーブル検索の時間がかからなくなるため(けっこう遅い)、結果が返ってくるのが速くなるようだ。