Bigquery - Querying Date Sharded Table Using Legacy and Standard SQL

FOR DATE SHARDED TABLE

#legacySQL
SELECT *
FROM TABLE_DATE_RANGE([Project:Dataset.Table_], DATE_ADD(CURRENT_TIMESTAMP(), -3, 'DAY'),
CURRENT_TIMESTAMP())
LIMIT 1000

#standardSQL
SELECT *
FROM `Project.Dataset.Table_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -3 day))
AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())
LIMIT 1000

Bigquery - Querying Day Partioned Table Using Legacy and Standard SQL

FOR PARTITIONED TABLE

#legacySQL
SELECT *
FROM [Project:Dataset.Table]
WHERE _PARTITIONTIME BETWEEN TIMESTAMP(DATE_ADD(CURRENT_TIMESTAMP(), -3, 'Day'))  AND TIMESTAMP(CURRENT_TIMESTAMP())
LIMIT 1000

#standardSQL
SELECT *
FROM `Project.Dataset.Table`
WHERE _PARTITIONTIME BETWEEN TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -3 DAY)) AND CURRENT_TIMESTAMP()
LIMIT 1000

Bigquery - SQL for Flattening Custom Metrics Value

Google Analytics stream data into bigquery in a nested json format, it make sometimes difficult for the users to flatten custom metrics data for each event, this can be overcome by using below custom dimension temp function (Standard SQL only). We can pass customMetrics.index and customMetrics.value as parameter for temp function.

CREATE TEMP FUNCTION
  customMetricByIndex(indx INT64,
    arr ARRAY<STRUCT<index INT64,
    value INT64>>) AS ( (
    SELECT
      x.value
    FROM
      UNNEST(arr) x
    WHERE
      indx=x.index) );

    SELECT visitStarttime, visitId, visitNumber,
    hit.hitNumber AS session_hit_count,
    hit.type AS hit_type,
    hit.page.hostname url_domain_name,
    hit.page,
    customMetricByIndex(3,  hit.customMetrics) AS custom_metrics_1
  FROM
    `project.dataset.ga_sessions_20180909`,
    UNNEST(hits) AS hit
    

Bigquery - SQL for Flattening Custom Dimensions Value

Google Analytics stream data into bigquery in a nested json format, it make sometimes difficult for the users to flatten custom dimension data for each event, this can be overcome by using below custom dimension temp function (Standard SQL only). We can pass customDimensions.index and customDimensions.value as parameter for temp function.

CREATE TEMP FUNCTION
  customDimensionByIndex(indx INT64,
    arr ARRAY<STRUCT<index INT64,
    value STRING>>) AS ( (
    SELECT
      x.value
    FROM
      UNNEST(arr) x
    WHERE
      indx=x.index) );

    SELECT visitStarttime, visitId, visitNumber,
    hit.hitNumber AS session_hit_count,
    hit.type AS hit_type,
    hit.page.hostname url_domain_name,
    hit.page,
    customDimensionByIndex(165,  hit.customDimensions) AS custom_variable_1
  FROM
    `project.dataset.ga_sessions_20180909`,
    UNNEST(hits) AS hit

Bigquery Views for Google Analytics Realtime Session - Standard SQL

People who started using Google Analytics real-time streaming into bigquery may come across a query conflict while calling ga_realtime_sessions table with data range filter condition, e.g.,

when we execute the below query

SELECT * FROM 
TABLE_DATE_RANGE([project:dataset.ga_realtime_sessions_], CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP()) 
LIMIT 1000

We end up with error message
Query Failed

Error: Cannot output multiple independently repeated fields at the same time.

The reason is because of both real-time table and views have same naming pattern

Realtime Table: project:dataset.ga_realtime_sessions_20180929
Realtime View: project:dataset.ga_realtime_sessions_view_20180929

In addition, the real-time view is available in Legacy SQL, so we cannot use it for Standard SQL queries, to overcome this it is good to save below query as view to get realtime data for today.

SELECT  * FROM
  `project.dataset.ga_realtime_sessions_2*`
WHERE
  CONCAT('2', CAST(_TABLE_SUFFIX AS string)) = FORMAT_DATE("%Y%m%d", CURRENT_DATE())
  AND exportKey IN (
  SELECT
    exportKey
  FROM (
    SELECT
      exportKey,
      exportTimeUsec,
      MAX(exportTimeUsec) OVER (PARTITION BY visitKey) AS maxexportTimeUsec
    FROM
      `project.dataset.ga_realtime_sessions_2*`
    WHERE
      CONCAT('2', CAST(_TABLE_SUFFIX AS string)) = FORMAT_DATE("%Y%m%d", CURRENT_DATE()))
  WHERE 
    exportTimeUsec >= maxexportTimeUsec)

Split Strings in Bigquery Using REGEXP

Split Strings in Bigquery Using REGEXP

Assume that we have a bigquery column with values like below:

---------------------------------------------------------
pair
----------------------------------------------------------
television:100
mobile:250
driver: 110
----------------------------------------------------------

Expected Output
---------------------------------------------------------
Device                         | Cost
---------------------------------------------------------
television                    |100
mobile                        | 250
driver                          | 110
----------------------------------------------------------

Use below bigquery statements to split the column:

 CASE
      WHEN REGEXP_MATCH(pair,":") THEN REGEXP_EXTRACT(pair, r'(\w*):')
      ELSE pair
    END AS attribute_name,
    REGEXP_EXTRACT(pair, r'\:(.*)') AS attribute_value

Bigquery Data Load with Command Line

Data Load with Command Line

Data load using bq involves three types:

1. Empty (Default): It writes data into an empty table, if data already exists it throws error.

bq query  ---n=1000 --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

2. Replace: It replace a current table with newly obtained data output. 
It involves loss of existing data in a destination table. 
Use it wisely to perform incremental load which involves update and inserts.

bq query  ---replace --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

3. Append: It appends new records to the existing table. 
If same command is executed more than one time it will create duplicate records. 
Can be used for incremental load which involves only data insert. 
bq query  ---append_table --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

How to Remove HTML Tags and Get the Word Count of a Content in Bigquery

REGEXP to remove html tags and get the word count of a content

SELECT content_id, COUNT(words) wordcount
FROM (
SELECT content_id, SPLIT(tt, '') words
FROM (
SELECT content_id,
REGEXP_REPLACE(content, r'(<[^>]+>|\&(nbsp;)|(amp;)|&#\d\d\d\d)', '') tt,
FROM [project:dataset.table] ))
GROUP BY content_id

BigQuery Get First and Last Day of Months

The below Query helps you to get the First Day and Last Day of a month. This can also be used in TABLE_DATE_RANGE to retrieve data for Previous Months.

SQL

SELECT DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,-1,"MONTH") First_Day_Previous_Month,
       DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE()),"DAY") ,0,"MONTH") Last_Day_Previous_Month,

       DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,0,"MONTH") First_Day_Current_Month

Bigquery Leagacy SQL vs Standard SQL - Array Queries

Bigquery supports both Standard and Legacy SQL, below is the example of how to migrate array related SQLs from legacy to standard SQL.

When we use array type of data in our tables then we should follow below Bigquery Syntaxes:

Legacy

SELECT date, id, code, country, gender, dob, city, location, product.category

FROM  TABLE_DATE_RANGE([PROJECT:DATASET.TABLE_],DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),DATE_ADD(CURRENT_TIMESTAMP(), -0, 'DAY'))

Standard

SELECT date, id, code, country, gender, dob, city, location, prod.category

FROM `PROJECT.DATASET.TABLE_*`,

UNNEST(product) AS prod

WHERE (_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))

AND FORMAT_DATE("%Y%m%d", CURRENT_DATE()))

Big Query Legacy Vs Standard Date Ranges Query

Bigquery supports both Standard and Legacy SQL, below are the few tips how we can migrate SQLs from legacy to standard SQL.

Legacy

For date shard table:

SELECT * FROM

TABLE_DATE_RANGE([PROJECT:DATASET.TABLE_],DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),DATE_ADD(CURRENT_TIMESTAMP(), -0, 'DAY'))

For partitioned table use

_PARTITIONTIME BETWEEN TIMESTAMP(DATE_ADD(CURRENT_TIMESTAMP(), -4, 'Day'))

AND TIMESTAMP(CURRENT_TIMESTAMP())

Standard

For date shard table:

SELECT * FROM

FROM `PROJECT.DATASET.TABLE_*`

WHERE (_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))

AND FORMAT_DATE("%Y%m%d", CURRENT_DATE()))

For partitioned table replace _TABLE_SUFFIX with DATE(_PARTITIONTIME)

Determining _PARTITION details in BigQuery Partitioned Table

Run below query to check the partition summary of Bigquery table:

SELECT DATE(_PARTITIONDATE) AS PT, DATE(CURRENT_TIMESTAMP()) , DATE(DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))
FROM [ProjectId:Dataset.Table]
GROUP BY PT

SELECT project_id, dataset_id, table_id, partition_id
, MSEC_TO_TIMESTAMP(creation_time) Created_date, MSEC_TO_TIMESTAMP(last_modified_time) modified_time
from [ProjectId:Dataset.Table$__PARTITIONS_SUMMARY__]

Install gcloud components in python virtual environment

Installing gcloud components in virtual environment

Step 1: Download google cloud SK file (below is an example for Mac)

url: https://cloud.google.com/sdk/docs/quickstart-mac-os-x

Step 2. Unzip the tar file, navigate to bin folder

>> ./install.sh

if you face any issues like Module Platform missing, execute below command:

>>$ export CLOUDSDK_PYTHON_SITEPACKAGES=1

Step 3: Run gcloud init

Step 4: Open new terminal and run 'bq ls', a authorization steps occurs, complete the authorization.

Google Cloud BQ Command Line Data Load

'bq' is command line tool provided by Google Cloud Platform to access bigquery table and perform operations like DDL, DML, etc. 

Refer http://mahadevanrv.blogspot.in/2016/06/install-google-bigquery-command-line.html for GCloud installation.

Data load using bq involves three types:

1. Empty (Default): It writes data into an empty table, if data already exists it throws error.

bq query  ---n=1000 --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

2. Replace: It replace a current table with newly obtained data output. It involves loss of existing data in a destination table. Use it wisely to perform incremental load which involves update and inserts.

bq query  ---replace --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

3. Append: It appends new records to the existing table. If same command is executed more than one time it will create duplicate records. Can be used for incremental load which involves only data insert.

bq query  ---append_table --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

Google BigQuery: Calculating Time difference in Minute and Hours

Assume you have a date column "OrderedDate", then we can calculate the total difference in minute and hour with respect to current date time:

SELECT
(CURRENT_TIMESTAMP() - TIMESTAMP_TO_USEC(OrderedDate)) / 1000000/ 3600 AS Diff_Hour,
 (CURRENT_TIMESTAMP() - TIMESTAMP_TO_USEC(OrderedDate)) / 1000000/ 60 AS Diff_Minute
FROM [Project:Dataset.Table]

BigQuery Table_Partition_Range - Last 7 Days

BigQuery to retrieve data for Last 7 Days dynamically applying Table_Date_Range:

SELECT *
, DateDiff(DATE_ADD(CURRENT_TIMESTAMP(), -1, 'Day'), DATE_ADD(CURRENT_TIMESTAMP(), -6, 'Day')) Days
FROM (TABLE_DATE_RANGE([dataset.tablename_],DATE_ADD(CURRENT_TIMESTAMP(), -6, 'Day'),
                      DATE_ADD(CURRENT_TIMESTAMP(), -1, 'Day')))
LIMIT 10;

BigQuery Table_Partition_Range - Last 7 Days

BigQuery to retrieve data for Last 7 Days dynamically applying Table_Date_Range:

SELECT *
, DateDiff(DATE_ADD(CURRENT_TIMESTAMP(), -1, 'Day'), DATE_ADD(CURRENT_TIMESTAMP(), -6, 'Day')) Days
FROM (TABLE_DATE_RANGE([dataset.tablename_],DATE_ADD(CURRENT_TIMESTAMP(), -6, 'Day'),
                      DATE_ADD(CURRENT_TIMESTAMP(), -1, 'Day')))
LIMIT 10;

BigQuery - Calculating Runnig Total

The below bigquery helps user to calculate running total of page visit:

SELECT DATE(a.date_time), a.hour,  a.minute, a.views, SUM(a.views) OVER (ORDER BY  a.hour,  a.minute) AS rt
FROM  [sampledate.clicks] a
JOIN  [sampledate.clicks] b ON  a.date_time = b.date_time
WHERE  b.date_time <= a.date_time 
AND DATE(a.date_time) = DATE(DATE_ADD(CURRENT_DATE(),-1,"DAY"))
ORDER BY  a.HOUR,  a.MINUTE

Install Google BigQuery - Command Line Tool

Google BigQuery can also be accessed from command line. To achieve this one need to implement below steps:

1. Install Google cloud using, gcloud install by opening the terminal
2. Check python version -- type python -version
3. Run apt-get install python-setuptools
4. Check if pip is installed, If not found, sudo easy_install pip
5. Install google client tools for python using, pip install --upgrade google-api-python-client .

If not installed try the following commands (only for Mac users)

1. Enter the following at a command prompt:
2. $ curl https://sdk.cloud.google.com | bash
3. Restart your shell:
4. $ exec -l $SHELL
5. Run gcloud init to initialize the gcloud environment:
6. $ gcloud init

6. Initialise gcloud using , gcloud init

7. Check big query using bq command, bq ls

BigQuery - Dealing with Date and Time

The below google bigquery provide us idea to convert timestamp to different date and time values:

SELECT CURRENT_DATE() as currentdate
              , CURRENT_TIME() currenttime, CURRENT_timestamp() currentdatetime
              , DATE(TIMESTAMP(CURRENT_timestamp())) AS dateonly
              , MONTH(TIMESTAMP(CURRENT_timestamp())) as monthonly
              , YEAR(TIMESTAMP(CURRENT_timestamp())) as yearonly
              , HOUR(TIMESTAMP(CURRENT_timestamp())) as houronly
              , MINUTE(TIMESTAMP(CURRENT_timestamp())) as minuteonly
              , SECOND(TIMESTAMP(CURRENT_timestamp())) as secondonly
              , DAY(TIMESTAMP(CURRENT_timestamp())) as dayonly
              , PARSE_UTC_USEC(STRING(CURRENT_timestamp())) as timestamtounix
              , NOW() AS CurrentUnixTimestamp
              , USEC_TO_TIMESTAMP(1465814687003321) AS unixtotimestamp
  FROM [project:dataset.table] LIMIT 1;