BigQuery Export データテーブルに対するスキーマの変更の処理

このページでは、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 データテーブルについてをご覧ください。

既存のテーブルとクエリへの影響

1 日の費用データのエクスポートのテーブル構造が変更されたため、エクスポートされたテーブルを直接参照するクエリでは、使用可能なデータがすべて取得できなくなります。この問題を解決するには、エクスポートされたテーブルにクエリを実行する BigQuery ビューを作成し、目的の構造で情報を提示することをおすすめします。そのうえで、エクスポートされたテーブルではなく、ビューやダッシュボードに取り込むためのクエリを調整できます。

ビューを使用すると、クエリとダッシュボードで使用されるデータの構造を標準化できます。

作成するビューはデータを正規化して、関連するすべてのテーブルにクエリと同じスキーマを提示する必要があります。これにより、将来のスキーマ変更から保護され、データスキーマが変更されたときにそれらのインスタンスでビューの基盤となるクエリを変更できます。

スキーマの変更を処理するビューの作成

以前のスキーマを使用するテーブルを保持する必要がある場合は、それらのテーブル用の BigQuery ビューを作成して、データスキーマを正規化することをおすすめします。以前のスキーマから新しいスキーマに移行するビューを作成する場合は、UNION ステートメントを使用して、テーブルとスキーマの不一致を統合できます。作成するビューは、クエリとダッシュボードで使用するデータ フィールドによって異なります。

状況に応じて、以下の例を使用できる場合もあります。ここでは、クエリに project.numberadjustment_info という新しいフィールドを使用する場合もあります。

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

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

BigQuery ビューを作成する手順の概要は次のとおりです。

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

更新されたスキーマですべてのフィールドを持つテーブルのビューを作成する

既存のスキーマと更新されたスキーマの両方を使用して新しいビューを作成するクエリを以下に示します。このタイプのビューでは、将来のスキーマの変更の公開が制限されます。

クエリでこのビューを使用することで、すべてのスキーマが同じスキーマになり、UNION ステートメントが正しく動作します。このクエリは、基になるテーブルの credits.typecredits.idcredits.fullproject.numberadjustment_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

credits.typecredits.idcredits.full のないテーブルのビューの作成

既存のスキーマ プロパティ credits.typecredits.idcredits.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

project.numberadjustment_info を使用しないテーブルのビューの作成

次に示すクエリは、既存のスキーマ プロパティ credits.typecredits.idcredits.full、が含まれており、新しいスキーマ プロパティ project.numberadjustment_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

ビューが元のテーブルと一致していることを確認する

次のクエリを使用すると、作成したビューが、クエリの実行元のテーブルと一致するデータを表示していることを確認できます。クエリでは、credits.typecredits.idcredits.full を使用せずにサンプルで作成されたビューを使用します。このビューの作成方法について詳しくは、credits.typecredits.idcredits.full を使用せずにテーブルのビューを作成するをご覧ください。

このクエリは、元のテーブルと、credits.typecredits.idcredits.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.typecredits.idcredits.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