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

Lindorm supports two service access methods: directly connecting to the OLAP resource group and querying via smart routing of the wide table engine. They are suitable for pure data analysis scenarios and business query scenarios that require analysis, respectively. Choose an appropriate access method based on your business requirements to connect to the OLAP resource group and run analysis queries.

Choose an access method

Log on to the Lindorm console.

Access method	Applicable scenarios	Connection target	Whether a Hint is required
Directly connect to the OLAP resource group	Pure data analysis scenarios, such as connecting to BI reporting tools or performing exploratory queries	OLAP resource group	No
Query via smart routing of the wide table engine	Daily business queries (high-concurrency point queries) that also require complex analytical queries	Wide table engine	Method 1 (SQL Hint): Required. Method 2 (connection string parameter): Not required.

Prerequisites

The OLAP resource group service has been activated. If not activated, see Activate and Manage.
40" data-tag="xref" id="a-activate-a-01">Activate and Manage.
The client IP address has been added to the Lindorm whitelist.
dorm-wh-01">Lindorm whitelist.

Connect directly to the OLAP resource group

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.

Procedure

(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).
gation pane, choose Configuration and Management > Database Connections.
Log on to the Lindorm console.
="uicontrol" conref="mcms_dita_lindorm.dita#lindorm/cluster.detail.compute" data-fallback="Compute Engine" data-isbold="true" id="uicontrol-compute-en-01"> tab. In the OLAP Connection method section, find the target resource group and obtain its VPC connection address.
In the upper-left corner of the page, select the region where the instance resides.
ref" id="a-configure-01">configure a MySQL client, connect to the OLAP resource group by using the MySQL protocol:
On the Instances page, click the target instance ID, or click Manage in the Actions column of the target instance.
e-h-01">-h and -P).
In the left-side navigation pane, choose Configuration and Management > Database Connections.
data-fallback="VPC" data-isbold="true" id="uicontrol-vpc-01"> connection address. ent.
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
```
dita#" data-node="3012269" data-root="61140" data-tag="xref" id="a-connect-by-03">Connect by using Lindorm-cli and Connect by using the MySQL protocol (recommended).
```
mysql -hld-bp1dv4****-proxy-ldps.lindorm.aliyuncs.com -P9030 -uroot -p
```
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).
on>

Query via smart routing of the wide table engine

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

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

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
```
configured. For more information, see Connect by using Lindorm-cli and Connect by using the MySQL protocol (recommended).
Method 2 (connection string parameter): Specify parameters when you establish the connection. After the connection is established, all queries are automatically routed to the specified OLAP resource group.
rting or exploratory queries. You connect by using the independent address of an OLAP resource group.

Method 1: Forward the query with a SQL Hint

This hint must immediately follow the SELECT keyword.

Examples

/*+ _use_ldps_($olap_cg_name) */

Important

Parameters

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

-- Use a database
USE db01;

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

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

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.

Variable	Type	Default value	Description
olap_cg_name	String	Default OLAP resource group	Specifies the name of the OLAP resource group to which the query is forwarded. If not specified, the query is forwarded to the default resource group.

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

Dynamically set session variables during forwarding

Syntax

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

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

Important

Example

Code example

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

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.

Variable	Type	Default value	Description	Recommended scenario
query_timeout	INT	-	Sets the query timeout in seconds.	Use this variable when you need to limit the execution duration of long-running queries.
enable_profile	BOOL	FALSE	Enables the query Profile feature to analyze the query execution process.	Use this variable when you troubleshoot query performance issues.

Method 2: Specify the OLAP resource group via a connection string

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 ("").

Connect via Lindorm-cli

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.

./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>

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

SELECT * FROM <catalog-name>.<target-database-name>.<target-table-name>;

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

SELECT * FROM lindorm_columnar.`default`.test_table;

Connect via JDBC

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).

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

Note

For more information about JDBC application development, see Connect and use the MySQL protocol (recommended).