How to use standard SQLQuery SQL in a data stream?

Question

I would like to run a simple query using BigQuery Standard SQL in a data stream, but I cannot find where to enable this option. How can i do this?

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

When I try to start it, I get an error message:

 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 warehouse_handled_returns Read / DataflowPipelineRunner.BatchBigQueryIONativeRead + ParMultiDo (FormatData) + warehouse_handled_returns Write / DataflowPipelineRunner.BatchBigQueryIOWrite / DataflowPipelineRunner.BatchBigQueryIONativeWrite failed, (7f29f1d9435d27bc):. BigQuery execution failed, (7f29f1d9435d2823):. 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 

Answer 1

Now you can use standard SQL with data flow.

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(); 

Answer 2

As long as DataFlow does not formally support BigQuery Standard SQL, one way is to run the query with the following comment:

 #StandardSQL 

This will instruct BigQuery to use standard SQL instead of Legacy SQL.

Answer 3

The Dataflow SDK for Java supports the standard SQLBuery SQL dialect starting with version 1.8.0.