mattewong

"I don't want to be beholden to Gmail [...] controlling a record of all of my conversations". Replacing Gmail: now THAT sounds interesting

While you're mentioning single-byte assumptions, add some more: - All space, dash and underscore characters are single-byte

I might disagree with some on your list, such as the below: - Excel can losslessly save CSVs it opens

This is not always false, depending on how careful the user is. Double-clicking to open a CSV file is definitely, but using the menu to import data, then going through the UI to treat every column as Text (and not General) should be fine

I might also add a couple: - A header row, if present, only spans a single row

OK thanks, I get that now. I suppose there could be at least two alternatives:

1. use "un-integrated" python code e.g. run a subprocess independently that just runs an external binary / shared lib / etc

2. avoid solutions that do not require a python environment entirely, or, use them in a less-commonly-employed manner that would not run the product through python (e.g. Airflow bash operator)

Both of the above seem perfectly reasonable to me, but I'm not sure whether others would agree.

(re "data product", say for example, Cockroach DB which I have no affiliation with and already has a python integration)

With platforms like DataBricks and Airflow that are heavily inclined towards python, it feels somewhat unavoidable to offer a python "import myproduct" option. But, is it truly necessary to be part of that python ecosystem to survive as a data product?

I know: python has a vibrant community, it's a virtuous cycle because every data lib is in python and therefore every data platform / analyst uses it etc, why would you ever not want to just add your product to that ecosystem etc. I get that, but that's also why I'm asking the question

This is a sincere question and I have no idea what you mean by 'treated like children with half-baked "integrations"'?

