[HOP-4120] BeamBQInput creates query with useLegacySQL = true

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 2.0.0
• Fix Version/s: 2.1.0
• Component/s: BigQuery

Environment

BeamDirect runner on macOS 11.6.7:
   openjdk version "11.0.15" 2022-04-19
   OpenJDK Runtime Environment Homebrew (build 11.0.15+0)
   OpenJDK 64-Bit Server VM Homebrew (build 11.0.15+0, mixed mode)

• Assignee: Hans Van Akelyen
• Reporter: Fabian Peters
• Created: 11/Aug/22 15:47
• Updated: 12/Aug/22 18:50
• Resolved: 12/Aug/22 18:50

Description

This BigQuery query, which requires "standard SQL", fails:
 

SELECT
  FORMAT_TIMESTAMP("%Y_%m_%d", max(timestamp)) AS latest_timestamp,
  FORMAT_TIMESTAMP("%Y_%m_%d", COALESCE(
    TIMESTAMP_ADD(MAX(timestamp), INTERVAL 2 day),
    TIMESTAMP_ADD(CURRENT_TIMESTAMP, INTERVAL -21 day)
  )) AS target_timestamp
FROM
  `foo.bar`
LIMIT
  1; 

 
Despite what I see in the BeamBQInput source here, the query is being posted with "useLegacySql=true" – see the logging output below.
 

2022/08/11 17:15:52 - baz-audit-log - Caused by: java.io.IOException: Unable to dry run query: GenericData{classInfo=[allowLargeResults, clustering, connectionProperties, createDisposition, createSession, defaultDataset, destinationEncryptionConfiguration, destinationTable, flattenResults, maximumBillingTier, maximumBytesBilled, parameterMode, preserveNulls, priority, query, queryParameters, rangePartitioning, schemaUpdateOptions, tableDefinitions, timePartitioning, useLegacySql, useQueryCache, userDefinedFunctionResources, writeDisposition], {flattenResults=true, query=SELECT
2022/08/11 17:15:52 - baz-audit-log -   max(timestamp) AS latest_timestamp,
2022/08/11 17:15:52 - baz-audit-log -   FORMAT_TIMESTAMP("%Y_%m_%d", COALESCE(
2022/08/11 17:15:52 - baz-audit-log -     TIMESTAMP_ADD(MAX(timestamp), INTERVAL 2 day),
2022/08/11 17:15:52 - baz-audit-log -     TIMESTAMP_ADD(CURRENT_TIMESTAMP, INTERVAL -21 day)
2022/08/11 17:15:52 - baz-audit-log -   )) AS target_timestamp
2022/08/11 17:15:52 - baz-audit-log - FROM
2022/08/11 17:15:52 - baz-audit-log -   `foo.bar`
2022/08/11 17:15:52 - baz-audit-log - LIMIT
2022/08/11 17:15:52 - baz-audit-log -   1;, useLegacySql=true}}, aborting after 9 retries.
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryServicesImpl.executeWithRetries(BigQueryServicesImpl.java:1333)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryServicesImpl$JobServiceImpl.dryRunQuery(BigQueryServicesImpl.java:402)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryQueryHelper.dryRunQueryIfNeeded(BigQueryQueryHelper.java:72)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryQuerySourceDef.getEstimatedSizeBytes(BigQueryQuerySourceDef.java:99)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryQuerySource.getEstimatedSizeBytes(BigQueryQuerySource.java:58)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.runners.direct.BoundedReadEvaluatorFactory$InputProvider.getInitialInputs(BoundedReadEvaluatorFactory.java:214)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.runners.direct.ReadEvaluatorFactory$InputProvider.getInitialInputs(ReadEvaluatorFactory.java:88)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.runners.direct.RootProviderRegistry.getInitialInputs(RootProviderRegistry.java:80)
2022/08/11 17:15:52 - baz-audit-log - Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
2022/08/11 17:15:52 - baz-audit-log - POST https://bigquery.googleapis.com/bigquery/v2/projects/dashboard-foo/jobs?prettyPrint=false
2022/08/11 17:15:52 - baz-audit-log - {
2022/08/11 17:15:52 - baz-audit-log -   "code" : 400,
2022/08/11 17:15:52 - baz-audit-log -   "errors" : [ {
2022/08/11 17:15:52 - baz-audit-log -     "domain" : "global",
2022/08/11 17:15:52 - baz-audit-log -     "location" : "q",
2022/08/11 17:15:52 - baz-audit-log -     "locationType" : "parameter",
2022/08/11 17:15:52 - baz-audit-log -     "message" : "Encountered \" <INTEGER_LITERAL> \"2 \"\" at line 4, column 44.\nWas expecting:\n    \")\" ...\n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
2022/08/11 17:15:52 - baz-audit-log -     "reason" : "invalidQuery"
2022/08/11 17:15:52 - baz-audit-log -   } ],
2022/08/11 17:15:52 - baz-audit-log -   "message" : "Encountered \" <INTEGER_LITERAL> \"2 \"\" at line 4, column 44.\nWas expecting:\n    \")\" ...\n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
2022/08/11 17:15:52 - baz-audit-log -   "status" : "INVALID_ARGUMENT"
2022/08/11 17:15:52 - baz-audit-log - }
2022/08/11 17:15:52 - baz-audit-log -     at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146)
2022/08/11 17:15:52 - baz-audit-log -     at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:118)
2022/08/11 17:15:52 - baz-audit-log -     at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:37)
2022/08/11 17:15:52 - baz-audit-log -     at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:428)
2022/08/11 17:15:52 - baz-audit-log -     at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1111)
2022/08/11 17:15:52 - baz-audit-log -     at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:514)
2022/08/11 17:15:52 - baz-audit-log -     at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:455)
2022/08/11 17:15:52 - baz-audit-log -     at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:565)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryServicesImpl.executeWithRetries(BigQueryServicesImpl.java:1324)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryServicesImpl$JobServiceImpl.dryRunQuery(BigQueryServicesImpl.java:402)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryQueryHelper.dryRunQueryIfNeeded(BigQueryQueryHelper.java:72)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryQuerySourceDef.getEstimatedSizeBytes(BigQueryQuerySourceDef.java:99)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.sdk.io.gcp.bigquery.BigQueryQuerySource.getEstimatedSizeBytes(BigQueryQuerySource.java:58)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.runners.direct.BoundedReadEvaluatorFactory$InputProvider.getInitialInputs(BoundedReadEvaluatorFactory.java:214)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.runners.direct.ReadEvaluatorFactory$InputProvider.getInitialInputs(ReadEvaluatorFactory.java:88)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.runners.direct.RootProviderRegistry.getInitialInputs(RootProviderRegistry.java:80)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.runners.direct.ExecutorServiceParallelExecutor.start(ExecutorServiceParallelExecutor.java:160)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.beam.runners.direct.DirectRunner.run(DirectRunner.java:213)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.hop.beam.engines.BeamPipelineEngine.executePipeline(BeamPipelineEngine.java:242)
2022/08/11 17:15:52 - baz-audit-log -     at org.apache.hop.beam.engines.BeamPipelineEngine.lambda$startThreads$0(BeamPipelineEngine.java:301)
2022/08/11 17:15:52 - baz-audit-log -     at java.base/java.lang.Thread.run(Thread.java:829)