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 write GET and SCAN queries. ApsaraDB for HBase Performance-enhanced Edition also provides a SQL query interface in the cluster management system for you to query data. If you are familiar with the SQL syntax, you can use this interface to query HBase tables.
To use this feature, navigate to the SQL queries page of the cluster management system. Before you query a table, you must select the namespace that stores the table. After you select a namespace, the table list on the right side displays all the tables in the namespace. You can click a table name to view the schema of the table. ROW represents a rowkey, and COL represents a predefined column name. You can write a SELECT query based on the table schema.
The following example shows how to perform a SQL query:
Select a namespace, for example, 'default'. Check the schema of the table that you want to query in the right-side table list. Enter a SQL statement into the editor. Click Run or press 'CTRL + Enter' (Windows)/'command + return' (macOS) to run the SQL statements. The query results are displayed below the editor. You can also check error messages in this area.
The following figure shows the steps.
Note: The SQL query editor in the cluster management system does not support the use namespace syntax. You must manually select a namespace from the namespace list in the upper-left corner of the editor.
Before you make a SQL query, make sure that you have read and understand the following limits:
The SQL query editor only supports the 'SELECT' statement. If you want to modify data, use the command-line interface or develop an application that uses the ApsaraDB for HBase API. To ensure data security, the system returns up to 100 entries upon each query. To query varbinary data with conditions, you must use hexadecimal strings as values. The 'ROW' field represents a rowkey in an HBase table. The 'ROW' and 'qualifier' fields only support 'varbinary' data. If the 'qualifier' field does not belong to the family 'f', you must specify a family, for example, 'select `ROW`, q1, `f1:q2` from ...'. The 'ROW' and 'COL' fields are SQL reserved fields. You must enclose a 'ROW' or 'COL' in a pair of back quotes (`). When you specify a family for a 'qualifier', you must also enclose it in a pair of back quotes.
Convert binary strings to hexadecimal strings
Data is stored in byte arrays (
byte) in ApsaraDB for HBase. The
varbinary values in the query results are displayed as HBase binary strings on the
SQL Queries page.
If you want to query varbinary fields with conditions (using the
WHERE clause that contains rowkey or other fields), you must specify the
values of the query conditions as hexadecimal strings. Example: To query data with rowkey
where condition in the SQL query must be
where rowkey='7321' (The hexadecimal value of string
A converter is provided on the
SQL Queries page for you to convert binary strings to hexadecimal strings. After you enter a
binary string into the text box, the corresponding hexadecimal value is automatically
displayed in the SQL editor. The following figure shows an example.
If you want to view query results displayed as hexadecimal strings, select the
HexString option next to
Run. The system then automatically converts varbinary values in the query results to
hexadecimal strings. The following figure shows an example.