このページでは、BigQuery のテーブルにエクスポートされた Cloud Billing データの 2020 年 10 月 28 日のスキーマ変更に対処する方法を説明します。
変更について
BigQuery にエクスポートされる Cloud Billing 標準使用料金データのテーブル スキーマが更新され、追加のデータ フィールドがわかりやすくなりました。BigQuery データセット内で、このテーブルの名前は gcp_billing_export_v1_<BILLING_ACCOUNT_ID>
です。
Cloud Billing の BigQuery 使用量データ エクスポート スキーマに次のデータ フィールドが追加されました。
project.number
adjustment_info
adjustment_info.id
adjustment_info.mode
adjustment_info.description
adjustment_info.type
このデータは 2020 年 10 月 29 日の時点のものであり、それ以前に記録されたデータでは利用できません。必要に応じて、移行を行い、新しいスキーマに合わせて統合または自動化を更新してください。新しいフィールドによって提供されるデータの詳細については、BigQuery の Cloud Billing データテーブルについてをご覧ください。
既存のテーブルとクエリへの影響
標準使用料の費用データ エクスポートのテーブル構造が変更されたため、エクスポートされたテーブルを直接参照するクエリでは、使用可能なすべてのデータが提供されなくなりました。この問題を解決するには、エクスポートしたテーブルをクエリして目的の構造で情報を表示する BigQuery ビューを作成することをおすすめします。その後、エクスポートしたテーブルではなく、ビューから pull するレポートやダッシュボードにフィードするようにクエリを調整できます。
ビューを使用すると、クエリとダッシュボードで使用されるデータの構造を標準化できます。
作成するビューではデータを正規化し、クエリですべての関連テーブルに同じスキーマを使用する必要があります。これにより、将来のスキーマの変更から保護し、データスキーマが変更されたときに、それらのインスタンスでビューの基盤となるクエリを変更できるようになります。
スキーマの変更に対応するビューの作成
以前のスキーマを使用するテーブルを保持する必要がある場合は、そのテーブルの BigQuery ビューを作成して、データスキーマを正規化することをおすすめします。以前のスキーマから新しいスキーマに移行するビューを作成する場合、UNION
ステートメントを使用すると、一致しないスキーマをテーブルと結合できます。作成するビューは、クエリとダッシュボードで使用するデータ フィールドによって異なります。
状況に応じて、以下の例を使用できる場合もあります。ここでは、クエリに project.number
と adjustment_info
という新しいフィールドを使用する場合もあります。
- 既存のスキーマ プロパティと新しいテーブル(
credits.type
、credits.id
、credits.full
、project.number
、adjustment_info
など)を含むテーブルを使用します。このビューの作成方法の例については、更新されたスキーマですべてのフィールドを持つテーブルのビューを作成するをご覧ください。 - 既存のスキーマ プロパティ
credits.type
、credits.id
、credits.full
を含まないテーブルを使用します。このビューの作成方法の例については、credits.type
、credits.id
、credits.full
のないテーブルのビューの作成をご覧ください。 - 既存のスキーマ プロパティ
credits.type
、credits.id
、credits.full
を含むが、新しいスキーマ プロパティproject.number
およびadjustment_info
は含まれていないテーブルを使用します。このビューの作成方法の例については、project.number
とadjustment_info
のないテーブルのビューの作成をご覧ください。
ビューを作成するには、ビューにアクセス可能なデータを定義する SQL クエリを作成します。詳細については、ビューの作成をご覧ください。
以下に、BigQuery ビューの作成手順の概要を示します。
- ビューを作成するクエリを選択する
- クエリを実行して結果を確認する
- ビューを保存する
- 新しいビューの名前を入力する
- 新しいビューのスキーマを確認する
1. 更新されたスキーマですべてのフィールドを持つテーブルのビューを作成する
次のクエリは、既存のスキーマと更新されたスキーマの両方を使用して新しいビューを作成します。このタイプのビューは、将来のスキーマ変更による影響を制限できます。
このビューをクエリに使用すると、すべてのスキーマが同じになり、UNION
ステートメントを正常に機能させることができます。このクエリは、基になるテーブルの credits.type
、credits.id
、credits.full
、project.number
、adjustment_info
フィールドと値を保持します。
標準 SQL
SELECT billing_account_id, STRUCT(service.id as id, service.description as description) as service, STRUCT(sku.id as id, sku.description as description) as sku, usage_start_time, usage_end_time, STRUCT( project.id as id, project.name as name, project.number as number, ARRAY(SELECT AS STRUCT label.key as key, label.value as value, FROM UNNEST(project.labels) as label) as labels, project.ancestry_numbers as ancestry_numbers) as project, ARRAY(SELECT AS STRUCT label.key as key, label.value as value, FROM UNNEST(labels) as label) as labels, ARRAY(SELECT AS STRUCT system_label.key as key, system_label.value as value, FROM UNNEST(system_labels) as system_label) as system_labels, STRUCT( location.location as location, location.country as country, location.region as region, location.zone as zone) as location, export_time, cost, currency, currency_conversion_rate, STRUCT( usage.amount as amount, usage.unit as unit, usage.amount_in_pricing_units as amount_in_pricing_units, usage.pricing_unit as pricing_unit) as usage, ARRAY(SELECT AS STRUCT credit.name as name, credit.amount as amount, credit.type as type, credit.id as id, credit.full_name as full_name, FROM UNNEST(credits) as credit) as credits, STRUCT( invoice.month as month) as invoice, cost_type, STRUCT( adjustment_info.id as id, adjustment_info.description as description, adjustment_info.mode as mode, adjustment_info.type as type) as adjustment_info, FROM TABLE_WITH_CREDITINFO_PROJECT_NUMBER_AND_ADJUSTMENT_INFO
2. credits.type
、credits.id
、credits.full
のないテーブルのビューを作成する
次のクエリは、既存のスキーマ プロパティ credits.type
、credits.id
、credits.full
が含まれていないテーブルを使用して新しいビューを作成します。
標準 SQL
SELECT billing_account_id, STRUCT(service.id as id, service.description as description) as service, STRUCT(sku.id as id, sku.description as description) as sku, usage_start_time, usage_end_time, STRUCT( project.id as id, project.name as name, CAST(NULL as string) as number, ARRAY(SELECT AS STRUCT label.key as key, label.value as value, FROM UNNEST(project.labels) as label) as labels, project.ancestry_numbers as ancestry_numbers) as project, ARRAY(SELECT AS STRUCT label.key as key, label.value as value, FROM UNNEST(labels) as label) as labels, ARRAY(SELECT AS STRUCT system_label.key as key, system_label.value as value, FROM UNNEST(system_labels) as system_label) as system_labels, STRUCT( location.location as location, location.country as country, location.region as region, location.zone as zone) as location, export_time, cost, currency, currency_conversion_rate, STRUCT( usage.amount as amount, usage.unit as unit, usage.amount_in_pricing_units as amount_in_pricing_units, usage.pricing_unit as pricing_unit) as usage, ARRAY(SELECT AS STRUCT credit.name as name, credit.amount as amount, CAST(NULL as STRING) as type, CAST(NULL as STRING) as id, CAST(NULL as STRING) as full_name, FROM UNNEST(credits) as credit) as credits, STRUCT( invoice.month as month) as invoice, cost_type, STRUCT( CAST(NULL as STRING) as id, CAST(NULL as STRING) as description, CAST(NULL as STRING) as mode, CAST(NULL as STRING) as type) as adjustment_info, FROM TABLE_WITHOUT_CREDIT_ID_TYPE_FULL_NAME
3. project.number
と adjustment_info
を使用せずにテーブルのビューを作成する
次に示すクエリは、既存のスキーマ プロパティ credits.type
、credits.id
、credits.full
、が含まれており、新しいスキーマ プロパティ project.number
と adjustment_info
が含まれていないテーブルを使用して新しいビューを作成します。
標準 SQL
SELECT billing_account_id, STRUCT(service.id as id, service.description as description) as service, STRUCT(sku.id as id, sku.description as description) as sku, usage_start_time, usage_end_time, STRUCT( project.id as id, project.name as name, CAST(NULL as string) as number, ARRAY(SELECT AS STRUCT label.key as key, label.value as value, FROM UNNEST(project.labels) as label) as labels, project.ancestry_numbers as ancestry_numbers) as project, ARRAY(SELECT AS STRUCT label.key as key, label.value as value, FROM UNNEST(labels) as label) as labels, ARRAY(SELECT AS STRUCT system_label.key as key, system_label.value as value, FROM UNNEST(system_labels) as system_label) as system_labels, STRUCT( location.location as location, location.country as country, location.region as region, location.zone as zone) as location, export_time, cost, currency, currency_conversion_rate, STRUCT( usage.amount as amount, usage.unit as unit, usage.amount_in_pricing_units as amount_in_pricing_units, usage.pricing_unit as pricing_unit) as usage, ARRAY(SELECT AS STRUCT credit.name as name, credit.amount as amount, credit.type as type, credit.id as id, credit.full_name as full_name, FROM UNNEST(credits) as credit) as credits, STRUCT( invoice.month as month) as invoice, cost_type, STRUCT( CAST(NULL as STRING) as id, CAST(NULL as STRING) as description, CAST(NULL as STRING) as mode, CAST(NULL as STRING) as type) as adjustment_info, FROM TABLE_WITHOUT_PROJECTNUMBER_AND_ADJUSTMENT_INFO
4. ビューがと元のテーブルの整合性を確認する
次のクエリを使用すると、作成したビューが、クエリの実行元のテーブルと一致するデータを表示していることを確認できます。クエリでは、credits.type
、credits.id
、credits.full
を使用せずにサンプルで作成されたビューを使用します。このビューの作成方法について詳しくは、credits.type
、credits.id
、credits.full
を使用せずにテーブルのビューを作成するをご覧ください。
このクエリは、元のテーブルと、credits.type
、credits.id
、credits.full
を使用せずに作成されたビューとの間で費用を行単位で比較します。
標準 SQL
-- ROW BY ROW COMPARISON OF COST BETWEEN ORIGINAL TABLE AND CONVERTED TABLE SELECT cost FROM TABLE_WITHOUT_CREDIT_ID_TYPE_FULL_NAME EXCEPT DISTINCT SELECT cost FROM TABLE_WITHOUT_CREDIT_ID_TYPE_FULL_NAME_VIEW
このクエリは、元のテーブルと、credits.type
、credits.id
、credits.full
を使用せずに作成されたビューとの間でクレジットを行単位で比較します。
標準 SQL
-- ROW BY ROW COMPARISON OF CREDITS BETWEEN ORIGINAL TABLE AND CONVERTED TABLE WITH CONCAT_AMOUNTS AS (SELECT ARRAY_CONCAT_AGG(ARRAY(SELECT amount FROM UNNEST(credits) as cred)) as amounts FROM TABLE_WITHOUT_CREDIT_ID_TYPE_FULL_NAME), CONCAT_AMOUNTS_CONVERTED AS (SELECT ARRAY_CONCAT_AGG(ARRAY(SELECT amount FROM UNNEST(credits) as cred)) as amounts FROM TABLE_WITHOUT_CREDIT_ID_TYPE_FULL_NAME_VIEW) SELECT amounts FROM CONCAT_AMOUNTS, UNNEST(amounts) as amounts EXCEPT DISTINCT SELECT amounts FROM CONCAT_AMOUNTS_CONVERTED, UNNEST(amounts) as amounts
関連トピック
- BigQuery の Cloud Billing データテーブルについて
- BigQuery への Cloud Billing データのエクスポートを設定する
- BigQuery への Cloud Billing エクスポート データに対するクエリの例
- Looker Studio で一定期間の利用額を可視化する