ApsaraDB for HBase:Access HBase tables using SQL

Step 1: Prepare sample data

The following Java code creates the dt table and writes one row with columns of each supported type. Skip this step if your HBase table already exists.

For more information about connecting with ApsaraDB for HBase API for Java, see Use ApsaraDB for HBase API for Java to develop applications. To create the table with HBase Shell instead, see Use Lindorm Shell to connect to LindormTable.

// Create the table with column family f1
try (Admin admin = connection.getAdmin()) {
    HTableDescriptor htd = new HTableDescriptor(TableName.valueOf("dt"));
    htd.addFamily(new HColumnDescriptor(Bytes.toBytes("f1")));
    admin.createTable(htd);
}

// Write one row with columns of each supported type
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 = "Some one";
    put.addColumn(family, Bytes.toBytes("name"), Bytes.toBytes(name));       // HSTRING

    int age = 25;
    put.addColumn(family, Bytes.toBytes("age"), Bytes.toBytes(age));          // HINTEGER

    long timestamp = 1656675491000L;
    put.addColumn(family, Bytes.toBytes("time"), Bytes.toBytes(timestamp));   // HLONG

    short buycode = 123;
    put.addColumn(family, Bytes.toBytes("buycode"), Bytes.toBytes(buycode));  // HSHORT

    float price = 12.3f;
    put.addColumn(family, Bytes.toBytes("price"), Bytes.toBytes(price));      // HFLOAT

    double price2 = 12.33333;
    put.addColumn(family, Bytes.toBytes("price2"), Bytes.toBytes(price2));    // HDOUBLE

    boolean isMale = true;
    put.addColumn(family, Bytes.toBytes("isMale"), Bytes.toBytes(isMale));    // HBOOLEAN

    table.put(put);
}

Step 2: Connect to LindormTable

Connect using Lindorm-cli. For connection instructions, see Use Lindorm-cli to connect to and use LindormTable.

For ApsaraDB for HBase Performance-enhanced Edition, change the JDBC URL format to jdbc:lindorm:table:url=http://<API URL for Java> and change the port from 30020 to 30060. For example, change ld-bp1ietqp4fby3**-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30020` to `jdbc:lindorm:table:url=http://ld-bp1ietqp4fby3**-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30060.

Step 3: Add column mappings

Map each column to its corresponding Lindorm SQL type. The mapping type must match the Java type used when writing the data — an incorrect mapping causes the system to return wrong values when decoding the bytes.

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;

To verify the current schema, run:

DESCRIBE dt;

For DESCRIBE syntax details, see DESCRIBE/SHOW/USE.

Step 4: Query data

-- Return one row
SELECT * FROM dt LIMIT 1;

-- Filter by Boolean column
SELECT * FROM dt WHERE f1:isMale=true LIMIT 1;

-- Filter by string column
SELECT * FROM dt WHERE f1:name='Some one' LIMIT 1;

-- Filter by range on a LONG column
SELECT * FROM dt WHERE f1:time>1656675490000 AND f1:time<1656675492000 LIMIT 1;

Step 5: (Optional) Create a secondary index

Secondary indexes are used to reduce data query time at the cost of storage space. Secondary indexes allow you to query data based on non-primary key columns. This improves query efficiency but requires more storage space. For more information about syntax limits on secondary indexes, see CREATE INDEX and Secondary indexes.

1. Set the mutability attribute on the primary table:

   ALTER TABLE dt SET 'MUTABILITY' = 'MUTABLE_LATEST';

   If you use a custom timestamp when writing data, set the attribute to MUTABLE_ALL instead.

2. Create the secondary index:

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

3. (Optional) If you specified the async parameter when creating the index and your LindormTable version is earlier than 2.6.3, build the historical data into the index:

   BUILD INDEX idx ON dt;

4. Verify the index status:

   SHOW INDEX FROM dt;

   Expected output:

   +---------------+-------------+-------------+--------------+------------------+---------------+-----------------+----------------+-------------+
   | 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 means the index is ready. INDEX_PROGRESS shows the build progress.

5. (Optional) Check the execution plan to confirm the index is being used:

   EXPLAIN SELECT * FROM dt WHERE f1:age=23 LIMIT 1;

Step 6: (Optional) Create a search index

Search indexes support more flexible queries across multiple columns.

1. Create the search index:

   CREATE INDEX search_idx USING SEARCH ON dt(f1:age,f1:name);

   Before creating a search index on an HBase table, note these constraints:

   • All columns in the search index must already be defined in the column mappings.

   • Column data types must match those specified in the mappings.

   • Column mappings for indexed columns cannot be removed after the search index is created. Removing them causes incorrect query results.

   • If you write data with a custom timestamp and need a search index, set the MUTABILITY attribute to MUTABLE_ALL.

2. Verify that both indexes are created:

   SHOW INDEX FROM dt;

   Expected output:

   +--------------+------------+------------+-------------+----------------+------------+---------------+----------------+-----------+-------------------+
   | 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         |                   |
   +--------------+------------+------------+-------------+----------------+------------+---------------+----------------+-----------+-------------------+

Step 7: (Optional) Remove column mappings

Remove a single column mapping:

ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:isMale;

Remove multiple column mappings:

ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:price2, f1:price2;