paslandau

# 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

# 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

-- 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

-- 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

-- 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

#!/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

#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

#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

<?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";

paslandau

https://www.aboutyou.at/ with Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
HTTP/1.1 302 Found
Location: https://m.aboutyou.at/
HTTP/1.1 200 OK

https://www.aboutyou.ch/ with Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
HTTP/1.1 302 Found
Location: https://m.aboutyou.ch/
HTTP/1.1 200 OK