Filter & Query HBase Data via Shell and Lindorm Insight SQL - ApsaraDB for HBase

ApsaraDB for HBase (Lindorm) provides two interfaces for querying and managing table data from the cluster management system: Data query (Compatible with HBase) for GUI-based row lookups and edits, and SQL Executor for running SQL statements against Lindorm tables. You can also use HBase Shell to create Get and Scan queries.

Jump to:

Query existing data
Add data to HBase tables
Modify existing data
SQL Executor
Obtain hexadecimal strings

Prerequisites

Before you begin, make sure you have:

Logged on to Lindorm Insight for your cluster. For more information, see Log on to Lindorm Insight.

Limitations

Lindorm returns up to 100 entries per query to protect data security.
The ROW field is equivalent to RowKey in Lindorm. Both the ROW and qualifier fields are of type varbinary.
If a qualifier field does not belong to a column family, specify a column family for it.
ROW and COL are reserved fields in SQL. Enclose them in backticks (`) when used in queries. When you specify a column family for a qualifier field, you must also enclose the field in backticks.

Data query (Compatible with HBase)

The Data query (Compatible with HBase) module lets you query table details and add data to existing tables from a GUI.

Query existing data

Query existing data from both SQL tables and HBase tables.

In the left-side navigation pane of the cluster management system, choose Data Query > Data query (Compatible with HBase).
Set the namespace and Table name filter conditions.
(Optional) Set additional filters: Filter column family, row key, start key, end key, and limit.
Click Query. The existing data in the specified table is displayed.

Add data to HBase tables

Data can be added only to HBase tables, not SQL tables, through the cluster management system.

Click Add.

In the Add table data dialog box, set the following parameters.

Parameter	Description
row key	The row key of the HBase table.
Col	The column to add, including the column family. Format: `<column family name:column name>`. Example: `f:name`.
Timestamp	The timestamp for the entry. Only Unix timestamps are supported.
Value	The value of the data. For supported data types, see Basic data types.

Click OK.

Modify existing data

Modify existing data in SQL tables or HBase tables.

In the row you want to modify, click Edit in the Operation column.
In the Edit table data dialog box, update the timestamp and data value.
Click OK to save the changes.

SQL Executor

Run SQL statements against Lindorm tables using the SQL Executor in Lindorm Insight.

In the left-side navigation pane of the cluster management system, choose Data Query > SQL Executor.
Select a namespace from the namespace drop-down list. The tables in that namespace are displayed on the right side of the page.
(Optional) If you need to query varbinary data using a specific value, get the hexadecimal string for that value first. See Obtain hexadecimal strings.
Enter a SQL statement in the editor. For example, to preview data in a table:
```
SELECT * FROM `your_table` LIMIT 10;
```
Click Execute.

To execute a SQL statement with a keyboard shortcut, press Ctrl+Enter on Windows or Command+Return on macOS.

Obtain hexadecimal strings

When filtering varbinary data by value or row key in a SQL query, use hexadecimal strings instead of plain text. The SQL Executor provides a built-in conversion tool.

For example, to query a row whose row key is r1, set the WHERE clause to WHERE rowkey='7321' in the SQL statement. The hexadecimal string of r1 is 7321.

To get the hexadecimal string for a value, use one of the following methods.

Method 1: Binary to hex string conversion field

In the left-side navigation pane of the cluster management system, choose Data Query > SQL Executor.
Select a namespace from the namespace drop-down list.
Click Execute to view the table schema.
Enter the string to convert in the Binary to hex string conversion field. The hexadecimal string appears at the top of the editor section.
Replace the condition value in your SQL statement with the hexadecimal string and click Execute. For example, if your original SQL statement is:
```
SELECT * FROM task WHERE ROW = "xxxx1";
```
The updated statement using the hexadecimal string 7878787831 is:
```
SELECT * FROM task WHERE ROW = "7878787831";
```

Method 2: HexString switch

In the left-side navigation pane of the cluster management system, choose Data Query > SQL Executor.
Select a namespace from the namespace drop-down list.
Turn on the HexString switch and click Execute. The execution results display the hexadecimal string for each value.
Replace the condition value in your SQL statement with the corresponding hexadecimal string and click Execute.