BigQueryのテーブルの種類について

BigQuery のテーブルの種類について

BigQuery で利用できるテーブルの種類ついてまとめました。詳細や最新情報について知りたい方は、ぜひ公式のドキュメントも参照してください。

• テーブルの概要

このドキュメントでは、テーブルの作成方法についてもご紹介します。なお作成には SQL を使用します。

テーブルの種類

BigQuery のテーブルは主に BigQuery ストレージでサポートされるネイティブテーブル、BigQuery の外部にあるストレージでサポートされる外部テーブル、SQL クエリによって定義される実態のないビューの 3 種類に分類されます。下記はそれぞれの分類に属するテーブルをまとめた表になります。

• ネイティブテーブル
  • 標準テーブル
  • 特殊なテーブル
    • クラスタ化テーブル
    • パーティション分割テーブル
  • マテリアライズドビュー
• 外部テーブル
• ビュー

ご覧のように BigQuery には様々な種類のテーブルが用意されており、用途によって使い分けることが可能です。本稿ではこの中の標準テーブル・クラスタ化テーブル・パーティション分割テーブル・マテリアライズドビュー・ビューをピックアップして、その概要やテーブル作成方法についてまとめていきたいと思います。

テーブル作成の前に...

テーブルの作成にはプロジェクト及び BiqQuery データセットの作成が必須です。次の手順でそれぞれを作成してください。

1. プロジェクトの作成と管理
2. データセットの作成

またテーブルの作成には次の IAM 権限が必要となります。必要に応じて設定してください。

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.jobs.create

標準テーブル

概要

もっとも標準的でイメージしやすい、表形式のテーブルです。標準テーブルでは、アクセス制御やテーブルに関する情報やメタデータを取得することができます。

テーブルの命名に関するルール

• 1024 文字以内
• 文字、マーク、数字、アンダースコア、ハイフン、スペースなどを含む文字で作成することができる
  • table 01、ग्राहक、00_お客様、étudiant-01は全て有効なテーブル名

実際に作ってみる

標準テーブルを作成するためには、CREATE TABLEステートメントを使用します。イメージは次の SQL になります。

CREATE TABLE mydataset.newtable (id INT64, name STRING)

それでは上記のクエリを BigQuery で実行してみましょう。データセット名およびテーブル名の部分は適宜変更してください。

BigQuery のエディターに SQL を貼り付けて実行します。

すると指定したデータセットに新たにテーブルが追加されていることが確認できます。これで標準テーブルを作成することができました。

標準テーブルの作成の詳細についてはテーブルの作成と使用をご確認ください。

参考

• テーブルの作成と使用

パーティション分割テーブル

概要

パーティション分割テーブルはデータをパーティションと呼ばれる小さなセグメント（まとまり）で分割した特殊なテーブルです。大きなテーブルを小さなパーティションに分割するこで、クエリのパフォーマンスの向上やクエリで読み取られるバイト数を減らすことによってコストの削減につながるなど、大きなメリットがあります。分割の方法については次の 3 種類があります。

文章だけではイメージしづらいため分割方法についてもう少し見てみましょう。

1. 時間単位列パーティショニングの例

例えばパーティショニングの単位を月単位に設定し、ある DATETIME 型の列でテーブルを分割するとします。そしてテーブルに次のような値を挿入すると、行は各パーティションに書き込まれます。

2022-01-01と2022-01-31は同じ月であるため202201というパーティションへ、2022-03-01は別の月であるため202203という別のパーティションへ書き込まれているのが確認できます。また、値が NULL の場合は__NULL__というパーティションに、さらに1960-01-01以前、または2159-12-31以降の値が含まれる場合は__UNPARTITIONED__という特殊なパーティションに書き込まれます。

2. 取り込み時間パーティショニングの例

取り込み時間パーティション分割テーブルには、_PARTITIONTIMEという名前の疑似列があります。この列の値は、パーティションの境界（時間単位や日単位など）で切り捨てられた各行の取り込み時間です。例として時間単位のパーティショニングで、取り込み時間パーティション分割テーブルを作成したとします。以下をご覧ください。

この例では時間単位のパーティショニングを使用しているため、_PARTITIONTIMEの値は 1 時間の境界で切り捨てられます。よって取り込み時間が2022-01-01 12:34:56であれば_PARTITIONTIMEは2021-01-01 12:00:00に、2022-01-01 12:45:00も同じく2021-01-01 12:00:00に、一方で取り込み時間が2022-01-01 13:30:00のもの_PARTITIONTIMEは2021-01-01 13:00:00となります。 こうしてデータが整理され分割されます。

3. 整数範囲パーティショニングの例

例えば次のような整数範囲パーティショニングの設定を行います。ageという年齢を表す整数型のカラムをパーティショニング列に指定して、範囲を0~100、間隔を10として設定します。

