BigQuery のテーブルデータを Google Cloud Storage にファイルエクスポートしてみた

BigQuery のテーブルデータを Google Cloud Storage にファイルエクスポートしてみた

#Google BigQuery
#Google Cloud (GCP)
みかみ

みかみ

facebook logohatena logotwitter logo
Clock Icon2020.08.17

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

やりたいこと

  • BigQuery のテーブルデータを GCS にファイル出力したい
  • BigQuery データを出力する時に選択可能なファイル形式などのオプションが知りたい
  • BigQuery の対象テーブルからの SELECT 結果をファイル出力するにはどうすればいいか知りたい
  • BigQuery テーブルデータをソートしてファイル出力するにはどうすればいいか知りたい

前提

Python クライアントライブラリおよび gcloud コマンド実行環境は準備済みです。

動作確認時には CLOUD SHELL を使用しました。

テーブルデータを GCS にエクスポート

BigQuery に以下の 7 レコードが格納された dogs テーブルがあります。

以下の Python コードを実行して、dogs テーブルのデータを GCS にエクスポートしてみます。

from google.cloud import bigquery

client = bigquery.Client()

bucket_name = "test-mikami"
project = "cm-da-mikami-yuki-258308"
dataset_id = "dataset_1"
table_id = "dogs"

destination_uri = "gs://{}/export_{}.csv".format(bucket_name, table_id)
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_id)

extract_job = client.extract_table(
    table_ref,
    destination_uri,
)
extract_job.result()

print(
    "Exported {}:{}.{} to {}".format(project, dataset_id, table_id, destination_uri)
)

gcp_da_user@cloudshell:~/test_export (cm-da-mikami-yuki-258308)$ python3 export.py
Exported cm-da-mikami-yuki-258308:dataset_1.dogs to gs://test-mikami/export_dogs.csv

GCS バケットを確認すると、期待通りファイルが出力されています。

出力ファイルの中身を確認してみます。

id,name
2,シベリアンハスキー
3,秋田犬
1,シェパード
4,ラブラドールレトリバー
7,コーギー
5,ボルゾイ
6,柴犬

テーブルデータが CSV ファイルとして GCS にエクスポートされたことが確認できました。

同じ処理を、bq extract コマンドでコマンドラインから実行することもできます。

gcp_da_user@cloudshell:~/test_export (cm-da-mikami-yuki-258308)$ bq extract \
> cm-da-mikami-yuki-258308:dataset_1.dogs \
> gs://test-mikami/export_dogs_bq.csv
Waiting on bqjob_r46bd1c39f9c599f1_00000173faba90ae_1 ... (0s) Current status: DONE

Python クライアントライブラリで実行した場合同様、テーブルデータが CSV ファイルにエクスポートできました。

エクスポートのオプション

テーブルデータを GCS にエクスポートする場合、以下のオプションが指定できます。

  • compression：圧縮形式。以下のいずれかを指定可能。デフォルトは NONE
    • DEFLATE（AVRO の場合のみ指定可能）
    • GZIP
    • NONE
    • SNAPPY（AVRO の場合のみ指定可能）
  • destination_format：出力ファイルフォーマット。以下のいずれかを指定可能。デフォルトは CSV
    • AVRO
    • CSV
    • NEWLINE_DELIMITED_JSON
  • field_delimiter：CSV ファイルの区切り文字。文字列で指定。デフォルトは ,
  • labels：データエクスポートジョブに付与するラベル
  • print_header：出力ファイルにヘッダを付与するかどうか。デフォルトは True
  • use_avro_logical_types：AVRO データ型に変換するかどうか

先ほどと同じテーブルデータを、AVRO の Deflate 圧縮ファイルでエクスポートしてみます。

from google.cloud import bigquery

client = bigquery.Client()

bucket_name = "test-mikami"
project = "cm-da-mikami-yuki-258308"
dataset_id = "dataset_1"
table_id = "dogs"

#destination_uri = "gs://{}/export_{}.csv".format(bucket_name, table_id)
destination_uri = "gs://{}/export_{}_deflate.avro".format(bucket_name, table_id)
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_id)

