· clickhouse til

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}"

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}"
Mark	super_secret_value

Super simple, but super handy!

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket