How to connect to an OLAP resource group to access data - Lindorm

This topic describes how to access column store and wide table data using an online analytical processing (OLAP) resource group. OLAP resource groups provide more comprehensive SQL analysis capabilities than the built-in SQL feature of the wide table engine. This makes them suitable for high-concurrency analysis of large-scale data.

Prerequisites

You have activated LindormTable for your Lindorm instance.
You have activated the Lindorm compute engine.
The IP address of the client has been added to the Lindorm whitelist.
You have enabled an OLAP resource group.

Get the OLAP resource group endpoint and connect

Log on to the Lindorm console. In the upper-left corner of the page, select the region of the instance. On the Instances page, click the ID of the target instance or click View Instance Details in the Actions column for the instance.
In the Configurations section of the Instance Details page, click Resource Groups in the Actions column corresponding to Compute Engine.
On the Resource Group Details page, select an OLAP resource group that has a Status and Description of Running. Hover the pointer over the name of the OLAP resource group to obtain its VPC internal endpoint, such as jdbc:mysql://ld-bp1dv48fk0yg0****-olap-proxy-ldps.lindorm.aliyuncs.com:9030.
After you configure the MySQL client, use the VPC internal endpoint and the username and password for LindormTable to connect to the OLAP resource group. The connection uses the MySQL protocol.
```
mysql -hld-bp1dv48fk0yg0****-olap-proxy-ldps.lindorm.aliyuncs.com -P9030 -uroot -p
```
After the connection is established, the MySQL command line prompt is displayed. You can then execute SQL statements.

Access data

OLAP resource groups use a compute-storage separation architecture. They support direct access to two internal data sources: the column store engine and the wide table engine. Catalogs are used to distinguish between data sources.

You can use the following SQL statements to switch between the column store and wide table data sources:

Switch to the wide table data source: SET CATALOG lindorm_table;.
Switch to the column store data source: SET CATALOG lindorm_columnar;.

After you switch, you can run SELECT catalog(); to check the current catalog.

Access column store data

Column store data uses an open data lake format that is compatible with the Apache Iceberg specification. The data is physically stored in the underlying distributed file system of the Lindorm instance. This storage architecture allows different types of resource groups in the same Lindorm instance, such as OLAP and extract, transform, and load (ETL) resource groups, to directly access the same columnar data.

Create a database.
```
CREATE DATABASE olapdemo;
```
Use the database.
```
USE olapdemo;
```

Create a data table.

CREATE TABLE test (id INT, name STRING) ENGINE = iceberg;

Write data.

INSERT INTO test VALUES (0, 'Jay'), (1, 'Edison');

Query data.

Example 1:

SELECT id, name FROM test WHERE id != 0;

Output:

+------+--------+
| id   | name   |
+------+--------+
|    1 | Edison |
+------+--------+

Example 2:

SELECT count(distinct name) FROM test;

Output:

+----------------------+
| count(DISTINCT name) |
+----------------------+
|                    2 |
+----------------------+

Example 3:

SELECT * FROM
 (SELECT id, name FROM test WHERE id != 0) t0
 JOIN
 (SELECT id, name FROM test WHERE id != 2) t1
 ON t0.id=t1.id;

+------+--------+------+--------+
| id   | name   | id   | name   |
+------+--------+------+--------+
|    1 | Edison |    1 | Edison |
+------+--------+------+--------+

Write the query result to the table.
```
INSERT INTO test SELECT * FROM test;
```
Delete the data table.
```
DROP TABLE test;
```
Delete the database.
```
DROP DATABASE olapdemo;
```

Access wide table data

The Lindorm wide table engine is a distributed storage system designed for large-scale structured and semi-structured data. It is compatible with open standard interfaces such as HBase, Phoenix (SQL), and Cassandra. The engine can store hundreds of trillions of rows of data in a single table, handle tens of millions of concurrent requests with millisecond-level response times, and provide strong consistency and cross-data center disaster recovery. The wide table engine is suitable for scenarios such as metadata management, orders and bills, user personas, social graphs, and log analysis. It efficiently meets business requirements for online storage and real-time queries of large-scale data.

