Connect Streamlit to ClickHouse

Introduction

This guide connects a Streamlit app to ClickHouse Cloud using st.connection, Secrets management, and the ClickHouse Connect SQLAlchemy dialect. You will create a small table of people and their pets, then display the results in your app.

The connection below uses ClickHouse Cloud over HTTPS on port 8443. For a self-managed ClickHouse server, use that server's HTTP port and set secure to match whether the server uses TLS. To connect to ClickHouse Managed Postgres, follow that guide.

Prerequisites

• A ClickHouse Cloud account.
• A Python environment with Streamlit installed.

Create a requirements.txt file in your app's directory:

streamlit>=1.28
clickhouse-connect[sqlalchemy]
sqlalchemy>=2.0.0

Install the dependencies:

python -m pip install -r requirements.txt

Create a ClickHouse service and add sample data

If you already have a ClickHouse Cloud service, use it for the following steps.

1. Sign in to the ClickHouse Cloud console and create a ClickHouse service. To create the service from the command line, follow the ClickHouse Cloud quick start.

2. Open your service's SQL Console and select the default database.

3. Run these statements to create a table and add three rows:

   SQL

   CREATE TABLE mytable (
       id UInt8,
       name String,
       pet LowCardinality(String)
   )
   ENGINE = MergeTree
   ORDER BY id;
   
   INSERT INTO mytable (id, name, pet)
   VALUES (1, 'Mary', 'dog'), (2, 'John', 'cat'), (3, 'Robert', 'bird');
   

Add connection details to your local app secrets

1. Open Connect for your ClickHouse service. Copy the hostname, HTTPS port, username, and password.

2. In your app's directory, create .streamlit/secrets.toml:

   TOML

   # .streamlit/secrets.toml
   
   [connections.clickhouse]
   dialect = "clickhousedb"
   host = "YOUR_HOST.clickhouse.cloud"
   port = 8443
   database = "default"
   username = "default"
   password = "YOUR_PASSWORD"
   
   [connections.clickhouse.create_engine_kwargs.connect_args]
   secure = true
   

3. Replace the example host and credentials with your service's connection details. Enter just the hostname in host, without https:// or a port. ClickHouse Connect uses HTTPS when secure = true and verifies the server certificate by default.

ClickHouse Cloud allows connections from anywhere until you restrict the service's IP access list. If your service allows only specific addresses, add the public IP address of the computer running the app.

Write your Streamlit app

Save this code as streamlit_app.py:

# streamlit_app.py

import streamlit as st

# Initialize connection.
conn = st.connection("clickhouse", type="sql")

# Perform query.
df = conn.query("SELECT name, pet FROM mytable ORDER BY id", ttl="10m")

# Print results.
for row in df.itertuples():
    st.write(f"{row.name} has a :{row.pet}:")

st.connection reads the connection details from your secrets file. The query result is cached for ten minutes. Set ttl=0 to fetch fresh results on every run, or see Caching for more options.

Run your app:

streamlit run streamlit_app.py

With the sample data above, your app should look like this:

Connect from Streamlit Community Cloud

To deploy the same app on Community Cloud:

• Include streamlit_app.py and requirements.txt in your GitHub repository.
• Add your secrets to your Community Cloud app by copying the contents of .streamlit/secrets.toml into its secrets settings.
• If the service allows only specific addresses, add Community Cloud's outbound IP addresses to its IP access list.