Interconnecting ClickHouse with OBS

Scenarios

Object Storage Service (OBS) is an object-based storage service designed for handling large volumes of data. It offers secure, highly reliable, and cost-effective storage, and is fully compatible with the Simple Storage Service (S3) protocol. You can seamlessly read and write data in OBS using S3 table functions or S3 table engines within MRS ClickHouse clusters.

This section describes how to use ClickHouse to read and write data files in the OBS file system.

Prerequisites

You have uploaded the table data files to be read by ClickHouse to the OBS file system. For details, see Creating a Parallel File System.

Interconnecting with OBS Using the S3 Table Function

1. Obtain the OBS file path.
   Log in to the OBS console. In the navigation pane, choose Parallel File Systems. On the displayed page, click the name of the target file system. On the Files page, click the file name to obtain the detailed path of the data file uploaded in Prerequisites.

   Figure 1 File path
   

2. Install the ClickHouse client.

   For details, see Installing a Client.

3. Use the ClickHouse client to connect to the ClickHouse server by referring to ClickHouse Client Practices.
4. Run the following command to obtain the data stored in OBS:

   select * from S3(path, [ak, sk,] format, structure, [compression])

   Table 1 Parameter description

   Parameter	Description
   path	OBS file path. For details about how to obtain the path, see 1.
   ak/sk	(Optional) AK/SK with OBS access permission. For details about how to obtain the AK/SK, see How Do I Obtain an Access Key (AK/SK)?
   format	Data file format. Common formats include CSV, Parquet, ORC, and JSON.
   structure	Data file structure. It is usually the column name and data type of a table. For example, the structure can be a string that describes the column name and data type, such as column1 INT, column2 STRING, and column3 DATE.
   compression	(Optional) Compression format used for the data file, for example, gzip, snappy, and bzip2.

   

Interconnecting with OBS Using the S3 Table Engine

1. Install the ClickHouse client.

   For details, see Installing a Client.

2. Use the ClickHouse client to connect to the ClickHouse server by referring to ClickHouse Client Practices.
3. Run the following commands to create a table:

   CREATE TABLE test1_s3 ('name' String, 'age' int)
   ENGINE = S3(path, [ak, sk,] format, [compression])

   Table 2 Parameter description

   Parameter	Description
   test1_s3	Name of the table to be created.
   path	OBS file path. For details about how to obtain the path, see 1.
   ak, sk	(Optional) AK/SK with OBS access permission. For details about how to obtain the AK/SK, see How Do I Obtain an Access Key (AK/SK)?
   format	Data file format. Common formats include CSV, Parquet, ORC, and JSON.
   compression	(Optional) Compression format used for the data file, for example, gzip, snappy, and bzip2.

   

4. Run the following command to query the table:

   select * from test1_s3;