ApsaraDB for HBase external tables (Standard or performance-enhanced edition)

Background information

ApsaraDB for HBase is a distributed database that is compatible with the standard HBase access protocol. It is available in Standard, Performance-enhanced, and Serverless editions. MaxCompute supports access only to ApsaraDB for HBase Standard Edition or ApsaraDB for HBase Performance-enhanced Edition clusters.

You must first establish a VPC connection between them. To access ApsaraDB for HBase using an external table, you must establish a network link between MaxCompute and the VPC and add the required IP addresses to the ApsaraDB for HBase whitelist. For more information, see Set a whitelist.

Prerequisites

Limits

• You can create an ApsaraDB for HBase external table to connect MaxCompute and ApsaraDB for HBase only in the following regions: China (Beijing), China (Shanghai), China (Zhangjiakou), China (Ulanqab), China (Hangzhou), China (Shenzhen), China (Hong Kong), China East 2 Finance Cloud (Zone F), Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Germany (Frankfurt), US (Silicon Valley), and US (Virginia). For information about how to establish a network connection, see Network connection process.

• After the network connection is established, MaxCompute can access only the specified VPC. To access another VPC in the same or a different region, you must establish a network connection from the connected VPC to the other VPC.

• External tables can be used to access only ApsaraDB for HBase 2.x clusters.

• ApsaraDB for HBase external tables do not support EMR HBase clusters.

• The cluster property is not supported for ApsaraDB for HBase external tables.

Usage notes

When you read data from the source ApsaraDB for HBase table, if the source table has fewer columns than the external table's schema, the missing columns are populated with NULL. If the source table has more columns, the extra columns are ignored.

HBase external table syntax

When you create an external table, you must specify HBaseStorageHandler in the DDL statement and configure information about the ApsaraDB for HBase Standard Edition or ApsaraDB for HBase Performance-enhanced Edition cluster. Queries on table and column names are case-insensitive.

-- Enable Hive compatibility mode.
set odps.sql.hive.compatible = true;
-- If you use an ApsaraDB for HBase Performance-enhanced Edition cluster, you must also set the odps.sql.hbase.version parameter to lindorm.
set odps.sql.hbase.version=lindorm;
-- Create an ApsaraDB for HBase external table.
create external table if not exists <mc_hbase_external>
(
  <col1_name> <data_type>,
  <col2_name> <data_type>,
  ......
)
-- The handler used to process data from the ApsaraDB for HBase 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 (
-- The hbase.table.name and hbase.zookeeper.quorum parameters are required.
'hbase.table.name'='<hbase_table_name>',
'hbase.zookeeper.quorum'='<VPC ip1|host1:port,VPC ip2|host2:port...>',
-- The hbase.client.username and hbase.client.password parameters are required only for ApsaraDB for HBase Performance-enhanced Edition clusters.
'hbase.client.username'='****',
'hbase.client.password'='****',
-- The hbase.zookeeper.property.clientPort, mcfed.zookeeper.session.timeout, and hbase.client.retries.number parameters are optional.
-- For ApsaraDB for HBase Standard Edition clusters, the default value of hbase.zookeeper.property.clientPort is 2181.
-- For ApsaraDB for HBase Performance-enhanced Edition clusters, the default value is 30020.
['hbase.zookeeper.property.clientPort'='{2181|30020}',]
['mcfed.zookeeper.session.timeout'='<value>',]
['hbase.client.retries.number'='<value>',]
'networklink'='<networklink_name>'
);

• mc_hbase_external: Required. The name of the ApsaraDB for HBase external table that you want to create.

• col_name: Required. The name of a column in the ApsaraDB for HBase external table.

• data_type: Required. The data type of the column.

• with serdeproperties:

  • 'hbase.columns.mapping'='<:key,col-family:col-name1,col-family:col-name2,...>': Required. The mappings between the columns of the ApsaraDB for HBase external table and the columns of the table in the ApsaraDB for HBase cluster.

    • The RowKey, which is specified as :key, must be the first entry.

    • The column format is col-family:col-name1,col-family:col-name2. Only one column family can be specified in a job.

• hbase://VPC ip|host:port: Required. The ZooKeeper master configured on ApsaraDB for HBase. This is the ZooKeeper connection endpoint for ApsaraDB for HBase Standard Edition or the Java API access endpoint for ApsaraDB for HBase Performance-enhanced Edition. You can also specify the VPC IP address, IP address range, or port of the associated ApsaraDB for HBase table. Examples: 192.0.2.1-192.0.2.10:9000, 192.0.2.20:70050 or 192.0.2.20/16:2181. To obtain this information, log on to the ApsaraDB for HBase console. On the Clusters page, click the name of the target cluster. In the left-side navigation pane, click Database Connection to view the Connection Information.

  • For distributed data sources, confirm the front-end IP address and port and the DataNode IP addresses and ports that you want to access. For an ApsaraDB for HBase external table, you must specify the IP addresses and ports of ZooKeeper, HMaster, and RegionServer (2181 or 30020, 16000, and 16020). The default ZooKeeper port is 2181 for ApsaraDB for HBase Standard Edition and 30020 for ApsaraDB for HBase Performance-enhanced Edition.

  • This field supports IP address ranges. IP address mapping consumes resources. By default, you can map a maximum of 2,000 IP addresses at a time.

