Why my favourite API is a zipfile on the European Central Bank's website

Re:Why my favourite API is a zipfile on the European Central... by wefarrell on Friday September 15, 2023 @04:20PM (#37528635)

One nice thing about CSV files being zipped and served via the web is they can be streamed directly into the database incredibly fast without having to persist them anywhere (aside from the db).

You can load the zip file as a stream, read the CSV line by line, transform it, and then load it to the db using COPY FROM stdin (assuming Postgres).

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by heavenlyblue on Friday September 15, 2023 @04:22PM (#37528663)

That doesn't sound like an amazingly safe idea

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by dambi0 on Friday September 15, 2023 @04:25PM (#37528683)

What specific risks do you foresee with this approach?

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by diroussel on Friday September 15, 2023 @04:36PM (#37528787)

Seem totally fine to me. As long as you can rollback if the download is truncated or the crc checksum doesn’t match.

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by chii on Saturday September 16, 2023 @12:08AM (#37531974)

> or the crc checksum doesn’t match.

which wouldn't exist if the api is simply just a single CSV file?

at least with a zip, the CRC exists (an incomplete zip file is detectable, an incomplete, but syntactically correct CSV file is not)

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by NL807 on Saturday September 16, 2023 @01:44AM (#37532332)

DROP DATABASE blah;

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by aidos on Saturday September 16, 2023 @03:08AM (#37532647)

That’s not how COPY FROM works in postgres. You give it a csv and a table matching the structure and it hammers the data into the table faster than anything else can.

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by berkes on Friday September 15, 2023 @05:19PM (#37529256)

It isn't. But that's easily mitigated with temp tables, ephemeral database and COPY etc.

Upstream can easily f-up and (accidentally) delete production data if you do this on a live db. Which is why PostgreSQL and nearly all other DBS have a miriad of tools to solve this by not doing it directly on a production database

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by wefarrell on Friday September 15, 2023 @08:53PM (#37530904)

Maybe I'm missing something but I don't see how it's possible for a COPY statement alone to remove existing data.

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by LgWoodenBadger on Friday September 15, 2023 @10:44PM (#37531545)

If in the regular scenario you load 10000 rows of new data and delete the old then it’s fine.

What if someone screws up the zip and instead of 10000 today, it’s only 10?

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by aidos on Saturday September 16, 2023 @03:11AM (#37532662)

I had this last week, but instead it was a 3rd party api and their service started returning null instead of true for the has_more property beyond the second page of results.

In either the solution is probably to check rough counts and error if not reasonable.

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by camgunz on Saturday September 16, 2023 @07:26AM (#37533847)

I think generally don't replace the prod db until the new one passes tests.

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by thibaut_barrere on Friday September 15, 2023 @05:27PM (#37529326)

Definitely, it is much easier to stream CSV than say JSON or XML (even if JSONL/Sax parsers exist etc).

[ Parent | on HN ]

Re:Why my favourite API is a zipfile on the European Central... by eastbound on Friday September 15, 2023 @05:50PM (#37529531)

I you feel risky, try a Foreign Data Wrapper ;)

[ Parent | on HN ]