👤 This documentation is intended for Site Administrators and/or Database Administrators.
To connect Periscope Data 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 Periscope 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 Periscope Data 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 Periscope. 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 Periscope 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 Periscope 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.