job_config = bigquery.ExtractJobConfig(
    compression='DEFLATE',
    destination_format='AVRO'
)
extract_job = client.extract_table(
    table_ref,
    destination_uri,
    job_config=job_config
)
extract_job.result()

print(
    "Exported {}:{}.{} to {}".format(project, dataset_id, table_id, destination_uri)
)

gcp_da_user@cloudshell:~/test_export (cm-da-mikami-yuki-258308)$ python3 export_avro.py
Exported cm-da-mikami-yuki-258308:dataset_1.dogs to gs://test-mikami/export_dogs_deflate.avro

直接開くことができない AVRO ファイルなので、以下の Python コードで出力フィルの中身を確認してみます。

from avro import datafile
from avro import io

with datafile.DataFileReader(open("export_dogs_deflate.avro", "rb"), io.DatumReader()) as reader:
    for resp in reader:
        print(resp)

gcp_da_user@cloudshell:~/test_export (cm-da-mikami-yuki-258308)$ gsutil cp gs://test-mikami/export_dogs_deflate.avro ./
Copying gs://test-mikami/export_dogs_deflate.avro...
/ [1 files][  315.0 B/  315.0 B]
Operation completed over 1 objects/315.0 B.
gcp_da_user@cloudshell:~/test_export (cm-da-mikami-yuki-258308)$ python3 read_avro.py
{'id': 2, 'name': 'シベリアンハスキー'}
{'id': 3, 'name': '秋田犬'}
{'id': 1, 'name': 'シェパード'}
{'id': 4, 'name': 'ラブラドールレトリバー'}
{'id': 5, 'name': 'ボルゾイ'}
{'id': 6, 'name': '柴犬'}
{'id': 7, 'name': 'コーギー'}

オプション指定の通り、テーブルデータが AVRO ファイルで出力されていることが確認できました。

bq extract コマンドでも同様のオプションを指定することができます。

テーブルのネストデータをファイルにエクスポート

BigQuery では、テーブルにネストデータを格納することが可能です。

ではネストデータをファイル出力するとどうなるのか確認してみます。

ネスト構造の JSON データをロードした、以下の sample_load_json テーブルデータを CSV ファイルで出力してみます。

from google.cloud import bigquery

client = bigquery.Client()

bucket_name = "test-mikami"
project = "cm-da-mikami-yuki-258308"
dataset_id = "dataset_1"
table_id = "sample_load_json"

destination_uri = "gs://{}/export_{}.csv".format(bucket_name, table_id)
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_id)

extract_job = client.extract_table(
    table_ref,
    destination_uri,
)
extract_job.result()

print(
    "Exported {}:{}.{} to {}".format(project, dataset_id, table_id, destination_uri)
)

gcp_da_user@cloudshell:~/test_export (cm-da-mikami-yuki-258308)$ python3 export_nested.py
Traceback (most recent call last):
  File "export_nested.py", line 18, in <module>
    extract_job.result()
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 812, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Operation cannot be performed on a nested schema. Field: friends

cannot be performed on a nested schema. とのことで、ネスト構造のテーブルデータは CSV 出力できないようです。。

では、出力ファイルフォーマットを JSONL に変更して、再度実行してみます。

（省略）
#destination_uri = "gs://{}/export_{}.csv".format(bucket_name, table_id)
destination_uri = "gs://{}/export_{}.jsonl".format(bucket_name, table_id)
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_id)

job_config = bigquery.ExtractJobConfig(
    destination_format='NEWLINE_DELIMITED_JSON',
)
extract_job = client.extract_table(
    table_ref,
    destination_uri,
    job_config=job_config
)
extract_job.result()
（省略）

gcp_da_user@cloudshell:~/test_export (cm-da-mikami-yuki-258308)$ python3 export_nested.py
Exported cm-da-mikami-yuki-258308:dataset_1.sample_load_json to gs://test-mikami/export_sample_load_json.jsonl

今度は無事テーブルデータをエクスポートできたようです。

出力ファイルの中身を確認してみます。

