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

• You have prepared an IAM user who has the permission to create MRS clusters.

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.	Europe-Dublin
   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.	root/admin
   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

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 1 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 2 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 3 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 4 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 5 Viewing data in the test010_dir table
   

10. Delete the created database table.

    drop database test001 on cluster default_cluster no delay;