🦐

🦐🦐🦐Markdownで書くBIツール、Evidence触ってみた🦐🦐🦐

2022/12/10に公開

tech

気にはなってるけど触ってないビッグデータ系のツール・サービスを触る Advent Calendar 2022の#9です。

Evidenceとは

MarkdownにSQLクエリやグラフの設定を記載し、レポート用の静的なHTMLドキュメントを作成するツールです。
デモ画面を見ていただくと、作成できるレポートのイメージがしやすいと思います。

この方法（コードでレポートを定義、静的なHTMLドキュメントを作成）により、

ソースコードと同じように、バージョン管理やレビュー
SQLクエリの結果を利用した、レポートの動的な制御（テンプレート）
色々な場所への埋め込みがしやすい

などのご利益がありそうです。

（Evidence公式サイトより抜粋）

インストール・プロジェクトの初期化

プロジェクトを設定するディレクトリで、npx degit evidence-dev/templateコマンドを実行します。

# my-projectというディレクトリを作成し、evidenceのプロジェクトのひな形を準備します
npx degit evidence-dev/template my-project
npm install

# 開発サーバの起動
npm run dev

うまくいくとブラウザが開き、Welcome画面が表示されるはずです。

DB設定

EvidenceはBigQueryやSnowflakeなどのDWH、PostgreSQLなどのRDS、SQLite・DuckDBなどの組み込みのDBに対応しています。
ここでは、re_data・re_cloudで使ったPostgreSQLを使って試してみます。

上の記事のデータベースを起動・データを準備（dbt seeds）し、npm run devで開いたドキュメントで、データベースの接続を設定します。

なお、この認証情報は.evidenceディレクトリに保存されるようです（バージョンコントロールに入れないように注意した方が良さそう）。

cat ./.evidence/template/evidence.settings.json
{"database":"postgres","credentials":{"host":"127.0.0.1","port":"5433","database":"toy_shop","user":"postgres","password":"admin"}}

レポート作成してみる

ひな形で作られるindex.mdを上書きし、レポート作成を試してみます。

クエリ

Evidenceでクエリを実行するには、コードブロック（```）で囲んだSQLクエリを記載します。また、テーブルやグラフでクエリ結果を参照できるように、コードブロックの右側にクエリの名前を記載しておきます。

```customer_list
 SELECT * FROM customers;
```

レポートにSQL（とデータ）が追加されます。

テーブル

テーブルを試してみます。先ほどのcustomer_listに追加して、DataTableを追加するだけです。

```customer_list
 SELECT * FROM customers;
```

<DataTable
  data={customer_list}
/>
```

HTMLドキュメントを開くと、SQLとテーブルが表示されていると思います。

テーブルは横方向にスクロールします（個人的にはわかりにくい…）
テーブルの上にマウスポインタ持っていき、右下の「Download」を選ぶと、 CSVダウンロードもできます

でっかい数字

KPIなど重要な情報を表示するために、テーブルではなく、でっかい数字を表示することができます（Single Value Chartという名前？）。


```sum_amount_this_month
SELECT SUM(amount) AS amount FROM orders LIMIT 1;
```


<BigValue 
    data={sum_amount_this_month}
    value='amount'
    title='Total Amount'
/>

でっかい数字が表示されます。

グラフ

グラフは

参照するクエリ
x軸とy軸

を指定したChartを追加します。

```sum_amount_per_month
 SELECT date, sum(amount) AS amount FROM (SELECT amount, date(time_created) AS date FROM orders) s GROUP BY date ORDER BY date;
```


<BarChart  
    data={sum_amount_per_month}
    x='date'
    y='amount'
    title='Monthly Amount'
/>

<LineChart
    data={sum_amount_per_month}
    x='date'
    y='amount'
    title='Monthly Amount'
/>

棒グラフと折れ線グラフが追加されました。

Query Chaining

${テーブル名}のように囲むと、dbtのrefのように、あるクエリを他のクエリから参照することができます。

先ほどのsum_amount_per_monthを別のクエリから参照してみます。
（なお、DBによってはサブクエリにエイリアスを付ける必要があります）


```sum_amount_last_month
 SELECT date, amount FROM ${sum_amount_per_month} s WHERE date = (SELECT MAX(date(date)) AS date FROM ${sum_amount_per_month} ss )
```

<BigValue 
    data={sum_amount_last_month}
    value='amount'
    title='Last Amount'
/>

それっぽいクエリとでっかい数字が追加されました。

テンプレート

クエリの実行結果を利用し、HTMLを動的に制御（テンプレート）することも出来ます。


```amount_per_customer
 SELECT id,name,SUM(amount) AS amount FROM (SELECT customers.id, name, amount FROM customers INNER JOIN orders ON customers.id = orders.customer_id) s GROUP BY id,name;
```


{#each amount_per_customer as customer}

{#if customer.amount > 425000 }
{customer.name}さんは我々のチョベリグな顧客です。

{/if}

配信

これまではnpm run devで開発用サーバ経由でレポートを閲覧していました。
npm run buildを実行するとbuildディレクトリに静的ファイルが作成されるので、ホスティングサービスにデプロイすると、レポートを配信することが出来ます。

SQLクエリはnpm run buildの時に実行され、レポート表示時には実行されません
- 「Instead, Evidence runs your queries once, at build time, and statically generates all of the pages in your projec」
ドキュメントにはNetlify・Vercelでビルド・配信する例が記載されています
ビルド・ホスティングはそこ以外でも可能です。例えば、ローカルでビルドしてNetlifyやS3から配信することも可能です

ローカルでビルドし、S3とNetlifyで試して表示できることを試してみました。