The intended context of the question, which admittedly could be more clear-- is for a data product that could be used for any data. Take Cockroach DB for example, which already has python integration ( https://www.cockroachlabs.com/blog/sample-app-python-and-coc... )

With platforms like DataBricks and Airflow that are heavily inclined towards python, it feels somewhat unavoidable to need to offer a way to access the product via "import" in python.

So the question is: in that scenario, could a commercial product still survive-- or even thrive-- without doing so?

It's nice that q has caching.... then again, it kind of needs it to solve its performance inefficiency.

Running "select *" on a 1mm-row worldcitiespop_mil file, q takes 27 seconds compared to `zsv sql` which takes 1.7 seconds ( https://github.com/liquidaty/zsv ) and also supports multiple file joins. I'm sure q is faster once cached, but taking a 16x performance hit up-front is not for me

Not sure what you mean by csvw. But, zsvlib is a CSV parser, and zsv is a CLI that uses zsvlib. zsv also uses the sqlite3 vtable based on the example in the original sqlite3 code, but it modifies it to use the zsvlib parser instead of the original CSV parser. The zsv parser is different from most CSV parsers in how it uses SIMD operations and minimizes memory copying. zsvlib parses CSV based on the same spec that Excel implements, so having data URIs in the CSV is fine, but if you wanted a compound value (e.g. text + link), you would need to overlay your own structure into the CSV text data (for example, embed JSON inside a column of CSV data). Not sure that answers your question but if not, feel free to add further detail and I'll try again...

https://github.com/liquidaty/zsv/blob/main/app/external/sqli... modifies the sqlite3 virtual table engine to use the faster zsv parser. have not quantified the difference, but in all tests I have run, `zsv sql` runs faster (sometimes much faster) than other sqlite3-on-CSV solutions mentioned in this entire discussion (unless you include those that cache their indexes and then measure against a post-cached query). Disclaimer: I'm the main zsv author

It doesn't detect column types automatically-- they are imported as text. You can, however, use math functions on them and sqlite3 will dynamically convert where possible (e.g. "select number_1_stored_as_text + 1 from mytable" will output 2)

Over all these CLIs, I prefer zsv ( https://github.com/liquidaty/zsv )-- then again, I wrote it so my preference should come as no surprise. On my Mac was 340% faster than the OP command, and does a lot more than just SQL/sqlite3-related

Nice article. I'm left wondering: - Are there any benchmarks available? - Is any full code example available (as opposed to a snippet)? - Does it handle real-world edge cases (unix vs dos line ends, non-"normal" quoting etc) - Does it normalize (e.g. `"hi"` and `hi` are both returned as `hi`)? - How does it compare, performance-wise (or in other ways) with https://github.com/BurntSushi/xsv, https://github.com/eBay/tsv-utils or https://github.com/liquidaty/zsv? (disclaimer: I wrote zsv)

The rate itself is nothing new; 5.78 is still lower than the average 30-yr fixed rate between 1972 and today (at least per https://www.bankrate.com/mortgages/historical-mortgage-rates... )-- and possibly since the beginning of mortgages.

But, the change in rates over the last 12 months -- a 97% increase from 2.93-- that is something new that the US market has never experienced before

Glad to see this. I'm in the midst of evaluating "modern cloud" ETL approaches and it seems that Airflow comes up everywhere I look, yet Step Functions seems better-- glad to see others with that perspective too. Major advantages I see are a) serverless, b) JSON-based config and c) no python lock-in. The only real disadvantages I would envision are a) AWS lock-in and b) at some volumne, server-based will be cheaper for high volume. Would love to find a cloud-vendor-neutral version of step functions that could be either serverless or not)

if you can combine this with cloud services (for example, each node is an S3 bucket or a lambda function) and now we're cookin with gas

Sorry for the delay, didn't see your reply so thought the thread was dead. Whether you want to add a new column to the input data, or add a new column to the report, I'm not seeing any need for backfilling either way.

There is a better way to do this.

An example of output that fits this paradigm you describe-- but to a much further degree-- would be the dozens of tables shown in the securities offering described at https://www.sec.gov/Archives/edgar/data/0001561167/000114420... search for "Stated Principal Balances of the Mortgage Loans as of the Cut-off Date" (on page A-3).

How do you generate reports like this in a manner that is flexible for end users without requiring IT in the middle? You start with the bare input, which is: data + report logic:

1. Specify the common columns that you want in your output tables (i.e. columns other than the first). In your example, that would be order_count, gmv, net_gmv

2. Separately, specify the tables that you want to generate, where each table spec consists of:

  - the bucket logic (in your example, that would be a formula representing "age broken out in buckets of 10")

  - optional other characteristics such as whether the data should be filtered before going into the table, which column the table should be sorted on, whether to limit the table output to the "top [10]" rows, etc etc

3. Third, run your data, plus the above spec, through some software that will generate your report for you

As for part 3, my company has recently launched a free platform for doing all of the above in a collaborative and secure manner. Please reach out if you'd like more info on this. Of course, you can do it yourself or have your IT do it-- but be aware it is not as easy as it sounds when you start having to deal with real-world practicalities like schema variability and scalability. And anyway, why bother if you can now do it all for free?

are you using the same categories AMZN provides or using another approach to categorize? You mention open sourcing the code, I would have checked there for an answer but couldn't find the link to it

BTW does c2tsv handle multibyte UTF8, \r\n vs \n, regular escapes e.g. embedded dbl-quote/nl/lf/comma, as well as CSV garbage that doesn't exist in theory but is abundant in real world (e.g. dbl-quote inside a cell that did not start w dbl-quote, malformed UTF8, etc)? Handling those in the same way Excel does added considerable overhead to zsv (and is the reason it could only perform a subset of the processing in SIMD and had to use regular branch code for other)

thanks for mentioning. will try out. did you use the default build settings for zsv (i.e. just plain old "make install")? also do you have copy or location for the dataset you used to test on? also what hardware / os if I may ask?

This is not an outlier. `mlr` is quite slow, literally off-the-charts slow for our purposes when we benchmarked vs xsv and zsv (see https://github.com/liquidaty/zsv. disclaimer: I'm one of its authors)

Not a problem at all. Nice work with goawk!

FYI I ran on the worldcities data at https://github.com/petewarden/dstkdata (credit to xsv for choosing that dataset) against https://github.com/BurntSushi/xsv and https://github.com/liquidaty/zsv (full disclosure: I am one of the zsv authors). Here's what I got.

fastest to slowest: zsv (0.07), xsv (0.16), goawk (0.42), python (~1.6)

Obviously, does not tell the whole story as this test was limited to "count" and an interpreted language is expected to always be slower compared to a precompiled command, but, it might be relevant to a user deciding what tool to use. Also, might be instructive as to room for improvement in the go code (or possibly the go code could use the c lib)-- I note that even if the goawk command is '{}' the runtime is still about the same.

full results:

---

goawk:

1000001 7000007 real 0m0.435s user 0m0.435s sys 0m0.031s

1000001 7000007 real 0m0.413s user 0m0.419s sys 0m0.024s

1000001 7000007 real 0m0.425s user 0m0.430s sys 0m0.024s

xsv:

1000000 real 0m0.157s user 0m0.141s sys 0m0.013s

1000000 real 0m0.156s user 0m0.141s sys 0m0.012s

1000000 real 0m0.158s user 0m0.142s sys 0m0.013s

zsv:

1000000 real 0m0.066s user 0m0.053s sys 0m0.010s

1000000 real 0m0.077s user 0m0.060s sys 0m0.012s

1000000 real 0m0.069s user 0m0.056s sys 0m0.010s

python:

1000001 7000007 real 0m1.589s user 0m1.553s sys 0m0.026s

1000001 7000007 real 0m1.583s user 0m1.550s sys 0m0.025s

1000001 7000007 real 0m2.122s user 0m1.675s sys 0m0.037s

---

The script for this was:

---

echo 'goawk:'

(time goawk -i csv '{ w+=NF } END { print NR, w }' < worldcitiespop_mil.csv) 2>&1 | xargs

(time goawk -i csv '{ w+=NF } END { print NR, w }' < worldcitiespop_mil.csv) 2>&1 | xargs

(time goawk -i csv '{ w+=NF } END { print NR, w }' < worldcitiespop_mil.csv) 2>&1 | xargs

echo 'xsv:'

(time xsv count < worldcitiespop_mil.csv) 2>&1 | xargs

(time xsv count < worldcitiespop_mil.csv) 2>&1 | xargs

(time xsv count < worldcitiespop_mil.csv) 2>&1 | xargs

echo 'zsv:'

(time zsv count < worldcitiespop_mil.csv) 2>&1 | xargs

(time zsv count < worldcitiespop_mil.csv) 2>&1 | xargs

(time zsv count < worldcitiespop_mil.csv) 2>&1 | xargs

echo 'python:'

(time python3 count.py < worldcitiespop_mil.csv) 2>&1 | xargs

(time python3 count.py < worldcitiespop_mil.csv) 2>&1 | xargs

(time python3 count.py < worldcitiespop_mil.csv) 2>&1 | xargs

thanks for this. am looking at the benchmarks. how do I get huge.csv? Don't see how to fetch or generate

Looks cool. But why use this instead of jq? You mention it's "like jq on steroids"; could you provide an example of a real-world use case where it solves a problem that couldn't be solved with jq?

Assuming you want to do this on a single machine with finite memory, a feasible approach would be:

1. Split into multiple shard using e.g. `split`, where each piece is small enough to fit comfortably into memory 2. Sort each shard. You could use something like `zsv` (sql sub-command) (https://github.com/liquidaty/zsv) or `xsv` (sort subcommand) (https://github.com/BurntSushi/xsv) for this 3. Use mergesort to sort all shards, at the same time, into a single output table, and in the process, de-dupe and/or merge (if you had too many shards for your operating system to allow so many files to be open at once, you might need to do an intermediate merge to consolidate shards)

For #3, I don't know of a tool to do this-- probably one exists for simple de-duping, but it may be harder if you need support for merge logic. If a tool for this does not exist, I would be imagine `zsv` could quite be easily extended to handle it.

Are you asking how to do this with a single machine, or how to do this assuming you have access to cloud resources?

You are in luck. My company is launching a free solution to exactly this problem. You will be able to normalize tables of data (from csv, txt, xlsx and more) that have the same subject matter but different formats. Variations in names, scalings, enumerations, derivations and more are automatically handled. You won’t need to hand over your data and you can choose to share the process you’ve created with anyone else in the world (or keep it private). Email me at info@liquidaty.com, we would love to help you with this as part of our private beta and I’m certain it will save you a ton of pain (and anyone who comes after you who you'd like to enable to solve this same problem in the future)

jq

I never really understood why anyone thinks this is a good idea. Popular, maybe. Smart? Not so sure.

Instead of just modifying your parser, you change your entire input format to be incompatible with what most of the rest of the world uses (standard JSON).

I get it, it requires slightly more technical skill / effort / etc to implement a proper streaming JSON parser (which BTW will perform WAY better in terms of speed and memory) instead of just writing "my_data.split('\n')". But, there are pre-existing libraries that already do that, and a one-time tiny bit of extra work, to open up a capability that you can reuse anywhere in your stack, seems to be a much better option than a lifetime of incompatibility with standard JSON.

I spent 10 seconds on it. looks pretty cool. But, some questions before I'd try to use it instead of jquery datatables or handsontable: - why did you build it and what alternatives did you try first? What unmet need does it fulfill? how does it compare to other libs like handsontable or jquery datatables? - how extensible is it? e.g. if I want to add custom context menus, custom locked / editable cells, etc - how is the license enforced-- i.e. does it require internet connectivity to work?

Couple further comments: - I tried copy + paste in your 1mm cells demo, and it didn't behave as expected (copied "n;m", pasted "n") - 1mm cells isn't very much if you are touting performance. 100k rows with 500 columns each = 50,000,000 cells; that would be a better example if you are trying to show that your lib handles large tables well. And if you are trying to suggest that this could be compared to Excel, then 1mm rows with 1000 columns would be better (Excel supports 1,048,576 rows by 16,384 columns)

anyway, looks like a nice start-- good luck!