4 ms·

This is a great book, there’s a few tools I would add. Datasette, clickhouse local (cli) and duckdb. I think ripgrep is a big omission, ripgrep | xargs jq and

This is a great book, there’s a few tools I would add. Datasette, clickhouse local (cli) and duckdb. I think ripgrep is a big omission, ripgrep | xargs jq and find -exec jq is one of my most common data science workflows cause you can get stuff done in a few minutes. An example of where I use this is to debug Infrastructure as Code that is generated for many regions and AZs quickly. Another one I like in this space is Bioninformatics Data Skills, for some reason Bioinformaticians use CLI workflows a lot and this book covers a lot of good info for those who are just starting out like tmux, make, git, ssh, background processes. Two other techniques I like are git-scraping (tracking the changes of data over time or just saving snapshots of your data to git so you can diff it): https://simonwillison.net/2020/Oct/9/git-scraping/ https://simonwillison.net/2020/Oct/9/git-scraping/ I most recently have used this technique to diff changes to build artifacts over time. This technique is not really CLI related per se, but I really like the http range query technique of hosting data: https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630 There’s simple ways to use this idea (like cooking up a quick h2o web server conf) to host data quickly. I also like the makefile data pipeline idea, I believe the technique is described in the book but I first heard of it from this HN comment: https://news.ycombinator.com/item?id=18896204 https://news.ycombinator.com/item?id=18896204 The basic idea is that you can use make to orchestrate the steps of your command line data science workflow and let make figure out when your intermediate data needs to be generated again. A good example is this map reduce with make here: https://www.benevolent.com/news-and-media/blog-and-videos/how-use-makefiles-run-simple-map-reduce-data-pipeline/ https://www.benevolent.com/news-and-media/blog-and-videos/ho...

synergy20 2y ago
"ripgrep + find -exec jq" what does this imply?
- faizshah 2y ago
  I guess I should have written it more explicitly but my two most common techniques are: - ripgrep PATTERN | xargs jq - find PATTERN -exec jq In both cases you have a large amount of data in your file system that is in JSON and you want to extract a subset of it for further processing. Ripgrep is an extremely fast way to do a content based search for a pattern and find is a fast way to do a file path based search. Then jq lets you extract the data. For other types of data I use the same technique of first using ripgrep to find the candidate files and then piping to a text processor like awk/perl/ruby to actually process the data. If you need FTS rather than regex search then SQLite FTS5 is my go to. So a full example is something like: `find . -name “*.pod.json” -print0 | xargs -0 -P 12 -I {} sh -c ‘jq -r “select(.spec.containers != null) | .spec.containers | to_entries[]” sh {} \; | jq -s ‘sort_by(.image)’` Something like that so it’s sort of like a map-reduce you first narrow the subset of inputs by first finding by file pattern, then you pull out the relevant data from each in a parallel xargs, then you reduce it with a jq -s. This technique is used because jq is very slow on large files and your later processing scripts might be slow so the first step of a good pipeline is to first throw away all the data you don’t need first.
  - synergy20 2y ago
    Thanks. I use 'rg' a lot but it finds part of the json text, not sure how 'jq' can be used with that since you're not finding a full json object in general
    - faizshah 2y ago
      Oh I see, so what I do is I use rg -l aka —-files-with-matches. To produce the relevant files and I use the filepath as the key for the next part of the processing pipeline. So the pattern is to reduce the candidate set of files with rg or find then extract the relevant parts using xargs jq then pipe to jq -s to produce the dataset.
      - synergy20 2y ago
        That makes sense, thanks!
  - rahimnathwani 2y ago
    If you write a book I will buy it.
  - wwader 2y ago
    You might want to look into using jq --stream together with inputs/truncate_stream/fromstream and friends if you want to use jq with large inputs. Not a speed daemon but will probably use a lot less memory.
    - faizshah 2y ago
      Thanks, if anyone else is interested there is an explanation of this feature here: https://subtxt.in/library-data/2016/03/28/json_stream_jq https://subtxt.in/library-data/2016/03/28/json_stream_jq And: https://github.com/jqlang/jq/wiki/FAQ#streaming-json-parser https://github.com/jqlang/jq/wiki/FAQ#streaming-json-parser The last time I tried, I think the reason I gave up on JQ for large inputs was that the throughput would max out at 7mb/s whereas the same thing with spark SQL on the same hardware (MacBook) would max out at 250mb/s. So I started looking into using other solutions for big data while I use jq in parallel for small data in multiple files. I will test it out again cause this was 4-5 years ago when I last tested it, but I believe jaq is still preferred for large inputs. Still I prefer for big data to use Spark/Polars/clickhouse etc.
Vaslo 2y ago
What’s the difference between using duckdb and clickhouse? Do you use them together or are they very similar?
- Jgrubb 2y ago
  I'm relatively new to both and love them both for what they do well. Duck is "in process" like SQLite. Clickhouse is a client/server model like most other databases. Duck is currently more optimized for the analyst or whomever working locally, importing and cleaning data - it's incredibly generous and well optimized for importing many different formats and handling all manner of CSV varieties etc. It's fast and fun to work with but being in process it's not optimized for, say, setting up remotely as the backend for a BI thing like Metabase and then running typical ETL processes to bring in new data. Only 1 process is allowed to connect at a time, so Metabase would sit on that and then you can't import new data in another process. (There is a RO connect mode where multiple processes can connect to read, but nothing is allowed to write if something else is connected.) Clickhouse feels more like a traditional data warehouse solution, but wicked fast. It's a lot more fussy with getting data in - it's not nearly as generous with importing CSV for example - but once it's in and in the CH native format it is jaw-droppingly fast. Its compression scheme(s) is super efficient and it's also quite fun to play with. I'm on a BigQuery backed data stack at my job but dream about usecases where we might employ Clickhouse for something more specialized. I use Duck when I'm building an analysis - rather than constantly connecting to BQ I'll just bring the base data into DuckDB locally and then work from there. I also use them together on a side project of mine - I eat questionable CSV with Duck, then save it to Parquet which Clickhouse will easily handle.
  - datadrivenangel 2y ago
    I'm really excited for MotherDuck, which is basically going to be a BigQuery style serverless data warehouse, but DuckDB flavored, so you can do the local development and then just move it to the cloud without having to translate.
  - Vaslo 2y ago
    Interesting! I've been thinking about what you are doing but instead using DuckDB and Postgres but maybe I'll give Clickhouse a look instead of Postgres.

