Skip to content

Instantly share code, notes, and snippets.

View thekensta's full-sized avatar

Chris Kenwright thekensta

View GitHub Profile
@thekensta
thekensta / big_query_costs_by_user.sql
Created June 12, 2018 13:44
Big Query Costs By User
/*
# https://cloud.google.com/bigquery/audit-logs#sample_audit_queries_in_bigquery
select
protopayload_auditlog.authenticationInfo.principalEmail as user_email,
protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query,
-- protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.state,
-- protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.code,
-- protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message,
sum(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes) billedBytes,
@thekensta
thekensta / price_elasticity.ipynb
Created April 9, 2018 18:08
Price Elasticity from Analytics Made Skeazy
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@thekensta
thekensta / bq_sales_yoy.sql
Created December 21, 2017 14:35
Year on Year Day of Week Sales in Big Query using GENERATE_DATE_ARRAY
--
-- Week Daily for non-Orthogonal Date dimensions e.g. day of week
--
with Calendar as (
select
this_year,
date_add(this_year, interval - 364 day) as last_year
from
unnest(GENERATE_DATE_ARRAY('2017-01-01', '2017-12-31')) as this_year
),
@thekensta
thekensta / fun_with_time.bash
Created December 6, 2017 21:26
Load and Extract DATETIMES and TIMESTAMPS in Big Query
#!/usr/bin/env bash
DATASET="<YOUR DATASET HERE>"
TABLE="fun_with_time"
SCHEMA_FILE="/tmp/ts-schema.json"
DATA_FILE="/tmp/ts-data.ndjson"
SQL_FILE="/tmp/query.sql"
BUCKET="<YOUR BUCKET HERE>"
cat <<EOM >${SCHEMA_FILE}
@thekensta
thekensta / gigaclear_scrape.py
Last active November 7, 2017 08:43
Fetch raw data for Gigaclear to see where their network is live
"""
Script to scrape Gigaclear status for each postcode (in this case in GL* areas)
Writes a Json file with lat-lon, postcode and response for display and
later reference
"""
import requests
import csv
import io
import time
@thekensta
thekensta / gigaclear_map.ipynb
Last active November 7, 2017 08:37
Map of Postcodes Activated by Gigaclear
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@thekensta
thekensta / gigaclear.ipynb
Last active October 30, 2017 10:44
Get Network Status for a Post Code
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@thekensta
thekensta / gbq_unnest.sql
Created October 18, 2017 08:49
Big Query Unnesting Structs
with X as (
-- select STRUCT<x int64, y string>(1, 'a') as R
select *
-- from unnest(split("I am the bad man, I am the bad man", " ")) as w with offset as c
from
unnest([
-- Will be named and explictly typed
-- STRUCT<x int64, y string>(1, 'a'),
-- STRUCT<x int64, y string>(2, 'b'),
-- STRUCT<x int64, y string>(3, 'c')
@thekensta
thekensta / pandas_pivot_multiindex.py
Last active January 19, 2016 15:39
Pandas Pivot Multi Index
x = pd.DataFrame({"Date": np.repeat(pd.date_range("2015-01-01", "2015-01-04"), 2),
"Class": [1, 2, 1, 2, 1, 2, 1, 2],
"Value": np.random.random(size=8)})
p1 = pd.pivot_table(x, index="Date", columns="Class")
p2 = pd.pivot_table(x, index="Date", columns="Class", values="Value")
p1
# Value
# Class 1 2
@thekensta
thekensta / read_aws_credentials.py
Created January 19, 2016 12:42
AWS Keys from Credentials
"""Really simple example of reading aws Key and Secret from credentials file. """
import os
from ConfigParser import SafeConfigParser # Python2
def main(profile="default"):
config = SafeConfigParser()
config.read(os.path.join(os.getenv("HOME"), ".aws/credentials"))
secret = config.get(profile, "aws_secret_access_key")