Create and Implement a BigQuery Source

​

Overview

​

Concepts

​

Service Account Owner

​

Data Entities

• Customer Data — refers to a wide range of information, such as demographic, behavioural, product-related, derived and modelled information. This data is gathered from various brand interfaces, platforms, tools and services.
• Non-customer Entity Data — refers to data that is not directly tied to a user but to an attribute associated with the user. The most common forms of Non-Customer Entity Data are product catalogues, order data, feed data, campaign data, account data and so on. For more information on Data Entities supported by Zeotap CDP, refer here.

​

Prerequisites

• Source Name – This is the name you will see within the Zeotap, once you successfully create the source.
• Region– This is the region where the data needs to be stored. Currently, Zeotap supports data ingestion in the following regions:
  • EU – All EU countries’ data are ingested to the bucket provisioned for you in the EU region.
  • UK – United Kingdom’s data are ingested to the bucket provisioned for you in the UK region.
• Sync frequency – Defines how often data is ingested from the source. Zeotap currently supports the following frequencies: Sync once, Every hour, Every 3 hours, Every 6 hours, Every 12 hours, Daily, Weekly and Monthly.
• Sync Time - When you choose Daily, Weekly, or Monthly as the sync frequency, you can specify the exact time for the sync to occur.
• Sync Period - Indicates whether the selected Sync Time is in the AM or PM.
• Sync Day - If Weekly sync is selected, you can specify the day of the week on which the sync should run.
• Monthly Sync date - If Monthly sync is selected, you can specify the day of the month on which the sync should run.
• Project ID – A Project ID is a unique string that differentiates your project from all others on Google Cloud. You can use the Google Cloud console to generate a Project ID. You can find the Project ID in the Google Cloud console Dashboard page. In the below example, the Project name is My Sample Project and the Project ID is my-sample-project-191923.

• Dataset – Datasets are top-level containers that are used to organise and control access to your tables and views. In the below example, the Dataset is GBQ_dataset.

• Table – A BigQuery table contains individual records organised in rows. Each record is composed of columns/fields. Every table is defined by a schema that describes the column names, data types and other information. In the below example, the tables are Demotest, DemotestBQ, Table006, Table007 and TableBQ. Currently, we do not support tables with JSON column types.

• Service Account Owner – Select the Service Account Owner based on your requirement. You can choose either Customer or Zeotap as the Service Account Owner. For more information about Service Account Owner, refer to Concepts.
  • If you select Customer as the Service Account Owner, a section appears where you have to upload the JSON key file that you have created. Click + SELECT FILE to upload the JSON key file. To know about how to create a JSON key file, refer here.
  • If you select Zeotap as the Service Account Owner, then you can directly proceed to the Data Entity selection step.
• Data Entity – Zeotap supports the following two groups of data entities. For more information about Data Entities refer to Concepts.
• Service Account Key JSON – The Service Account Key JSON is necessary to use a service account from outside of Google Cloud, such as on other platforms or on-premises. For information on creating a Service Account Key JSON on the Google Cloud console, refer here.

{
  "type": "service_account",
  "project_id": "PROJECT_ID",
  "private_key_id": "KEY_ID",
  "private_key": "-----BEGIN PRIVATE KEY-----\nPRIVATE_KEY\n-----END PRIVATE KEY-----\n",
  "client_email": "SERVICE_ACCOUNT_EMAIL",
  "client_id": "CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL"
}

​

Get Started

1. Create a Google BigQuery Source within Zeotap CDP.
2. Integrate the Created Source With the Google BigQuery platform.

​

Step 1 - Create a Google BigQuery Source Within Zeotap CDP

1

Navigate to the Sources application in the Zeotap CDP App.

2

Click CREATE SOURCE.

3

Choose Data Warehouse as the Category.

4

Click Google BigQuery at the Data Source.

5

Enter the below information based on your BigQuery account. Note that the information about how to procure these details are mentioned in the Prerequisites section.a. Sync frequencyb. Project IDc. Datasetd. Table

6

Select the Service Account Owner based on your requirement. You can choose either Customer or Zeotap as the Service Account Owner.a. If you select Customer as the Service Account Owner, a section appears where you have to upload the JSON key file that you have already created. Click + SELECT FILE to upload the JSON key file. To know about how to create a JSON key file, refer here.

b. If you select Zeotap as the Service Account Owner, then you can directly proceed to the Data Entity selection step.

7

Under Data Entity, depending on the type of data that you want to ingest, choose either Customer Data or Non Customer Data and proceed with the source creation. To know more about Customer Data and Non Customer Data, refer here.

8

Under Delta Queries Selection, you can decide whether or not you want to consider deltas (data additions/changes) in a dataset for a specific time period based on the values of the timestamp column. Based on your requirement, select either true or false.

a. If you select true, then the fields, Delta Column Name and Delta Column Data Type get enabled, where you must provide the following details:i. Under Delta Column Name, provide the name of the column that you want to fetch data from.ii. Under Delta Column Data Type, choose the time increment for fetching the data from the selected column.

b. If you select false, then you are not required to make any additional selections. You can directly proceed to the source creation step.

9

Click CREATE SOURCE.

​

Step 2 - Integrate the Created Source with the Google BigQuery Platform

​

Scenario 1

1

In the IMPLEMENTATION DETAILS tab, under Access Details, copy the Service URL.

2

Go to the Google BigQuery console and open your project.

3

On your project, click the three dots and click Open in > New tab.

4

In the new tab, click SHARING and then click Permissions.

5

Click ADD PRINCIPAL.

6

Under Add principals, in the New principals field, paste the Service URL that you have copied earlier.

7

Under Assign roles, choose BigQuery > BigQuery Data Viewer.

8

Click SAVE.

9

Return to the IMPLEMENTATION DETAILS tab. Besides the Service URL field, click TEST CONNECTION.

10

Upon successful Integration, a message appears confirming that the connection is successfully established, allowing you to begin data ingestion.

​

Scenario 2

​

Related Topics

• For information about how to map the Catalogue for Customer Data (CE), refer here.
• For information about how to map the Catalogue for Non-Customer Entity (NCE) data, refer here.
• For information about how to add a Non-Customer Entity (NCE) attribute in the Catalogue, refer here.
• For information about Join Key Enricher and how to configure it, refer here.