データの処理

このドキュメントでは BigQuery データの取り扱い方法(パラメータの設定、ネストされたフィールドや繰り返しフィールドの処理など)について説明します。

パラメータ

REST API メソッドは、pathquerybody の 3 種類のパラメータを受け取ります。次のメソッドのシグネチャは、3 つすべてのパラメータの型を示しています。

PUT https://www.googleapis.com/bigquery/v2/projects/{projectId}/datasets/{datasetId}/tables/{tableId}?userIp="192.0.2.211"
{
   "friendlyName": string,
   "description": string
}
  • projectIddatasetIdtableId はすべてパスパラメータです
  • userIp はクエリ パラメータです
  • friendlyNamedescription は、両方とも本文パラメータです

API ドキュメントは BigQuery で具体的に定義されたすべてのクエリ パラメータについて説明しています。すべてのオペレーションに適用されるクエリ パラメータを以下に示します。

パラメータの設定

クライアント ライブラリによって、パラメータの型を設定するためのテクニックは異なります。たとえば、Python クライアントを使用する場合は、パスパラメータとクエリ パラメータを同じ方法で設定しますが、本文パラメータを設定するには別の方法を使用します。

updateResponse = tableCollection.update(projectId='1234',           # Path param
                                        datasetId='5678',           # Path param
                                        tableId='9012',             # Path param
                                        userIp='192.0.2.211'        # Query param
                                        body={'friendlyName':'Donut Count',                 # Body params
                                              'description':'Worldwide donut usage count'}) #

トップへ戻る

すべての Google BigQuery API オペレーションに適用されるクエリ パラメータを次の表に示します。

注(API キーと auth トークンに関する):

  1. リクエストで OAuth 2.0 トークンを指定しない限りkey パラメータはすべてのリクエストで必要になります。
  2. OAuth スコープを必要とするすべてのリクエストで認証トークンを送信する必要がありますOAuth 2.0 はサポートされている唯一の認証プロトコルです。
  3. リクエストで OAuth 2.0 トークンを提供するには、次のいずれかの方法を使用します。
    • access_token クエリ パラメータを使用します。例: ?access_token=oauth2-token
    • HTTP Authorization ヘッダーを使用します。例: Authorization: Bearer oauth2-token

特に記載のない限り、すべてのパラメータは省略可能です。

パラメータ 意味 メモ
access_token 現在のユーザーの OAuth 2.0 トークン。
callback コールバック関数。
  • レスポンスを処理する JavaScript コールバック関数の名前。
  • JavaScript JSON-P リクエストで使用されます。
fields レスポンスに含めるフィールドのサブセットを指定するセレクタ。
  • 詳細については、部分レスポンスをご覧ください。
  • パフォーマンス向上のために使用します。
key API キー(必須*)
  • *OAuth 2.0 トークンを指定しない場合は必須。
  • API キーによりプロジェクトが特定され、API アクセス、割り当て、レポートが提供されます。
  • プロジェクトの API キーは Google Cloud Platform Console から入手します。
prettyPrint

インデントと改行の付いたレスポンスを返す。

  • true の場合は、人が読める形式でレスポンスを返します。
  • デフォルト値: true
  • false の場合、レスポンスのペイロード サイズが減るため、環境によってはパフォーマンスが向上する場合があります。
quotaUser userIp の代替。
  • ユーザーの IP アドレスが不明な場合でも、サーバー側アプリケーションからユーザーごとの割り当てを適用できます。この状況は、ユーザーに代わって App Engine で cron ジョブを実行するアプリケーションで発生する場合があります。
  • ユーザーを一意に識別する任意の文字列を 40 文字以内で選択できます。
  • 両方指定した場合、userIp は無視されます。
  • 詳しくは、API 使用の上限設定をご覧ください。
userIp API 呼び出しが行われているエンドユーザーの IP アドレス。
  • サーバー側アプリケーションから API を呼び出す際に、ユーザーごとの割り当てを適用できます。
  • 詳しくは、API 使用の上限設定をご覧ください。

検索結果のページ分割

collection.list メソッドから返される結果は特定の状況下でページ分割されます。ページあたりの結果の数は maxResults プロパティによって制御されます。

メソッド ページ分割基準 maxResults のデフォルト値 maxResults の最大値
Tabledata.list レスポンス サイズがシリアル化された JSON で 10 MB を超える場合、または maxResults 行を超える場合は、ページ分割された結果を返します。 100,000 100,000
その他すべての collection.list メソッド レスポンスが maxResults 行を超える場合、ページ分割された結果を返します。 50 1,000

