Use Managed Service for ClickHouse® API
To get started with the service using DoubleCloud API:
Before you start
Your primary tool to interact with the DoubleCloud is the console. We need to set it up and then configure it before moving on.
-
Go to the console.
-
Log in to DoubleCloud if you already have an account, or create one if you are opening the console for the first time.
Note
This tutorial shows how to connect to a cluster with a native CLI client and Docker
-
Install the software:
DockerDEB-basedRPM-based-
Open your terminal.
-
(Optional) Start Docker
sudo service docker start
-
Pull the clickhouse-client
docker pull clickhouse/clickhouse-client
-
Open your terminal.
-
Connect to the ClickHouse® official DEB repository
sudo apt update && sudo apt install -y apt-transport-https ca-certificates dirmngr && \ sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754 && \ echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \ /etc/apt/sources.list.d/clickhouse.list
-
Refresh the package list and install the clickhouse-client
sudo apt update && sudo apt install -y clickhouse-client
-
Open your terminal.
-
Connect to a ClickHouse® official RPM repository
sudo yum install -y yum-utils sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
-
Install the clickhouse-client
sudo yum install -y clickhouse-client
Warning
If you run a RedHat 7-based Linux distribution, including Cent OS 7, Oracle Linux 7 and others, you need to download and install trusted certificates and manually add the path to them in the clickhouse-client configuration file as follows:
-
Install the
root
certificate:curl https://letsencrypt.org/certs/isrg-root-x2-cross-signed.pem > \ /etc/pki/ca-trust/source/anchors/isrg-root-x2-cross-signed.pem
-
Install the
intermediate
certificate:curl https://letsencrypt.org/certs/lets-encrypt-r3-cross-signed.pem > \ /etc/pki/ca-trust/source/anchors/lets-encrypt-r3-cross-signed.pem
-
Update the list of trusted certificates:
sudo update-ca-trust
-
Locate your clickhouse-client configuration file (by default, you can find it at
/etc/clickhouse-client/config.xml
) and add the path to the certificates into the<openSSL>
section:<client> <!-- Used for connection to server's secure tcp port --> <loadDefaultCAFile>true</loadDefaultCAFile> <cacheSessions>true</cacheSessions> <disableProtocols>sslv2,sslv3</disableProtocols> <preferServerCiphers>true</preferServerCiphers> <caConfig>/etc/ssl/certs/ca-bundle.crt</caConfig> <!-- Use for self-signed: <verificationMode>none</verificationMode> --> <invalidCertificateHandler> <!-- Use for self-signed: <name>AcceptCertificateHandler</name> --> <name>RejectCertificateHandler</name> </invalidCertificateHandler> </client>
-
-
Create a service account:
A service account is a specific kind of account the allows you to use a third-party service or application to interact with DoubleCloud via the API. When authenticating as a service account, such application has the access to the service determined by the service account's user role.
- Go to the Service accounts tab of the Members page in the console. You'll see the following dialog:
-
Name your service account.
-
From the drop-down menu, select the Admin user role - we will need both read and write access.
-
Click Submit. You'll see your new service account appear on the list.
-
Issue an API key for your service account:
-
Go to the Service accounts tab of the Members page in the console.
-
Open the information page of the service account for which you want to create an API key.
-
Under API keys, click Create key to create you account's first Secret key. You'll see the following dialog:
-
Click Download file with keys. You'll use it to authenticate API requests.
-
Create your cluster
ClickHouse® clusters are one or more database hosts.
To create a ClickHouse® cluster, use the ClusterService
create method. The required parameters to create a functional cluster:
-
project_id
- the ID of your project. You can get this value on your project's information page. -
cloud_type
-aws
. -
region_id
- for this quickstart, specifyeu-central-1
. -
name
- your cluster's name,tutorial-cluster
. -
resources
- specify the following from the doublecloud.clickhouse.v1.ClusterResources model:
import json
import logging
from google.protobuf.wrappers_pb2 import Int64Value
import doublecloud
from doublecloud.clickhouse.v1.cluster_pb2 import ClusterResources
from doublecloud.clickhouse.v1.cluster_service_pb2 import CreateClusterRequest
from doublecloud.clickhouse.v1.cluster_service_pb2_grpc import ClusterServiceStub
def create_cluster(sdk, project_id, region_id, name, network_id):
cluster_service = sdk.client(ClusterServiceStub)
operation = cluster_service.Create(
CreateClusterRequest(
project_id=project_id,
cloud_type="aws",
region_id="eu-central-1",
name="tutorial-cluster",
resources=ClusterResources(
clickhouse=ClusterResources.Clickhouse(
resource_preset_id="s1-c2-m4",
disk_size=Int64Value(value=34359738368),
replica_count=Int64Value(value=1),
shard_count=Int64Value(value=1),
)
),
network_id=network_id,
)
)
logging.info("Creating initiated")
return operation
Connect to the cluster and create a database
-
Select Clusters from the list of services on the left.
-
Select the name of your cluster to open its information page. By default, you will see the Overview tab.
-
Under Connection strings, find the Native interface string and click Copy.
-
Open your terminal and run a command to connect to your cluster:
DockerNative clickhouse-clientdocker run --network host --rm -it clickhouse/<Native interface connection string>
The complete Docker command structuredocker run --network host --rm -it \ clickhouse/clickhouse-client \ --host <FQDN of your cluster> \ --secure \ --user <cluster user name> \ --password <cluster user password> \ --port 9440
<Native interface connection string>
-
After you have connected to the cluster, run the CREATE
CREATE DATABASE IF NOT EXISTS first_database
After that, you can check the result by executing the following command:
SHOW DATABASES
The output will contain the name of your recently created database:
┌─name───────────────┐ │ INFORMATION_SCHEMA │ │ _system │ │ default │ │ first_database │ │ information_schema │ │ system │ └────────────────────┘
-
Now, it's time to add a table to the
first_database
on your cluster. The table will have the following structure adjusted to the dataset that we'll upload later:CREATE TABLE first_database.hits ON CLUSTER default ( Hit_ID Int32, Date Date, Time_Spent Float32, Cookie_Enabled Int32, Region_ID Int32, Gender String, Browser String, Traffic_Source String, Technology String ) ENGINE = ReplicatedMergeTree ORDER BY (Hit_ID, Date)
Check if your query was successful and the table exists in the database:
SHOW TABLES FROM first_database
The readout will be the following:
┌─name─┐ │ hits │ └──────┘
Tip
Alternatively, you can use a GUI-based IDE of your choice to connect to a cluster and create a database.
Upload data
-
Now you can open another terminal instance or exit from your cluster by typing the
exit
command. -
Run the following query to fetch the data from our S3 bucket and combine it with the INSERT
curl https://doublecloud-docs.s3.eu-central-1.amazonaws.com/data-sets/hits_sample.csv | docker run --network host --rm -it clickhouse/<Native interface connection string> --query="INSERT INTO first_database.hits FORMAT CSVWithNames" --format_csv_delimiter=";"
The complete Docker command structurecurl https://doublecloud-docs.s3.eu-central-1.amazonaws.com/data-sets/hits_sample.csv | docker run --network host --rm -i clickhouse/clickhouse-client --host <FQDN of your cluster> --port 9440 --secure --user <your cluster username> --password <your cluster password> --query="INSERT INTO first_database.hits FORMAT CSVWithNames" --format_csv_delimiter=";"
-
If the query was successful, perform the
SELECT
query to see the loaded data:docker run --network host --rm -it clickhouse/<Native interface connection string> --query="SELECT * FROM first_database.hits LIMIT 20"
The complete Docker command structuredocker run --network host --rm -i clickhouse/clickhouse-client --host <FQDN of your cluster> --port 9440 --secure --user <your cluster username> --password <your cluster password> --query="SELECT * FROM first_database.hits LIMIT 20"
-
Run the following query that will fetch the data from our S3 bucket and combine it with the INSERT
<Native interface connection string> --query="INSERT INTO first_database.hits FORMAT CSVWithNames"
-
If the query was successful, perform the
SELECT
query to see the loaded data:<Native interface connection string> --query="SELECT * FROM first_database.hits LIMIT 20"
You should see your data in the table:
67112 2016-01-10 388.93976 0 184 Male Firefox Direct PC (Mac)
65411 2016-01-30 267.13525 0 229 Female Firefox Search engine PC (Windows)
57240 2016-02-05 321.99017 0 229 Female Firefox Search engine PC (Windows)
34703 2016-02-11 300.81143 1 34 Male Safari Recommendations Tablet (Android)
55134 2016-02-17 324.48718 0 2 Male Firefox Direct Other
46247 2016-02-23 307.71362 1 2 Male Safari Recommendations Smartphone (Android)
15422 2016-02-29 118.30818 1 2 Female Safari Recommendations Smartphone (Android)
36341 2016-03-06 330.35165 1 51 Male Chrome Social network PC (Windows)
54562 2016-03-12 325.20392 0 51 Female Chrome Search engine Smartphone (I0S)
63833 2016-03-18 316.09775 1 51 Female Chrome Search engine Smartphone (I0S)
43941 2016-03-24 263.73654 1 51 Male Firefox Search engine PC (Windows)
38583 2016-03-30 363.8421 0 51 Female Safari Internal Smartphone (Android)
65839 2016-04-05 284.4762 1 51 Male Firefox Direct PC (Windows)
38024 2016-04-11 305.58286 1 51 Male Edge Search engine PC (Windows)
30573 2016-04-17 170.59955 0 52 Male Edge Search engine PC (Windows)
59083 2016-04-23 512.53 0 52 Male Edge Search engine PC (Windows)
40305 2016-04-29 324.144 0 52 Female Firefox Direct Other
Now you have a Managed ClickHouse® cluster with data in it. See the links below to continue exploring!
Download the complete code example
You can download the full code listing for all the steps above from our Python SDK GitHub repository