Skip to main content

Configure a BigQuery Data Connection for a Hybrid Clean Room

If you have data in Google BigQuery and want to be able to use that data in questions in LiveRamp Clean Room, you can create a BigQuery data connection.

A BigQuery data connection for a Hybrid clean room can be used in the following clean room types:

  • Hybrid

  • Confidential Computing

Note

To configure a BigQuery data connection to be used in a Google Cloud BigQuery (native-pattern) clean room, see "Configure a BigQuery Data Connection for a BigQuery Clean Room".

After you’ve created the data connection and Clean Room has validated the connection by connecting to the data in your cloud account, you will then need to map the fields before the data connection is ready to use. This is where you specify which fields can be queryable across any clean rooms, which fields contain identifiers to be used in matching, and any columns by which you wish to partition the dataset for questions.

After fields have been mapped, you’re ready to provision the resulting dataset to your desired clean rooms. Within each clean room, you’ll be able to set dataset analysis rules, exclude or include columns, filter for specific values, and set permission levels.

To configure a BigQuery data connection to be used in either a Hybrid clean room or a Confidential Computing clean room, see the instructions below.

Overall Steps

Perform the following overall steps in Google Cloud Platform to configure a BigQuery data connection to be used in a Hybrid clean room or a Confidential Computing clean room:

  1. Create a Google service account

  2. Determine partition columns

  3. Add BigQuery Read Session User Permission

  4. Add BigQuery Data View Permission

Once the above steps have been performed in Google Cloud Platform, perform the following overall steps in LiveRamp Clean Room:

  1. Add the credentials

  2. Create the data connection

  3. Map the fields

For information on performing these steps, see the sections below.

Perform Steps in Google Cloud Platform

Perform the steps in the sections below in Google Cloud Platform to configure a BigQuery data connection for a hybrid clean room.

Create a Google Service Account

Perform the steps in the sections below in Google Cloud Platform to configure a BigQuery data connection for a hybrid clean room.

  1. From GCP's main menu, select IAM & Admin → Service Accounts.

    hybrid_bq_sa.png

  2. Click CREATE SERVICE ACCOUNT. Save the service account email because you will need it in later steps.

    image idm3666

  3. Enter a name for the service account.

    hybrid_bq_details.png

  4. Create a Private Key of type JSON for the newly created Service Account and save the JSON Key File.

    Note

    This credential JSON will be used in LiveRamp Clean Room to set up your credentials and data connections.

    hybrid_bq_jsonkey.png

Determine Partition Columns

LiveRamp Clean Room allows you to define column(s) for partitioning data during question runs. This is recommended, as it reduces query cost and time to execute. Based on the tables you plan to connect, determine which columns are partition columns and then perform the following steps in Google Cloud Platform:

Note

If you do not need to define any partition columns, skip this procedure.

  1. From the Google Cloud Platform main menu, select. Service Accounts → IAM & Admin → Roles.

  2. Create a custom role with the following permissions at the Project level, depending on which data connection type you'll be using:

    • Google Cloud BigQuery data connection type:

      • bigquery.jobs.create

      • bigquery.jobs.delete

      • bigquery.jobs.update

      • bigquery.readsessions.create

      • bigquery.readsessions.getData

      • roles/bigquery.readSessionUser

    • Google Cloud Authorized View data connection type:

      • bigquery.jobs.create

      • bigquery.readsessions.create

      • bigquery.readsessions.getData

      • bigquery.tables.create

      • bigquery.tables.delete

      • bigquery.tables.get

      • bigquery.tables.getData

      • bigquery.tables.update

      • bigquery.tables.updateData

  3. In the same Google Project, create an empty dataset by following Google's instructions here.

    Note

    The empty dataset should exist in the same region as the source dataset.

  4. Assign the newly-created custom role to the empty dataset or use the role created for all datasets by assigning it at the Project level as in the next step. For more information, see Google's instructions here.

  5. To define partition columns, apply the following roles and permissions to all relevant datasets/tables:

    • roles/bigquery.dataViewer

    • bigquery.tables.create

    • bigquery.tables.updateData

    • bigquery.tables.update

    • bigquery.tables.delete

Add BigQuery Read Session User Permission

To add BigQuery Read Session User Permission to the service account in Google Cloud Platform at the Project level:

  1. From the Google Cloud Platform main menu, select. Service Accounts → IAM & Admin → Manage Resources.

    hybrid_bq_mng_resources.png

  2. Select the desired Google Project.

  3. In the right pane, under the Permissions tab click Add Principal.

  4. Enter the email of the newly created Service Principal and assign the role "BigQuery Read Session User".

    hybrid_bq_read_session_user.png

  5. Click SAVE.

Add BigQuery Data View Permission

To add BigQuery Data View Permission to the service account in Google Cloud Platform at the BigQuery Table level:

  1. From the Google Cloud Platform main menu, select BigQuery View.

  2. Select the BigQuery table and click Share.

  3. In the right pane, click Add Principal.

  4. Enter the email of the new created Service Principal and assign the role "BigQuery Data Viewer".

    Note

    if you're configuring a View or Authorized View, make sure the provided Service Account has access to both the View/Authorized View and the source datasets which are used to materialize them.

    hybrid_bq_sa_bqtable.png

