Connect BigQuery with a Service Account

Connecting BigQuery with a service account involves using the service account credentials to authenticate and authorize access to BigQuery. This allows C3 AI applications to interact with BigQuery without requiring individual user credentials.

Copy link to this section

Prerequisites

Verify that your project includes the necessary dependencies or libraries that provide the SqlSourceSystem class.

Copy link to this section

Set up Connection to Google BigQuery using a service account

Establish a secure connection to Google BigQuery using a service account for the SQL source system "TestExternalSystem2".

1. Create a service account:

   a. Go to the Google Cloud Console.

   b. Navigate to IAM & Admin > Service Accounts.

   c. Click Create Service Account.

   d. Fill in the necessary details and click Create.

   e. Assign the required roles (e.g., BigQuery Data Viewer).

   f. Download the JSON key file.

2. Prepare the Service Account JSON:

   a. Open the downloaded JSON key file.

   b. Copy the contents of the file.

3. Set up variables in your code:

   a. Define the name of the SQL source:

   Type

   Copy code to clipboard

   system:sysName = 'TestExternalSystem2'

   b. Store the service account JSON credentials:

   Type

   Copy code to clipboard

   variable:content = `{
   "type": "service_account",
   "project_id": "your_project_id",
   "private_key_id": "your_private_key_id",
   "private_key": "your_private_key",
   "client_email": "your_client_email",
   "client_id": "your_client_id",
   "auth_uri": "your_auth_uri",
   "token_uri": "your_token_uri",
   "auth_provider_x509_cert_url": "your_auth_provider_x509_cert_url",
   "client_x509_cert_url": "your_client_x509_cert_url"
   }`

   c. Set the JDBC URL:

   Type

   Copy code to clipboard

   BigQuery:url = "jdbc:googlebigquery:your_connection_string"

Copy link to this section

Configure JDBC credentials for Google BigQuery using OAuthJWT

Set up and configure JDBC credentials to securely connect to Google BigQuery using OAuth 2.0 with a JSON Web Token (JWT) for authentication.

1. Define authentication properties:

   Create a properties dictionary to specify the authentication scheme and include the service account JSON credentials.

   JSON

   Copy code to clipboard

   properties = {
   "AuthScheme": "OAuthJWT",
   "OAuthJWTCertType": "GOOGLEJSONBLOB",
   "OAuthJWTCert": content
   }

2. Build JDBC credentials:

   Use the JdbcCredentials.builder() method to construct the credentials object with the necessary URL, properties, and datastore type.

   JavaScript

   Copy code to clipboard

   creds = JdbcCredentials.builder().url(url)
                                      .username("******")
                                      .password("******")
                                      .properties(properties)
                                      .datastore("googlebigquery").build();

3. Set credentials in JDBC store:

   Retrieve the JDBC store for the specified system name and set the credentials.

   JavaScript

   Copy code to clipboard

   JdbcStore.forName(sysName).setCredentials(creds, ConfigOverride.APP);

4. Mark JDBC store as external:

   Indicate that the JDBC store connects to an external data source.

   JavaScript

   Copy code to clipboard

   JdbcStore.forName(sysName).setExternal(ConfigOverride.APP);

5. Test the connection:

   Verify the connection to Google BigQuery by running a test query to ensure the setup is correct and functioning as expected.

Copy link to this section

Connect to Google BigQuery using SqlSourceSystem

Utilize the SqlSourceSystem to establish a connection to Google BigQuery and list the available table names.

1. Retrieve the SqlSourceSystem:

   Use the forName method to get the SqlSourceSystem instance for the specified system name.

   JavaScript

   Copy code to clipboard

   sys = SqlSourceSystem.forName(sysName)

2. Establish the connection:

   Call the connect method on the SqlSourceSystem instance to establish a connection to Google BigQuery.

   JavaScript

   Copy code to clipboard

   sys.connect()

3. List table names:

   Use the listTableNames method to retrieve and display the names of the tables available in the connected BigQuery dataset.

   JavaScript

   Copy code to clipboard

   sys.connect().listTableNames()

4. Verify the connection:

   Ensure that the connection is successful and the table names are correctly listed. This step helps confirm that the setup and connection are functioning as expected.