maxResults を上記の最大値より大きい値に設定すると、結果は最大値に基づいてページ分割されます。

ページは全行数のサブセットです。1 ページ分のデータを超える場合、結果データは pageToken プロパティを持ちます。 次のページの結果を取得するには、list をもう一度呼び出し、pageToken という URL パラメータにこのトークン値を指定します。

テーブルデータのページ分割に使用する bigquery.tabledata.list メソッドは行オフセット値またはページトークンを使用します。詳しくは、テーブルデータの閲覧をご覧ください。

BigQuery の結果をページ分割するサンプルを以下に示します。

C#

BigQuery クライアントのインストールと作成の詳細については、BigQuery クライアント ライブラリをご覧ください。

public int TableDataList(
    string datasetId, string tableId, int pageSize, BigQueryClient client)
{
    int recordCount = 0;
    var result = client.ListRows(datasetId, tableId, null,
        new ListRowsOptions { PageSize = pageSize });
    // If there are more rows than were returned in the first page of results,
    // iterating over the rows will lazily evaluate the results each time,
    // making further requests as necessary.
    foreach (var row in result)
    {
        Console.WriteLine($"{row["title"]}: {row["unique_words"]}");
        recordCount++;
    }
    return recordCount;
}

Java

BigQuery クライアントのインストールと作成の詳細については、BigQuery クライアント ライブラリをご覧ください。

Page<List<FieldValue>> tableData =
    bigquery.listTableData(datasetName, tableName, TableDataListOption.pageSize(100));
for (List<FieldValue> row : tableData.iterateAll()) {
  // do something with the row
}

Go

BigQuery クライアントのインストールと作成の詳細については、BigQuery クライアント ライブラリをご覧ください。

Go 用の Google Cloud クライアント ライブラリでは、デフォルトで自動的に結果がページ分割されるため、ページ分割のためのコードを記述する必要はありません。次に例を示します。

table := client.Dataset(datasetID).Table(tableID)
it := table.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Node.js

BigQuery クライアントのインストールと作成の詳細については、BigQuery クライアント ライブラリをご覧ください。

Node.js 用の Google Cloud クライアント ライブラリでは、デフォルトで自動的に結果がページ分割されるため、ページ分割のためのコードを記述する必要はありません。次に例を示します。

// Imports the Google Cloud client library
const BigQuery = require('@google-cloud/bigquery');

// The project ID to use, e.g. "your-project-id"
// const projectId = "your-project-id";

// The ID of the dataset of the table to browse, e.g. "my_dataset"
// const datasetId = "my_dataset";

// The ID of the table to browse, e.g. "my_table"
// const tableId = "my_table";

// Instantiates a client
const bigquery = BigQuery({
  projectId: projectId
});

// Lists rows in the table
bigquery
  .dataset(datasetId)
  .table(tableId)
  .getRows()
  .then((results) => {
    const rows = results[0];
    console.log('Rows:');
    rows.forEach((row) => console.log(row));
  })
  .catch((err) => {
    console.error('ERROR:', err);
  });

PHP

BigQuery クライアントのインストールと作成の詳細については、BigQuery クライアント ライブラリをご覧ください。

PHP 用の Google Cloud クライアント ライブラリの場合、反復処理の中でジェネレータ関数 rows を使用すると、ページ分割が自動的に行われます。この関数を使用して次のページの結果をフェッチします。

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Browse a bigquery table.
 * Example:
 * ```
 * browse_table($projectId, $datasetId, $tableId);
 * ```
 *
 * @param string $projectId  The Google project ID.
 * @param string $datasetId  The BigQuery dataset ID.
 * @param string $tableId    The BigQuery table ID.
 * @param string $maxResults The number of results to return at a time.
 * @param string $startIndex The row index to start on.
 *
 * @return int number of rows returned
 */
function browse_table($projectId, $datasetId, $tableId, $maxResults = 10, $startIndex = 0)
{
    $options = [
        'maxResults' => $maxResults,
        'startIndex' => $startIndex
    ];
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);
    $numRows = 0;
    foreach ($table->rows($options) as $row) {
        print('---');
        foreach ($row as $column => $value) {
            printf('%s: %s' . PHP_EOL, $column, $value);
        }
        $numRows++;
    }

    return $numRows;
}

