ApsaraDB for HBase:Use SQL statements to access an ApsaraDB for HBase table

Syntax

In Lindorm SQL, you can add mappings for the qualifiers in a custom column family in an ApsaraDB for HBase table. This way, you can use the SQL statements for subsequent queries.

You can use the following syntax to add and remove mappings:

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 can be specified by the hbase_type parameter.

Data preparations

In this topic, ApsaraDB for HBase API for Java is used to create a sample ApsaraDB for HBase table and write data to the table. For more information, see Use ApsaraDB for HBase API for Java to develop applications.

// Create a sample ApsaraDB for HBase table named dt and specify f1 as the column family name.           
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);
            }
    
// Write data to the sample table.
try (Table table = connection.getTable(TableName.valueOf("dt"))) {
    byte[] rowkey = Bytes.toBytes("row1");
    byte[] family = Bytes.toBytes("f1");
    Put put = new Put(rowkey);
    // Write data of the STRING type and specify name as the column name.
    String name = "Some one";
    put.addColumn(family, Bytes.toBytes("name"), Bytes.toBytes(name));
    // Write data of the INT type and specify age as the column name.
    int age = 25;
    put.addColumn(family, Bytes.toBytes("age"), Bytes.toBytes(age));
    // Write data of the LONG type and specify time as the column name.
    long timestamp = 1656675491000L;
    put.addColumn(family, Bytes.toBytes("time"), Bytes.toBytes(timestamp));
    // Write data of the SHORT type and specify buycode as the column name.
    short buycode = 123;
    put.addColumn(family, Bytes.toBytes("buycode"), Bytes.toBytes(buycode));
    // Write data of the FLOAT type and specify price as the column name.
    float price = 12.3f;
    put.addColumn(family, Bytes.toBytes("price"), Bytes.toBytes(price));
    // Write data of the DOUBLE type and specify price2 as the column name.
    double price2 = 12.33333;
    put.addColumn(family, Bytes.toBytes("price2"), Bytes.toBytes(price2));
    // Write data of the BOOLEAN type and specify isMale as the column name.
    boolean isMale = true;
    put.addColumn(family, Bytes.toBytes("isMale"), Bytes.toBytes(isMale));

    // Write null values for all types of data.
    //put.addColumn(family, qualifier, null);

    table.put(put);
    }

Procedure

The following example shows how to create an ApsaraDB for HBase table named dt and use SQL statements to access the ApsaraDB for HBase table.

1. Obtain the endpoint of an ApsaraDB for HBase cluster.

   1. Log on to the ApsaraDB for HBase console.

   2. On the Clusters page, find the instance that you want to manage and click the instance ID.

   3. In the left-side navigation pane, click Database Connection.

   4. Obtain the value of Java API Address.

2. Change the value of Java API Address to the endpoint that is used by SQL statements to access the ApsaraDB for HBase table. To change the value of Java API Address, perform the following steps:

   1. Add the jdbc:lindorm:table:url=http:// prefix before the value of Java API Address.

   2. Change the port number in the value of Java API Address from 30020 to 30060.

      Note

      If the endpoint obtained in the ApsaraDB for HBase console is ld-bp1ietqp4fby3****-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30020, the actual connection string that is used by SQL statements to access the ApsaraDB for HBase table must be jdbc:lindorm:table:url=http://ld-bp1ietqp4fby3****-proxy-hbaseue.hbaseue.rds.aliyuncs.com:30060.

3. Use Lindorm-cli to connect to and use LindormTable. For more information about how to connect to and use LindormTable, see Use Lindorm-cli to connect to and use LindormTable.

4. Execute the ALTER TABLE statement to add mappings for the data that is written to the columns of 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 column mappings refers to specifying data types for the columns regardless of whether to write data to the columns.

   • The system converts the bytes back to the original data based on schemas. You must specify a correct data type mapped to a SQL data type in the Lindorm SQL statement.

   In the following example, if you specify HINTEGER as the data type for the column named f:age2, the system may retrieve incorrect data by invoking the Bytes.toInt() function.

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

5. Execute the DESCRIBE statement to view the mappings of the current schema.

   DESCRIBE dt;

   Note

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

6. Query data in the dt table by using the following SQL statements:

   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;

7. 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. Modify the attribute of the primary table named dt.

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

      Note

      If you use a custom timestamp, you must set the attribute of the primary table to MUTABLE_ALL.

   2. Create a secondary index.

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

   3. If you specify the async parameter when you create the secondary index, build historical data from the primary table to an index table. Then, you can query historical data by using the secondary index. Skip this step if you do not specify the async parameter when you create the secondary index.

      BUILD INDEX idx ON dt;

   4. View the index.

      SHOW INDEX FROM dt;

      The following result is returned:

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

      • If the value of INDEX_STATE in the returned result is ACTIVE, the index is built.

      • The value of INDEX_PROGRESS in the returned result specifies the progress of index building.

   5. (Optional) Execute the EXPLAIN statement to view the execution plan. You can check whether the secondary index is hit.

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

8. (Optional) Remove one or more column mappings.

   • Remove one column mapping. The following sample code provides an example:

     ALTER TABLE dt UNMAP DYNAMIC COLUMN f1:isMale;

   • Remove multiple column mappings. The following sample code provides an example:

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