Search code examples
dockerubuntuclickhousesentryclickhouse-client

How to execute a script in Clickhouse from Linux CLI?

This may seem to be an easy question, but I did not find an answer, despite searching for it for a while and my attempts did not succeed.

I'm running Sentry via Docker and it uses Clickhouse. I can connect to the container where clickhouse is located via

docker exec -it $(docker ps -aqf 'name=sentry-self-hosted_clickhouse_1') bash

And there I can connect to clickhouse-client and execute commands there. For the sake of simplicity, let's assume that the command I am to run is as simple as

SELECT 1 + 2;

I have tested it and it correctly returned 3, just as expected, as the attached image also shows:

Sample output resulting in 1+2=3

Now, what I would like to do is to run this script from outside the Docker container and output the results in the terminal (my actual goal is to create a programmatic backup system for Sentry, of which Clickhouse backup is also a part of, but asking for that would be too vague and I decided to instead ask about my main obstacle, that is, executing a script from outside of clickhouse-client). This is how I can connect to Clickhouse from outside the container:

Surely I can connect to the Docker container via clickhouse-client directly too, like

docker exec -it $(docker ps -aqf 'name=sentry-self-hosted_clickhouse_1') clickhouse-client -u <myusername> --password <mypassword>

and so my stuff afterwards. But, what I would like to have instead is something like this (of course, the command is incorrect):

docker exec -it $(docker ps -aqf 'name=sentry-self-hosted_clickhouse_1') clickhouse-client -u <myusername> --password <mypassword> -e "SELECT 1 + 2;"

(this -e was inspired of how MySQL allows me to execute commands inline like this)

or even better:

cat myinput.sql | docker exec $(docker ps -aqf 'name=sentry-self-hosted_clickhouse_1') clickhouse-client -u <myusername> --password <mypassword> > myoutput.sql

How can I achieve this? (I have searched for possible solutions, without much luck, either I am missing something obvious, or this is not well-documented)

Solution

  • To run a query passed as argument:

    $ docker compose exec clickhouse clickhouse-client -q 'select 1 + 2'
   ┌─plus(1, 2)─┐
1. │          3 │
   └────────────┘

    To run a query piped in:

    $ echo 'select 1 + 2' | docker compose exec -T clickhouse clickhouse-client
3

    These work with my Docker Compose setup. To run these with your plain Docker setup, the following should work:

    $ docker exec -it $(docker ps -aqf 'name=sentry-self-hosted_clickhouse_1') clickhouse-client -u <myusername> --password <mypassword> -q 'select 1 + 2'

    and

    $ echo 'select 1 + 2' | docker exec -i $(docker ps -aqf 'name=sentry-self-hosted_clickhouse_1') clickhouse-client -u <myusername> --password <mypassword>

    (Note I removed -t and only kept -i, see this.)