23 Feb 2024

Using environment variables in ClickHouse queries

For quite some time I’ve been wondering how to get access to an environment variable in a ClickHouse Local and finally today I have a solution, which we’ll explore in this blog post.

My reason for wanting to do this is so that I can pass through a ClickHouse Cloud password to use in a remoteSecure function call. I wanted to do this as part of a blog post I wrote showing how to do Hybrid Query Execution with ClickHouse.

The hook that lets us do this is named parameters, which I spotted at the bottom of the ClickHouse CLI help:

In addition, --param_name=value can be specified for substitution of parameters for parametrized queries.

So if we wanted to pass in a literal value we could do this:

./clickhouse --param_variable="Mark" -q "SELECT {variable:String}"

Output

Mark

And then it’s only a small extra step to pass in an environment variable:

export MY_ENV_VAR="super_secret_value"
./clickhouse \
  --param_variable="Mark" \
  --param_variable2=${MY_ENV_VAR} \
  -q "SELECT {variable:String}, {variable2:String}"

Output

Mark	super_secret_value

Super simple, but super handy!

About the author

I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.