Wide table engine - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL provides the wide table engine that is compatible with the capabilities of the open source columnar database ClickHouse. This topic describes the features of the wide table engine, how to enable the wide table engine feature, and how to use the wide table engine.

Important

The wide table engine feature is in invitational preview. To enable the feature, submit a ticket.

What is the wide table engine?

The wide table engine is fully compatible with the capabilities and syntax of the open source columnar database ClickHouse and can handle large amounts of columnar data. The wide table engine is suitable for aggregate query and analysis scenarios in which tables contain a large number of fields.

The wide table engine provides the following features:

The wide table engine uses the resident computing resources of interactive resource groups to run SQL jobs.
The wide table engine provides a storage-compute decoupled architecture based on the ReplicatedMergeTree engine, disk cache, and Object Storage Service (OSS).
You can easily integrate and manage the wide table engine without the need to specify databases or replicas.
The wide table engine provides high availability and high-performance distributed queries.

For more information about ClickHouse, see What Is ClickHouse?

Prerequisites

Before you enable the wide table engine feature for an AnalyticDB for MySQL cluster, make sure that the following requirements are met:

The AnalyticDB for MySQL cluster is of Data Lakehouse Edition.
The minor version of the AnalyticDB for MySQL cluster is 3.2.1.0 or later.
Note
To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.
The multi-cluster feature is disabled for interactive resource groups in the AnalyticDB for MySQL cluster.
Note
You can find an interactive resource group on the Resource Groups tab of the Resource Management page and click Modify in the Actions column to check whether Enable Multi-cluster is turned off. If Enable Multi-cluster is turned off, the multi-cluster feature is disabled for the interactive resource group.
An interactive resource group that contains more than 0 AnalyticDB compute units (ACUs) of reserved computing resources exists in the AnalyticDB for MySQL cluster.

Usage notes

After you enable the wide table engine feature, the feature cannot be disabled.
When you enable the wide table engine feature, specific nodes restart. As a result, specific SQL statements fail. We recommend that you enable the wide table engine feature during off-peak hours.
After you enable the wide table engine feature, you cannot create a resource group for which the multi-cluster feature is enabled or scale down the reserved computing resources to 0 ACUs.
You cannot back up or restore the data stored by the wide table engine.
Before you use the wide table engine, you must create an account for the wide table engine. Wide table engine accounts and database accounts of an AnalyticDB for MySQL cluster belong to different account systems. You cannot use a wide table engine account to access tables in the AnalyticDB for MySQL cluster, or use a database account of the cluster to access the wide table engine.

Billing rules

After you enable the wide table engine feature, the disk cache automatically becomes available, and you are charged a disk cache fee. For more information, see Pricing for Data Lakehouse Edition.
When you use the wide table engine, you are charged a data storage fee based on the unit price of the cold data storage of AnalyticDB for MySQL Data Lakehouse Edition clusters. For more information, see Pricing for Data Lakehouse Edition.

Enable the wide table engine feature

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, choose Cluster Management > Wide Table Engine.
On the Configuration Information tab, turn on Enable Wide Table Engine. After you enable the wide table engine feature, the disk cache automatically becomes available.
(Optional) Adjust the slider or click the plus sign to increase the value of the Disk Cache Settings parameter.

Create a wide table engine account

The wide table engine of AnalyticDB for MySQL supports privileged accounts and standard accounts. The following section describes the differences between privileged accounts and standard accounts and how to create the accounts.

Differences between privileged accounts and standard accounts

Account type

Description

Privileged account

You can create and manage privileged accounts only in the console.
You can create only a single privileged account for each cluster. The privileged account can be used to manage all standard accounts and databases of the cluster.
The privileged account allows you to implement fine-grained permission control to suit your business requirements. For example, you can grant each standard account the permissions to query specific tables.

Standard account

You can create a standard account in the AnalyticDB for MySQL console or by executing an SQL statement. For information about how to execute an SQL statement to create a standard account, see CREATE USER.

You can create up to 256 standard accounts for a cluster.

You must manually grant a standard account the permissions on specific databases. For more information, see GRANT.

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, choose Cluster Management > Wide Table Engine.
Click the Accounts tab. On the Accounts tab, click Create Account.

In the Create Account panel, create a privileged account or a standard account.

Parameter	Description
Database Account	The name of the wide table engine account. Enter a name that meets the on-screen requirements.
Account Type	Select Privileged Account or Standard Account.
New Password	The password of the wide table engine account. Enter a password that meets the on-screen requirements.
Confirm Password	Enter the password again.
Description (Optional)	The description of the wide table engine account, which facilitates subsequent account management.

Connect to and use the wide table engine

AnalyticDB for MySQL Data Lakehouse Edition allows you to connect to the wide table engine in an internal or public environment by using the HTTP, HTTPS, TCP, or MySQL protocol. In this example, a MySQL client is used to connect to the wide table engine.

Step 1: Connect to the wide table engine

Connect to the wide table engine over an internal network

Prerequisites

