Databases

Amazon Athena

The AWS Access Key and Access Secret should be for the AWS user you want to connect through. When creating an Athena connection, we recommend creating a user in AWS that only has access to the data you want to query through Athena. For data you want to query, the AWS user only needs read access.

The S3 Results Directory should be a bucket where Athena can write query results. For this bucket, the AWS user specified above needs read and write access. The bucket should be a full S3 url (e.g., s3://acme/my/athena/bucket)

Click Connect. You will land on a page that displays your database schema.

Note: If you are using VPC Security Groups, you must also grant Mode access to that security group. Please click on your VPC Security Group name and follow this section of our Amazon RDS guide, starting with step 2.

Click on the name of the security group.

You’ll see a list of authorized connections. Click the blue “Add Connection Type” button in the upper left.

Select “CIDR/IP” from the “Connection Type” dropdown, and paste the address 54.68.30.98/32 in the “CIDR/IP to Authorize” field.

Click the blue “Authorize” button

Repeat steps 5 through 7 for each of the addresses listed below:

54.68.30.98/32

54.68.45.3/32

54.164.204.122/32

54.172.100.146/32

Amplitude Redshift

Connecting to an Amplitude Redshift instance is very similar to connected to a generic Redshift database.

Once you have an Enterprise-level account with Amplitude, your Customer Success Manager will provide you with your Redshift credentials for logging into your database. If you need your credentials, you can email support@amplitude.com.

Google BigQuery

Creating a service account to use with Mode

To connect Mode to your BigQuery project, you will need to create a service account for Mode to use.

Click on the dropdown to the left of the search bar and select the project you wish to connect.

Click on the hamburger menu in the upper left and select IAM & Admin.

Click on the “Service accounts” tab and click the blue “Create service account” button.

In the dialogue box, name your new service account. Choose Big Query -> Big Query User and Big Query Data Viewer for the roles for this service account. Click the checkbox to furnish a new private key and select the P12 key type. Clicking Create will create your service account and automatically download your key.

A dialogue will appear saying the service account has been created. You can safely close the dialogue.

Return to Mode, and begin the process to connect a BigQuery database. When you see the form to enter your BigQuery credentials, first enter the database’s display name.

In the “Project ID” field, enter the BigQuery project ID. It can be found in the URL of your Google Developers Console. The URL should be structured like this: https://console.developers.google.com/apis/library?project=MY_PROJECT_ID

In the “Service Account Email Address” field in Mode, add the email address associated with the service account you created. This email address can be found on the service accounts page in step 4, under “Service account ID.”

Finally, under the “Key” field, upload the key that was downloaded on step 5. Click Connect to complete the connection.

Organizations connected to a Google BigQuery data source have the option to analyze data in their BigQuery database using two different versions of SQL: BigQuery legacy SQL, or standard SQL. Standard SQL is more similar to most conventional versions of SQL and compliant with the SQL 2011 standard.

Standard vs. Legacy SQL

When connecting your BigQuery data source, you will choose a default SQL dialect. Regardless of your choice, users can specify which dialect they would like to use on a query-by-query basis by prefacing their query with #standardSQL or #legacySQL in the Query Editor.

An organization admin can change the default SQL preference a BigQuery data source at any time by following these steps:

Note: When you change this setting queries written in the previous SQL style may break. For example if you switch your default to Standard SQL, queries written in the legacy SQL style may break. However, you can easily fix the broken SQL queries by simply prefacing the query with a simple hashtag—in the example above, by adding #legacySQL.

Use BigQuery to query Google Sheets

You can use Mode to query Google Sheets in BigQuery. The first step, enabling your Google Drive API, only needs to be done once.

1. Enable Google Drive API

Navigate to the Google Developer Console and select your project from the project dropdown menu. From the left menu, click on Library. Under Google Apps API, click on Drive API to enable the API from that page.

In the navigation, hover over a schema name and click the down arrow icon next to the name. Choose Create new table.

Select Google Drive for the new table’s location and paste the Google Sheet URL (not the shared link but the actual URL you use to view the Sheet) into this field. Choose Google Sheets as the Format. Then add all the column names and types from your Google Sheet under Schema. Click Create Table when ready.

3. Enable Google Sheets for Mode Use

For Mode to query your new table, share your Google Sheet with the service account email address you used to connect Mode to BigQuery. You can also share folders in your drive with this email address, which will allow Mode to query every sheet in that folder.

To get your service account email address, go to your Google Cloud Console, switch to your Project, and click on Service Accounts. Copy the service account email address you used to connect Mode to BigQuery.

To share a sheet or folder, click Share on the Google Sheet or in the folder, and paste the service account email address into the Share email field.

Note that BigQuery schemas don’t refresh automatically in Mode. To see this table appear in your schema browser, go to your Mode Settings page and click Data Sources. Choose your Big Query data source, and click the green Refresh button in the upper right corner to update the schema browser in Mode.

HeapSQL

Once you’ve signed up for Heap and enabled Heap SQL, your Customer Success Manager will provide you with your Redshift credentials for connecting to your database. If you need your Redshift credentials, please email support@heapanalytics.com for help!

Once you have your credentials, follow the steps below to connect Mode:

Log in to Mode and follow to steps to connect a database with Direct Connect.

Treasure Data

Cloud environments

Amazon Web Services

Overview

We have two ways of connecting to your RDS instance depending on your AWS settings. If your
instance is publicly accessible then Mode can connect directly to it. For databases that are
not accessible our connection Bridge will need to be installed. We’ll start by determining
if your database is accessible to us.

Accessibility

Click the arrow and then magnifying glass view the details of the instance that you want to connect

Look under “Security and Network”. Your instance is in a VPC if there’s a entry labeled VPC. It is typically found below Availability Zone and above Security Groups. If you don’t have an entry here then your instance is not in a VPC and you can jump to the “Security Groups” section below.

Instances in VPCs can be public or private. We can find out by looking under “Security and Network” again. This time for an entry labeled Publicly Accessible. If it is followed by a Yes then you can jump to the “Security Groups” section below.

To connect to instances in a private VPC you’ll need to install our bridge connector.

Security Groups

All connections from Mode will come from one of the four IP addresses below. In most cases you’ll need to add these addresses to your RDS instance’s Security Group. We’ve broken the steps down into two sections: VPC and No VPC. Security Groups in a VPC are managed the same between EC2 and RDS. RDS instances outside of a VPC have a different process.

Addresses

54.68.30.98/32

54.68.45.3/32

54.164.204.122/32

54.172.100.146/32

VPC

Under “Security and Network” click the security group name.

A new tab will be opened. Click on the “Inbound” tab and then “Edit”.

Find the RDS type that matches your instance (e.g. Postgres), enter each of our addresses and then click “Save”.

No VPC

Under “Security and Network” click the security group to which that database belongs.

At the bottom of the page, highlight the “Connection Type” selector and choose “CIDR/IP”