Overview
The Syniti Knowledge Platform (SKP) provides support for integrating with Google BigQuery using REST APIs. Replicate Preview allows you to use Google BigQuery as both a source and target in replication workspaces, limited to Snapshot (Refresh) replication mode. This article covers how to create BigQuery datastore connection and configure BigQuery datastores for replication at a high-level.
Prerequisites
Before creating a refresh replication for source or target using your BigQuery datasets and tables, ensure that you complete the following configurations:
BigQuery Datastore and Connection: A BigQuery datastore and connection needs to be created in the required system in the Catalog module. Refer to Create a Connection and Connecting to Google BigQuery for more information.
Note
When creating a datastore connection to BigQuery:
Database: Enter your Google Cloud Project ID.
Schema: Enter your BigQuery Dataset name.
These values are required to scope BigQuery operations to the specific project and dataset you want to access. Refer to Creating and managing projects and BigQuery Datasets for more information.
Select Import All to ingest metadata of all tables present in the BiqQuery dataset.
User Permissions: Users do not need individual BigQuery permissions. The Syniti OAuth2 client is pre-configured with all required BigQuery permissions. However, your Google developer account (user identity) is required for authentication (tracking and audit) only, not authorization.
If you are using this connection as a source, run a scan to ingest metadata from this connection's objects into the SKP.
BigQuery as a Source
In a Replicate Preview workspace, create a source datastore using the BigQuery datastore connection for the required target datastore. Refer to the Creating Source Datastores section for more information.
Create the required replications either by using the Source Table Selection dialog box or by adding them manually.
Source Considerations
Snapshot Replication: BigQuery supports full table scans for snapshot (refresh) replication, allowing you to replicate entire datasets or specific tables to your target datastore.
Large Datasets: BigQuery efficiently handles large datasets. The SKP automatically manages pagination to retrieve all records from your source tables.
Query Performance: BigQuery's query optimizer ensures efficient data retrieval. Monitor your usage to manage costs, as BigQuery charges per byte scanned. Refer to BigQuery Quotas and Limits for more information.
BigQuery as a Target
In a Replicate Preview workspace, create a target datastore using the BigQuery datastore connection. Refer to the Creating Target Datastores section for more information.
Note
Configure your target replication settings by specifying write options (Truncate (default), Append, Drop) and defining table mappings from your source to BigQuery tables.
Target Considerations
Auto Table Creation: The SKP supports automatic table creation in a BigQuery dataset. If a target table doesn't exist, it will be created based on the source table schema.
Write Options:
Append: Inserts new records into existing BigQuery tables without modifying existing data.
Truncate: Truncates existing data and reloads the table with data from the source.
Drop Target Table: Drops and recreates the target table and reloads the table with data from the source.
Cost Optimization: Monitor your BigQuery storage and query costs. Refer to BigQuery Pricing for detailed cost information.
Important Notes
Metadata Discovery: After adding tables to your BigQuery datastore, metadata is automatically scanned and imported. This process may take some time for large datasets.
Dataset Permissions: Ensure your service account has permissions to both read from source datasets and write to target datasets.
Regional Considerations: BigQuery datasets are region-specific. Ensure your source and target datasets are in regions that meet your compliance and performance requirements.
Connection Testing: Always test your BigQuery connections to verify authentication and permissions are correctly configured before running large-scale replications.