You need to query data records in ApsaraDB for Lindorm when you develop, debug, or maintain your services.

To query data, go 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. You can write a SELECT query based on the table schema.

Procedure

To implement an SQL query, perform the following steps:

  1. Select a namespace. Example: default.
  2. Check the schema of the table that you want to query in the right-side table list.
  3. Enter an SQL statement in the editor.
  4. Click Run to run the SQL statement. If you use a Windows system, you can press Ctrl+Enter. If you use a macOS system, press Command+Return.
  5. The query results are displayed below the editor. You can also check error messages in this section.

The following figure shows the steps.

runsqlk
Note 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.

Limits

Before you perform an SQL query, make sure that you have read and understand the following limits:

  • ApsaraDB for Lindorm supports only the SELECT statement.
  • To ensure data security, ApsaraDB for Lindorm returns a maximum of 100 data records for each query.
  • To query varbinary data with conditions, you must use hexadecimal strings as values.
  • The ROW field corresponds to the rowkey in ApsaraDB for Lindorm. The ROW and qualifier fields are of the varbinary data type. If the qualifier does not belong to the family, you must specify the family, such as select 'ROW', q1, 'f1:q2' from....
  • ROW and COL are SQL reserved words. Therefore, you must use backticks (`) to enclose them in your queries. When you specify a family for a qualifier, you must also use backticks (`) to enclose the family.

Convert binary strings to hexadecimal strings

Data is stored in byte arrays (byte[]). The varbinary values in the query results are displayed as 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 the rowkey r1, the where condition in the SQL query must be where rowkey='7321'. The hexadecimal value of string r1 is 7321.

A converter is provided on the SQL Queries page for you to convert binary strings to hexadecimal strings. After you enter a binary string in the text box, the corresponding hexadecimal value is automatically displayed in the SQL editor. The following figure shows an example.

726f7731

If you want to view query results that are displayed as hexadecimal strings, select the HexString option next to Run. ApsaraDB for Lindorm automatically converts varbinary values to hexadecimal strings. The following figure shows an example.

hexstring