BigQuery Dynamic variable


I'm trying to use dynamic variables inside a BigQuery query, but I'm not sure if it's possible or what the best solution is. Let me explain:

I want to access events between specific timestamps, like this:

e.event_timestamp BETWEEN 1713650401000000 AND 1714514399000000

I want these timestamps to be dynamic and to use the value from a local variable named dateStart. I've tried multiple approaches:

e.event_timestamp BETWEEN {{dateStart}} AND 1714514399000000

DECLARE dateStartValue INT64 {{dateStart}};
e.event_timestamp BETWEEN dateStartValue AND 1714514399000000

However, none of these attempts are working.

So, my question is: is it possible to achieve this? If not, how can I achieve something similar?

Thank you.

variables are json objects, by default they have a value and id property.

try using {{ dateStart.value }} instead of just the variable name.

That's what i try :

 `{{ date.value }}` e

But i still get an error : Table " ? " must be qualified with a dataset (e.g. dataset.table).

I think i'm gonna query all Table and later filter with a JS function

Hi @SimonJo,

You can use dynamic values in BigQuery where clauses, but prepared statements settings prevent querying dynamic table names as a security precaution.

1 Like