OLAP resource groups can connect to the wide table engine. They use independent computing resources and enhanced query capabilities to perform real-time analysis on wide table data.

If you already have a usable wide table, proceed to the next step. Otherwise, connect to LindormTable and execute the following statements to create a wide table named tb:

-- Create a database.
CREATE DATABASE test; 

-- Use the database.
USE test; 

-- Create a wide table and insert two data records.
CREATE TABLE tb (id varchar, name varchar, address varchar,  primary key(id, name)) ; 
UPSERT INTO tb (id, name, address) values ('001', 'Jack',  'hz'); 
UPSERT INTO tb (id, name, address) values ('002', 'Edison',  'bj');

In the MySQL command line interface that is connected to the OLAP resource group, execute the following statements to access wide table data.

Explicitly switch the data source to lindorm_table.
```
SET CATALOG lindorm_table;
```
View all databases in the current Lindorm instance.
```
SHOW DATABASES;
```
Use the test database.
```
USE test;
```
View all existing tables in the current database.
```
SHOW TABLES;
```

Query wide table data.

Example 1:

SELECT * FROM tb LIMIT 5;

Output:

+------+--------+---------+
| id   | name   | address |
+------+--------+---------+
| 001  | Jack   | hz      |
| 002  | Edison | bj      |
+------+--------+---------+

Example 2:

SELECT count(*) FROM tb;

Output:

+----------+
| count(*) |
+----------+
|        2 |
+----------+

Important

The wide table engine data source supports only querying data from existing tables. Other operations are not supported.

Use an OLAP resource group through the wide table engine

An OLAP resource group can serve as an extended query engine for the wide table engine. By connecting to the wide table engine endpoint and adding a specific hint to your SQL statement, you can forward complex queries to an OLAP resource group for execution. This process does not require a separate connection. This method lets you combine the high-concurrency query capabilities of the wide table engine with the complex analysis capabilities of the OLAP resource group, which enables intelligent routing of SQL statements.

When you are connected to the Lindorm wide table engine using MySQL or Lindorm-cli, you can use the _use_ldps_ hint to forward specific SQL statements to an OLAP resource group for execution. For example:

-- Use 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 specified OLAP resource group
SELECT /*+ _use_ldps_(olap_cg_name) */ * FROM tb01 LIMIT 5;

The hint format is /*+ _use_ldps_(olap_cg_name) */ and must be placed after the outermost SELECT keyword. The (olap_cg_name) parameter specifies the name of the OLAP resource group to which the query is forwarded. If this parameter is not specified, the query is forwarded to the default resource group.

Advanced usage

When you forward a query to an OLAP resource group, you can specify session variables for the query execution. This provides fine-grained control over the query behavior. The following example shows how to specify variables:

-- Specify session variables for the query
SELECT /*+ _use_ldps_ */ /*+ SET_VAR(enable_lindorm_table_concurrent_scan = false) */ * FROM tb01 LIMIT 5;

-- Specify multiple session variables for the query
SELECT /*+ _use_ldps_ */ /*+ SET_VAR(enable_lindorm_table_concurrent_scan = false, enable_build_join_filter_from_lindorm_table_in_opt = true) */ * FROM tb01 LIMIT 5;

The hint format for specifying session variables is /*+ SET_VAR(enable_lindorm_table_concurrent_scan = false) */. Variables are specified in the key = value format. You can separate multiple variables with commas. For information about the supported variables, see the next section. This hint must be placed after the OLAP resource group forwarding hint and cannot share the same comment block.

Note

The session hint must be placed in a separate comment block after the OLAP resource group forwarding hint.

Parameters

Variable	Type	Default value	Description
enable_build_join_filter_from_lindorm_table_in_opt	BOOL	FALSE	When you join two tables in the wide table engine, a filter condition for the left table can be generated and pushed down based on the query result of the right table. This accelerates the data scanning process for the left table.
enable_lindorm_table_concurrent_scan	BOOL	TRUE	This parameter controls the concurrency for reading a Lindorm wide table. If set to TRUE, the wide table is scanned concurrently. If set to FALSE, the wide table is scanned in a single thread.