User

i have a bigquery dataset that is sync with the partner sales consonle from google the schema is Table schema Warning: Attempting to modify the export schema yourself can result in data no longer getting exported. If you want to add or remove columns, you can create a view or a materialized view, or you can join against your other tables in your queries.

If your table gets in this state, you will need to revert it back to having all the correct columns and nothing else. Afterwards, you may need to go to the Billing export page and click Update.

The following schema describes the data in the BigQuery table.

In your BigQuery dataset, the detailed rebilling export is loaded into a data table named reseller_billing_detailed_export_v1.

Field Type Description billing_account_id String The account ID that the usage is associated with, which may be the customer's or yours. Depending on the product family, this may be a billing account or a Cloud Identity ID. payer_billing_account_id String The ID of your parent billing account. invoice.month String The year and month (YYYYMM) of the invoice that includes the cost line items. For example: "202101" is the same as January 2021. cost_type String The type of cost this line item represents: regular, tax, adjustment, or rounding error. service.id String The ID of the service that the usage is associated with. service.description String The Google service that reported the billing data. sku.id String The SKU ID of the resource used by the service. sku.description String A description of the resource type used by the service. For example, Cloud Storage is of type Standard Storage US. usage_start_time Timestamp The start time of the calculated cost's hourly usage window. The usage and costs for all services are displayed with hourly granularity. Long running service usage is spread across multiple hourly windows. For subscription products, this is the beginning of the billing month. usage_end_time Timestamp The end time of the calculated cost's hourly usage window. The usage and costs for all services are displayed with hourly granularity. Long running service usage is spread across multiple hourly windows. For subscription products, this is the end of the billing month. project Struct Information about the Google Cloud project that generated the Cloud Billing data. project.number String An internally-generated, anonymized, and unique identifier for the Google Cloud project that generated the Cloud Billing data. project.id String The ID of the Google Cloud project that generated the Cloud Billing data. project.name String The name of the Google Cloud project that generated the Cloud Billing data. project.labels Struct, Repeated project.labels.key String If labels are present, the key portion of the key:value pair. project.labels.value String If labels are present, the value portion of the key:value pair. project.ancestry_numbers String The resource hierarchy ancestors for the project identified by the project.id. labels Struct, Repeated labels.key String If labels are present, the key portion of the key:value pair. labels.value String If labels are present, the value portion of the key:value pair. system_labels Struct, Repeated system_labels.key String If system labels are present, the key portion of the key:value pair. system_labels.value String If system labels are present, the value portion of the key:value pair. location.location String Location of usage at the level of a country, region, or zone. Shows global for resources with no specific location. For more information, see Regions and Zones. location.country String When location.location is a country, region, or zone, this field is the country of usage. For example, US. For more information, see Regions and Zones. location.region String When location.location is a country or region, this field is the region of usage. For example, us-central1. For more information, see Regions and Zones. location.zone String When location.location is a zone, this field is the zone of usage. For example, us-central1-a. For more information, see Regions and Zones. resource Struct Describes the structure and value of information relevant to service resources (for example, virtual machines or an SSD) that generate service usage. resource.name String A service-specific identifier for the resource that generated relevant usage. Can be user-generated. resource.global_name String A globally unique service identifier for the resource that generated relevant usage. cost Numeric The cost of usage before any applied credits. To get the total cost including credits, add credits.amount to cost. currency String The cost's billing currency. For more information, see Local Currency for Billing and Payments. currency_conversion_rate Float The exchange rate from US dollars to the local currency. That is, cost ÷ currency_conversion_rate equals the cost in US dollars. usage.amount Numeric The quantity of usage.unit used. usage.unit String The base unit for measuring resource usage. For example, the base unit for standard storage is byte-seconds. usage.amount_in_pricing_unit Numeric The quantity of usage.pricing_unit usage. usage.pricing_unit String The units for measuring resource usage. credits Struct, Repeated Contains fields that describe the structure and value of credits. credits.id String If populated, indicates that a credit is associated with the product SKU. credits.full_name String The name of the credit associated with the product SKU. credits.type String This field describes the purpose or origin of the credits.id. credits.name String A description of the credit applied to the usage. credits.amount Numeric The amount of the credit applied to the usage. credits.channel_partner_amount Numeric The amount of the credit applied to the usage, after rebilling markups and discounts. Based on the channel_partner_name and the associated ChannelPartnerRepricingConfig. If credits.type is RESELLER_MARGIN, this amount will be zero. credits.customer_amount Numeric The amount of the credit applied to the usage, after rebilling markups and discounts. Based on the customer_name and the associated CustomerRepricingConfig. If credits.type is RESELLER_MARGIN, this amount will be zero. adjustmentsInfo Struct, Repeated Contains fields that describe the structure and value of an adjustment to cost line items. adjustmentsInfo.adjustment_id String If populated, an adjustment is associated with a cost line item. adjustmentsInfo.adjustments_id is the unique ID for all the adjustments caused by an issue. adjustmentsInfo.adjustment_description String A description of the adjustment and its cause. adjustmentsInfo.adjustment_type String The type of the adjustment. adjustmentsInfo.adjustment_mode String How the adjustment was issued. export_time Timestamp A processing time associated with an append of billing data. This will always increase with each new export. Use the export_time column to understand when the exported billing data was last updated. entitlement_name String The resource name of the Cloud Channel API Entitlement. Uses the format: accounts/{account}/customers/{customer}/entitlements/{entitlement} channel_partner_name String (Distributors only) The resource name of the channel partner that is selling to the end customer. Uses the format: accounts/{account}/channelPartnerLinks/{channelPartner}. channel_partner_cost Numeric (Distributors only) The cost to the channel partner, identified by channel_partner_name, after applying an existing ChannelPartnerRepricingConfig. customer_name String The resource name of the reseller's customer who consumed this usage in the format accounts/{account}/customers/{customer} customer_cost Numeric The cost to the reseller's customer identified by the customer_name column, after applying the CustomerRepricingConfig, if one exists. cost_at_list Numeric The cost if charged with public pricing. customer_repricing_config_name String The resource name of the Cloud Channel API CustomerRepricingConfig. Uses the format: accounts/{account}/customers/{customer}/customerRepricingConfigs/{id}. Note: For Google Cloud, the first full month of data with this field is January 2023. channel_partner_repricing_config_name String (Distributors only) The resource name of the Cloud Channel API ChannelPartnerRepricingConfig. Uses the format: accounts/{account}/channelPartnerLinks/{channelPartner}/channelPartnerRepricingConfigs/{id}. Note: For Google Cloud, the first full month of data with this field is January 2023. tags Struct Fields that describe the tag, such as key, value, and namespace. Note: For Google Cloud, the first full month of data with these tags is January 2023. tags.key String The short name or display name of the key associated with this particular tag. tags.value String The resources attached to a tag key. At any given time, exactly one value can be attached to a resource for a given key. tags.inherited Boolean Indicates whether a tag binding is inherited (Tags Inherited = True) or direct/non-inherited (Tags Inherited = False). tags.namespace String Represents the resource hierarchy that define tag key and values. Namespace can be combined with tag key and tag value short names to create a globally unique, fully qualified name for the tag key or tag value. customer_correlation_id String The external CRM ID for the customer. Populated only if a CRM ID exists for this customer. Note: For Google Cloud, the first full month of data with this field is March 2023. price Struct Fields that describe the structure and value related to the prices charged for usage. Note: For Google Cloud, the first full month of data with price fields is May 2023. price.effective_price Numeric The price charged for the usage. price.tier_start_amount Numeric The lower bound number of units for a SKU's pricing tier. For example, a SKU with three pricing tiers such as 0-100 units, 101-1000 units, and 1001+ units, will display three pricing rows for the SKU, with 0, 101, and 1001 in the price.tier_start_amount field representing the starting unit quantity for the SKU's pricing tiers. Learn more about Google Cloud pricing tiers. price.unit String The unit of usage in which the pricing is specified and resource usage is measured (such as gibibyte, tebibyte, month, year, gibibyte hour, gibibyte month, or count). The value in the price.unit field matches the value in the usage.pricing_unit field. price.pricing_unit_quantity Numeric The SKU's pricing tier unit quantity. For example, if the tier price is $1 per 1000000 Bytes, then this column will show 1000000. transaction_type String The transaction type of the seller. The transaction type might be one of the following: GOOGLE = 1: Services sold by Google Cloud. THIRD_PARTY_RESELLER = 2: Third party services resold by Google Cloud. THIRD_PARTY_AGENCY = 3: Third party services sold by a partner, with Google Cloud acting as the agent. Note: For Google Cloud, the first full month of data with this field is August 2023. seller_name String The legal name of the seller. Note: For Google Cloud, the first full month of data with this field is August 2023.

