DocsICP FilingConsole
官方文档
首页

Data access

更新时间：
复制 MD 格式
产品详情
我的收藏

Use an extract, transform, and load (ETL) resource group to run SQL queries against LindormTable and column store data through an interactive beeline session. This lets you query both wide table and columnar data from a single Spark SQL environment without additional infrastructure.

Prerequisites

Before you begin, ensure that you have:

  • JDK 1.8 or later installed

  • LindormTable activated

  • The Lindorm compute engine activated

  • The client IP address added to the Lindorm whitelist

  • An ETL resource group enabled

Usage notes

If your application runs on an Elastic Compute Service (ECS) instance, the Lindorm instance and the ECS instance must meet the following requirements to connect over a virtual private cloud (VPC):

  • Both instances are in the same region. Deploy them in the same zone to reduce network latency.

  • Both instances are in the same VPC.

Step 1: Connect to an ETL resource group

  1. Download the Spark deployment package and decompress it.

  2. Set the SPARK_HOME environment variable to the path of the decompressed package.

    export SPARK_HOME=/path/to/spark/;

  3. Configure $SPARK_HOME/conf/beeline.conf with the following parameters.

    To add extra job parameters, append them to beeline.conf in key=value format. For details, see Job configuration.
    ParameterDescription
    endpointThe JDBC endpoint of the Lindorm compute engine. For details, see View endpoints.
    userThe username for LindormTable.
    passwordThe password for the specified username.
    shareResourceWhether to share computing resources across multiple sessions of the same user. Default: true.
    compute-groupThe name of the ETL resource group. Default: default.

  4. Go to $SPARK_HOME/bin and start an interactive session.

    ./beeline

    The session accepts SQL statements for read and write operations.

Step 2: Access data

The ETL resource group uses catalogs to route SQL operations to the correct data source. Each catalog maps to one data source type:

  • lindorm_columnar — column store data (Apache Iceberg format)

  • lindorm_table — wide table data (LindormTable)

The session starts in the lindorm_columnar catalog by default. Switch catalogs before running queries to make sure SQL statements target the intended data source.

-- Switch to column store
USE lindorm_columnar;

-- Switch to wide table
USE lindorm_table;

Access column store data

Column store data is stored in an open data lake format that is compatible with the Apache Iceberg specification on Lindorm's distributed file system. Both the online analytical processing (OLAP) resource group and the ETL resource group in the same Lindorm instance can access this data directly.

  1. Create and select a database.

    -- Create a database.
CREATE DATABASE etldemo;

-- Select the database.
USE etldemo;

  2. Create a table and insert data.

    -- Create a table.
CREATE TABLE test (id INT, name STRING);

-- Insert data.
INSERT INTO test VALUES (0, 'Jay'), (1, 'Edison');

  3. Query data. The following examples show common query patterns. Filter rows by column value:

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

    Expected output:

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

    Count distinct values:

    SELECT count(DISTINCT name) FROM test;

    Expected output:

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

    Join two result sets:

    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;

    Expected output:

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

  4. Write query results back to a table.

    INSERT INTO test SELECT * FROM test;

  5. Drop the table and database when done.

    DROP TABLE test PURGE;
DROP DATABASE etldemo;

Access wide table data

The ETL resource group connects to LindormTable through the lindorm_table catalog to perform real-time analytics on wide table data, using independent compute resources for enhanced query performance.

If you already have a wide table in LindormTable, skip the setup step below. Otherwise, connect to LindormTable and create a test table.

-- Create a database.
CREATE DATABASE test;

-- Select the database.
USE test;

-- Create a table and insert two 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 beeline session, run the following steps to query wide table data.

  1. Switch to the lindorm_table catalog and select the database.

    -- Switch to the wide table data source.
USE lindorm_table;

-- Select the database.
USE test;

  2. Query the data. Retrieve rows with a row limit:

    SELECT * FROM tb LIMIT 5;

    Expected output:

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

    Count all rows:

    SELECT count(*) FROM tb;

    Expected output:

    +-----------+
| count(1)  |
+-----------+
| 2         |
+-----------+

What's next

For programmatic access, use Spark SQL or Java Database Connectivity (JDBC) to query Lindorm column store and wide table data from JAR and Python jobs:

References