Wide table engine-AnalyticDB(AnalyticDB)-阿里云帮助中心

Overview

The wide table engine is fully compatible with the capabilities and syntax of ClickHouse, an open-source columnar database. It processes large amounts of columnar data, making it ideal for aggregate queries and analysis on wide tables.

The wide table engine features the following architecture and capabilities:

The wide table engine uses resident computing resources from an Interactive resource group to run SQL jobs.
It is built on a cloud-native, storage-compute decoupled architecture that uses the ReplicatedMergeTree engine, disk cache, and OSS.
It provides a fully managed experience, eliminating the need to define databases or replicas.
The wide table engine provides high availability and high-performance distributed query capabilities.

For more information about ClickHouse, an open-source columnar database, see the ClickHouse documentation.

Prerequisites

You have submitted a ticket and received approval to use the wide table engine.
The cluster is a Data Lakehouse Edition cluster.
The cluster's minor version is 3.2.2.0 or later.

Note
To check the minor version of your Data Lakehouse Edition cluster, run the command SELECT adb_version();. To upgrade the minor version, contact technical support.
The cluster has an Interactive resource group with minimum computing resources greater than 0 ACU.

Usage notes

Once enabled, the wide table engine cannot be disabled.
When you enable the wide table engine, some nodes restart, which may cause some SQL jobs to fail. We recommend performing this operation during off-peak hours.
After you enable the wide table engine, you cannot scale down the minimum computing resources of the Interactive resource group to 0 ACU.
Backing up and restoring data for the wide table engine is not supported.
To use the wide table engine, you must create a dedicated account. This account system is separate from the standard database accounts of your AnalyticDB for MySQL cluster. You cannot use a wide table engine account to access tables in the AnalyticDB for MySQL cluster, and vice versa.

Billing

After you enable the wide table engine, disk cache is automatically enabled, which incurs disk cache fees. For more information, see Pricing for Data Lakehouse Edition.
Using the wide table engine incurs data storage fees. The unit price for this storage is the same as the price for cold data storage in a Data Lakehouse Edition cluster. For more information, see Pricing for Data Lakehouse Edition.

Enable the wide table engine

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. 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 the switch for Enable Wide Table Engine. After you enable the wide table engine, disk cache is automatically enabled.
(Optional) On the Disk Cache Settings card, adjust the slider or click the plus sign (+) to change the disk cache size.

Create a wide table engine account

The AnalyticDB for MySQL wide table engine supports privileged and standard accounts. This section describes their differences and how to create them.

Differences between privileged and standard accounts

Database account type

Description

Privileged account

You can create and manage a privileged account only in the console.
You can create only one privileged account per cluster. A privileged account can manage all standard accounts and databases.
This account type provides extensive permissions for custom and fine-grained permission management, such as granting query permissions on different tables to different users.

Standard account

You can create a standard account in the console or by running an SQL statement. For more information on creating accounts with SQL, see CREATE USER.

You can create up to 256 standard accounts per cluster.

You must manually grant permissions on specified databases to a standard account. 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. 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 Accounts tab, click Create Account.

In the Create Account panel, configure the parameters for a Privileged Account or a Standard Account.

Parameter	Description
Database Account	The name of the account. Enter a name that meets the on-screen requirements.
Account Type	Select Privileged Account or Standard Account.
New Password	The password for the account. Enter a password that meets the on-screen requirements.
Confirm Password	Confirm the password.
Description (Optional)	Enter a description for the account.

Connect to and use the wide table engine

Data Lakehouse Edition clusters allow connections to the wide table engine over an internal network or the internet through HTTP, HTTPS, TCP, and MySQL protocols. The following sections demonstrate how to connect by using a MySQL client.

Step 1: Connect to the wide table engine

Internal network connection

Prerequisites

The MySQL client and the Data Lakehouse Edition cluster are in the same VPC.
You have created a wide table engine account. For more information, see Create a wide table engine account.
You have added the MySQL client's IP address to the cluster's whitelist. For more information, see Configure a whitelist.

