時間単位の列パーティション分割テーブルの作成と使用

このドキュメントでは、DATE、TIMESTAMP、または DATETIME 列で分割されたテーブルを作成して使用する方法について説明します。取り込み時間パーティション分割テーブルについては、取り込み時間パーティション分割テーブルの作成と使用をご覧ください。整数範囲パーティション分割テーブルについては、整数範囲パーティション分割テーブルの作成と使用をご覧ください。

パーティション分割テーブルを作成すると、次のことができるようになります。

  • テーブルデータへのアクセスを制御する
  • パーティション分割テーブルに関する情報を取得する
  • データセット内のパーティション分割テーブルのリストを取得する
  • メタテーブルを使用してパーティション分割テーブルのメタデータを取得する

パーティション分割テーブルのプロパティの更新、パーティション分割テーブルのコピー、パーティション分割テーブルの削除など、パーティション分割テーブルの管理の詳細については、パーティション分割テーブルの管理をご覧ください。

制限事項

パーティション分割テーブルには次の制限事項があります。

  • パーティショニング列は、スカラー DATE 列、TIMESTAMP 列、DATETIME 列のいずれかであることが必要です。列のモードは REQUIRED または NULLABLE にできますが、REPEATED（配列ベース）にすることはできません。
  • パーティショニング列はトップレベル フィールドであることが必要です。分割する列として RECORD（STRUCT）のリーフ フィールドは使用できません。
  • レガシー SQL を使用して、パーティション分割テーブルをクエリすることや、クエリ結果をパーティション分割テーブルに書き込むことはできません。

パーティション分割テーブルの作成

パーティション分割テーブルは次の方法で作成できます。

  • Cloud Console を使用する。
  • PARTITION BY 句に partition expression を指定したデータ定義言語（DDL）CREATE TABLE ステートメントを使用する。
  • bq コマンドライン ツールの bq mk コマンドを使用する。
  • プログラムで tables.insert API メソッドを呼び出して作成する。
  • クエリ結果から作成する。
  • データを読み込むときに作成する。

テーブルの命名

BigQuery でテーブルを作成するとき、テーブル名はデータセットごとに一意である必要があります。テーブル名の要件は次のとおりです。

  • 1,024 文字以内。
  • カテゴリ L（文字）、M（マーク）、N（数字）、Pc（コネクタ、アンダースコアを含む）、Pd（ダッシュ）、Zs（スペース）の Unicode 文字を含む。詳しくは、一般カテゴリをご覧ください。

たとえば、table-01、ग्राहक、00_お客様、étudiant はすべて有効なテーブル名です。

必要な権限

テーブルを作成するには、少なくとも次の権限が付与されている必要があります。

  • bigquery.tables.create（テーブルを作成する権限）
  • bigquery.tables.updateData（読み込みジョブ、クエリジョブ、コピージョブを使用してテーブルにデータを書き込む権限）
  • bigquery.jobs.create（テーブルにデータを書き込むクエリジョブ、読み込みジョブ、コピージョブを実行する権限）

テーブルに書き込むデータにアクセスするには、bigquery.tables.getData などの追加の権限が必要になる場合があります。

次の事前定義済みの IAM ロールには bigquery.tables.create 権限と bigquery.tables.updateData 権限の両方が含まれています。

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

次の事前定義済みの IAM ロールには bigquery.jobs.create 権限が含まれています。

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

また、bigquery.datasets.create 権限を持つユーザーがデータセットを作成すると、そのデータセットに対する bigquery.dataOwner アクセス権がユーザーに付与されます。bigquery.dataOwner アクセス権があれば、データセット内でテーブルを作成および更新できます。

BigQuery での IAM のロールと権限について詳しくは、事前定義ロールと権限をご覧ください。

日別パーティショニングと、時間別、月別、年別のパーティショニングの比較

TIMESTAMP、DATETIME、DATE 列を使用してデータを分割する場合、データとニーズに応じて、日単位、時間単位、月単位、年単位のいずれかでパーティションを作成できます。

日別パーティショニングはデフォルトのパーティショニング タイプで、クラスタリングで使用すると、ほとんどの BigQuery のユースケースで適切に機能します。特に、データが長期間にわたって分散している場合や、データが継続的に追加される場合は、日別パーティショニングが適しています。データ範囲が長期間の場合は、日別パーティショニングを使用してテーブルのパーティション制限を維持できます。

大量のデータが短期間（通常は 6 か月未満のタイムスタンプ値）のデータ範囲にある場合は、時間別パーティショニングを選択します。時間別パーティショニングを使用すると、特定のパーティションのデータを追加、切り捨て、削除する場合などに時間単位でデータを処理できます。

テーブルの 1 日あたりのデータ量は比較的少ないものの、長い期間にわたる場合は、月別または年別パーティショニングを選択します。このパーティショニングのオプションは、ワークフローで長い期間（例: 500 日超）にわたる行の更新または追加を頻繁に行う必要がある場合にも推奨されます。月別または年別パーティショニングをタイムスタンプ、日付、日時パーティショニング列でクラスタリングと併用すると、これらの状況で最適なパフォーマンスを実現します。詳細と例については、クラスタリングを使用した時間単位のパーティショニングをご覧ください。

