The 'SAFE.' prefix - how not to let errors trick you
This section describes handy SQL features and functions in BigQuery. This article covers the `SAFE.` prefix to make your queries more error-proof.
Editor's note: this post is written by Tamas Ujhelyi—follow him for handy SQL tips on BigQuery.
Do you die a little inside when your query throws an error?
You're already having a tough day, work's moving slower than you planned, tons of meetings, etc.
And now your query fails you too.😔
You check the data, and OF COURSE the data's messy - that's the issue, because why would life be easy?
Turns out, life can be easy, though. (If you use BigQuery, that is.)
BigQuery has a nice little feature for saying "not today" to errors.
It's called the 'SAFE.' prefix.
You just:
1) grab the function that's giving you a headache (e.g. parse_date()),
2) and prefix it with safe. (e.g. safe.parse_date()).
Now, if the function would return an error, it won't - it'll return null instead.
This means your query will run, so you can be sad for other reasons, but not because of your query. (Please don't be sad!)
The example I brought up isn't random.
Recently I couldn't parse a date whose value was "6".
So I used parse_date('%m/%d/%Y', date_value) and got a null as promised.
I was happy.
Hope you're happy too & have an awesome day!
safe. won't work with certain functions. There are some cases where similarly named functions exist when the safe. prefix itself does not work, e.g. safe_divide for divisions and safe_cast for changing data types.)