Google BigQuery Component Release Notes

This page lists the main features added to the Google BigQuery Component.

Feature Highlights

Version 2.2.6

Integration BigQuery

When loading data in append mode from BigQuery to BigQuery, if at the same time we activate deleteStrategy = key, the delete generated by the Template can have a complex subquery.

To improve performance, we always enable pre-integration systematically when deleteStrategy is set to key. Minor improvements and fixed issues

Improvements and fixes issues

This version contains also some other minor improvements and fixed issues, which can be found in the complete changelog.

Version 2.2.5

Minor improvements and fixed issues

This version contains some minor improvements and fixed issues, which can be found in the complete changelog. Complete changelog

Version 2.2.4

Minor improvements and fixed issues

This version contains some minor improvements and fixed issues, which can be found in the complete changelog. Complete changelog

Version 2.2.3

Minor improvements and fixed issues

This version contains some minor improvements and fixed issues, which can be found in the complete changelog. Complete changelog

Version 2.2.2

Using SQL syntax for Create table statement instead of scripting

The creation of Tables done by the Templates are now performed through a query in Standard SQL instead of being done through scripting with the Legacy API.

Version 2.2.1

Minor improvements and fixed issues

This version contains some minor improvements and fixed issues, which can be found in the complete changelog.

Version 2.2.0

Data Types

Addition of two new data types, BIGNUMERIC and BIGDECIMAL.

Version 2.1.2

Minor improvements and fixed issues

This version contains some minor improvements and fixed issues, which can be found in the complete changelog.

Version 2.1.1

Fixed issues

This version contains some minor fixed issues, which can be found in the complete changelog.

Version 2.1.0

Support of EMF Compare

Several improvements have been made to support EMF Compare.

Version 2.0.9

Minor improvements and fixed issues

This version contains some minor improvements and fixed issues, which can be found in the complete changelog.

Version 2.0.8

Support using Metadata Variable

It is now possible to store a Metadata Variable within BigQuery.

Version 2.0.7

Support defining and using partitions for query optimizations

When working with BigQuery tables having partitioned data, you can now define the partitions under the tables in Metadata:

release notes partition

After having defined partition information in Metadata, this can be used when reading data from this table to optimize the read queries.

When loading data from this BigQuery table to another BigQuery table, you can enable the "Enable Partition Pruning" parameter on the INTEGRATION BigQuery Template.

This will indicate to the Template to optimize the requests used to query the source table based on the partitions defined on it.

release notes pruning

Note that optimizations are performed only when having on the Mapping a filter on the partitioned source table.

When there is no filter, the whole source table is read so there are no possible partition optimizations.

Version 2.0.6

Support authentication through "ApplicationDefault" mode

Google Cloud Platform Component has evolved, allowing to authenticate through ApplicationDefault mode.

BigQuery Component has been updated to support this new mode.

Constant in GROUP BY clauses

Support using constant values in GROUP BY clauses by automatically using aliases.

Change Data Capture (CDC)

Multiple improvements have been performed to homogenize the usage of Change Data Capture (CDC) in the various Components.

Parameters have been homogenized, so that all Templates should now have the same CDC Parameters, with the same support of features.

Multiple fixes have also been performed to correct CDC issues. Refer to the changelog for the exact list of changes.

Version 2.0.5

JDBC Drivers classes and URLs

Semarchy xDM Data Integration Designer 20.2.0 introduces an improvement which allows Components and Modules definition to provide themselves the proper JDBC drivers classes and URLs, for the connection wizard to automatically adapt depending on the Module selected.

This Component has therefore been updated to take into account this improvement and provide the necessary JDBC drivers classes and URLs information.

Version 2.0.4

When you are working with Google Cloud BigQuery Metadata, you need to define the Google Cloud Platform Credentials to be able to perform operations.

A new step in the BigQuery wizard has been added to propose selecting the corresponding credentials, which will be displayed when creating a new BigQuery Metadata or when launching the wizard from an existing Metadata.