as you can see there is a channel_partner_name column with this format accounts/C045sqjbr/channelPartnerLinks/C01dmi1l0

i need to create a new column with the value C01dmi1l0 which is after channelPartnerLinks this represent the Channel_partner_id this new info should be in a new table
ChatGPT
To create a new column with the Channel_partner_id in a new table, you can use the SUBSTR and STRPOS functions to extract the desired substring from the channel_partner_name column. Here's an example query that demonstrates how to achieve this:

sql
Copy code
-- Create a new table with the extracted Channel_partner_id
CREATE TABLE your_dataset.new_table AS
SELECT
  *,
  SUBSTR(channel_partner_name, STRPOS(channel_partner_name, 'channelPartnerLinks/') + LENGTH('channelPartnerLinks/')) AS Channel_partner_id

use some regular expression 
User

i have a bigquery dataset that is sync with the partner sales consonle from google the schema is Table schema Warning: Attempting to modify the export schema yourself can result in data no longer getting exported. If you want to add or remove columns, you can create a view or a materialized view, or you can join against your other tables in your queries.

If your table gets in this state, you will need to revert it back to having all the correct columns and nothing else. Afterwards, you may need to go to the Billing export page and click Update.

The following schema describes the data in the BigQuery table.

In your BigQuery dataset, the detailed rebilling export is loaded into a data table named reseller_billing_detailed_export_v1.

