Adding labels to BigQuery resources

Google BigQuery labels are key-value pairs that can be attached to BigQuery datasets, tables, and views. Labels are optional, but you can use them for search or classification purposes.

In Semarchy xDI Designer, you can define job labels to use with BigQuery in three different places:

  1. In BigQuery metadata, at the schema and table levels.

  2. In templates designed for BigQuery.

  3. In SQL operations that use the BigQuery driver, as part of the query.

When you define labels in BigQuery metadata and templates, Designer treats them as additive and concatenates them during jobs. Labels that you define in SQL queries directly override any others.

To learn about labels in Google BigQuery, see the official Google documentation.

Define labels in metadata

Define job labels in BigQuery metadata to make sure that a resource has a label regardless of its process.

Labels defined in metadata only apply to data flows from mappings, that use one of the templates listed in the next section.

  1. Open a BigQuery metadata object.

  2. In the metadata, select a BigQuery schema or table element to open its properties.

  3. Go to the Standard finger tab.

  4. Activate the Job Labels field, and enter your labels.

Screenshot of BigQuery metadata

This field expects a one-line string with key-value pairs, formatted like a JSON object without curly braces {}:

"label_key1": "label_value1","label_key2": "label_value2"

When finished, save your metadata. These labels apply to all data flows that use the labeled elements, and are added to any labels you defined in supported templates.

Define labels in templates

Define job labels in BigQuery templates to make sure that a data flow has a label regardless of the resources it touches.

Template support for labels is limited to the following templates:

  • INTEGRATION Bigquery

  • TOOL Bigquery Table Operation

  • INTEGRATION RDBMS to Bigquery

  • LOAD RDBMS to Bigquery

  1. Open a mapping that contains a BigQuery datastore.

  2. On the datastore, select and open one of these four templates:

    • INTEGRATION Bigquery

    • TOOL Bigquery Table Operation

    • INTEGRATION RDBMS to Bigquery

    • LOAD RDBMS to Bigquery

  3. Go to the Standard properties finger tab.

  4. Activate the Job Labels field, and enter your labels.

As in metadata, this field expects a one-line string with key-value pairs, formatted like a JSON object without curly braces {}:

"label_key1": "label_value1","label_key2": "label_value2"

When finished, save your mapping. These labels apply to all data flows that use this instance of the template, and are added to any labels you defined in metadata.

Define labels in queries

If you use SQL operation actions with the BigQuery driver, and those operations need labels, you must add labels to those actions directly. Standalone SQL operation actions do not fetch labels from metadata or templates.

To add job labels to a query, you must define them on the first line, before the actual query. Start the line with jobLabels=, then add a JSON object with curly braces and key-value pairs.

jobLabels={"project": "campaign_analysis","campaign": "emea","team": "marketing_eu","service": "marketing"}
create table `bigquery01`.`dataset01`.T_CUSTOMER
(
    CUS_ID          FLOAT64 ,
    TIT_CODE        STRING ,
    CUS_FIRST_NAME  STRING ,
    CUS_COMPANY     STRING ,
    CUS_LAST_NAME   STRING
)