Posts

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
Post a Comment
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...
Post a Comment
Read more

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   plat...
Post a Comment
Read more

Git tagging

1) view existing tags in repo git tag 2) Set a tag on latest commit git tag -a v1.4 -m 'major release' 3) set a tag on specific commit with commit id git tag -a v1.2 9fcddd 4) push up tags to repo git push --tags origin master 5) Delete a tag remove from repo: git push --delete origin tagname remove from local: git tag --delete tagname 6) reverting back to a tag. Its best to revert back in a new branch so not to lose commits. git checkout -b branchname v1.2 or git reset --hard v1.2 7) See commit attached to the tag git show v1.2
Post a Comment
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.
Post a Comment
Read more