Using a MySQL Client to Access the Column Store Engine

Step 1: Install Lindorm-cli

1. Download the Lindorm-cli installation package for your operating system. The following table provides the download links.

   Note

   You can use the SHA256 checksum in the following table to verify the integrity and authenticity of the downloaded Lindorm-cli installation package.

   Operating system	Download link	SHA256 checksum
   Linux	lindorm-cli for linux You can also run the following command to download the package: wget https://tsdbtools.oss-cn-hangzhou.aliyuncs.com/lindorm-cli-linux-latest.tar.gz	21945c466e2de2c256edd16afdfbb2234301573feb649a673939471a3d882b1a
   Linux-arm64	lindorm-cli for linux-arm64 You can also run the following command to download the package: wget https://tsdbtools.oss-cn-hangzhou.aliyuncs.com/lindorm-cli-linux-arm64-latest.tar.gz	bcae4d8479201d69e4883105d1a7f933fa46d78e19c171e8e1ebfbee28f6d953
   Mac (Intel chip)	lindorm-cli for mac	e82cb1678ea5b263813ccb87fff0149526c36a2654082785efe5a6aaeb4eb24b
   Mac (Arm chip)	lindorm-cli for mac	538d5fef122b416ca2ab63fd7074b77ef25840e9feb8d3490405aae36a537a68
   Windows	lindorm-cli for windows-x64	e61078108a72b85382363e9619b8820aa5245bad60dec79ffd4bf9fea976e280

2. Decompress the Lindorm-cli package.

   For example, on a Linux operating system, run the following command to decompress the package. After the package is decompressed, you can find the lindorm-cli file.

   tar zxvf lindorm-cli-linux-latest.tar.gz

Create a database

1. Run the following statement to create a database named test.

   CREATE DATABASE test;

2. You can view the database list.

   SHOW DATABASES;

   The following result is returned:

   +--------------------+
   | DATABASE           |
   +--------------------+
   | default            |
   | test               |
   | information_schema |
   +--------------------+

3. Run the following statement to use the test database.

   USE test;

Create a table

1. Run the following statement to create a primary key table named sensor in the test database.

   CREATE TABLE sensor (
       device_id VARCHAR,
       region VARCHAR,
       time TIMESTAMP,
       update_date VARCHAR,
       temperature DOUBLE,
       humidity BIGINT,
       PRIMARY KEY(device_id,update_date,time)
     )PARTITION BY LIST(`IDENTITY`(update_date),bucket(256, device_id));

2. Run the following statement to query the created table.

   SHOW TABLES;

   The following result is returned:

   +------------+------------+--------------+
   | TABLE_NAME | TABLE_TYPE | TABLE_SCHEMA |
   +------------+------------+--------------+
   | sensor     | Common     | test         |
   +------------+------------+--------------+

3. You can view the table schema.

   DESCRIBE TABLE sensor;

   The following result is returned:

   +--------------+------------+-------------+-----------+----------------+------------+
   | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | TYPE      | IS_PRIMARY_KEY | SORT_ORDER |
   +--------------+------------+-------------+-----------+----------------+------------+
   | test         | sensor     | device_id   | VARCHAR   | true           | ASC        |
   | test         | sensor     | update_date | VARCHAR   | true           | ASC        |
   | test         | sensor     | time        | TIMESTAMP | true           | ASC        |
   | test         | sensor     | region      | VARCHAR   | false          | none       |
   | test         | sensor     | temperature | DOUBLE    | false          | none       |
   | test         | sensor     | humidity    | BIGINT    | false          | none       |
   +--------------+------------+-------------+-----------+----------------+------------+

4. You can view the partitioning rule of the table.

   SHOW TABLE VARIABLES FROM sensor like 'PARTITION_KEY';

   The following result is returned:

   +---------------+---------------------------------------------------+
   | Variable_name | Value                                             |
   +---------------+---------------------------------------------------+
   | PARTITION_KEY | LIST(identity(update_date),bucket(256,device_id)) |
   +---------------+---------------------------------------------------+

Write data

• Write data in standard mode using INSERT INTO.

  INSERT INTO sensor (device_id, region, time, update_date, temperature, humidity) VALUES('F07A1260','north-cn',1716282726000,'2024-05-21',12.1,45);
  INSERT INTO sensor (device_id, region, time, update_date, temperature, humidity) VALUES('F07A1260','north-cn',1716282736000,'2024-05-21',13.2,47);
  INSERT INTO sensor (device_id, region, time, update_date, temperature, humidity) VALUES('F07A1260','north-cn',1716282746000,'2024-05-21',10.6,46);
  INSERT INTO sensor (device_id, region, time, update_date, temperature, humidity) VALUES('F07A1261','south-cn',1716282726000,'2024-05-21',18.1,44);
  INSERT INTO sensor (device_id, region, time, update_date, temperature, humidity) VALUES('F07A1261','south-cn',1716282736000,'2024-05-21',19.7,44);

• Write data in JSON package mode.

  INSERT INTO sensor (_l_btc_json_row) VALUES ('{ "device_id":"F07A1260", "region":"north-cn", "time":1716282726000, "update_date":"2024-05-21", "temperature":12.1, "humidity":45 }');
  INSERT INTO sensor (_l_btc_json_row) VALUES ('{ "device_id":"F07A1260", "region":"north-cn", "time":1716282736000, "update_date":"2024-05-21", "temperature":13.2, "humidity":47 }');
  INSERT INTO sensor (_l_btc_json_row) VALUES ('{ "device_id":"F07A1260", "region":"north-cn", "time":1716282746000, "update_date":"2024-05-21", "temperature":10.6, "humidity":46 }');
  INSERT INTO sensor (_l_btc_json_row) VALUES ('{ "device_id":"F07A1261", "region":"south-cn", "time":1716282726000, "update_date":"2024-05-21", "temperature":18.1, "humidity":44 }');
  INSERT INTO sensor (_l_btc_json_row) VALUES ('{ "device_id":"F07A1261", "region":"south-cn", "time":1716282736000, "update_date":"2024-05-21", "temperature":19.7, "humidity":44 }');

Query data

SELECT * FROM sensor WHERE device_id = 'F07A1260';

The following result is returned:

+-----------+-------------+---------------------+----------+-------------+----------+
| device_id | update_date | time                | region   | temperature | humidity |
+-----------+-------------+---------------------+----------+-------------+----------+
| F07A1260  | 2024-05-21  | 2024-05-21 17:12:06 | north-cn | 12.1        | 45       |
| F07A1260  | 2024-05-21  | 2024-05-21 17:12:16 | north-cn | 13.2        | 47       |
| F07A1260  | 2024-05-21  | 2024-05-21 17:12:26 | north-cn | 10.6        | 46       |
+-----------+-------------+---------------------+----------+-------------+----------+

Delete the table

DROP TABLE sensor;