スキーマ定義を持つ空のパーティション分割テーブルの作成

スキーマ定義を持たない空のパーティション分割テーブルを作成することはできません。パーティションの作成に使用される列を識別するためには、スキーマが必要です。

スキーマ定義を持つ空のパーティション分割テーブルを作成すると、次のことができるようになります。

  • bq コマンドライン ツールを使用してインラインでスキーマを指定する。
  • bq コマンドライン ツールを使用して JSON スキーマ ファイルを指定する。
  • API の tables.insert メソッドを呼び出すときに、テーブル リソースでスキーマを指定する。

テーブル スキーマの指定方法については、スキーマの指定をご覧ください。

パーティション分割テーブルを作成すると、次の操作を行えるようになります。

  • データの読み込み。
  • クエリの結果の書き込み。
  • データのコピー。

スキーマ定義を持つ空のパーティション分割テーブルを作成するには:

Console

  1. [エクスプローラ] パネルでプロジェクトを展開し、データセットを選択します。

  2. 詳細パネルで [テーブルを作成] をクリックします。

  3. [テーブルの作成] パネルの [ソース] セクションで、次の操作を行います。

    • [テーブルの作成元] で [空のテーブル] を選択します。

  4. [送信先] で次の操作を行います。

    • [データセット名] で該当するデータセットを選択し、作成するテーブルの名前を [テーブル名] フィールドに入力します。
    • [テーブルタイプ] が [ネイティブ テーブル] に設定されていることを確認します。

  5. [スキーマ] セクションにスキーマ定義を入力します。次のいずれかの方法で、スキーマ情報を手動で入力します。

    • [テキストとして編集] を有効にし、テーブル スキーマを JSON 配列として入力します。

    • [フィールドを追加] をクリックしてスキーマの情報を入力します。

  6. [パーティションとクラスタの設定] で [パーティショニングなし] をクリックし、[フィールドにより分割] を選択して、DATE 列、TIMESTAMP 列、DATETIME 列を選択します。スキーマに DATE 列、TIMESTAMP 列、または DATETIME 列が含まれていない場合、このオプションは使用できません。

  7. （省略可）クエリを実行するパーティションを指定する WHERE 句の使用を必須にするには、[パーティショニング フィルタ] で [パーティション フィルタを要求] ボックスをクリックします。パーティション フィルタを必須にすると、コストが削減され、パフォーマンスが向上する場合があります。詳細については、パーティション分割テーブルのクエリをご覧ください。

  8. （省略可）[詳細オプション] をクリックします。Cloud Key Management Service 鍵を使用する場合は、[暗号化] で [顧客管理の暗号鍵] をクリックします。[Google が管理する鍵] の設定をそのままにすると、BigQuery は保存されているデータを暗号化します。

  9. [テーブルを作成] をクリックします。

SQL

データ定義言語（DDL）ステートメントでは、標準 SQL クエリ構文を使用してテーブルとビューの作成と変更ができます。

データ定義言語ステートメントの使用をご覧ください。

Cloud Console で DDL ステートメントを使用してパーティション分割テーブルを作成するには:

  1. Cloud Console で [BigQuery] ページを開きます。

    [BigQuery] ページに移動

  2. [クエリを新規作成] をクリックします。

  3. [クエリエディタ] テキスト領域に CREATE TABLE DDL ステートメントを入力します。

    次のクエリでは、transaction_date DATE 列に基づいた日別パーティションを使用して newtable というテーブルを作成します。パーティション有効期限は 3 日です。

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_date DATE)
 PARTITION BY
   transaction_date
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_date" )

    次のクエリでは同じテーブルが作成されますが、代わりに時間別のパーティションがあります。TIMESTAMP_TRUNC を使用して、時間マークのタイムスタンプが記述されることに注意してください。

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, HOUR)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

    次のクエリは同じテーブルを作成しますが、月別で分割されます。TIMESTAMP_TRUNC を使用して、月マークのタイムスタンプが記述されることに注意してください。

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, MONTH)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

    次のクエリは同じテーブルを作成しますが、年別で分割されます。TIMESTAMP_TRUNC を使用して、年マークのタイムスタンプが記述されることに注意してください。

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, YEAR)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

  4. [実行] をクリックします。クエリが完了すると、テーブルがそのデータセットのテーブルのリストに表示されます。

bq

bq mk コマンドを使用し、--table フラグ（または -t ショートカット）、--schema フラグ、--time_partitioning_field フラグを指定します。テーブルのスキーマ定義をインラインで、または JSON スキーマ ファイルを指定して提供できます。

オプション パラメータには --expiration、--description、--time_partitioning_expiration、--destination_kms_key、--require_partition_filter、--time_partitioning_type、--label があります。

デフォルト以外のプロジェクトでテーブルを作成する場合は、project_id:dataset の形式でプロジェクト ID をデータセットに追加します。