The MySQL client resides in the same virtual private cloud (VPC) as the AnalyticDB for MySQL Data Lakehouse Edition cluster.
A wide table engine account is created. For more information, see the "Create a wide table engine account" section of this topic.
The IP address of the MySQL client is added to a whitelist of the AnalyticDB for MySQL Data Lakehouse Edition cluster. For more information, see IP address whitelists.

Usage notes

When you call the AllocateClusterPublicConnection operation to apply for a public endpoint for the wide table engine, transient connections may occur on internal networks. Make sure that your application is configured to reconnect to the AnalyticDB for MySQL cluster.
After you change the VPC and the vSwitch of the AnalyticDB for MySQL cluster, the VPC and the vSwitch to which the wide table engine is connected remain unchanged.

Procedure

Obtain the internal endpoint and port number of the wide table engine.
1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.
2. In the left-side navigation pane, choose Cluster Management > Wide Table Engine.
3. On the Configuration Information tab, obtain the values of the VPC Endpoint and VPC MySQL Port Number parameters.
Connect to the wide table engine.
```
mysql -hurl -Pport -uuser -ppassword
```
Parameters:
- url: the value of the VPC Endpoint parameter.
- port: the value of the VPC MySQL Port Number parameter, which is fixed to 9004.
- user: the name of the wide table engine account.
- password: the password of the wide table engine account.
Example:
```
mysql -hamv-bp1s7butid48s4mw62****.ads.aliyuncs.com -P9004 -utest -p1Ws****    
```

Connect to the wide table engine over the Internet

Prerequisites

The IP address of the MySQL client is added to a whitelist of the AnalyticDB for MySQL Data Lakehouse Edition cluster. For more information, see IP address whitelists.

Procedure

Obtain the public endpoint and port number of the wide table engine.
1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.
2. In the left-side navigation pane, choose Cluster Management > Wide Table Engine.
3. On the Configuration Information tab, click Apply for Public Endpoint.
4. On the Configuration Information tab, obtain the values of the Public Endpoint and Public MySQL Port Number parameters.
Connect to the wide table engine.
```
mysql -hurl -Pport -uuser -ppassword
```
Parameters:
- url: the value of the Public Endpoint parameter.
- port: the value of the Public MySQL Port Number parameter, which is fixed to 9004.
- user: the name of the wide table engine account.
- password: the password of the wide table engine account.

Connect to the wide table engine over an SSL connection

Download the CA certificate.
1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.
2. In the left-side navigation pane, choose Cluster Management > Wide Table Engine.
3. On the Configuration Information tab, obtain the value of the VPC HTTPS Port Number parameter and click Download CA Certificate.
Connect to the wide table engine.
```
curl --cacert file https://url:port
```
Parameters:
- file: the path of the CA certificate.
- url: the endpoint of the wide table engine.
  - If you want to connect to the wide table engine over an internal network, specify a VPC endpoint.
  - If you want to connect to the wide table engine over the Internet, apply for and specify a public endpoint.
- port: the value of the VPC HTTPS Port Number parameter, which is fixed to 8443.
Example:
```
curl --cacert ca.pem https://amv-bp1s7butid48s4mw62****.ads.aliyuncs.com:8443
```

Step 2: Use the wide table engine

Create a database named adb_demo.
```
CREATE DATABASE adb_demo ON CLUSTER default [ENGINE = Replicated];
```
Note
- When you create a database, you must include the ON CLUSTER clause and specify the default parameter. This way, the database is automatically created on each node.
- When you create a database, you must set the ENGINE parameter to Replicated. You cannot specify the zoo_path, shard_name, or replica_name parameter after the ENGINE parameter. If you do not specify the ENGINE parameter, the Replicated engine is automatically used.
- For information about how to create a database, see CREATE DATABASE.
Create a table named test.
```
CREATE TABLE test (
  id Int,
  name String,
  age Int,
  likes String,
  PRIMARY KEY(id)
) 
ENGINE = ReplicatedMergeTree;
```
Note
- When you create a table, you must set the ENGINE parameter to ReplicatedMergeTree. You cannot specify the zoo_path, shard_name, or replica_name parameter after the ENGINE parameter. The system automatically adds the required parameters.
- For information about how to create a table, see CREATE TABLE.

Insert data into the test table.

INSERT INTO test(id,name,age,likes)VALUES('1','Lili','10','Football');
INSERT INTO test(id,name,age,likes)VALUES('2','Tom','15','Basketball');
INSERT INTO test(id,name,age,likes)VALUES('3','Jack','20','Volleyball');
INSERT INTO test(id,name,age,likes)VALUES('4','Lucy','25','Badminton');

Query data from the test table.

SELECT * FROM test;

The following information is returned:

+-----------+-----------+-----------+-----------+
|    id     |   name    |    age    |   likes   |
+-----------+-----------+-----------+-----------+
|    1      |   Lili    |    10     |    Football   |
+-----------+-----------+-----------+-----------+  
|    2      |   Tom     |    15     |    Basketball   |
+-----------+-----------+-----------+-----------+ 
|    3      |   Jack    |    20     |    Volleyball   |
+-----------+-----------+-----------+-----------+ 
|    4      |   Lucy    |    25     |   Badminton  |
+-----------+-----------+-----------+-----------+