BigQueryでのデータ操作でパーティションを利用することは非常に重要となります。
BigQueryの料金で大きいところを締めるのがクエリの利用です。
クエリの料金としては操作した回数ではなく、走査されたデータ量で料金が変わってきます。
これは10件の検索結果でも、1,000件の検索結果でも扱う元のデータ数が一緒であれば金額は変わりません。
検索する対象を絞ることで料金（と検索速度）を大きく節約できますが、その際に「パーティショニング」は非常に有効な手段になります。
具体的には日付でテーブルを分割してデータは分割されたデータごとに保存されます。
BigQueryのテーブルの作成時に「取り込み時間位より分割」を選択すれば日毎のパーティショニングが有効になります。
パーティションを利用したデータの挿入と検索
以下のプログラムでデータの挿入と検索を行います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
from google.cloud import bigquery import os credentials_json = './credentials.json' os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = credentials_json ## Set Project ID project = "project_name" ## Set Dataset dataset = "dataset" ## Set Table table = "table" bigquery_client = bigquery.Client() ## 2019/03/01のパーティションにデータを追加 query = "INSERT INTO `{0}.{1}.{2}` (_PARTITIONTIME, name, id) SELECT TIMESTAMP('2019-03-01'), 'hanako', 10".format(project, dataset, table) bigquery_client.query(query).result() ## 2019/03/01のパーティションにデータを追加 query = "INSERT INTO `{0}.{1}.{2}` (_PARTITIONTIME, name, id) SELECT TIMESTAMP('2019-03-01'), 'taro', 20".format(project, dataset, table) bigquery_client.query(query).result() ## 2019/03/15のパーティションにデータを追加 query = "INSERT INTO `{0}.{1}.{2}` (_PARTITIONTIME, name, id) SELECT TIMESTAMP('2019-03-15'), 'jiro', 30".format(project, dataset, table) bigquery_client.query(query).result() ## 2019/03/01のパーティションからデータを取得 query = "SELECT name,id FROM `{0}.{1}.{2}` where _PARTITIONTIME = TIMESTAMP('2019-03-01')".format(project, dataset, table) rows = bigquery_client.query(query).result() for row in rows: print(row[0] + ":" + str(row[1])) |
こちらをプロンプトで確認してみます。
|
1 2 3 4 5 |
$ python3.6 sample.py taro:20 hanako:10 |
結果として、パーティションに指定したものだけを読み込むことができました。
時刻など少し複雑なフォーマットでの挿入
時刻など少し複雑なデータを扱った場合のDMLを記載します。
フォーマットは以下とします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[ { "name": "name", "type": "string" }, { "name": "id", "type": "integer" }, { "name": "my_day", "type": "date" }, { "name": "my_datetime", "type": "datetime" } ] |
queryなどは以下のように書けます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
query = "INSERT INTO `{0}.{1}.{2}` \ (_PARTITIONTIME, \ name, \ id, \ my_day, \ my_datetime) \ SELECT TIMESTAMP('{3}'), \ '{4}', \ {5} \ DATE('{6}') \ DATETIME(TIMESTAMP('{7}'))".format(project, \ dataset, \ table, \ my_day.date(), \ my_name, \ my_id, \ my_day.date(), \ "{0:%Y-%m-%d %H:%M:%S}".format(my_datetime) |
こちらでパーティションを利用したDMLの操作など実施できました。
★関連記事
・Pythonでのgoogle-cloud-bigqueryライブラリを利用したBigQueryの操作を標準SQLとレガシーSQLで実施（データの取得）
・Pythonでのgoogle-cloud-bigqueryライブラリを利用したBigQueryのシンプルなデータ挿入とデータ更新
・Pythonでのgoogle-cloud-bigqueryライブラリを利用したBigQueryへのストリーミング挿入
このブログは株式会社CoLabMixによる技術ブログです。
GCP、AWSなどでのインフラ構築・運用や、クローリング・分析・検索などを主体とした開発を行なっています。
Ruby on RailsやDjango、Pythonなどの開発依頼などお気軽にお声がけください。
開発パートナーを増やしたいという企業と積極的に繋がっていきたいです。
