Is there a way to pass credentials as command-line variables or environment variables for table unloads?

[brentstrong]

I've been experimenting with passing credentials as variables to the unload macro so as to avoid having keys checked into a repo, but have not yet been successful. I'm wondering if anyone else has been able to do this.

I would expect that this code would work in combination with this command line call.

  config({
    "materialized":"table",
    "post-hook": [
      "{{ redshift.unload_table(this.schema,
            this.table,
            s3_path='s3://mybucket',
            aws_key={{ var(aws_access_key) }},
            aws_secret={{ var(aws_secret_access_key) }},
            add_quotes=True,
            escape=True,
            compression=GZIP,
            delimiter=',') }}"
    ]
  })
}}

select * from mytable

dbt run --model assurance_unload --vars "{aws_access_key: *******, aws_secret_access_key:*********}"

Instead I get this error.

    line 5
      aws_key={{ var(aws_access_key) }},

  > in macro statement (macros/core.sql)
  > called by macro run_hooks (macros/materializations/helpers.sql)
  > called by macro materialization_table_default (macros/materializations/table/table.sql)
  > called by model test_unload (models/assurance/test_unload.sql)

When trying different combinations of quote types and escaping quotes, various other errors are encountered.

Is this a supported use case? Thanks!

[drewbanin]

hey @brentstrong - check out the documentation for environment variables.

In this particular case, I think the issue is with the post-hook code. I think you have some extra curly brackets near the aws_key and aws_secret config keys. Since this block is already inside of the jinja curlies, you can just call the var function inline. The code below should work if you change var to env_var too!

{{
  config({
    "materialized":"table",
    "post-hook": [
      "{{ redshift.unload_table(this.schema,
            this.table,
            s3_path='s3://mybucket',
            aws_key=var('aws_access_key'),
            aws_secret=var('aws_secret_access_key'),
            add_quotes=True,
            escape=True,
            compression=GZIP,
            delimiter=',') }}"
    ]
  })
}}

I'm going to close this out because we typically use GitHub issues for bugtracking/prioritization rather than support, but feel free to join us over in slack if you'd like :)

Edit: Added quotes to the string inside of the var() function