Connecting BigQuery

👤 This documentation is intended for Site Administrators and/or Database Administrators.

To connect Sisense for Cloud Data Teams to a BigQuery database, please make sure to have the following prior to attempting a connection:

• Service Account (really long email ending in @[project_id].iam.geserviceaccount.com)
• Project ID
• P12 Auth File made for service account

Here are instructions for getting each:

1: Navigate to the correct project 

Go to https://console.cloud.google.com/ and log in. Once you have logged into your Bigquery instance, navigate to the project you would like to add to Sisense in the upper-left corner. 

2: Add New Service Account

Navigate to the Service Accounts settings through the IAM & Admin. Add a new service account for Sisense for Cloud Data Teams by creating a Service account from the Google Developers Console:

‍

3: Provision the Service Account

Give the service account the one of the following sets of roles:

• Project - Viewer

To restrict access to certain datasets within the project, remove Viewer Access from Project Viewers for that dataset via the dataset share settings.

Note: It may be necessary to switch to bigquery's Classic UI to easily find these settings

4: Generate a P12 Key

Click the Furnish a new private key and generate a P12 key to authenticate the user through Sisense. The P12 key will be automatically downloaded to your browser when you create the Service Account.

Querying Google Sheets through BigQuery

To query Google Sheets through a BigQuery connection, ensure that the Service account attached to Sisense for your database is able to access the sheet. To do this, navigate to the Google Sheets Sharing settings, and add the service account as a user that can access the sheet.

Querying BigQuery Tables

BigQuery databases support two distinct SQL dialects: Legacy SQL and Standard SQL. The default dialect that Sisense will use on the database can be specified in the database connection menu.

All queries running against the BigQuery database will use the selected dialect by default. At the query level, the default selection can be overridden by including the tag #standardSQL or #legacySQL on the first line.

Changing the Bigquery SQL Dialect

If you would like to change the SQL Dialect (Standard vs Legacy), navigate to the Database tab through the Settings gear icon, change the type, and input the P12 key again to be able to submit the new connection setting. The language can be also specified for a specific query by using the token #standardSQL or #legacySQL at the beginning of the query.