Python

BigQuery クライアントのインストールと作成の詳細については、BigQuery クライアント ライブラリをご覧ください。

def list_rows(dataset_name, table_name, project=None):
    """Prints rows in the given table.

    Will print 25 rows at most for brevity as tables can contain large amounts
    of rows.

    If no project is specified, then the currently active project is used.
    """
    bigquery_client = bigquery.Client(project=project)
    dataset = bigquery_client.dataset(dataset_name)
    table = dataset.table(table_name)

    if not table.exists():
        print('Table {}:{} does not exist.'.format(dataset_name, table_name))
        return

    # Reload the table so that the schema is available.
    table.reload()

    # Load at most 25 results. You can change the max_results argument to load
    # more rows from BigQuery, but note that this can take some time. It's
    # preferred to use a query.
    rows = list(table.fetch_data(max_results=25))

    # Use format to create a simple table.
    format_string = '{!s:<16} ' * len(table.schema)

    # Print schema field names
    field_names = [field.name for field in table.schema]
    print(format_string.format(*field_names))

    for row in rows:
        print(format_string.format(*row))

Ruby

BigQuery クライアントのインストールと作成の詳細については、BigQuery クライアント ライブラリをご覧ください。

Ruby 用 Google Cloud クライアント ライブラリの場合、Table#dataData#next を使用すると、ページ分割が自動的に行われます。

# project_id = "Your Google Cloud project ID"
# dataset_id = "ID of the dataset containing table"
# table_id   = "ID of the table to display data for"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id
dataset  = bigquery.dataset dataset_id
table    = dataset.table table_id

table.data.each do |row|
  row.each      do |column_name, value|
    puts "#{column_name} = #{value}"
  end
end

任意のページのリクエストと、冗長リスト呼び出しの回避

前のページに戻る場合、またはキャッシュされた pageToken の値を使用して任意のページにジャンプする場合、ページのデータが最後に表示された後で変更されている可能性がありますが、データが変更されたことは明確には示されません。これを確認するには、ETag プロパティを使用できます。

すべての collection.list メソッド(Tabledata 用を除く)は、結果で Etag プロパティを返します。このプロパティは、ページが最後のリクエスト以降に変更されているかどうかを確認するために使用できるページ結果のハッシュです。ETag 値を使用して BigQuery にリクエストを行うと、BigQuery は ETag 値と API によって返された ETag 値を比較し、ETag 値が一致しているかどうかに基づいて応答します。次の方法で、冗長なリスト呼び出しを回避するために ETag を使用できます。

  • 値が変更されている場合にのみリスト値を返す場合:

    値が変更されている場合にのみリスト値のページを返す場合は、HTTP の "If-None-Match" ヘッダーを使用し、保存しておいた ETag を指定してリスト呼び出しを行うことができます。指定した ETag がサーバー上の ETag と一致しない場合、BigQuery は、新しいリスト値のページを返します。Etag が一致する場合、BigQuery は HTTP 304 "変更されていません" 結果を返し、値を返しません。この例として、ユーザーが BigQuery に格納されている情報を定期的に入力するウェブページがあります。ETag と If-None-Match ヘッダーを使用することにより、データに変更がない場合に冗長なリスト呼び出しを避けることができます。

  • 値が変更されていない場合にのみリスト値を返す場合:

    リストの値が変更されていない場合にのみリスト値のページを返す場合は、HTTP の "if-match" ヘッダーを使用できます。BigQuery は、ETag 値を比較し、結果が変更されていない場合は結果のページを返します。ページが変更されている場合は、412 "前提条件失敗" を返します。

注: Etag は冗長なリスト呼び出しを避けるために有効な方法ですが、同じ方法を使用して、いずれかのオブジェクトが変更されたかどうかを確認できます。たとえば、特定のテーブルに対する get リクエストを実行し、Etag を使用して、テーブルが完全なレスポンスを返す前に変更されているかどうかを判断できます。

トップへ戻る

レガシー SQL でのネストされたデータと繰り返しデータ

このセクションの情報はレガシー SQL に適用されます。ネストされたデータと繰り返しデータには、標準 SQL のクエリ構文を使用してください。標準 SQL でネストされたデータと繰り返しデータを扱う方法については、レガシー SQL からの移行をご覧ください。

