MaxCompute:ApsaraDB for HBase external tables (Standard Edition or Performance-enhanced Edition)

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB for HBase Standard Edition or Performance-enhanced Edition cluster with environment configuration complete.

  • Standard Edition: Quick start for ApsaraDB for HBase Standard Edition

  • Performance-enhanced Edition: Quick start for ApsaraDB for HBase Performance-enhanced Edition

• A table created in the HBase cluster using an HBase client. This topic uses mf_hbase_test_t as the example table, created with the following commands:

  # Create an HBase table with column family "f"
  create 'mf_hbase_test_t', 'f'
  # Insert a row: RowKey=1, column f:col1, value "hello"
  put 'mf_hbase_test_t', '1', 'f:col1', 'hello'

  For HBase Shell commands, see Use HBase Shell.

• A MaxCompute project. See Create a MaxCompute project.

• The MaxCompute client installed and configured. See Install and configure the MaxCompute client.

• A VPC network connection established between MaxCompute and ApsaraDB for HBase, with the ApsaraDB for HBase whitelist configured. See Configure a whitelist and Network connection process.

Limitations

• Supported 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 VPC access details, see VPC access solution (leased line connection).

• VPC access scope: After connecting, MaxCompute can only access the VPC specified by its VPC ID. To access a different region or another VPC in the same region, establish a separate connection between the VPCs.

• HBase version: Only HBase 2.x clusters are supported.

• Unsupported cluster types: EMR HBase clusters are not supported.

• Unsupported properties: The cluster property is not supported.

Syntax

Both editions use the same CREATE EXTERNAL TABLE statement structure. Enable Hive compatibility mode before running the statement. For Performance-enhanced Edition, also enable VPC network access and set the HBase version to lindorm.

-- Required for all clusters: enable Hive compatibility mode
set odps.sql.hive.compatible = true;

-- Required for Performance-enhanced Edition only:
-- set odps.sql.external.net.vpc=true;
-- set odps.sql.hbase.version=lindorm;

CREATE EXTERNAL TABLE IF NOT EXISTS <mc_hbase_external>
(
  <col1_name> <data_type>,
  <col2_name> <data_type>,
  ...
)
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'='<hbase_table_name>',
  'hbase.zookeeper.quorum'='<VPC ip1|host1:port,VPC ip2|host2:port,...>',
  -- Performance-enhanced Edition only:
  'hbase.client.username'='<username>',
  'hbase.client.password'='<password>',
  -- Optional:
  'hbase.zookeeper.property.clientPort'='<port>',
  'mcfed.zookeeper.session.timeout'='<milliseconds>',
  'hbase.client.retries.number'='<count>',
  'networklink'='<networklink_name>'
);

Table and column names are not case-sensitive and cannot be forced to a specific case.

Column mapping rules

The hbase.columns.mapping value is a comma-delimited string that maps each external table column to an HBase column. Follow these rules:

• One entry per column: The number of entries must match the number of columns in the external table. Do not include whitespace between entries — it will be treated as part of the column name.

• RowKey must come first: The :key entry maps to the HBase RowKey and must be the first item in the mapping string.

• Column format: Each non-RowKey entry uses the format column-family:column-name (for example, f:col1).

• One column family per job: Only one column family can be specified per job.

• Fewer columns than HBase: External table columns with no matching HBase column are filled with NULL.

• More columns than HBase: HBase columns with no mapping in the external table are ignored.

Create an HBase external table

This example creates an external table for mf_hbase_test_t (an HBase table with column family f, columns col1 and col2), queries it, and writes data back.

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'
);

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'
);

SELECT * FROM mf_hbase_test_t_ext;

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

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

SELECT * FROM mf_hbase_test_t_ext;

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

Load data to HBase using bulk load

To import large amounts of data into HBase, you can use the HBase bulk load method. This method first generates HFiles, which are the underlying storage files for HBase, and then directly moves these HFiles to the HBase storage directory. Compared with calling the `HTable.put` online interface, this method is more efficient and has less impact on HBase operations because it bypasses the HBase RegionServer and Write-ahead Logging (WAL).

