# Connecting to Google BigQuery
### **Loading from table**
Ensure the table's schema is compatible with Tellius for seamless integration and data loading.
### **Loading from external tables**
The external tables refer to data that is stored outside of BigQuery. For external tables, we suggest using a query (like 'SELECT \* FROM external\_table') to load the table.
This is important because external tables differ from regular tables in that they do not have a storage layer within BigQuery. Instead, the table data is stored externally, with only the metadata layer present in BigQuery.
### **Loading from View**
By default, BigQuery does not materialize loading data from Views. It is necessary to materialize the View first, by saving the output to a separate table and then loading the data from that table.
The intermediate table is a temporary table with a Time-To-Live (TTL) of 24 hours, and it will be deleted once the TTL ends. The TTL can be set to less than 24 hours. For this integration, it is recommended to build a separate materialization dataset (with full write access to Tellius).
### **Loading from SQL query**
Loading data from an SQL query is similar to loading data from a View. The results of the query are saved in an intermediate, temporary table, from which the data is loaded.
The following details are required from the user to load data from external tables or Views or SQL queries:
* Materialization project
* Materialization dataset
### **Pre-requisites for configuring BigQuery in Google Cloud**
#### **Service Account Creation**
1. In the Google Cloud console, click on **IAM and Admin** section on the left pane.
2. Choose **Service accounts** and select the required project.
3. Click on **+ Create service account** on the top pane.
4. Enter the name, ID, and description of the service account.
5. Copy the **service account email address** (which is of the format \@\.iam.gserviceaccount.com)
Creating a service account
6. Click on **Done** to create the service account.
7. Copy the service account email, which will be in the following format: \@\.iam.gserviceaccount.com
8. Select the required service account.
9. Navigate to the **Keys** section at the top and click on **Add Key**.
Adding Keys
10. Under **Key type,** choose the **JSON** option and click on **Create**.
Creating JSON key
11. When prompted to download the file, save the file to upload it later to Tellius.
12. Verify whether the JSON file consists of the following structure:
```json
{
"type": "service_account",
"project_id": "",
"private_key_id": "random_id",
"private_key": "-----BEGIN PRIVATE KEY-----\nxxxxxx\n-----END PRIVATE KEY-----\n",
"client_email": "@.iam.gserviceaccount.com",
"client_id": "",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": ""
}
```
#### **Project Level Permissions**
The following roles need to be included at the project level:
* **BigQuery Read Session User**
It is required to read the data in parallel from Google Storage
* **BigQuery Job User**
It is required to load data from Views or execute live-mode SQL queries directly from BigQuery. It is also required to run jobs where the Views will be materialized to a temporary table by triggering a job and then reading from Google Storage.
1. Select the required project.
2. Under **Actions**, click on **Manage permissions**.
3. Click on **Grant access**.
4. Under **Add principals**, paste the service account email copied from the previous section.
5. Under **Assign roles**, choose the roles: **BigQuery Read Session User** and **BigQuery Job User.**
6. Click on **Save**.
7. **Policy updated** toast will be displayed.
Project level permissions
#### **Dataset Level Permissions**
* The role **BigQuery Data Viewer** should be assigned to the service account of the Tellius dataset from where the data would be read.
* If Views are read into Tellius, then the role **BigQuery Data Editor** needs to be assigned to the service account of the same dataset or a new dataset created solely for Tellius to create temporary tables.
1. Click on the dataset and select **Options → Share**.
2. Under **Add principals**, paste the service account email address copied from the first section.
3. Under **Assign roles**, provide the required role.
4. Click on **Save**.
