スキーマの自動検出の使用
スキーマの自動検出
スキーマの自動検出により、BigQuery は CSV、JSON、Google スプレッドシートのデータのスキーマを推測できます。スキーマの自動検出は、BigQuery にデータを読み込む場合や、外部データソースに対してクエリを行う場合に使用できます。
自動検出を有効にすると、BigQuery は各列のデータ型を推測します。BigQuery は、データソース内でランダムにファイルを選択し、データの最初の最大 500 行をスキャンして、代表的なサンプルとして使用します。 次に BigQuery は、各フィールドを検証し、そのサンプル内の値に基づいてそのフィールドにデータ型を割り当てようとします。
CSV、JSON、Google スプレッドシートのデータに対してスキーマの自動検出を有効にしない場合は、テーブルの作成時に手動でスキーマを指定する必要があります。
Avro、Parquet、ORC、Firestore エクスポート、Datastore エクスポート ファイルでは、スキーマの自動検出を有効にする必要はありません。これらのファイル形式は自己記述型であるため、BigQuery はソースデータからテーブル スキーマを自動的に推測します。Parquet、Avro、Orc ファイルでは、推定されたスキーマをオーバーライドする明示的なスキーマをオプションで指定できます。
テーブルに検出されたスキーマは、次の方法で表示できます。
- Google Cloud コンソールを使用する
bq
コマンドライン ツールのbq show
コマンドを使用する。
BigQuery がスキーマを検出するときに、GoogleSQL 構文に対応させるためにフィールド名が変更されることがまれにあります。
データ型の変換の詳細については、次をご覧ください。
- データ型の変換(Datastore からのデータの読み込み時)
- データ型の変換(Firestore からのデータの読み込み時)
- Avro の変換
- Parquet の変換
- ORC 変換
スキーマ自動検出を使用したデータの読み込み
データの読み込み時にスキーマの自動検出を有効にするには、次のいずれかの方法を使用します。
- Google Cloud コンソールで、[スキーマ] セクションにおいて、[自動検出] について、[スキーマと入力パラメータ] オプションをオンにします。
bq
コマンドライン ツールで、--autodetect
パラメータを指定してbq load
コマンドを実行します。
スキーマの自動検出を有効にすると、BigQuery は CSV と JSON ファイルのスキーマを可能な限り自動的に推測します。自動検出のロジックでは、最初の 500 行までのデータを読み取り、スキーマ フィールドの型を推測します。--skip_leading_rows
フラグを指定すると、先頭行はスキップされます。フィールドの型は、フィールドが最も大きい行に基づいて推測されます。すべての列またはフィールドに値が存在するデータ行が 1 つ以上存在していれば、自動検出は想定どおりに機能します。
Avro、Parquet、ORC の各ファイル、Firestore と Datastore のエクスポート ファイルでは、スキーマの自動検出は使用されません。この種のファイルを BigQuery に読み込むと、自己記述型ソースデータから自動的にテーブル スキーマが取得されます。
JSON や CSV データを読み込むときにスキーマの自動検出を使用するには:
コンソール
Google Cloud コンソールで [BigQuery] ページに移動します。
[エクスプローラ] パネルでプロジェクトを開いて、データセットを選択します。
アクション オプションを開いて、[開く] をクリックします。
詳細パネルで [テーブルを作成]
をクリックします。[テーブルの作成] ページの [ソース] セクションで、次の操作を行います。
- [テーブルの作成元] で、目的のソースタイプを選択します。
ソース フィールドで、ファイルまたは Cloud Storage バケットを選択するか、Cloud Storage URI を入力します。Google Cloud コンソールで複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成するテーブルを含むデータセットと同じロケーションに存在する必要があります。
ファイル形式として、CSV または JSON を選択します。
[テーブルの作成] ページの [送信先] セクションで、次の操作を行います。
[データセット名] で、該当するデータセットを選択します。
[テーブル名] フィールドに、作成するテーブルの名前を入力します。
[テーブルタイプ] が [ネイティブ テーブル] に設定されていることを確認します。
[テーブルを作成] をクリックします。
bq
--autodetect
パラメータを指定して bq load
コマンドを発行します。
(省略可)--location
フラグを指定して、その値をロケーションに設定します。
次のコマンドは、スキーマの自動検出を使用してファイルを読み込みます。
bq --location=LOCATION load \ --autodetect \ --source_format=FORMAT \ DATASET.TABLE \ PATH_TO_SOURCE
次のように置き換えます。
LOCATION
: ロケーションの名前。--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。ロケーションのデフォルト値は、.bigqueryrc ファイルを使用して設定できます。FORMAT
:NEWLINE_DELIMITED_JSON
またはCSV
。DATASET
: データの読み込み先のテーブルを含むデータセット。TABLE
: データの読み込み先のテーブル名。PATH_TO_SOURCE
: CSV または JSON ファイルの場所です。
例:
次のコマンドを入力して、myfile.csv
をローカルマシンから、mydataset
というデータセット内に格納されている mytable
という名前のテーブルに読み込みます。
bq load --autodetect --source_format=CSV mydataset.mytable ./myfile.csv
次のコマンドを入力して、myfile.json
をローカルマシンから、mydataset
というデータセット内に格納されている mytable
という名前のテーブルに読み込みます。
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable ./myfile.json
API
ソースデータを指す
load
ジョブを作成します。ジョブの作成方法の詳細については、プログラムによる BigQuery ジョブの実行をご覧ください。jobReference
セクションにあるlocation
プロパティでロケーションを指定します。sourceFormat
プロパティを設定して、データ形式を指定します。スキーマの自動検出を使用するには、この値をNEWLINE_DELIMITED_JSON
またはCSV
に設定する必要があります。autodetect
プロパティを使用して、スキーマの自動検出をtrue
に設定します。
Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Go の手順に沿って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
Java
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
PHP
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの PHP の手順に沿って設定を行ってください。詳細については、BigQuery PHP API のリファレンス ドキュメントをご覧ください。
Python
スキーマの自動検出を有効にするには、LoadJobConfig.autodetect プロパティを True
に設定します。
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Python の手順に沿って設定を行ってください。 詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
Ruby
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Ruby の手順に沿って設定を行ってください。詳細については、BigQuery Ruby API のリファレンス ドキュメントをご覧ください。
外部データソースのスキーマ自動検出
スキーマの自動検出は、CSV、JSON、Google スプレッドシートの外部データソースで使用できます。スキーマの自動検出を有効にすると、BigQuery はソースデータからスキーマを可能な限り自動的に推測します。これらのソースでスキーマの自動検出を有効にしない場合は、明示的なスキーマを指定する必要があります。
外部 Avro、Parquet、ORC、Firestore エクスポート、Datastore エクスポート ファイルに対してクエリを実行する場合、スキーマの自動検出を有効にする必要はありません。これらのファイル形式は自己記述型であるため、BigQuery はソースデータからテーブル スキーマを自動的に推測します。Parquet、Avro、Orc ファイルでは、推定スキーマをオーバーライドする明示的なスキーマをオプションで指定できます。
Google Cloud コンソールでは、[自動検出] について [スキーマと入力パラメータ] オプションをオンにして、スキーマの自動検出を有効にできます。
bq
コマンドライン ツールを使用すると、CSV、JSON、Google スプレッドシート データ用のテーブル定義ファイルを作成するときに、スキーマの自動検出を有効にできます。bq
ツールを使用してテーブル定義ファイルを作成する場合は、--autodetect
フラグを mkdef
コマンドに渡してスキーマの自動検出を有効にするか、--noautodetect
フラグを渡して自動検出を無効にします。
--autodetect
フラグを使用する場合は、テーブル定義ファイルで autodetect
を true
に設定します。--noautodetect
を使用する場合は、autodetect
を false
に設定します。テーブル定義の作成時に外部データソースのスキーマを定義せず、--noautodetect
または--autodetect
フラグを使用しない場合は、autodetect
をデフォルトの true
に設定します。
API を使用してテーブル定義ファイルを作成する場合は、autodetect
プロパティの値を true
または false
に設定します。autodetect
を true
に設定すると、自動検出が有効になります。autodetect
を false
に設定すると、自動検出が無効になります。
自動検出の詳細
自動検出では、スキーマの詳細が検出されるだけでなく、次のものも認識されます。
圧縮
BigQuery は、ファイルを開くときに gzip 互換ファイル圧縮を認識します。
日付と時刻の値
BigQuery はソースデータの形式に基づいて日付と時刻の値を検出します。
DATE
列の値は、YYYY-MM-DD
の形式にする必要があります。
TIME
列の値は、HH:MM:SS[.SSSSSS]
の形式にする必要があります(秒の小数点以下は省略可能です)。
TIMESTAMP
列では、BigQuery は以下のような幅広いタイムスタンプ形式を検出します(ただし、これらに限定されません)。
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSSSSS
YYYY/MM/DD HH:MM
タイムスタンプには、UTC オフセットと UTC ゾーン指定子「Z」を含めることもできます。
BigQuery がタイムスタンプ値として自動的に検出する値の例を次に示します。
- 2018-08-19 12:11
- 2018-08-19 12:11:35.22
- 2018/08/19 12:11
- 2018-08-19 07:11:35.220 -05:00
BigQuery が形式を認識しない場合、列は文字列データ型として読み込まれます。その場合、ソースデータを読み込む前にデータの前処理が必要になることがあります。たとえば、スプレッドシートから CSV データをエクスポートする場合は、ここに示す例のいずれかに一致する日付形式を設定します。BigQuery に読み込んだ後でデータを変換することもできます。
CSV データのスキーマ自動検出
CSV 区切り文字
BigQuery は以下の区切り文字を検出します。
- カンマ(,)
- パイプ(|)
- タブ(\t)
CSV ヘッダー
BigQuery は、ファイルの最初の行をファイル内の他の行と比較することでヘッダーを推定します。最初の行には文字列のみが含まれ、他の行には他のデータ型も含まれる場合、BigQuery はその最初の行がヘッダー行であると想定します。この場合、BigQuery はヘッダー行のフィールド名に基づいて列名を割り当てます。この名前は、BigQuery の列の命名規則を満たすように変更される場合があります。たとえば、スペースはアンダースコアに置き換えられます。
それ以外の場合、BigQuery は最初の行がデータ行であると想定し、string_field_1
などの一般的な列名を割り当てます。テーブルを作成した後は、スキーマでは列名を更新できませんが、手動で名前を変更することはできます。もう 1 つの方法は、autodetect を使用する代わりに明示的なスキーマを指定することです。
すべてのデータ フィールドが文字列のヘッダー行を含む CSV ファイルがあるとします。この場合、最初の行がヘッダーであることを BigQuery は自動的に検出しません。--skip_leading_rows
オプションを使用することで、ヘッダー行をスキップできます。そうしない場合、ヘッダーはデータとしてインポートされます。こうしたケースでは、明示的なスキーマを指定して列名を割り当てることも検討してください。
CSV 引用符付き改行
BigQuery は、CSV フィールド内の引用符付き改行文字を検出し、引用符付き改行文字を行境界として解釈しません。
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"}
詳細については、Cloud Storage からの JSON データの読み込みをご覧ください。
テーブルのセキュリティ
BigQuery でテーブルへのアクセスを制御するには、テーブルのアクセス制御の概要をご覧ください。