Show HN: OctoSQL – Query and join multiple databases and files, written in Go • Hacker News Board

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

#71

Post by cube2222 » Wed, Jul 17, 2019, 7:08 AM UTC

simonw wrote: post #65

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

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!)

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

#72

Post by tempguy9999 » Wed, Jul 17, 2019, 9:15 AM UTC

brunoqc wrote: post #69

tempguy9999 wrote: post #43

Earlier quoted context omitted.

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

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.

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

#73

Post by bmn__ » Thu, Jul 18, 2019, 1:25 PM UTC

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

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

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

#74

Post by cube2222 » Thu, Jul 18, 2019, 10:03 PM UTC

riku_iki wrote: post #39

cube2222 wrote: post #24

Earlier quoted context omitted.

No, currently we support only lookup joins. We're planning to add table/hash-joins based on table-size heuristics.

How do you look up csv records?..

Hey, sorry for the late response, currently totally inefficient - scan whole file each time. We plan on adding an in memory or on disk index for such cases.