- ECBの eurofxref-hist.zip は単純な為替レートCSVの束にすぎないが、
curl、gunzip、sqlite3 だけでドルがユーロに対して最も強かった日付 2000-10-26 をすぐに見つけられる
- 元データは
Date の後ろに通貨ごとの列が続く wide format なので分析には扱いづらく、Date,Currency,Rate 形式の long format に変換する整形が必要になる
- 各行末の trailing comma のせいでCSVパーサーが空列を読み込み、Pandasでは
.iloc[:,:-1] で最後の列を削除してはじめて melt の結果がきれいになる
- 整形したCSVは csvbase に HTTP PUT でアップロードしておけば、
gnuplot、DuckDB、sqlite3 のようなツールとつないでグラフ作成、移動平均の計算、HTTP CSV の読み込みに活用できる
- アクセス交渉、認証、割り当て量、複雑なAPIドキュメントなしに取得できる公開データは open API のように機能し、単純なzipファイルでも金融アプリケーションのデータ交換基盤になりうる
zipファイル1つで為替を問い合わせる
- ECBはユーロと他通貨の過去為替データを 公式zipファイル として公開している
- 以下のパイプラインはデータをダウンロードして展開し、SQLiteのメモリDBにCSVを読み込んでUSDの値で並べ替えたうえで最初の日付を求める
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip \
| gunzip \
| sqlite3 ':memory:' '.import /dev/stdin stdin' \
"select Date from stdin order by USD asc limit 1;"
- 出力は
2000-10-26 である
curl -s は標準エラーの雑音を減らし、gunzip はzipファイルを展開する- Mac OS や BSD ではBSD系の
gunzip はzipファイルをサポートしないため、代わりに bsdtar -xOf - を使う必要がある
sqlite3 ':memory:' はインメモリDBを使い、.import /dev/stdin stdin は標準入力を stdin テーブルとして取り込む
CSV形式の整形とPandas melt
- 元のCSVヘッダーは
Date,USD,JPY,BGN,CYP,CZK,DKK,... のように日付列の後に通貨ごとの列が続く wide format である
- フィルターや集計を行うには
Date,Currency,Rate 形式の long format のほうが扱いやすい
- wide format を long format に変換する作業は一般に melt と呼ばれる
- ほとんどのSQLデータベースには melt に相当する演算がないため、データ整形にはPandasが便利である
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).melt("Date").to_csv(sys.stdout, index=False)'
- ECBのファイルには各行末に trailing comma があり、CSVパーサーが最後に空の列を余分に読み込む
- この空列は
melt 結果の末尾に不要な行を作るため、削除が必要になる
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)'
.iloc[:, :-1] は全行と、最後の列を除くすべての列を選択する- ECBの為替データはフォーマット整形が必要だが、アクセス交渉、支払い、営業担当者との会話、メールアドレス・会社名・役職の提出、割り当て量、認証、APIドキュメントの読解なしですぐに使える
- 基本フォーマットと形の問題だけ対処すればよいため、公開データ配布としては比較的よい部類である
csvbaseに整形済みデータをアップロードする
- 整形したCSVは csvbase table にアップロードしておけば、毎回の整形作業を避けられる
- 既存のパイプラインの末尾に
curl を1つ追加するだけで、CSVを HTTP PUT でアップロードできる
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)' | \
curl -n --upload-file - \
'https://csvbase.com/calpaterson/eurofxref-hist?public=yes'
--upload-file - は標準入力から受け取ったデータを指定URLへアップロードする- csvbase にテーブルがなければ新しく作成し、あればそのテーブルにデータを入れる
-n は ~/.netrc の 認証情報 を使用する
gnuplotで為替グラフを描く
- 整形済みの csvbase テーブルは
curl でCSVを取得し、grep、cut、gnuplot とつなげられる
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
grep USD | \
cut -d, -f 2,4 | \
gnuplot -e "set datafile separator ','; set term dumb; \
plot '-' using 1:2 with lines title 'usd'"
- このコマンドは6,000個を超えるデータポイントを 80x25文字ターミナル 上である程度読めるASCIIアートとして描画する
gnuplot の設定はCSV入力を受け取り、日付と為替レートを線グラフとして描くように合わせてある
set datafile separator ',': 入力がCSVであることを指定set term dumb: ASCIIアートで描画plot -: 標準入力からデータを受け取るusing 1:2 with lines: 1列目と2列目、つまり日付と為替レートで線を描くtitle 'usd': 線の名前を usd に指定
- SVG画像として出力することもでき、時系列データらしく見せるにはx軸が時間であることを指定し、時間形式とx軸目盛りの回転も設定する必要がある
- 繰り返し使うために
plot_timeseries_to_svg という Bash 関数にまとめられる
DuckDBで移動平均を計算する
- USD為替レートのトレンド線を見るには、DuckDBで 移動平均 を計算できる
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
duckdb -csv -c "select Date, avg(value) over \
(order by date rows between 100 preceding and current row) \
as rolling from read_csv_auto('/dev/stdin')
where variable = 'USD';" | \
plot_timeseries_to_svg rolling
duckdb がなくても、同じクエリを sqlite3 用に書き換えるのはそれほど難しくない- DuckDBはSQLiteに似ているが、行指向ではなく 列指向 である
- DuckDBはHTTP上のCSVを直接読み込み、テーブルファイルを作成できる
CREATE TABLE eurofxref_hist AS SELECT * FROM
read_csv_auto("https://csvbase.com/calpaterson/eurofxref-hist";);
- DuckDBは型推論がかなり優秀で、ターミナルサイズを検知して大きな結果をデフォルトで折りたたんで表示する
- 大きなクエリでは進行バーを表示でき、Markdownテーブル出力も可能である
公開データがopen APIのように機能する仕組み
- zipファイル内のCSVと、
brew install または apt install で簡単に入れられるツールだけでも多くのことができる
eurofxref-hist.zip は組織間データ交換プロトコルとして非常に単純な形である- このzipファイルは小さく見えるが、多くの金融アプリケーションが毎日利用している
- ECBが trailing comma をそのまま残しているのは、いま削除すると多くのコードが壊れる可能性があるためだと考えられる
- 公開データが非常に簡単に提供されていれば、open API の役割も果たす
- 多くのAPIがリモート関数呼び出しよりデータ交換に近いのであれば、簡単に取得できる公開データと機能的には大きく変わらない
csvbaseの単純なURLとHTTP動詞
https://csvbase.com/<username>/<table_name>;
https://csvbase.com/calpaterson/eurofxref-hist
- 各URLには4つの主要なHTTP動詞がある
GET: CSVを取得する。ブラウザではWebページを受け取れるPUT: 新しいCSVで新規テーブルを作成するか、既存テーブルを上書きするPOST: 既存テーブルにCSV行を大量追加するDELETE: そのテーブルを削除する
- 認証には HTTP Basic Auth を使う
データ整形とパイプラインについてのメモ
- SQLデータベースで melt に相当する機能を提供するものとして、Snowflakeの UNPIVOT と MS SQL Server の PIVOT/UNPIVOT がある
- RやPandasが使われる重要な理由の1つは、データ整形 機能が強い点にある
- Bashパイプラインはマルチプロセスで動作し、各プログラムが独立したプロセスで並列実行される
curl がWebからデータをダウンロードしている間に、grep はフィルタリングし、sqlite はクエリし、別の curl は再アップロードできる- この構造は クラウド代替と競争力のある性能 を出せることがある
- 2000年10月のユーロに対するドル為替レートは
0.8252 で、これは1ドルで1.21ユーロを買えたことを意味する
- ユーロは1999年1月に紙幣と硬貨なしで発足し、当初は銀行内部にのみ存在していて、紙幣と硬貨は後から登場した
1件のコメント
Hacker News のコメント
ECBで15年ほど前に働いていたとき、このファイルを覚えている。
このファイルはECBのウェブサイトで圧倒的に多くダウンロードされていたファイルで、多くの人や金融機関が毎日取得して自分たちのシステムを更新するのに使っていた。
毎日決まった公開時刻の直後の数分間はトラフィックが大きく跳ね上がり、展開すると単純なCSVファイルになるようにしたのは意図的な判断だった。
そのおかげで、安定して高速に、少ないリソースでファイルを提供できた。当時ECBの公開ウェブサイトを担当していた小さなチームは、このデータを単一の静的ファイルとして提供するという技術的判断を大いに誇ってよかったと思う。
派手さもなく、フレームワークもない。
15年ほど前、誰もが一度は製品を買ったことがありそうな古い大企業で、製品記録システムと、合併・買収で残った下位／並行システムとの間のデータ交換を扱っていたが、そのほとんどは固定長ファイルや区切り文字付きファイルをSFTPサーバーでやり取りする大量インポート／エクスポートだった。
当時その製品はすでに15年物で、そうしたデータソースやエクスポートが20〜30個ほど行き来していたが、とてもよく動いていた。
今も大きな変更なしに使われている可能性が高く、当時フロントエンドはSmalltalk製の古いものを書き直している最中だった。
私たちが使っていたデータソースの中で一番扱いやすかった。
アーキテクトは、ZIPはこの目的の仕様に合った形式ではないと言い、コンプライアンスは個人情報漏えいチェックが必要だと言い、リスク部門は悪意ある行為者がファイルをダウンロードできないようにすべきだと言うだろう。
ウェブ担当者は、サイトに何か追加するには承認済みの変更手順が必要だと言いそうだ。
単純なファイルダウンロードとCSVファイルは素晴らしい。
もっと多くのところが、データをこうした単純な形式で公開してくれればいいのにと思うし、米国政府のデータダウンロードで「カート」に入れなければならないたびに少しずつ死ぬ気分になる。
この特定のパイプラインを簡単にするラッパーツールもたくさんあるし、Webビューやもう少し高度な機能が必要ならDatasetteのようなものもよい。
ZIPファイルをストリームとして読み、CSVを行単位で処理して変換したあと、PostgresならCOPY FROM stdinを使ってデータベースにロードできる。
とても論理的で便利に見えるのに、今まで出会ったことがなかった。
CSV形式のレポートが多いので、早くクエリを高速に回すのに使ってみたい。
例えば
"Look, this contains \"quotes\"!",012345と"Look, this contains ""quotes""!",012345のように引用符の扱い方が分かれ、さらに壊れた例として"Look, this contains "quotes"!",012345やLook, this contains "quotes"!,012345もあり得る。スプレッドシートの痕跡として、
"Look, this contains ""quotes""!",12345のように先頭の0が削られることもある。理論上はJSONも手で直して半端に壊れたファイルにできるが、実際にはJSONファイルにそういうことをする例はほとんど見たことがないし、シリアル番号のような値もJSONでは「親切な」アプリが先頭の0を削ってしまう整数ではなく、文字列として残ることが多い。
いったいなぜそんなことをするのか、正当な理由はあるのだろうか。
CSVをZIPにまとめたJSON文書に変えても、利点は同じだ。
本当の問題は、静的に提供される1つのファイルを単純にダウンロードするだけなのに、障害物が多すぎることだ。
政府機関向けのAPIを作ったことがあるが、データは年に1回変わるか、ごくまれに改訂されるだけだった。
データセット全体は1MB未満のZIPファイル1つに収められたが、ソリューションアーキテクトが要件を定める中で話が大きくなった。
リクエストしたまさにその瞬間にデータが変わっているかもしれないという理由でキャッシュを使わせず、遅いAPIになり、データ変更を購読者に通知する過剰に複雑なWebhookシステムまでできた。
ZIPファイル1つでは単純すぎたかもしれないが、実際に必要だったものと大きく違っていたわけでもない。
もっと気の利いた形にしたいなら、ファイルが変わったときにトリガーされるWebhookを追加して、クライアントが1日1回ポーリングしなくても再ダウンロードすべき時点を知れるようにすればよい。
あるいは、変更があったときにあらかじめ決めたメールをメーリングリストに送るスクリプトを作るだけでも十分だ。
以前と変わっていなければ空のHTTP 304レスポンスを受け取り、変わっていれば新しいETagと一緒に1MB未満のZIPファイルを再取得すればいい。ここで何が足りないのか分からない。
キャッシュは複雑さを増し、手動でキャッシュを再検証しなければならないリスクも生むので、ソリューションアーキテクトが正しかった可能性もある。
結果値
2000-10-26を1つ得るために565KBのファイルをダウンロードしなければならないなら、ひどいAPIです。大量のデータを取得してユーザーに再提供したい場合なら、ZIPでまとめたCSVは優れていて、多言語対応があまり良くない公共交通機関のリアルタイム列車時刻向けprotobufよりもずっと好ましいです。
しかし単一の値を得るAPIのように扱うなら、とんでもない無駄で、アプリにこういう形で組み込む人はいないでほしいです。
記事自体は素晴らしいですが、タイトルがあまりにも挑発的な主張のように感じます。
1日に1回以上リクエストする理由はまったくなく、こうしたデータを使う人たちは、互いにかなり異なるフィルタや集計を求めている可能性が高いです。
現在の為替レートを得る用途なら悪い設計であるのは確かですが、その用途には別のサービスがあり、このファイルは典型的なユースケースによく合っています。
APIとは直接関係ありませんが、以前、土地管理アプリケーションをサポートしていたとき、新バージョンが出るまではISDN級の回線かもしれない遅いサテライトオフィスでも問題なく動いていたのに、新バージョンではまったく動かなくなりました。
ベンダーはRDPサーバー上で動かせと言っていましたが、馬鹿げていると思って調べたところ、ある呼び出しが何の理由もなく
SELECT * FROM sometableを実行していて、同じ実行内の他の呼び出しはきちんとしたSQLのselect句を使っていました。それをベンダーに伝えると、最初は私たちがどうやってそれを突き止めたのか非常に困惑していましたが、最終的には遅い回線でも使えるよう修正した新バージョンを出しました。
自社テストでなぜそれを見つけられず、顧客に高価な解決策を押しつけたのか理解に苦しみます。
最近のJavaScriptを少しでも見たことがあるなら、565KBとその中から大きな値を探すロジックは、どんな合理的な基準でも非常に小さいものです。
ある人は「フィルタリングなしに全データを受け取るとしても、データを得る方法」をAPIと見なしますが、個人的には、テーブル全体のダウンロードはモデルにロジックが働いていないデータモデルのダウンロードであり、APIとは自分が関心を持つ方法でモデルの一部を絞り込んで返すロジックだと考えています。
金融ソフトウェアをバックエンドとフロントエンドの両方でかなり作ってきましたが、フロントエンドでは実データに到達する前からその程度の量の「データ」を送信することが、残念ながらよくあります。
バックエンドでは設計上の判断にすぎず、夜間のcronジョブで為替レートをパースして用途に合わせた
todays-rates.jsonを作り、静的ファイルとしてモバイル・Web・マイクロサービスのアプリに提供するより速いものはありません。モバイルアプリがこのZIP-CSV-over-HTTPを直接消費しなければならない、という意味はどこにもありません。
小さなデータ1つが必要になるたびに大きなファイルを受け取らなければならないと不満を言う人向けに、とても単純な最適化があります。
ファイルが追記専用であることを保証し、ZIPファイルの代わりにHTTP gzip/brotliのような圧縮を使うなら、レンジリクエストで最後の更新以降の新しいデータだけを受け取れます。
ここに安心のためのチェックサムヘッダーを1つ加えれば、かなり効率的でありながら非常に単純な増分APIになります。
もちろん状態を保存する必要があり、初回ダウンロードと状態維持のコストを払わなければならず、2007-08-22のEUR/JPYレートを1つだけ一度だけ必要とする場合には非効率です。
まだかなり作業中ですが、現在の「研究品質」のコードはこちらにあります: https://pypi.org/project/csvbase-client/
https://github.com/gtsystem/python-remotezip
1日分のパッチが1つあるだけでも、手元のファイルを最新に保つために必要な帯域幅を大きく減らせます。
1日に数百KB余分にダウンロードすることが意味を持つ場合の話で、たいていはそうではない可能性が高いです。
sqliteの例にはタイプミスがあります。スクリーンショットにはありませんが、
sqliteに -csv 引数を追加する必要があります。もう一度追加してキャッシュを無効化します。子どもたちを寝かしつけた後で、何が間違っていたのか確認するつもりです。
修正: 私の環境で動いた理由は、
~/.sqlitercに.separator ','の設定があったためでした。以前、CSVファイルを主に投入していることに気づいて、デフォルトとして設定しておいたようです。
少し横道にそれると、ユーロは当初電子的にしか存在していなかったとしても、ユーロ圏加盟国の既存通貨とは固定為替レートがありました。
特に、ドイツの確立され信頼されていたDeutsche Markと固定されていました。
したがって「初期のユーロはなぜ弱かったのか」を説明するには、当時のDEMがなぜ弱かったのかも説明しなければなりませんが、該当段落の説明はその検証を通らないように思います。
毎回データベース全体をダウンロードして読み取り専用として扱える小さな問題では、単純さの価値を過小評価してはいけません。
SQLiteは
.jsonや.csvファイルのように移植性がありながら、データベースのように対話する準備がより整っているので気に入っています。clickhouse-localを使えば、古いCSVファイルもデータベースのように扱えます。核心はここにあります。
この場合、しなくてよかったこと: アクセス権の交渉、たとえば支払いをしたり営業担当者と話したりすること、メールアドレス・会社名・役職を誰かの見込み客データベースに入れること、クォータを守ること、認証すること、APIドキュメントを読むこと、基本的な形式と構造より深刻な問題に対処すること。
帯域幅は無料ではありません。
SQLiteはZIPファイルを読み書きできます。
https://sqlite.org/zipfile.html
gunzipの代わりにsqlite3で展開できるのか気になります。ファイルをディスクに保存してよいなら、こうできます:
sqlite3 -newline '' ':memory:' "SELECT data FROM zipfile('eurofxref-hist.zip')" \| sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \"select ...;"