> For the complete documentation index, see [llms.txt](https://developer.mediarithmics.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://developer.mediarithmics.io/advanced-usages/data-warehouse-management/create-a-data-warehouse/bigquery.md).

# BigQuery

## Overview

When exposing your data to mediarithmics we recommend the following structure : 

* A BigQuery project that contains : 
  * **One or multiple datasets** with data you want to expose to mediarithmics with read-only access
  * **2 datasets specifically created for mediarithmics usage** with read and write access.

{% hint style="info" %}
You do not have to expose all tables/views in a given dataset. Refer to [this section](/advanced-usages/data-warehouse-management/create-a-data-warehouse/bigquery.md#grant-access) for more information
{% endhint %}

This page describes how to set up a service account with such access. 

## Steps

1. Enable the Cloud Resource Manager API
2. Create a service account with the right set of permissions
3. Create dedicated datasets for mediarithmics usage
4. Generate a credentials JSON file

### 1. Enable the 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. 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 IAM & Admin>Service Accounts
* Select you project
* Click on “Create service account”

<figure><img src="/files/B9lpZxwv1l97IwNiORqE" 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="/files/Vgz11U6kkGxok70gtJPS" 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>

{% 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 %}

### 3. Create dedicated datasets for mediarithmics usage

We recommend to create two dedicated datasets for mediarithmics' usage.&#x20;

* **mics\_workspace** dataset that holds all temporary tables created while syncing with your warehouse
* **mics\_output** dataset that holds all output tables — the clean results of daily processing, ready for consumption

{% hint style="info" %}
Note that the tables in mics\_output are not meant to be interface tables : their schema could change with successive releases. If you need a specific stable table schema to expose it to other tools, please contact your account manager.&#x20;
{% endhint %}

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

CREATE SCHEMA IF NOT EXISTS `project-name.mics_output`
OPTIONS (
  location = 'EU',
  description = 'Output schema for mediarithmics'
);
```

{% hint style="info" %}
Nota bene : If you wish to use a [synchronization strategy](/data-ingestion/data-warehouse-ingestion.md#synchronization-strategies) that handles updates and deletes of data, you will need a [working dataset](/data-ingestion/data-warehouse-ingestion.md#working-schema) (i.e. the **mics\_workspace** dataset)
{% endhint %}

The service account you created in step 3, should have the following role on the **mics\_output** and **mics\_workspace datasets**

* <mark style="color:red;">`bigquery.dataEditor`</mark>

### 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="/files/GoDcWs0XtD40lpqgV434" 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 %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://developer.mediarithmics.io/advanced-usages/data-warehouse-management/create-a-data-warehouse/bigquery.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.