All Products
Search

This Product

    ApsaraDB for HBase:Data query

    Document Center

    ApsaraDB for HBase:Data query

    Last Updated:Jul 10, 2024

    You may need to query a data entry stored in ApsaraDB for HBase when you develop, debug, or maintain your services. You can use HBase Shell to create Get and Scan queries. You can also use the easy-to-use SQL query interface in Lindorm Insight to query data. In the interface, you can use the SQL syntax to query data from ApsaraDB for HBase Performance-enhanced Edition (Lindorm) tables.

    Prerequisites

    You are logged on to Lindorm Insight of your cluster. For more information, see Log on to Lindorm Insight.

    Limits

    • To ensure data security, Lindorm returns only up to 100 entries for each query.

    • The ROW field is equivalent to RowKey in Lindorm. The data types of the ROW and qualifier fields are varbinary. If the qualifier field does not belong to a family, you must specify a family.

    • The ROW and COL fields are reserved fields in SQL. When you use these fields in queries, you must enclose the fields in backticks (`). When you specify a family for the qualifier field, you must also enclose the field in backticks (`).

    Data query (Compatible with HBase)

    In the Data query (Compatible with HBase) module, you can query the detailed information about a specified table or add attributes to existing tables.

    Query existing data

    You can query existing data in SQL tables and HBase tables.

    1. In the left-side navigation pane of the cluster management system, choose Data Query > Data query (Compatible with HBase).

    2. Specify filter conditions, including namespace and Table name.

    3. (Optional) specify other conditions, including Filter column family, row key, start key, end key, and limit.

    4. Click Query. The existing data in the specified table is displayed.

    Add data to HBase tables

    You can add data only to HBase tables but not SQL tables in the cluster management system.

    1. Click Add.

    2. In the Add table data dialog box, specify the parameters described in the following table.

      Parameter

      Description

      row key

      The rowkey of the HBase table.

      Col

      The column that you want to add to the table. You must specify a column family in the value of this parameter. The value of this parameter is in the following format: <Column family name:Column name>. Example: f:name.

      Timestamp

      Only Unix timestamps are supported.

      Value

      The value of the data. For more information about the supported data types, see Basic data types.

    3. Click OK to add a row of data to the table.

    Modify existing data

    You can modify existing data in SQL tables and HBase tables.

    1. Click Edit in the Operation column corresponding to the row that you want to modify.image.png

    2. In the Edit table data dialog box, modify the timestamp and data value.

    3. Click OK to save the modified data.

    SQL Executor

    1. In the left-side navigation pane of the cluster management system, choose Data Query > SQL Executor.

    2. Select a namespace from the namespace drop-down list. Tables in the namespace are displayed on the right side of the page.

    3. (Optional): Obtain hexadecimal strings. For more information, see Obtain hexadecimal strings.

    4. Enter the SQL statement for query in the editor.

    5. Click Execute.

      Note

      You can also use the following shortcut keys to execute the SQL statement: Windows: CTRL + ENTER. macOS: COMMAND + RETURN.

    Obtain hexadecimal strings

    To query varbinary data with conditions or the specified row in an HBase table, you must use hexadecimal strings as values. For example, to query data whose rowkey is r1, set the WHERE clause to WHERE rowkey='7321' in the SQL statement. The hexadecimal string of r1 is 7321. The Data Query page of the cluster management system provides a conversion tool for you to convert values to hexadecimal strings.

    1. In the left-side navigation pane of the cluster management system, choose Data Query > SQL Executor.

    2. Select a namespace from the namespace drop-down list. Tables in the namespace are displayed on the right side of the page.

    3. Obtain the hexadecimal string of the value.

      • Method 1: Click Execute to view the schema of the table. Enter the string that you want to convert in the Binary to hex string conversion field. You can obtain the hexadecimal string at the top of the editor section.image.png

      • Method 2: Turn on the HexString switch and then click Execute. Obtain the hexadecimal string corresponding to the value in the execution results.

    4. Enter the SQL statement that you want to execute and replace the condition value in the statement with the converted hexadecimal string.

      For example, if the original SQL statement that you want to execute is: SELECT * FROM task WHERE ROW = "xxxx1";, the statement in which the value is replaced with a hexadecimal string is SELECT * FROM task WHERE ROW = "7878787831";

    1. Click Execute.