Help Center/ MapReduce Service/ Getting Started/ Creating and Using a ClickHouse Cluster for Columnar Store
Updated on 2024-08-28 GMT+08:00

Creating and Using a ClickHouse Cluster for Columnar Store

Scenario

This topic helps you create a ClickHouse cluster from scratch and create and query a ClickHouse table through the cluster client.

ClickHouse is an open-source columnar database oriented to online analysis and processing. It is independent of the Hadoop big data system and features ultimate compression rate and fast query performance.

Procedure

Before you start, complete operations described in Preparations. Then, follow these steps:

  1. Creating an MRS Cluster: Create a ClickHouse cluster of MRS 3.2.0-LTS.1.
  2. Installing the Cluster Client: Download and install the MRS cluster client.
  3. Creating a Table through the ClickHouse Client: Create a table on the ClickHouse client and insert data into the table.

Preparations

Step 1: Creating an MRS Cluster

  1. Go to the Buy Cluster page.
  2. Search for MapReduce Service in the service list and enter the MRS console.
  3. Click Buy Cluster. The Quick Config tab is displayed.
  4. Configure the cluster as you need. In this example, a pay-per-use MRS 3.2.0-LTS.1 cluster will be created. For more details about how to configure the parameters, see Quickly Creating a Cluster.

    Table 1 MRS cluster parameters

    Parameter

    Description

    Value

    Billing Mode

    Billing mode of the cluster you want to create. MRS provides two billing modes: yearly/monthly and pay-per-use.

    Pay-per-use is a postpaid billing mode. You pay as you go and pay for what you use. The cluster usage is calculated by the second but billed every hour.

    Pay-per-use

    Region

    Region where the MRS resources to be requested belong.

    MRS clusters in different regions cannot communicate with each other over an intranet. For lower network latency and quick resource access, select the nearest region.

    CN-Hong Kong

    Cluster Name

    Name of the MRS cluster you want to create.

    mrs_demo

    Cluster Type

    A range of clusters that accommodate diverse big data demands. You can select a Custom cluster to run a wide range of analytics components supported by MRS.

    Custom

    Version Type

    Service type of the MRS

    LTS

    Cluster Version

    Version of the MRS cluster. Supported open-source components and their functions vary depending on the cluster version. You are advised to select the latest version.

    MRS 3.2.0-LTS.1

    Component

    Cluster templates containing preset opensource components you will need for your business.

    ClickHouse Cluster

    AZ

    Available AZ associated with the cluster region.

    AZ1

    VPC

    VPC where you want to create the cluster. You can click View VPC to view the name and ID. If no VPC is available, create one.

    vpc-default

    Subnet

    Subnet where your cluster belongs. You can access the VPC management console to view the names and IDs of existing subnets in the VPC. If no subnet is created under the VPC, click Create Subnet to create one.

    subnet-default

    Cluster Node

    Cluster node details.

    Default value

    Kerberos Authentication

    Whether Kerberos authentication is enabled.

    Disabled

    Username

    Username for logging in to the cluster management page and the ECS node.

    admin/root

    Password

    User password for logging in to the cluster management page and the ECS node.

    -

    Confirm Password

    Enter the user password again.

    -

    Enterprise Project

    Enterprise project to which the cluster belongs.

    default

    Secure Communications

    Select the check box to agree to use the access control rules.

    Selected

    Figure 1 Buying a ClickHouse cluster

  5. Click Buy Now. A page is displayed showing that the task has been submitted.
  6. Click Back to Cluster List. You can view the status of the newly created cluster on the Active Clusters page.

    Wait for the cluster creation to complete. The initial status of the cluster is Starting. After the cluster is created, the cluster status becomes Running.

Step 2: Installing the Cluster Client

You need to install a cluster client to connect to component services in the cluster and submit jobs.