上記の設定によって、テーブルはage列において10の間隔の範囲にパーティション分割が行われます。つまり0~9の値が 1 つ目のパーティションに、10~19までが 2 つ目のパーティションに...といった具合で分割されます。また設定範囲外の値は__UNPARTITIONED__というパーティションに、値が NULL の場合は__NULL__という特殊なパーティションに入ります。以下はパーティション分割の例です。

以上が各分割方法のイメージになります。その他のパーティション分割テーブルの詳細についてはパーティション分割テーブルの概要をご確認ください。

制限

• レガシー SQL を使用して、パーティション分割テーブルをクエリすることや、クエリ結果をパーティション分割テーブルに書き込むことができない。
• 時間単位列パーティショニングにおいて、パーティショニングに用いる列は、スカラーDATE列、TIMESTAMP列、DATETIME列のいずれかであることが必要。
• 整数範囲パーティショニングにおいて、分割する列はINTEGER列である必要がある。また列のモードはREQUIREDまたはNULLABLEはできるが、配列ベースのREPEATEDにすることはできない。

その他の制限についてはこちらをご確認ください。

テーブルの命名に関するルール

標準テーブルと同じです。

実際に作ってみる

時間単位列パーティション分割テーブルを作成するには、PARTITION BY句でカラムを指定して、CREATE TABLEステートメントを実行します。イメージは次の SQL になります。

CREATE TABLE
  mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
  transaction_date

それでは上記のクエリを BigQuery で実行してみましょう。データセット名およびテーブル名の部分は適宜変更してください。

BigQuery のエディターに SQL を貼り付けて実行します。

作成したテーブルを選択し、「DETAILS」を開いてください。Table TypeがPartitionedとなっていることが確認できます。またPartitioned on fieldがtransaction_dateになっており、指定した列でパーティションが作成されていることが確認できます。これでパーティション分割テーブルを作成することができました。

Partitioned Byの項目がDAYとなっていることから、デフォルトのパーティショニングの単位は日単位になっているようです。パーティショニング単位を指定するためには、PARTITION BY句にDATE_TRUNC関数を含めることで可能となります。これは他の分割テーブルの作成の際にも有効です。次の SQL では、DATE_TRUNC関数を使用して、パーティショニング単位を月単位に設定した例となります。

CREATE TABLE
  mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
  DATE_TRUNC(transaction_date, MONTH)

上記のクエリを BigQuery で実行すると、次のようにPartitioned Byの項目がMONTHとなっていることが確認できます。

取り込み時間パーティション分割テーブルを作成するためには、PARTITION BY句で_PARTITIONDATEを指定します。次の SQL がその例になります。

CREATE TABLE
  mydataset.newtable (transaction_id INT64)
PARTITION BY
  _PARTITIONDATE

上記の SQL を実行するとPartitioned on fieldが_PARTITIONDATEとなっているテーブルが追加されたことが確認できます。

整数範囲パーティション分割テーブルを作成するためには、PARTITION BY句でRANGE_BUCKET関数を用いることによって行うことができます。

CREATE TABLE mydataset.newtable (age INT64)
PARTITION BY
  RANGE_BUCKET(age, GENERATE_ARRAY(0, 100, 10))

上記の SQL を実行すると設定が反映されていることが確認できます。

さらなる詳細はパーティション分割テーブルの作成 をご確認ください。

参考

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

クラスタ化テーブル

概要

BiqQuery でクラスタ化テーブルを作成すると、テーブルの中の指定したカラムの内容に基づいてデータを自動的にクラスタ化します。つまり指定したカラムでソートした状態でテーブルを構築します。AWS Redshift や AWS DynamoDB の Sort Key のようなイメージになります。また複数のカラムを指定すると、指定したカラムの順序でデータの並べ替えが行われます。指定したカラムであらかじめデータの並び替えをしておくことによって効率よく SQL が実行できるため、特にフィルタ句を使用するクエリやデータを集計するクエリなど、特定のタイプのクエリのパフォーマンスの向上が期待されます。

詳細についてはクラスタ化テーブルの概要をご確認ください。

制限

BigQuery のクラスタ化テーブルの主な制限として次の事項があります。

• クラスタリング列は最大で 4 つまで指定できます。
• クラスタリング列のデータ型はDATA、BOOL、GEOGRAPHY、INT64、NUMERIC、BIGNUMERIC、STRING、TIMESTAMP、DATETIMEのいずれかでなければなりません。
• クラスタリングにSTRING型の列を使用する場合、はじめの 1024 文字を使ってクラスタリングを行います。

その他制限事項や詳細についてはこちらをご確認ください。

テーブルの命名に関するルール

標準テーブルと同じです。

実際に作ってみる

CLUSTER BY句でクラスタリング列を指定して、CREATE TABLEステートメントを使用することで、クラスタ化テーブルを作成することができます。またPARTITION BY句でパーティション列を指定することで、パーティション分割テーブルとクラスター化テーブルの両方の機能を使用することが可能です。次の SQL はその例になります。

