BigQuery エクスポート データ テーブルに対するスキーマ変更の処理

このページでは、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 という新しいフィールドを使用する場合もあります。

1. 既存のスキーマ プロパティと新しいテーブル（credits.type、credits.id、credits.full、project.number、adjustment_info など）を含むテーブルを使用します。このビューの作成方法の例については、更新されたスキーマですべてのフィールドを持つテーブルのビューを作成するをご覧ください。
2. 既存のスキーマ プロパティ credits.type、credits.id、credits.full を含まないテーブルを使用します。このビューの作成方法の例については、credits.type、credits.id、credits.full のないテーブルのビューの作成をご覧ください。
3. 既存のスキーマ プロパティ credits.type、credits.id、credits.full を含むが、新しいスキーマ プロパティ project.number および adjustment_info は含まれていないテーブルを使用します。このビューの作成方法の例については、project.number と adjustment_info のないテーブルのビューの作成をご覧ください。

ビューを作成するには、ビューにアクセス可能なデータを定義する SQL クエリを作成します。詳細については、ビューの作成をご覧ください。

以下に、BigQuery ビューの作成手順の概要を示します。

1. ビューを作成するクエリを選択する
2. クエリを実行して結果を確認する
3. ビューを保存する
4. 新しいビューの名前を入力する
5. 新しいビューのスキーマを確認する

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 で一定期間の利用額を可視化する