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;
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
Under Show Options uncheck Use Legacy Sql
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`
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;'
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;
