Google BigQuery Templates

This article describes the principal changes of Google BigQuery Templates.

The Template download section can be found at this page and Google BigQuery section at this page.

 

templates.google.bigquery.2019-11-07

Prerequisites:

• Google Cloud Platform Connector 1.0.4 or higher
• Google Cloud Platform Templates 2019-11-07 or higher

 

Support loading CSV and JSON files from GCS to Google BigQuery

Two new Templates have been added to load files which are on Google Cloud Storage (GCS) on Google BigQuery.

Those Templates allows to load CSV and JSON files already present in GCS directly in Google BigQuery.

All operations being performed directly on Google Cloud Platform itself without transferring it locally or anywhere else, this offers optimized performances.

 

templates.google.bigquery.2019-10-08

Support reading data from repeated fields and records

Google BigQuery records and repeated fields can now be used as source in a Mapping!

You can retrieve data from such fields by Mapping them as usual in a target table:

 

For repeated fields / repeated records, you must check the "Is Repeated" attribute in Metadata:

 

 

This is mandatory for now, as the Metadata Reverse is not yet retrieving this information.

In a future version, the reverse will retrieve automatically this information, avoiding to have to set it manually.

 

LOAD Rdbms to BigQuery Template

Support reading data from files

LOAD Rdbms to BigQuery Template now supports have a File Metadata as source.

This offers the possibility have a load step when loading file data, if necessary to perform transformations while loading data for instance.

Note that when mapping a target BigQuery table from a source file, the default selected Template is "INTEGRATION Rdbms to BigQuery" for performances purposes as it loads data directly into the target table without any temporary object creation.

If you want to enable the load step, simply change the selected Template from "INTEGRATION Rdbms to BigQuery" to "INTEGRATION BigQuery".

The load Template will appear automatically.

 

Temporary table creation now using Standard SQL

Temporary load table created when loading data into Google BigQuery through LOAD Rdbms to BigQuery Template is now performed through a Standard SQL query instead of Legacy SQL.

We are progressively migrating all Legacy SQL queries to Standard SQL which is the recommanded Google API.

This should offer better datatype support and is the currently Google supported API.

 

REJECT BigQuery Template

New Template available!

A dedicated Reject Template is now available to perform checks on data while loading Google BigQuery table.

This Template can be used as usual, by enabling reject detection on the target table.

 

INTEGRATION BigQuery

Temporary table creation now using Standard SQL

Temporary integration table created when loading data into Google BigQuery through INTEGRATION BigQuery Template is now performed through a Standard SQL query instead of Legacy SQL.

We are progressively migrating all Legacy SQL queries to Standard SQL which is the recommanded Google API.

This should offer better datatype support and is the currently Google supported API.

 

Cleanup of unused steps

INTEGRATION BigQuery Template has been cleaned up; some unused steps have been removed for better clarity when looking at executed steps.

 

Metadata Queries

Metadata Queries were not working properly

Metadata Queries were not working properly with Google BigQuery.

Generated queries were not correct, avoiding to use Metadata Queries in Mappings.

This has been fixed, Metadata Queries should work properly now.

 

templates.google.bigquery.2019-07-19

Google BigQuery technology updates

Create and drop table xpath expressions now generating Standard SQL queries.

Create table and drop table xpath expressions which can used on a Google BigQuery Metadata are now generating standard SQL queries instead of custom scripting.

This offers more comprehensive and visual representation of generated queries, and a better genericity.

 

Datatypes fixes

Numeric datatype was missing from datatype definition and some other datatypes such as INTEGER and FLOAT were having issues with their creation masks which were incorrect.

 

Templates improvements

Insert statistics when using direct mode

Statistics are now well computed as SQL_STAT_INSERT when using direct mode

This requires Stambia's Google BigQuery Connector 1.0.3 or higher

 

Error management improvements

Mechanism used to retrieve the errors of Google BigQuery jobs has been improved to return more accurate errors in some particular cases where the real error was caught and a generic error returned instead.

 

templates.google.bigquery.2019-02-08

INTEGRATION BigQuery

Recycling of previous rejects fixed

When using the option to recycle the rejects of previous execution an extra step is executed to add those previous rejects in the integration flow.

Possible duplicates while retrieving those rejects are now filtered using DISTINCT keyword.

 

templates.google.bigquery.2018-09-19

Two new Templates have been added:

• LOAD Rdbms to BigQuery
• INTEGRATION BigQuery

They offer the possibility to perform a load step before integrating data into BigQuery, and to perform direct BigQuery to BigQuery integrations.

INTEGRATION Rdbms to BigQuery Template is still included and preferred when wanting to integrate data directly into BigQuery without creating temporary tables.

The two new Templates simply allows to load data first into a temporary table if wanting to perform transformations, staging, ...

Other Templates have been updated to:

• Support using Standard SQL mode which is used by default from Google BigQuery Connector version 1.0.1
• Automatically add delimiters around the dataset and project name, which may contain special characters
• Automatically add delimiters around columns when their name correspond to a reserved keyword

 

This new Template version requires to use Stambia's Google BigQuery Connector 1.0.1 or higher

 

You have no rights to post comments