5 ms·
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 plac
by cube2222 7y ago
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!
- truth_seeker 7y agoGenerally, I make use of file_fdw module of Postgres to achieve this. https://www.postgresql.org/docs/11/file-fdw.html https://www.postgresql.org/docs/11/file-fdw.html
- cube2222 7y agoDidn'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?
- truth_seeker 7y agoYes sir, that too is supported. https://www.postgresql.org/docs/11/contrib-dblink-function.html https://www.postgresql.org/docs/11/contrib-dblink-function.h...
- cube2222 7y agoSeems like it's solving part of the problems we're having really well, I'll have to read up more on it, thanks!
- truth_seeker 7y agoSure. You might also wanna check this: PG community extensions - https://pgxn.org/ 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.
- jon-wood 7y agoPostgres 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.
- brunoqc 7y agoI 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.
- michelpp 7y agoTHe 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-improving-copy-and-bulkloading-in-postgresql-12/ https://www.cybertec-postgresql.com/en/tech-preview-improvin...
- deleted 7y ago[deleted]
- truth_seeker 7y agoThis link covers it in detail: http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/ http://www.postgresqltutorial.com/import-csv-file-into-posgr...
- tempguy9999 7y agoAs 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
- sixdimensional 7y agoNot 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.
- riku_iki 7y agoDo fdw/standard connectors support pushdown predicates well? Documentation doesn't say much about that.
- truth_seeker 7y agoYep. PG 9.5 and later supports it. Use "EXPLAIN VERBOSE" before your query to get more info.
- just_myles 7y agoMy god. I didn't know postgres can do this. Thank you!
- sixdimensional 7y agoThis 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 https://en.wikipedia.org/wiki/SQL/MED
- greggyb 7y agoCompletely 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/').
- minxomat 7y ago> 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/ 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.
- cube2222 7y agoThanks, didn't know about it! I'll make sure to read up on it!
- dlphn___xyz 7y agoi 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?
- minxomat 7y agoSure. Create a filesystem data source in the Drill config (https://drill.apache.org/docs/file-system-storage-plugin/ 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.
- sixdimensional 7y agoYou might need to tweak the Drill JVM memory settings a little bit though if you're doing this on a netbook without much memory!
- nerdponx 7y ago
- tirumaraiselvan 7y agoAt 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-database-and-other-data-sources/ https://blog.hasura.io/remote-joins-a-graphql-api-to-join-da...
- bryanrasmussen 7y agomicrosoft logparser https://www.microsoft.com/en-us/download/details.aspx?id=24659 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
- arendtio 7y agoPretty cool stuff. Does it support writing to (single) data sources too or only reading queries?
- cube2222 7y agoThanks! 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.
- adonese 7y agothere's datasette[1] to look forward to (perhaps to get some inspirations or port some ideas) [1]: https://github.com/simonw/datasette https://github.com/simonw/datasette
- monstrado 7y agoCool 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-power-of-clickhouse-sql-in-a-single-command https://www.altinity.com/blog/2019/6/11/clickhouse-local-the...
- sixdimensional 7y agoA 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!
- cube2222 7y agoThanks for the kind words! Appreciate it!
- jhoechtl 7y agoDid it start as a support tool for easy query of open data or what was the motivation?
- cube2222 7y agoThe only motivation was to make a tool allowing you to query multiple datasource simultaneously in SQL and that was also the start of it.
- simonw 7y agoI'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 https://github.com/simonw/csvs-to-sqlite - convert CSV files to a SQLite database https://sqlite-utils.readthedocs.io/en/stable/cli.html https://sqlite-utils.readthedocs.io/en/stable/cli.html - convert JSON files https://github.com/simonw/db-to-sqlite 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#tools-for-creating-sqlite-databases 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/ https://datasette.readthedocs.io/
- cube2222 7y agoThis 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!)
- tensor 7y agoSounds 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.
- bmn__ 7y agoAlso see https://osquery.io/ https://osquery.io/ https://github.com/harelba/q https://github.com/harelba/q