This wizard has been added to ease the configuration and usability of Google BigQuery Metadata.

the credentials proposed in this wizard are retrieved from all Google Cloud Platform Metadata of current workspace, in which credentials are defined. All existing credentials are displayed automatically. Just select one and click on Next to continue to the next step.

Version 2.0.3

Fixed issue with join

When perfoming joins between BigQuery tables or stages in a Mapping, generated join query was not correct in some situations, causing errors at execution.

Version 2.0.2

REJECT BigQuery

All constraints verifications are now performed in a single instruction, to limit the number of requests.

We modified this to avoid reaching the query limits of Google BigQuery APIs.

Load RDBMS to BigQuery

When the work schema is different from the target schema, the load jobs did not load the table in the correct location.

Load Google Cloud Storage Json to BigQuery

New option SuccessIfNoFile

Adding a new option named SuccessIfNoFile.

When set to 'true' the LOAD template will not fail if source file(s) is(are) do not exist and will create an empty table based on the expected structure.

This allows to continue processing even when there is no source data, for instance.

Fix work schema usage

When the work schema is different from the target schema, the load jobs did not load the table in the correct location.

Version 2.0.1

Credentials and Project ID

A new attribute to drag and drop a Google Cloud Platform Credentials Metadata node on the BigQuery Metadata has been added, to replace the usage of 'credentialsFile' and 'projectId' properties which are now deprecated.

Note that for backward compatibility, when properties are defined they have priority on the new credentials link.

To use the new mechanism, you must remove the properties and then define the link.

Temporary Bucket

A new attribute to drag and drop a Google Cloud Storage Bucket Metadata node has been added, to replace the default storage bucket attribute which is now deprecated.

Note that for backward compatibility, when the legacy attribute is defined, it has priority on the new storage link.

Note that the legacy attribute has been moved under deprecated tab.

Fix issue with Bigint

This version fixes an issue which was causing an SQLFeatureNotSupportedException in mapping using BigInt as source.

Fix issue with credentials error message

This version fixes an issue which was causing unclear and misleading error message when the credentials were the defined or incorrectly.

Change Log

Version 2.0.5 (Google BigQuery Component)

New Features

  • DI-2105: Google BigQuery Component - update module definition to propose more accurate driver and urls

Version 2.0.6 (Google BigQuery Component)

New Features

  • DI-1781: BigQuery - Support using constant values in GROUP BY clauses by automatically using aliases

  • DI-1944: Support a new authentication mode named "applicationDefault" which searches for authentication credentials in the current environment automatically through default Google’s mechanism

  • DI-1912: Templates updated - support having CDC sources on Templates which were not supporting it (such as staging templates)

  • DI-1910: Templates updated - New parameter 'Cdc Subscriber' on Templates on which it was not handled yet

  • DI-1909: Templates updated - New Parameters 'Unlock Cdc Table' and 'Lock Cdc Table' to configure the behaviour of CDC tables locking

Bug Fixes

  • DI-1908: Templates updated - The 'Cdc Subscriber' parameter was ignored in some Templates on Lock / Unlock CDC steps

  • DI-1907: Templates updated - The 'Cdc Subscriber' parameter was ignored in some Templates when querying the source data

Version 2.0.7 (Google BigQuery Component)

New Features

  • DI-1632: Ability to define partitions in Metadata, and to optimize read requests in Mappings based on them

Version 2.0.7 - Patch 1 (Google BigQuery Component)

New Features

  • DI-3392: Metadata Variables - Added support of Metadata Variable in BigQuery

Version 2.0.8 (Google BigQuery Component)

New Features

  • DI-1009: Template - INTEGRATION BigQuery (Hierarchical) BETA - New Template to write into repeated / nested fields

  • DI-1052: The isRepeated attribute was not reversed in the metadata, to use repeated fields.

