Hacker News Reader

Comment by 1vuio0pswjnm7 | Hacker News Reader

  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;"

SQLite can read and write zip files.

https://sqlite.org/zipfile.html

Is it possible to use sqlite3 instead of gunzip for decompression.

View on HN

Very good point!

If you don't mind saving the file to disk, you can do:

  sqlite3 -newline '' ':memory:' "SELECT data FROM zipfile('eurofxref-hist.zip')" \
  | sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \
    "select ...;"

Doing this without a temporary file is tricky; for example `readfile('/dev/stdin')` doesn't work because SQLite tries to use seek(). Here is a super ugly method (using xxd to convert the zipfile to hex, to include it as a string literal in the SQL query):

  curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip \
  | { printf "SELECT data FROM zipfile(x'"; xxd -p | tr -d '\n'; printf "')"; } \
  | sqlite3 -newline '' \
  | sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \
    "select ...;"

Reply on news.ycombinator.com