CODEWITHSUNDEEP
sqlgoogle-bigquerydata-visualization
Francesco Bria
Francesco Bria

Reputation: 261

Error in Google BigQuery <EOF>

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;

Upvotes: 16

Views: 31827

Answers (6)

Vzzarr
Vzzarr

Reputation: 5690

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.

Upvotes: 1

Tarun Aggarwal
Tarun Aggarwal

Reputation: 11

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;

Upvotes: 0

Kumar Pankaj Dubey
Kumar Pankaj Dubey

Reputation: 2317

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;'

Upvotes: 1

SANN3
SANN3

Reputation: 10099

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`

Upvotes: 0

Ilya Zinkevych
Ilya Zinkevych

Reputation: 51

Under Show Options uncheck Use Legacy Sql

Upvotes: 3

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

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

Upvotes: 14

Related Questions