Bug Fixes

  • DI-3407: Templates - "LOAD GCS File to BigQuery" and "LOAD GCS JSON to BigQuery" Templates were throwing an error when using credentials string - WrappedException: Wrapped java.lang.NumberFormatException: For input string: "{"

  • DI-3409: Templates - "Load GCS File to BigQuery" and "Load GCS Json to BigQuery" Templates - an exception was thrown when using credentials "string" authentication method (Null Pointer in Method Invocation BSF info)

  • DI-3480: BigQuery Metadata - Java Type used for NUMERIC columns was incorrect

  • DI-3491: When reversing a BigQuery table with multiple records some of the records (not all of them) was reversed on the wrong level.

  • DI-3565: Template - INTEGRATION BigQuery - SQL_STAT_INSERT and SQL_STAT_UPDATE statistics were not displayed

Version 2.0.9 (Google BigQuery Component)

New Features

  • DI-2782: Template - LOAD GCS File to BigQuery - Add support for file_name property field

Bug Fixes

  • DI-2153: Template - Load Rdbms to Bigquery - CDC_CHANGE_TYPE column is unexpectedly not loaded

Version 2.1.0 (Google BigQuery Component)

New Features

  • DI-3510: EMF compare utility - Component has been updated to support EMF Compare comparison utility

Bug Fixes

  • DI-1245: Google BigQuery Templates - when loading data into BigQuery, some Templates were not using the correct working schema

  • DI-2378: Template - Load Rdbms to Bigquery - an error such as "No such field: L1" was thrown when there was multiple time this Load Template for the same target datastore (parallelism was not handled properly)

  • DI-1165: Template - "INTEGRATION Bigquery" and "Load Rdbms to BigQuery" - SQL_STAT_INSERT and SQL_STAT_UPDATE statistics were not computed and not displayed

  • DI-1277: Google BigQuery - When loading DATE (or TIME) type from Bigquery into some databases (such as PostgreSQL) an error such as the following could unexpectedly be thrown "l1_c_time is of type time without time zone but expression is of type character varying"

  • DI-3833: Google BigQuery - Randomly, the "EngineExceptionI: A class was not found in the engine" error was displayed even when the Module was complete, because there was an unexpected jar in the Module which caused conflicts, and which has now been removed

Version 2.1.1 (Google BigQuery Component)

Bug Fixes

  • DI-4256: Template - Integration BigQuery - incorrect query might be generated in some situations when using deleteInsert integration strategy

Version 2.1.2 (Google BigQuery Component)

New Features

  • DI-4297: Template - Reject BigQuery - when using "Drop Reject Table" parameter, the drop and re-creation of the reject table is now performed in one query using REPLACE or IF NOT EXISTS clauses for performances and optimizations purposes

Bug Fixes

  • DI-4285: Template - Integration BigQuery - sometimes the preintegration table is unexpectedly created and populated even if it is not used later in the Template

  • DI-4298: Template - Reject BigQuery - when both "Drop Reject Tables" and "Delete Previous Rejects" parameters are enabled at the same time, the delete of previous rejects should not be performed because it is not necessary, as the table is recreated empty

Version 2.2.0 (Google BigQuery Component)

New Features

  • DI-3597: Addition of two new data types - BIGNUMERIC and BIGDECIMAL

Bug Fixes

  • DI-1209: Template - INTEGRATION BigQuery - handle nullity in update key when performing updates or comparison with target table

  • DI-3331: Google Cloud Storage and Google BigQuery wizards - When launching the wizard on an existing Metadata, the first step of the wizard did not automatically select the account defined in Metadata

Version 2.2.1 (Google BigQuery Component)

