使用SQL访问HBase表_云原生多模数据库 Lindorm(Lindorm)-阿里云帮助中心

本文介绍如何使用SQL语句访问HBase表。

前提条件

宽表引擎为2.6.4及以上版本。如何查看或升级当前版本，请参见宽表引擎版本说明和升级小版本。

背景信息

Lindorm宽表引擎支持直接访问通过Lindorm Shell或HBase Java API创建的数据表。但由于HBase是Schema-free的，因此HBase中的列会被当做动态列处理，类型为VARBINARY，即Byte。关于动态列的详细信息，请参见动态列。为了能够在基于HBase API写入的列上使用Lindorm SQL，同时使用丰富的数据类型和二级索引，云数据库HBase提供了HBase列映射功能以及HBase兼容类型。

语法

在Lindorm SQL中，对HBase表中自定义Column Family中的Qualifier添加映射，方便后续使用SQL进行查询。

添加和移除映射的语法如下：

dynamic_column_mapping_statement   := ALTER TABLE table_name MAP DYNAMIC COLUMN
                                      qualifer_definition hbase_type;
dynamic_column_unmapping_statement := ALTER TABLE table_name UNMAP DYNAMIC COLUMN
                                      qualifer_definition_list;
qualifer_definition_list           := qualifer_definition
                                      (',' qualifer_definition)*
qualifer_definition                := [ family_name ':' ] qualifier_name
hbase_type                         := HLONG | HINTEGER | HSHORT | HFLOAT |
                                      HDOUBLE | HSTRING | HBOOLEAN

其中，hbase_type可指定的映射数据类型如下表所示：

数据类型	对应的Java类型	描述
HLONG	java.lang.Long	使用Bytes.toBytes(long)方式写入HBase的列。
HINTEGER	java.lang.Integer	使用Bytes.toBytes(int)方式写入HBase的列。
HSHORT	java.lang.Short	使用Bytes.toBytes(short)方式写入HBase的列。
HFLOAT	java.lang.Float	使用Bytes.toBytes(float)方式写入HBase的列。
HDOUBLE	java.lang.Double	使用Bytes.toBytes(double)方式写入HBase的列。
HSTRING	java.lang.String	使用Bytes.toBytes(String)方式写入HBase的列。
HBOOLEAN	java.lang.Boolean	使用Bytes.toBytes(boolean)方式写入HBase的列.

说明

宽表引擎版本2.5.1及以上版本支持对Rowkey的映射，映射方法与其他Qualifier相同。映射对象固定为ROW且ROW关键字需要用反引号（``）引用。
如果使用其他语言，您可以参考Java类org.apache.hadoop.hbase.util.Bytes中的toBytes方法对数据进行编码写入。
Java中Bytes.toBytes(String)采用UTF-8编码，其他语言利用toBytes将String转成Bytes时，也需要使用UTF-8编码。

数据准备

以HBase Java API为例，具体操作，请参见基于HBase Java API的应用开发。

说明

其他的建表方式及数据写入方式，请参见通过Lindorm Shell访问宽表引擎。

//创建名为dt，family名为f1的HBase示例表           
try (Admin admin = connection.getAdmin()) {
            Table table = connection.getTable(TableName.valueOf("dt"));
            HTableDescriptor htd = new HTableDescriptor(TableName.valueOf("dt"));
            htd.addFamily(new HColumnDescriptor(Bytes.toBytes("f1")));
            admin.createTable(htd);
            }
    
//写入数据
try (Table table = connection.getTable(TableName.valueOf("dt"))) {
    byte[] rowkey = Bytes.toBytes("row1");
    byte[] family = Bytes.toBytes("f1");
    Put put = new Put(rowkey);
    //写入String类型,列名为name
    String name = "Some one";
    put.addColumn(family, Bytes.toBytes("name"), Bytes.toBytes(name));
    //写入Int类型，列名为age
    int age = 25;
    put.addColumn(family, Bytes.toBytes("age"), Bytes.toBytes(age));
    //写入Long类型，列名为time
    long timestamp = 1656675491000L;
    put.addColumn(family, Bytes.toBytes("time"), Bytes.toBytes(timestamp));
    //写入Short类型，列名为buycode
    short buycode = 123;
    put.addColumn(family, Bytes.toBytes("buycode"), Bytes.toBytes(buycode));
    //写入Float类型，列名为price
    float price = 12.3f;
    put.addColumn(family, Bytes.toBytes("price"), Bytes.toBytes(price));
    //写入Double类型，列名为price2
    double price2 = 12.33333;
    put.addColumn(family, Bytes.toBytes("price2"), Bytes.toBytes(price2));
    //写入Boolean类型，列名为isMale
    boolean isMale = true;
    put.addColumn(family, Bytes.toBytes("isMale"), Bytes.toBytes(isMale));

    //写入null值，所有类型写入空值null都表达为
    //put.addColumn(family, qualifier, null);

    table.put(put);
    }

操作步骤

以下以访问示例表dt为例，介绍如何使用SQL访问HBase表。

通过Lindorm-cli连接并使用宽表引擎。具体操作，请参见通过Lindorm-cli连接并使用宽表引擎。
说明
如果您在HBase增强版中使用SQL访问HBase表，需要将控制台获取的地址拼接成jdbc:lindorm:table:url=http://控制台上获取的Java API地址的形式，端口需要由30020修改为30060。
例如：在控制台上获取的连接串地址为ld-bp1ietqp4fby3****-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30020，转换后的连接串地址为jdbc:lindorm:table:url=http://ld-bp1ietqp4fby3****-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30060。

使用ALTER TABLE语句对写入dt表的数据添加列映射。

