Query Wide Table Data via SQL Executor - Lindorm

Use the cluster management system to query, add, and modify data in Lindorm wide tables. The system provides two query interfaces: the HBase-compatible query module for browsing and editing table data, and the SQL Executor for running SQL statements.

Prerequisites

Before you begin, ensure that you have:

Logged on to the cluster management system of your Lindorm instance. For more information, see Log on to the cluster management system.

Limitations

Each query returns at most 100 entries.
The ROW field is equivalent to row key in Lindorm. Both ROW and qualifier fields have the varbinary data type.
If a qualifier field does not belong to a column family, you must specify a column family.
ROW and COL are reserved fields in SQL. Enclose them in backticks (` `) when using them in queries. When specifying a column family for a qualifier` field, also enclose the field in backticks.

Choose a query method

The cluster management system provides two data query modules:

Module	Best for
Data query (Compatible with HBase)	Browsing table data with filter conditions; adding rows to HBase tables
SQL Executor	Running SQL statements against SQL tables or HBase tables

Data query (Compatible with HBase)

Use this module to query table data with filter conditions, add rows to HBase tables, and edit existing rows in both SQL tables and HBase tables.

Query existing data

Applies to SQL tables and HBase tables.

In the left-side navigation pane, choose Data Query > Data query (Compatible with HBase).
Set the namespace and Table name filter conditions.
(Optional) Set additional filter conditions: Filter column family, row key, start key, end key, and limit.
Click Query.

The data matching your filter conditions is displayed.

Add data to an HBase table

Applies to HBase tables only. Adding rows to SQL tables is not supported in this module.

Click Add.

In the Add table data dialog box, fill in the following parameters.

Parameter	Description
row key	The row key of the HBase table.
Col	The column to add. Specify the column family in the format `<column family name:column name>` — for example, `f:name`.
Timestamp	Only Unix timestamps are supported.
Value	The data value. For supported data types, see Basic data types.

Click OK.

Edit existing data

Applies to SQL tables and HBase tables.

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

SQL Executor

Use the SQL Executor to run SQL statements against tables in a selected namespace.

In the left-side navigation pane, choose Data Query > SQL Executor.
Select a namespace from the namespace drop-down list. Tables in that namespace are displayed on the right side of the page.
(Optional) If you need to query varbinary data or filter by row key, convert your value to a hexadecimal string first. See Get hexadecimal strings.
Enter your SQL statement in the editor.
Click Execute

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

Get hexadecimal strings

When querying varbinary data with a condition or filtering by a specific row in an HBase table, use hexadecimal strings as values in your WHERE clause.

For example, to query a row with row key r1, the WHERE clause becomes WHERE rowkey='7321' because 7321 is the hexadecimal representation of r1.

Two methods are available:

Method 1 — Binary to hex string conversion field
1. In the left-side navigation pane, choose Data Query > SQL Executor.
2. Select a namespace from the namespace drop-down list.
3. Click Execute to view the table schema.
4. Enter the value you want to convert in the Binary to hex string conversion field. The hexadecimal string appears at the top of the editor section.
Method 2 — HexString switch
1. In the left-side navigation pane, choose Data Query > SQL Executor.
2. Select a namespace from the namespace drop-down list.
3. Turn on the HexString switch.
4. Click Execute. The hexadecimal string for each value appears in the execution results.

After getting the hexadecimal string, replace the original value in your SQL statement with it. For example:

Original statement: SELECT * FROM task WHERE ROW = "xxxx1";
Updated statement: SELECT * FROM task WHERE ROW = "7878787831";