New Features

  • DI-4453: Template - INTEGRATION BigQuery - Expressions executed on target where not supported with merge incremental method

  • DI-4455: Template - INTEGRATION BigQuery - New parameter "Deletion Strategy"

  • DI-4456: Template - INTEGRATION BigQuery - New parameter "Truncate Target Table"

  • DI-4457: Template - INTEGRATION BigQuery - Create as select is now used to create integration table

  • DI-4458: Template - INTEGRATION BigQuery - New parameters "Enable Target Partition Pruning" and "Target Partition Filter"

  • DI-4460: Template - INTEGRATION BigQuery - Change partition boundaries calculation method so it executes once in a Mapping

  • DI-4461: Template - INTEGRATION BigQuery - When using a merge, a comparison is made on 'Non target' fields to minimize and optimize the number of updated records

  • DI-4462: Template - INTEGRATION BigQuery - remove option 'Useless Update Detection Method' which is not used anymore

Bug Fixes

  • DI-4454: Template - INTEGRATION BigQuery - Merge did not work with doUpdate only mode

  • DI-4459: Template - INTEGRATION BigQuery - Fix insertion of columns that are supposed to be evaluated on target when being in append mode

Version 2.2.2 (Google BigQuery Component)

New Features

  • DI-4295: TOOL - BigQuery Table Operations - add "truncate target table" parameter

  • DI-4516: TOOL - BigQuery Table Operation - use SQL syntax for Create table statement instead of scripting

Version 2.2.3 (Google BigQuery Component)

Bug Fixes

  • DI-4532: Template - Integration BigQuery - Flag of rows to update step should also be executed in insert only mode

Version 2.2.4 (Google BigQuery Component)

Bug Fixes

  • DI-4698: BigQuery - table and column names were unexpectedly truncated to 30 characters

Version 2.2.5 (Google BigQuery Component)

Bug Fixes

  • DI-4510: Template - LOAD GCS File to BigQuery - when loading data into BigQuery the templates is not using the correct work schema

Version 2.2.6 (Google BigQuery Component)

New Features

  • DI-4939: Template - Integration BigQuery - when deletion strategy is set to "key", create the preintegration table systematically for performances purposes

Bug Fixes

  • DI-4830: Template - Integration BigQuery - null values in columns being part of update or delete keys were not taken into account

  • DI-4957: Template - Integration BigQuery - null values were not taken into account on the action "Recycle of previous rejected data"

Version 20.1.0

New Features

  • DI-1188: Google Cloud: add a wizard to configure the credentials on Google Storage and Google BigQuery Metadata

Version 3.0.0 (Component Pack)

New Features

  • DI-2530: Added automatic reverse of partitioned tables when reversing BigQuery tables

  • DI-2795: Google BigQuery: Failing queries return information beyond the failed Job ID if available.

  • DI-3848: Google BigQuery partitions: Add "Year" and "Month" to the list of "Timestamp truncation" values.

  • DI-4508: Update Components and Designer to take into account dedicated license permissions

  • DI-4731: Rebranding: Template messages

  • DI-4813: Rebranding: Drivers classes and URLs

  • DI-4962: Improved component dependencies and requirements management

Version 3.0.1 (Component Pack)

New Features

  • DI-4939: Template - Integration BigQuery - when deletion strategy is set to "key", create the preintegration table systematically for performances purposes

Bug Fixes

  • DI-4830: Template - Integration BigQuery - null values in columns being part of update or delete keys were not taken into account

  • DI-4957: Template - Integration BigQuery - null values were not taken into account on the action "Recycle of previous rejected data"

Version 3.0.2 (Component Pack)

Bug Fixes

  • DI-5367: Template - INTEGRATION BigQuery - Reject Recycling is unexpectedly ignored when working with partitioned tables.

Version 5.3.6 (Component Pack)

New Features

  • DI-6041: Show detailed information about the error when a Google Big Query job fails.

  • DI-5839: Google BigQuery: add a retry mode to re-run automatically the query when an 'internalerror' or 'jobBackendError' error message is returned.

  • DI-4919: BigQuery: Add the ability to change the location used to run queries.