Lindorm:Access HBase tables using SQL

Syntax

In Lindorm SQL, you can add mappings to qualifiers in custom column families of an HBase table to facilitate SQL queries.

The syntax for adding and removing mappings is as follows:

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

The following table describes the mapping data types that hbase_type can specify:

Data preparation

The following example uses HBase Java API. For more information, see Use ApsaraDB for HBase API for Java to develop applications.

// Create the HBase sample table named "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 data
try (Table table = connection.getTable(TableName.valueOf("dt"))) {
    byte[] rowkey = Bytes.toBytes("row1");
    byte[] family = Bytes.toBytes("f1");
    Put put = new Put(rowkey);
    // Write a String value, column name "name"
    String name = "Some one";
    put.addColumn(family, Bytes.toBytes("name"), Bytes.toBytes(name));
    // Write an Int value, column name "age"
    int age = 25;
    put.addColumn(family, Bytes.toBytes("age"), Bytes.toBytes(age));
    // Write a Long value, column name "time"
    long timestamp = 1656675491000L;
    put.addColumn(family, Bytes.toBytes("time"), Bytes.toBytes(timestamp));
    // Write a Short value, column name "buycode"
    short buycode = 123;
    put.addColumn(family, Bytes.toBytes("buycode"), Bytes.toBytes(buycode));
    // Write a Float value, column name "price"
    float price = 12.3f;
    put.addColumn(family, Bytes.toBytes("price"), Bytes.toBytes(price));
    // Write a Double value, column name "price2"
    double price2 = 12.33333;
    put.addColumn(family, Bytes.toBytes("price2"), Bytes.toBytes(price2));
    // Write a Boolean value, column name "isMale"
    boolean isMale = true;
    put.addColumn(family, Bytes.toBytes("isMale"), Bytes.toBytes(isMale));

    // Write a null value. For all types, writing null is expressed as:
    //put.addColumn(family, qualifier, null);

    table.put(put);
    }

Procedure

The following example uses the sample table dt to describe how to access an HBase table by using SQL.

1. Connect to the wide-table engine by using Lindorm-cli. For more information, see Connect to and use the wide table engine with Lindorm-cli.

   Note

   If you use SQL to access an HBase table in ApsaraDB for HBase Enhanced Edition, construct the address obtained in the console into the format of jdbc:lindorm:table:url=http://Java API address obtained in the console. Change the port from 30020 to 30060.

   For example, if the connection string address obtained in the console is ld-bp1ietqp4fby3****-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30020, the converted connection string address is jdbc:lindorm:table:url=http://ld-bp1ietqp4fby3****-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30060.

2. Use the ALTER TABLE statement to add column mappings to the data written to the dt table.

   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;

   Note

   • Adding a column mapping specifies the data type of the column, regardless of whether data is written.

   • The system decodes the original value from Bytes based on the schema. Therefore, you must use the correct data type when mapping to Lindorm SQL.

   In the following example, if you specify the data type of the f:age2 column as HINTEGER, the system calls the Bytes.toInt() method and returns an incorrect original value.

   int age = 25;
   byte[] ageValue = Bytes.toBytes(age);
   put.addColumn(Bytes.toBytes("f"), Bytes.toBytes("age"), ageValue);// The data type of column f:age is INT, and it is mapped to HINTEGER in Lindorm SQL.
   String age2 = "25";
   byte[] age2Value = Bytes.toBytes(age2);
   put.addColumn(Bytes.toBytes("f"), Bytes.toBytes("age2"), age2Value);// The data type of column f:age2 is STRING, and it is mapped to HSTRING in Lindorm SQL.

3. Use the DESCRIBE statement to view the mapping relationships of the current schema.

   DESCRIBE dt;

   Note

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

4. Query the data in the dt table by using a SQL statement.

   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;

5. (Optional) Create a secondary index.

   A secondary index trades space for time. It improves the query efficiency of non-primary-key query patterns but occupies some storage space. For more information about the syntax and usage limits of secondary indexes, see CREATE INDEX and Secondary indexes.

   1. Modify the properties of the primary table dt.

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

      Note

      If custom timestamps are used, set the primary table property to MUTABLE_ALL.

   2. Create the secondary index:

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

   3. Optional: If your wide-table engine version is earlier than 2.6.3 and you use the async parameter (asynchronous index build) when creating a secondary index, manually build the historical data in the primary table into the index table. After the build is complete, you can query the historical data by using the secondary index. If the async parameter is not used during creation, you can skip this step.

      BUILD INDEX idx ON dt;

   4. View the index.

      SHOW INDEX FROM dt;

      The result:

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

      Note

      • When INDEX_STATE in the return value is Active, the data build is complete.

      • PINDEX_PROGRESS in the return value indicates the progress of the index build.

   5. Optional: Use the EXPLAIN statement to view the execution plan and check whether a secondary index is hit.

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

6. Optional: Create a search index.

   1. Create a search index.

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

      Note

      If you create a search index on an HBase table by using SQL, note the following limits for each search index column:

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

      • The supported data types are consistent with the mappable data types. For more information, see Mapping data types.

      • You cannot remove the mapping of a search index column. Otherwise, the query results will be incorrect.

      • If you use custom timestamps to write to the HBase table and you need to create a search index, you must set the MUTABILITY property of the table to MUTABLE_ALL.

   2. Check whether the index is created successfully.

      SHOW INDEX FROM dt;

      The result:

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

7. Optional: Remove column mappings.

   • Remove a column mapping. The following is a sample:

     ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:isMale;

   • Remove multiple column mappings. The following is a sample:

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