Heroku Dataclipsでお手軽公開クエリ #PostgreSQL

Heroku Dataclipsで、Heroku Postgresに対するクエリの結果を手軽に公開することができます。この機能で、Herokuで稼働しているアプリケーションそのものからは提供されていない方法でも、データを活用することができます。この記事では利用手順の概略と利用例を紹介します。

この記事は、Heroku Advent Calendar 2019の2日目の記事です。1日目は狩野さんによる『DeveLove関西で、アジャイル開発におけるHerokuFlowの運用について登壇してきました』でした。アジャイルちゃんと実践してみたいなあ！3日目はsho7650さんによる『Heroku で Linux コマンドを巧みに使いこなす』です。手元のマシンをLinuxにしてもいいのよ？(それができないから苦労してるんだ)

Dataclipを作成する

https://data.heroku.com/dataclips を閲覧し、New Dataclipボタンをクリックします
タイトルを入力し、クエリ先のアプリケーションとデータベースを選択します
必要なら「Published」状態を「Draft」状態にします
入力欄にクエリを入力します
Save & Runボタンを押しクエリの結果が表示されるのを待ちます
満足できるまでクエリを編集しSave & Runボタンを押します
「Draft」状態を「Published」状態にします

これで、アプリケーションにアクセスのある人はDataclipを閲覧できるようになります。

Dataclipを公開する

https://data.heroku.com/dataclips を閲覧し、公開するDataclipを選びます
「Share」ボタンを押し、「Create Public URL」を押します
表示されたURLをコピーして、公開します

公開されたDataclipを確認する

筆者はHerokuでぼっちマストドンサーバを運用しています。下記のようなクエリで、過去2日間のどの時間にたくさん投稿していたかがバレてしまいます。本来はHeroku Postgresへのアクセス権がないとクエリを実行できませんが、Dataclipsによってクエリの結果を公開することができます。

WITH toots AS (
SELECT
DATE_TRUNC('hour', created_at) AT TIME ZONE 'UTC' AT TIME ZONE 'HST' as hour,
id
FROM statuses
WHERE account_id = 1
        AND created_at >= DATE_TRUNC('hour', NOW()) - interval '2 days'
        AND created_at < DATE_TRUNC('hour', NOW())
), hours AS (
SELECT hh AT TIME ZONE 'UTC' AT TIME ZONE 'HST' as local
FROM generate_series(
  (DATE_TRUNC('hour', NOW()) - interval '2 days')::timestamp,
  (DATE_TRUNC('hour', NOW()))::timestamp,
  '1 hour'::interval) hh
)
SELECT local, COUNT(id)
FROM toots RIGHT JOIN hours ON toots.hour = hours.local
GROUP BY local ORDER BY local;

このクエリをDataclipにしてありますので、ちょろっと覗いてみましょう: https://data.heroku.com/dataclips/zwfqiwbctjswnxvukzbnblyfvwnh

右上のボタンでChartを選択することで、簡単なグラフとして表示することもできます。金曜日の10時台に何かあったのかな？

「Share」ボタンから限定されたHerokuアカウントのみに公開することもできます。活用してみてください。