Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: OctoSQL – Query and join multiple databases and files, written in Go

github.com

Post reply on HN
Print view View original

61–70 of 74 posts

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#61

Post by stevepike »

minxomat wrote: post #49
kfk wrote: post #48

Hey, at work we have been forced to use Denodo. How does this compare to Denodo? Seems a lot better and doesn’t try to do any “data virtualization”. I am a bit scared by the query optimizer because the Denodo one is super buggy. Overall I think the concept is great and this might be a great companion to etl tools.

If vis is the focus, I'd recommend Postgres FDW over anything else here, just because PG is supported by almost every vis software (metabase, redash, many Apache projects, Tableau, Google Data Studio (free) etc.)

I've been working on a new company that's doing cloud hosted postgres foreign data wrappers as a service (plus a bunch of interface / workflow stuff on top) and while I'm biased I totally agree. The postgres FDW system has gotten very sophisticated in later releases. It does performance optimizations like pushing down filters / aggregates, and it even goes further by supporting sending remote explain calls out to the underlying datasources to use in query planning. In one of my local tests vs. Denodo it smartly decided _not_ to push a group by down when the cardinality of the column being grouped on is very high (since the grouping doesn't reduce the number of rows sent over the wire very much), which blew me away.

If anyone is interested I'd love to chat: steve (at) syndetic.co

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#62

Post by cube2222 »

sixdimensional wrote: post #60
cube2222 wrote: post #2

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,…

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!

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#63

Post by jhoechtl »

cube2222 wrote: post #2

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,…

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

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#64

Post by cube2222 »

jhoechtl wrote: post #63
cube2222 wrote: post #2

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,…

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.

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#65

Post by simonw »

cube2222 wrote: post #2

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,…

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/

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#66

Post by tensor »

cube2222 wrote: post #2

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,…

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.

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#67

Post by nerdponx »

minxomat wrote: post #18
cube2222 wrote: post #2

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,…

> 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, cluster…

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.

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#69

Post by brunoqc »

tempguy9999 wrote: post #43
brunoqc wrote: post #22

Earlier quoted context omitted.

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…

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.

Re: Show HN: OctoSQL – Query and join multiple databases and files, written in Go

#70

Post by greggyb »

sixdimensional wrote: post #56
truth_seeker wrote: post #3

Earlier quoted context omitted.

Generally, I make use of file_fdw module of Postgres to achieve this. https://www.postgresql.org/docs/11/file-fdw.html

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:/…

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/').
Post reply on HN