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

51–60 of 74 posts

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

#51

Post by cube2222 »

arendtio wrote: post #50
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,…

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.

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

#52

Post by cube2222 »

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.

Hey, I have no idea really. I've never used Denodo and a quick glance at their homepage doesn't tell much so I don't understand what they mean with data virtualization.

We're obviously surely not as battle tested, this being the initial release, but hopefully we'll be able to compare favorably!

The query optimizer is kinda simple currently. Mostly pushing down filters under maps and pushing supported workloads down to the datasources. Though I don't know how it will evolve going further ;)

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

#53

Post by adonese »

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

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

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

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

#54

Post by monstrado »

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

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...

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

#55

Post by sixdimensional »

minxomat wrote: post #46
dlphn___xyz wrote: post #45

Earlier quoted context omitted.

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!

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

#56

Post by sixdimensional »

truth_seeker wrote: post #3
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,…

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://en.wikipedia.org/wiki/SQL/MED

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

#57

Post by sixdimensional »

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.

I used to work for a competitor to Denodo. Query optimization for data virtualization/federation across heterogenous systems is very complicated - that much I can assure you.

You might be interested in Dremio (which is a modernized and open source version of Drill, but also has a commercial version) too. I had previously studied Spark, PrestoDb and Hive, among other things, for similar purposes.

If you ever want to chat feel free to contact me via my profile email.

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

#58

Post by sixdimensional »

sails wrote: post #16

Really nice! Something similar (with a GUI) is https://www.dremio.com/ that I think has gained some traction.

Dremio is quite cool and still something I am watching develop closely. Their involvement with the open source ecosystem is quite nice. Apache Drill, Arrow, Parquet and Gandiva are all super important contributions that the team behind Dremio has been involved in AFAIK.

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

#59

Post by sixdimensional »

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

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.

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

#60

Post by sixdimensional »

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!

Post reply on HN