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

#11

Post by jon-wood » Tue, Jul 16, 2019, 2:22 PM UTC

cube2222 wrote: post #4

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

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?

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.

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

#12

Post by cube2222 » Tue, Jul 16, 2019, 2:24 PM UTC

srameshc wrote: post #9

Thanks for sharing. This is cool, just what I wanted. I like the fact I can create config files and query not just he database but also query JSON.

Make sure to let us know if you have any feedback after testing it out!

The Go standard library really helped handling all the different json file formats you might expect. (Like being a whole file table, or record per line)

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

#13

Post by 21stio » Tue, Jul 16, 2019, 2:37 PM UTC

awesome, really cool!

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

#14

Post by [deleted] » Tue, Jul 16, 2019, 2:40 PM UTC

[deleted]

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

#15

Post by mjirv » Tue, Jul 16, 2019, 2:47 PM UTC

This is great. I keep the following in my bash profile for querying CSVs with SQL, which works OK, but this is probably better:

  function csv_to_sqlite() {
    echo "used as csv_to_sqlite PATH_TO_CSV TABLE_NAME"
    sqlite3 -csv -cmd ".import $1 $2" -cmd ".headers on"
  }

Two suggestions:

1. It would be a lot more useful if I could link an entire database rather than just tables

2. Sometimes I just want to query a single CSV, so a command line argument where I can just specify the path would be nice

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

#16

Post by sails » Tue, Jul 16, 2019, 2:47 PM UTC

Really nice!

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

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

#17

Post by [deleted] » Tue, Jul 16, 2019, 2:49 PM UTC

MoOmer wrote: post #6

Really cool, thanks for sharing. You all might want to look at Apache Calcite ( http://calcite.apache.org/ ) as well for inspiration, which has similar functionality as a subset of its features!

[deleted]

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

#18

Post by minxomat » Tue, Jul 16, 2019, 2:51 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,…

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

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

#19

Post by blacksqr » Tue, Jul 16, 2019, 2:54 PM UTC

"Sqawk is an Awk-like program that uses SQL and can combine data from multiple files. It is powered by SQLite."

https://github.com/dbohdan/sqawk

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

#20

Post by cube2222 » Tue, Jul 16, 2019, 2:57 PM UTC

mjirv wrote: post #15

This is great. I keep the following in my bash profile for querying CSVs with SQL, which works OK, but this is probably better: function csv_to_sqlite() { echo "used as csv_to_sqlite PATH_TO_CSV TABLE_NAME" sqlite3 -csv -cmd ".import $1 $2" -cmd ".headers on" } Two suggestions: 1. It would be a lot more useful if I could link an entire database rather than just tables 2. Sometimes I just want to query a single CSV, s…

Thanks!

1. Initially we wanted to do one table per config-entry, but that'll be an easy to do UX improvement.

2. We're planning to add support for piping the csv/json file through stdin! That would be the most seamless workflow I think.