In your business, you may need to query data in databases to verify business code, analyze product effects, or identify issues in an online environment. To meet this requirement, Data Management (DMS) provides the SQLConsole in which you can write SQL statements to query data. This topic describes the SQLConsole in the DMS console.
- To query data in a database, you must have the query permission on the database.
- If a table contains sensitive or confidential fields and you do not have permissions on the fields, you cannot use the fields in WHERE clauses to construct query conditions.
- If a table contains sensitive or confidential fields and you do not have permissions
on the fields, the values of the fields are displayed as
******in query results.
- In the left-side navigation tree of the SQLConsole, you can enter a keyword in the search box at the top to search for a table in the current database. You can also view information about each table, such as the schema, fields, and indexes of the table. The navigation tree can display 1,000 tables at most.
- By default, a maximum of 200 data rows can be returned for each query result. If you are a DMS administrator, you can modify this default number as needed. Perform the following steps: In the top navigation bar of the DMS console, choose . On the Security Rules page, find the security rule set that you want to modify and click Edit in the Actions column. On the SQLConsole tab, find the Maximum number of returned rows per query rule and click Edit in the Actions column.
- By default, a maximum of 10 GB of data can be queried each time. If you are a DMS administrator, you can modify this default volume as needed. Perform the following steps: In the top navigation bar of the DMS console, choose . On the Security Rules page, find the security rule set that you want to modify and click Edit in the Actions column. On the SQLConsole tab, find the Limit the maximum allowed SQL full table scan (MB) rule and click Edit in the Actions column.
- By default, the timeout period for executing each SQL statement is 60 seconds. If you are a DMS administrator, you can modify the default timeout period as needed. Perform the following steps: In the top navigation bar of the DMS console, choose . On the Instance page, find the instance that you want to edit, move the pointer over More in the Actions column, and then select Edit instance. In the Edit instance dialog box, modify the query timeout(s) parameter in the Advanced information section.
Single database query
- Log on to the DMS console.
- Choose SQLConsole > Single Database query in the top navigation bar. In the Please select the database first dialog box, select a database in which you want to query data and click Confirm.
- In the code editor, write an SQL statement and click Execute.
Overview of the single database query page
- Access section
The Access section is at the top of the page. This section displays the permissions that you have on the current database. You can move the pointer over the Info icon to view information about the database. The information includes the one or more database owners and the DBA of the instance to which the database belongs.
- Tables or Query icon
The Tables or Query icon is in the upper-right corner of the page. You can click the icon to switch between the SQLConsole and the Table List tab.
- Performance icon
The performance icon is next to the Tables or Query icon. Move the pointer over the performance icon and select an item. You are navigated to the Database Autonomy Service (DAS) console. For more information, see the DAS documentation.
- Sync Metadata icon
The Sync Metadata icon is next to the performance icon. After you click the Sync Metadata icon and click OK in the message that appears, DMS starts to collect the up-to-date metadata of the current database. The metadata includes the information about tables, fields, indexes, and programmable objects. The collected metadata is used to manage permissions on the tables, fields, and programmable objects in a fine-grained manner.
- Export icon
The export icon is next to the Sync Metadata icon. You can click the export icon to export schemas in the current database to a Word, Excel, or PDF file. You can also export the SQL statements that are used to create all the tables in the database.
You can write and execute SQL statements in the SQLConsole to manage data in the current database. The SQLConsole also provides other features, such as Format, Execute Plan, Saved SQL, and SQL Diagnostics.
- You can click Format to adjust the layout of the SQL statement that you enter. The adjustment makes the statement clearer.
- You can click Execute Plan to view performance information about the SQL statement that you enter.
- You can use Saved SQL to add SQL statements that are frequently used. This way, you can insert the statements into the code editor without the need to write them. You can also manage the frequently used statements as needed.
- After you move the pointer over Display Settings, a dialog box that contains multiple parameters appears. You can set the Character Set parameter to specify the character set of query results.
- In the Display Settings dialog box, you can set the SQL Prompt parameter to specify whether to enable SQL prompts. If you set this parameter to Open, the code editor provides smart prompts when you write an SQL statement. The smart prompts include the names of the tables, fields, and keywords in the current database.
- In the Display Settings dialog box, you can set the Show Results parameter to specify whether the execution result of the current SQL statement overwrites, or is appended to, the execution result of the last statement.
- Execution results
After each SQL statement is executed, the execution result is displayed on a new tab below the code editor. You can perform operations on the result data on this tab. For example, you can view row details, generate charts based on the result data, or export data as required.
- Execution History tab
The fixed Execution History tab displays the execution history of SQL statements. Each record includes information such as the start time, database, and executed SQL statement.