# BigQuery

## Steps

1. Make sure the BigQuery project has the correct API enabled
2. Create a working schema
3. Create a dedicated service account with the right set of permissions
4. Generate a credentials JSON file

### 1. Enable Cloud Resource Manager API

* In the Google Cloud Console, navigate to the APIs & Services > Library page.
* In the search bar, type "Cloud Resource Manager API".
* Click on the search result for the Cloud Resource Manager API and click the Enable button

### 2. Create a working schema

If you wish to use a synchronization strategy that handles updates and deletes of data, you will need a [working schema](https://developer.mediarithmics.io/data-streams/data-warehouse/preliminary-setup/..#working-schema). In that case, you should create a dedicated dataset in your project. You can use the following query:

```sql
CREATE SCHEMA IF NOT EXISTS `project-name.mediarithmics_dataset`
OPTIONS (
  location = 'EU',
  description = 'Working schema mediarithmics'
);
```

### 3. Service account creation and permissions

We recommend using a dedicated service account with the appropriate set of permissions.

To create a service account follow these steps :

* Go to <https://console.cloud.google.com/iam-admin/serviceaccounts>
* Select you project
* Click on “Create service account”

<figure><img src="https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FoxUR5i7IfdPp7GQvZWNF%2FCapture%20d%E2%80%99%C3%A9cran%202025-04-23%20165744%20(1).png?alt=media&#x26;token=fbf4e52a-f552-45a0-bb2b-7677a79930a9" alt=""><figcaption></figcaption></figure>

* Input the necessary informations : service account name, service account id (automatically generated), description. Click on “Create and continue”

<figure><img src="https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FcGHefB03lgAY4zjubsR1%2Fimage%20(6).png?alt=media&#x26;token=15ed6980-cf02-480f-ace3-e16c55c2455a" alt=""><figcaption></figcaption></figure>

#### Grant access&#x20;

* We recommend giving the service account Read only access to your data by giving the following roles at the **project level** :&#x20;
  * <mark style="color:red;">`bigquery.jobUser`</mark>&#x20;
  * <mark style="color:red;">`bigquery.dataViewer`</mark>
* In addition, if applicable, the service account should also have the following role **only on the working schema :**
  * <mark style="color:red;">`bigquery.dataEditor`</mark>

{% hint style="info" %}

#### Grant limited access

If you do not want to give the <mark style="color:red;">`bigquery.dataViewer`</mark> role at the project level, you can assign it only to specific datasets, tables or views in your project. If you do that then you should add the following permissions at the **project** level :&#x20;

* <mark style="color:red;">`bigquery.datasets.get`</mark>
* <mark style="color:red;">`bigquery.tables.list`</mark>
* <mark style="color:red;">`bigquery.tables.get`</mark>

To assign these customs permissions you should create a [custom role](https://cloud.google.com/iam/docs/creating-custom-roles#creating) than carries the <mark style="color:red;">`bigquery.jobUser`</mark> role and the <mark style="color:red;">`bigquery.datasets.get`</mark> <mark style="color:red;">`bigquery.tables.list`</mark> <mark style="color:red;">`bigquery.tables.get`</mark> permissions.

If the <mark style="color:red;">`bigquery.dataViewer`</mark> role is even too much, you can assign the <mark style="color:red;">`bigquery.tables.getData`</mark> permission at a table level only.&#x20;
{% endhint %}

### 4. Generate a credentials JSON file

To export a credentials JSON file follow these steps :

* Go to <https://console.cloud.google.com/iam-admin/serviceaccounts>
* Select you project
* Select your service account and on the “…” menu select “Manage keys”
* Then click on “Add key”>”Create new key”

<figure><img src="https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FQtI0irAzLy46aIagkVNP%2FCapture%20d%E2%80%99%C3%A9cran%202025-04-24%20093106%20(1).png?alt=media&#x26;token=07955ac2-ba6f-456a-a334-6d2afa7c793f" alt=""><figcaption></figcaption></figure>

* Select JSON then “Create”
* The key is automatically downloaded

{% hint style="warning" %}
Note you need to have at least the <mark style="color:red;">`roles/iam.serviceAccountKeyAdmin`</mark> role to perform these actions
{% endhint %}