Error in Google BigQuery <EOF>

Question

I am new to Google BigQuery. I need help with the query error:

"Encountered " "WITH" "with "" at line 1, column 1. Was expecting: EOF"

with
  t1 as 
  (
    select
    date(USEC_TO_TIMESTAMP(event_dim.timestamp_micros)) date, event_dim.name
    from
    [myfoody-1313:it_rawfish_myfoody_ANDROID.app_events_20160727]
    where
    event_dim.name='pv_detail' and event_dim.params.key='item_id' and
    event_dim.params.value.string_value='31'
  )
  select
  date(d) as day, count(event_dim.name)
from
  generate_series(current_date - interval '6 day', current_date, '1 day') d
  left join t1 on t1.date = d
group by day
order by day;

Yearlong

After you enable standard SQL using Mikhail's suggestion below, there are a couple of other transformations you will need to make to your query, namely: * Use CAST(TIMESTAMP_MICROS(event_dim.timestamp_micros) AS DATE). * Use an explicit array instead of GENERATE_SERIES. I filed a feature request for series/array generation here, which would fill this gap: code.google.com/p/google-bigquery/issues/detail?id=646 – Alfreda

As an update, GENERATE_DATE_ARRAY is now available in BigQuery. in the query above, for instance, you could use FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY), CURRENT_DATE(), INTERVAL 1 DAY)) AS d LEFT JOIN .... – Alfreda

Answer 1

I need help on a query error: "Encountered " "WITH" "with "" at line 1, column 1. Was expecting: EOF"

WITH is supported for BigQuery Standard SQL - see Enabling Standard SQL - you should enable Standard SQL

Yu

Answer 2

Under Show Options uncheck Use Legacy Sql

Bandanna

Answer 3

To access BQ using Terminal command:

For Ignoring these cases: "Encountered", "WITH", "with" at line 1, column 1. "Was expecting: EOF"

Use: --use_legacy_sql=false

Normal Query example from the terminal:

bq query --use_legacy_sql=false 'SELECT * from `table_name` where published_date >= "2020-05-05" limit 10;'

Dupondius

Answer 4

I was running

self.hook.run_query(sql=sql_query)

from the Airflow BigQuery Hook where I had a further error message compared to the one posted in the question:

Was expecting:
    <EOF>
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]

According to the 1st doc I mentioned:

use_legacy_sql (bool | None) – Whether to use legacy SQL (true) or standard SQL (false). If None, defaults to self.use_legacy_sql.

So I changed it to:

self.hook.run_query(sql=sql_query, use_legacy_sql=False)

and worked for me.

Psychotechnics

Answer 5

We also getting the same exception while running the query via Java SDK, to fix it we passed table name like `project_id.dataset_id.table`

Patrimony

Answer 6

Your query syntax is wrong so i fix that and try #standardsql while writing any query, in this way you don't need to do any setting changes. Try below code and add in ur whole query.

standardSQL

with t1 as ( select date((current_timestamp())) date, 'xyz' name -- from

-- where -- event_dim.name='pv_detail' and event_dim.params.key='item_id' and -- event_dim.params.value.string_value='31' )

select cast(d as date) day, count(name) from UNNEST(GENERATE_DATE_ARRAY(date_sub(CURRENT_DATE(), INTERVAL 6 day), date_sub(current_date(), INTERVAL 1 day))) d left join t1 on t1.date = cast(d as date) group by 1 order by 1;

Bit