How to reference variables inside sqlrunner .sql scripts


#1

Hi all,
How do I insert a variable into a .sql script that is run by sqlrunner?

I’m using the .sql script example here as reference.

Here’s what I have so far, along with the error I’m seeing:

Bash
/var/snowplow-sqlrunner/sql-runner -var var1=schema1 -playbook /path/to/playbook.yml

Playbook.yaml
:targets:
  - :name:     "name"
    :type:     postgresql
    :host:     localhost
    :database: warehouse
    :port:     5432
    :username: abcd
    :password: 1234
:steps:
  - :name: "01"
    :queries:
      - :name: "query"
        :file: ./script.sql
		
script.sql
INSERT INTO {{.var1}}.mytable ......

Error output
ERROR #42601 syntax error at or near "{"

#2

@rob,

You are using variables. To recognize them, you need to add :template: true as per https://github.com/snowplow/sql-runner/wiki/Guide-for-analysts#query-files


#3

@ihor - That did it, thanks!


#4

can I override playbook variables from command line

/var/snowplow-sqlrunner/sql-runner -var host=abc.com -playbook /path/to/playbook.yml