How to access data and compute through an OLAP resource group-Lindorm(Lindorm)-阿里云帮助中心

Prerequisites

The OLAP resource group service has been activated. If not activated, see Activate and Manage.
The client IP address has been added to the Lindorm whitelist.
(For intelligent routing only) The wide table engine connection is configured. For more information, see Connect by using Lindorm-cli and Connect by using the MySQL protocol (recommended).

Connect directly to an OLAP resource group

Best suited for pure data analysis scenarios such as BI reporting or exploratory queries. You connect by using the independent address of an OLAP resource group.

Procedure

Log on to the Lindorm console.
In the upper-left corner of the page, select the region where the instance resides.
On the Instances page, click the target instance ID, or click Manage in the Actions column of the target instance.
In the left-side navigation pane, choose Configuration and Management > Database Connections.
Click the Compute Engine tab. In the OLAPConnection method section, find the target resource group and obtain its VPC connection address.
After you configure a MySQL client, connect to the OLAP resource group by using the MySQL protocol:
1. Extract the hostname from the VPC address obtained in step 5. The console displays the address in host:port format, but the mysql CLI requires the host and port as separate flags (-h and -P).
2. Run the following command. Use the username and password of the Lindorm wide table engine to log on.
```
mysql -hld-bp1dv4****-proxy-ldps.lindorm.aliyuncs.com -P9030 -uroot -p
```

Route queries via the wide table engine

Best suited for mixed workloads where you run analytical queries alongside high-concurrency point queries. You connect to the wide table engine and route queries to an OLAP resource group by adding hints to SQL statements or by specifying parameters in the connection string. For more information about how to connect to the wide table engine, see Connect by using Lindorm-cli and Connect by using the MySQL protocol (recommended).

The following table compares the two routing methods:

Aspect	SQL hints	Connection string parameters
Scope	Per-query	Per-connection
Flexibility	Route individual queries to different resource groups	All queries on the connection use the same resource group
Session variable tuning	Supports `SET_VAR` for per-query execution control	Not supported
Use case	Ad-hoc analytical queries mixed with point queries	Dedicated analytical connections through the wide table engine

We recommend connection string parameters for most scenarios. Use SQL hints when you need per-query routing control.

Forward queries through SQL hints

After you connect to the Lindorm wide table engine by using a MySQL client or Lindorm-cli, use the _use_ldps_ hint to forward specific SQL statements to an OLAP resource group.

Syntax

/*+ _use_ldps_(olap_cg_name) */

Important

This hint must immediately follow the SELECT keyword.

Examples

-- Select the database.
USE db01;

-- Forward the query to the default OLAP resource group.
SELECT /*+ _use_ldps_ */ * FROM tb01 LIMIT 5;

-- Forward the query to a specific OLAP resource group.
SELECT /*+ _use_ldps_(olap_cg0) */ * FROM tb01 LIMIT 5;

Parameters

Variable	Type	Default value	Description
olap_cg_name	String	Default OLAP resource group	Name of the OLAP resource group to which the query is forwarded. If omitted, the query is forwarded to the default resource group.

To set the default OLAP resource group, see Activate and Manage.

Dynamically set session variables

When forwarding a query to an OLAP resource group through SQL hints, use the SET_VAR hint to control execution behavior in the OLAP engine, such as adjusting concurrency or enabling optimization options.

Note

The SET_VAR hint is available only with the SQL hint method. Connection string parameters do not support per-query session variable tuning.

Syntax

/*+ SET_VAR(key1 = value1, key2 = value2) */

Important

The SET_VAR hint must immediately follow the _use_ldps_ hint and must not share a comment block with the forwarding hint.

Example

-- Set session variables for a query.
SELECT /*+ _use_ldps_ */ /*+ SET_VAR(query_timeout = 60, enable_profile = true) */ * FROM tb01 LIMIT 5;

Specify an OLAP resource group through connection strings

Specify the _use_ldps_=true and compute-group=<resource group name> parameters in the connection string when you connect to the wide table engine. All queries on the connection are then automatically routed to the specified OLAP resource group without per-query hints.

Connect by using Lindorm-cli

When you connect to the wide table engine by using Lindorm-cli, append ?_use_ldps_=true&compute-group=<OLAP resource group name> to the JDBC URL. Enclose the URL parameters in double quotation marks ("").

./lindorm-cli -url "jdbc:lindorm:table:url=http://ld-bp12pt80qr38p****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060?_use_ldps_=true&compute-group=olapcg1" -username <username> -password <password>

After the connection is established, run a query to verify that queries are routed to the OLAP resource group. For more information about query syntax, see the Lindorm-cli data access documentation.

SELECT * FROM <catalog_name>.<target_database>.<target_table>;

If you use the default database default, enclose it with backticks when you query data. Example: SELECT * FROM lindorm_columnar.\default`.test_table;`

Connect by using JDBC

Connect to the wide table engine by using the MySQL protocol. The following example assumes the OLAP resource group name is olapcg1:

jdbc:mysql://ld-bp17j28j2y7pm****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com:33060/?connectionAttributes=_use_ldps_:true,compute-group:olapcg1

After the connection is established, run a query to verify that queries are routed to the OLAP resource group. For more information about JDBC application development, see Connect by using the MySQL protocol (recommended).