Creating a ClickHouse Database

How to use the Altinity Cloud Manager (ACM) to create a ClickHouse database on a Google Kubernetes (GKE) cluster.

7 May 2023 · Read time 3 min

Introduction

In this section you will create a ClickHouse database and tables on your Google GKE-cluster using the ACM. You will then use your cluster’s Explore menu in the ACM to run the database-creation scripts and queries. Finally, you will use the clickhouse-client command line tool from your local terminal using the Connection Details string to test data-retrieval queries.

Creating a ClickHouse Database

The following steps you will use the clusters EXPLORE menu in the Query tab.

Figure 1 - Using the Cluster > EXPLORE > Query tab to create and query ClickHouse databases and tables.

To create a new database on your Altinity.Cloud Anywhere cluster from the ACM:

Login to the ACM and select Clusters, then select EXPLORE on your cluster.
In the Query text box, enter the following CREATE TABLE SQL query:

CREATE TABLE IF NOT EXISTS events_local ON CLUSTER '{cluster}' (
    event_date  Date,
    event_type  Int32,
    article_id  Int32,
    title       String
) ENGINE = ReplicatedMergeTree('/clickhouse/{cluster}/tables/{shard}/{database}/{table}', '{replica}')
    PARTITION BY toYYYYMM(event_date)
    ORDER BY (event_type, article_id);

Create a second table:

CREATE TABLE events ON CLUSTER '{cluster}' AS events_local
   ENGINE = Distributed('{cluster}', default, events_local, rand())

Add some data with this query:

INSERT INTO events VALUES(today(), 1, 13, 'Example');

List the data you just entered:

SELECT * FROM events;

# Response
test-anywhere-6.johndoetest-a123.altinity.cloud:8443 (query time: 0.196s)
┌─event_date─┬─event_type─┬─article_id─┬─title───┐
│ 2023-03-24 │          1 │         13 │ Example │
└────────────┴────────────┴────────────┴─────────┘

Show all the tables:

show tables

# Response
test-anywhere-6.johndoetest-a123.altinity.cloud:8443 (query time: 0.275s)
┌─name─────────┐
│ events       │
│ events_local │
└──────────────┘

Testing ClickHouse on your local terminal

This section shows you how to use your local computer terminal to login to your Clickhouse Cluster that you created in the Altinity Cloud Manager.

Prerequisite

clickhouse-client (Installation instructions)

Connection String

The connection string comes from your cluster (Example: test-gcp-anyw) Connection Details link. The Copy/Paste for client connections string highlighted in red in Figure 2 is used in your terminal (you supply the password; Example: adminpassword)

Figure 2 - Using the Cluster > EXPLORE > Query tab to create and query ClickHouse databases and tables.

Find your pod name:

kubectl -n altinity-cloud-managed-clickhouse get all

# Response
NAME                                               READY   STATUS    RESTARTS        AGE
pod/chi-test-anywhere-6-johndoe-anywhere-6-0-0-0   2/2     Running   8 (3h25m ago)   2d17h

On your command line terminal, login to that pod using the name you got from step 1:

kubectl -n altinity-cloud-managed-clickhouse exec -it pod/chi-test-anywhere-6-johndoe-anywhere-6-0-0-0 -- bash

# Response
Defaulted container "clickhouse-pod" out of: clickhouse-pod, clickhouse-backup
clickhouse@chi-test-anywhere-6-johndoe-anywhere-6-0-0-0:/$

clickhouse@chi-test-anywhere-6-johndoe-anywhere-6-0-0-0:/$ 
clickhouse@chi-test-anywhere-6-johndoe-anywhere-6-0-0-0:/$ clickhouse-client

# Response
<jemalloc>: MADV_DONTNEED does not work (memset will be used instead)
<jemalloc>: (This is the expected behaviour if you are running under QEMU)
ClickHouse client version 22.8.13.21.altinitystable (altinity build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.8.13 revision 54460.

test-anywhere-6 :)

Run a show tables sql command:

test-anywhere-6 :) show tables

# Response

SHOW TABLES

Query id: da01133d-0130-4b98-9090-4ebc6fa4b568

┌─name─────────┐
│ events       │
│ events_local │
└──────────────┘

2 rows in set. Elapsed: 0.013 sec.

Run SQL query to show data in the events table:

test-anywhere-6 :) SELECT * FROM events;

# Response

SELECT * 
FROM events

Query id: 00fef876-e9b0-44b1-b768-9e662eda0483

┌─event_date─┬─event_type─┬─article_id─┬─title───┐
│ 2023-03-24 │          1 │         13 │ Example │
└────────────┴────────────┴────────────┴─────────┘

1 row in set. Elapsed: 0.023 sec. 

test-anywhere-6 :)

Review the following database creation and query instructions:

Feedback

Was this page helpful?

Glad to hear it!

Sorry to hear that. We'll track this issue and see how we can improve.

Last modified 2023.05.11: Added Google GKE Installation instructions