Creating Tables and Adding Data

26 January 2023 · Read time 3 min

Overview - Creating Tables

This section is for first time users that have just learned how to create a ClickHouse cluster, and now want to add tables and data.

The Altinity.Cloud Manager (ACM) screens used on this page are:

• ACM home page ❯ Clusters
• ACM: Cluster (name) > Explore > Query tab
• ACM: Cluster (name) > Explore > Schema tab
• ACM: Cluster (name) > Explore > Schema tab > Table (name)
• ACM: Cluster (name) > Explore > Schema tab > Table (name) > Table Details > Sample Rows

Creating Tables

The next step after creating a new ClickHouse cluster is to create tables. After completing this example, two empty tables are created:

• events_local
• events

Prerequisite

• Open the UI screen: ACM: Cluster (name) > Explore > Schema tab

To create two tables in your blank cluster by using a SQL Query:

1. From the domain menu, select your domain (ie. your.domain). https://acm.altinity.cloud/clusters

2. In your cluster (ie. example-cluster), select EXPLORE. Confirm that the Query tab is selected.

3. To create the first table called events_local table, copy and paste in the following SQL query then EXECUTE:

   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);
   

4. To create the second table called events, copy and paste in the following SQL query then EXECUTE:

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

5. Below the EXECUTE button, the following information displays after running each SQL query:

   example-cluster.your-domain.altinity.cloud:8443 (query time: 0.335s)

   chi-example-cluster-example-cluster-0-0	9000	0		1	0
   chi-example-cluster-example-cluster-0-1	9000	0		0	0
   

6. In the Schema tab, confirm that the two tables events_local and events are present.

Adding Data

In the Query tab, SQL commands are used to add data to your ClickHouse tables and to verify the additions.

Prerequisite

• Open the UI screen: ACM: Cluster (name) > Explore > Schema tab

To add data to the events_local table:

1. Copy and paste the following to the cluster Query field then Execute:

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

2. Verify that the data has been added to events_local by running the query:

   SELECT * FROM events;
   

3. The following response appears below the EXECUTE button.

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

Viewing Schema and Data

The Schema tab contains a list of your ClickHouse tables and data in your cluster.

Prerequisite

• Open the UI screen: ACM: Cluster (name) > Explore > Schema tab
• UI screen: ACM: Cluster (name) > Explore > Schema tab > Table (name) > Table Details > Sample Rows
  

To view the Adding:

1. Select the Schema tab. Two tables are listed, events_local and events.

2. Within the Schema tab, select the Table link called events_local.

3. In the Table Details dialog box, select the tab Sample Rows. The following information appears.

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

4. Select DONE to close the Table Details window.