この記事は Redash Advent Calendar 2017 4日目の記事です。

実際に試してみた、RedashでのPythonDataSourceを使ったDBのデータのヘルスチェックの仕組みについて紹介します。

Redash v3.0.0で追加されるQuery Resultsデータソースについてを使うともっと簡単にできるのでは思いますが、まだ試していないので2.xでの話になります。

前提

Redshiftなどのデータウェアハウスを運用していると、日次バッチが行われた結果データが正常に生成されたのかなどのヘルスチェックを行う必要が出てくることがあります。
（データが正常無い場合には再度バッチを実行するなど対処が必要になったりします）

ここではRedashだけを使ってデータのヘルスチェックを実現する方法を紹介します。

実際には特定の日時にデータが存在することの確認では、SQLではデータが無い場合は結果が取得できないため
一般的にはカレンダーテーブルを作ってJoinする方法がよく使われるかと思いますが、ここではPythonDataSourceを利用してカレンダーテーブルを使わない方法を試してみます。

実現方法

実現するにあたり以下の3つのクエリを用意します。

RedshiftからSQLでデータを抽出するクエリ
1の結果を利用し、PythonDataSource（欠損日のデータを0件とする）でデータを整形するクエリ
Alert用データの生成（ヘルスチェック対象の抽出）するクエリ

3で作成したデータを、Alert条件として利用します。

RedshiftからSQLでデータを抽出するクエリ

バッチなどで生成されたデータを抽出するクエリです。
過去7日分のデータを取得したりします。

# Queryid 30
SELECT
  TRUNC(DATE_TRUNC('day',a.request_time)) as day,
  COUNT(DISTINCT a.customer_id) as count
FROM
  access_logs a
WHERE
  a.request_time >= dateadd(day,-6,'today')
GROUP BY
  DATE_TRUNC('day',a.request_time)
ORDER BY
  DATE_TRUNC('day',a.request_time)
;

上記のQueryの結果は以下のようになります。

PythonDataSource（欠損日のデータを0件とする）でデータを整形するクエリ

以下のようなPythonDataSourceへのクエリを実行します。
一番新しいデータ（昨日のデータ）が含まれない場合は、0件のデータを生成しています。
（単純に件数の確認だけでも問題場合が多いかもしれませんが、実際はもう少し複雑な要件があるので以下のようなコードをベースにしています）

# Queryid は71
import datetime

# Queryid 30は、dayとcountから構成されている
events_count = get_query_result(30)

# 対象のデータは前日のデータがあるか
target = datetime.datetime.now() + datetime.timedelta(hours=9) + datetime.timedelta(days=-1)
yesterday = target.strftime('%Y-%m-%d')

result = {}
for row in events_count['rows']:
  result[row['day']] = row['count']
  add_result_row(result, {'day': datetime.datetime.strptime(row['day'],'%Y-%m-%d'),
    'count': row['count']})


# Query結果の最新のデータが、前日であるかのチェックを行い前日のデータを0件とする
if (yesterday != events_count['rows'][-1]['day']):
  add_result_row(result, {'day': datetime.datetime.strptime(yesterday,'%Y-%m-%d'), 'count': 0})

add_result_column(result, 'day', '', 'datetime')
add_result_column(result, 'count', '', 'integer')

このようにデータが無い日をRedash上で0件として表示することができるようになります。

Alert用データの生成（ヘルスチェック対象の抽出）するクエリ

アラートのために、最新のデータが無い場合は0件となるようにして、0件の場合はAlertを飛ばすなどとして運用できます。

import datetime

events_count = get_query_result(30)

target = datetime.datetime.now() + datetime.timedelta(hours=9) + datetime.timedelta(days=-1)
yesterday = target.strftime('%Y-%m-%d')

result = {}

if (yesterday != events_count['rows'][-1]['day']):
  add_result_row(result, {'day': datetime.datetime.strptime(yesterday,'%Y-%m-%d'),
    'count': 0})
else:
  add_result_row(result, {'day': events_count['rows'][-1]['day'],
    'count': events_count['rows'][-1]['count']})

add_result_column(result, 'day', '', 'datetime')
add_result_column(result, 'count', '', 'integer')

このように0件と表示されるようにします。
（Query Resultsデータソースを使うとこの辺りは、最初に作ったPythonデータソースを対象に1件だけデータを取得するというようなことにした方が良いと思います）

まとめ

PythonDataSourceを利用してデータのヘルスチェックする方法を紹介してみましたが、カレンダーテーブルが用意できる場合はカレンダーテーブルを利用して実現した方が楽かとは思いますが、まだまだPythonDataSourceでできることは多いかと思います。

5日目の記事

明日は ariarijp さんが「Script Query Runnerについて」の記事を書いてくださるようです。お楽しみに。