Configuring Splunk DB Connect for use with Google BigQuery

Create a service account

1. Export several environment variables for use throughout the configuration instructions.

   export SA_NAME=dbx-bq
   export PROJECT_ID=<PROJECT_ID>
   export KEY_FILE=dbx-bq-sa.json
   export SA_EMAIL=${SA_NAME}@${PROJECT_ID}.iam.gserviceaccount.com

2. Create a service account in a Google Cloud project.

   gcloud iam service-accounts create ${SA_NAME} --project=${PROJECT_ID}
   

3. Export a JSON credential for this new service account.

   gcloud iam service-accounts keys create ${KEY_FILE} --iam-account=${SA_EMAIL}
   

4. Grant this service account the bigquery.user role within the project scope. Optionally, to use the dbxoutput command provided by DB Connect, you can grant bigquery.admin instead. Note that the bigquery.user role is sufficient to use the dbxquery command.

   gcloud projects add-iam-policy-binding ${PROJECT_ID} --member=serviceAccount:${SA_EMAIL} --role=roles/bigquery.user
   

5. In order to use the JSON credential within the {{platform}, you need to flatten the JSON into a single line. The easiest way to do this is using the jq utility:

   jq -c . ${KEY_FILE}
   

6. This should output the JSON on a single line similar to the output below.

   {"type":"service_account","project_id":"<REDACTED>,"private_key_id":"e580bb185173189d15a653408a24957ffb8d2872","private_key":"-----BEGIN PRIVATE KEY-----\n<REDACTED>\n-----END PRIVATE KEY-----\n","client_email":"dev-dbx-bq-sa@<REDACTED>.iam.gserviceaccount.com","client_id":"109787105253850867414","auth_uri":"https://accounts.google.com/o/oauth2/auth","token_uri":"https://oauth2.googleapis.com/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/dev-dbx-bq-sa%40<REDACTED>.iam.gserviceaccount.com","universe_domain":"googleapis.com"}
   

7. Copy and paste this flattened JSON into a text file so you can reference it later in your DB Connect configuration.

Configure DB Connect

To prepare DB Connect for a BigQuery connection, you must first create a placeholder identity and a new connection.

Create an identity

Within the Splunk DB Connect App, navigate to Configuration > Databases > Identities. Click New Identity and select Basic Identity.

For the Identity Name, Username, and Password fields, enter bq and then click Save.

Create a connection

1. Navigate to Configuration > Databases > Connections. Click New Connection.
2. Enter bq as the connection name. Select bq as the Identity, BigQuery as the Connection Type, and Etc/UTC for Timezone.
3. Place a check mark next to the Edit JDBC URL option. This allows you to edit the JDBC connection URL and inject a service account credential.
4. To create the custom JDBC URL, use the following format with the changes described below:

   jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;EnableSession=1;ProjectId=<YOUR-PROJECT>;OAuthType=0;OAuthServiceAcctEmail=<YOUR-SERVICE-ACCOUNT-EMAIL>;OAuthPvtKey=<JSON-SERVICE-ACCOUNT-KEY>;
   

   • Substitute your project ID for <YOUR-PROJECT>.
   • Substitute the email of the service account created in the account creation steps from the previous section for<YOUR-SERVICE-ACCOUNT-EMAIL>.
   • Substitute the single-line version of the service account key you previously reformatted with the jq command for the<JSON-SERVICE-ACCOUNT-KEY>.
   • Ensure the JDBC URL ends with a semicolon.
   
   Here is a completed example:

   jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;EnableSession=1;ProjectId=my-project-151018;OAuthType=0;OAuthServiceAcctEmail=dev-dbx-bq-sa@my-project-151018.iam.gserviceaccount.com;OAuthPvtKey={"type":"service_account","project_id":"my-project-151018","private_key_id":"8715a33233d1d7f3a1f8c6138eb780b474c67422","private_key":"-----BEGIN PRIVATE KEY-----\n<REDACTED>-----END PRIVATE KEY-----\n","client_email":"dev-dbx-bq-sa@gsa-project-151018.iam.gserviceaccount.com","client_id":"109787105253850867414","auth_uri":"https://accounts.google.com/o/oauth2/auth","token_uri":"https://oauth2.googleapis.com/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/dev-dbx-bq-sa%40my-project-151018.iam.gserviceaccount.com","universe_domain":"googleapis.com"};
   

   

5. Ignore the Advanced settings and click Save to finish.