cube2222 / octosql

OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.
Mozilla Public License 2.0
4.76k stars 200 forks source link

Add suport for in-code defined SQL datasources #175

Closed styczynski closed 2 years ago

styczynski commented 4 years ago

This PR adds the following features:

Important notes:

Detailed description

In my opinion, which may be biased, it's much quicker to copy just one (saved elsewhere) string containing SQL query than copying the configuration file and that string together. This PR introduces custom CREATE statements:

  CREATE DATASOURCE [name] OF TYPE [type] OPTIONS [options_specs]

Where [name] is valid SQL identifier, [type] is an SQL literal string containing type name of the data source and [options_specs] is either empty or is of the form:

 option_specs := ( <option_name> <sql_expr> [, <identifier> <sql_expr>...] )
 option_name := <identifier> | <indentifier> . <option_name>

Example usage:

  CREATE DATASOURCE bikes OF TYPE 'json' OPTIONS (path 'bikes.json');
  SELECT * FROM bikes bikes

Configuration keys can be nested if needed:

  CREATE DATASOURCE some_source OF TYPE 'some_type' OPTIONS (x.y.z 'value');

Please note: Nested values are supported, but arrays are not. The keys values are yet limited to SQL strings.

This is clean and expressive. The options notation was taken from SQLServer OPTION CLAUSE and entire syntax was based on CREATE SERVER

As the byproduct of that feature Octosql can now execute multiple queries in a single console command:

  $ octosql "SELECT * FROM bikes bikes; SELECT * FROM tests tests"

The other minor change was rewriting app/app.go into many files in octosql/ which exports very simple API. This way the code is much cleaner and let's third party tools (like native bindings) use awesome Octosql features much easier.

cube2222 commented 4 years ago

Overall I'm speechless, this is great! (though I still encourage you to speak with us (making an issue/request for comments before making PR's 😛)

Anyways, I've glanced through the code, put some cosmetic comments. I'll read it more thoroughly soon, but this is probably ready to merge.