使用SQL訪問HBase表 - ApsaraDB for HBase

本文介紹如何使用SQL語句訪問HBase表。

前提條件

寬表引擎為2.4.3.2及以上版本。如何查看或升級目前的版本，請參見寬表引擎版本說明和升級小版本。

背景資訊

Lindorm寬表引擎支援直接存取通過Lindorm Shell或HBase Java API建立的資料表。但由於HBase是Schema-free的，因此HBase中的列會被當做動態列處理，類型為VARBINARY，即Byte。關於動態列的詳細資料，請參見動態列。為了能夠在基於HBase API寫入的列上使用Lindorm SQL，同時使用豐富的資料類型和二級索引，ApsaraDB for 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表。

擷取串連地址。
1. 登入HBase控制台。
2. 在叢集列表頁面，單擊目的地組群ID，進入叢集詳情頁面。
3. 在左側導覽列，選擇資料庫連接。
4. 擷取Java API訪問地址。
將Java API訪問地址修改為SQL訪問HBase表的串連地址。修改方法如下：
1. 在Java API訪問地址前添加jdbc:lindorm:table:url=http://首碼。
2. 將Java API訪問地址的連接埠號碼由30020修改為30060 。
  說明
  假設在控制台上擷取的串連地址為ld-bp1ietqp4fby3****-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30020，則實際用於SQL訪問HBase表的串連串應是jdbc:lindorm:table:url=http://ld-bp1ietqp4fby3****-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30060。
通過Lindorm-cli串連並使用寬表引擎。具體操作，請參見通過Lindorm-cli串連並使用寬表引擎。

使用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和二級索引。

修改主表dt的屬性。
```
ALTER TABLE dt SET 'MUTABILITY' = 'MUTABLE_LATEST';
```
說明
如果使用了自訂時間戳記，主表屬性需要設定為MUTABLE_ALL。

建立二級索引。

CREATE INDEX idx ON dt(f1:age) 'INDEX_COVERED_TYPE'='COVERED_DYNAMIC_COLUMNS';

如果建立二級索引時使用了async參數，您需要手動將主表中的歷史資料構建到索引表中。構建完成後，才能通過二級索引查詢歷史資料。如果建立時沒有使用async參數，可跳過此步驟。
```
BUILD INDEX idx ON dt;
```

查看索引。

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的值表示索引構建的進度。

可選：使用EXPLAIN語句查看執行計畫，可以查看是否命中二級索引。
```
EXPLAIN SELECT * FROM dt WHERE f1:age=23 LIMIT 1;
```

可選：刪除列映射。
- 移除一個列映射。範例程式碼如下：
```
ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:isMale;
```
- 移除多個列映射。範例程式碼如下：
```
ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:price2, f1:price2;
```