MaxCompute:Lindorm external tables (cloud-native multi-model database)

Background information

Lindorm is a cloud-native, multi-model, hyper-converged database designed and optimized for scenarios such as the Internet of Things (IoT), the Internet, and the Internet of Vehicles. It supports unified access and integrated processing of various 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. It is a core database that supports key businesses at Alibaba. For more information about Lindorm, see Lindorm Product Overview.

Prerequisites

Limits

• You can create Lindorm external tables only in the following regions because only these regions support the solution for direct connection to a VPC over a leased line: 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 are not supported in other regions.

• When you establish network connectivity, MaxCompute connects only to the VPC that corresponds to the specified VPC ID. To access resources across regions or in other VPCs within the same region, you must establish network connectivity between the VPC specified in the leased line connection solution and the other VPCs. You can use an existing cloud VPC connection solution to accomplish this.

• Lindorm external tables do not support the cluster property.

Notes

When you read data from a Lindorm source table, if the source table has fewer columns than the number of columns defined in the external table's Data Definition Language (DDL) statement, the missing column values are filled with NULL. If the source table has more columns, the data in the extra columns is discarded.

Syntax for creating a Lindorm external table

--Enable Hive-compatible mode.
SET odps.sql.hive.compatible = true;
--Set 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>
(
  <col1_name> <data_type>,
  <col2_name> <data_type>,
  ......
)
--The handler for the Lindorm data source.
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES(
  'hbase.columns.mapping'='<:key,col-family:col-name1,col-family:col-name2,...>')
  TBLPROPERTIES(
    --hbase.table.name and mcfed.hbase.zookeeper.quorum are required.
    'hbase.table.name'='<namespace:lindorm_table_name>',
    'hbase.zookeeper.quorum'='<VPC ip1|host1:port,VPC ip2|host2:port...>',
    --Configure the hbase.client.username and hbase.client.password parameters.
    'hbase.client.username'='****','hbase.client.password'='****',
    --hbase.zookeeper.property.clientPort, mcfed.zookeeper.session.timeout, and hbase.client.retries.number are optional parameters.
    --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>'
);

Usage examples

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

   1. Create the mf_lindorm_test_t table.

      CREATE 'mf_lindorm_test_t','f';

   2. Insert data into the mf_lindorm_test_t table.

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

   3. Query data in the mf_lindorm_test_t table.

      scan 'mf_lindorm_test_t';

      The query result is shown in the following figure:

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

   1. Configure the environment context and create the MaxCompute Lindorm external table mf_lindorm_test_t_ext.

      set odps.sql.hive.compatible = true;
      set odps.sql.hbase.version=lindorm;
      CREATE EXTERNAL TABLE mf_lindorm_test_t_ext
      (
      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'='mf_lindorm_test_t',
          'hbase.zookeeper.quorum'='ld-2z****-proxy-lindorm.lindorm.rds.aliyuncs.com:30020',
          'hbase.client.username'='root',
          'hbase.client.password'='root',
          'networklink'='networklink4hbase');

   2. Query data from the mf_lindorm_test_t_ext external table.

      set odps.sql.hive.compatible = true;
      set odps.sql.hbase.version=lindorm;
      select * from mf_lindorm_test_t_ext;

      The query result is as follows:

      +------------+------------+----------+
      | key        | col1       | col2     |
      +------------+------------+----------+
      | 1122       | hello      | hbase    |  
      +------------+------------+----------+

   3. Insert data into the mf_lindorm_test_t_ext external table.

      Run the INSERT OVERWRITE statement to write data to the created external table.

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

   4. Query the data in the mf_lindorm_test_t_ext external table again.

      set odps.sql.hive.compatible = true;
      set odps.sql.hbase.version=lindorm;
      select * from mf_lindorm_test_t_ext;

      The query result is as follows:

      +------------+------------+-------------+
      | key        | col1       | col2        |
      +------------+------------+-------------+
      | 1122       | hello      | hbase       | 
      | 1123       | lindorm    | mftestinsert|   
      +------------+------------+-------------+

   5. Use the scan statement to query data in the Lindorm table mf_lindorm_test_t.

      scan 'mf_lindorm_test_t';

      The query result is as follows: