4 Ways Big Query Metadata Can Help You | by Marie Truong | May, 2022

Get knowledge about tables, jobs, and extra

Photograph by Hunter Harritt on Unsplash

A typical solution to outline metadata is “knowledge concerning the knowledge”. In Huge Question, metadata might be extraordinarily helpful and handy as it’s organized in a sequence of views known as INFORMATION_SCHEMA.

Listed below are 4 examples of how you should use Huge Question Metadata.

Have you ever ever created 10 tables in a row after which realized you misnamed a column in each single one in all them? Fortunately, the TABLES metadata views is there to assist.

SELECT table_name, ddl 
FROM the_look.INFORMATION_SCHEMA.TABLES

This request provides you with the DDL (Data Definition Language) question used to create the desk. You may then copy-paste the DDL assertion and edit it to recreate your desk.

The Historical past tab solely provides you entry to jobs that have been executed up to now week. Fortunately, you may overpass these restrictions by utilizing the JOBS views.

SELECT job_type, question
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE DATE(creation_time) = "2022-04-26"

Let’s say you need to choose solely string columns from a desk. Sadly, normal SQL doesn’t will let you choose columns dynamically. That is the place the COLUMNS view is useful.

We are able to get an summary of the columns we wish by querying the view :

SELECT *
FROM the_look.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = "orders_analysed_table" AND data_type = "STRING"

Then we are able to use script inscriptions to retailer the column names right into a variable and show the results of our dynamic question :


DECLARE select_query STRING;
EXECUTE IMMEDIATE
"""SELECT STRING_AGG(column_name, ",")
FROM the_look.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = "orders_analysed_table" AND data_type = "STRING"
"""
INTO select_query;
EXECUTE IMMEDIATE FORMAT("""
SELECT %s
FROM the_look.orders_analysed_table
""", select_query)

What this question does is :

  • Declare a variable of kind string
  • Execute a question to fetch the string columns of the desk and retailer it within the earlier variable
  • Format a choose question with the variable and execute it

Metadata may come in useful when making an attempt to watch your value. You should utilize the JOBS_TIMELINE view to examine the evolution of your spending.

SELECT DATE(job_creation_time) AS date, SUM(total_bytes_billed) AS total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE DATE(job_creation_time) >= "2022-04-01"
GROUP BY 1
ORDER BY 1

Wish to go additional? Why not use one of many Huge Question Time Collection fashions to foretell the bytes billed subsequent months?

More Posts