--destination_kms_key はここでは説明しません。このフラグの詳細については、Cloud Key Management Service 鍵によるデータの保護をご覧ください。

次のコマンドを入力して、スキーマ定義を持つ空のパーティション分割テーブルを作成します。

bq mk --table \
--expiration integer1 \
--schema schema \
--time_partitioning_field column \
--time_partitioning_type unit_time \
--time_partitioning_expiration integer2 \
--[no]require_partition_filter \
--description "description" \
--label key:value, key:value \
project_id:dataset.table

以下を置き換えます。

  • integer1 はテーブルのデフォルトの存続期間（秒）です。最小値は 3,600 秒（1 時間）です。現在の UTC 時間にこの整数値を足した値が有効期限になります。時間単位のパーティション分割テーブルの作成時に有効期限を設定した場合、データセットのデフォルトのテーブル有効期限の設定は無視されます。この値を設定すると、指定した時間が経過したときにテーブルとすべてのパーティションは削除されます。
  • schema は、field:data_type, field:data_type という形式のインライン スキーマ定義、またはローカルマシン上の JSON スキーマ ファイルへのパスです。
  • column は、パーティションの作成に使用される TIMESTAMP 列、DATETIME 列、または DATE 列の名前です。
  • unit_time は、目的の時間単位のパーティショニングの粒度に基づいて、DAY、HOUR、MONTH、YEAR のいずれかになります。time_partitioning_type が指定されていない場合、デフォルトは DAY です。
  • integer2 は、テーブルのパーティションのデフォルトの存続期間（秒）です。最小値はありません。パーティションの日付にこの整数値を足した値が有効期限になります。パーティションの有効期限はテーブルの有効期限とは無関係であり、それをオーバーライドしません。テーブルの有効期限より長いパーティション有効期限を設定すると、テーブルの有効期限が優先されます。
  • description はテーブルの説明です。引用符で囲みます。
  • key:value は、ラベルを表す Key-Value ペアです。カンマ区切りのリストを使用して複数のラベルを入力できます。
  • project_id は、プロジェクト ID です。
  • dataset は、プロジェクトのデータセットです。
  • table は、作成するパーティション分割テーブルの名前です。

bq mk コマンドの詳細については、bq mk をご覧ください。

bq コマンドライン ツールを使用してスキーマを指定する場合、RECORD（STRUCT）型および列の説明を含めることはできず、列のモードも指定できません。すべてのモードはデフォルトの NULLABLE になります。説明、モード、RECORD 型を含めるには、JSON スキーマ ファイルを指定します。

例:

次のコマンドを入力して、デフォルト プロジェクトの mydataset に mypartitionedtable という名前の時間別パーティション分割テーブルを作成します。パーティショニングの有効期限は 86,400 秒（1 日）に設定され、テーブルの有効期限は 2,592,000 秒（1 か月、つまり 30 日）に設定され、説明は This is my partitioned table に設定され、ラベルは organization:development に設定されます。このコマンドでは --table ではなく -t ショートカットを使用しています。

クエリを実行するパーティションを指定するために WHERE 句の使用を必須にする場合は、--require_partition_filter フラグを使用します。パーティション フィルタを必須にすると、コストが削減され、パフォーマンスが向上する場合があります。詳細については、パーティション分割テーブルのクエリをご覧ください。

スキーマはインラインで ts:TIMESTAMP,column1:STRING,column2:INTEGER,column4:STRING と指定されています。指定した TIMESTAMP フィールド ts は、データを時間で分割するために使用されます。時間別パーティショニングの場合は、DATE 列ではなく、TIMESTAMP 列または DATETIME 列を使用します。

bq mk -t \
--expiration 2592000 \
--schema 'ts:TIMESTAMP,column1:STRING,column2:INTEGER,column4:STRING' \
--time_partitioning_field ts \
--time_partitioning_type HOUR \
--time_partitioning_expiration 86400  \
--require_partition_filter \
--description "This is my partitioned table" \
--label org:dev \
mydataset.mypartitionedtable

デフォルト プロジェクトではない myotherproject 内に mypartitionedtable という名前の日別パーティション分割テーブルを作成するには、次のコマンドを入力します。パーティショニングの有効期限は 259,200 秒（3 日）に設定され、説明は This is my partitioned table に設定され、ラベルは organization:development に設定されます。このコマンドでは --table ではなく -t ショートカットを使用しています。このコマンドではテーブルの有効期限を指定していません。データセットにデフォルトのテーブル有効期限がある場合、それが適用されます。データセットにデフォルトのテーブル有効期限がない場合、テーブルは期限切れになりませんが、パーティションは 3 日で期限切れになります。

スキーマは、/tmp/myschema.json というローカル JSON ファイルで定義されています。このスキーマ定義には、データの日別パーティショニングに使用される ts という TIMESTAMP フィールドがあります。

bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_field ts \
--time_partitioning_type DAY \
--time_partitioning_expiration 86400  \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.mypartitionedtable

テーブルを作成した後に、bq コマンドライン ツールを使用して、パーティション分割テーブルのテーブルの有効期限、パーティションの有効期限、説明、ラベルを更新できます。