BigQuery では、JSON または Avro ファイル形式によるネストされたデータと繰り返しデータの読み込みおよびエクスポートをサポートしています。多くのレガシー SQL クエリの場合、BigQuery はデータを自動的にフラット化します。たとえば、多くの SELECT 文は、データの構造を維持しながらネストされたフィールドまたは繰り返しフィールドを取得します。WHERE 句を使用すると、構造を維持しながらデータをフィルタリングできます。逆に、ORDER BY 句と GROUP BY 句は、照会されたデータを暗黙的にフラット化します。データが暗黙的にフラット化されない場合(レガシー SQL で複数の繰り返しフィールドにクエリを実行する場合など)、FLATTENWITHIN SQL 関数を使用してデータにクエリを実行できます。

次のサンプル ファイルをダウンロードし、BigQuery アカウントにアップロードすることによって、これらのクエリを実行して試すことができます。

FLATTEN

ネストされたデータを照会する場合、BigQuery は自動的にテーブル データをフラット化します。たとえば、personsDataSchema.json を見てみましょう。

   Last modified                 Schema                 Total Rows   Total Bytes   Expiration
 ----------------- ----------------------------------- ------------ ------------- ------------
  27 Sep 10:01:06   |- kind: string                     4            794
                    |- fullName: string (required)
                    |- age: integer
                    |- gender: string
                    +- phoneNumber: record
                    |  |- areaCode: integer
                    |  |- number: integer
                    +- children: record (repeated)
                    |  |- name: string
                    |  |- gender: string
                    |  |- age: integer
                    +- citiesLived: record (repeated)
                    |  |- place: string
                    |  +- yearsLived: integer (repeated)

いくつかの繰り返しフィールドとネストされたフィールドがあることに注意してください。次のクエリを実行するとします。

SELECT
  fullName AS name,
  age,
  gender,
  citiesLived.place,
  citiesLived.yearsLived
FROM [dataset.tableId]

この場合、データはフラット化されて出力されます。

+---------------+-----+--------+-------------------+------------------------+
|     name      | age | gender | citiesLived_place | citiesLived_yearsLived |
+---------------+-----+--------+-------------------+------------------------+
| John Doe      |  22 | Male   | Seattle           |                   1995 |
| John Doe      |  22 | Male   | Stockholm         |                   2005 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1989 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1993 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1998 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   2002 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1990 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1993 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1998 |
| Mike Jones    |  35 | Male   | Washington DC     |                   2008 |
| Mike Jones    |  35 | Male   | Portland          |                   1993 |
| Mike Jones    |  35 | Male   | Portland          |                   1998 |
| Mike Jones    |  35 | Male   | Portland          |                   2003 |
| Mike Jones    |  35 | Male   | Portland          |                   2005 |
| Mike Jones    |  35 | Male   | Austin            |                   1973 |
| Mike Jones    |  35 | Male   | Austin            |                   1998 |
| Mike Jones    |  35 | Male   | Austin            |                   2001 |
| Mike Jones    |  35 | Male   | Austin            |                   2005 |
| Anna Karenina |  45 | Female | Stockholm         |                   1992 |
| Anna Karenina |  45 | Female | Stockholm         |                   1998 |
| Anna Karenina |  45 | Female | Stockholm         |                   2000 |
| Anna Karenina |  45 | Female | Stockholm         |                   2010 |
| Anna Karenina |  45 | Female | Russia            |                   1998 |
| Anna Karenina |  45 | Female | Russia            |                   2001 |
| Anna Karenina |  45 | Female | Russia            |                   2005 |
| Anna Karenina |  45 | Female | Austin            |                   1995 |
| Anna Karenina |  45 | Female | Austin            |                   1999 |
+---------------+-----+--------+-------------------+------------------------+

この例では、citiesLived.placecitiesLived_place になり、citiesLived.yearsLivedcitiesLived_yearsLived になっています。

BigQuery はネストされたフィールドを自動的にフラット化できますが、複数の繰り返しフィールドを処理するときは、明示的に FLATTEN を呼び出す必要があります。たとえば、次のクエリを実行するとします。

SELECT fullName, age
FROM [dataset.tableId]
WHERE
  (citiesLived.yearsLived > 1995 ) AND
  (children.age > 3)

この場合、次のようなエラーが返されます。

Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived

複数の繰り返しフィールドに対してクエリを実行するには、いずれかのフィールドをフラット化する必要があります。

