BigQuery as a Source Walkthrough

Introduction

This document provides a comprehensive walkthrough in BigQuery as a Source, starting with establishing a new River within Rivery, then proceeding to the selection of extraction modes and the administration of your data storage.
By the end of this guide, you will have a clear understanding of how to effectively harness Rivery's capabilities to extract data from BigQuery and seamlessly merge it into your data ecosystem.

Prerequisite

Please ensure the creation of a correct Connection for BigQuery Source within Rivery.

River Modes

When using BigQuery as a source, you have the choice to select between two River modes:

• Multi-Tables (Standard Extraction)
• Custom Query.

Multi-Tables (Standard Extraction)

This mode in Rivery combines data from various tables into a single schema before transferring it to the destination. It establishes table relationships to ensure consistent linking and loading. Rivery's Multi-Tables River mode mainly employs SQL queries for transformations, with scheduling or manual triggering options.

To obtain further details about using Multi-Tables (Standard Extraction), kindly refer to our documentation on Databases River Modes.

Custom Query

Rivery's Custom Query River mode empowers users to input data into the platform via personalized SQL queries, offering utmost control over data loading and transformations. Users can specify data and transformations precisely, using SQL, pulling from databases or data warehouses. Data is then scheduled for automatic or on-demand loading into Rivery, ensuring real-time data access.

Please Note:

• When using a custom query, specific data types are deliberately treated as strings. The data types labeled as "TO_JSON_STRING" are the ones enclosed in double quotes when converted into strings.

• When opting for the Incremental Extract mode, it's crucial to recognize that you have a choice of only 2 options: Datetime and Running number.

To obtain further details about using Custom Query, kindly refer to our documentation on Databases River Modes.

Limitations

• Data Export Bucket Region
  The region designated in the 'Region' input for the connection must align with the region of the Data export bucket (Custom Filezone).

• Project-Based Connection
  Rivery enables connections on a per-region basis. When dealing with multiple BigQuery projects, establish a distinct connection for each project.

• Mapping for Primary and Foreign Keys
  Automatic detection of mapping match keys is restricted to native BigQuery Primary and Foreign key fields only. Custom keys may not be recognized during the mapping process.

• Partition and Cluster Fields
  The mapping procedure within Rivery does not allocate Partition and Cluster Fields.

• Absence of Deleted Records Indication
  Data which has been removed is not flagged, leading to the fact that its absence is not indicated within the Target.