Perform Steps in LiveRamp Clean Room

Once the above steps have been performed in Google Cloud Platform, perform the overall steps in the sections below in LiveRamp Clean Room.

Note

if your cloud security limits access to only approved IP addresses, talk to your LiveRamp representative before creating the data connection to coordinate any necessary allowlisting of LiveRamp IP addresses.

Add the Credentials

To add credentials:

  1. From the LiveRamp Clean Room navigation pane, select Data Management → Credentials.

  2. Click Add Credential.

    add_credential.png

  3. Enter a descriptive name for the credential.

  4. For the Credentials Type, select "Google Service Account".

  5. For the Project ID, enter the project ID.

  6. Enter the Credential JSON you stored in the "Create a Google Service Account" procedure above.

  7. Click Save Credential.

    mceclip0.png

Create the Data Connection

After you've added the credentials to LiveRamp Clean Room, create the data connection:

Note

if your cloud security limits access to only approved IP addresses, talk to your LiveRamp representative before creating the data connection to coordinate any necessary allowlisting of LiveRamp IP addresses.

  1. From the LiveRamp Clean Room navigation pane, select Data Management → Data Connections.

  2. From the Data Connections page, click New Data Connection.

    data_cxn_new.png

  3. From the New Data Connection screen, select either Google Cloud BigQuery or Google Cloud Authorized View.

    Screenshot 2024-03-28 at 12.07.55.png

  4. Select the credentials created in the previous procedure from the list.

    Screenshot 2024-03-28 at 12.10.43.png

  5. If you selected the "Google Cloud BigQuery" data connection type, complete the following fields in the Set up Data Connection section:

    Screenshot 2024-06-17 at 11.43.12.png

    • To use partitioning on the dataset associated with the data connection, slide the Uses Partitioning toggle to the right.

    • Category: Enter a category of your choice.

    • Dataset Type: Select Generic.

  6. If you selected the "Google Cloud BigQuery" data connection type, complete the following tasks and fields in the Data Location and Schema section:

    • To use partitioning on the dataset associated with the data connection, slide the Uses Partitioning toggle to the right.

      Note

      If the dataset uses partitioning, the dataset can be divided into subsets so that data processing occurs only on relevant data during question runs, which results in faster processing times. When using partitioning, a temporary dataset is required to be entered below.

    • Project Id: Enter the Google Project ID.

    • Source Dataset: Enter the BigQuery source dataset.

    • Source Table: Enter the BigQuery source table name

      Note

      LiveRamp Clean Room does not support BigQuery views, only BigQuery tables.

    • Temporary Dataset: Enter the name of the temporary empty dataset you created in the "Determine Partition Columns" section above to use when partitioning columns.

      Note

      Once you've specified a temporary dataset to use when partitioning, be sure not to remove the temporary dataset or change the dataset name.

  7. If you selected the "Google Cloud Authorized View" data connection type, complete the following fields:

    LCR-Configure_BQ_Hybrid_Data_Connection-Authorized_View_fields.png

    • To use partitioning on the dataset associated with the data connection, slide the Uses Partitioning toggle to the right.

    • Category: Enter a category of your choice.

    • Dataset Type: Select Generic.

    • Authorized View: Enter the name of the authorized view.

    • Dataset: Enter the the name of the BigQuery source dataset.

    • Project Id: Enter the Google Project ID.

  8. Review the data connection details and click Save Data Connection.

    Note

    All configured data connections can be seen on the Data Connections page.

  9. If you haven't already, upload your data files to your specified location.

When a connection is initially configured, it will show "Verifying Access" as the configuration status. Once the connection is confirmed and the status has changed to "Mapping Required", map the table's fields.

You will receive file processing notifications via email.

Map the Fields

Once the above steps have been performed in Google Cloud Platform, perform the overall steps in the sections below in LiveRamp Clean Room.

Note

Before mapping the fields, we recommend confirming any expectations your partners might have for field types for any specific fields that will be used in questions.

  1. From the row for the newly created data connection, click the More Options menu (the three dots) and then click Edit Mapping.

    The Map Fields screen opens, and the file column names auto-populate.

    data_cxn_mapping_mapfields.png

  2. For any columns that you do not want to be queryable, slide the Include toggle to the left.

    Note

    Ignore the field delimiter fields because this was defined in a previous step.

  3. Click Next.

    The Add Metadata screen opens.

  4. For any column that contains PII data, slide the PII toggle to the right.

    Note

  5. Select the data type for each column.

  6. If a column contains PII, slide the User Identifiers toggle to the right and then select the user identifier that defines the PII data.

  7. For any partition columns that were defined in the "Determine Partition Columns" section above, slide the Allow Partitions toggle to the right.

  8. Click Save.

Your data connection configuration is now complete and the status changes to "Completed".

You can now provision the resulting dataset to your desired Hybrid or Confidential Computing clean rooms.

In this section: