Connect Remote Clients

Now that we’ve shown how to create a cluster and use ClickHouse SQL queries into your new cluster, let’s connect to it remotely.

For the following, we’re going to be using the clickhouse-client program, but the same process will help you gain access from your favorite client.

Full instructions for installing ClickHouse can be found on the ClickHouse Installation page. We’ll keep this simple and assume you’re using a Linux environment like Ubuntu. For this example, we set up a virtual machine running Ubuntu 20.04.

First, we need to know our connection details for our Altinity.Cloud ClickHouse cluster. To view your connection details:

1. From the Clusters View, select Access Point for the cluster to manage.

2. From here, you can copy and paste the settings for the ClickHouse client from your cluster’s Access Point. For example:

   clickhouse-client -h yourdataset.yourcluster.altinity.cloud --port 9440 -s --user=admin --password
   

ClickHouse Client

The ClickHouse Client is a command line based program that will be familiar to SQL based users.

Setting Up ClickHouse Client in Linux

If you’ve already set up ClickHouse client, then you can skip this step. Instructions for installing ClickHouse can be found at ClickHouse.Tech - we’ll just be focusing on the clickhouse-client as a quick example.

For those who need quick instructions on how to install ClickHouse client in their deb based Linux environment (like Ubuntu), use the following:

sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4

echo "deb https://repo.clickhouse.tech/deb/stable/ main/" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

sudo apt-get install -y clickhouse-client

Connect With ClickHouse Client

If your ClickHouse client is ready, then you can copy and paste your connection settings into your favorite terminal program, and you’ll be connected.

ClickHouse Python Driver

Users who prefer Python can use the clickhouse-driver to connect through Python. These instructions are very minimal, and are intended just to get you working in Python with your new Altinity.Cloud cluster.

These instructions are in the bash shell and require the following be installed in your environment:

• Python 3.7 and above

• The Python module venv

• git

• IMPORTANT NOTE: Install the clickhouse-driver 0.2.0 or above which has support for Server Name Indication (SNI) when using TLS communications.

To connect with the Python clickhouse-driver:

1. (Optional) Setup your local environment. For example:

   python3 -m venv test 
   . test/bin/activate
   

2. Install the driver with pip3:

   pip3 install clickhouse-driver
   

3. Start Python.

4. Add the client and connection details. The Access Point provides the necessary information to link directly to your cluster.

   

   Import the clickhouse_driver client and enter the connection settings:

   from clickhouse_driver import Client
   client = Client('<HOSTNAME>', user='admin', password=<PASSWORD>, port=9440, secure='y', verify=False)
   

5. Run client.execute and submit your query. Let’s just look at the tables from within Python:

   >>> client.execute('SHOW TABLES in default')
   [('events',), ('events_local',)]
   

6. You can perform selects and inserts as you need. For example, continuing from our Your First Queries using Cluster Explore.

   >>> result = client.execute('SELECT * FROM default.events')
   >>> print(result)
   [(datetime.date(2020, 11, 23), 1, 13, 'Example')]
   

For more information see the article ClickHouse And Python: Getting To Know The ClickHouse-driver Client.