結論としてはしょうもない話なのだが、正解に辿り着くまでにまぁまぁ時間を要したので、
同じ問題に直面した人向けに記事化しておく
3行で
- 記事タイトルの疑問は、私がひたすらググり続けてもよく分からなかった疑問
- どんなにググっても解決されないのは当然で、別に特殊な作成方法など存在しないことが原因だった
- つまりBigQueryの
_TABLE_SUFFIXは日付に限らずどんなパターンでもワイルドカードで取得できる
そもそもなぜ私はワイルドカードで取得するタイプのテーブルには特別なテーブル作成が必要だと思っていたのか？
BigQueryの公式リファレンスにはワイルドカード（*）を利用したテーブルのクエリ方法についての説明はすでに存在している。
クエリの走査ボリュームを減らすためにもこの機能は可能な限り活用したいのでどうやったらワイルドカードで引っかかるようにサフィックス付きのテーブルを作成出来るのか調べることに。。。。
そして、どんなにググってもワイルドカードを利用して_TABLE_SUFFIXをWHERE句に指定して絞っているクエリ例しかなく発見出来ずやや途方にくれていた。
WebUIではどう表現されているか
BigQueryのパブリックデータga_sessionsは日付別にテーブルが管理されており、こんな感じを再現したい
bigquery-public-data.google_analytics_sample.ga_sessions_
実際に、BigQueryをWebUIで確認すると複数のテーブルがこんな感じで格納されている。
WebUIで見ると、366日分のテーブルに関して日付を認識しつつクエリできるように設計されているように見える。。。
青枠で囲っている部分が日付で選択できるので、私はてっきりこの手のワイルドカードを使うテーブルは日付を認識できるように特別な方法でCreate Tableしなければ使えないと思い込んでいた。
ただ、これは完全なる勘違いで、WebUI上でそう表現されているだけでテーブル名は日付で保持していたりはしなかった。
結局どうすればいいのか？
結論から言えば
自分が認識しやすいサフィックスをテーブル名に付けておけばどんなテーブル名でもワイルドカードで取得できる
ただ、BiqQueryのテーブル名の規則では
1,024 文字以内
英字（大文字または小文字）、数字、アンダースコアだけが含まれている
となっているため日付のフォーマットでテーブル名を作成することは不可。
何を言っているかと言うと下記のケースのことを言っている↓
sales_actual_data_2019-01-02みたいなテーブルは作成出来ず、
sales_actual_data_20190102の命名形式でテーブルを作成する必要がある
実際にテーブルを作ってみて挙動を確かめる
試しに２つのパターンでテーブルを作ってみてワイルドカードでクエリできるかを試す
1. サフィックスに日付っぽい数値を入れて日付として認識されるか試す
DataSet名:demoでテーブル名:demo_table_{Date}となるようなテーブルを作成してWebUI上どのように見えるか実際に確認する
まずは、2019/01/01の1日分のテーブルだけを作成した場合
from google.cloud.bigquery import Client, SchemaField, Table
project_id = '{YourGCPProjectID}'
client = Client(project=project_id)
# DataSet名
dataset_name = "demo"
# １つだけテーブルを作成する
table_name = "demo_table_20190101"
table_id = "{}.{}.{}".format(client.project, dataset_name, table_name)
# DDLのようにテーブルの定義をSchemaとして記載
schema = [
SchemaField('ItemId', 'INTEGER', 'NULLABLE', None),
SchemaField('Number', 'INTEGER', 'NULLABLE', None),
SchemaField('Amount', 'INTEGER', 'NULLABLE', None),
SchemaField('Date', 'DATETIME', 'NULLABLE', None),
]
# テーブルを作成
table = Table(table_id, schema=schema)
table = client.create_table(table)
画面上はこんな感じで反映される
どうやらこの時点では、このサフィックスを日付だとは認識していない模様
次に2019/01/02-2019/01/05の４日分を追加でテーブル作成してみる
# 試しに2019/01/02/-2019/01/05のテーブルを追加で作成してみる
for table_suffix in range(20190102,20190106):
table_name = "demo_table_{}".format(table_suffix)
table_id = "{}.{}.{}".format(client.project, dataset_name, table_name)
table = Table(table_id, schema=schema)
table = client.create_table(table)
すると画面ではサフィックス部分がまとめられてこんな感じで表示されるようになる
そして、日付部分がドロップダウンリスト形式で選べるようになっている
もちろん、このテーブルをワイルドカードでクエリできる
SELECT
ItemId
, Number
, Amount
, Date
FROM
`{YourGCPProjectID}.demo.demo_table_*`
WHERE
_TABLE_SUFFIX BETWEEN '20190101' AND '20190103'
LIMIT 1000
;
2. 文字列をサフィックスにしてワイルドカードが機能するか試す
日付はいい感じで出来そうなのはわかったので、次にサフィックスが文字列でも機能するかを試す
適当に球技名をサフィックスに付けてテーブルがどのように認識されるか試す
# DDLのようにテーブルの定義をSchemaとして記載
schema = [
SchemaField('ItemId', 'INTEGER', 'NULLABLE', None),
SchemaField('Number', 'INTEGER', 'NULLABLE', None),
SchemaField('Amount', 'INTEGER', 'NULLABLE', None),
SchemaField('Genre', 'STRING', 'NULLABLE', None),
]
# 商品やジャンル別にテーブルが別れていたと仮定して複数テーブルを作成してみる
for table_suffix in ["football","baseball","rugbyball","volleyball"] :
table_name = "item_table_{}".format(table_suffix)
table_id = "{}.{}.{}".format(client.project, dataset_name, table_name)
table = Table(table_id, schema=schema)
table = client.create_table(table)
画面上は、日付のようにまとまっては表示されない
しかし、ワイルドカード自体は有効で、下記のようにクエリが可能なことは確認した
SELECT
ItemId
, Number
, Amount
, Genre
FROM
`{YourGCPProjectID}.demo.item_table_*`
WHERE
_TABLE_SUFFIX IN ('baseball', 'football' )
LIMIT 100
;
おしまい