Useful SQL Queries for Big Query

Below are a couple useful SQL queries to get started with searching through Big Query for future data profiling projects:

If already know the table you want to return and which dataset it blongs to, you can return everything in that table with SELECT * FROM [dataset].[table]

If we know the data we wan't is in the chargeback_dly table withing the arap dataset, we can show that table with the following:

SELECT * FROM arap.chargeback_dly

this command displays (SELECT) everthing (*) the selected table. Point to the dataset (arap) and then the table, seperated by a period (arap.chargeback_dly).

If we are looking for a specific piece of data but are unsure which table it is in, we can use INFORMATION_SCHEMA.COLUMNS to search for table containing a column with a piece of a word or string:

SELECT * FROM [dataset].INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%[term you want the column to contain]%'

Adding the % before and after the thing you want to search will also include any results that have something before and/or after whatever you passed it.

SELECT * FROM arap.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%oos%'

Using this we can see a table, sales_diag_detail_base_view containing a column with the term "oos". Having the % before and after the search term allows us to return results that aren't only "oos" without any text before or after.

This is particularly useful when trying to narrow down where a KPI is stored in a big dataset.

Similarly, you can search a dataset for a table containing a term.

SELECT * FROM [dataset].INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%[term you want the column to contain]%

SELECT * FROM arap.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%sales%'

Using this, we can search the dataset and show a list of all the tables containing the work, "sales" in their title, along with other details about the table.

There are many other options and ways to find the data you're looking for in Big Query, but these are just a few simple ones I found helpful when getting starting sifting through big datasets.

-- CashBarnes - 08 Oct 2020