• tblproperties:

  • 'hbase.table.name'='<hbase_table_name>': Required. The name of the table in the ApsaraDB for HBase cluster that MaxCompute will map to. This table must already exist and have been created using an HBase client.

  • 'hbase.zookeeper.quorum'='<VPC ip1|host1:port,VPC ip2|host2:port...>': Required. The IP address or domain name of ZooKeeper for ApsaraDB for HBase. This is the ZooKeeper connection endpoint for ApsaraDB for HBase Standard Edition or the Java API access endpoint for ApsaraDB for HBase Performance-enhanced Edition.

  • hbase.client.username: Required. This parameter is required only for ApsaraDB for HBase Performance-enhanced Edition clusters. To find the username, log on to the ApsaraDB for HBase console. On the Clusters page, click the name of the target cluster. In the left-side navigation pane, click Database Connection. Then, find the Default Username in the Connection Information section.

  • hbase.client.password: Required. This parameter is required only for ApsaraDB for HBase Performance-enhanced Edition clusters. To find the password, log on to the ApsaraDB for HBase console. On the Clusters page, click the name of the target cluster. In the left-side navigation pane, click Database Connection. Then, find the Default Password in the Connection Information section.

  • 'hbase.zookeeper.property.clientPort'='{2181|30020}': Optional. The ZooKeeper port for ApsaraDB for HBase. The default ZooKeeper port is 2181 for ApsaraDB for HBase Standard Edition and 30020 for ApsaraDB for HBase Performance-enhanced Edition.

  • 'mcfed.zookeeper.session.timeout'='<value>': Optional. The ZooKeeper session timeout. Unit: milliseconds. A value of 30000 is recommended.

  • 'hbase.client.retries.number'='<value>': Optional. The number of connection retries for the HBase client.

  • 'networklink'='<networklink_name>': Required. The name of the MaxCompute network connection that links to the VPC where the ApsaraDB for HBase instance resides.

  Note

  Configurations that start with mcfed.zookeeper and hbase are ZooKeeper-related configurations.

Examples

1. Log on to the MaxCompute client and run the CREATE EXTERNAL TABLE statement to create an ApsaraDB for HBase external table.

   Examples:

   • ApsaraDB for HBase Standard Edition

     set odps.sql.hive.compatible = true;
     create external table if not exists mf_hbase_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_hbase_test_t',
     'hbase.zookeeper.quorum'='hb-2zef****-master1-001.hbase.rds.aliyuncs.com,hb-2zef****-master2-001.hbase.rds.aliyuncs.com,hb-2zef****-master3-001.hbase.rds.aliyuncs.com',
     'hbase.zookeeper.property.clientPort'='2181',
     'networklink'='networklink4hbase');

   • ApsaraDB for HBase Performance-enhanced Edition

     set odps.sql.external.net.vpc=true;
     set odps.sql.hive.compatible = true;
     set odps.sql.hbase.version=lindorm;
     CREATE EXTERNAL TABLE mf_hbase_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_hbase_test_t',
     'hbase.zookeeper.quorum'='hb-2zef****-master1-001.hbase.rds.aliyuncs.com,hb-2zef****-master2-001.hbase.rds.aliyuncs.com,hb-2zef****-master3-001.hbase.rds.aliyuncs.com',
     'hbase.zookeeper.property.clientPort'='2181',
     'networklink'='networklink4hbase');

2. Run the SELECT command to query the table and verify that the association is successful.

   Example:

   select * from mf_hbase_test_t_ext;

   The following result is returned:

   +------+------------+------------+
   | key  | col1       | col2       |
   +------+------------+------------+
   | 1    | hello      | NULL       |
   +------+------------+------------+

3. Run the INSERT OVERWRITE statement to write data to the table in ApsaraDB for HBase.

   Example:

   INSERT OVERWRITE TABLE mf_hbase_test_t_ext SELECT '2', 'hbase', 'mftestinsert';

4. Run the SELECT statement to verify the written data.

   Example:

   select * from mf_hbase_test_t_ext;

   The following result is returned:

   +------+------------+--------------------+
   | key  | col1       | col2               |
   +------+------------+--------------------+
   | 1    | hello      | NULL               |
   | 2    | hbase      | mftestinsert       |
   +------+------------+--------------------+