Lindorm external tables

Introduction

Lindorm is a cloud-native multi-model database designed for scenarios such as the Internet of Things (IoT), internet services, and the Internet of Vehicles. It supports unified access to and integrated processing of diverse data types, including wide tables, time series, text, objects, streams, and spatial data. Lindorm is compatible with multiple standard interfaces, such as SQL, HBase/Cassandra/S3, TSDB, HDFS, Solr, and Kafka, and is one of the core databases that supports Alibaba's key services.

If a network connection is established between MaxCompute and Lindorm, you can create an external table that maps to a Lindorm table. This allows you to:

• Synchronize data from a Lindorm table to MaxCompute for subsequent ETL processing.

• Run a federated query by joining the table with other Lindorm tables.

• Write data from MaxCompute to a Lindorm table.

Limitations

• Region availability: This feature is only available in the following regions: China (Beijing), China (Shanghai), China (Zhangjiakou), China (Hangzhou), China (Shenzhen), China (Hong Kong), China (Shanghai) Finance Cloud Zone F, Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Germany (Frankfurt), US (Silicon Valley), and US (Virginia).

• Lindorm external tables do not support the cluster property.

Considerations

When you read data from a source Lindorm table, mismatches in the column count are handled as follows: If the source table has fewer columns than defined in the external table's DDL, the missing columns are set to NULL. If the source table has more columns, the extra data is ignored.

Create a Lindorm external table

-- Enable Hive-compatible mode.
SET odps.sql.hive.compatible = true;
-- Configure the odps.sql.hbase.version parameter to switch the HBase version to lindorm.
SET odps.sql.hbase.version=lindorm;
-- Create a Lindorm external table.
CREATE EXTERNAL TABLE if NOT EXISTS <mc_lindorm_external>
(
  <col_name1> <data_type>,
  <col_name2> <data_type>,
  ......
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' -- The handler for the Lindorm data source.
WITH SERDEPROPERTIES(
  'hbase.columns.mapping'='<:key,col-family:col-name1,col-family:col-name2,...>')
TBLPROPERTIES(
    -- The hbase.table.name and hbase.zookeeper.quorum parameters are required.
    'hbase.table.name'='',
    'hbase.zookeeper.quorum'='',
    -- Configure the hbase.client.username and hbase.client.password parameters.
    'hbase.client.username'='****','hbase.client.password'='****',
    -- The hbase.zookeeper.property.clientPort, mcfed.zookeeper.session.timeout, and hbase.client.retries.number parameters are optional.
    -- The default value of hbase.zookeeper.property.clientPort is 30020.
    ['hbase.zookeeper.property.clientPort'='{30020}',]
    ['mcfed.zookeeper.session.timeout'='{{VALUE}}',]
    ['hbase.client.retries.number'='{{VALUE}}',]
    'networklink'='{{NETWORKLINK_NAME}}'
);

Example

1. You have created a MaxCompute project and installed and configured the MaxCompute client.

2. You have activated the Lindorm service and created a Lindorm instance.

3. You have downloaded and configured Lindorm Shell. For more information, see Access the wide-table engine by using Lindorm Shell.

4. Network connection

   • You have established a network connection between MaxCompute and Lindorm through a VPC. For more information about VPC connection solutions, see Network connection process.

   • This connection only allows MaxCompute to access the specified VPC. To access other VPCs or resources across regions, you must connect the VPCs by following the instructions in VPC access (direct connection over a leased line).

5. Use Lindorm Shell to create a Lindorm table and insert data.

   1. Create the lindorm_test table.

      CREATE 'lindorm_test','f';

   2. Insert data into the lindorm_test table.

      put 'lindorm_test', '1122', 'f:col1', 'hello';
      put 'lindorm_test', '1122', 'f:col2', 'hbase';

   3. Query data from the lindorm_test table.

      scan 'lindorm_test';

      The following result is returned:

      ROW                                 COLUMN+CELL
       1122                              column=f:col1, timestamp=1634201741745, value=hello
       1122                              column=f:col2, timestamp=1634201835934, value=hbase
      1 row(s)
      Took 0.0766 seconds

6. Create a MaxCompute external table and map it to the Lindorm data source.

   1. Run the following command on the MaxCompute client to create a Lindorm external table named lindorm_test_ext.

      SET odps.sql.hive.compatible = true;
      SET odps.sql.hbase.version=lindorm;
      CREATE EXTERNAL TABLE lindorm_test_ext
      (
        row_key  INT,
        col1 STRING,
        col2 STRING
      )
      STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
      WITH SERDEPROPERTIES (
        'hbase.columns.mapping'=':key,f:col1,f:col2')
      TBLPROPERTIES (
        'hbase.table.name'='lindorm_test',
        'hbase.zookeeper.quorum'='ld-2z****-proxy-lindorm.lindorm.rds.aliyuncs.com:30020',
        'hbase.client.username'='<user_name>',
        'hbase.client.password'='***',
        'networklink'='<your network link name>');

   2. Query data from the external table lindorm_test_ext.

      SET odps.sql.hive.compatible = true;
      SET odps.sql.hbase.version=lindorm;
      SELECT * FROM lindorm_test_ext;
      -- The following result is returned:
      +------------+------------+----------+
      | key        | col1       | col2     |
      +------------+------------+----------+
      | 1122       | hello      | hbase    |  
      +------------+------------+----------+

   3. Insert data into the external table lindorm_test_ext.

      SET odps.sql.hive.compatible = true;
      SET odps.sql.hbase.version = lindorm;
      INSERT OVERWRITE TABLE lindorm_test_ext SELECT '1123', 'lindorm', 'mftestinsert';

   4. Query data from the external table lindorm_test_ext again.

      SET odps.sql.hive.compatible = true;
      SET odps.sql.hbase.version=lindorm;
      SELECT * FROM lindorm_test_ext;
      -- The following result is returned:
      +------------+------------+-------------+
      | key        | col1       | col2        |
      +------------+------------+-------------+
      | 1122       | hello      | hbase       | 
      | 1123       | lindorm    | mftestinsert|   
      +------------+------------+-------------+

   5. Use the scan statement to query data from the Lindorm table lindorm_test.

      scan 'lindorm_test';

      The following result is returned:

      ROW                                 COLUMN+CELL
       1122                              column=f:col1, timestamp=1634201741745, value=hello
       1122                              column=f:col2, timestamp=1634201835934, value=hbase
       1123                              column=f:col1, timestamp=1634552508947, value=lindorm
       1123                              column=f:col2, timestamp=1634552508947, value=mftestinsert
      2 row(s)
      Took 0.0683 seconds