synergy20 2y ago

"ripgrep + find -exec jq" what does this imply?

faizshah 2y ago
I guess I should have written it more explicitly but my two most common techniques are: - ripgrep PATTERN | xargs jq - find PATTERN -exec jq In both cases you have a large amount of data in your file system that is in JSON and you want to extract a subset of it for further processing. Ripgrep is an extremely fast way to do a content based search for a pattern and find is a fast way to do a file path based search. Then jq lets you extract the data. For other types of data I use the same technique of first using ripgrep to find the candidate files and then piping to a text processor like awk/perl/ruby to actually process the data. If you need FTS rather than regex search then SQLite FTS5 is my go to. So a full example is something like: `find . -name “*.pod.json” -print0 | xargs -0 -P 12 -I {} sh -c ‘jq -r “select(.spec.containers != null) | .spec.containers | to_entries[]” sh {} \; | jq -s ‘sort_by(.image)’` Something like that so it’s sort of like a map-reduce you first narrow the subset of inputs by first finding by file pattern, then you pull out the relevant data from each in a parallel xargs, then you reduce it with a jq -s. This technique is used because jq is very slow on large files and your later processing scripts might be slow so the first step of a good pipeline is to first throw away all the data you don’t need first.
- synergy20 2y ago
  Thanks. I use 'rg' a lot but it finds part of the json text, not sure how 'jq' can be used with that since you're not finding a full json object in general
  - faizshah 2y ago
    Oh I see, so what I do is I use rg -l aka —-files-with-matches. To produce the relevant files and I use the filepath as the key for the next part of the processing pipeline. So the pattern is to reduce the candidate set of files with rg or find then extract the relevant parts using xargs jq then pipe to jq -s to produce the dataset.
    - synergy20 2y ago
      That makes sense, thanks!
- rahimnathwani 2y ago
  If you write a book I will buy it.
- wwader 2y ago
  You might want to look into using jq --stream together with inputs/truncate_stream/fromstream and friends if you want to use jq with large inputs. Not a speed daemon but will probably use a lot less memory.
  - faizshah 2y ago
    Thanks, if anyone else is interested there is an explanation of this feature here: https://subtxt.in/library-data/2016/03/28/json_stream_jq https://subtxt.in/library-data/2016/03/28/json_stream_jq And: https://github.com/jqlang/jq/wiki/FAQ#streaming-json-parser https://github.com/jqlang/jq/wiki/FAQ#streaming-json-parser The last time I tried, I think the reason I gave up on JQ for large inputs was that the throughput would max out at 7mb/s whereas the same thing with spark SQL on the same hardware (MacBook) would max out at 250mb/s. So I started looking into using other solutions for big data while I use jq in parallel for small data in multiple files. I will test it out again cause this was 4-5 years ago when I last tested it, but I believe jaq is still preferred for large inputs. Still I prefer for big data to use Spark/Polars/clickhouse etc.

Vaslo 2y ago

What’s the difference between using duckdb and clickhouse? Do you use them together or are they very similar?

Jgrubb 2y ago
I'm relatively new to both and love them both for what they do well. Duck is "in process" like SQLite. Clickhouse is a client/server model like most other databases. Duck is currently more optimized for the analyst or whomever working locally, importing and cleaning data - it's incredibly generous and well optimized for importing many different formats and handling all manner of CSV varieties etc. It's fast and fun to work with but being in process it's not optimized for, say, setting up remotely as the backend for a BI thing like Metabase and then running typical ETL processes to bring in new data. Only 1 process is allowed to connect at a time, so Metabase would sit on that and then you can't import new data in another process. (There is a RO connect mode where multiple processes can connect to read, but nothing is allowed to write if something else is connected.) Clickhouse feels more like a traditional data warehouse solution, but wicked fast. It's a lot more fussy with getting data in - it's not nearly as generous with importing CSV for example - but once it's in and in the CH native format it is jaw-droppingly fast. Its compression scheme(s) is super efficient and it's also quite fun to play with. I'm on a BigQuery backed data stack at my job but dream about usecases where we might employ Clickhouse for something more specialized. I use Duck when I'm building an analysis - rather than constantly connecting to BQ I'll just bring the base data into DuckDB locally and then work from there. I also use them together on a side project of mine - I eat questionable CSV with Duck, then save it to Parquet which Clickhouse will easily handle.
- datadrivenangel 2y ago
  I'm really excited for MotherDuck, which is basically going to be a BigQuery style serverless data warehouse, but DuckDB flavored, so you can do the local development and then just move it to the cloud without having to translate.
- Vaslo 2y ago
  Interesting! I've been thinking about what you are doing but instead using DuckDB and Postgres but maybe I'll give Clickhouse a look instead of Postgres.