{"favoriteFruit":"strawberry","friends":[{"name":"Vaughn Franks","id":"0"},{"name":"Misty Lawrence","id":"1"},{"name":"Ayala Solis","id":"2"}],"latitude":-9.94468,"registered":"2016-06-29 11:36:01 UTC","address":"994 Saratoga Avenue, Evergreen, Alaska, 2454","longitude":26.303266,"phone":"+1 (832) 564-3999","greeting":"Hello, Emilia Burch! You have 9 unread messages.","email":"emiliaburch@halap.com","tags":["aliqua","ut","ipsum","dolore","elit","id","id"],"company":"HALAP","name":"Emilia Burch","eyeColor":"brown","gender":"female","age":"25","picture":"http://placehold.it/32x32","balance":2255.86,"isActive":false,"guid":"69d23d6d-b337-4935-a212-bea42087acc0","about":"Qui ad fugiat commodo in adipisicing nulla in quis ullamco. Elit minim cillum ex ex. Ut occaecat consectetur voluptate magna duis sit veniam consequat occaecat et consequat aliquip. Lorem et consectetur ea voluptate in duis esse ipsum pariatur quis veniam. Ut eu quis laborum ullamco. Ullamco ea mollit reprehenderit quis ea velit nulla sunt ea.\r\n","index":"3","_id":"5e9e78daf9617236cb600016"}
{"favoriteFruit":"strawberry","friends":[{"name":"Lynn Simon","id":"0"},{"name":"Cummings Charles","id":"1"},{"name":"Karen Cherry","id":"2"}],"latitude":35.3641,"registered":"2014-08-27 18:53:16 UTC","address":"571 Withers Street, Siglerville, Pennsylvania, 6878","longitude":-53.866185,"phone":"+1 (870) 460-2091","greeting":"Hello, Tracy Cash! You have 5 unread messages.","email":"tracycash@cytrex.com","tags":["ipsum","laboris","anim","non","minim","tempor","ea"],"company":"CYTREX","name":"Tracy Cash","eyeColor":"green","gender":"female","age":"33","picture":"http://placehold.it/32x32","balance":2567.27,"isActive":false,"guid":"5672d7cb-c597-476b-bbaf-4a6709bf506b","about":"Minim voluptate labore labore qui irure cupidatat officia adipisicing nisi eiusmod. Veniam pariatur dolore esse dolore aliquip ut duis irure exercitation consectetur ad voluptate deserunt. Dolore esse esse sunt qui enim irure nulla ipsum occaecat. Consectetur ipsum mollit aute fugiat excepteur duis pariatur quis nisi aliqua qui veniam cillum consectetur.\r\n","index":"4","_id":"5e9e78da017481351e9c4b9b"}
{"favoriteFruit":"apple","friends":[{"name":"Carolina Fischer","id":"0"},{"name":"Leola Brewer","id":"1"},{"name":"Anita Michael","id":"2"}],"latitude":57.758086,"registered":"2017-08-06 14:21:27 UTC","address":"187 Greenpoint Avenue, Tilden, New Mexico, 6712","longitude":-113.347217,"phone":"+1 (868) 502-2898","greeting":"Hello, Burton French! You have 5 unread messages.","email":"burtonfrench@talae.com","tags":["commodo","ipsum","officia","duis","nulla","mollit","ex"],"company":"TALAE","name":"Burton French","eyeColor":"green","gender":"male","age":"21","picture":"http://placehold.it/32x32","balance":2672.01,"isActive":false,"guid":"641b44d6-4e44-452e-90ed-44d5458ccff0","about":"Enim adipisicing quis mollit eiusmod irure do. Do voluptate adipisicing voluptate velit minim sunt do. Dolor officia ullamco labore qui aliqua culpa ipsum ex consectetur consectetur irure cillum minim.\r\n","index":"6","_id":"5e9e78da72c32bfc6f591542"}
{"favoriteFruit":"apple","friends":[{"name":"Opal Keller","id":"0"},{"name":"Duncan Bradshaw","id":"1"},{"name":"Theresa Mooney","id":"2"}],"latitude":-5.142501,"registered":"2020-02-08 10:46:15 UTC","address":"860 Emerald Street, Silkworth, Michigan, 9197","longitude":-119.229143,"phone":"+1 (842) 457-2021","greeting":"Hello, Judith Dickson! You have 4 unread messages.","email":"judithdickson@zytrex.com","tags":["proident","Lorem","est","officia","non","ad","id"],"company":"ZYTREX","name":"Judith Dickson","eyeColor":"green","gender":"female","age":"27","picture":"http://placehold.it/32x32","balance":3845.13,"isActive":true,"guid":"936bae0e-97d4-4bcc-b5f1-00c22f79817d","about":"Ex quis fugiat fugiat velit ut incididunt officia veniam cupidatat duis laborum cupidatat. Exercitation magna eiusmod adipisicing sunt esse incididunt. Adipisicing laborum aliqua qui enim ut Lorem sint pariatur cupidatat. Aliquip minim incididunt cupidatat elit. Incididunt occaecat exercitation mollit aliqua quis. Velit sunt ut cupidatat mollit cupidatat eiusmod excepteur laboris.\r\n","index":"0","_id":"5e9e78da2a1ce10414295d99"}
{"favoriteFruit":"strawberry","friends":[{"name":"Polly Phelps","id":"0"},{"name":"Tamra Carey","id":"1"},{"name":"Mercado Barron","id":"2"}],"latitude":-67.067065,"registered":"2016-07-18 18:47:59 UTC","address":"689 Cadman Plaza, Derwood, California, 4868","longitude":-79.439951,"phone":"+1 (882) 501-2437","greeting":"Hello, Charity Mills! You have 9 unread messages.","email":"charitymills@isologia.com","tags":["enim","ullamco","aute","adipisicing","irure","nostrud","ea"],"company":"ISOLOGIA","name":"Charity Mills","eyeColor":"green","gender":"female","age":"34","picture":"http://placehold.it/32x32","balance":2541.93,"isActive":true,"guid":"34adf2b7-fd94-4413-96eb-656c0966251a","about":"Incididunt eu esse ad sit laborum qui sint esse exercitation id qui. Consequat aliquip adipisicing non culpa. Enim et elit minim aliqua nostrud deserunt sit aliqua.\r\n","index":"1","_id":"5e9e78da668e337086eaff73"}
{"favoriteFruit":"apple","friends":[{"name":"Kim Nelson","id":"0"},{"name":"Booth Mueller","id":"1"},{"name":"Brewer Hooper","id":"2"}],"latitude":-64.729569,"registered":"2018-04-27 21:25:15 UTC","address":"363 Taaffe Place, Defiance, Illinois, 191","longitude":91.426433,"phone":"+1 (800) 508-2319","greeting":"Hello, Jannie Morgan! You have 6 unread messages.","email":"janniemorgan@slumberia.com","tags":["proident","dolor","quis","deserunt","cillum","adipisicing","sint"],"company":"SLUMBERIA","name":"Jannie Morgan","eyeColor":"brown","gender":"female","age":"35","picture":"http://placehold.it/32x32","balance":3830.75,"isActive":true,"guid":"04debd69-618d-45c8-b38d-60660f8a086c","about":"Duis velit exercitation irure dolor consequat reprehenderit adipisicing id. Irure do culpa incididunt Lorem in irure irure dolore cupidatat. Proident ipsum ex qui laborum ut. Aute reprehenderit pariatur minim laboris. Est aliquip laboris labore aliquip dolore nostrud qui amet non magna voluptate ad. Lorem ullamco quis sunt mollit. Occaecat duis elit esse sit elit ipsum veniam pariatur esse anim eiusmod aliqua.\r\n","index":"2","_id":"5e9e78da6d422ce2fd29895c"}
{"favoriteFruit":"banana","friends":[{"name":"Karina Davenport","id":"0"},{"name":"Meyers Chaney","id":"1"},{"name":"Tyson Mack","id":"2"}],"latitude":-13.703048,"registered":"2017-05-29 18:28:57 UTC","address":"573 Louise Terrace, Crenshaw, Puerto Rico, 7823","longitude":-62.551788,"phone":"+1 (883) 403-3327","greeting":"Hello, Shana Marshall! You have 6 unread messages.","email":"shanamarshall@velity.com","tags":["aute","esse","nostrud","eu","labore","ullamco","aliqua"],"company":"VELITY","name":"Shana Marshall","eyeColor":"blue","gender":"female","age":"28","picture":"http://placehold.it/32x32","balance":1351.45,"isActive":true,"guid":"9b209aee-5f84-46e5-b351-3d28ff7a5406","about":"Nulla minim duis velit aute mollit do aliquip id duis. Ullamco quis minim do elit voluptate ipsum et. Cupidatat non eu pariatur enim ea mollit esse cillum. Irure exercitation nulla irure magna enim nisi excepteur aute culpa. Culpa consequat magna voluptate ut enim aute incididunt elit.\r\n","index":"5","_id":"5e9e78da567659cf2f112f14"}

