CODEWITHSUNDEEP
google-bigquerygoogle-cloud-dataflow
Henrique Martins
Henrique Martins

Reputation: 352

How to use BigQuery Standard SQL in Dataflow?

I would like to run a simple query using BigQuery Standard SQL within dataflow but I can't find where to enable this option. How can I do that?

pipeline.apply(Read.named(metricName + " Read").fromQuery("select * from table1 UNION DISTINCT select * from table2"));

When I try to run it I receive the error:

2016-07-20T13:35:22.543Z: Error:   (6e0ad847af078af9): Workflow failed. Causes: (fe6c7bcb1a35a057): S01:warehouse_handled_returns Read/DataflowPipelineRunner.BatchBigQueryIONativeRead+ParMultiDo(FormatData)+warehouse_handled_returns Write/DataflowPipelineRunner.BatchBigQueryIOWrite/DataflowPipelineRunner.BatchBigQueryIONativeWrite failed., (7f29f1d9435d27bc): BigQuery execution failed., (7f29f1d9435d2823): Error:
Message: Encountered "" at line 23, column 27.

HTTP Code: 400

Upvotes: 6

Views: 2537

Answers (3)

Dan Halperin
Dan Halperin

Reputation: 2247

Dataflow SDK for Java supports BigQuery's Standard SQL dialect beginning in version 1.8.0.

Upvotes: 0

Graham Polley
Graham Polley

Reputation: 14781

You can now use standard SQL with Dataflow.

https://cloud.google.com/dataflow/model/bigquery-io

PCollection<TableRow> weatherData = p.apply(
BigQueryIO.Read
.named("ReadYearAndTemp")
.fromQuery("SELECT year, mean_temp FROM `samples.weather_stations`")
.usingStandardSql();

Upvotes: 5

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

Until DataFlow formally supports BigQuery Standard SQL, one workaround is to start query with the following comment:

#StandardSQL

This will instruct BigQuery to use Standard SQL instead of Legacy SQL

Upvotes: 1

Related Questions