You can install the client on a node in or outside the cluster. This topic installs the client on the Master1 node as an example.

  1. Click the MRS cluster name in the cluster list to go to the dashboard page.
  2. Click Access Manager next to MRS Manager. In the displayed dialog box, select EIP and configure the EIP information.

    For the first access, click Manage EIPs to purchase an EIP on the EIP console. Go back to the Access MRS Manager dialog box, refresh the EIP list, and select the EIP.

  3. Select the confirmation check box and click OK to log in to the FusionInsight Manager of the cluster.

    The username for logging in to FusionInsight Manager is admin, and the password is the one configured during cluster purchase.

  4. On the displayed Homepage page, click next to the cluster name and click Download Client to download the cluster client.

    Figure 2 Downloading the client

    In the Download Cluster Client dialog box, set the following parameters:

    • Set Select Client Type to Complete Client.
    • Retain the default value for Platform Type, for example, x86_64.
    • Retain the default path for Save to Path. The generated file will be saved in the /tmp/FusionInsight-Client directory on the active OMS node of the cluster.
    Figure 3 Downloading the cluster client

    Click OK and wait until the client software is generated.

  5. Go back to the MRS console and click the cluster name in the cluster list. Go to the Nodes tab, click the name of the node that contains master1. In the upper right corner of the ECS details page, click Remote Login to log in to the Master1 node.

    Figure 4 Checking the Master1 node

  6. Log in to the Master1 node as user root. The password is the one you set for the root user during cluster purchase.
  7. Switch to the directory where the client software package is stored and decompress the package.

    cd /tmp/FusionInsight-Client/

    tar -xvf FusionInsight_Cluster_1_Services_Client.tar

    tar -xvf FusionInsight_Cluster_1_Services_ClientConfig.tar

  8. Go to the directory where the installation package is stored and install the client.

    cd FusionInsight_Cluster_1_Services_ClientConfig

    Install the client to a specified directory (an absolute path), for example, /opt/client.

    ./install.sh /opt/client

    ...
    ... component client is installed successfully
    ...

    A client installation directory will be automatically created if it does not exist. If there is such directory, it must be empty. The specified client installation directory can contain only uppercase letters, lowercase letters, digits, and underscores (_), and cannot contain space.

Step 3: Creating a Table Through the ClickHouse Client

  1. Log in to the node (Master1) where the MRS client is deployed as user root.
  2. Switch to the client installation directory and configure environment variables.

    cd /opt/client

    source bigdata_env

  3. Run the clickhouse client command to connect to the ClickHouse server.

    clickhouse client --host IP address of the ClickHouseServer instance --port 9000 --user Username --password

    • To obtain the IP address of the ClickHouseServer instance, log in to FusionInsight Manager of the cluster and choose Cluster > Services > ClickHouse > Instances.
    • Clusters with Kerberos authentication disabled use non-SSL connections by default. The default connection port is 9000. To view the port number, log in to FusionInsight Manager of the cluster, choose Cluster > Services > ClickHouse > Configurations, and search for tcp_port.
    • If the --user and --password parameters are not specified, the default user is used for logging in to the ClickHouse client by default. If you want to specify the username and password, run the create user SQL statement on the ClickHouse client to create a ClickHouse user.

  4. Create the test001 database.

    create database test001 on cluster default_cluster;

  5. Create the replication table test010.

    CREATE TABLE test001.test010 on cluster default_cluster
    (
    `EventDate` DateTime,
    `CounterID` UInt32,
    `UserID` UInt32,
    `ver` UInt16
    )
    ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/test3', '{replica}', ver)
    ORDER BY (CounterID, EventDate, intHash32(UserID));

  6. Create a replication table test010 and insert data into the table.

    insert into test001.test010 values('2020-01-29',111,111,111);

  7. View data in the test010 table.

    select * from test001.test010;
    Figure 5 Viewing data in the test010 table.

  8. Create a distributed table test001.test010_dir and insert data into the table.

    create table test001.test010_dir ON CLUSTER default_cluster as  test001.test010 ENGINE = Distributed(default_cluster, test001, test010, rand());
    insert into test001.test010_dir values('2020-01-29',111,111,111);

  9. Query the distributed table.

    select * from test001.test010_dir;
    Figure 6 Viewing data in the test010_dir table

  10. Delete the created database table.

    drop database test001 on cluster default_cluster no delay;

Related Information

For details about how to manage ClickHouse permissions, import data from RDS for MySQL, OBS, HDFS, and GaussDB(DWS) to ClickHouse tables, manage multiple ClickHouse tenants, and access ClickHouse through ELB, see Using ClickHouse.