On this Page

Snap type:

Write

Description:

This Snap allows you to execute queries on BigQuery easily leveraging the jobs and query APIs. This Snap works only with single queries. A complete list of supported queries as well as examples are documented here: https://cloud.google.com/bigquery/query-reference.

Expected upstream Snaps: [None]
Expected downstream Snaps: The Snap will output one document for every record retrieved, hence any document processing Snap can be used downstream. Mapper or any other application where the data returned from the query needs to be written to are examples.
Expected input: [None]
Expected output: Document for each record retrieved. Special types such as TIMESTAMP, TIME are converted into SnapLogic internal date type representations which then can be consumed by downstream Snaps just like any other data type.

Snaps in Google BigQuery Snap Pack

Write datetime values to the database tables, always in UTC format.
Convert any non-UTC values in the incoming data to UTC before writing them.
Consider datetime values without the time zone mentioned, as in UTC.

So, ensure that you include the time zone in all the datetime values that you load into Google BigQuery tables using this Snap.

For example: "2020-08-29T18:38:07.370 America/Los_Angeles", “2020-09-11T10:05:14.000-07:00", “2020-09-11T17:05:14.000Z”

Prerequisites:

[None]

Support and limitations: Works in Ultra Tasks.

Known Issues

Google BigQuery does not support very large exponential values—larger than EXP(700). So, while displaying values of such high exponential order in the validation preview, this Snap routes to the error view, and displays the following error:
"Data conversion failed for field f0_(FLOAT) of value Infinity."
The Snap also displays empty output in the preview, which is not expected.

Account:

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Google BigQuery Account for information on the type of account to use.

Views:

Input	This Snap has at most one document input view.
Output	This Snap has exactly one document output view which displays the result set returned from the query - one document for each record retrieved.
Error	This Snap has at most one document error view and produces zero or more documents in the view.

Settings

Label

Specify a name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

Project ID

Required. This drop-down shows you a list of all the available projects that your user Account has access to. Clicking on the the drop-down always pulls the latest list of available projects. The project on which the query should be executed should be selected.

Location Specify or select a region from the list of suggested locations on which you want to execute BigQuery.

Query

The query that you want to execute on BigQuery for the selected project. For a full list of supported functions and operators for your queries, see Legacy SQL Functions and Operators and Standard SQL Functions and Operators.

