MaxCompute:Lindorm external tables

Introduction

Lindorm is a cloud-native multi-model database designed and optimized for scenarios such as the Internet of Things (IoT), the internet, 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.

After establishing a network connection between MaxCompute and Lindorm, you can create an external table to map to a Lindorm table. This lets you perform the following operations:

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

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

• Write data from MaxCompute to a Lindorm table.

Limitations

• Region availability: This feature is available only 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 column count are handled as follows: If the source table has fewer columns than defined in the external table DDL, the missing columns are populated with NULL values. Conversely, if the source table has more columns, the extra column data is discarded.

Create a Lindorm external table

-- Enable Hive-compatible mode.
SET odps.sql.hive.compatible = true;
-- Set 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 based on a VPC connection solution. For more information about VPC connection solutions, see Network enablement process.

   • This connection only allows MaxCompute to access the specified VPC. To access resources in other VPCs or across regions, follow the instructions in VPC access (direct connection over a leased line) to set up connectivity between the VPC specified in the leased line connection solution and other VPCs.

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:

      

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: