Hacker News Reader

Comment by cube2222 | Hacker News Reader

Hey, one of the authors here.

The motivation behind this project is that I always wanted a simple commandline tool allowing me to join data from different places, without needing to set up stuff like presto or spark. On another hand, I never encountered any tool which allows me to easily query csv and json data using SQL (which at least in my opinion is fairly ergonomic to use).

This started as an university project, but we're now continuing it as an open source one, as it's been a great success so far.

Anyways, feedback greatly requested and appreciated!

View on HN

> commandline tool allowing me to join data from different places, without needing to set up stuff like presto or spark. On another hand, I never encountered any tool which allows me to easily query csv and json data using SQL (which at least in my opinion is fairly ergonomic to use).

This exactly describes Drill (https://drill.apache.org/) which can query any data source under the sun (RDBMS, NoSQL, files, clusters, serialized data (JSON, CSV, Parquet...), object storage (S3, OpenStack, ...)) using SQL and JOIN between them with pushdown optimizations. It's got an awesome CLI and can be used in any language via REST or JDBC.

Thanks, didn't know about it! I'll make sure to read up on it!

I last tried this program several years ago, and I remembered the set up was somewhat difficult and arcane. It still very really useful, I should give it another look.

i have a reoccurring use case where i have huge csv files that i need to aggregate / summarize using a pretty old netbook. the only way i can get this done is through spark/scala. would drill be a better option?

Sure. Create a filesystem data source in the Drill config (https://drill.apache.org/docs/file-system-storage-plugin/) with the directories where your CSVs are in. Then query them however you'd like. Use the CLI if you want to produce another CSV or REST/JDBC if you need the data somewhere else. If the query is complex and long-running you can monitor it's state in the Drill web UI.

You might need to tweak the Drill JVM memory settings a little bit though if you're doing this on a netbook without much memory!

Generally, I make use of file_fdw module of Postgres to achieve this.

https://www.postgresql.org/docs/11/file-fdw.html

Didn't know about that, thanks!

Is there a way to connect this to other databases, to basically have views of your redis/MySQL/other databases in postgres?

Yes sir, that too is supported.

https://www.postgresql.org/docs/11/contrib-dblink-function.h...

Seems like it's solving part of the problems we're having really well, I'll have to read up more on it, thanks!

Sure. You might also wanna check this:

PG community extensions - https://pgxn.org/

There are a lot of open-source or free extensions to make PG as a data platform rather than just a RDBMS.

Postgres Foreign Data Wrappers are an incredibly powerful tool. I've in the past built a basic data warehouse for a distributed system by pulling in read slaves from assorted service's databases as FDW sources to a central Postgres instance, after which Postgres allows you to treat them all as one big database.

This is a helpful utility for sure - but beware, joins between foreign data sources and local ones can be slow and very un-optimized, especially with bigger data sources.

I used to work for a company that made a data federation software platform. I am always surprised that people forget that management of external data is part of the SQL standard actually - SQL/MED ("SQL Management of External Data") [1]!

[1] https://en.wikipedia.org/wiki/SQL/MED

Completely unrelated, but that URL really threw me for a loop. I am not used to seeing a slash in the article topic (section after '/wiki/').

I wonder if you or the other Postgres users here could give me some tips.

What would be the best way to use the data from a huge Excel file into my web apps?

Currently I'm converting it to CSV and use `BULK INSERT` with MSSQL. I know that with MSSQL I can also use Excel files as "external database" but AFAIK, Excel files are not indexed and are super slow when used as an "external database".

I wouldn't mind switching to Postgres. I actually prefer Postgres.

THe process is similar, you would convert to csv and then use the \copy command to bulk insert the data. In pg 12, the copy command has grown a lot of very useful features like WHERE filtering, so if you get the chance go for 12:

https://www.cybertec-postgresql.com/en/tech-preview-improvin...

As an MSSQL guy who rarely connects it to excel, if your workflow allows it consider pulling the entirety of the excel sheet into a table in the db and working on it just from there. What's huge for excel isn't for MSSQL.

As a decades-long user of MSSQL guy who is getting sick of being rpeatedly done over by MS, could you explain why you prefer postgres? That would be incredibly interesting, TIA

For the excel sheet I was wondering if there was a better way since I want to update all the date many times per day to be up-to-date. I thought maybe I was wasting some ressources.

> could you explain why you prefer postgres?

I don't have many reasons to be honest. Both MSSQL and Postgres are fine I guess but I like that Postgres is open source and so I can use it in personal projects for fun.

I guess the thing I hate the most about MSSQL is that, at work, we never have the latest version, because why pay again. And every single time I search how to do something I must use the less optimal way since the new way only works on the latest version.

At least with Postgres I can always run the latest version.

I can't help you really but decide where the master data lives - in the spreadsheet or in the DB? Try and move it into the DB, so it needn't touch the spreadsheet unless you want to only look at it in the spreadsheet, never alter it there.

DBs handle and manipulate data better far better than excel. Excel does data presentation better.

A decent DB, well looked after and backed up, is the safest place for any quantity of data. Spreadsheets are not - excel is not reliable once you start filling it up and doing complex updates.

So that's my advice. Of course your needs and workflow may not fit that at all well, so YMMV!

Thanks for the opinion on mssql vs postgres.

Not OP but, in my mind (having used both extensively), Postgres is basically the closest I feel you can get to MSSQL in the open source world, in terms of completeness of a fully functional relational database that scales - not to mention Postgres has tons of features that MSSQL has been playing catch up with for years.

I like both of them personally. Postgres didn't used to have as strong of a showing on Windows, and not as fancy tools as Management Studio/Query Analyzer/Azure Data Studio, but I think today Postgres is absolutely viable to replace SQL Server if you want to switch to open source.

This link covers it in detail:

http://www.postgresqltutorial.com/import-csv-file-into-posgr...

Do fdw/standard connectors support pushdown predicates well? Documentation doesn't say much about that.

Yep. PG 9.5 and later supports it.

Use "EXPLAIN VERBOSE" before your query to get more info.

My god. I didn't know postgres can do this. Thank you!

At Hasura, we recently released the preview of Remote Joins that allows you to join different GraphQL APIs. So, all you need is a graphql wrapper over your DB/sources (Postgres comes in-built) and you can then join whatever sources.

https://blog.hasura.io/remote-joins-a-graphql-api-to-join-da...

A great motivation and keeping it simple from an interface perspective is a cool intention.

It's a great start with tons of directions you can take it, and many interesting challenges along the way - keep up the good work!

Thanks for the kind words! Appreciate it!

I've been approaching this problem from a slightly different direction: I'm constructing a set of tools for converting different data formats into SQLite, because once your data is in SQLite you can join it, filter it and do all kinds of other useful SQL things on top of it.

Tools I've written so far include:

https://github.com/simonw/csvs-to-sqlite - convert CSV files to a SQLite database

https://sqlite-utils.readthedocs.io/en/stable/cli.html - convert JSON files

https://github.com/simonw/db-to-sqlite - export data from any SQL database supported by SQLAlchemy (e.g. MySQL and PostgreSQL) into a SQLite file

More tools here: https://datasette.readthedocs.io/en/stable/ecosystem.html#to...

The end goal is to have databases I can load up in my Datasette web application tool so I can run SQL and export the results as JSON/CSV: https://datasette.readthedocs.io/

This looks great!

However, we're aiming for datasources which are either too big to download or change frequently, so also have very different goals really. (the same SQL for everything inspiration though!)

Cool project! I've had success with ClickHouses's local utility which is extremely fast. It helps that its basically a "local" version of an already insanely efficient columnar database.

https://www.altinity.com/blog/2019/6/11/clickhouse-local-the...

Also see https://osquery.io/ https://github.com/harelba/q

Sounds like a neat project. On the topic, you can actually make things like SQL files (but not json) available to PostgreSQL via Foreign Data Wrappers. Worth checking out if you use PostgreSQL already.

microsoft logparser https://www.microsoft.com/en-us/download/details.aspx?id=246... was good for querying various text formats from command line with an SQL-like syntax

Did it start as a support tool for easy query of open data or what was the motivation?

The only motivation was to make a tool allowing you to query multiple datasource simultaneously in SQL and that was also the start of it.

there's datasette[1] to look forward to (perhaps to get some inspirations or port some ideas)

[1]: https://github.com/simonw/datasette

Pretty cool stuff.

Does it support writing to (single) data sources too or only reading queries?

Thanks! We only support reads, and for now we're not planning to add write support.

You can use the csv output format though and later import that somewhere.

Reply on news.ycombinator.com