20 ms·
My favourite API is a zipfile on the European Central Bank's website
- Waterluvian 3y agoFor little problems where you can just download the entire database every time and do read-only work, never underestimate the value of keeping it simple! I really like SQLite for this because it has the portability of a .Json or .csv file but it’s more ready to interact with as a database.
- semi-extrinsic 3y agoJust use clickhouse-local, and you can interact with any old CSV file as if it was a database.
- lxgr 3y agoThe example is failing at decompression for me, and I suspect it's because of this: > That data comes as a zipfile, which gunzip will decompress. Doesn't gunzip expect gzip files, as opposed to ZIP (i.e. .zip) files? On Linux I get further (apparently Linux gunzip is more tolerant of the format error than the macOS default one), but there, I then run into: > Error: no such column: Date
- gniv 3y agoThe example works on my MacBook if I replace gunzip with funzip. Also, I like to see the value. So: curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | funzip | sqlite3 -csv ':memory:' '.import /dev/stdin stdin' "select Date,USD from stdin order by USD asc limit 1;"
- xomodo 3y agoThx. I have extended a bit as a script: # !/bin/bash ARG1=${1:-'select Date from stdin order by USD asc limit 1;'} curl -o - -Ls https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip \ | funzip | sqlite3 -csv ':memory:' '.import /dev/stdin stdin' "${ARG1}" # Usage: $ ./run.sh 'select * from stdin limit 1;'
- halJordan 3y agoHere's the gzipped tarball of the docs you can read to find out https://www.gnu.org/software/gzip/manual/gzip.txt.gz https://www.gnu.org/software/gzip/manual/gzip.txt.gz
- jandrese 3y agoThis is slightly abusive of gunzip, but it does work, but only because the zipfile in question only contains a single file and you are piping it in via shell. If you had tried to download the file and then run gunzip on it the program would have refused. As for the second error, I think you might be trying to import an empty file or maybe an error message? Probably related to the first problem.
- pixl97 3y agoAt least in Ubuntu it doesn't do anything at all. # gunzip -d master.zip gunzip: master.zip: unknown suffix -- ignored
- lxgr 3y agoIt's a ZIP file, so you'll want to use unzip, not gunzip. Ubuntu's version of g(un)zip actually does tolerate ZIP inputs though; for that usage, just omit the '-d'.
- hddqsb 3y agoPer the docs (I linked them in another comment), the correct command is `gunzip <foo.zip` or `gunzip -S .zip foo.zip`.
- deleted 3y ago[deleted]
- hddqsb 3y agoGNU gzip has limited support for ZIP: > Files created by zip can be uncompressed by gzip only if they have a single member compressed with the “deflation” method. This feature is only intended to help conversion of tar.zip files to the tar.gz format. -- https://www.gnu.org/software/gzip/manual/html_node/Overview.html https://www.gnu.org/software/gzip/manual/html_node/Overview....
- liamkinne 3y agoI once hade the unfortunate experience of building an API for a government org where the data changed once a year or when amendments were made which happens very infrequently. The whole data set could have been zipped into a <1MB file but instead a “solution architect” go their hands on the requirements. We ended up with a slow API because they wouldn’t let us cache results in case the data had changed just as it was requested. And an overly complex webhook system for notifying subscribers of changes to the data. A zip file probably was too simple, but not far off what was actually required.
- ipaddr 3y agoIf data changes only once a year or rarely that would imply usage of the api is a rare event for a user of the data so speed isn't a huge concern. Caching would introduce more complexities and the risk of needing to manually revalidate the cache. The solution architect was probably right.
- xg15 3y agoWhy do rare writes imply rare usage? It's possible the file is read often and by different systems even if changes are infrequent. If the API was used rarely, that would be even more of an argument for a simple implementation and not a complex system involving webhooks.
- justsomehnguy 3y agocat /api/version.txt 2023.01.01 ls /api version.txt data.zip
- accrual 3y agoOr maybe encode the version into the filename? It would overwrite if nothing changed, and the previous versions would remain available. 2023.01.01-data.zip
- justsomehnguy 3y ago
- deleted 3y ago[deleted]
- Scene_Cast2 3y agoThere's an issue with openly & freely available financial data. It tends to be limited (i.e. you need many sources to answer the questions you want and not just the questions a particular source is able to answer). And if you're bringing in different sources of data, each source requires custom fetching and parsing, along with being just annoyingly slightly different from other sources. Even if the value is given on a per-day basis (which is a huge assumption, lots of things like interest rates are published much slower), you have things like "is the value at the start of day, end of day, market close, median? What time zone? Which exchange? What about days when the exchange was closed? What about odd exchange hours or halts?", "what's the date and time format, what's the naming convention?"
- mhh__ 3y agoThis why Bloomberg and friends make money. They're a cartel but they at least do both making all this data work together and also transcribe stuff that is written in English into structured data etc (hence their interest in AI)
- mhh__ 3y agoTarget rates aren't published very frequently but interest rates themselves are typically daily or better.
- AceJohnny2 3y agoKey point: > Some things we didn't have to do in this case: negotiate access (for example by paying money or talking to a salesman); deposit our email address/company name/job title into someone's database of qualified leads, observe any quota; authenticate (often a substantial side-quest of its own), read any API docs at all or deal with any issues more serious than basic formatting and shape.
- crazygringo 3y agoNo, it's a terrible API if retrieving the result '2000-10-26' requires downloading a 565 KB file. I don't want to seem overly negative -- zipped CSV files are fantastic when you want to import lots of data that you then re-serve to users. I would vastly prefer it over e.g. protobufs that are currently used for mass transit system's live train times, but have terrible support in many languages. But it's incredibly wasteful to treat it like an API to retrieve a single value. I hope nobody would ever write something like that into an app... (So the article is cool, it's just the headline that's too much of a "hot take" for me.)
- smallpipe 3y agoHow much data do I need to download before I can do a protobuf request ?
- eddythompson80 3y agoAre you counting the client size? TLS handshake? Because otherwise the answer is none.
- crazygringo 3y agoMass transit files are often a single protobuf file that is like a megabyte or something. I'm talking about zipped CSV files as easier to use than protobuf files. Neither is a request in this comparison.
- theLiminator 3y agoThey should ship parquet, supports predicate pushdown and is dramatically more compact, while you can't get row level data, it's great for analytical queries.
- IanCal 3y agoIMO this is the best simple option right now. For sorted data you only need the relevant row groups which can be tunable to sensible sizes for your data and access pattern.
- NelsonMinar 3y agoSimple file downloads and CSV files are fantastic. I wish more folks would publish data in simple formats like this. Every time I have to fill a "shopping cart" for a US government data download I die a little. There's a bunch of wrapper tools to make this particular pipeline easier. Also something like Datasette is great if you want a web view and some fancier features.
- stormfather 3y agoI've never stopped to question the absurdity of the gov data shopping cart thing. WHY!?!? Is there some justification for that?
- icyfox 3y agoI can almost assure you it was an explicit requirement in an RFP that was copied from some master template. So not a good justification - but a justification in the eyes of the bureaucracy. The book Recoding America has a lot of anecdotes to this effect; most of these situations reduce to a Congressional mandate that got misinterpreted along the way. My favorite was for an update to Social Security. The department in charge of the implementation swore that Congress was forcing them to build a facebook for doctors (literally where doctors could friend other doctors and message them). Congress had no such intention; it was actually 3rd party lobbying that wanted the requirement so they could build their own solution outside of government. Really crazy stuff.
- reverius42 3y ago> Congress had no such intention; it was actually 3rd party lobbying Right, but 3rd party lobbying can't force anyone to do anything, whereas Congress can (and did) give this mandate the force of law. The fact that lobbyists got Congress to do something that they had "no such intention" to do is its own problem, but let's not lose sight of who is responsible for laws.
- icyfox 3y ago
- paradox460 3y agoIf this is his favorite API, he should check out patent office gazettes
- andrewmcwatters 3y agoThe EDGAR API bulk data is similar in nature, albeit in JSON instead.[1] [1]: https://github.com/andrewmcwattersandco/programming-language-benchmarks#tests https://github.com/andrewmcwattersandco/programming-language...
- mhh__ 3y agoLots of exchanges and banks still use FTP and and friends to publish data to both the public and counterparties
- j7ake 3y agoThe other annoying thing is the large file that is behind some click button, so you cannot easily copy and paste the url and download it via wget.
- lovasoa 3y ago> 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;" Error: in prepare, no such column: Date (1) There is a typo in the example (that is not in the screenshot): you need to add a -csv argument to sqlite.
- calpaterson 3y agoThis is odd, I did originally have that argument but I removed it because it didn't seem to matter - it works without ("On My Machine"(tm)) Erk - readding it and busting the cache. After I put my kids to bed I will figure out what is wrong EDIT: the reason it works for me is because I've got this config in ~/.sqliterc: .separator ',' Apparently at some point in the past I realised that I mostly insert csv files into it and set that default.
- c7b 3y agoGenerally curious - any particular reasons you chose sqlite and gnuplot for this task rather than, say, Python?
- calpaterson 3y agoI do also use Python (pandas) partway down the page :) I'm just trying to show different tools to give people ideas about how much is possible with stuff that is already in /usr/bin/ If you were to ask my own tool preferences which I use for $DAYJOB: pandas for data cleaning and small data because I think that dataframes are genuinely a good API. I use SQL for larger datasets and I am not keen on matplotlib but still use it for graphs I have to show to other people.
- walth 3y agoShouting out that clickhouse-local can do all of those in one step, including downloading, parsing, SQL query and charting.
- RyanHamilton 3y agocsvbase looks really good. Nice landing page, well written docs. Great work shipping.
- supergeek133 3y agoI used to help run the Best Buy product catalog API. We did nightly CSVs instead of making people poll/paginate the entire catalog. It was super widely used.
- pharrington 3y agoYeah having a local snapshot of an entire dataset is obviously nice when it's feasible. But there's no need to conflate that with "application programming interface" just to market csvbase.
- taldo 3y agoA very simple optimization for those complaining about having to fetch a large file every time you need a little datapoint: if they promised the file was append-only, and used HTTP gzip/brotli/whatever compression (as opposed to shipping a zip file), you could use range requests to only get the new data after your last refresh. Throw in an extra checksum header for peace of mind, and you have a pretty efficient, yet extremely simple incremental API. (Yes, this assumes you keep the state, and you have to pay the price of the first download + state-keeping. Yes, it's also inefficient if you just need to get the EUR/JPY rate from 2007-08-22 a single time.)
- calpaterson 3y agoAbsolutely! I have a plan for a client lib that uses ETags (+ other tricks) to do just that. Very WIP but check out my current "research quality" code here: https://pypi.org/project/csvbase-client/ https://pypi.org/project/csvbase-client/
- acqq 3y agoAlso, on the topic of range requests, when a server allows the range requests for zip files, the zip files are huge and one needs just a few files from them, one can actually download just the "central directory" and the compressed data of the needed files without downloading the whole zip file: https://github.com/gtsystem/python-remotezip https://github.com/gtsystem/python-remotezip
- GuB-42 3y agoOr, just serve a bunch of diff files. Just having a single daily patch can drastically reduce the bandwidth required to keep the file up to date on your side. That's if downloading a few hundred kB more per day matters to you. It probably doesn't.
- gigatexal 3y agoYup! I built a pipeline to grab this and create a historical table of rates on GCP. It was a fun little project.
- sdfghswe 3y agoWhat's the use case here? For a quick look up you're not going to write that monster command. And for a more ongoing repeated use, you're not going to download and uncompress the file every time you want to query it... I get the satisfaction as a curiosity, but other than that, to me that wouldn't be enough to make the my "favorite" or even "good".
- accrual 3y agoI viewed it as an example of how simple it could be (essentially a one-liner), but not meant for actual repeated use as your points suggest.
- mmcnl 3y agoGreat read. I didn't know it's so easy to plot something in the terminal. Also really shows the power of 2D data structures. A good long format and you can do anything.
- shortrounddev2 3y agoThe site apparently serves .csv directly as well (replace .zip with .csv). I used this to convert the expression to powershell (using only built-in powershell cmdlets and commands): $> (invoke-webrequest "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.csv").Content | ConvertFrom-Csv | sort "usd" | select "date" -first 1 Date ---- 2000-10-26 Doing it with a zip file would be a little more verbose since there is no built in "gunzip" type command which operates on streams, but you can write one which does basically that out of built in .Net functions: function ConvertFrom-Zip { param( [Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true)] [byte[]]$Data ) process { $memoryStream = [System.IO.MemoryStream]::new($Data) $zipArchive = [System.IO.Compression.ZipArchive]::new($memoryStream) $outputStreams = @() foreach ($entry in $zipArchive.Entries) { $reader = [System.IO.StreamReader]::new($entry.Open()) $outputStreams += $reader.ReadToEnd() $reader.Close() } $zipArchive.Dispose() $memoryStream.Dispose() return $outputStreams } } and call it like: # unary "," operator required to have powershell # pipe the byte[] as a single argument rather # than piping each byte individually to # ConvertFrom-Zip $> ,(invoke-webrequest "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip").Content | ConvertFrom-Zip | ConvertFrom-Csv | sort "usd" | select "date" -first 1 Date ---- 2000-10-26 I love powershell
- llimllib 3y agoHere it is in nushell: /tmp> # be kind to the server and only download the file if it's updated /tmp> curl -s -o /tmp/euro.zip -z /tmp/euro.zip https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip /tmp> unzip -p /tmp/euro.zip | from csv | select Date USD | sort-by USD | first ╭──────┬────────────╮ │ Date │ 2000-10-26 │ │ USD │ 0.83 │ ╰──────┴────────────╯ (I removed the pipe to gunzip because 1. gunzip doesn't work like that on mac and 2. it's not something you should expect to work anyway, zip files often won't work like that, they're a container and their unzip can't normally be streamed)
- jakswa 3y agoI'll throw out the the GTFS transit standard involves publishing zipped CSVs representing your DB tables regularly. There are nice things about it and downsides IMO. This is how google map's transit directions function -- they rely on agencies to publish their schedules regularly in zipped CSVs I think. One downside is that dev experience is pretty bad in my opinion. It took me years of fiddling in my free time to realize that if you happen to try to use the CSVs near a schedule change, you don't know what data you're missing, or that you're missing data, until you go to use it. My local agency doesn't publish historical CSVs so if you just missed that old CSV and need it, you're relying on the community to have a copy somewhere. Similarly, if a schedule change just happened but you failed to download the CSV, you don't know until you go to match up IDs in the APIs.
- swader999 3y agoCsv is surprisingly common and I prefer it despite parsing, quoting, encoding and escaping woes. Amazon S3 let's you query csv files already loaded in buckets which is interesting but I haven't used yet. One company I worked at a long time ago used free dropbox accounts as a ftp like drop that they would consume. Was hilarious and it worked well and was easy to stay under the free limits.
- 1vuio0pswjnm7 3y agocurl -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 https://sqlite.org/zipfile.html Is it possible to use sqlite3 instead of gunzip for decompression.
- hddqsb 3y agoVery 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 ...;"
- tomp 3y agoInteresting. As someone with a lot of data experience, and in particular including financial data (trading team in a hedge fund) I definitely prefer the column format where each currency is its own column. That way it's very easy to filter for only the currencies I care about, and common data processing software (e.g. Pandas for Python) natively support columns, so you can get e.g. USDGBP rate simply by dividing two columns. The biggest drawback of `eurofxref-hist` IMO is that it uses EUR as the numeraire (i.e. EUR is always 1), whereas most of the finance world uses USD. (Yeah I know it's in the name, it's just that I'd rather use a file with USD-denominated FX rates, if one was available.)
- dist-epoch 3y agoNot sure what you mean by "most of the finance world", but in forex trading the EUR/USD rate is denominated exactly as in this file - 1.066 for today and not the inverse 0.094.
- tomp 3y agoright, I mean quant trading - i.e. where people actually use data :D you want things relatively standardised, which means using a common unit of account - for most funds AFAIK that's USD
- nly 3y agoThis is my experience as well. Everything is expressed as 'number to multiply by to get USD' so USDJPY would always be used in-house inverted. British firm as well. Nobody likes the GBP.
- fastasucan 3y agoAre you really asking for the european central bank to not use its own currency?
- andylynch 3y agoBut why would the ECB publish its Euro fixings in some other currency?
- stevoski 3y agoAh, I remember this specific file from my time at the ECB 15-ish years ago. IIRC it was by far the most downloaded file on the ECB website. Tons of people, including many financial institutions, downloaded it daily, and used it to update their own systems. IIRC #2 in the minutes immediately after the daily scheduled time for publishing this file, there was a massive traffic spike. It was a conscious decision to make it a simple CSV file (once unzipped): it made it possible to serve the file reliably, fast, and with little resources needed. The small team responsible at the time for the ECB’S public website was inordinately proud of the technical decisions made to serve this data in a single static file. And rightly so.
- andylynch 3y agoI know this file too, I was one of them. Of all the data sources we used, it was the best to work with.
- nlehuen 3y agoIt's a bit scary to imagine the consequences if this file was somehow corrupted (wrong column headers for instance)!
- thghtihadanacct 3y agoJust wait until you inherit a service that, sans documentation, pulls in a web resource file that suddenly is no longer available :(
- dizhn 3y agoscale8 recently archived their GitHub repo without explanation and right after that the url for a GDPR opt-in list they were using changed. The product is now dead because the code doesn't handle the situation where that file might not be available. (Scale8 is a web analytics and tag management software like google analytics)
- dizhn 3y ago
- albertzeyer 3y agoI get: gunzip: unknown compression format
- hamachi4455 3y ago[dead]
- babblingfish 3y agoI believe that curl does not have client side caching so every time you run the command it downloads the csv. While downloading the csv and then analyzing it would no longer makes it a magic one-liner you can send to people. It would save bandwidth and reduce traffic on the API. Unless there is caching going on here? Perhaps a CDN cache on the server side?
- acqq 3y ago> I believe that curl does not have client side caching See: https://news.ycombinator.com/item?id=37528558 https://news.ycombinator.com/item?id=37528558 by llimllib Specifically: curl -o /tmp/euro.zip -z /tmp/euro.zip Option o is output, option z says "Request a file that has been modified later than the given time and date, or one that has been modified before that time." And: https://news.ycombinator.com/item?id=37529690 https://news.ycombinator.com/item?id=37529690 by sltkr: "the server doesn't support that header, but since the response does include a Last-Modified header, curl helpfully aborts the transfer if the Last-Modified date is the same as the mtime of the previously downloaded file."
- eql5 3y agoSimplicity Wins. All. Ways. (TM)
- gkfasdfasdf 3y agoHow about caching the zip file and adding a '-z' to the curl command to only download if newer.
- deleted 3y ago[deleted]
- mrighele 3y ago> Switching from wide to long is a simple operation, commonly called a "melt". Unfortunately, it's not available in SQL. That's not completely true. In most SQL databases you can query information about the table, and this is true for Sqlite too. You cannot use this information to have a query with a "dynamic" number of columns, but you can always generate the query from the metadata and then executed the generated queries. It is not exactly fun to do as a one-liner, but it works: > curl 'https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.csv https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.csv' | sqlite3 -csv ':memory:' '.import /dev/stdin stdin' '.mode list' '.output query.sql' "select 'select Date, ''' || name || ''', ' || name || ' from stdin ;' from pragma_table_info('stdin') where cid > 0 and cid < 42;" '.output stdout' '.mode csv' '.read query.sql' (yes, you're also left with some temporary file on the computer).
- mannyv 3y agoETL: the engine behind a whole lot of everything.
- twobitshifter 3y agoIf you’re going to go all bash, use cut (made for it) or sed to remove the extra comma. And you could use awk to do the melt if you don’t have python and pandas.
- keepamovin 3y agoI find awk to be more reliable even for simple string manipulation. Something about sed's regexes throw me for a loop!
- ninjin 3y agoIn my case it is not Bash, but POSIX shell: #!/bin/sh set -eu u=https://www.ecb.europa.eu/stats/eurofxref/eurofxref.zip ftp -Vo - $u | zcat | sed 's/, $//' | awk -F ', ' ' NR == 1 { for(i = 1; i <= NF; ++i) { col[$i] = i c = ($i != "GBP") ? $i : "EUR" printf "%s%s", c, (i < NF) ? "\t" : "\n" } } NR >= 2 { printf "%s\t", $1 for(i = 2; i <= NF; ++i) { c = (i != col["GBP"]) ? $i/$(col["GBP"]) : $i printf "%s%s", c, (i < NF) ? "\t" : "\n" } } ' Fetches the latest rates and converts the base currency to GBP and format to TSV. Then I run a cron(8) job 15~30 minutes after the release each work day and dump the output so that I can retrieve it from httpd(8) over HTTPS to be injected into my window manager status bar. A pretty elegant solution that spares the ECB from my desktops hammering their server. All with tools from the OpenBSD base install.
- thibran 3y agoToo many different tools with weird syntax... once you are used to Nushell, you keep using it :) http get https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | gunzip | from csv | sort-by USD | first | get Date
- nextaccountic 3y ago> Although pulling out a simple max is easy, the data shape is not ideal. It's in "wide" format - a Date column, and then an extra column for every currency. Here's the csv header for that file: > Date,USD,JPY,BGN,CYP,CZK,DKK,EEK,GBP,HUF,LTL,LVL,MTL,[and on, and on] > When doing filters and aggregations, life is easier if the data is in "long" format, like this: > Date,Currency,Rate > Switching from wide to long is a simple operation, commonly called a "melt". Unfortunately, it's not available in SQL. > No matter, you can melt with pandas: > curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip 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)' You sure this can't be done in SQL? I myself don't know enough SQL, but I believe that you can decompose a single row into multiple rows in SQL by joining multiple queries using UNION ALL: https://stackoverflow.com/questions/46217564/converting-single-row-into-multiple-rows-based-on-values-in-columns https://stackoverflow.com/questions/46217564/converting-sing... https://stackoverflow.com/questions/52279384/how-do-i-melt-a-sql-table https://stackoverflow.com/questions/52279384/how-do-i-melt-a... Now the question is, how to make this less verbose (so that the query doesn't need to mention each of the 42 columns)
- tbrownaw 3y agoThat sounds like PIVOT and UNPIVOT. But of course being SQL you have to list all the columns explicitly, which can get kind of annoying.
- georgespencer 3y agoSee also: the US “no fly” list, which is a read-only Excel file in the cloud.
- deleted 3y ago[deleted]
- Joel_Mckay 3y agoThere are several reasons a compressed archive isn't a good data format outside trusted backups. 1. inconsistent binary formats, float precision edge conditions, and unknown Endianness. Thus, assuming the Marshalling of some document is reliable is risky/fragile, so pick some standard your partners also support... try XML/XSLT, BSON, JSON, SOAP, AMQP+why, or even EDIFACT. 2. "dump and load" is usually inefficient, with an exception when the entire dataset is going to change every time (NOAA weather maps etc.) 3. Anyone wise to the 42TiB bzip 18kiB file joke is acutely aware of what compressed files can do to server scripts. 4. Tuning firewall traffic-shaping for a web-server is different from a server designed to handle large files. Too tolerant rules causes persistent DDoS exposure issues, and too strict causes connections to become unreliable when busy/slow. 5. Anyone that has to deal with CSV files knows how many proprietary interpretations of a simple document format emerge. Best of luck, and remember to have fun =)
- Bost 3y agoZIP Bomb is 42.zip compressed archive whose size is 42 kilobytes but expands to 4.5 petabytes of uncompressed data. https://blog.fileformat.com/compression/zip-bombs-exploding-your-storage/ https://blog.fileformat.com/compression/zip-bombs-exploding-...
- isoprophlex 3y agoAbout your Point 5, let me tell you about the ESV file format: Eggplant Separated Values! Who needs escaping if you have the eggplant emoji!
- Joel_Mckay 3y agoMy favorite was console control-characters accidentally embedded in plain text by cross-platform document code-page encoding errors. One learns over the years that every program stage/interface must sanitize input, sanity check formats, and isolate data on a per account log. Did the user intend "P=0/O" as a joke... we'll never know. =)
- eru 3y agoGoing on a tangent: > Why was the Euro so weak back in 2000? It was launched, without coins or notes, in January 1999. The Euro was, initially, a sort of in-game currency for the European Union. It existed only inside banks - so there were no notes or coins for it. That all came later. So did belief - early on it didn't look like the little Euro was going to make it: so the rate against the Dollar was 0.8252. That means that in October 2000, a Dollar would buy you 1.21 Euros (to reverse exchange rates, do 1/rate). Nowadays the Euro is much stronger: a Dollar would buy you less than 1 Euro. Even if the Euro initially only existed electronically, it still had fixed exchange rates with the old currencies of the EU zone members. Most importantly with the established and well-trusted Deutsche Mark of Germany. So any explanation of 'why was the Euro initially weak' also has to explain why the DEM was weak at the time. The explanation given in that paragraph doesn't sound like it's passing that test.
- michelb 3y agoFinally something I actually CAN build in a weekend ;)
- emmelaich 3y agoFWIW, this is absolutely RESTful. Except not versioned I suppose, but if the only version is LATEST, then maybe it is.
- pietz 3y agoJust thinking out loud: If we make the zip file a parquet file, wouldn't we be able to do partial downloads (like getting the first 1000 lines) instead of getting the whole file every single time?
- hamilyon2 3y agoIf your data is somewhat sparse, somewhat hierarchical or have evolving/amorphous schema, gzipped jsonl is better. Jsonl is supposed by clickhouse and jq and a bit more consistent around escaping/delimiting
- zeristor 3y agoI've been looking for something use to show graphs in the terminal...
- warthog 3y agoYou should add files to the website in sync whenever they get published. Most statistics instututions publish their data in csv format in a webpage. You could bookmark them once and create a database of all the data so the app is used more
- deleted 3y ago[deleted]
- stcroixx 3y agoI worked at the Chicago Mercantile Exchange 15 or so years ago and we had dozens of similar files that most major financial institutions downloaded daily. If they weren’t published on schedule we would start getting calls from executives at Bloomberg, Goldman, etc. I wrote a small system to manage and monitor their publication.