Configure the Google BigQuery target

Complete the procedure below to configure the Google BigQuery target and verify that you can write to the Google BigQuery resource.

• In the Google Cloud console, under IAM, give the GCP service account the following minimum permissions on the project:

  Note: For more information on Google IAM roles and permissions, see https://cloud.google.com/iam/docs/understanding-roles and https://cloud.google.com/iam/docs/creating-custom-roles.

  • bigquery.datasets.create

  • bigquery.datasets.get

  • bigquery.datasets.update

  • bigquery.tables.create

  • bigquery.tables.list

  • bigquery.tables.delete

  • bigquery.tables.get

  • bigquery.tables.update

  • bigquery.tables.updateData

  • bigquery.jobs.create

  • Alternatively, you can assign the GCP service account the roles/bigquery.dataOwner and roles/bigquery.jobUser roles which include the minimum permissions.

• Create the Google BigQuery target before you configure it. For more information, see Add a publish target.

1. On the Administration page, click the Targets tab. Then select the target in the Publish Targets list.

   Note: If you have already selected your target, skip to the next step.

2. Refer to the following table and enter the required information. Click the buttons as they become enabled.

   Parameter	Description
   GCP Service Account Key File	JSON file that contains the GCP service account credentials.
   BigQuery Project ID	Unique ID for your BigQuery project in the Google Cloud Platform (GCP)
   BigQuery Dataset	Top-level container that organizes and controls access to tables and views. The dataset is contained within a project and must be created before data can be streamed into BigQuery.
   Upload Page Size	(Optional) Number of rows that are uploaded in a bulk insert. The default, 500, is the recommended size.

3. Click Verify GCP BigQuery Writer.

4. Click Save Changes.

5. Give users access to the Google BigQuery target. For more information, see Grant access to targets.

   Note: No record on a Google BigQuery instance can be modified within 90 minutes after being written or updated. For PI Integrator Sync service to continue behaving as expected (and not encounter this limitation on views writing to BigQuery), the AFMonitorUpdateFrequency is recommended to be set in the %PIHOME64%\Integrators\BA\CAST.UI.WindowsService.exe.config to 120 minutes as follows: <add key="AFMonitorDataUpdateFrequency" value="120"/>.

The extra time buffer (120-minute configuration versus 90-minute limitation), is to provide a wide enough window for Google BigQuery to have flushed the streaming buffer. Without this buffer, you could accidentally encounter the limitation by setting the AFMonitorUpdateFrequency to the same time as the limitation (90 minutes.) If the buffer is set correctly, you will see the following message at application startup:

UnifiedAppConfig:AFMonitorDataUpdateFrequency: Successfully parsed.

AFMonitorDataUpdateFrequency value from 'finalAppConfigFilePath'.

Using AFMonitorDataUpdateFrequency) value, '120'(minutes).

If the buffer is not set correctly and the limitation is encountered, either by clicking Update Data in the UI or by being triggered by PI Integrator Sync Service without AFMonitorUpdateFrequency being configured properly, you will see the following error message:

UpdateData operation halted due to PublishTarget restriction (*-90min maximum EndTime).

This PublishTarget, GcpBigQueryWriter, does not support deleting/updating data rows that have been modified within the last 90 minutes.
Result: No data has been changed on the PublishTarget.