API

timePartitioning プロパティと schema プロパティを指定する定義済みのテーブル リソースを使用して tables.insert メソッドを呼び出します。

Go

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Go の手順に従って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。

import (
	"context"
	"fmt"
	"time"

	"cloud.google.com/go/bigquery"
)

// createTablePartitioned demonstrates creating a table and specifying a time partitioning configuration.
func createTablePartitioned(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "name", Type: bigquery.StringFieldType},
		{Name: "post_abbr", Type: bigquery.IntegerFieldType},
		{Name: "date", Type: bigquery.DateFieldType},
	}
	metadata := &bigquery.TableMetadata{
		TimePartitioning: &bigquery.TimePartitioning{
			Field:      "date",
			Expiration: 90 * 24 * time.Hour,
		},
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metadata); err != nil {
		return err
	}
	return nil
}

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の設定手順を実施してください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TimePartitioning;

public class CreatePartitionedTable {

  public static void runCreatePartitionedTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createPartitionedTable(datasetName, tableName);
  }

  public static void createPartitionedTable(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Schema schema =
          Schema.of(
              Field.of("stringField", StandardSQLTypeName.STRING),
              Field.of("booleanField", StandardSQLTypeName.BOOL),
              Field.of("dateField", StandardSQLTypeName.DATE));

      StandardTableDefinition tableDefinition =
          StandardTableDefinition.newBuilder()
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .build();
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Partitioned table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Partitioned table was not created. \n" + e.toString());
    }
  }
}

Node.js

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Node.js の設定手順を実施してください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function createTablePartitioned() {
  // Creates a new partitioned table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  const schema = 'Name:string, Post_Abbr:string, Date:date';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
    timePartitioning: {
      type: 'DAY',
      expirationMS: '7776000000',
      field: 'date',
    },
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);
  console.log(`Table ${table.id} created with partitioning: `);
  console.log(table.metadata.timePartitioning);
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の手順に従って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')