出力データが見にくいので、1 行目のオブジェクトのみ、インデントを入れて整形してみました。

{
    "favoriteFruit": "strawberry",
    "friends": [
        {
            "name": "Vaughn Franks",
            "id": "0"
        },
        {
            "name": "Misty Lawrence",
            "id": "1"
        },
        {
            "name": "Ayala Solis",
            "id": "2"
        }
    ],
    "latitude": -9.94468,
    "registered": "2016-06-29 11:36:01 UTC",
    "address": "994 Saratoga Avenue, Evergreen, Alaska, 2454",
    "longitude": 26.303266,
    "phone": "+1 (832) 564-3999",
    "greeting": "Hello, Emilia Burch! You have 9 unread messages.",
    "email": "emiliaburch@halap.com",
    "tags": [
        "aliqua",
        "ut",
        "ipsum",
        "dolore",
        "elit",
        "id",
        "id"
    ],
    "company": "HALAP",
    "name": "Emilia Burch",
    "eyeColor": "brown",
    "gender": "female",
    "age": "25",
    "picture": "http://placehold.it/32x32",
    "balance": 2255.86,
    "isActive": false,
    "guid": "69d23d6d-b337-4935-a212-bea42087acc0",
    "about": "Qui ad fugiat commodo in adipisicing nulla in quis ullamco. Elit minim cillum ex ex. Ut occaecat consectetur voluptate magna duis sit veniam consequat occaecat et consequat aliquip. Lorem et consectetur ea voluptate in duis esse ipsum pariatur quis veniam. Ut eu quis laborum ullamco. Ullamco ea mollit reprehenderit quis ea velit nulla sunt ea.\r\n",
    "index": "3",
    "_id": "5e9e78daf9617236cb600016"
}

