HSQL
"Hash (of data) and SQL" is a library that parses .sql files with YAML
front matter. This allows
analysts and other non-developers to write and develop ETLs without
having to write source code but still giving them the power of
specifying variables to interpolate into the SQL and other metadata that
the program executing the SQL can use.
How to use this
Rather than specifying variables and metadata for a set of database
queries in a .rb,.py or other programming language source file the queries
should be written into a .sql file directly.
# filename: daily_summary.sql
owner: jackdanger
schedule: hourly
environments:
production:
output_table: summaries
update_condition:
development:
output_table: jackdanger_summaries
update_condition: WHERE 1 <> 1
---
INSERT INTO {{{output_table}}} SELECT * FROM interesting_information;
UPDATE summaries_performed SET complete = 1 {{{update_condition}}};The above is a .sql file and any text editor will allow analysts to use
code completion and syntax highlighting for their queries.
The data hash in the YAML front matter lists a set of variables, by
environment, that can be interpolated into the SQL queries. To render
the queries an environment must be provided. The templating system used
for interpolating data is Mustache
(though theoretically we could use any templating system).
Rendering SQL on the command line
$ hsql daily_summary.sql -env development
USE some_database;
INSERT INTO jackdanger_summaries SELECT * FROM interesting_information;
UPDATE summaries_performed SET complete = 1 WHERE 1 <> 1;The hsql command-line utility outputs valid Postgres SQL with any
template variables filled in from the YAML data.
To access the metadata directly use the --yaml or --json flag
$ hsql daily_summary.sql -env development --json
{"owner":"jackdanger","schedule":"hourly","output_table":"jackdanger_summaries","update_condition":"WHERE 1 <> 1"}
$ hsql daily_summary.sql -env development --yaml
---
owner: jackdanger
schedule: hourly
output_table: jackdanger_summaries
update_condition: WHERE 1 <> 1Working with times and dates
By default when you run the hsql command it will set the template
variable {{{ now }}} to the current moment (as per the local
machine's clock). You can modify this by setting a --timestamp
command line flag to any Ruby-parseable time or date string and that
will be used to establish the value of {{{ now }}}.
To avoid having to do date math in SQL all of the following are also
available in every template, relative to the value of {{{ now }}}:
"now" => "'2015-10-06 12:34:55 -0700'",
"beginning_of_hour" => "'2015-10-06 12:00:00 -0700'",
"beginning_of_day" => "'2015-10-06 00:00:00 -0700'",
"beginning_of_week" => "'2015-10-05 00:00:00 -0700'",
"beginning_of_month" => "'2015-10-01 00:00:00 -0700'",
"beginning_of_quarter" => "'2015-10-01 00:00:00 -0700'",
"beginning_of_year" => "'2015-01-01 00:00:00 -0800'",
"end_of_hour" => "'2015-10-06 12:59:59 -0700'",
"end_of_day" => "'2015-10-06 23:59:59 -0700'",
"end_of_week" => "'2015-10-11 23:59:59 -0700'",
"end_of_month" => "'2015-10-31 23:59:59 -0700'",
"end_of_quarter" => "'2015-12-31 23:59:59 -0800'",
"end_of_year" => "'2015-12-31 23:59:59 -0800'",
"beginning_of_previous_hour" => "'2015-10-06 11:00:00 -0700'",
"end_of_previous_hour" => "'2015-10-06 11:59:59 -0700'",
"beginning_of_previous_day" => "'2015-10-05 00:00:00 -0700'",
"end_of_previous_day" => "'2015-10-05 23:59:59 -0700'",
"beginning_of_previous_week" => "'2015-09-28 00:00:00 -0700'",
"end_of_previous_week" => "'2015-10-04 23:59:59 -0700'",
"beginning_of_previous_month" => "'2015-09-01 00:00:00 -0700'",
"end_of_previous_month" => "'2015-09-30 23:59:59 -0700'",
"beginning_of_previous_quarter" => "'2015-07-01 00:00:00 -0700'",
"end_of_previous_quarter" => "'2015-09-30 23:59:59 -0700'",
"beginning_of_previous_year" => "'2014-01-01 00:00:00 -0800'",
"end_of_previous_year" => "'2014-12-31 23:59:59 -0800'"
Programmatic Ruby API
If you'd like to manipulate the queries in some advanced way (e.g. to modify the AST of a parsed query) you can use the gem as a dependency.
>> require 'hsql'
>> file = HSQL::File.parse_file('./simple.sql', 'development')
>> query = file.queries.first;
>> query.to_s
=> "INSERT INTO jackdanger_summaries SELECT count(*) FROM interesting_information"
>> query.ast
=> {
"INSERT INTO" => {
"relation" => {
"RANGEVAR" => {
"schemaname" => nil,
"relname" => "jackdanger_summaries",
"inhOpt" => 2,
"relpersistence" => "p",
"alias" => nil,
"location" => 13
}
},
"cols" => nil,
"selectStmt" => {
"SELECT" => {
"distinctClause" => nil,
"intoClause" => nil,
"targetList" => [
[0] {
"RESTARGET" => {
"name" => nil,
"indirection" => nil,
"val" => {
"FUNCCALL" => {
"funcname" => [
[0] "count"
],
"args" => nil,
"agg_order" => nil,
"agg_filter" => nil,
"agg_within_group" => false,
"agg_star" => true,
"agg_distinct" => false,
"func_variadic" => false,
"over" => nil,
"location" => 79
}
},
"location" => 79
}
}
],
"fromClause" => [
[0] {
"RANGEVAR" => {
"schemaname" => nil,
"relname" => "interesting_information",
"inhOpt" => 2,
"relpersistence" => "p",
"alias" => nil,
"location" => 95
}
}
],
"whereClause" => nil,
"groupClause" => nil,
"havingClause" => nil,
"windowClause" => nil,
"valuesLists" => nil,
"sortClause" => nil,
"limitOffset" => nil,
"limitCount" => nil,
"lockingClause" => nil,
"withClause" => nil,
"op" => 0,
"all" => false,
"larg" => nil,
"rarg" => nil
}
},
"returningList" => nil,
"withClause" => nil
}
}
>> query.ast['INSERT INTO']['relation']['RANGEVAR']['relname'] = 'othertable'
=> "othertable"
>> query.to_s
=> "INSERT INTO othertable SELECT count(*) FROM interesting_information"Please don't hesitate to open a PR or issue for any reason. New contributors and bug fixes are welcome. Forks of this project will be celebrated.