table_ref = dataset_ref.table("my_partitioned_table")
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
table = bigquery.Table(table_ref, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days

table = client.create_table(table)

print(
    "Created table {}, partitioned on column {}".format(
        table.table_id, table.time_partitioning.field
    )
)

クエリ結果からのパーティション分割テーブルの作成

クエリ結果からパーティション分割テーブルを作成するには、結果を新しい宛先テーブルに書き込みます。分割テーブルまたは分割されていないテーブルをクエリすることによって、分割テーブルを作成できます。クエリ結果を使用して、既存の標準テーブルをパーティション分割テーブルに変更することはできません。

クエリ結果から分割テーブルを作成する場合、標準 SQL を使用する必要があります。現時点では、分割テーブルをクエリする場合や、クエリ結果を分割テーブルに書き込む場合に、レガシー SQL はサポートされていません。

パーティション デコレータを使用すると、クエリ結果を特定のパーティションに書き込むことができます。たとえば、2016 年 5 月 1 日のパーティションに結果を書き込むには、次のパーティション デコレータを使用します。

table_name$20160501

パーティション デコレータを使用してクエリ結果を特定のパーティションに書き込む場合、パーティションに書き込まれるデータは、テーブルのパーティショニング スキームに準拠している必要があります。パーティションに書き込まれるすべての行は、パーティションの日付内に収まる値を持つ必要があります。

例:

次のクエリは、2018 年 2 月 1 日以降のデータを取得し、テーブル mytable の $20180201 パーティションにデータを書き込みます。 テーブルには、TS という名前の TIMESTAMP 列と a という名前の INT64 列の 2 つの列があります。

bq query \
--nouse_legacy_sql  \
--destination_table=mytable$20180201 \
'SELECT
   TIMESTAMP("2018-02-01") AS TS,
   2 AS a'

次のクエリは、2018 年 1 月 31 日以降のデータを取得し、mytable の $20180201 パーティションにデータを書き込みます。書き込もうとしているデータ値がパーティションの日付内に収まらないため、このクエリは失敗します。

bq query \
--nouse_legacy_sql  \
--destination_table=T$20180201 \
'SELECT
   TIMESTAMP("2018-01-31") as TS,
   2 as a'

パーティション分割テーブル内のデータに対する追加または修正（置換）の詳細については、パーティション分割テーブルデータに対する追加および上書きをご覧ください。パーティション分割テーブルに対するクエリの詳細については、パーティション分割テーブルのクエリをご覧ください。

クエリ結果からのパーティション分割テーブルの作成

クエリ結果からパーティション分割テーブルを作成するには:

Console

Cloud Console を使用してデータのクエリを実行する場合、宛先テーブルのパーティショニング オプションを指定できません。

bq

bq query コマンドを入力します。クエリ結果に基づいて永続テーブルを作成することを --destination_table フラグによって指定し、作成するテーブルがパーティション分割テーブルであることを --time_partitioning_field フラグによって指定します。

標準 SQL 構文を使用するには、use_legacy_sql=false フラグを指定します。デフォルト プロジェクト以外のプロジェクトにあるテーブルにクエリ結果を書き込むには、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

（省略可）--location フラグを指定して、その値をロケーションに設定します。

次のコマンドを入力して、クエリ結果から宛先の新しいパーティション分割テーブルを作成します。

bq --location=location query \
--destination_table project_id:dataset.table \
--time_partitioning_field column \
--time_partitioning_type unit_time
--use_legacy_sql=false \
'query'

以下を置き換えます。

  • location はロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
  • project_id は、プロジェクト ID です。
  • dataset は、新しいパーティション分割テーブルを配置するデータセットの名前です。
  • table は、クエリ結果を使用して作成するパーティション分割テーブルの名前です。
  • column は、パーティションの作成に使用される TIMESTAMP 列または DATE 列の名前です。
  • unit_time は、目的の時間単位のパーティショニングの粒度に基づいて、DAY、HOUR、MONTH、YEAR のいずれかになります。time_partitioning_type が指定されていない場合のデフォルトは DAY です。
  • query は標準 SQL 構文のクエリです。レガシー SQL を使用して、パーティション分割テーブルをクエリすることや、クエリ結果をパーティション分割テーブルに書き込むことはできません。

例:

次のコマンドを入力すると、mydataset 内の mypartitionedtable というパーティション分割テーブルにクエリ結果が書き込まれます。mydataset はデフォルト プロジェクトにあります。このクエリは、パーティション分割されていないテーブルである NHTSA Traffic Fatality 一般公開データセットからデータを取得します。このテーブルの timestamp_of_crash TIMESTAMP 列は、パーティション作成に使用されます。

bq query \
--destination_table mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

次のコマンドを入力すると、mydataset 内の mypartitionedtable というパーティション分割テーブルにクエリ結果が書き込まれます。mydataset はデフォルト プロジェクトではなく myotherproject にあります。このクエリは、パーティション分割されていないテーブルである NHTSA Traffic Fatality 一般公開データセットからデータを取得します。このテーブルの timestamp_of_crash TIMESTAMP 列は、パーティション作成に使用されます。

bq query \
--destination_table myotherproject:mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

API

クエリの結果を永続的なパーティション分割テーブルに保存するには、jobs.insert メソッドを呼び出し、query ジョブを構成して destinationTable プロパティと timePartitioning プロパティの値を含めます。

ジョブリソースの jobReference セクションにある location プロパティでロケーションを指定します。

データを読み込むときのパーティション分割テーブルの作成

新しいテーブルにデータを読み込むときにパーティショニング オプションを指定することによって、パーティション分割テーブルを作成できます。データを読み込む前に空のパーティション分割テーブルを作成しておく必要はありません。分割テーブルを作成すると同時にデータを読み込むことができます。

BigQuery にデータを読み込むときに、テーブル スキーマを指定できます。また、サポートされているデータ形式であれば、スキーマの自動検出を使用できます。

パーティション デコレータを使用してデータを特定のパーティションに読み込むことができます。たとえば、2016 年 5 月 1 日に生成されたすべてのデータを 20160501 パーティションに読み込むには、次のパーティション デコレータを使用します。

table_name$20160501

パーティション デコレータを使用してデータを特定のパーティションに読み込む場合、パーティションに読み込まれるデータは、テーブルのパーティショニング スキームに準拠している必要があります。パーティションに書き込まれるすべての行は、パーティションの日付内に収まる値を持つ必要があります。

データの読み込みの詳細については、BigQuery へのデータの読み込みの概要をご覧ください。

クラスタリングを使用した時間単位のパーティショニング

時間単位のパーティショニングは、クラスタリングで使用できます。クラスタリングを使用した時間単位のパーティション分割テーブルでは、まずパーティショニング列の時間単位の境界（日、時間、月、年）を基準にデータが分割されます。その後、各パーティションの境界内でデータが分割され、さらにクラスタリング列でクラスタ化されます。

例として、このコマンドは日別パーティション分割列とクラスタを含むテーブルを作成します。

    bq mk --time_partitioning_type=DAY \
    --time_partitioning_field=ts_column \
    --clustering_fields=column1,column2 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

テーブルの形式を取得すると、日別のタイムスタンプ パーティショニングとクラスタリングの両方が有効になります。

    bq show --format=prettyjson mydataset.mytable2
    ...
      "clustering": {
        "fields": [
          "column1",
          "column2"
        ]
      },
    ...
      "timePartitioning": {
        "field": "ts_column",
        "type": "DAY"
      },
    ...

テーブルあたりのパーティション数の上限を超えて実行している場合、または多数のパーティション間で散在するデータが過少であることから頻繁に変化している場合は、代わりに時間間隔の長いパーティションを同じパーティショニング列でのクラスタリングと併用することを検討してください。これは、パーティション分割テーブルを使用してパーティションの制限内に留まる場合に推奨される方法です。

たとえば、このコマンドは、日別パーティション分割テーブルとクラスタ化テーブルを同じ列に作成します。

    bq mk --time_partitioning_type=DAY \
    --time_partitioning_field=ts_column \
    --clustering_fields=ts_column,column1 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

時間間隔のより長いパーティショニングを使用した、上のテーブルの別の例を示します。

    bq mk --time_partitioning_type=MONTH \
    --time_partitioning_field=ts_column \
    --clustering_fields=ts_column,column1 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

パーティション分割テーブルへのアクセスの制御

テーブルとビューへのアクセスを構成するには、エンティティに次のレベルで IAM ロールを付与します。以下に、各レベルを許可されるリソースの範囲が大きい順に一覧で示します。

  • Google Cloud リソース階層の上位レベル（プロジェクト、フォルダ、組織レベルなど）
  • データセット レベル
  • テーブル / ビューレベル

IAM で保護されているリソースを使用したアクセスは追加型です。たとえば、エンティティにプロジェクトなどの上位レベルのアクセス権がない場合は、データセット レベルでアクセス権を付与すると、データセット内のテーブルとビューにアクセスできます。同様に、エンティティに高レベルまたはデータセット レベルでのアクセス権がない場合は、テーブルまたはビューレベルでエンティティにアクセス権を付与できます。

プロジェクト、フォルダ、組織レベルなど、Google Cloud リソース階層の上位レベルで IAM ロールを付与すると、エンティティは幅広いリソースのセットにアクセスできるようになります。たとえば、プロジェクト レベルでエンティティにロールを付与すると、そのエンティティには、プロジェクトに含まれるすべてのデータセットに適用される権限が付与されます。

データセット レベルでロールを付与すると、そのエンティティが上位レベルでアクセスできない場合でも、そのデータセットのテーブルとビューで実行できるオペレーションが指定されます。データセット レベルのアクセス制御を構成する方法については、データセットへのアクセスの制御をご覧ください。

テーブルまたはビューレベルでロールを付与すると、エンティティに上位レベルのアクセスがない場合でも、特定のテーブルやビューに対してエンティティが実行できるオペレーションが特定されます。テーブルレベルのアクセス制御の構成については、テーブルおよびビューへのアクセスの制御をご覧ください。

また、IAM カスタムロールを作成することもできます。カスタムロールを作成する場合、エンティティに実行を許可する特定のオペレーションによって、付与する権限は異なります。

IAM で保護されているリソースに「拒否」権限を設定することはできません。

ロールと権限の詳細については、以下をご覧ください。

分割テーブルの使用

パーティション分割テーブルに関する情報の取得

テーブルに関する情報は、次の方法で取得できます。

  • Cloud Console を使用する。
  • bq コマンドライン ツールで bq show コマンドを使用する。
  • tables.get API メソッドを呼び出す。
  • クライアント ライブラリを使用する。

必要な権限

テーブルに関する情報を取得するには、少なくとも bigquery.tables.get 権限が付与されている必要があります。次の事前定義済みの IAM ロールには bigquery.tables.get 権限が含まれています。

  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

また、bigquery.datasets.create 権限を持つユーザーがデータセットを作成すると、そのデータセットに対する bigquery.dataOwner アクセス権がユーザーに付与されます。bigquery.dataOwner アクセス権により、ユーザーはテーブルのメタデータを取得できます。

BigQuery での IAM ロールと権限の詳細については、アクセス制御をご覧ください。

パーティション分割テーブルの情報の取得

パーティション分割テーブルに関する情報を表示するには:

Console

  1. Cloud Console で [BigQuery] ページを開きます。

    [BigQuery] ページに移動

  2. [エクスプローラ] パネルで、プロジェクトとデータセットを展開し、テーブルを選択します。

  3. 詳細パネルで、[詳細] をクリックします。このタブには、テーブルの説明とテーブルの情報が表示されます。

    テーブルの詳細

  4. [スキーマ] タブをクリックすると、テーブルのスキーマ定義が表示されます。パーティション分割テーブルには _PARTITIONTIME 疑似列が含まれていないことがわかります。

bq

すべてのテーブル情報を表示するには、bq show コマンドを発行します。テーブルのスキーマ情報のみを表示するには、--schema フラグを使用します。--format フラグを使用して出力を制御できます。

デフォルト以外のプロジェクトにあるテーブルの情報を取得する場合は、project_id:dataset の形式でプロジェクト ID をデータセットに追加します。

bq show --schema --format=prettyjson project_id:dataset.table

以下を置き換えます。

  • project_id は、プロジェクト ID です。
  • dataset は、データセットの名前です。
  • table は、テーブルの名前です。

例:

次のコマンドを入力して、mydataset にある mytable に関するすべての情報を表示します。mydataset はデフォルト プロジェクトにあります。

bq show --format=prettyjson mydataset.mytable

次のコマンドを入力して、mydataset にある mytable に関するすべての情報を表示します。mydataset はデフォルト プロジェクトではなく myotherproject にあります。

bq show --format=prettyjson myotherproject:mydataset.mytable

出力は次のようになります。

{
  "creationTime": "1563236533535",
  "description": "This is my partitioned table",
  "etag": "/ABcDEo7f8GHijKL2mnOpQr==",
  "expirationTime": "1565828533000",
  "id": "myproject:mydataset.mypartitionedtable",
  "kind": "bigquery#table",
  "labels": {
    "org": "dev"
  },
  "lastModifiedTime": "1563236533576",
  "location": "US",
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "requirePartitionFilter": true,
  "schema": {
    "fields": [
      {
        "name": "ts",
        "type": "TIMESTAMP"
      },
      {
        "name": "column1",
        "type": "STRING"
      },
      {
        "name": "column2",
        "type": "INTEGER"
      },
      {
        "name": "column3",
        "type": "STRING"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/mypartitionedtable",
  "tableReference": {
    "datasetId": "mydataset",
    "projectId": "myproject",
    "tableId": "mypartitionedtable"
  },
  "timePartitioning": {
    "expirationMs": "86400000",
    "field": "ts",
    "requirePartitionFilter": true,
    "type": "DAY"
  },
  "type": "TABLE"
}

mydataset 内の mytable に関するスキーマ情報のみを表示するには、次のコマンドを入力します。mydataset はデフォルト プロジェクトではなく myotherproject にあります。

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

出力は次のようになります。

[
  {
    "name": "ts",
    "type": "TIMESTAMP"
  },
  {
    "name": "column1",
    "type": "STRING"
  },
  {
    "name": "column2",
    "type": "INTEGER"
  },
  {
    "name": "column3",
    "type": "STRING"
  }
]

API

bigquery.tables.get メソッドを呼び出し、関連パラメータを指定します。

データセット内のパーティション分割テーブルの一覧表示

データセット内のテーブル（パーティション分割テーブルを含む）を一覧表示するには、次の方法を使用します。

  • Cloud Console を使用する。
  • bq コマンドライン ツールで bq ls コマンドを使用する。
  • tables.list API メソッドを呼び出す。
  • クライアント ライブラリを使用する。

必要な権限

データセットに含まれるテーブルを一覧表示するには、少なくとも bigquery.tables.list 権限が付与されている必要があります。次の IAM 事前定義ロールには bigquery.tables.list 権限が含まれています。

  • bigquery.user
  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

BigQuery での IAM ロールと権限の詳細については、アクセス制御をご覧ください。

パーティション分割テーブルの一覧表示

データセット内のテーブル（パーティション分割テーブルを含む）を一覧表示するには、次の手順に従います。

Console

  1. Cloud Console で [BigQuery] ページを開きます。

    [BigQuery] ページに移動

  2. [エクスプローラ] パネルでプロジェクトを展開し、データセットを選択します。

  3. リストをスクロールして、データセット内のテーブルを表示します。テーブル、パーティション分割テーブル、モデル、ビューは、別々のアイコンで区別できます。

bq

bq ls コマンドを発行します。--format フラグを使用して出力を制御できます。デフォルト プロジェクト以外のプロジェクトにあるテーブルを一覧表示する場合は、project_id:dataset の形式でプロジェクト ID をデータセットに追加します。

bq ls --format=pretty project_id:dataset

以下を置き換えます。

  • project_id は、プロジェクト ID です。
  • dataset は、データセットの名前です。

このコマンドを実行すると、Type フィールドに TABLE または VIEW が表示されます。パーティション分割テーブルの場合、Time Partitioning フィールドに DAY とパーティションの作成に使用された列が表示されます。また、有効期限が指定されている場合は、パーティションの有効期限がミリ秒単位で表示されます。

例:

+-------------------------+-------+----------------------+---------------------------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning                                 |
+-------------------------+-------+----------------------+---------------------------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (field: source_date, expirationMs: 86400000) |
| myview                  | VIEW  |                      |                                                   |
+-------------------------+-------+----------------------+---------------------------------------------------+

例:

次のコマンドを入力すると、デフォルト プロジェクトの mydataset データセット内のテーブルが一覧表示されます。

bq ls --format=pretty mydataset

次のコマンドを入力すると、myotherproject にある mydataset データセット内のテーブルが一覧表示されます。

bq ls --format=pretty myotherproject:mydataset

API

API を使用してテーブルを一覧表示するには、tables.list メソッドを呼び出します。

パーティション分割テーブル内のパーティションの一覧表示

パーティション分割テーブル内のパーティションを一覧表示するには、レガシー SQL を使用して __PARTITIONS_SUMMARY__ メタテーブルをクエリします。

クエリを実行するには、Cloud Console を使用するか、bq query コマンドを使用します。または、jobs.insert メソッドを呼び出して query ジョブを構成します。

必要な権限

__PARTITIONS_SUMMARY__ メタテーブルを使用するクエリジョブを実行するには、少なくとも bigquery.jobs.create 権限が付与されている必要があります。次の事前定義済みの IAM ロールには bigquery.jobs.create 権限が含まれています。

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

bigquery.tables.getData 権限も必要です。次の IAM 事前定義ロールには bigquery.tables.getData 権限が含まれています。

  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

BigQuery での IAM ロールの詳細については、アクセス制御をご覧ください。

パーティション分割テーブル内のパーティションの一覧表示

レガシー SQL を使用して、パーティション分割テーブル内のパーティションを一覧表示できます。パーティション分割テーブル内のパーティションを一覧表示するには、次のようにします。

Console

  1. Cloud Console で [BigQuery] ページを開きます。

    [BigQuery] ページに移動

  2. [クエリを新規作成] ボタンをクリックします。

  3. __PARTITIONS_SUMMARY__ メタテーブルをクエリするには、次のテキストを [クエリエディタ] に入力します。

    #legacySQL
SELECT
  partition_id
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

    以下を置き換えます。

    • dataset は、テーブルが含まれているデータセットです。
    • table はテーブルの名前です。

  4. [実行] をクリックします。

bq

bq query コマンドを使用して次のクエリを入力します。

bq --location=location query \
--use_legacy_sql=true \
'SELECT
  partition_id
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]'

以下を置き換えます。

  • location はロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
  • dataset は、テーブルが含まれているデータセットです。
  • table は、テーブルの名前です。

API

jobs.insert メソッドを呼び出し、テーブルの __PARTITIONS_SUMMARY__ メタテーブルをクエリする query ジョブを構成します。

メタテーブルを使用したパーティション分割テーブルのメタデータの取得

メタテーブルと呼ばれる特殊なテーブルを使用して、パーティション分割テーブルに関する情報を取得できます。メタテーブルには、データセット内のテーブルとビューのリストなどのメタデータが格納されます。メタテーブルは読み取り専用です。

現在、INFORMATION_SCHEMA サービスを使用してパーティション分割テーブルのメタデータを取得することはできません。

メタテーブルを使用したパーティション メタデータの取得

__PARTITIONS_SUMMARY__ メタテーブルは、その内容が時間パーティション分割テーブル内のパーティションに関するメタデータを表す特殊なテーブルです。__PARTITIONS_SUMMARY__ メタテーブルは読み取り専用です。

時間パーティション分割テーブル内のパーティションに関するメタデータにアクセスするには、クエリの SELECT ステートメントで __PARTITIONS_SUMMARY__ メタテーブルを使用します。次の方法でクエリを実行できます。

  • Cloud Console を使用する
  • bq コマンドライン ツールの bq query コマンドを使用する
  • jobs.insert API メソッドを呼び出して query ジョブを構成する
  • クライアント ライブラリを使用する

現時点では、標準 SQL ではパーティション デコレータ セパレータ（$）がサポートされていないため、標準 SQL では __PARTITIONS_SUMMARY__ をクエリできません。__PARTITIONS_SUMMARY__ メタテーブルを使用するレガシー SQL クエリは、次のようになります。

#legacySQL
SELECT
  column
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

ここで

  • dataset は、データセットの名前です。
  • table は、時間パーティション分割テーブルの名前です。
  • column は、次のいずれかです。
値 説明
project_id プロジェクトの名前。
dataset_id データセットの名前。
table_id 時間パーティション分割テーブルの名前。
partition_id パーティションの名前（日付）。
creation_time パーティションが作成された日時（UTC 1970 年 1 月 1 日からのミリ秒数）。
last_modified_time パーティションが最後に変更された日時（UTC 1970 年 1 月 1 日からのミリ秒数）。

パーティション メタテーブルの権限

__PARTITIONS_SUMMARY__ メタテーブルを使用するクエリジョブを実行するには、少なくとも bigquery.jobs.create 権限が付与されている必要があります。次の事前定義済みの IAM ロールには bigquery.jobs.create 権限が含まれています。

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

bigquery.tables.getData 権限も必要です。次の IAM 事前定義ロールには bigquery.tables.getData 権限が含まれています。

  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

BigQuery での IAM ロールの詳細については、アクセス制御をご覧ください。

パーティション メタテーブルの例

次のクエリは、mydataset.mytable という名前の時間パーティション分割テーブルのすべてのパーティション メタデータを取得します。

Console 

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

出力は次のようになります。

+----------------+------------+----------------+--------------+---------------+--------------------+
|   project_id   | dataset_id |    table_id    | partition_id | creation_time | last_modified_time |
+----------------+------------+----------------+--------------+---------------+--------------------+
| myproject      | mydataset  | mytable        | 20160314     | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20160315     | 1517190224120 | 1517190224997      |
+----------------+------------+----------------+--------------+---------------+--------------------+

次のクエリは、mydataset.mytable のパーティションが最後に変更された時間を一覧表示します。

Console 

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

出力は次のようになります。

+--------------+--------------------+
| partition_id | last_modified_time |
+--------------+--------------------+
| 20160102     |      1471632556179 |
| 20160101     |      1471632538142 |
| 20160103     |      1471632570463 |
+--------------+--------------------+

人が読める形式で last_modified_time フィールドを表示するには、FORMAT_UTC_USEC 関数を使用します。例:

Console 

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

出力は次のようになります。

+--------------+----------------------------+
| partition_id |       last_modified        |
+--------------+----------------------------+
| 20160103     | 2016-08-19 18:49:30.463000 |
| 20160102     | 2016-08-19 18:49:16.179000 |
| 20160101     | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

次のステップ