1. Log on to the MaxCompute client and run the CREATE EXTERNAL TABLE statement to create an external table associated with the schema of the table in HBase.

   The following shows the DDL statement:

   set odps.sql.hive.compatible=true;     -- Enable Hive compatibility mode.
   create external table if not exists <mc_hfile_hbase_external>
   (
    <col1_name> <data_type>,
    <col2_name> <data_type>
   )
   stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  -- The handler for the HBase data source.
   with serdeproperties ('hbase.table.name'='<hbase_table_name>',
                         'hbase.columns.mapping'='<:key,col-family:col-name1,col-family:col-name2,...>', 
                         'odps.external.net.vpc'='true',
                         'odps.vpc.id'='<VPC ID>',
                         'odps.vpc.access.ips'='<VPC ip:port,VPC ipBegin-ipEnd:port|network_segment:port>',
                         'odps.vpc.region'='<RegionID>')
   location '<hbase://VPC ip:port>'   
   tblproperties (
   'hbase.table.name'='<hbase_table_name>',
   'hbase.columns.mapping'='<:key,col-family:col-name1,col-family:col-name2,...>',
   'hfile.temp.path'='<hdfs://hbase-cluster/hfiletmp>',
   'hadoop.user.name'='<admin>',
   'mcfed.fs.defaultFS'='<hdfs://hbase-cluster>',
   'mcfed.dfs.nameservices'='<hbase-cluster>',
   'mcfed.dfs.ha.namenodes.hbase-cluster'='nn1,nn2',
   'mcfed.dfs.namenode.rpc-address.hbase-cluster.nn1'='j63g09343.sqa.eu95:8020',
   'mcfed.dfs.namenode.rpc-address.hbase-cluster.nn2'='j63g09355.sqa.eu95:8020',
   'mcfed.dfs.client.failover.proxy.provider.hbase-cluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider',
   'mcfed.zookeeper.session.timeout'='<value>',
   'hbase.client.retries.number'='<value>',
   'hbase.zookeeper.quorum'='<VPC ip1,VPC ip2...>',
   'hbase.zookeeper.property.clientPort'='2181'

   tblproperties:

   • hfile.temp.path: The storage directory for temporary HFiles. Ensure that this directory is used only by MaxCompute to write HFiles. When you specify this configuration item, MaxCompute first writes data to this directory, and then runs the HBase BulkLoad command to import the HFiles into HBase.

   • hadoop.user.name: The user who writes HFiles in Hadoop Distributed File System (HDFS).

   • Configuration items that start with mcfed.fs.defaultFS and mcfed.dfs are related to HDFS High Availability (HA) configuration. For more information, see the DDL statement.

   • 'hbase.zookeeper.quorum'='ip_address:port|ip_address_range:port|host_name': The IP address of the ZooKeeper for HBase. You can also set this to the ZooKeeper host name.

   Sample command:

   set odps.sql.hive.compatible=true;
   create external table if not exists test_hfile_hbase_external
   (
   id string, cfa string, cfb string
   )
   stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
   with serdeproperties ('hbase.table.name'='htest',
                         'hbase.columns.mapping'=':key,cf:a,cf:b',
                         'odps.external.net.vpc'='true',
                         'odps.vpc.id'='vpc-uf630ldmi9l0rrr01****',
                         'odps.vpc.access.ips'='192.0.2.0-192.0.2.10:8020,192.0.2.15-192.0.2.20:2181',
                         'odps.vpc.region'='cn-shanghai')
   location 'hbase://192.0.2.0:2181'
   tblproperties (
   'hbase.table.name'='htest',
   'hbase.columns.mapping'=':key,cf:a,cf:b',
   'hfile.temp.path'='hdfs://hbase-cluster/hfiletmp',
   'hadoop.user.name'='admin',
   'mcfed.fs.defaultFS'='hdfs://hbase-cluster',
   'mcfed.dfs.nameservices'='hbase-cluster',
   'mcfed.dfs.ha.namenodes.hbase-cluster'='nn1,nn2',
   'mcfed.dfs.namenode.rpc-address.hbase-cluster.nn1'='j63g09343.sqa.eu95:8020',
   'mcfed.dfs.namenode.rpc-address.hbase-cluster.nn2'='j63g09355.sqa.eu95:8020',
   'mcfed.dfs.client.failover.proxy.provider.hbase-cluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider',
   'mcfed.zookeeper.session.timeout'='30',
   'hbase.client.retries.number'='1',
   'hbase.zookeeper.quorum'='j63g11301.sqa.eu95,j63g11306.sqa.eu95,j63g09367.sqa.eu95',
   'hbase.zookeeper.property.clientPort'='2181');

2. Run the INSERT OVERWRITE statement to write data to the table in HBase and perform a global sort.

   • To perform a global sort on the MaxCompute source table, you can use the range-clustered table mechanism of MaxCompute. When the optimizer generates an execution plan and detects that data is being written to an HBase table, it treats the table as a range-clustered table. The execution plan then performs a global sort on the source table. Sample command:

     INSERT OVERWRITE TABLE test_hfile_hbase_external SELECT uuid(), a_name,a_age FROM hy_test1;

     The generated execution plan is shown in the following figure. In the M1 step, the data in the source table (hy_test1) is globally sorted by RowKey.

   • After you run the INSERT OVERWRITE statement, MaxCompute automatically writes the data to HDFS in HFile format. The temporary path for the HFiles is specified by the hfile.temp.path parameter.

     Each Fuxi instance corresponds to a region of the HBase external table, which is the R2_1 step in the preceding figure. In this step, the sorted data is written to HDFS in HFile format.

     Note

     When you write HFiles, note the following two limits:

     • The first column must be the RowKey, which is :key in hbase.columns.mapping.

     • The remaining columns must belong to a single column family. This ensures that each instance writes to only one HFile.

   • Move the HFiles to the HBase storage directory and notify the HBase RegionServer to load them.

     After the HFiles for all regions of the HBase external table are written to HDFS, MaxCompute automatically runs the bulk load operation. This operation moves the HFiles to the HBase storage directory and notifies the HBase RegionServer to load them.

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

   Sample command:

   select * from cluster_test_hbase_external;

Related topics

• Configure a whitelist

• Network connection process

• Lindorm external tables