ALTER TABLE dt MAP DYNAMIC COLUMN `ROW` HSTRING, f1:name HSTRING, f1:age HINTEGER, f1:time HLONG, f1:buycode HSHORT, f1:price HFLOAT, f1:price2 HDOUBLE, f1:isMale HBOOLEAN;

说明

添加列映射是指定列的数据类型，与是否写入数据无关。
系统会根据Schema从Bytes中反解出原始数值，因此映射到Lindorm SQL时必须使用正确的数据类型。

以下示例中，如果用户把f:age2列的数据类型写为HINTEGER，系统调用Bytes.toInt()方法会反解出错误的原始值。

int age = 25;
byte[] ageValue = Bytes.toBytes(age);
put.addColumn(Bytes.toBytes("f"), Bytes.toBytes("age"), ageValue);//f:age列的数据类型为INT，映射到Lindorm SQL为HINTEGER类型。
String age2 = "25";
byte[] age2Value = Bytes.toBytes(age2);
put.addColumn(Bytes.toBytes("f"), Bytes.toBytes("age2"), age2Value);//f:age2列的数据类型为STRING，映射到Lindorm SQL为HSTRING类型。

通过DESCRIBE语句查看当前Schema的映射关系。
```
DESCRIBE dt;
```
说明
DESCRIBE TABLE语法的详细信息，请参见DESCRIBE/SHOW/USE。

通过SQL语句查询dt表中的数据。

SELECT * FROM dt LIMIT 1;
SELECT * FROM dt WHERE f1:isMale=true LIMIT 1;
SELECT * FROM dt WHERE f1:name='Some one' LIMIT 1;
SELECT * FROM dt WHERE f1:time>1656675490000 and f1:time<1656675492000 LIMIT 1;

（可选）创建二级索引。
二级索引是一种空间换时间的解决方案，它有利于提升非主键查询模式的查询效率，但需要占用一些存储空间。关于二级索引的语法使用限制，请参见CREATE INDEX和二级索引。
1. 修改主表dt的属性。
```
ALTER TABLE dt SET 'MUTABILITY' = 'MUTABLE_LATEST';
```
  说明
  如果使用了自定义时间戳，主表属性需要设置为MUTABLE_ALL。
2. 创建二级索引。
```
CREATE INDEX idx ON dt(f1:age) WITH (INDEX_COVERED_TYPE ='COVERED_DYNAMIC_COLUMNS');
```
3. 可选：如果您的宽表引擎版本小于2.6.3，创建二级索引时使用了async参数（异步构建索引），您需要手动将主表中的历史数据构建到索引表中。构建完成后，才能通过二级索引查询历史数据。如果创建时没有使用async参数，可跳过此步骤。
```
BUILD INDEX idx ON dt;
```
4. 查看索引。
```
SHOW INDEX FROM dt;
```
  返回结果：
```
+---------------+----------- -+-------------+--------------+------------------+---------------+-----------------+----------------+-------------+
| TABLE_SCHEMA  | DATA_TABLE  | INDEX_NAME  | INDEX_STATE  |  INDEX_PROGRESS  |  INDEX_TYPE   |  INDEX_COVERED  |  INDEX_COLUMN  |  INDEX_TTL  |
+---------------+-------------+-------------+--------------+------------------+---------------+-----------------+----------------+-------------+
| default       | dt          | idx         | ACTIVE       | 100%             | SECONDARY     |  TRUE           |  f1:age,ROW    |             |
+---------------+-------------+-------------+--------------+------------------+---------------+-----------------+----------------+-------------+
```
  说明
  返回值中的INDEX_STATE的值为Active时，说明数据构建完成。
  返回值中PINDEX_PROGRESS的值表示索引构建的进度。
5. 可选：使用EXPLAIN语句查看执行计划，可以查看是否命中二级索引。
```
EXPLAIN SELECT * FROM dt WHERE f1:age=23 LIMIT 1;
```

可选：创建搜索索引。

创建搜索索引。
```
CREATE INDEX search_idx USING SEARCH ON dt(f1:age,f1:name);
```
说明
如果您是通过SQL在HBase表上建立的搜索索引，各个搜索索引列需要注意以下限制：
- 所有搜索索引列需要在列映射关系中定义。
- 支持的数据类型与可映射的数据类型一致，具体说明请参见映射数据类型。
- 不能对搜索索引列解除映射，否则将导致查询结果错误。
- 如果您使用自定义时间戳写入HBase表，且需要创建搜索索引，必须将表的MUTABILITY属性设置为MUTABLE_ALL。

查看索引是否创建成功。

SHOW INDEX FROM dt;

返回结果：

+--------------+------------+------------+-------------+----------------+------------+---------------+----------------+-----------+-------------------+
| TABLE_SCHEMA | DATA_TABLE | INDEX_NAME | INDEX_STATE | INDEX_PROGRESS | INDEX_TYPE | INDEX_COVERED |  INDEX_COLUMN  | INDEX_TTL | INDEX_DESCRIPTION |
+--------------+------------+------------+-------------+----------------+------------+---------------+----------------+-----------+-------------------+
| default      | dt         | idx        | ACTIVE      | DONE           | SECONDARY  | DYNAMIC       | f1:age,ROW     |           |                   |
| default      | dt         | search_idx | BUILDING    | N/A            | SEARCH     | NA            | f1:age,f1:name | 0         |                   |
+--------------+------------+------------+-------------+----------------+------------+---------------+----------------+-----------+-------------------+

可选：删除列映射。
- 移除一个列映射。示例代码如下：
```
ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:isMale;
```
- 移除多个列映射。示例代码如下：
```
ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:price2, f1:price2;
```