出力ファイルフォーマットが JSONL であれば、ネストデータもファイル出力できることが確認できました。

SELECT 結果をファイルにエクスポート

Python クライアントライブラリの extract_table や gcloud コマンドの bq extract では、指定したテーブルの全レコードがファイルにエクスポートされます。

テーブルデータから特定のレコードのみ抽出してファイル出力したり、データをソートしてエクスポートする場合は、出力データを一度別テーブルに格納したり、BigQuery 管理コンソールから GUI 操作で実行する必要があります。

以下の Python コードで、出力対象データを一度別テーブルに格納した後、別テーブルデータを CSVファイルとして GCS にエクスポートしてみます。

from google.cloud import bigquery

client = bigquery.Client()

bucket_name = "test-mikami"
project = "cm-da-mikami-yuki-258308"
dataset_id = "dataset_1"
table_src = "dogs"
table_dst = "{}_temp".format(table_src)
destination_uri = "gs://{}/export_{}.csv".format(bucket_name, table_dst)

# create export target table
sql = """
    SELECT id, name
    FROM {}.{}
    WHERE id IN (1, 2, 3)
    ORDER BY id;
""".format(dataset_id, table_src)
job_config = bigquery.QueryJobConfig(destination="{}.{}.{}".format(project, dataset_id, table_dst))
query_job = client.query(sql, job_config=job_config)
query_job.result()
print("Query results loaded to the table {}".format(table_dst))

