Source and destination
Go to https://console.cloud.google.com/apis/credentials/serviceaccountkey to create a new service account.
Select these BigQuery roles:
- BigQuery Job User
- BigQuery Data Viewer
- BigQuery Metadata Viewer
If you plan on using BigQuery as a destination, you'll also need:
- BigQuery Data Editor
- Select JSON for Key type.
Click Create and save the JSON key file.
In Polytomic, go to Connections → Add Connection → Google BigQuery.
Upload the JSON service account key from step 3 to Polytomic's Service account credentials field.
- Click Save.
Note: If you are trying to write "Record" field types in BigQuery, Polytomic may modify field names in order to comply with BigQuery field naming rules. The following rules apply:
- Only the first 300 characters are used;
- field names consist of only upper/lowercase letters, numbers, and underscores;
- all other characters are dropped.
Creating new tables
Polytomic can automatically create new tables in BigQuery. The user must have Data Editor (or better) permissions in order to create the required dataset and tables.
You should be able to create a new table using the Create new table.. target for BigQuery. A new table will be created after you save the sync for the first time.
Once the table is created, the table will never be dropped or altered by Polytomic. If you want to change the schema, either modify the schema by hand and reflect the updates in Polytomic, or get Polytomic to create another new table.
Before saving the sync, you'll be asked to input a table name, which will be used when creating the new table. The new columns will be named and typed based on the source column names.
For each selected field, Polytomic will create a table with the following column types:
|Polytomic Type||BigQuery Type|
Suppose we want to create a new table that contains user IDs, their organization ID, the user's name, and the user's last login time.
You can click "Preview" to see what the columns will be named during table creation. The model field names may be transformed slightly.
Note the following:
- Polytomic will not create nested fields (type
Record) or array fields (mode
Repeated) at this time. While we support syncing to these fields, if you want to use them in your schema, you can create the table without these fields, and then augment the table after from the console.
- Table name and column names may be augmented to fit naming conventions.
- Duplicate field names will be made unambiguous by prepending the model name (e.g. the two id fields turn into 'user_id' and 'company_id' respectively).
polytomic_job_timestampfield will always be included in the schema, regardless of sync mode.
Updated 6 months ago