We recommend you to add a single query in the SQL Statement field.
Make sure that the query conforms to either Standard SQL or Legacy SQL.
Use appropriate escape mechanisms for passing special/invalid characters.
- Backticks ( `) for Standard SQL
- Square brackets ([ ]) in case of Legacy SQL.
See Sample Queries for more information.

This setting also supports expressions that can be enabled to parameterize a specific section of the query like table names or columns to be selected.

Standard SQL

Select this checkbox if you want to use the Standard SQL dialect in the Query field. It is crucial that you understand how the Snap interprets the dialect used in the Query field.

Do not select this check box if the query contains #legacySQL prefix or uses Legacy SQL dialect.

Default value: Not selected

Destination dataset ID

Dataset ID of the dataset where the destination table has to be created in case of the query returning large query results.

Destination table ID

Table ID of the destination table to write the query results to in case of the query returning large query results.

Action on destination table

This option specifies the action that has to be taken if the destination table already exists. Options available include OVERWRITE, APPEND, and ERROR.

Default value: OVERWRITE

Snap execution

Select one of the three modes in which the Snap executes. Available options are:

Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.
Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.
Disabled: Disables the Snap and all Snaps that are downstream from it.

Interpreting the SQL Query Dialect

The Snap determines the SQL dialect used in the query based on the following flags:

Dialect specified as prefix within the Query field (#standardSQL or #legacySQL)
Default Standard SQL check box at account level
Standard SQL check box at Snap level

The prefix specified in the query ignores the other two flags.

When the prefix is not specified,
- The user must select either one of the check boxes at the account level and at the Snap level to specify that the query uses Standard SQL dialect.
  - Else, the query is considered to be written in Legacy SQL.

The following matrix depicts all the possible real-time scenarios for resolving the query dialect:

Prefix in Query	Check box at account level	Check box at Snap level	Query Dialect
Not specified			Legacy SQL
Not specified			Standard SQL
Not specified			Standard SQL
Not specified			Standard SQL
`#legacySQL`			Legacy SQL
`#legacySQL`			Legacy SQL
`#legacySQL`			Legacy SQL
`#legacySQL`			Legacy SQL
`#standardSQL`			Standard SQL
`#standardSQL`			Standard SQL
`#standardSQL`			Standard SQL
`#standardSQL`			Standard SQL

For existing Pipelines

If the prefix is defined in the Query field, the query is interpreted accordingly.
- Else, the query is treated as using Legacy SQL dialect.
To mark a query without prefix as using Standard SQL, select the Standard SQL check box at the Snap level.
- To update all Pipelines for an account to use Standard SQL, select the Default Standard SQL checkbox at the account level.

Sample Queries

Here are some SQL statements in each of the two SQL dialects, that the BigQuery Execute Snap can execute:

SQL Operation	Standard SQL	Legacy SQL
Select	SELECT id, name FROM `project-123.testKamal.TestTablet` LIMIT 5000	SELECT id, name FROM [project-123:testKamal.TestTablet] LIMIT 5000
Create (DDL) table with nested array	CREATE OR REPLACE TABLE `project-123.testKamal.TestTable2` ( x INT64, y STRUCT< a ARRAY<STRING>, b BOOL > )	Not allowed in Legacy SQL.
Insert (DML)	INSERT INTO `project-123.testKamal.TestTable2` (x, y) VALUES (1, (['1', '2', '3'], true)), (2, (['a', 'b'], false))	Not allowed in Legacy SQL.
Row count	SELECT COUNT(DISTINCT x) FROM `project- 123.testKamal.TestTable2`	SELECT EXACT_COUNT_DISTINCT(x) FROM [project- 123:testKamal.TestTable2]
Convert an Array into Table rows (Flattening)	SELECT x, a, y.b as b FROM `project-123.testKamal.TestTable2`, UNNEST(y.a) as a	SELECT x, y.a as a, y.b as b FROM FLATTEN([project- 123:testKamal.TestTable2], y.a)
DROP (DDL)	DROP TABLE `project-123.testKamal.TestTable2`	Not allowed in Legacy SQL.

After the execution of query, the results are written to the output view. The sample output of Execute Snap looks as follows.

Snap Pack History

Click to view/expand

Release	Snap Pack Version	Date	Type	Updates
August 2024	438patches28058	13 Sep 2024	Latest	Fixed an issue with the BigQuery Table Data List Snap that displayed a `null pointer exception` when the table source schema contained a nested `Array list` schema.
August 2024	main27765	21 Aug 2024	Stable	Updated and certified against the current Snaplogic Platform release.
May 2024	main26341	08 May 2024	Stable	Updated and certified against the current SnapLogic Platform release.
February 2024	main25112	14 Feb 2024	Stable	Updated and certified against the current SnapLogic Platform release.
November 2023	main23721	08 Nov 2023	Stable	Updated and certified against the current SnapLogic Platform release.
August 2023	main22460	16 Aug 2023	Stable	Updated and certified against the current SnapLogic Platform release.
May 2023	433patches22057	11 Aug 2023	Latest	Introduced the Google BigQuery Upsert (Streaming) Snap, which enables you to perform bulk update/insert operations into a BigQuery table from existing tables or any input data stream.
May 2023	433patches21955	24 Jul 2023	Latest	Fixed an issue with the GBQ-Google Service Account that caused an input stream to remain open.
May 2023	main21015	10 May 2023	Stable	Updated and certified to be compatible with the August SnapLogic Platform release.
February 2023	432patches20962	09 May 2023	Latest	Fixed an intermittent null pointer exception that occurred in the BigQuery Write Snap.
February 2023	432patches20298	28 Mar 2023	Latest	Disabled retries on truncated tables for the the BigQuery Write and BigQuery Bulk Load (Streaming) Snaps. Added better error messages for error conditions in the BigQuery Table Data List Snap. Fixed an issue with the BigQuery Table Create Snap that caused an error to display when fields were separated with a comma and a space. Fixed an issue with the sorting of the Partitioning time dropdown in the BigQuery Table Create Snap.
February 2023	432patches19840	01 Mar 2023	Latest	Fixed an issue with the Google BigQuery Bulk Load (Streaming) Snap that caused the `Table not found` message to display even when Create table if not present was selected.
February 2023	main19844	09 Feb 2023	Stable	Introduced the following new Snaps: Google BigQuery Table Create Snap—enables you to create tables that support clustering and partitioning. Google BigQuery Table Data List Snap—enables you to read table data from a BigQuery dataset and lists the contents of the table in rows in the output.
November 2022	431patches19301	12 Jan 2023	Latest	The Google BigQuery Bulk Load (Streaming) Snap works as expected, with no active Timer threads remaining when the Pipeline execution fails.
November 2022	main18944	10 Nov 2022	Stable	Upgraded with the latest SnapLogic Platform release.
August 2022	main17386	11 Aug 2022	Stable	The Location field in the BigQuery Execute Snap lists all the locations in the suggestions. Introduced the following Snaps and accounts to manage datasets in Google BigQuery (GBQ): BigQuery Dataset Create: Creates datasets in the GBQ. BigQuery Dataset Delete: Removes datasets from the GBQ. BigQuery Dataset List: Lists datasets from the GBQ. BigQuery Table Delete: Removes tables from the GBQ. BigQuery Table List: Lists tables that are read from the GBQ dataset. Google Service Account JSON: Accesses the resources on GBQ using a JSON Key.
4.29	main15993	14 May 2022	Stable	Upgraded with the latest SnapLogic Platform release.
4.28 Patch	428patches15459	12 Apr 2022	Latest	Fixed an issue with Google BigQuery Execute Snap, where the Snap displayed `404 Job not found` error when calling a procedure. Fixed an issue with the Google BigQuery Bulk Load (Cloud Storage) Snap where the Snap failed, because the access token expired when it had to wait longer to execute. With this fix, the Snap is reloaded to get refreshed access token.
4.28 Patch	428patches14743	23 Feb 2022	Latest	Fixed an issue with the Google BigQuery Execute Snap, where the Snap displayed an error when the input data contained a table having the record type column and its value was null. Fixed an issue with the Google BigQuery Write Snap, when the input data contained complex data type columns (such as nested fields) and Create table if not present checkbox was selected.
4.28	main14627	12 Feb 2022	Stable	Upgraded with the latest SnapLogic Platform release.
4.27 Patch	427patches13752	12 Jan 2022	Latest	Upgraded Google BigQuery driver to 1.119.0 version to support time partition intervals by MONTH and YEAR.
4.27 Patch	427patches13615	16 Dec 2021	Latest	Fixed the table truncate 404 error with the Google BigQuery Load (Streaming) Snap by supporting the retry functionality. The Snap now waits in case of an error and retries before loading the data.
4.27 Patch	427patches12691	23 Nov 2021	Latest	Fixed an issue with the Google BigQuery Bulk Load (Cloud Storage) Snap, where the Snap failed with an exception for big query tables. The `CreateDisposition` is now set conditionally on the basis of the setting in the Create table if not present checkbox.
4.27	main12833	13 Nov 2021	Stable	Enhanced the Google BigQuery Bulk Load (Cloud Storage) Snap with the following batching and retry properties to process input records: Batching: Processes the input records in batches. Batch Size: The number of records batched per request. Batch Timeout (milliseconds): Time in milliseconds to elapse following which the batch, if not empty, will be processed even though it might be lesser than the given batch size.
4.26	main11181	14 Aug 2021	Stable	Upgraded with the latest SnapLogic Platform release.
4.25	main9554	08 May 2021	Stable	Upgraded with the latest SnapLogic Platform release.
4.24	main8556	13 Feb 2021	Stable	Upgraded with the latest SnapLogic Platform release.
4.23	main7430	14 Nov 2020	Stable	Enhanced the Google BigQuery Execute Snap and Google BigQuery accounts to enable you to choose the SQL dialect to use in the Query field. See Interpreting the SQL Query Dialect for more information. Fixed the precision loss in Google BigQuery Execute Snap output that strips millisecond values while retrieving TIMESTAMP values from Google BigQuery tables.
4.22	main6403	12 Sep 2020	Stable	Upgraded with the latest SnapLogic Platform release.
4.21	snapsmrc542	09 May 2020	Stable	Upgraded with the latest SnapLogic Platform release.
4.20 Patch	google/bigquery8773	19 Mar 2020	Latest	Fixed the NPE issue with stored procedures and DROP TABLE queries in the Google BigQuery Execute Snap.
4.20	snapsmrc535	08 Feb 2020	Stable	Upgraded with the latest SnapLogic Platform release.
4.19	snaprsmrc528	14 Nov 2019	Stable	Upgraded with the latest SnapLogic Platform release.
4.18	snapsmrc523	10 Aug 2019	Stable	Upgraded with the latest SnapLogic Platform release.
4.17	ALL7402	11 Jun 2019	Latest	Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers.
4.17	snapsmrc515	11 Jun 2019	Stable	Added the Snap Execution field to all Standard-mode Snaps. In some Snaps, this field replaces the existing Execute during preview check box.
4.16	snapsmrc508	16 Feb 2019	Stable	Upgraded with the latest SnapLogic Platform release.
4.15	snapsmrc500	15 Dec 2018	Stable	Upgraded with the latest SnapLogic Platform release.
4.14	snapsmrc490	11 Aug 2018	Stable	Upgraded with the latest SnapLogic Platform release.
4.13	snapsmrc486	12 May 2018	Stable	Upgraded with the latest SnapLogic Platform release.
4.12	snapsmrc480	17 Feb 2018	Stable	Added a new property Schema auto detect in the Google BigQuery Bulk Load (Cloud Storage) Snap to support CSV and JSON files where one or more columns in the source file may not contain any values.
4.11	snapsmrc465	11 Nov 2017	Stable	Added new Snap: Google BigQuery Bulk Load (Cloud Storage) Added new Snap: Google BigQuery Bulk Load (Streaming). Updated Google Big Query Write Snap with a new Create table if not present property.
4.10 Patch	google/bigquery4046	05 Oct 2017	Latest	Addressed an issue when authenticating with Dynamic OAuth accounts.
4.10	snapsmrc414	12 Aug 2017	Stable	Upgraded with the latest SnapLogic Platform release.
4.9	snapsmrc405	13 May 2017	Stable	Upgraded with the latest SnapLogic Platform release.
4.8 Patch	bigquery2952	27 Apr 2017	Latest	Supports refreshing OAuth access tokens during long-running pipeline executions. Fixed an issue with writing small batch sizes and when querying empty dataset tables.
4.8.0 Patch	bigquery2813	05 Apr 2017	Latest	Reload OAuth account from Platform when the access token expires during pipeline execution.
4.8	snapsmrc398	11 Feb 2017	Stable	Upgraded with the latest SnapLogic Platform release.
4.7	snapsmrc382	23 Nov 2016	Stable	Upgraded with the latest SnapLogic Platform release.
4.6	snapsmrc362	13 Aug 2016	Stable	Upgraded with the latest SnapLogic Platform release.
4.5.1	snapsmrc344	18 May 2016	Stable	Upgraded with the latest SnapLogic Platform release.
4.4.1	NA	18 Mar 2016	Stable	Upgraded with the latest SnapLogic Platform release.
4.4	NA	13 Feb 2016	Stable	Upgraded with the latest SnapLogic Platform release.
4.3.2	NA	15 Jan 2016	Stable	Resolved the following issues with the Google BigQuery Execute Snap: throwing binary data in stacktrace and two error messages. improve error handling for suggestions improve error handling on bad queries suggestion bubble missing for Destination table ID Resolved an issue with Auto refresh token not working in Google BigQuery account.