Schema Setup

In addition to your current data, Cotera will require one dataset that it has both read and write access to, called cotera_data.

Please create this for use in the next step.

Authentication

To authenticate, Cotera requires you to create a service account for us with the relevant permissions to access your BigQuery data. We’ve provided instructions on how to do this via the console, but of course this can be done through your IaC provider (e.g. Terraform) if you prefer.

First you need to create a new service account. To do this:

1. Head to the Google Cloud console and go IAM & Admin > Service Accounts.

2. Click the Create Service Account button

3. Fill out the service account details as you wish, the below is just an example. Click create and continue when you’re done.

4. You will need to grant the BigQuery User role to the service account, which you can do in part of the service account creator. Note that this will only grant us the ability to view the schema etc at the project level. In order for us to read any of your existing data you will need to grant fine grained permission to datasets or tables in the BigQuery console. Click ‘Done’ at the bottom.

5. Head over to the BigQuery console. If you made the cotera_data dataset you will need to grant us read and write permissions on it. GCP have docs on how to do this here. However here’s a walk through with screenshots that you may find helpful. To share a dataset, click the three dots next to it, and select ‘Share’.

6. This will open a slide over on the right where you can add the permissions. Click ‘Add Principal’

app/(main)/docs/reference/data-warehouse/big-query/page.mdx

7. This opens another slideover, where you can search for the service account you made before. Select the service account and then the BigQuery User role.

8. You can now repeat this process for all datasets that you would like Cotera to read from. The only difference is that for readonly datasets you should select the BigQuery Viewer role. You can also do this at the table level if you don’t wish to share an entire dataset.

Now that the service account is configured, you need to create a JSON access key for that service account. Google’s doc’s are quite detailed on this, they’re here

Create and delete service account keys  |  IAM Documentation  |  Google Cloud

Here’s what it looks like in practice:

1. Head back to the IAM service account page and click on the service account you made for Cotera. It will open up a page with details about your service account, you want to go the ‘Keys’ tab:

2. Click the ‘ADD KEY’ button and select ‘Create new key’.

3. Select the JSON key type and hit CREATE:

4. Once you click CREATE the Google Cloud console will automatically download a JSON file containing the key - it should be in your Downloads folder!

Finally, we’re read to upload the credentials to Cotera. Head over to the settings page and go to the integrations section.

Currently, you need to copy each of the fields from the JSON key into the form provided, we’re working on a way to upload the JSON file directly!

For the Display Name field, you can put in any name you like. The read_schema and write_schema fields should both be set to cotera_data the dataset you made earlier on.