View create-or-replace-bigquery-table-with-partition.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace table my_dataset.my_new_partitioned_table PARTITION BY date_column | |
as | |
( | |
select * | |
from my_dataset.my_table | |
) | |
View buigquery-angle-of-intersection.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
gislib.sp.ST_InitialBearing( ST_GeogPoint(45,35), ST_GeogPoint(135,35) ), | |
gislib.sp.ST_InitialBearing( ST_GeogPoint(-94.58, 39.1), ST_GeogPoint(-90.2, 38.63) ) |
View percentiles-in-bigquery.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select | |
percentiles[offset(10)] as p10, | |
percentiles[offset(25)] as p25, | |
percentiles[offset(50)] as p50, | |
percentiles[offset(75)] as p75, | |
percentiles[offset(90)] as p90, | |
from ( | |
select approx_quantiles(char_length(text), 100) percentiles |
View bigquery-use-parameterised-query.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE ORG_NAME STRING; | |
SET ORG_NAME = "AFL"; | |
EXECUTE IMMEDIATE """ | |
WITH x as | |
( | |
select | |
country | |
, category | |
, subcategory | |
, organisation |
View first_last_date_current_month_bigquery.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
How to find FIRST_DAY, LAST_DAY of current month, next month, previous month in Google Bigquery | |
Using #Standard SQL | |
First day of the current month: | |
```SQL | |
SELECT DATE_TRUNC(CURRENT_DATE(), MONTH) | |
``` | |
Last day of the current month (first day next month minus 1): |
View bigquery-row-for-each-date-in-range.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with dates as ( | |
SELECT day, | |
FORMAT_DATE('%A', day) AS dow | |
FROM UNNEST( | |
GENERATE_DATE_ARRAY(DATE('2020-02-01'), DATE('2020-02-29'), INTERVAL 1 DAY) | |
) AS day | |
) |
View handler.js
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var https = require('https'); | |
var util = require('util'); | |
const SLACK_URI = process.env.SLACK_URI || 'XXXX/XXXX/XXXXXXXXXXXXXXXX' | |
const SLACK_CHANNEL = process.env.SLACK_CHANNEL || 'YOUR-SLACK-CHANNEL' | |
exports.handler = async (event, context) => { | |
console.log(JSON.stringify(event, null, 2)); |
View Service downtime report - template
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Executive summary | |
- brief paragraph that anyone can understand & apology for those affected. | |
The rest of the report should be purely factual with no emotion & or blame. | |
Issue Summary | |
- short summary (5 sentences) | |
- list the duration along with start and end times (include timezone) | |
- state the impact (most user requests resulted in 500 errors, at peak 100%) | |
- close with root cause |