Field Type Description billing_account_id String The account ID that the usage is associated with, which may be the customer's or yours. Depending on the product family, this may be a billing account or a Cloud Identity ID. payer_billing_account_id String The ID of your parent billing account. invoice.month String The year and month (YYYYMM) of the invoice that includes the cost line items. For example: "202101" is the same as January 2021. cost_type String The type of cost this line item represents: regular, tax, adjustment, or rounding error. service.id String The ID of the service that the usage is associated with. service.description String The Google service that reported the billing data. sku.id String The SKU ID of the resource used by the service. sku.description String A description of the resource type used by the service. For example, Cloud Storage is of type Standard Storage US. usage_start_time Timestamp The start time of the calculated cost's hourly usage window. The usage and costs for all services are displayed with hourly granularity. Long running service usage is spread across multiple hourly windows. For subscription products, this is the beginning of the billing month. usage_end_time Timestamp The end time of the calculated cost's hourly usage window. The usage and costs for all services are displayed with hourly granularity. Long running service usage is spread across multiple hourly windows. For subscription products, this is the end of the billing month. project Struct Information about the Google Cloud project that generated the Cloud Billing data. project.number String An internally-generated, anonymized, and unique identifier for the Google Cloud project that generated the Cloud Billing data. project.id String The ID of the Google Cloud project that generated the Cloud Billing data. project.name String The name of the Google Cloud project that generated the Cloud Billing data. project.labels Struct, Repeated project.labels.key String If labels are present, the key portion of the key:value pair. project.labels.value String If labels are present, the value portion of the key:value pair. project.ancestry_numbers String The resource hierarchy ancestors for the project identified by the project.id. labels Struct, Repeated labels.key String If labels are present, the key portion of the key:value pair. labels.value String If labels are present, the value portion of the key:value pair. system_labels Struct, Repeated system_labels.key String If system labels are present, the key portion of the key:value pair. system_labels.value String If system labels are present, the value portion of the key:value pair. location.location String Location of usage at the level of a country, region, or zone. Shows global for resources with no specific location. For more information, see Regions and Zones. location.country String When location.location is a country, region, or zone, this field is the country of usage. For example, US. For more information, see Regions and Zones. location.region String When location.location is a country or region, this field is the region of usage. For example, us-central1. For more information, see Regions and Zones. location.zone String When location.location is a zone, this field is the zone of usage. For example, us-central1-a. For more information, see Regions and Zones. resource Struct Describes the structure and value of information relevant to service resources (for example, virtual machines or an SSD) that generate service usage. resource.name String A service-specific identifier for the resource that generated relevant usage. Can be user-generated. resource.global_name String A globally unique service identifier for the resource that generated relevant usage. cost Numeric The cost of usage before any applied credits. To get the total cost including credits, add credits.amount to cost. currency String The cost's billing currency. For more information, see Local Currency for Billing and Payments. currency_conversion_rate Float The exchange rate from US dollars to the local currency. That is, cost ÷ currency_conversion_rate equals the cost in US dollars. usage.amount Numeric The quantity of usage.unit used. usage.unit String The base unit for measuring resource usage. For example, the base unit for standard storage is byte-seconds. usage.amount_in_pricing_unit Numeric The quantity of usage.pricing_unit usage. usage.pricing_unit String The units for measuring resource usage. credits Struct, Repeated Contains fields that describe the structure and value of credits. credits.id String If populated, indicates that a credit is associated with the product SKU. credits.full_name String The name of the credit associated with the product SKU. credits.type String This field describes the purpose or origin of the credits.id. credits.name String A description of the credit applied to the usage. credits.amount Numeric The amount of the credit applied to the usage. credits.channel_partner_amount Numeric The amount of the credit applied to the usage, after rebilling markups and discounts. Based on the channel_partner_name and the associated ChannelPartnerRepricingConfig. If credits.type is RESELLER_MARGIN, this amount will be zero. credits.customer_amount Numeric The amount of the credit applied to the usage, after rebilling markups and discounts. Based on the customer_name and the associated CustomerRepricingConfig. If credits.type is RESELLER_MARGIN, this amount will be zero. adjustmentsInfo Struct, Repeated Contains fields that describe the structure and value of an adjustment to cost line items. adjustmentsInfo.adjustment_id String If populated, an adjustment is associated with a cost line item. adjustmentsInfo.adjustments_id is the unique ID for all the adjustments caused by an issue. adjustmentsInfo.adjustment_description String A description of the adjustment and its cause. adjustmentsInfo.adjustment_type String The type of the adjustment. adjustmentsInfo.adjustment_mode String How the adjustment was issued. export_time Timestamp A processing time associated with an append of billing data. This will always increase with each new export. Use the export_time column to understand when the exported billing data was last updated. entitlement_name String The resource name of the Cloud Channel API Entitlement. Uses the format: accounts/{account}/customers/{customer}/entitlements/{entitlement} channel_partner_name String (Distributors only) The resource name of the channel partner that is selling to the end customer. Uses the format: accounts/{account}/channelPartnerLinks/{channelPartner}. channel_partner_cost Numeric (Distributors only) The cost to the channel partner, identified by channel_partner_name, after applying an existing ChannelPartnerRepricingConfig. customer_name String The resource name of the reseller's customer who consumed this usage in the format accounts/{account}/customers/{customer} customer_cost Numeric The cost to the reseller's customer identified by the customer_name column, after applying the CustomerRepricingConfig, if one exists. cost_at_list Numeric The cost if charged with public pricing. customer_repricing_config_name String The resource name of the Cloud Channel API CustomerRepricingConfig. Uses the format: accounts/{account}/customers/{customer}/customerRepricingConfigs/{id}. Note: For Google Cloud, the first full month of data with this field is January 2023. channel_partner_repricing_config_name String (Distributors only) The resource name of the Cloud Channel API ChannelPartnerRepricingConfig. Uses the format: accounts/{account}/channelPartnerLinks/{channelPartner}/channelPartnerRepricingConfigs/{id}. Note: For Google Cloud, the first full month of data with this field is January 2023. tags Struct Fields that describe the tag, such as key, value, and namespace. Note: For Google Cloud, the first full month of data with these tags is January 2023. tags.key String The short name or display name of the key associated with this particular tag. tags.value String The resources attached to a tag key. At any given time, exactly one value can be attached to a resource for a given key. tags.inherited Boolean Indicates whether a tag binding is inherited (Tags Inherited = True) or direct/non-inherited (Tags Inherited = False). tags.namespace String Represents the resource hierarchy that define tag key and values. Namespace can be combined with tag key and tag value short names to create a globally unique, fully qualified name for the tag key or tag value. customer_correlation_id String The external CRM ID for the customer. Populated only if a CRM ID exists for this customer. Note: For Google Cloud, the first full month of data with this field is March 2023. price Struct Fields that describe the structure and value related to the prices charged for usage. Note: For Google Cloud, the first full month of data with price fields is May 2023. price.effective_price Numeric The price charged for the usage. price.tier_start_amount Numeric The lower bound number of units for a SKU's pricing tier. For example, a SKU with three pricing tiers such as 0-100 units, 101-1000 units, and 1001+ units, will display three pricing rows for the SKU, with 0, 101, and 1001 in the price.tier_start_amount field representing the starting unit quantity for the SKU's pricing tiers. Learn more about Google Cloud pricing tiers. price.unit String The unit of usage in which the pricing is specified and resource usage is measured (such as gibibyte, tebibyte, month, year, gibibyte hour, gibibyte month, or count). The value in the price.unit field matches the value in the usage.pricing_unit field. price.pricing_unit_quantity Numeric The SKU's pricing tier unit quantity. For example, if the tier price is $1 per 1000000 Bytes, then this column will show 1000000. transaction_type String The transaction type of the seller. The transaction type might be one of the following: GOOGLE = 1: Services sold by Google Cloud. THIRD_PARTY_RESELLER = 2: Third party services resold by Google Cloud. THIRD_PARTY_AGENCY = 3: Third party services sold by a partner, with Google Cloud acting as the agent. Note: For Google Cloud, the first full month of data with this field is August 2023. seller_name String The legal name of the seller. Note: For Google Cloud, the first full month of data with this field is August 2023.

