- eurofxref-hist.zip của ECB chỉ là một gói CSV tỷ giá đơn giản, nhưng chỉ với
curl, gunzip, sqlite3 là có thể tìm ngay ngày đồng đô la mạnh nhất so với euro: 2000-10-26
- Dữ liệu gốc có wide format với các cột tiền tệ nối sau
Date, nên không thuận tiện cho phân tích và cần được chuyển sang long format dạng Date,Currency,Rate
- Do có trailing comma ở cuối mỗi dòng, trình phân tích CSV sẽ đọc thêm một cột rỗng; trong Pandas cần dùng
.iloc[:,:-1] để bỏ cột cuối thì kết quả melt mới sạch
- CSV sau khi chuẩn hóa có thể được tải lên csvbase bằng HTTP PUT rồi nối tiếp với các công cụ như
gnuplot, DuckDB, sqlite3 để vẽ đồ thị, tính trung bình trượt và nạp CSV qua HTTP
- Dữ liệu công khai có thể tải về mà không cần thương lượng quyền truy cập, xác thực, hạn mức hay tài liệu API phức tạp thực chất hoạt động giống như open API, và ngay cả một file zip đơn giản cũng có thể là nền tảng trao đổi dữ liệu cho các ứng dụng tài chính
Truy vấn tỷ giá chỉ với một file zip
- ECB công bố dữ liệu tỷ giá lịch sử giữa euro và các đồng tiền khác dưới dạng file zip chính thức
- Pipeline dưới đây tải dữ liệu về, giải nén, đọc CSV vào cơ sở dữ liệu SQLite trong bộ nhớ, sắp xếp theo giá trị USD rồi lấy ra ngày đầu tiên
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;"
- Kết quả là
2000-10-26
curl -s giúp giảm bớt nhiễu trên standard error, còn gunzip dùng để giải nén file zip- Trên Mac OS hoặc BSD,
gunzip dòng BSD không hỗ trợ file zip nên cần thay bằng bsdtar -xOf -
sqlite3 ':memory:' dùng cơ sở dữ liệu trong bộ nhớ, còn .import /dev/stdin stdin nạp standard input vào bảng stdin
Chuẩn hóa dạng CSV và Pandas melt
- Header CSV gốc có dạng wide format như
Date,USD,JPY,BGN,CYP,CZK,DKK,..., tức sau cột ngày là các cột theo từng đồng tiền
- Để lọc và tổng hợp, long format dạng
Date,Currency,Rate sẽ dễ thao tác hơn
- Việc chuyển từ wide format sang long format thường được gọi là melt
- Phần lớn cơ sở dữ liệu SQL không có phép toán tương đương melt, nên Pandas rất hữu ích cho khâu chuẩn hóa dữ liệu
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)'
- File của ECB có trailing comma ở cuối mỗi dòng nên trình phân tích CSV sẽ đọc thêm một cột rỗng ở cuối
- Cột rỗng này tạo ra các dòng vô nghĩa ở cuối kết quả
melt, vì vậy cần loại bỏ
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] chọn tất cả các dòng và tất cả các cột trừ cột cuối cùng- Dữ liệu ngoại hối của ECB cần được chỉnh lại định dạng, nhưng có thể dùng ngay mà không cần thương lượng quyền truy cập, thanh toán, trao đổi với bộ phận kinh doanh, gửi email/tên công ty/chức danh, hạn mức, xác thực hay đọc tài liệu API
- Vì chỉ cần xử lý định dạng và hình dạng dữ liệu cơ bản nên đây vẫn là một bản phát hành dữ liệu công khai tương đối tốt
Tải dữ liệu đã chuẩn hóa lên csvbase
- CSV đã chuẩn hóa có thể được tải lên csvbase table để tránh phải lặp lại bước làm sạch nhiều lần
- Chỉ cần thêm một
curl vào cuối pipeline hiện có là có thể tải CSV lên bằng 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 - tải dữ liệu nhận từ standard input lên URL được chỉ định- Nếu chưa có bảng trên csvbase thì sẽ tạo mới, nếu đã có thì dữ liệu sẽ được đưa vào bảng đó
-n sử dụng thông tin xác thực trong ~/.netrc
Vẽ đồ thị tỷ giá bằng gnuplot
- Bảng csvbase đã chuẩn hóa có thể lấy CSV bằng
curl rồi nối với 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'"
- Lệnh này vẽ hơn 6.000 điểm dữ liệu dưới dạng ASCII art sao cho vẫn có thể đọc được phần nào trong terminal ký tự 80x25
- Cấu hình
gnuplot được thiết lập để nhận đầu vào CSV và vẽ ngày cùng tỷ giá dưới dạng biểu đồ đường
set datafile separator ',': chỉ định đầu vào là CSVset term dumb: vẽ dưới dạng ASCII artplot -: nhận dữ liệu từ standard inputusing 1:2 with lines: vẽ đường bằng cột 1 và cột 2, tức ngày và tỷ giátitle 'usd': đặt tên đường là usd
- Cũng có thể xuất ra ảnh SVG; để trông giống dữ liệu chuỗi thời gian hơn thì cần chỉ định trục x là thời gian, đặt định dạng thời gian và xoay nhãn trục x
- Có thể gói lại thành hàm Bash
plot_timeseries_to_svg để dùng lặp lại
Tính trung bình trượt bằng DuckDB
- Để xem đường xu hướng của tỷ giá USD, có thể dùng DuckDB để tính trung bình trượt
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
- Nếu không có
duckdb thì cũng không khó để chuyển truy vấn tương tự sang sqlite3
- DuckDB giống SQLite nhưng là cơ sở dữ liệu column-oriented thay vì row-oriented
- DuckDB có thể đọc trực tiếp CSV qua HTTP và tạo thành file bảng
CREATE TABLE eurofxref_hist AS SELECT * FROM
read_csv_auto("https://csvbase.com/calpaterson/eurofxref-hist";);
- DuckDB suy luận kiểu dữ liệu khá tốt và có thể phát hiện kích thước terminal để mặc định rút gọn kết quả lớn khi hiển thị
- Nó cũng có thể hiện thanh tiến trình cho truy vấn lớn và xuất bảng Markdown
Cách dữ liệu công khai hoạt động như open API
- Chỉ với CSV trong file zip và các công cụ có thể cài dễ dàng bằng
brew install hoặc apt install cũng đã làm được rất nhiều việc
eurofxref-hist.zip là một giao thức trao đổi dữ liệu giữa các tổ chức ở dạng cực kỳ đơn giản- File zip này trông nhỏ bé nhưng được rất nhiều ứng dụng tài chính sử dụng hằng ngày
- Một cách nhìn là ECB giữ nguyên trailing comma vì nếu bỏ đi bây giờ có thể sẽ làm hỏng rất nhiều đoạn mã đang chạy
- Khi dữ liệu công khai được cung cấp cực kỳ dễ dàng, nó cũng đảm nhiệm vai trò của một open API
- Nếu nhiều API thực chất gần với trao đổi dữ liệu hơn là gọi hàm từ xa, thì về mặt chức năng chúng không khác nhiều so với dữ liệu công khai có thể tải về dễ dàng
URL đơn giản và các HTTP verb của csvbase
- csvbase dùng một URL cho mỗi bảng
https://csvbase.com/<username>/<table_name>;
https://csvbase.com/calpaterson/eurofxref-hist
- Mỗi URL có bốn HTTP verb chính
GET: nhận CSV, hoặc trên trình duyệt thì có thể nhận trang webPUT: tạo bảng mới bằng CSV mới hoặc ghi đè bảng hiện cóPOST: thêm hàng loạt dòng CSV vào bảng hiện cóDELETE: xóa bảng đó
- Xác thực dùng HTTP Basic Auth
Ghi chú về chuẩn hóa dữ liệu và pipeline
- Trong các cơ sở dữ liệu SQL, một số hệ có tính năng tương đương melt, chẳng hạn UNPIVOT của Snowflake và PIVOT/UNPIVOT của MS SQL Server
- Một trong những lý do quan trọng khiến R và Pandas được dùng nhiều là khả năng chuẩn hóa dữ liệu rất mạnh
- Bash pipeline chạy theo kiểu đa tiến trình, nên mỗi chương trình chạy song song trong một tiến trình độc lập
- Vào tháng 10/2000, tỷ giá USD so với euro là
0.8252, nghĩa là 1 đô la có thể mua được 1,21 euro
- Euro ra đời vào tháng 1/1999 mà chưa có tiền giấy và tiền xu; ban đầu nó chỉ tồn tại trong nội bộ ngân hàng và tiền giấy cùng tiền xu xuất hiện sau đó
1 bình luận
Ý kiến trên Hacker News
Tôi nhớ tệp này khi làm ở ECB khoảng 15 năm trước
Đây là tệp được tải xuống nhiều áp đảo trên website của ECB, và rất nhiều người cùng tổ chức tài chính tải về mỗi ngày để cập nhật hệ thống của họ
Trong vài phút ngay sau thời điểm công bố cố định hằng ngày, lưu lượng truy cập tăng vọt; việc khi giải nén ra chỉ là một tệp CSV đơn giản là một quyết định có chủ đích
Nhờ vậy có thể cung cấp tệp một cách ổn định, nhanh chóng với ít tài nguyên, và nhóm nhỏ phụ trách website công khai của ECB khi đó hoàn toàn có thể tự hào về quyết định kỹ thuật là cung cấp dữ liệu này dưới dạng một tệp tĩnh duy nhất
Nó không hào nhoáng, cũng không có framework
Khoảng 15 năm trước, tôi từng xử lý trao đổi dữ liệu giữa hệ thống ghi nhận sản phẩm của một tập đoàn lớn lâu đời mà hẳn ai cũng từng mua sản phẩm của họ, với các hệ thống con/song song còn lại sau sáp nhập và mua lại; phần lớn là nhập/xuất hàng loạt các tệp độ rộng cố định hoặc tệp có ký tự phân tách qua máy chủ SFTP
Khi đó sản phẩm đã tồn tại 15 năm, và có khoảng 20–30 nguồn dữ liệu hoặc bản xuất dữ liệu như vậy qua lại, nhưng chúng chạy rất tốt
Khả năng cao hiện nay chúng vẫn đang được dùng mà không thay đổi lớn; lúc đó frontend cũ viết bằng Smalltalk đang được viết lại
Trong số các nguồn dữ liệu chúng tôi dùng, nó là thứ dễ xử lý nhất
Kiến trúc sư sẽ nói ZIP không phải định dạng phù hợp với đặc tả cho mục đích này, bộ phận tuân thủ sẽ nói cần kiểm tra rò rỉ thông tin cá nhân, còn phía rủi ro sẽ nói phải ngăn tác nhân độc hại tải tệp xuống
Người phụ trách web có lẽ sẽ nói muốn thêm thứ gì vào site thì cần quy trình thay đổi đã được phê duyệt
Tải xuống tệp đơn giản và tệp CSV là điều tuyệt vời
Tôi mong nhiều nơi công bố dữ liệu bằng những định dạng đơn giản như thế này hơn, và mỗi lần phải lấp đầy “giỏ hàng” để tải dữ liệu của chính phủ Mỹ là tôi lại cảm thấy như chết dần một chút
Cũng có nhiều công cụ wrapper giúp pipeline cụ thể này dễ hơn; nếu cần giao diện web và thêm vài tính năng nâng cao hơn một chút thì những thứ như Datasette cũng rất tốt
Có thể đọc tệp ZIP dưới dạng stream, xử lý CSV theo từng dòng để chuyển đổi, rồi nạp vào cơ sở dữ liệu bằng COPY FROM stdin trong trường hợp Postgres
Nghe vừa hợp lý vừa hữu ích, vậy mà đến giờ tôi mới biết
Tôi có nhiều báo cáo dạng CSV, nên rất muốn thử dùng nó để chạy truy vấn nhanh
Ví dụ cách xử lý dấu ngoặc kép có thể khác nhau như
"Look, this contains \"quotes\"!",012345và"Look, this contains ""quotes""!",012345; thậm chí có các ví dụ hỏng hơn như"Look, this contains "quotes"!",012345hayLook, this contains "quotes"!,012345Dấu vết của bảng tính cũng có thể làm mất số 0 ở đầu, như
"Look, this contains ""quotes""!",12345Về lý thuyết, JSON cũng có thể bị sửa tay thành một tệp hỏng một nửa, nhưng trên thực tế tôi hiếm khi thấy ai làm vậy với tệp JSON; các giá trị như số sê-ri trong JSON cũng thường được giữ dưới dạng chuỗi, chứ không bị một ứng dụng “thân thiện” cắt mất số 0 ở đầu như với số nguyên
Rốt cuộc vì sao lại như vậy, có lý do chính đáng nào không?
Đổi CSV được đóng ZIP thành tài liệu JSON được đóng ZIP thì lợi ích vẫn giống nhau
Vấn đề thật sự là có quá nhiều rào cản chỉ để tải xuống một tệp duy nhất được phục vụ tĩnh
Tôi từng xây API cho một cơ quan chính phủ, trong đó dữ liệu chỉ thay đổi mỗi năm một lần hoặc rất hiếm khi được sửa đổi
Toàn bộ dataset có thể gói trong một tệp ZIP dưới 1MB, nhưng mọi chuyện phình to khi solution architect xác định yêu cầu
Họ không cho dùng cache vì dữ liệu có thể đã thay đổi ngay tại đúng khoảnh khắc yêu cầu được gửi, khiến API trở nên chậm; thậm chí còn sinh ra một hệ thống webhook quá phức tạp để thông báo thay đổi dữ liệu cho người đăng ký
Một tệp ZIP duy nhất có thể là quá đơn giản, nhưng cũng không khác mấy so với thứ thực sự cần
Nếu muốn làm đẹp hơn, có thể thêm webhook được kích hoạt khi tệp thay đổi, để client biết khi nào cần tải lại thay vì polling mỗi ngày một lần
Hoặc chỉ cần viết một script gửi email đã định sẵn đến mailing list khi có thay đổi là đủ
Nếu không có gì khác trước thì nhận phản hồi HTTP 304 rỗng; nếu đã thay đổi thì tải lại tệp ZIP dưới 1MB kèm ETag mới. Tôi không rõ còn thiếu gì ở đây
Cache làm tăng độ phức tạp và tạo nguy cơ phải tự tay xác thực lại cache, nên có khả năng solution architect đã đúng
Nếu phải tải xuống một tệp 565KB chỉ để lấy một kết quả
2000-10-26thì đó là một API tệ hạiNếu mục tiêu là lấy một lượng lớn dữ liệu rồi cung cấp lại cho người dùng, CSV được gói trong ZIP là rất tuyệt, và tôi thích nó hơn nhiều so với protobuf cho lịch tàu thời gian thực của giao thông công cộng vốn hỗ trợ nhiều ngôn ngữ không tốt
Nhưng nếu coi nó như một API để lấy một giá trị đơn lẻ thì đó là sự lãng phí khủng khiếp, và tôi hy vọng không ai đưa nó vào app theo cách này
Bản thân bài viết rất hay, nhưng tiêu đề có cảm giác quá giống một luận điểm khiêu khích
Hoàn toàn không có lý do gì để yêu cầu quá một lần mỗi ngày, và những người dùng loại dữ liệu này có khả năng muốn các bộ lọc hoặc phép tổng hợp rất khác nhau
Nếu dùng để lấy tỷ giá hiện tại thì đúng là thiết kế tệ, nhưng cho mục đích đó có các dịch vụ khác, còn tệp này phù hợp với trường hợp sử dụng điển hình
Không liên quan trực tiếp đến API, nhưng trước đây khi tôi hỗ trợ một ứng dụng quản lý đất đai, trước khi phiên bản mới ra mắt, nó vẫn chạy tốt ngay cả ở các văn phòng vệ tinh có đường truyền chậm, có thể chỉ ngang ISDN; còn phiên bản mới thì hoàn toàn không chạy được
Nhà cung cấp bảo là hãy chạy trên máy chủ RDP, nhưng tôi thấy quá vô lý nên điều tra, và phát hiện một lệnh gọi nào đó vô cớ thực hiện
SELECT * FROM sometable, trong khi các lệnh gọi khác trong cùng lần chạy thì dùng mệnh đề SQL select đúng cáchKhi nói chuyện này với nhà cung cấp, ban đầu họ rất bối rối không hiểu làm sao chúng tôi biết được, và cuối cùng họ phát hành phiên bản mới được sửa để có thể dùng cả trên đường truyền chậm
Thật khó hiểu vì sao kiểm thử nội bộ của họ không bắt được lỗi đó mà lại đẩy một giải pháp đắt đỏ cho khách hàng
Nếu dạo này bạn có nhìn qua JavaScript dù chỉ một chút, thì 565KB và logic tìm một giá trị lớn trong đó là rất nhỏ theo bất kỳ tiêu chuẩn hợp lý nào
Có người xem “cách lấy dữ liệu, dù là nhận toàn bộ dữ liệu không lọc” là API, nhưng cá nhân tôi thấy tải xuống toàn bộ bảng là tải xuống mô hình dữ liệu, nơi logic không hoạt động trên mô hình, còn API là logic lọc và trả về một phần của mô hình theo cách tôi quan tâm
Tôi đã làm khá nhiều phần mềm tài chính ở cả backend lẫn frontend, và ở frontend, đáng tiếc là việc truyền chừng đó “dữ liệu” còn trước khi chạm tới dữ liệu thật là chuyện phổ biến
Ở backend thì đây chỉ là một quyết định thiết kế, và không gì nhanh hơn một cron job chạy hằng đêm để phân tích tỷ giá, tạo
todays-rates.jsonphù hợp mục đích rồi phục vụ nó như tệp tĩnh cho các ứng dụng mobile, web và microserviceKhông có chỗ nào nói rằng ứng dụng mobile nhất thiết phải trực tiếp tiêu thụ ZIP-CSV-over-HTTP này
Có một tối ưu hóa rất đơn giản cho những ai phàn nàn rằng mỗi khi cần một mẩu dữ liệu nhỏ lại phải tải một tệp lớn
Nếu bảo đảm tệp là chỉ được append và dùng nén kiểu HTTP gzip/brotli thay vì tệp ZIP, thì có thể dùng range request để chỉ lấy dữ liệu mới kể từ lần cập nhật cuối
Thêm một header checksum để yên tâm nữa là có một API tăng dần khá hiệu quả mà vẫn rất đơn giản
Tất nhiên bạn phải lưu trạng thái, chịu chi phí tải lần đầu và duy trì trạng thái, và nếu chỉ cần đúng một lần tỷ giá EUR/JPY ngày 2007-08-22 thì cách này không hiệu quả
Vẫn còn rất đang trong quá trình thực hiện, nhưng mã hiện ở mức “chất lượng nghiên cứu” có ở đây: https://pypi.org/project/csvbase-client/
https://github.com/gtsystem/python-remotezip
Chỉ cần một bản vá theo ngày cũng có thể giảm mạnh băng thông cần thiết để tôi giữ tệp phía mình luôn cập nhật
Đó là trong trường hợp việc tải thêm vài trăm KB mỗi ngày có ý nghĩa, mà phần lớn có lẽ là không
Ví dụ
sqlitecó lỗi gõ nhầmTrong ảnh chụp màn hình không có, nhưng cần thêm đối số -csv cho
sqliteTôi sẽ thêm lại và vô hiệu hóa cache. Sau khi cho bọn trẻ đi ngủ tôi sẽ kiểm tra xem có gì sai
Sửa: Lý do nó chạy trên môi trường của tôi là vì trong
~/.sqliterccó thiết lập.separator ','Có vẻ trước đây tôi nhận ra mình chủ yếu nạp tệp CSV nên đã đặt nó làm mặc định
Rẽ ngang một chút, dù ban đầu euro chỉ tồn tại dưới dạng điện tử, nó vẫn có tỷ giá cố định với các đồng tiền hiện có của các nước thành viên Eurozone
Đặc biệt là nó được cố định với Deutsche Mark của Đức, một đồng tiền đã được thiết lập và đáng tin cậy
Vì vậy, để giải thích “vì sao euro thời kỳ đầu yếu”, cũng cần giải thích vì sao DEM khi đó yếu, và phần giải thích trong đoạn đó có vẻ không vượt qua được phép kiểm tra này
Với các bài toán nhỏ nơi có thể tải xuống toàn bộ cơ sở dữ liệu mỗi lần và xử lý ở chế độ chỉ đọc, không nên đánh giá thấp giá trị của sự đơn giản
Tôi thích SQLite vì nó có tính di động như tệp
.jsonhay.csv, nhưng lại sẵn sàng hơn nhiều để tương tác như một cơ sở dữ liệuclickhouse-localthì cũng có thể xử lý các tệp CSV cũ như cơ sở dữ liệuĐiểm mấu chốt nằm ở đây
Những việc không phải làm trong trường hợp này: thương lượng quyền truy cập, chẳng hạn như trả tiền hoặc nói chuyện với nhân viên bán hàng; đưa địa chỉ email, tên công ty, chức danh của bạn vào cơ sở dữ liệu khách hàng tiềm năng của ai đó; tuân thủ quota; xác thực; đọc tài liệu API; xử lý các vấn đề nghiêm trọng hơn định dạng và cấu trúc cơ bản
Băng thông không miễn phí
SQLite có thể đọc và ghi tệp ZIP
https://sqlite.org/zipfile.html
Tôi thắc mắc liệu có thể giải nén bằng
sqlite3thay vìgunzipkhôngNếu có thể lưu tệp xuống đĩa thì có thể làm như sau:
sqlite3 -newline '' ':memory:' "SELECT data FROM zipfile('eurofxref-hist.zip')" \| sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \"select ...;"