Bigquery Queries

Use decorator and extract data for specific date adjusted for local time

SELECT
site,
event_type,
count(*) as total_events,
EXACT_COUNT_DISTINCT(event_message_id) as uniq_events
FROM
[email.sendgrid_events@1471590000000-]
WHERE
event_provider = 'ongage'
AND DATE(DATE_ADD(SEC_TO_TIMESTAMP(time_unix), -7, 'HOUR')) = '2016-08-21'
AND site = 'careeralerts.com'
GROUP BY
site, event_type
ORDER BY
site, event_type

Generating date as local time using moment.js

const moment = require('moment-timezone');

// will be -420 minutes which is 7 hours
var timezoneOffsetMinutes = moment.tz.zone('America/Los_Angeles').parse(moment()) * -1;

SELECT
siteId as site,
type as campaignType,
DATE(DATE_ADD(e.ts, timezoneOffsetMinutes, "minute")) as aggDate ...

Get total counts grouped by the hour adjusted for PST time for the past 10 days

SELECT hour(ts)-7,count(*)
FROM [project:DataSet.SomeTable@-864000000-]
WHERE
  platform='ecapi'
GROUP BY 1
ORDER BY 1

get total counts per minute in past hour

SELECT hour(ts), minute(ts), platform, count(*)
FROM [project:DataSet.SomeTable@-3600000-]
WHERE platform='ecapi'
GROUP BY 1,2,3
ORDER BY 1 DESC, 2 DESC,3

totals in past 7 hours per hour

SELECT hour(ts), platform, count(*)
FROM [rm-data:EmailMetricsCollection.Email@-25200000-]
WHERE platform='ecapi'
GROUP BY 1,2
ORDER BY 1 DESC, 2 DESC

last 10 min

SELECT hour(ts), minute(ts), count(*)
FROM [project:DataSet.Table@-600000-]
WHERE platform='ecapi'
GROUP BY 1,2
ORDER BY 1 DESC, 2 DESC

getting count from range of log tables

SELECT count(*) FROM [project:dataSet.table_20190319],[project:dataSet.table_20190320] where resource.labels.namespace_id = 'prod'
and textPayload contains('Dynamic')

excluding fields in group by and select

SELECT date(ts) date, origin, destination,
  --source,  // exclude field from display
  count(*)
FROM [project:dataset.table]
WHERE (_PARTITIONTIME >= "2019-07-25 00:00:00" OR _PARTITIONTIME IS NULL)
GROUP BY 1,2,3--,4 // Will only group by first 3 columns. 4th is ignored
ORDER BY 1 DESC,2,3--,4 // Will only order by first 3 columns. 4th is ignored


Comments

Post a Comment

Popular posts from this blog

Copy partitioned table schema to another table

Use the gcloud bq cli tool 1) export the source table schema to json file. Each item only requires name and type fields. bq show --schema --format=prettyjson projectId:Dataset.table > emailschema.json https://cloud.google.com/bigquery/docs/schemas#specify-schema-manual-cli 2) Create the table in destination as partitioned bq mk --table --schema ./emailschema.json --time_partitioning_type DAY projectId:dataset.table https://cloud.google.com/bigquery/docs/creating-column-partitions
Read more

Using moment.js

const moment = require('moment-timezone'); Calculating duration in seconds from current time to a future time. e.g. the next day const nextDayDateString = moment().tz('America/Los_Angeles').add(1, "days").format("YYYY-MM-DD 00:00:00"); const nextDayDate = moment(nextDayDateString).tz('America/Los_Angeles'); const nextDayExpireSeconds = Math.round(moment.duration(nextDayDate.diff(moment())).asSeconds()); Calculating unix timestamp of certain date in local time const nextDayDt = moment().tz('America/Los_Angeles').add(1, "days").format("YYYY-MM-DD 00:00:00"); const nextDayDtUnix = moment(nextDayDt).tz('America/Los_Angeles').utc().unix(); Determining date string by the number of days back using local time // Will display date string 30 days back const daysBack = 30; var dateFrom = moment.tz(moment().subtract(daysBack,'days').format('YYYY-MM-DD 00:00:00'), 'America/Los_Angele...
Read more

Changing BigQuery table field names

https://cloud.google.com/bigquery/docs/manually-changing-schemas With sql you can change a BQ table field name e.g. SELECT * EXCEPT(last_activity_ts, invalid_reason), last_activity_ts as evpo, invalid_reason as evpo_invalid_reason FROM dataset.tableName This sql will select all fields except the ones you want to change then change the name using as keyword. In bq options select the table you want to change and replace table option. The table will replace itself with original table content but with new field names. For large datasets you are paying for entire table scan and can be expensive.
Read more