AWS Access key is not being fetched from env variables

nazfoxway commented 9 months ago

Describe the bug

Steps to reproduce

 vars:
  clickhouse_s3:
   bucket: xx/xx
   path: /**.parquet
   fmt: Parquet
   aws_access_key_id: "{{ env_var('AWS_ACCESS_KEY_ID') }}"
   aws_secret_access_key: "{{ env_var('AWS_SECRET_ACCESS_KEY')}}"

or select * from {{clickhouse_s3source('clickhouse_s3',aws_access_key_id='{{ env_var("AWS_ACCESS_KEY_ID") }}',aws_secret_access_key='{{ env_var("AWS_SECRET_ACCESS_KEY")}}')}}

It wont pick up env var. If provided explicitly it works.

Expected behaviour

Should work with variables

Code examples, such as models or profile settings

dbt and/or ClickHouse server logs

Configuration

Environment

dbt version:
dbt-clickhouse version:
clickhouse-driver version (if using native)
clickhouse-connect version (if using http):
Python version:
Operating system:

ClickHouse server

ClickHouse Server version:
ClickHouse Server non-default settings, if any:
CREATE TABLE statements for tables involved:
Sample data for these tables, use clickhouse-obfuscator if necessary

emirkmo commented 9 months ago

If you don't even take a moment to provide any information about your setup, it's likely your setup that causes it as environment variable parsing is a feature of dbt core and its jinja and yaml templating.

nazfoxway commented 9 months ago

its hard to give more context since i did everything as in documentation and its not really more than described. As an example the setup with duckdb same env variables work perfectly but Clickhouse not. All other vars are being picked up except access id and secret. If these env variables would not work, they would not work in other projects also.

nazarigue commented 7 months ago

FYI finally we figured out that in this macro secrets are not being passed via env variables, so we created our own s3 macro so secret would be hidden. So macro works only when access id and secret are exposed in .sql file.

emirkmo commented 7 months ago

Thanks for the update, however I still don't understand what you mean as we use environment variables to fill the secrets for the macro and have done it for the past half year.

dvic commented 6 months ago

I'm hitting the same problem, setting config like

 vars:
  clickhouse_s3:
   bucket: xx/xx
   path: /**.parquet
   fmt: Parquet
   aws_access_key_id: "{{ env_var('AWS_ACCESS_KEY_ID') }}"
   aws_secret_access_key: "{{ env_var('AWS_SECRET_ACCESS_KEY')}}"

does not work, as described above. Setting it explicitly without env_var works but only when passed directly to the macro.

wei-jiang-dns53 commented 2 months ago

FYI finally we figured out that in this macro secrets are not being passed via env variables, so we created our own s3 macro so secret would be hidden. So macro works only when access id and secret are exposed in .sql file.

Agree with you.

I just pdb into dbt/adapters/clickhouse/impl.py and found the reason.

even the document says we can define AWS credentials

in dbt_projects.yml>>>vars>>>*_s3,
in schema.yml,
in jinja2 {{config}}

But in function, adapter.s3source_clause loads aws credentials from the macro clickhouse_s3source() ONLY! see https://github.com/ClickHouse/dbt-clickhouse/blob/43de9c63e3133d14861fde0b65837a4bec2f0a75/dbt/adapters/clickhouse/impl.py#L261-L266

Finally, the solution should be like :

Inject AWS_ACCESS_KEY_ID & AWS_SECRET_ACCESS_KEY into environment variables

and


select *
from {{ clickhouse_s3source('foo_s3', aws_access_key_id=env_var("AWS_ACCESS_KEY_ID"), aws_secret_access_key=env_var("AWS_SECRET_ACCESS_KEY")) }}



it is still a bug and need to be fixed.

wei-jiang-dns53 commented 2 months ago

I'm hitting the same problem, setting config like
 vars:
  clickhouse_s3:
   bucket: xx/xx
   path: /**.parquet
   fmt: Parquet
   aws_access_key_id: "{{ env_var('AWS_ACCESS_KEY_ID') }}"
   aws_secret_access_key: "{{ env_var('AWS_SECRET_ACCESS_KEY')}}"
does not work, as described above. Setting it explicitly without env_var works but only when passed directly to the macro.

https://docs.getdbt.com/docs/build/project-variables "jinja" is not supported in vars

ClickHouse / dbt-clickhouse