Connectors and Connections > Data Ingestion and Replication connection properties > Google BigQuery V2 connection properties

Google BigQuery V2 connection properties

Create a Google BigQuery V2 connection to securely read data from or write data to Google BigQuery.

Connect to Google BigQuery

Let's configure the Google BigQuery V2 connection properties to connect to Google BigQuery.

Before you begin

Before you configure a connection, ensure that you download the Google service account key file in JSON format. The service account key file is created when you create a Google service account.

You require the client email, private key, and project ID from the service account key JSON file to create a Google BigQuery connection.

Connection details

The following table describes the basic connection properties:

Property	Description
Connection Name	Name of the connection. Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -, Maximum length is 255 characters.
Description	Description of the connection. Maximum length is 4000 characters.
Use Secret Vault	Stores sensitive credentials for this connection in the secrets manager that is configured for your organization. This property appears only if secrets manager is set up for your organization. This property is not supported by Data Ingestion and Replication and the Data Access Management services. When you enable the secret vault in the connection, you can select which credentials that the Secure Agent retrieves from the secrets manager. If you don't enable this option, the credentials are stored in the repository or on a local Secure Agent, depending on how your organization is configured. Note: If you’re using this connection to apply data access policies through pushdown or proxy services, you cannot use the Secret Vault configuration option. For information about how to configure and use a secrets manager, see Secrets manager configuration.
Runtime Environment	The name of the runtime environment where you want to run tasks. For application ingestion and replication tasks and database ingestion and replication tasks that have a supported source type, you can use the serverless runtime environment. You cannot run an application ingestion and replication task, database ingestion and replication task, or streaming ingestion and replication task on a Hosted Agent or elastic runtime environment. For more information about how to configure and use the runtime environments, see Runtime environments.

Authentication types

You can configure Service Account or Workload Identity Federation authentication method to access Google BigQuery. When you use Workload Identity Federation authentication method, you can configure Okta as the identity provider.

Select the required authentication method and configure the authentication-specific parameters.

Default is Service Account authentication method.

Service Account authentication

Service Account authentication requires at a minimum your Google BigQuery service account email, service account key, and project ID.

The following table describes the basic connection properties for Service Account authentication:

Property	Description
Service Account Email	The client_email value from the Google service account key JSON file.
Service Account Key	The private_key value from the Google service account key JSON file. To use the private key from the JSON file, enter the entire block of text found within the double quotation marks in the private_key field, starting with -----BEGIN PRIVATE KEY-----\n and ending at -----END PRIVATE KEY-----\n.
Project ID	The project_id value from the Google service account key JSON file. If you have created multiple projects with the same service account, enter the ID of the project that contains the dataset that you want to connect to.

Note: If you want to validate the credentials for the Service Account Email, Service Account Key, and Project ID during a test connection, set the flag CredentialValidation:true in the Provide Optional Properties field in advanced settings.

Advanced settings

The following table describes the advanced connection properties for Service Account authentication:

Property	Description
Storage Path	Path in Google Cloud Storage where the agent creates a local stage file to store the data temporarily. The agent uses this storage when it reads data in staging mode or writes data in bulk mode. Use one of the following formats: - gs://<bucket_name> - gs://<bucket_name>/<folder_name> When you enable cross-region replication in Google BigQuery, enter a Google Cloud Storage path that supports dual region storage.
Connection Mode	The mode that you want to use to read data from or write data to Google BigQuery. Select one of the following connection modes: - Simple. Flattens each field within the Record data type field as a separate field in the mapping. - Hybrid. Displays all the top-level fields in the Google BigQuery table including Record data type fields. Google BigQuery V2 Connector displays the top-level Record data type field as a single field of the String data type in the mapping. - Complex. Displays all the columns in the Google BigQuery table as a single field of the String data type in the mapping. Default is Simple.
Use Legacy SQL for Custom Query	Select this option to use legacy SQL to define a custom query. If you clear this option, use standard SQL to define a custom query. This property doesn't apply if you configure the Google BigQuery V2 connection in hybrid or complex mode.
Dataset Name for Custom Query	The name of the Google BigQuery dataset that you want to specify in a custom query.
Schema Definition File Path	Directory on the Secure Agent machine where the Secure Agent creates a JSON file with the sample schema of the Google BigQuery table. The JSON file name is the same as the Google BigQuery table name. Alternatively, you can specify a storage path in Google Cloud Storage where the Secure Agent creates a JSON file with the sample schema of the Google BigQuery table. You can download the JSON file from the specified storage path in Google Cloud Storage to a local machine. The schema definition file is required if you configure complex connection mode in the following scenarios: - You add a Hierarchy Builder transformation in a mapping to read data from relational sources and write data to a Google BigQuery target. - You add a Hierarchy Parser transformation in a mapping to read data from a Google BigQuery source and write data to relational targets.
Region ID	The region name where the Google BigQuery dataset that you want to access resides. Note: Ensure that you specify a bucket name or the bucket name and folder name in the Storage Path property that resides in the specified region. For more information about the regions supported by Google BigQuery, see Dataset locations.
Staging Dataset	The Google BigQuery dataset name where you want to create the staging table to stage the data. You can define a Google BigQuery dataset that is different from the source or target dataset.
Provide Optional Properties	Comma-separated key-value pairs of custom properties in the Google BigQuery V2 connection to configure certain source and target functionalities. For more information about the list of custom properties that you can specify, see Optional Properties configuration Knowledge Base.

Proxy server settings

If your organization uses an outgoing proxy server to connect to the Internet, the Secure Agent connects to Informatica Intelligent Cloud Services through the proxy server.

You can configure the Secure Agent to use the proxy server on Windows and Linux. You can use the unauthenticated or authenticated proxy server.

To configure the proxy settings for the Secure Agent, perform the following tasks:

•Configure the Secure Agent through the Secure Agent Manager on Windows or shell command on Linux.

For instructions, see Configure the proxy settings on Windows or Configure the proxy settings on Linux.

•Configure the JVM options for the DTM in the Secure Agent properties. For instructions, see the Proxy server settings Knowledge article.

The proxy settings configured in the proxy.ini configuration file are applicable at design time, and the proxy settings configured through the JVM options are applicable at runtime.

Configure proxy settings for NTLM authentication

You can use a proxy server that uses NTLM authentication to connect to Google BigQuery. To configure the proxy settings for NTLM authentication, perform the following steps:

1In Administrator, select Runtime Environments.
2Select the Secure Agent for which you want to configure from the list of available Secure Agents.
3In the upper-right corner, click Edit.
4In the System Configuration Details section, select the Type as DTM for the Data Integration Server.
5Edit the JVMOption1 and add the following value:

-Dhttp.auth.ntlm.domain=<domain name>

6Select the Type as Platform for the Data Integration Server.
7Edit the INFA_DEBUG property and add the following value:

-Dhttp.auth.ntlm.domain=<domain name>

8Click Save.
9Restart the Secure Agent.