Use environment variables for playbook targets


We might be missing something basic, but after looking at the code and documentation, it seems that SQL Runner does not allow external configuration for its targets, only hardcoded values. This greatly limits the usability of SQL runner in an environment where we follow 12 Factor App rules and exernalize all configuration into environment variables.

What we tried was:

  - :name:     redshift
    :type:     redshift
    :host:     {{systemEnv "REDSHIFT_HOST"}}
    :database: {{systemEnv "REDSHIFT_DATABASE"}}
    :port:     {{systemEnv "REDSHIFT_PORT"}}
    :username: {{systemEnv "REDSHIFT_USER"}}
    :password: {{systemEnv "REDSHIFT_PASSWORD"}}

but this would require the YAML file to be passed through templating. It seems that only .sql files referenced by the playbook are templated and allow systemEnv and friends.

On the other token, EmrEtlRunner has long allowed external configuration:

    - name: redshift
      type: redshift
      host: <%= ENV['REDSHIFT_HOST'] %>
      database: <%= ENV['REDSHIFT_DATABASE'] %>
      port:  <%= ENV['REDSHIFT_PORT'] %>
      username: <%= ENV['REDSHIFT_USER'] %>
      password: <%= ENV['REDSHIFT_PASSWORD'] %>
      maxerror: 1
      comprows: 200000 # Default for a 1 XL node cluster. Not used unless --include compupdate specified
      ssl_mode: disable

This is pretty much a blocker for us right now as we try to roll out the built-in data modeling playbooks deduplicate and web-recalculate.

If the functionality is missing, can the templating be incorporated into 0.5.0?


Hi @rgabo - you are right: the playbook is not passed through templating. The SQL Runner uses the variables defined in the playbook to then apply templating to the SQL files.

This has been fine for us internally because we use an external configuration management system (similar to consul-template) which handles templating of the playbooks themselves.

But yes, as you say SQL Runner could support a two-pass templating system, where the playbook is templated, and then in turn the SQL files are templated. This would be a nice fit for 12 Factor App fans. PR welcome!