# export target data
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_dst)
extract_job = client.extract_table(
    table_ref,
    destination_uri,
)
extract_job.result()
print("Exported {}:{}.{} to {}".format(project, dataset_id, table_dst, destination_uri))

# drop export target table
table = client.get_table("{}.{}".format(dataset_id, table_dst))
client.delete_table(table)
print("Deleted table {}.{}.{}".format(project, dataset_id, table_dst))

gcp_da_user@cloudshell:~/test_export (cm-da-mikami-yuki-258308)$ python3 export_selected_data.py
Query results loaded to the table dogs_temp
Exported cm-da-mikami-yuki-258308:dataset_1.dogs_temp to gs://test-mikami/export_dogs_temp.csv
Deleted table cm-da-mikami-yuki-258308.dataset_1.dogs_temp

出力ファイルを確認してみます。

id,name
1,シェパード
2,シベリアンハスキー
3,秋田犬

出力対象データを別テーブルに格納する際に SELECT 結果をソートしたところ、ファイル出力結果もソート順で出力されましたが、extract_table や bq extract コマンドでは、出力データのソート順は保証されません。

特定の条件でソートした結果をファイル出力する必要がある場合には、BigQuery 管理コンソールの GUI を利用する必要があります。

クエリエディタに SELECT 文を入力して「実行」します。

正常に実行が完了すると、「結果の保存」リンクが表示されるのでクリック。

プルダウンからファイルフォーマット等を選択して「保存」します。

出力ファイルを確認してみます。

id,name
1,シェパード
2,シベリアンハスキー
3,秋田犬
4,ラブラドールレトリバー
5,ボルゾイ
6,柴犬
7,コーギー

出力データの最大レコード数に制限があるので大量データを出力する場合は注意が必要です。 また、ファイルを GCS に保存する場合は別途ローカルから GCS にアップロードする必要がありますが、期待通りソート済みのデータが CSV ファイルに出力できました。

また、GUI 操作では、「結果の保存先」に Google スプレッドシートを選択することも可能です。

結果を共有したい場合などに便利ですね。

まとめ（所感）

BigQuery でも、Python クライアントライブラリや gcloud コマンドでテーブルデータを簡単に GCS にエクスポートできることが確認できました。

現時点ではプログラムやコマンド経由で SELECT 結果を直接ファイルエクスポートすることはできませんが、管理コンソールの GUI を使えば出力対象のデータだけを抽出したり、抽出結果をソートして直接ファイル出力することもできます。

GUI 操作でエクスポートする場合には Google スプレッドシートへのデータ出力も可能なので、特に業務で利用する場合には便利ではないかと思いました。

参考

Share this article

facebook logohatena logotwitter logo

関連記事

BigQuery で Cloud Storage データの自動検出を試してみた（Preview） #cm_google_cloud_adcal_2024

BigQuery で Cloud Storage データの自動検出を試してみた（Preview） #cm_google_cloud_adcal_2024

User avatar
エノカワ
2024.12.15
BigQuery で JSON Lines ではないカンマ区切りの JSON 形式のファイルを読み込む裏技 #cm_google_cloud_adcal_2024

BigQuery で JSON Lines ではないカンマ区切りの JSON 形式のファイルを読み込む裏技 #cm_google_cloud_adcal_2024

User avatar
hanzawa.yuya
2024.12.13
BigQuery のリソースへのアクセスを IAM Conditions とタグで制御する方法をまとめてみた #cm_google_cloud_adcal_2024

BigQuery のリソースへのアクセスを IAM Conditions とタグで制御する方法をまとめてみた #cm_google_cloud_adcal_2024

User avatar
hanzawa.yuya
2024.12.11
[Google Cloud] あの機能/サービスは今！？ #cm_google_cloud_adcal_2024

[Google Cloud] あの機能/サービスは今！？ #cm_google_cloud_adcal_2024

User avatar
みかみ
2024.12.07
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.