BigQuery Tips & Tricks – Part 1

I am becoming a fan of BigQuery recent days. It’s saving me a lots of time while of our business platforms are running on Google Cloud Platform. Every application, every operation we are keeping logs from every corner of our infrastructure as much as possible that gives us a greater good insight about everything. All logs are being stored in BigQuery. It’s awesome, completely automated and scaled.

With Google’s Data Studio, our project managers, business analysts and every other department (with or without any technical knowledge) can build their own reports and get more insight about every piece of components that’s making our business great.

So sometimes, I need to be able to practice some tips and tricks to get more complex insight, data transformation and other small stuffs to make the whole ecosystem more useful. So this articles I am keeping here for my own practice purpose so whenever I forgot any little things, I can find that from here. This BigQuery tips and tricks will be also helpful for you.

Convert timezone in query

All the BigQuery timestamps are in UTC timezone. If you want to change the timestamp field in another timezone. Just follow this

SELECT DATETIME(timestamp, "Asia/Dhaka") from table

Parse and Access JSON string with JSON Path

Assume, you have some logs or some data in JSON format and you want to access properties from inside the JSON string. You should use JSON_EXTRACT() or JSON_EXTRACT_SCALAR() function.

SELECT JSON_EXTRACT('{"job_slugs":"I am a test Job slug"}', '$.job_slugs') as job_slugs from table

It can work based on JSON path. You can evaluate your JSON path from http://jsonpath.com. It’s awesome

Remove double quotes from beginning and end of the string

Let’s say, you extracted some data and it’s looks like ” String inside double quotes”. Now it’s making you trouble to work with that string. Simply follow this

SELECT
 REGEXP_REPLACE('"String inside double quotes"', r'^"|"

And you will get String inside double quotes without the double quotes. It's a simple tricks with REGEX_REPLACE() bigquery function.

Also read my other BigQuery related posts from here
, '') AS unquoted_string from table

And you will get String inside double quotes without the double quotes. It’s a simple tricks with REGEX_REPLACE() bigquery function.

Also read my other BigQuery related posts from here