as you can see there is a channel_partner_name column with this format accounts/C045sqjbr/channelPartnerLinks/C01dmi1l0

i need to create a new column with the value C01dmi1l0 which is after channelPartnerLinks this represent the Channel_partner_id this new info should be in a new table
ChatGPT
To create a new column with the Channel_partner_id in a new table, you can use the SUBSTR and STRPOS functions to extract the desired substring from the channel_partner_name column. Here's an example query that demonstrates how to achieve this:

sql
Copy code
-- Create a new table with the extracted Channel_partner_id
CREATE TABLE your_dataset.new_table AS
SELECT
  *,
  SUBSTR(channel_partner_name, STRPOS(channel_partner_name, 'channelPartnerLinks/') + LENGTH('channelPartnerLinks/')) AS Channel_partner_id
FROM
  your_dataset.reseller_billing_detailed_export_v1;
This query creates a new table named new_table in your dataset. It selects all columns from the existing table (reseller_billing_detailed_export_v1) and adds a new column Channel_partner_id with the extracted value from the channel_partner_name column.

Make sure to replace your_dataset with the actual name of your BigQuery dataset.

Note: The STRPOS function is used to find the position of the substring 'channelPartnerLinks/' in the channel_partner_name column, and SUBSTR is used to extract the substring starting from that position. This assumes that the Channel_partner_id always comes after 'channelPartnerLinks/'. If the format varies, you may need to adjust the query accordingly.
FROM
  your_dataset.reseller_billing_detailed_export_v1;
This query creates a new table named new_table in your dataset. It selects all columns from the existing table (reseller_billing_detailed_export_v1) and adds a new column Channel_partner_id with the extracted value from the channel_partner_name column.

Make sure to replace your_dataset with the actual name of your BigQuery dataset.

Note: The STRPOS function is used to find the position of the substring 'channelPartnerLinks/' in the channel_partner_name column, and SUBSTR is used to extract the substring starting from that position. This assumes that the Channel_partner_id always comes after 'channelPartnerLinks/'. If the format varies, you may need to adjust the query accordingly.