Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
¯\_(ツ)_/¯

Pascal Landau paslandau

💭
¯\_(ツ)_/¯
View GitHub Profile
@paslandau
paslandau / monitor-query-costs-in-bigquery-example.sql
Last active Jun 21, 2020
Monitor Query Costs in BigQuery via INFORMATION_SCHEMA views
View monitor-query-costs-in-bigquery-example.sql
# Monitor Query costs in BigQuery; standard-sql; 2020-06-21
# @see http://www.pascallandau.com/bigquery-snippets/monitor-query-costs/
DECLARE timezone STRING DEFAULT "Europe/Berlin";
DECLARE gb_divisor INT64 DEFAULT 1024*1024*1024;
DECLARE tb_divisor INT64 DEFAULT gb_divisor*1024;
DECLARE cost_per_tb_in_dollar INT64 DEFAULT 5;
DECLARE cost_factor FLOAT64 DEFAULT cost_per_tb_in_dollar / tb_divisor;
SELECT
@paslandau
paslandau / calculate-median-bigquery-example.sql
Created Jun 20, 2020
Example: Calculate the MEDIAN in BigQuery
View calculate-median-bigquery-example.sql
# Calculate the MEDIAN in BigQuery; standard-sql; 2020-06-20
# @see http://www.pascallandau.com/bigquery-snippets/calculate-median/
WITH data as (
SELECT
1 as id,
"2020-06-20" as day,
10 as quantity
UNION ALL SELECT 2,"2020-06-20", 15
UNION ALL SELECT 1,"2020-06-21",5
UNION ALL SELECT 2,"2020-06-21",10
@paslandau
paslandau / expression-subqueries-for-nested-repeated-fields.sql
Last active May 29, 2020
How to use expression subqueries to query nested and repeated fields in Google BigQuery
View expression-subqueries-for-nested-repeated-fields.sql
-- Using expression subqueries to query nested and repeated fields in Google BigQuery; 2020-05-29
-- @see http://www.pascallandau.com/bigquery-snippets/expression-subqueries-for-nested-repeated-fields/
WITH example as (
SELECT
1 as id,
[
STRUCT("foo" as key, "foo 1" as value),
STRUCT("bar" as key, "bar 1" as value)
] AS data,
UNION ALL
@paslandau
paslandau / use-temporary-tables-with-named-subquery.sql
Last active May 29, 2020
Using temporary tables via WITH (named subqueries) in Google BigQuery
View use-temporary-tables-with-named-subquery.sql
-- Using temporary tables via WITH (named subqueries) in Google BigQuery; 2020-05-29
-- @see http://www.pascallandau.com/bigquery-snippets/use-temporary-tables-with-named-subquery/
WITH data as (
SELECT
1 as id,
DATE("2018-04-08") AS date,
UNION ALL SELECT 2, DATE("2018-04-09")
UNION ALL SELECT 3, DATE("2018-04-10")
UNION ALL SELECT 4, DATE("2018-04-11")
),
@paslandau
paslandau / use-variables.sql
Last active May 29, 2020
Declare and use variables in BigQuery
View use-variables.sql
-- Declare and use variables in BigQuery; 2020-05-29
-- @see http://www.pascallandau.com/bigquery-snippets/use-variables/
DECLARE foo_var STRING DEFAULT "foo";
SELECT foo_var
@paslandau
paslandau / mysql2csv
Last active Jan 8, 2020
Helper command to export data for an arbitrary mysql query into a CSV file.
View mysql2csv
#!/usr/bin/env php
<?php
$intro = <<<TEXT
Helper command to export data for an arbitrary mysql query into a CSV file.
Especially helpful if the use of "SELECT ... INTO OUTFILE" is not an option, e.g.
because the mysql server is running on a remote host.
Usage example:
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"
@paslandau
paslandau / convert-timestamp-to-different-timezone.sql
Last active Jun 28, 2019
Convert timestamp/date/datetime to different timezone in BigQuery
View convert-timestamp-to-different-timezone.sql
#standardSQL
# Convert date / time to a different timezone in BigQuery; standard-sql; 2018-04-08
# @see http://www.pascallandau.com/bigquery-snippets/convert-timestamp-date-datetime-to-different-timezone/
WITH examples AS (
SELECT TIMESTAMP("2018-04-08T15:50:10+00:00") AS timestamp # Daylight saving time
UNION ALL SELECT TIMESTAMP("2018-03-08T15:50:10+00:00") # Standard time
)
SELECT
timestamp,
DATETIME(timestamp) as datetime,
@paslandau
paslandau / isDirtyFix.php
Last active Sep 18, 2018
Dirty fix for Model::isDirty() in Laravel to honor the $casts attribute
View isDirtyFix.php
<?php
/**
* Determine if the model or given attribute(s) have been modified.
*
* @param Model $m
* @param array|string|null $attributes
* @return bool
*/
function _is_dirty(Model $m, $attributes = null){
@paslandau
paslandau / bigquery-extract-url-parameters-array.sql
Last active Jul 29, 2018
Extract query parameters from a URL as ARRAY in BigQuery
View bigquery-extract-url-parameters-array.sql
#standardSQL
# Extract query parameters from a URL as ARRAY in BigQuery; standard-sql; 2018-04-08
# @see http://www.pascallandau.com/bigquery-snippets/extract-url-parameters-array/
WITH examples AS (
SELECT 1 AS id,
'?foo=bar' AS query,
'simple' AS description
UNION ALL SELECT 2, '?foo=bar&bar=baz', 'multiple params'
UNION ALL SELECT 3, '?foo[]=bar&foo[]=baz', 'arrays'
UNION ALL SELECT 4, '', 'no query'
@paslandau
paslandau / php-encoding-test.php
Last active Jan 10, 2018
Weird behaviour when changing the different php.ini settings to influence the default mb_internal_encoding() encoding.
View php-encoding-test.php
<?php
echo "\n\nPHP: " . phpversion()."\n";
echo "Default: " . mb_internal_encoding()."\n";
echo "Old value of default_charset: ".ini_set("default_charset", "ASCII")."\n";
echo "when default_charset is set to ASCII: " . mb_internal_encoding()."\n";
echo "Old value of internal_encoding: ".ini_set("internal_encoding", "ASCII")."\n";
echo "when internal_encoding is set to ASCII: " . mb_internal_encoding()."\n";
echo "Old value of mbstring.mb_internal_encoding: ". ini_set("mbstring.internal_encoding", "ASCII")."\n";
echo "when mbstring.internal_encoding is set to ASCII: " . mb_internal_encoding()."\n";