SELECT
  fullName,
  age,
  gender,
  citiesLived.place
FROM (FLATTEN([dataset.tableId], children))
WHERE
  (citiesLived.yearsLived > 1995) AND
  (children.age > 3)
GROUP BY fullName, age, gender, citiesLived.place

この場合、次の結果が返されます。

+------------+-----+--------+-------------------+
|  fullName  | age | gender | citiesLived_place |
+------------+-----+--------+-------------------+
| John Doe   |  22 | Male   | Stockholm         |
| Mike Jones |  35 | Male   | Los Angeles       |
| Mike Jones |  35 | Male   | Washington DC     |
| Mike Jones |  35 | Male   | Portland          |
| Mike Jones |  35 | Male   | Austin            |
+------------+-----+--------+-------------------+

WITHIN 句

WITHIN キーワードは特に集計関数と組み合わせてレコード内またはネストされたフィールド内の子や繰り返しフィールドを集計する場合に使用します。WITHIN キーワードを指定するとき、次のいずれかを使用して集計する範囲を指定する必要があります。

  • WITHIN RECORD: レコードに含まれる繰り返し値のデータを集計します。
  • WITHIN node_name: 指定したノードに含まれる繰り返し値のデータを集計します。ここで、node_name は集計関数に指定したフィールドの親ノードの名前です。

上記の例で、それぞれの人の子供の数を調べるとします。そのためには、各レコードの children.name の数をカウントします。

SELECT
  fullName,
  COUNT(children.name) WITHIN RECORD AS numberOfChildren
FROM [dataset.tableId];

次の結果が得られます。

+---------------+------------------+
|   fullName    | numberOfChildren |
+---------------+------------------+
| John Doe      |                2 |
| Jane Austen   |                2 |
| Mike Jones    |                3 |
| Anna Karenina |                0 |
+---------------+------------------+

比較のために、子供の名前をすべてリストしてみます。

SELECT fullName, children.name
FROM [dataset.tableId]
+---------------+---------------+
|   fullName    | children_name |
+---------------+---------------+
| John Doe      | Jane          |
| John Doe      | John          |
| Jane Austen   | Josh          |
| Jane Austen   | Jim           |
| Mike Jones    | Earl          |
| Mike Jones    | Sam           |
| Mike Jones    | Kit           |
| Anna Karenina | None          |
+---------------+---------------+

これは WITHIN RECORD のクエリの結果と一致します。John Doe には、Jane と John という名前の 2 人の子供がいます。Jane Austen には、Josh と Jim という名前の 2 人の子供がいます。Mike Jones には、Earl、Sam、Kit という 3 人の子供がいます。Anna Karenina には子供がいません。

ここで、人々が住んだことがある場所の数を知りたいとします。この場合は、WITHIN 句を使用して 1 つの特定のノード全体で集計を実行します。

SELECT
  fullName,
  COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived,
  citiesLived.place,
  COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity,
FROM [dataset.tableId];
+---------------+---------------------+-------------------+-------------------------+
|   fullName    | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity |
+---------------+---------------------+-------------------+-------------------------+
| John Doe      |                   2 | Seattle           |                       1 |
| John Doe      |                   2 | Stockholm         |                       1 |
| Mike Jones    |                   4 | Los Angeles       |                       4 |
| Mike Jones    |                   4 | Washington DC     |                       4 |
| Mike Jones    |                   4 | Portland          |                       4 |
| Mike Jones    |                   4 | Austin            |                       4 |
| Anna Karenina |                   3 | Stockholm         |                       4 |
| Anna Karenina |                   3 | Russia            |                       3 |
| Anna Karenina |                   3 | Austin            |                       2 |
+---------------+---------------------+-------------------+-------------------------+

このクエリは、次のことを行います。

  • citiesLived.placeWITHIN RECORD を実行し、それぞれの人が住んだことのある場所の数をカウントします。
  • citiesLived.yearsLivedWITHIN を実行し、それぞれの人が各都市に住んだことのある回数をカウントします(citiesLived 全体でカウント)。

範囲を設定してネストされたフィールドや繰り返しフィールドを集計する機能は BigQuery の長所の 1 つであり、コストのかかる結合をクエリで使用する必要がなくなります。

トップへ戻る

外出先でもリソースをモニタリング

Google Cloud Console アプリを入手して、プロジェクトの管理にお役立てください。

フィードバックを送信...

BigQuery のドキュメント