スキーマの自動検出の使用
スキーマの自動検出
スキーマの自動検出により、BigQuery は CSV、JSON、Google スプレッドシートのデータのスキーマを推測できます。スキーマの自動検出は、BigQuery にデータを読み込む場合や、外部データソースに対してクエリを行う場合に使用できます。
自動検出を有効にすると、BigQuery は各列のデータ型を推測します。BigQuery は、データソース内でランダムにファイルを選択し、データの最初の最大 500 行をスキャンして、代表的なサンプルとして使用します。BigQuery は、各フィールドを検証し、そのサンプル内の値に基づいてそのフィールドにデータ型を割り当てようとします。列内のすべての行が空の場合、自動検出はデフォルトで列の STRING
データ型になります。
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 のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Java の設定手順を完了してください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Node.js の設定手順を完了してください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
PHP
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある PHP の設定手順を完了してください。詳細については、BigQuery PHP API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Python
スキーマの自動検出を有効にするには、LoadJobConfig.autodetect プロパティを True
に設定します。
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Python の設定手順を完了してください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Ruby
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Ruby の設定手順を完了してください。詳細については、BigQuery Ruby API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
外部データソースのスキーマ自動検出
スキーマの自動検出は、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 データの読み込みをご覧ください。
Google スプレッドシートのスキーマの自動検出
スプレッドシートの場合、CSV ファイルの自動検出と同様に、BigQuery は最初の行がヘッダー行かどうかを自動検出します。最初の行がヘッダーとして識別されると、BigQuery はヘッダー行のフィールド名に基づいて列名を割り当て、その行をスキップします。この名前は、BigQuery の列の命名規則を満たすように変更される場合があります。たとえば、スペースはアンダースコアに置き換えられます。
テーブルのセキュリティ
BigQuery でテーブルへのアクセスを制御するには、テーブルのアクセス制御の概要をご覧ください。