BigQuery

Google BigQuery is a data cloud platform designed to simplify building and managing data warehouse solutions.

You'll learn how to add BigQuery as a Nylas Streams.

Prerequisites

Before you begin, you need to create an integration and grants. Depending on your data needs, review the options below.

Google app, if you want to stream messages or grants from Google connected accounts.
Azure app, if you want to stream messages or grants from Microsoft connected accounts.
Zoom app, if you want to stream meeting transcripts from your connected Zoom accounts.

Create a Service Account

Create a service account to use with BigQuery.

Go to IAM & Admin > Service Accounts, then click Create Service account.
Fill out the Service account details section. This can be anything you want.
You can skip, Grant this service account access to project and Grant users access to this service account.
Keep track service account email, you'll need this in Create a BigQuery dataset.
Now, you need to create a new key. Click Actions > Manage keys.
Click Add key > Create new key.
Choose JSON. The key will automatically download. Keep this safe. You'll need it in the Create a BigQuery stream step.

Google console showing the Create service account page

Create the BigQuery Dataset

We've created a script that will set up the dataset, tables, and permissions.

Go to BigQuery in Google Console.
Run the following script to set up BigQuery for Nylas Streams.
1. Replace your_service_account with your own service account email created in Create a BigQuery Dataset. For example, [email protected].
2. Do not change the dataset name or table names. Otherwise, your Stream will not work.

Do not change the dataset name or table names!

Only edit the service account email.

-- create dataset
create schema if not exists data_streams;

-- create three tables
create table if not exists data_streams.message (
    id string,
    grant_id string,
    subject string,
    body string,
    thread_id string,
    received_at timestamp,
    _synced_at timestamp
);

create table if not exists data_streams.participant(
    message_id string,
    type string,
    name string,
    email string,
    _synced_at timestamp
  );

create table if not exists data_streams.nylas_test(
    test_data string  ,
    _synced_at timestamp
  );

-- grant proper access to the dataset and tables
grant `roles/bigquery.dataEditor`
on schema data_streams 
to "serviceAccount:your_service_account";

grant `roles/bigquery.dataEditor`
on table data_streams.message
TO "serviceAccount:your_service_account";

grant `roles/bigquery.dataEditor`
on table data_streams.participant
TO "serviceAccount:your_service_account";

grant `roles/bigquery.dataEditor`
on table data_streams.nylas_test
TO "serviceAccount:your_service_account";

BigQuery console showing the completed script and code pasted in.

Create a BigQuery Stream

You'll connect your BigQuery data to Nylas.

Select Streams > Create new Stream. On the next screen, select BigQuery.
Choose your data source.
Enter the following information:
1. Give your Stream a name.
2. Enter the Google Project ID. You can find the ID under Project info on the Google Console dashboard.
3. Copy and paste the service account JSON from Create a Service Account into Big Query Service Account Credentials.
4. Your dataset name is data_streams. This must match the schema name and can not be changed.
Create your Stream. It will take a few minutes for the Stream to activate.

Test Your Integration

Make sure you're able to receive data.

Message

If you selected message.create as your data source, create a new email message.

Grants

If you selected grants as your data source, you’ll get notifications when a grant expires or when an account is granted access to an Integration.

BigQuery showing a successful test message.

Troubleshooting

Streaming insert is not allowed on the free tier

If you try to set up a Stream on a free trial, Google will return an access denied. A billing account is required with a payment method to use streams.

Stream creation failed; Dataset notFound

The BigQuery dataset name in the dashboard must be data_streams.

Stream creation failed; Invalid dataset name

The BigQuery dataset name in the dashboard must be data_streams.

Invalid value: IAM setPolicy failed for Table

When replacing your_service_account with the service account email, remove any spaces between serviceAccount: and your_service_account. For example, TO "serviceAccount:your_service_account";