Lindorm:Use SQL to access an HBase table

Prerequisites

Before you begin, ensure that you have:

• LindormTable version 2.6.4 or later. To check or upgrade your version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance

How column mapping works

HBase tables are schema-free. Columns store raw bytes (VARBINARY), and column names are dynamic. LindormTable's column mapping feature bridges this gap: declare the Java serialization type used when writing each qualifier, and Lindorm SQL can decode the bytes and expose the columns as typed SQL columns — enabling WHERE filters, secondary indexes, and search indexes on HBase data.

For more information about dynamic columns, see Dynamic columns.

Column mapping syntax

Use ALTER TABLE to add or remove column mappings:

-- Add a mapping
ALTER TABLE <table_name> MAP DYNAMIC COLUMN [<family>:]<qualifier> <hbase_type>;

-- Remove one or more mappings
ALTER TABLE <table_name> UNMAP DYNAMIC COLUMN [<family>:]<qualifier> [, ...];

Supported hbase_type values

Usage notes

• Adding a mapping declares the type for a column — it does not require data to already exist in that column.

• Rowkey mappings are supported on LindormTable 2.5.1 and later. Enclose the rowkey identifier in backticks: ` ROW `.

• In languages other than Java, use the toBytes method from org.apache.hadoop.hbase.util.Bytes. Strings are always encoded as UTF-8.

Common mistakes

Mapping type does not match the write path

This is the most critical correctness risk. If column f:age was written with Bytes.toBytes(int), map it as HINTEGER. If you map it as HSTRING instead, Lindorm SQL calls Bytes.toString() on 4 raw integer bytes and returns garbage.

Example of a correct mapping:

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

String age2 = "25";
put.addColumn(Bytes.toBytes("f"), Bytes.toBytes("age2"), Bytes.toBytes(age2));
// Map as HSTRING

Mixing these up is the most common source of incorrect query results.

Prepare sample data

The steps below use the following HBase table and data. If you already have an HBase table, skip to Map column qualifiers.

The sample creates a table named dt with column family f1 and writes one row using ApsaraDB for HBase API for Java. To use HBase Shell instead, see Use Lindorm Shell to connect to LindormTable.

// Create table dt 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
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);
}

Map column qualifiers

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

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

2. Map the rowkey and all qualifiers to their corresponding Lindorm SQL types:

   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;

3. Verify the mapping by running DESCRIBE:

   DESCRIBE dt;

   For more information about the DESCRIBE syntax, see DESCRIBE/SHOW/USE.

4. Query the table:

   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 indexes (optional)

Adding indexes improves query performance but requires additional storage. Choose the index type based on your query pattern:

Remove column mappings (optional)

Remove a single mapping:

ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:isMale;

Remove multiple mappings in one statement:

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

What's next

• Dynamic columns

• DESCRIBE/SHOW/USE

• CREATE INDEX

• Secondary indexes