Labels applied to BigQuery not showing up in INFORMATION_SCHEMA

[cking8]

I have a few views in BigQuery whose queries can get pretty expensive, so I'm trying to apply labels to these views to be able to monitor their costs. I'm using a separate view to query cost-related information about these jobs. Here's a simplified version of the query:

SELECT creation_time, total_bytes_billed, labels  

FROM `[project-id].region-[region].INFORMATION_SCHEMA.JOBS_BY_PROJECT`

WHERE job_type = 'QUERY'

I applied several labels last week (about 72 hours ago). They are still not appearing in the results of that query. I can see other labels like "sheets_connector" = "connected_sheets". I was expecting all the custom labels that I applied to the view to show up as well. What am I missing about how this works? Any help/guidance will be much appreciated!

[ms4446]

Labels applied to BigQuery resources can be visible in the INFORMATION_SCHEMA views. However, it's important to ensure that the labels are applied to the jobs when they're run, not just to the views or tables being queried. If you're encountering this issue, consider the following workarounds to monitor the cost of your views:

1. BigQuery Billing Export:

   • Export your BigQuery usage data to Cloud Storage and then analyze the data to identify the cost of your views. This export will provide detailed information about all Google Cloud services, and you can filter the data for BigQuery-specific costs.

   Steps for Exporting BigQuery Billing Data to Cloud Storage:

   • Go to the Billing tab in the Google Cloud Console.
   • Under Export billing data, click Create export.
   • Select the Cloud Storage destination type.
   • Specify the bucket and bucket folder where you want to export the data.
   • Click Create.

2. BigQuery Audit Logs:

   • Export audit logs for your BigQuery resources to Cloud Storage. These logs provide detailed activity records, which can be especially useful for tracking query costs when labels are applied to jobs.

   Steps for Exporting BigQuery Audit Logs to Cloud Storage:

   • Go to the Cloud Logging interface in the Google Cloud Console.
   • Click the Logs tab.
   • Under Sinks, click Create sink.
   • Select the Cloud Storage destination type.
   • Specify the bucket and bucket folder where you want to export the logs.
   • Click Create.

3. BigQuery Cost Estimation:

   • Use the BigQuery cost estimation feature to get an idea of the cost of your views. This estimate is based on the amount of data the query will scan. However, note that actual costs might vary based on factors like caching.

   Steps for Estimating the Cost of a View:

   • Go to the BigQuery console.
   • Click the Query tab.
   • In the query editor, enter your view query.
   • Click the Cost estimate button. BigQuery will display an estimated cost for the query based on the size of the data it will process and the complexity of the query.

[cking8]

Thanks for the response! When you say "it's important to ensure that the labels are applied to the jobs when they're run, not just to the views or tables", how can I do that? How can I apply a label to a job when it is run?

[ms4446]

Labels can be applied to BigQuery jobs in three ways:

1. Using the BigQuery Web UI:

   1. Go to the BigQuery console.
   2. Click the Jobs tab.
   3. Click the name of the job that you want to apply a label to.
   4. Click the Labels tab.
   5. Click Add label.
   6. Enter the key and value for the label.
   7. Click Add.

2. Using the BigQuery API:

   • When you submit a job using the BigQuery API, you can specify a label in the request body. For example, to submit a query job with the label environment:production, you would use the following request body:

   {
     "configuration": {
       "query": "SELECT * FROM `my_dataset.my_table`",
       "labels": {
         "environment": "production"
       }
     }
   }
   

3. Using the BigQuery command-line tool:

   • When you submit a job using the BigQuery command-line tool, you can specify a label using the --labels flag. For example, to submit a query job with the label environment:production, you would use the following command:

   bq query --labels="environment:production" "SELECT * FROM `my_dataset.my_table`"
   

Additional Notes:

• You can apply up to 64 labels to a job.
• Each label must be a key-value pair.
• The key must be between 1 and 63 characters long.
• The value can be up to 63 characters long.
• The key and value can only contain lowercase letters, numeric characters, underscores, and dashes.

Label Constraints

The constraints on label keys and values are in accordance with Google Cloud's labeling policy, which applies to other Google Cloud services as well, not just BigQuery. For more information, see the Google Cloud labeling documentation: https://cloud.google.com/resource-manager/docs/creating-managing-labels.

INFORMATION_SCHEMA.JOBS_BY_PROJECT

The labels that appear in the INFORMATION_SCHEMA.JOBS_BY_PROJECT view are those applied to the job when it's run, not labels applied to tables or views.

[cking8]

Hm. I think I'm a little lost, but I'm really trying! When I go to the BigQuery console (https://console.cloud.google.com/bigquery), I don't see a Jobs tab. If you mean the Project History panel at the bottom, I can click on that and then click on a job name, but there are no tabs, and I can't apply a label there. 

If you can't see them in INFORMATION_SCHEMA.JOBS_BY_PROJECT, what is the point of adding labels to a view? 

On https://cloud.google.com/bigquery/docs/adding-labels#adding-label-to-session it says I can add labels to a session by including 

SET @@query_label = "KEY:VALUE";

at the beginning of a SQL statement, but it won't let me save that in the view query. 

[ms4446]

Sorry for the confusion. Let me clarify the use of labels in BigQuery:

Applying Labels to Jobs: You can apply labels to BigQuery jobs when you run them. This is useful for tracking costs or categorizing specific query operations. When using the BigQuery API or client libraries, you can specify labels for the jobs you're creating.

Point of Adding Labels to a View: Labels on views (or tables) are primarily for organizational purposes. They can help categorize and manage resources. For instance, you might label views based on the team that uses them or the type of data they contain. However, these labels won't directly affect the cost attribution of the queries that use these views.

Adding Labels to a Session: You can add labels to a session by including SET @@query_label = "KEY:VALUE"; at the beginning of a SQL statement. This will label the resulting job from that query session. However, this command cannot be used within the definition of a view.

If you're looking to monitor the costs of specific views using labels, you'll need to apply labels to the queries (jobs) that use these views, not the views themselves.