BigQuery Variables 👴🏼

[@alainponroy2621]

super interesting video 🙂

[@alsonyang230]

Great videos and thanks for the content!
Is there a proper offical way to distinguish between a variable and a column name at use? Like you would @ to singal this is a parameter. the underscore prefix to me is a patch solution that will works 99.99% of the times until some crazy people start to write column name with underscore prefix and break it accidentally😂

[@rohbot]

Hey Alson, thanks for the kind message and your question. You are absolutely right, I can see this happening as well :-D
The documentation does not state any prefix that you could use to adress the variable, however all system variables from bigquery are adressed with a @@ (cloud.google.com/bigquery/docs/reference/system-variables).
The catch on this is that you cannot define a column name with an @ inside (cloud.google.com/bigquery/docs/schemas#column_names), however you can define a variable having an @ inside and reference it just fine.
In one of the examples in the video I could define the variable platform as follows:
DECLARE `@platform` STRING DEFAULT 'ANDROID';
SELECT
*
FROM dwh_main.traffic
WHERE platform = `@platform` -- works fine
Referencing the variable without renaming it in the select statement would not work
SELECT
`@platform`, -- throws an error
`@platform` as platform -- does not throw error
FROM dwh_main.traffic
WHERE platform = `@platform` -- works fine
With this you can be sure that you will not have a column that is matching your variable :-)
Let me know if that helped with 0.01%
Martin

[@alsonyang230]

@@rohbot Hey Martin, thanks for your response. This makes sense and would work great i think. really appreciate that!

[@meena_deb3795]

Does the declared Variable remains the same inside the whole script once we have declared?
Or do we need to declare them before each and every create or select in case we want to reuse the same declared variable inside the script multiple times in different places?

[@rohbot]

Hey Meena_Deb,
Great question! Yes you would only need to declare them once and be able to use them multiple times in your script. If you are in session mode, then you just need to execute the variable declaration once, but in the "normal" mode you always need to execute the variable declaration as well when running your query.
Let me know if I that was helpful :-)
Stay Great
Martin

[@meena_deb3795]

@@rohbot that's great, thank you