Hacker News Reader

Comment by yosai | Hacker News Reader

@data_ders We realized that more than 70% of business data is shared in CSV and Excel formats, and only a small percentage use API integrations for data exchange..So CSV is here to stay for sure..On the other side,data engine is a sub module inside YoBulk.We are trying to automate complete CSV importing workflow..mostly solving the CSV errors in a collaborative way with the data donor..Yobulk's USP is how we show the errors in human readable way.We have written wrapper on top of some open source data validation engines.Yes..I have used apache Arrow..we not competing with Apache Arrow..We are creating an alternative to flatfile.com

View on HN

Man, been down this path for a long while. It gets tough! Flattening csvs with hierarchical headers (as in, headers that that apply a category to a second row of headers) are tough.

The ways csv can fail is just fucking nuts. Especially when they're half hand written, half automated, or where a failure is 20m rows in. Hard to have speed and strong checks simultaneously.

Yes you are right..In YoBulk we flatten the CSV to a JSON schema store it in a document DB and do all the validations.Chunking the CSV and analysing the stream buffers for validation is giving us speed also.

Have you been able to get something that might match a relational database? Auto-generation of a relational schema from a large dataset, or multiple datasets, is a deeply interesting idea.

Would you really pay for this? I made one for a client analyzing some X million line csvs by sharding the records then computing across 500 lambda instances to arrive at a schema

Sounds lovely, but not something I would be able to do for reasons that I won't go into here. I would love to hear how you do it, though I'd understand if you can't or won't share.

It was straight forward. Basically with the csv's I processed, there were a few sets of columns that would make natural tables, i.e. addresses that would create natural 'Street Name', 'City', 'Zip' Tables.

The first step was just sharding the csv's into 200 lines or so with a naming schema. Then storing those in S3.

Then the following was creating a manager Lambda that would send out the schema creation task to however many Lambda concurrent instances I could run, and hold the shards until instances freed up. So it allowed me to do the inefficient work in the individual Lambda instance.

The inefficient work was determining how many uniques were in the dataset with a threshold. As in if there were 50 or more uniques, then consider that column 'dead' to becoming a schema. However, if it were less, filter it up to the main manager thread.

Then the main manager thread would binary dispatch the results to combine the results into a main schema with another unique value threshold.

Overall it was a fun learning experience. The hardest part was removing the download time to get the full file into the lambda... I never got that right. The processing was dang fast, the latency to get the rows into the individual threads never got 'fast fast'

---

Good luck! Cheers!

We thought the same solution for YoBulk but intentionally did not use S3 and lambda because all cloud infra cost and maintenance cost associated with this solution.You can find the details in our blog https://www.yobulk.dev/blog/Scaling%20a%20CSV%20importer

Ha! Sweet - well done to you and the team.

Hope I get back on CSV projects again soon to try your stuff out. Cheers

Ohh Yes..You are spot on. It's there in our upcoming release.Stay tuned please.

I still like the jsonl standard quite a lot. JSON is pretty much universal, yet it‘s better structured than csv. The ‚l‘ine in jsonl makes it easier parsable and independent from the remaining structure. Keys are duplicated hard but that‘s where gzip comes in.

Reply on news.ycombinator.com