4 ms·

@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 exchang

by yosai 4y ago

@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

chaps 4y ago
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.
- yosai 4y ago
  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.
  - chaps 4y ago
    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.
    - anonymouse008 4y ago
      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
      - chaps 4y ago
        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.
        anonymouse008 4y ago
        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!
        yosai 4y ago
        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 https://www.yobulk.dev/blog/Scaling%20a%20CSV%20importer
        anonymouse008 4y ago
        Ha! Sweet - well done to you and the team. Hope I get back on CSV projects again soon to try your stuff out. Cheers
    - yosai 4y ago
      Ohh Yes..You are spot on. It's there in our upcoming release.Stay tuned please.
faebi 4y ago
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.

by yosai 4y ago

chaps 4y ago

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.

yosai 4y ago
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.
- chaps 4y ago
  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.
  - anonymouse008 4y ago
    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
    - chaps 4y ago
      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.
      - anonymouse008 4y ago
        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!
        yosai 4y ago
        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 https://www.yobulk.dev/blog/Scaling%20a%20CSV%20importer
        anonymouse008 4y ago
        Ha! Sweet - well done to you and the team. Hope I get back on CSV projects again soon to try your stuff out. Cheers
  - yosai 4y ago
    Ohh Yes..You are spot on. It's there in our upcoming release.Stay tuned please.

faebi 4y ago

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.