CREATE TABLE
  `mydataset.newtable`(id,INT64, column1 INT64, column2 INT64, column3 INT64)
CLUSTER BY
  column1,
  column2,
  column3

それでは上記のクエリを BigQuery で実行してみましょう。データセット名およびテーブル名の部分は適宜変更してください。

BigQuery のエディターに SQL を貼り付けて実行します。

作成したテーブルを選択し、「DETAILS」を開いてください。「Clusterd by」という項目に、指定したカラムが表示されていることが確認できます。これでクラスタ化テーブルを作成することができました。

他にもコンソール、bq コマンド、API などで作成する方法があります。詳細はクラスタ化テーブルの作成と使用をご確認ください。

参考

• クラスタ化テーブルの概要
• クラスタ化テーブルの作成と使用
• クラスタ化テーブルのクエリ
• BigQuery テーブルチューニングに効果的なクラスタリングが、既存テーブルにも追加できるようになりました！

ビューテーブル

概要

ビューは SQL クエリによって定義される仮想テーブルのことです。実際にテーブルを作成するわけではないためテーブルのように実態はなく、仮想的なテーブル形式のデータを作り出せる機能です。ビューに対してあたかも普通のテーブルと同じようにクエリを投げると、その裏側でビューの元となるベーステーブルに対してクエリが実行され、その結果をテーブルと同じような形で取得することができます。

詳細はビューの概要をご確認ください。

制限

いくつかの制限があります。詳しくは公式ドキュメントを参照して欲しいのですが、以下の 3 点が大きな制限です。

• ビューは ReadOnly です。
• 標準 SQL とレガシー SQL の併用は不可であり、標準 SQL からレガシー SQL で書かれたビューを参照することはできません。
• ビューからデータをエクスポートする BigQuery ジョブは実行できません。

その他の制限についてはこちらをご確認ください。

テーブルの命名に関するルール

標準テーブルと同じです。

実際に作ってみる

CREATE VIEWステートメントを使用してビューを作成することができます。CREATE VIEWステートメントでビューの作成を宣言し、SELECTステートメントで目的のデータ取得のためのクエリを記述します。 次がビュー作成のための SQL の例になります。この SQL では公開データセットからSELECTしています。公開データセットの解説についてはBigQuery で扱える一般公開データセットまとめをご覧ください。

CREATE VIEW mydataset.view_name(name, number) AS
  SELECT
    name,
    number
  FROM
    bigquery-public-data.usa_names.usa_1910_current
  WHERE
    gender = "M"
  ORDER BY
    number DESC

それでは上記のクエリを BigQuery で実行してみましょう。データセット名およびテーブル名の部分は適宜変更してください。

BigQuery のエディターに SQL を貼り付けて実行します。

作成したビューを選択し、「DETAILS」を開いてください。作成したビューの詳細やクエリの定義を確認することができます。これでビューを作成することができました。

ビューの作成の詳細に関してはビューの作成をご確認ください。

参考

• ビューの概要
• ビューの作成

マテリアライズドビュー

概要

マテリアライズドビューはビューと同じようにクエリによって定義されます。しかしマテリアライズドビューは通常のビューとは異なりテーブルのように実体をもちます。つまりクエリの実行結果がテーブルとして保存されているため、通常のビューのように参照するたびにベーステーブルに対してクエリが実行されるということがなく、低コストかつ高いパフォーマンスが期待されます。もちろん実体を持つことでその分のストレージ容量が必要であり、保存費用が発生します。マテリアライズドビューはベーステーブルが更新されても、その全ての更新分が自動的にマテリアライズドビューに反映されるため、常に最新の状態に保たれます。そのためユーザーはメンテナンスに気を遣う必要はありません。

詳細についてはマテリアライズドビューの概要をご確認ください。

制限

• マテリアライズドビューは ReadOnly です。マテリアライズドビューへのデータの書き込み、読み込み、それのエクスポートなど、データを直接操作することはできません。
• 各ベーステーブルは、同じデータセットから最大 20 個のマテリアライズドビュー、同じプロジェクトから最大 100 個のマテリアライズドビュー、組織全体から最大 500 個のマテリアライズドビューを参照することができます。
• マテリアライズドビューには標準 SQL 言語のみが使用可能です。

その他の制限についてはこちらをご確認ください。

テーブルの命名に関するルール

標準テーブルと同じです。

実際に使ってみる

CREATE MATERIALIZED VIEWステートメントを使用して、マテリアライズドビューを作成することができます。ビューの作成とほとんど変わりません。次がマテリアライズドビュー作成のための SQL の例になります

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table AS
  SELECT product_id, SUM(clicks) AS sum_clicks
  FROM project-id.my_dataset.my_base_table
  GROUP BY 1

マテリアライズドビューの作成の詳細に関してはマテリアライズドビューの作成 をご確認ください。

参考

• マテリアライズドビューの概要
• マテリアライズドビューの作成