处理 BigQuery Export 数据表的架构变更

本页介绍如何处理 2020 年 10 月 28 日对导出到 BigQuery 中的表的 Cloud Billing 数据的架构变更。

了解变更

导出到 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 视图，用于查询导出的表，并以首选结构显示信息。然后，您可以调整馈入报告和信息中心的查询，从视图（而非导出的表）中提取。

通过使用视图，您可以标准化查询和信息中心中使用的数据的结构。

您创建的视图应对数据进行归一化，以便所有相关表使用相同的架构为您展示查询。这样可以防止您未来的架构更改，还可让您在数据架构更改时在这些实例中修改视图的基础查询。

创建视图以处理架构更改

如果您需要保留使用旧架构的表，我们建议您为这些表创建 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 数据表。
• 设置将 Cloud Billing 数据导出到 BigQuery 的功能
• 将 Cloud Billing 数据导出到 BigQuery 的查询示例
• 通过 Looker 数据洞察直观呈现一段时间内的支出