re:dashのQuery作成を効率化する

Query Snippets

データを集計＆可視化して分析するツールとしてre:dashは優秀なツールです。
re:dashのQuery Snippetsに予めよく使うクエリの雛形を書いておくことで
クエリ作成を効率化することができます。

re:dash右上の歯車アイコンよりSetting画面に遷移できます。

New Snippetボタンより作成します。
${1}と記載することでtab展開時に穴埋め形式になります。

新規Query作成時に作成済みSnippetsが候補として出てきて、tabキーで展開することができます。

各データソースに対してよく使うQuery Snippetsのサンプルを紹介します。
各データソースで混じらないように次のようなプレフィックスをつけるようにしています。

_ : MongoDB
$ : BigQuery
@ : Query Results
! : SpreadSheet
¥ : Google Analytics
~ : Athena
プレフィックスなし : python

MongoDB

_mongo: MongoDBの集計はaggregateのoperatorを組み合わせることでほとんどの集計が可能です。

{
    "collection": "${1:collection}",
    "aggregate": [
        ${2}
    ]
}

_match: 条件に一致するデータを取得します。（find）

{
    "\$match": {
        "${1:field}": "${2:condition}"
    }
},

_group: 特定のデータ列の同等のデータに対して集計を行います。（合計値、累計数など）

{
    "\$group": {
        "_id": "$${1:doc}"
    }
}

_first: group時、先頭のデータを取り出します。

{
    "\$first": "$${1:field}"
}

_push: group時、グルーピングされたデータ列を配列データとして格納します。

{
    "\$push": "$${1:field}"
}

_sum: group時、グルーピングされたデータ列の合計値を返却します。fieldが1の場合はグルーピングされた累数を計算できます。

{
    "\$sum": "$${1:field}"
}

_project: 特定のデータ列のみ抜き出します。（select、map）

{
    "\$project": {
        "${1:field}": "$${2:doc}"   
    }
}

_date: date型のデータを指定のフォーマットに整形します。

{
    "\$dateToString":{ "date": ${1:date}, "format": ${2:format}}
}

_cond: 指定条件に一致するデータのとき、thenの値をセット、そうでない場合はelseの値をセットします。

{
    "\$cond": { "if": ${1:condition}, "then": ${2}, "else": ${3} } 
}

_lookup: ObjectIdから別のテーブルを結合します。(join)

{
    "\$lookup": {
        "from": "${1:doc}",
        "localField": "${2:field}",
        "foreignField": "${3:_id}",
        "as": "${4:as}"
    }
}

_unwind: 指定列が配列データの場合、行展開します。

{
    "\$unwind": "$${1:doc}"
}

_sort: 指定の列に対し、ソートする。配列形式でwrapしなければならないのはre:dash独自の仕様

{
    "\$sort": [
        {
            "name": "${1:doc}",
            "direction": ${2:-1}
        }
    ]
}

BigQuery

$select: 特定のデータセットに対して、一週間分のデータを取得するクエリ。基本的にstandardSQLを使ったほうが便利な文法も使えるので#standardSQLを先頭につける。

#standardSQL
SELECT ${1:*}
FROM `${2:dataset}`
WHERE ${3:(DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AND CURRENT_DATE()) AND _PARTITIONTIME BETWEEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 9 DAY)) AND CURRENT_TIMESTAMP()}

$with: 複数のクエリ結果からさらに集計する

#standardSQL
WITH ${1:view1} AS (
        ${2:select1}
    ), ${3:view2} AS (
        ${4:select2}
    )
SELECT ${5:*} FROM ${1:view1}, ${3:view2}

$count_distinct: unigueでかつnullでないデータ（LegacySQLのEXACT_COUNT_DISTINCTに相当）

COUNT(DISTINCT ${1:field}})

$json_exact_scalar: jsonデータのjsonフィールドに一致するデータ

JSON_EXTRACT_SCALAR(${1:field}, "$.${2:json_field}")

$regexp_contains: 正規表現にマッチするデータ（where条件）

REGEXP_CONTAINS(${1:field}, '${2:regexp}')

$group: 特定のデータ列の同等のデータに対して集計を行います。（合計値、累計数など）

GROUP BY ${1:field}

$having: group化したデータで表示するデータの条件を指定します。

HAVING ${1:condition}

$order: 指定の$fieldでソートします。

ORDER BY ${1:field} ${2:DESC}

Query Results

@queryresult : re:dash上の異なる複数のクエリ結果を結合する。redashのquery番号と結合する列fieldを指定する。簡易的な結合はこれでできるが、複雑になるとPythonで整形が必要になる。

SELECT ${1: *}
FROM query_${2:000} a
JOIN query_${3:001} b ON a.${4:field} = b.${4:field}

Python

python: redash上でpythonが使えます＝大体なんでもできる。他のクエリ結果をpythonで整形したり、pythonのライブラリが使えます。get_query_resultで別のre:dashクエリを取得し、add_result_rowで表示するデータを行として出力します。add_result_columnで出力するデータ列を指定します。

data = get_query_result(${1:000})

for row in data['rows']:
    add_result_row(result, row)

add_result_column(result, '${2:field}', '', 'string')

request: 外部のAPIをコールしてjsonデータを取得します。

res = requests.get(${1:url}).json()

Google Analytics

¥ga: 特定のメトリックスのディメンションに対してデータを取得する

{
    "ids": "ga:${1:ProjectId}",
    "start_date": "${2:30}daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:${3:sessions}",
    "dimensions": "${4:ga:medium,ga:dimension1,ga:month}",
    "filters" : "${5:dimension1==1}",
    "sort": "-ga:${6:month}"
}

SpreadSheet

!spreadsheet: SpreadSheetのシートをそのまま取得、SheetIdとシート番号を指定する

${1:sheetId}|${1:0}

Athena

~athena: CloudFrontのアクセスログ（S3保存ログ）からアクセスデータを取得する

select ${1:*}
from logs.cloudfront_log
where ${2:date > date_add('day',-2, now()) AND date < date_add('day', -1, now())}
${3:limit 100}