Usage notes

If you call the AllocateClusterPublicConnection API operation to apply for a public endpoint for the wide table engine, transient disconnections may occur over the internal network. Ensure your application has an automatic reconnection mechanism.
Changing the VPC and vSwitch for an AnalyticDB for MySQL cluster does not affect the VPC and vSwitch connected to the wide table engine.

Procedure

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. Find the cluster that you want to manage and click the cluster ID.
2. In the left-side navigation pane, choose Cluster Information > Wide Table Engine.
3. On the Configuration Information tab, view the VPC Endpoint and VPC MySQL Port Number.
Run the following command to connect to the wide table engine:
```
mysql -hurl -Pport -uuser -ppassword
```
Parameters:
- url: The VPC Endpoint of the wide table engine.
- port: The VPC MySQL Port Number, which is fixed at 9004.
- user: The wide table engine account.
- password: The password for the wide table engine account.
Example:
```
mysql -hamv-bp1s7butid48s4mw62****.ads.aliyuncs.com -P9004 -utest -p1Ws****    
```

Internet connection

Prerequisites

You have added the MySQL client's IP address to the cluster's whitelist. For more information, see Configure a whitelist.

Procedure

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. Find the cluster that you want to manage and click the cluster ID.
2. In the left-side navigation pane, choose Cluster Information > Wide Table Engine.
3. On the Configuration Information tab, click Apply for Public Endpoint.
4. On the Configuration Information tab, view the Public Endpoint and Public MySQL Port Number.
Run the following command to connect to the wide table engine:
```
mysql -hurl -Pport -uuser -ppassword
```
Parameters:
- url: The Public Endpoint of the wide table engine.
- port: The Public MySQL Port Number, which is fixed at 9004.
- user: The wide table engine account.
- password: The password for the wide table engine account.

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. Find the cluster that you want to manage and click the cluster ID.
2. In the left-side navigation pane, choose Cluster Information > Wide Table Engine.
3. On the Configuration Information tab, view the VPC HTTPS Port Number and click Download CA Certificate.
Run the following command to connect to the wide table engine:
```
curl --cacert file https://url:port
```
Parameters:
- file: The path where the CA certificate is stored.
- url: The endpoint of the wide table engine.
  - To connect over an internal network, use the VPC Endpoint.
  - If you want to connect to the wide table engine over the public network, you must first apply for a public endpoint and then enter the Public Endpoint.
- port: The VPC HTTPS Port Number, which is fixed at 8443.
Example:
```
curl --cacert ca.pem https://amv-bp1s7butid48s4mw62****.ads.aliyuncs.com:8443
```

Step 2: Use the wide table engine

Create the adb_demo database.
```
CREATE DATABASE adb_demo ON CLUSTER default [ENGINE = Replicated];
```
Note
- When you create a database, you must include the ON CLUSTER default clause. This clause is required and cannot be modified. It ensures that the database is created on every node.
- The database engine must be Replicated (this is the default and can be omitted). You cannot configure the zoo_path, shard_name, or replica_name parameters for the Replicated engine.
- For more information about the syntax and parameters, see CREATE DATABASE.
Create the test table.
```
CREATE TABLE test (
  id Int,
  name String,
  age Int,
  likes String,
  PRIMARY KEY(id)
) 
ENGINE = ReplicatedMergeTree;
```
Note
- The table engine must be ReplicatedMergeTree. You cannot configure the zoo_path, shard_name, or replica_name parameters for ReplicatedMergeTree. The system automatically configures the required parameters.
- For more information about the syntax and parameters, 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 the data.

SELECT * FROM test;

The following result is returned:

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

FAQ

Whitelist error

Cause: The wide table engine feature is in an invite-only preview. You must be on the whitelist to use it.

Solution: Submit a ticket to contact technical support and request access. Before submitting a ticket, ensure your cluster meets the requirements in the Prerequisites section.