Overview

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.

• Snap Type: Write
• Works in Ultra Tasks

Prerequisites

[None]

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.

Supported Accounts

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

Snap Views

Snap Settings

Troubleshooting

Implicit retries in BigQuery Snaps

The BigQuery Snaps handle all retriable BigQuery errors (BigQuery exception, IO exception, and Runtime exception) internally.

• 429 (Too Many Requests):
  • Retry attempts: Maximum of 5 retries.
  • Delay Between Retries: Backoff strategy with jitter (random variation) is applied to prevent synchronized retries and reduce load.
• 401 (Unauthorized):
  • Retry attempts: Maximum of 3 retries.
  • Delay Between Retries: Backoff strategy is applied.
  • Additional Actions: Reloads the BigQuery account on the retry event.
• IOException and 500, 502, 503, 504 (Server Errors):
  • Retry attempts: Maximum of 3 retries.
  • Delay Between Retries: Backoff strategy is applied.

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:

Sample Queries

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

SELECT id, name FROM `project-123.testKamal.TestTablet`
LIMIT 5000

SELECT id, name FROM [project-123:testKamal.TestTablet]
LIMIT 5000

CREATE OR REPLACE TABLE `project-123.testKamal.TestTable2`
( x INT64, y STRUCT< a ARRAY<STRING>, b BOOL > )

INSERT INTO `project-123.testKamal.TestTable2` (x, y)
VALUES (1, (['1', '2', '3'], true)), (2, (['a', 'b'],
false))

SELECT COUNT(DISTINCT x) FROM `project-
123.testKamal.TestTable2`

SELECT EXACT_COUNT_DISTINCT(x) FROM [project-
123:testKamal.TestTable2]

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 TABLE `project-123.testKamal.TestTable2`

After the execution of query, the results are written to the output view.