Cloud Storage からの JSON データの読み込み
Cloud Storage から新しいテーブルまたはパーティションに改行区切りの JSON データを読み込めます。また、既存のテーブルまたはパーティションに追加することも、既存のテーブルまたはパーティションを上書きすることもできます。BigQuery に読み込まれたデータは Capacitor の列型(BigQuery のストレージ形式)に変換されます。
Cloud Storage から BigQuery テーブルにデータを読み込むとき、テーブルを含むデータセットが Cloud Storage バケットと同じリージョンまたはマルチリージョンのロケーションに存在している必要があります。
改行で区切られた JSON 形式は、JSON Lines と同じ形式になります。
制限事項
Cloud Storage バケットから BigQuery にデータを読み込む際には、次の制限があります。
- データセットのロケーションが
US
マルチリージョン以外の値に設定されている場合は、Cloud Storage バケットがデータセットと同じリージョンに存在するか、データセットと同じマルチリージョンに含まれている必要があります。 - BigQuery では外部データソースに対して整合性が保証されません。クエリの実行中に基になるデータを変更すると、予期しない動作が発生する可能性があります。
- BigQuery では、Cloud Storage オブジェクトのバージョニングはサポートされていません。Cloud Storage URI に世代番号を含めると、読み込みジョブは失敗します。
JSON ファイルを BigQuery に読み込む場合は、次の点に注意してください。
- JSON データは改行区切りである必要があります。各 JSON オブジェクトはファイル内でそれぞれ別の行に配置されている必要があります。
- gzip 圧縮を使用した場合、BigQuery はデータを並列で読み取ることができません。圧縮された JSON データを BigQuery に読み込む場合は、圧縮されていないデータの読み込みよりも時間がかかります。
- 同じ読み込みジョブに圧縮ファイルと非圧縮ファイルの両方を含めることはできません。
- gzip ファイルの最大サイズは 4 GB です。
BigQuery は、取り込み時にスキーマ情報が不明な場合でも、
JSON
型をサポートします。JSON
型として宣言されているフィールドは、未加工の JSON 値で読み込まれます。BigQuery API を使用して [-253+1, 253-1] の範囲外の整数(通常は 9,007,199,254,740,991 より大きい値)を読み込んで整数(INT64)の列に格納する場合は、データの破損を避けるため、文字列として渡します。この問題は、JSON/ECMAScript の整数のサイズ制限が原因で発生します。詳細については、RFC 7159 の Numbers のセクションをご覧ください。
- CSV データまたは JSON データを読み込む場合、
DATE
列の値に区切りとしてダッシュ(-
)を使用し、YYYY-MM-DD
(年-月-日)の形式にする必要があります。 - JSON または CSV データを読み込む場合、
TIMESTAMP
列のタイムスタンプ値の日付部分の区切りにはダッシュ(-
)を使用し、日付はYYYY-MM-DD
(年-月-日)の形式にする必要があります。タイムスタンプの時間部分hh:mm:ss
(時-分-秒)には、区切りとしてコロン(:
)を使用します。
準備
このドキュメントの各タスクを行うのに必要な権限をユーザーに与える Identity and Access Management(IAM)ロールを付与し、データを保存するためのデータセットを作成します。
必要な権限
BigQuery にデータを読み込むには、読み込みジョブを実行してデータを BigQuery のテーブルとパーティションに読み込む IAM 権限が必要です。Cloud Storage からデータを読み込む場合は、データを含むバケットに対する IAM アクセス権限も必要です。
BigQuery にデータを読み込む権限
新しい BigQuery テーブルやパーティションにデータを読み込む場合、または既存のテーブルやパーティションにデータの追加や上書きを行う場合は、次の IAM 権限が必要です。
bigquery.tables.create
bigquery.tables.updateData
bigquery.tables.update
bigquery.jobs.create
以下の各事前定義 IAM ロールには、BigQuery テーブルやパーティションにデータを読み込むために必要な権限が含まれています。
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
(bigquery.jobs.create
権限を含む)bigquery.user
(bigquery.jobs.create
権限を含む)bigquery.jobUser
(bigquery.jobs.create
権限を含む)
また、bigquery.datasets.create
権限がある場合は、作成するデータセットで読み込みジョブを使用してテーブルの作成と更新を行えます。
BigQuery での IAM のロールと権限については、事前定義ロールと権限をご覧ください。
Cloud Storage からデータを読み込む権限
Cloud Storage バケットからデータを読み込むには、次の IAM 権限が必要です。
storage.buckets.get
storage.objects.get
storage.objects.list
(URI ワイルドカードを使用する場合に必要)
データセットを作成する
データを保存する BigQuery データセットを作成します。
JSON データを新しいテーブルに読み込む
Cloud Storage から新しい BigQuery テーブルに JSON データを読み込むには:
コンソール
Google Cloud コンソールで [BigQuery] ページに移動します。
- [エクスプローラ] ペインでプロジェクトを展開し、データセットを選択します。
- [データセット情報] セクションで、 [テーブルを作成] をクリックします。
- [テーブルの作成] パネルで、次の詳細を指定します。
- [ソース] セクションの [テーブルの作成元] リストで [Google Cloud Storage] を選択します。次に、以下の操作を行います。
- Cloud Storage バケットからファイルを選択するか、Cloud Storage URI を入力します。Google Cloud Console で複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成、追加、または上書きするテーブルを含むデータセットと同じロケーションに存在している必要があります。
- [ファイル形式] で [JSONL(改行区切り JSON)] を選択します。
- Cloud Storage バケットからファイルを選択するか、Cloud Storage URI を入力します。Google Cloud Console で複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成、追加、または上書きするテーブルを含むデータセットと同じロケーションに存在している必要があります。
- [送信先] セクションで、次の詳細を指定します。
- [データセット] で、テーブルを作成するデータセットを選択します。
- [テーブル] フィールドに、作成するテーブルの名前を入力します。
- [テーブルタイプ] フィールドが [ネイティブ テーブル] に設定されていることを確認します。
- [スキーマ] セクションでスキーマ定義を入力します。スキーマの自動検出を有効にするには、[自動検出] を選択します。スキーマ情報は、次のいずれかの方法で手動で入力できます。
- オプション 1: [テキストとして編集] をクリックし、スキーマを JSON 配列の形式で貼り付けます。JSON 配列を使用する場合は、JSON スキーマ ファイルの作成と同じプロセスを使用してスキーマを生成します。既存のテーブルのスキーマを JSON 形式で表示するには、次のコマンドを入力します。
bq show --format=prettyjson dataset.table
- オプション 2: 型]、[モード] を指定します。 [フィールドを追加] をクリックして、テーブル スキーマを入力します。各フィールドの [名前]、[
- オプション 1: [テキストとして編集] をクリックし、スキーマを JSON 配列の形式で貼り付けます。JSON 配列を使用する場合は、JSON スキーマ ファイルの作成と同じプロセスを使用してスキーマを生成します。既存のテーブルのスキーマを JSON 形式で表示するには、次のコマンドを入力します。
- 省略可: [パーティションとクラスタの設定] を指定します。詳細については、パーティション分割テーブルの作成とクラスタ化テーブルの作成と使用をご覧ください。
- [詳細オプション] をクリックして、次の操作を行います。
- [書き込み設定] で、[空の場合に書き込む] を選択したままにします。これにより、新しいテーブルが作成され、データが読み込まれます。
- [許可されているエラー数] で、デフォルト値の
0
を使用するか、無視できる最大行数を入力します。エラーを含む行数がこの値を超えると、ジョブはinvalid
メッセージとなり、失敗します。このオプションは、CSV ファイルと JSON ファイルにのみ適用されます。 - テーブルのスキーマに存在しない行の値を無視する場合は、[不明な値] を選択します。
- Cloud Key Management Service 鍵を使用するには、[暗号化] で [顧客管理の暗号鍵] をクリックします。[Google が管理する暗号鍵] の設定をそのままにすると、BigQuery は保存されているデータを暗号化します。
- [テーブルを作成] をクリックします。
SQL
LOAD DATA
DDL ステートメントを使用します。次の例では、JSON ファイルを新しいテーブル mytable
に読み込みます。
Google Cloud コンソールで [BigQuery] ページに移動します。
クエリエディタで次のステートメントを入力します。
LOAD DATA OVERWRITE mydataset.mytable FROM FILES ( format = 'JSON', uris = ['gs://bucket/path/file.json']);
[
実行] をクリックします。
クエリの実行方法については、インタラクティブ クエリの実行をご覧ください。
bq
bq load
コマンドを使用します。--source_format
フラグを使用して NEWLINE_DELIMITED_JSON
を指定し、Cloud Storage URI を設定します。単一の URI、URI のカンマ区切りのリスト、ワイルドカードを含む URI を指定できます。スキーマをインラインまたはスキーマ定義ファイルで指定するか、スキーマ自動検出を使用します。
(省略可)--location
フラグを指定して、その値をロケーションに設定します。
次のフラグを使用することもできます。
--max_bad_records
: ジョブ全体が失敗する前に許容される不良レコードの最大数を指定する整数。デフォルト値は0
です。--max_bad_records
の値にかかわらず、最大で 5 つの任意のタイプのエラーが返されます。--ignore_unknown_values
: 指定すると、CSV または JSON データで認識されない余分な値が許可され、無視されます。--autodetect
: 指定すると、CSV および JSON データのスキーマ自動検出が有効になります。--time_partitioning_type
: テーブルでの時間ベースのパーティショニングを有効にし、パーティション タイプを設定します。有効な値はHOUR
、DAY
、MONTH
、YEAR
です。DATE
、DATETIME
、TIMESTAMP
列でパーティション分割されたテーブルを作成する場合、このフラグは省略可能です。時間ベースのパーティショニングのデフォルト パーティション タイプはDAY
です。既存のテーブルのパーティショニング仕様を変更することはできません。--time_partitioning_expiration
: 時間ベースのパーティションを削除する必要があるタイミングを指定する整数(秒単位)。パーティションの日付(UTC)に、この整数値を足した値が有効期限になります。--time_partitioning_field
: パーティション分割テーブルの作成に使用されるDATE
またはTIMESTAMP
の列。この値を指定せずに時間ベースのパーティショニングを有効にすると、取り込み時間パーティション分割テーブルが作成されます。--require_partition_filter
: 有効にすると、クエリの実行時にWHERE
句でパーティションを指定するようユーザーに求めます。パーティション フィルタを必須にすると、コストが削減され、パフォーマンスが向上する場合があります。詳細については、パーティション分割テーブルのクエリをご覧ください。--clustering_fields
: クラスタ化テーブルの作成に使用する列名のカンマ区切りのリスト。最大 4 個の列名を指定できます。--destination_kms_key
: テーブルデータの暗号化に使用される Cloud KMS 鍵。パーティション分割テーブルの詳細については、以下をご覧ください。
クラスタ化テーブルの詳細については、以下をご覧ください。
テーブルの暗号化の詳細については、以下をご覧ください。
JSON データを BigQuery に読み込むには、次のコマンドを入力します。
bq --location=LOCATION load \ --source_format=FORMAT \ DATASET.TABLE \ PATH_TO_SOURCE \ SCHEMA
次のように置き換えます。
LOCATION
: ロケーション。--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。FORMAT
:NEWLINE_DELIMITED_JSON
DATASET
: 既存のデータセット。TABLE
: データの読み込み先のテーブル名。PATH_TO_SOURCE
: 完全修飾の Cloud Storage URI または URI のカンマ区切りのリスト。ワイルドカードも使用できます。SCHEMA
: 有効なスキーマ。スキーマはローカルの JSON ファイルにすることも、コマンドの一部としてインラインで入力することもできます。スキーマ ファイルを使用する場合、拡張子は指定しないでください。また、スキーマ定義を指定する代わりに、--autodetect
フラグを使用することもできます。
例:
次のコマンドは、gs://mybucket/mydata.json
から mydataset
内の mytable
というテーブルにデータを読み込みます。スキーマは、myschema
という名前のローカル スキーマ ファイルで定義されています。
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
./myschema
次のコマンドは、gs://mybucket/mydata.json
からデータを読み込んで mydataset
内の mytable
という新しい取り込み時間パーティション分割テーブルに追加します。スキーマは、myschema
という名前のローカル スキーマ ファイルで定義されています。
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
--time_partitioning_type=DAY \
mydataset.mytable \
gs://mybucket/mydata.json \
./myschema
次のコマンドは、gs://mybucket/mydata.json
からデータを読み込んで mydataset
内の mytable
というパーティション分割テーブルに追加します。テーブルは mytimestamp
列で分割されます。スキーマは、myschema
という名前のローカル スキーマ ファイルで定義されています。
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
--time_partitioning_field mytimestamp \
mydataset.mytable \
gs://mybucket/mydata.json \
./myschema
次のコマンドは、gs://mybucket/mydata.json
から mydataset
内の mytable
というテーブルにデータを読み込みます。スキーマは自動検出されます。
bq load \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json
次のコマンドは、gs://mybucket/mydata.json
から mydataset
内の mytable
というテーブルにデータを読み込みます。スキーマは、FIELD:DATA_TYPE, FIELD:DATA_TYPE
の形式でインラインで定義されます。
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
qtr:STRING,sales:FLOAT,year:STRING
次のコマンドは、gs://mybucket/
の複数のファイルから mydataset
内の mytable
という名前のテーブルにデータを読み込みます。Cloud Storage の URI ではワイルドカードを使用しています。スキーマは自動検出されます。
bq load \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata*.json
次のコマンドは、gs://mybucket/
の複数のファイルから mydataset
内の mytable
という名前のテーブルにデータを読み込みます。このコマンドでは、Cloud Storage の URI のカンマ区切りのリストをワイルドカード付きで使用しています。スキーマは、myschema
という名前のローカル スキーマ ファイルで定義されています。
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
"gs://mybucket/00/*.json","gs://mybucket/01/*.json" \
./myschema
API
Cloud Storage のソースデータを参照する
load
ジョブを作成します。(省略可)ジョブリソースの
jobReference
セクションにあるlocation
プロパティでロケーションを指定します。source URIs
プロパティは、完全修飾のgs://BUCKET/OBJECT
の形式にする必要があります。各 URI にワイルドカード文字(*)を 1 つ含めることができます。JSON
データ形式は、sourceFormat
プロパティをNEWLINE_DELIMITED_JSON
に設定することで指定します。ジョブのステータスを確認するには、
jobs.get(JOB_ID*)
を呼び出します。JOB_ID
は、最初のリクエストで返されるジョブの ID で置き換えます。status.state = DONE
である場合、ジョブは正常に完了しています。status.errorResult
プロパティが存在する場合は、リクエストが失敗したことを意味し、該当するオブジェクトにエラーを説明する情報が格納されます。リクエストが失敗した場合、テーブルは作成されず、データは読み込まれません。status.errorResult
が存在しない場合、ジョブは正常に完了していますが、一部の行のインポートで問題があったなど、致命的でないエラーが発生した可能性があります。致命的でないエラーは、返されたジョブ オブジェクトのstatus.errors
プロパティに格納されています。
API に関する注:
読み込みジョブはアトミックで整合性があります。読み込みジョブが失敗した場合、データは一切利用できず、読み込みジョブが成功した場合はすべてのデータが利用可能になります。
おすすめの方法として、
jobs.insert
を呼び出して読み込みジョブを作成する際に、一意の ID を生成して、その ID をjobReference.jobId
として渡すようにします。この手法を使用すると、ネットワーク障害時にクライアントは既知のジョブ ID を使ってポーリングまたは再試行できるので、頑健性が向上します。同じジョブ ID に対して
jobs.insert
を呼び出しても結果は同じになります。同じジョブ ID で何回でも再試行できますが、成功するオペレーションはそのうちの 1 回だけです。
C#
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの C# の手順に沿って設定を行ってください。詳細については、BigQuery C# API のリファレンス ドキュメントをご覧ください。
BigQueryClient.CreateLoadJob()
メソッドを使用して、Cloud Storage からの読み込みジョブを開始します。改行区切りの JSON を使用するには、CreateLoadJobOptions
オブジェクトを作成し、その SourceFormat
プロパティを FileFormat.NewlineDelimitedJson
に設定します。
Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Go の手順に沿って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
LoadJobConfiguration.builder(tableId, sourceUri) メソッドを使用して、Cloud Storage からの読み込みジョブを開始します。改行区切りの JSON を使用するには、LoadJobConfiguration.setFormatOptions(FormatOptions.json()) を使用します。
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
PHP
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの PHP の手順に沿って設定を行ってください。詳細については、BigQuery PHP API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Python の手順に沿って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
Client.load_table_from_uri() メソッドを使用して、Cloud Storage から読み込みジョブを開始します。改行区切りの JSON を使用するには、LoadJobConfig.source_format プロパティを文字列NEWLINE_DELIMITED_JSON
に設定し、ジョブ構成を load_table_from_uri()
メソッドの job_config
引数として渡します。
Ruby
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Ruby の手順に沿って設定を行ってください。詳細については、BigQuery Ruby API のリファレンス ドキュメントをご覧ください。
Dataset.load_job() メソッドを使用して、Cloud Storage からの読み込みジョブを開始します。改行区切りの JSON を使用するには、format
パラメータを "json"
に設定します。
ネストされた JSON データと繰り返し JSON データの読み込み
BigQuery は、JSON、Avro、ORC、Parquet、Firestore、Datastore など、オブジェクト ベースのスキーマをサポートするソース形式からネストされたデータや繰り返しデータを読み込むことができます。
各行に、ネストされたフィールド / 繰り返しフィールドを含む 1 つの JSON オブジェクトが必要です。
次の例は、ネストされたデータ / 繰り返しデータの例を示します。このテーブルには人に関する情報が含まれています。このテーブルは、次のフィールドで構成されています。
id
first_name
last_name
dob
(生年月日)addresses
(ネストと繰り返しのあるフィールド)addresses.status
(現在または以前)addresses.address
addresses.city
addresses.state
addresses.zip
addresses.numberOfYears
(居住年数)
JSON データファイルは次のようになります。address フィールドには([ ]
によって示される)値の配列が含まれています。
{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}
このテーブルのスキーマは次のようになります。
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "addresses", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "status", "type": "STRING", "mode": "NULLABLE" }, { "name": "address", "type": "STRING", "mode": "NULLABLE" }, { "name": "city", "type": "STRING", "mode": "NULLABLE" }, { "name": "state", "type": "STRING", "mode": "NULLABLE" }, { "name": "zip", "type": "STRING", "mode": "NULLABLE" }, { "name": "numberOfYears", "type": "STRING", "mode": "NULLABLE" } ] } ]
ネストされたスキーマと繰り返しスキーマを指定する方法については、ネストされたフィールドと繰り返しフィールドの指定をご覧ください。
半構造化 JSON データの読み込み
BigQuery では、フィールドが異なる型の値を取ることができる半構造化データの読み込みがサポートされています。以下の例は、上記のネストされた JSON データと繰り返し JSON データ例と似ています。ただし、address
フィールドは、STRING
、STRUCT
、または ARRAY
になり得ます。
{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","address":"123 First Avenue, Seattle WA 11111"} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","address":{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"}} {"id":"3","first_name":"Bob","last_name":"Doe","dob":"1982-01-10","address":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"}, "321 Main Street Hoboken NJ 44444"]}
このデータは、次のスキーマを使用することで BigQuery に読み込めます。
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "address", "type": "JSON", "mode": "NULLABLE" } ]
address
フィールドは JSON
型の列に読み込まれ、この例では混合型を保持できます。データに混合型が含まれているかどうかにかかわらず、JSON
としてデータを取り込むことができます。たとえば、first_name
フィールドの型として STRING
ではなく JSON
を指定できます。詳細については、GoogleSQL での JSON データの操作をご覧ください。
JSON データをテーブルに追加または上書きする
テーブルに追加のデータを読み込むには、ソースファイルを使用するか、クエリ結果を追加します。
Google Cloud コンソールでは、[書き込み設定] オプションを使用して、ソースファイルやクエリ結果からデータを読み込むときに行う操作を指定します。
追加のデータをテーブルに読み込む場合、以下のオプションがあります。
Console のオプション | bq ツールのフラグ |
BigQuery API のプロパティ | 説明 |
---|---|---|---|
空の場合に書き込む | 非対応 | WRITE_EMPTY |
テーブルが空の場合にのみデータを書き込みます。 |
テーブルに追加する | --noreplace または --replace=false (--[no]replace を指定しない場合、デフォルトは追加) |
WRITE_APPEND |
(デフォルト)テーブルの末尾にデータを追加します。 |
テーブルを上書きする | --replace または --replace=true |
WRITE_TRUNCATE |
新しいデータを書き込む前に、テーブル内の既存のデータをすべて消去します。この操作を行うと、テーブル スキーマと Cloud KMS 鍵も削除されます。 |
既存のテーブルにデータを読み込む場合、読み込みジョブでデータの追加やテーブルの上書きを行うことができます。
次のいずれかの方法で、テーブルを追加または上書きできます。
- Google Cloud コンソール
bq
コマンドライン ツールのbq load
コマンドjobs.insert
API メソッドとload
ジョブの構成- クライアント ライブラリ
コンソール
Google Cloud コンソールで [BigQuery] ページに移動します。
- [エクスプローラ] ペインでプロジェクトを展開し、データセットを選択します。
- [データセット情報] セクションで、 [テーブルを作成] をクリックします。
- [テーブルの作成] パネルで、次の詳細を指定します。
- [ソース] セクションの [テーブルの作成元] リストで [Google Cloud Storage] を選択します。次に、以下の操作を行います。
- Cloud Storage バケットからファイルを選択するか、Cloud Storage URI を入力します。Google Cloud Console で複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成、追加、または上書きするテーブルを含むデータセットと同じロケーションに存在している必要があります。
- [ファイル形式] で [JSONL(改行区切り JSON)] を選択します。
- Cloud Storage バケットからファイルを選択するか、Cloud Storage URI を入力します。Google Cloud Console で複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成、追加、または上書きするテーブルを含むデータセットと同じロケーションに存在している必要があります。
- [送信先] セクションで、次の詳細を指定します。
- [データセット] で、テーブルを作成するデータセットを選択します。
- [テーブル] フィールドに、作成するテーブルの名前を入力します。
- [テーブルタイプ] フィールドが [ネイティブ テーブル] に設定されていることを確認します。
- [スキーマ] セクションでスキーマ定義を入力します。スキーマの自動検出を有効にするには、[自動検出] を選択します。スキーマ情報は、次のいずれかの方法で手動で入力できます。
- オプション 1: [テキストとして編集] をクリックし、スキーマを JSON 配列の形式で貼り付けます。JSON 配列を使用する場合は、JSON スキーマ ファイルの作成と同じプロセスを使用してスキーマを生成します。既存のテーブルのスキーマを JSON 形式で表示するには、次のコマンドを入力します。
bq show --format=prettyjson dataset.table
- オプション 2: 型]、[モード] を指定します。 [フィールドを追加] をクリックして、テーブル スキーマを入力します。各フィールドの [名前]、[
- オプション 1: [テキストとして編集] をクリックし、スキーマを JSON 配列の形式で貼り付けます。JSON 配列を使用する場合は、JSON スキーマ ファイルの作成と同じプロセスを使用してスキーマを生成します。既存のテーブルのスキーマを JSON 形式で表示するには、次のコマンドを入力します。
- 省略可: [パーティションとクラスタの設定] を指定します。詳細については、パーティション分割テーブルの作成とクラスタ化テーブルの作成と使用をご覧ください。追加や上書きではテーブルをパーティション分割テーブルまたはクラスタ化テーブルに変換できません。Google Cloud Console では、読み込みジョブでパーティション分割テーブルやクラスタ化テーブルの追加または上書きを行うことはできません。
- [詳細オプション] をクリックして、次の操作を行います。
- [書き込み設定] で、[テーブルに追加する] または [テーブルを上書きする] を選択します。
- [許可されているエラー数] で、デフォルト値の
0
を使用するか、無視できる最大行数を入力します。エラーを含む行数がこの値を超えると、ジョブはinvalid
メッセージとなり、失敗します。このオプションは、CSV ファイルと JSON ファイルにのみ適用されます。 - テーブルのスキーマに存在しない行の値を無視する場合は、[不明な値] を選択します。
- Cloud Key Management Service 鍵を使用するには、[暗号化] で [顧客管理の暗号鍵] をクリックします。[Google が管理する暗号鍵] の設定をそのままにすると、BigQuery は保存されているデータを暗号化します。
- [テーブルを作成] をクリックします。
SQL
LOAD DATA
DDL ステートメントを使用します。次の例では、テーブル mytable
に JSON ファイルを追加します。
Google Cloud コンソールで [BigQuery] ページに移動します。
クエリエディタで次のステートメントを入力します。
LOAD DATA INTO mydataset.mytable FROM FILES ( format = 'JSON', uris = ['gs://bucket/path/file.json']);
[
実行] をクリックします。
クエリの実行方法については、インタラクティブ クエリの実行をご覧ください。
bq
bq load
コマンドを使用します。--source_format
フラグを使用して NEWLINE_DELIMITED_JSON
を指定し、Cloud Storage URI を設定します。単一の URI、URI のカンマ区切りのリスト、ワイルドカードを含む URI を指定できます。
スキーマをインラインまたはスキーマ定義ファイルで指定するか、スキーマ自動検出を使用します。
テーブルを上書きするには、--replace
フラグを指定します。テーブルにデータを追加するには、--noreplace
フラグを使用します。フラグを指定しない場合、デフォルトではデータが追加されます。
テーブルを追加または上書きするときに、テーブルのスキーマを変更できます。読み込みオペレーションでサポートされるスキーマの変更については、テーブル スキーマの変更をご覧ください。
(省略可)--location
フラグを指定して、その値をロケーションに設定します。
次のフラグを使用することもできます。
--max_bad_records
: ジョブ全体が失敗する前に許容される不良レコードの最大数を指定する整数。デフォルト値は0
です。--max_bad_records
の値にかかわらず、最大で 5 つの任意のタイプのエラーが返されます。--ignore_unknown_values
: 指定すると、CSV または JSON データで認識されない余分な値が許可され、無視されます。--autodetect
: 指定すると、CSV および JSON データのスキーマ自動検出が有効になります。--destination_kms_key
: テーブルデータの暗号化に使用される Cloud KMS 鍵。
bq --location=LOCATION load \ --[no]replace \ --source_format=FORMAT \ DATASET.TABLE \ PATH_TO_SOURCE \ SCHEMA
次のように置き換えます。
LOCATION
: ロケーション。--location
フラグは省略可能です。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。FORMAT
:NEWLINE_DELIMITED_JSON
DATASET
: 既存のデータセット。TABLE
: データの読み込み先のテーブル名。PATH_TO_SOURCE
: 完全修飾の Cloud Storage URI または URI のカンマ区切りのリスト。ワイルドカードも使用できます。SCHEMA
: 有効なスキーマ。スキーマはローカルの JSON ファイルにすることも、コマンドの一部としてインラインで入力することもできます。また、スキーマ定義を指定する代わりに、--autodetect
フラグを使用することもできます。
例:
次のコマンドは、gs://mybucket/mydata.json
からデータを読み込んで mydataset
内の mytable
というテーブルを上書きします。スキーマはスキーマ自動検出を使用して定義されます。
bq load \
--autodetect \
--replace \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json
次のコマンドは、gs://mybucket/mydata.json
からデータを読み込んで mydataset
内の mytable
というテーブルに追加します。スキーマは、JSON スキーマ ファイル myschema
を使用して定義されます。
bq load \
--noreplace \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
./myschema
API
Cloud Storage のソースデータを参照する
load
ジョブを作成します。(省略可)ジョブリソースの
jobReference
セクションにあるlocation
プロパティでロケーションを指定します。source URIs
プロパティは、完全修飾のgs://BUCKET/OBJECT
の形式にする必要があります。複数の URI をカンマ区切りのリストとして含めることができます。ワイルドカードもサポートされます。configuration.load.sourceFormat
プロパティをNEWLINE_DELIMITED_JSON
に設定して、データ形式を指定します。configuration.load.writeDisposition
プロパティをWRITE_TRUNCATE
またはWRITE_APPEND
に設定して、書き込み設定を指定します。
Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Go の手順に沿って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
Java
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
PHP
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの PHP の手順に沿って設定を行ってください。詳細については、BigQuery PHP API のリファレンス ドキュメントをご覧ください。
Python
既存のテーブルの行を置換するには、LoadJobConfig.write_disposition プロパティを文字列 WRITE_TRUNCATE
に設定します。
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Python の手順に沿って設定を行ってください。 詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
Ruby
既存のテーブルの行を置換するには、Table.load_job() の write
パラメータを "WRITE_TRUNCATE"
に設定します。
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Ruby の手順に沿って設定を行ってください。 詳細については、BigQuery Ruby API のリファレンス ドキュメントをご覧ください。
Hive パーティション分割 JSON データを読み込む
BigQuery では、Cloud Storage に保管されている Hive パーティション分割 JSON データを読み取り可能であり、宛先 BigQuery マネージド テーブルの列として Hive パーティショニング列を取り込みます。詳細については、外部パーティション分割データの読み込みをご覧ください。
JSON データの読み込みの詳細
このセクションでは、JSON データを読み込むときに BigQuery がさまざまなデータ型を解析する方法について説明します。
データ型
Boolean。BigQuery は、ブール値データとして 1 または 0、true または false、t または f、yes または no、y または n(すべて大文字と小文字の区別なし)の任意のペアを解析できます。スキーマ autodetection は、これらのうち 0 と 1 以外を自動的に検出します。Bytes。BYTES 型の列は Base64 としてエンコードする必要があります。
Date。DATE 型の列は YYYY-MM-DD
の形式にする必要があります。
Datetime。DATETIME 型の列は YYYY-MM-DD
HH:MM:SS[.SSSSSS]
の形式にする必要があります。
地理的な意味。GEOGRAPHY 型の列は、次のいずれかの形式の文字列にする必要があります。
- Well-known text(WKT)
- Well-known binary(WKB)
- GeoJSON
WKB を使用する場合は、値を 16 進コードにする必要があります。
有効なデータの例を以下に示します。
- WKT:
POINT(1 2)
- GeoJSON:
{ "type": "Point", "coordinates": [1, 2] }
- Hex encoded WKB:
0101000000feffffffffffef3f0000000000000040
GEOGRAPHY データを読み込む際は、事前に地理空間データの読み込みもご覧ください。
期間。INTERVAL 型の列は ISO 8601 形式 PYMDTHMS
にする必要があります。ここで、
- 値が期間を示す P = 指定子。これは常に指定する必要があります。
- Y = 年
- M = 月
- D = 日
- 期間の時間部分を示す T = 指定子。これは常に指定する必要があります。
- H = 時
- M = 分
- S = 秒。秒は、マイクロ秒の精度で、全体値または最大 6 桁の小数値として表すことができます。
負の値を指定するには、先頭にダッシュ(-)を追加します。
有効なデータの例を以下に示します。
P-10000Y0M-3660000DT-87840000H0M0S
P0Y0M0DT0H0M0.000001S
P10000Y0M3660000DT87840000H0M0S
INTERVAL データを読み込むには、--schema
フラグを使用してスキーマを指定し bq load
コマンドを使用する必要があります。コンソールを使用して INTERVAL データをアップロードすることはできません。
時間。TIME 型の列は HH:MM:SS[.SSSSSS]
の形式にする必要があります。
Timestamp。BigQuery はさまざまなタイムスタンプ形式に対応しています。タイムスタンプには日付の部分と時刻の部分を含める必要があります。
日付の部分は
YYYY-MM-DD
型またはYYYY/MM/DD
型にできます。タイムスタンプ部分は、
HH:MM[:SS[.SSSSSS]]
型にする必要があります(秒数と 1 秒未満の秒数は省略可能です)。日付と時刻はスペースまたは「T」で区切る必要があります。
必要に応じて、日付と時刻の後に UTC オフセットまたは UTC ゾーン指定子(
Z
)を追加できます。詳細については、タイムゾーンをご覧ください。
有効なタイムスタンプ値の例は次のとおりです。
- 2018-08-19 12:11
- 2018-08-19 12:11:35
- 2018-08-19 12:11:35.22
- 2018/08/19 12:11
- 2018-07-05 12:54:00 UTC
- 2018-08-19 07:11:35.220 -05:00
- 2018-08-19T12:11:35.220Z
スキーマを指定すると、BigQuery はタイムスタンプ値として Unix エポック時間も指定できます。ただし、スキーマの自動検出ではこのケースは検出されず、値は数値型または文字列型として扱われます。
Unix エポック タイムスタンプ値の例:
- 1534680695
- 1.534680695e11
Array(繰り返しフィールド)。値は JSON 配列または null
にする必要があります。JSON の null
は SQL の NULL
に変換されます。配列自体に null
値を含めることはできません。
スキーマの自動検出
このセクションでは、JSON ファイルを読み込むときのスキーマの自動検出の動作について説明します。
JSON のネストされたフィールドと繰り返しフィールド
BigQuery は、JSON ファイルのネストされたフィールドと繰り返しフィールドを推測します。フィールド値が JSON オブジェクトの場合、BigQuery は RECORD
型としてその列を読み込みます。フィールド値が配列の場合、BigQuery はこの列を繰り返し列として読み込みます。ネストされたデータと繰り返しデータを含む JSON データの例については、ネストされた JSON データと繰り返し JSON データの読み込みをご覧ください。
文字列変換
スキーマの自動検出を有効にすると、BigQuery は文字列をブール値、数値、または日時型に変換します。たとえば、次の JSON データを使用すると、スキーマの自動検出によって id
フィールドが INTEGER
列に変換されます。
{ "name":"Alice","id":"12"}
{ "name":"Bob","id":"34"}
{ "name":"Charles","id":"45"}
JSON のオプション
BigQuery による JSON データの解析方法を変更するには、Google Cloud コンソール、bq
コマンドライン ツール、API、またはクライアント ライブラリで追加のオプションを指定します。
JSON のオプション | Console のオプション | bq ツールのフラグ |
BigQuery API のプロパティ | 説明 |
---|---|---|---|---|
許可されている不良レコード数 | 許容されるエラー数 | --max_bad_records |
maxBadRecords (Java、Python) |
(省略可)BigQuery がジョブの実行時に無視できる不良レコードの最大数。不良レコードの数がこの値を超えると、ジョブ結果で「無効」エラーが返されます。デフォルト値は 0、つまりすべてのレコードが有効である必要があります。 |
不明な値 | 不明な値を無視 | --ignore_unknown_values |
ignoreUnknownValues (Java、Python) |
(省略可)テーブル スキーマで示されていない余分な値を許可するかどうかを指定します。true の場合、余分な値は無視されます。false の場合、余分な列を含むレコードは不良レコードとして処理され、不良レコードが多すぎる場合はジョブの結果内で無効なエラーが返されます。デフォルト値は false です。なにが余分な値として扱われるかは、sourceFormat プロパティによって決まります。CSV: 末尾の列。JSON: 列名と一致しない名前付きの値。 |
次のステップ
- ローカル ファイルから JSON データを読み込む方法については、ローカル ファイルからのデータの読み込みをご覧ください。
- JSON データの作成、取り込み、クエリの詳細については、GoogleSQL での JSON データの操作をご覧ください。