March 4, 2021
やりたいこと # GAS から BigQuery へデータセットをロードする CSV によるロード JSONL によるロード CSV によるロード # 基本的にはリファレンスにあるサンプルコード通りに書けばよい テーブルを作成しながらロードするのでテーブル定義もコードに含まれる テーブル作成有無を気にしたくなかったのでサンプルコードに対して「テーブルが存在すれば削除して再作成してからロード」を追加している function loadCSVIntoBigQuery() { const csv = '1,2,abc,2021-03-01\n3,4,efg,2021-03-02'; const projectId = '<YOUR PROJECT ID IS HERE>'; const datasetId = '<YOUR DATASET ID IS HERE>'; const tableId = '<TABLE NAME TO BE CREATED>'; try { BigQuery.Tables.remove(projectId, datasetId, tableId); } catch(e) { console.log(e); } BigQuery.Tables.insert({ tableReference: { projectId, datasetId, tableId }, schema: { fields: [ {name: 'col1', type: 'INTEGER'}, {name: 'col2', type: 'INTEGER'}, {name: 'col3', type: 'STRING'}, {name: 'col4', type: 'DATE'}, ] } }, projectId, datasetId); const data = Utilities.
...
February 27, 2021
やりたいこと # Google Apps Script から BigQuery へクエリを発行する 発行したクエリの結果を取得する 事前に知っておくべきこと # 基本的にはリファレンスにある BigQuery Service のサンプルコード通りに書けばよい 標準 SQL とレガシー SQL というものがあり、BigQuery コンソールのデフォルトが標準 SQL、GAS の BigQuery ライブラリのデフォルトがレガシー SQL のため単純にコピペするとその差異で動かなかったりする クエリを渡す request オブジェクトに useLegacySql: false を渡してやると標準SQLで動く サンプルコード # function runQuery() { const projectId = '<YOUR PROJECT NAME HERE>'; let queryResults = BigQuery.Jobs.query({ useLegacySql: false, query: ` SELECT 1 AS num, 'aaa' AS str UNION ALL SELECT 2 AS num, 'bbb' AS str UNION ALL SELECT 3 AS num, 'ccc' AS str ` }, projectId); const jobId = queryResults.
...
February 20, 2021
移動平均とは？ # 移動平均 - Wikipedia
BigQueryの分析関数 # 行をグルーピングして、そのグループに対して値を計算する。グループを指定するのが OVER 句。
WITH dates AS ( SELECT date, (MOD(offset, 3) + 1) * (MOD(offset, 5) + 1) + (MOD(offset, 7) + 1) AS num FROM UNNEST(GENERATE_DATE_ARRAY('2021-02-01', '2021-02-28')) AS date WITH OFFSET ) SELECT date, num, AVG(num) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS num_sma FROM dates ORDER BY date 現在の行 + 前2行（3行分）の移動平均が num_sma で出力される。
See Also # 標準SQLの分析関数のコンセプト | BigQuery | Google Cloud
February 16, 2021
UNNEST と併用することで機械的な値テーブルの生成が楽になるかもしれない。
INT型の配列を生成する GENERATE_ARRAY # SELECT * FROM UNNEST(GENERATE_ARRAY(0, 10, 2)) DATE型の配列を生成する GENERATE_DATE_ARRAY # SELECT * FROM UNNEST(GENERATE_DATE_ARRAY('2021-02-01', '2021-02-28')) 7日間隔の日付（つまり1週間毎の日付）も作成できる。
SELECT * FROM UNNEST(GENERATE_DATE_ARRAY('2021-02-01', '2021-02-28', INTERVAL 7 DAY)) See Also # 標準SQLの配列関数 | BigQuery | Google Cloud
February 16, 2021
よく使いそうな BigQuery の文字列関数の使い方をメモ。
CONCAT 文字列の連結 # 引数で指定した文字列を全て連結した文字列を返す。引数の数は可変。
SELECT a, b, CONCAT(a, b) AS concat_with_a_and_b FROM UNNEST([ STRUCT<a STRING, b STRING>('a', 'b'), STRUCT('c', 'd'), STRUCT('e', 'f') ]) ARRAY_TO_STRING 配列各要素の文字列連結 # ARRAYの各要素を文字列連結できる。
SELECT ARRAY_TO_STRING(['a','b','c'], '_') LPAD 文字列長を固定化、前ゼロ # SELECT LPAD('123', 8), LPAD('456', 8, '0') REGEXP_CONTAINS 正規表現によるパターンチェック # SELECT REGEXP_CONTAINS('abcdefg', R'c.e'), REGEXP_CONTAINS('abcdefg', R'x.z') REGEXP_EXTRACT, REGEXP_REPLACE 正規表現によるパターン抽出 # SELECT REGEXP_EXTRACT('https://codeemall.info/abc/index.html', R'https://[^/]+/([^/]+)/.*', 1), REGEXP_REPLACE('https://codeemall.info/abc/index.html', R'https://[^/]+/([^/]+)/.*', 'path is \\1') See Also # 標準SQLの文字列関数 | BigQuery | Google Cloud
February 16, 2021
配列をテーブルとして扱う # UNNEST 演算子を利用すると配列をテーブルとして扱うことができる。
SELECT * FROM UNNEST([1,2,3,4,5]) AS でエイリアスを付ければアクセスできる。
SELECT SUM(num) FROM UNNEST([1,2,3,4,5]) AS num STRUCT の配列をテーブルとして扱う # SELECT * FROM UNNEST([ (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e') ]) ただこれだと匿名フィールドになって扱いづらい。STRUCTで型を明示するとアクセスできる。型を宣言するのは最初の要素だけで良い。
SELECT * FROM UNNEST([ STRUCT<num INT64, str STRING>(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e') ]) 順序を保持する # UNNEST は順序を保持しない（保持することを保証しない）とある。順序を保持する場合は WITH OFFSET を付与してそれで ORDER BY する。
SELECT * FROM UNNEST(['a','b','c','d','e']) WITH OFFSET AS offset ORDER BY offset WITH OFFSET でエイリアスを指定しない場合のデフォルト名は offset らしい（上記サンプルコードはデフォルトと同じ名前を指定している）
...
February 7, 2021
BigQueryでサポートされるデータ型 # 数値型 INT64 NUMERIC BIGNUMERIC FLOAT64 ブール型 BOOL 文字列型 STRING バイト型 BYTES 日付型 DATE 日時型 DATETIME 時刻型 TIME タイムスタンプ型 TIMESTAMP 配列型 ARRAY 構造体型 STRUCT 地理型 GEOGRAPHY SELECT NULL AS val_of_NULL, 1 AS val_of_INT64, 0xFF AS val_of_INT64, 1.0 AS val_of_FLOAT64, NUMERIC '1' AS val_of_NUMERIC, BIGNUMERIC '1' AS val_of_BIGNUMERIC, TRUE AS val_of_BOOL_TRUE, FALSE AS val_of_BOOL_FALSE, 'string with single quote' AS val_of_STRING, "string with double quote" AS val_of_STRING, '''string with triple single quote it can have multi lines''' AS val_of_STRING, """string with triple double quote it can have multi lines""" AS val_of_STRING, B'bytes' AS val_of_BYTES, R'regexp' AS val_of_STRING_as_RegExp, DATE '2021-02-07' AS val_of_DATE, DATETIME '2021-02-07 15:10:20' AS val_of_DATETIME, TIME '15:10:20' AS val_of_TIME, TIMESTAMP '2021-02-07T15:10:20+0900' AS val_of_TIMESTAMP, [1,2,3] AS val_of_ARRAY_OF_INT64, ARRAY[1,2,3] AS val_of_ARRAY_OF_INT64, ARRAY<INT64>[1,2,3] AS val_of_ARRAY_OF_INT64, (123, 'string') AS val_of_STRUCT, STRUCT(123 AS num, 'string' AS str) AS val_of_STRUCT, STRUCT<num INT64, str STRING>(123, 'string') AS val_of_STRUCT, R プレフィックスの正規表現リテラル # 正規表現のデータ型があるわけではないので中身は STRING かもしれない。 R プレフィックスを使うとエスケープシーケンスの扱いが変わり正規表現が書きやすい。
...