SQL Editor Overview

Video Tutorial

Editor Functions

• In the upper part of the editor, you can switch the data source, database, and schema.
• SQL statements can be written in the middle, where highlighting, basic syntax tips, and information about databases, schemas, tables, and fields are provided. For details about the SQL syntax, visit SQL Syntax Reference.
• With this tool, you can format SQL statements and query execution plans. Note that the PERFORMANCE execution plan executes SQL statements. Be cautious when viewing the execution plans of write SQL statements, such as INSERT, UPDATE, and DELETE, to avoid affecting production data.
• You can click Save to save the SQL statements you have compiled to an OBS bucket.
• The query results are displayed in multiple pages. You can generate pie, line, or bar charts from different fields. You can also export the results to an Excel file. The SQL execution records area displays SQL statement records from the past six months.
• You can switch to the script panel and show the directory folder. The script file is saved in the created directory. For details, see Creating a Directory. Currently, the editor provides two levels of directories. A maximum of 10 folders can be added to each level of directory. Each folder can store multiple script files. The script files can be saved in the corresponding OBS bucket file directory. The OBS bucket file address can be set globally. For details, see Global Settings.
  Figure 1 SQL editor page
  

Data Development Operations

Metadata management includes the hierarchical display of metadata. The hierarchy begins with the data source at the root, branching into databases and user roles. Databases include system schemas, user schemas, and foreign servers. System schemas and user schemas are distinguished by OIDs and system schemas cannot be changed or deleted. User schemas include common/partitioned tables, foreign tables, views, functions, sequences, and synonyms. A table contains columns, constraints, indexes, partitions, and triggers. The LIST and INFO APIs are available for querying the metadata lists and details.

The following figure shows the metadata list. Currently, databases, schemas, common tables, fields, indexes, constraints, and partitions can be added.

For the development instructions, see Developing DWS Database Objects, Importing and Querying DWS Data, Performing Operations on DWS Users and Permissions, and Diagnosing SQL Execution and Developing Scripts.

Figure 2 Metadata information hierarchy

Notes and Constraints

• This tool is supported only by clusters of version 8.1.3 or later.
• This tool is not included in the upgrade of the management plane to version 8.3.1 or later. The SQL editor is available only when the management plane of version 8.3.1 or later is installed and deployed for the first time.

• The editor depends on DWS and OBS . You need to enable the DWS cluster query and OBS query operations, and interconnect the editor with the Cloud Trace Service (CTS) service to record traces of operation APIs.

• When you execute a query SQL statement in the editor, the LIMIT clause is automatically added. The default value is 1,000, and the maximum value is 20,000. If the result of a single query exceeds 200 MB, only the result within 200 MB is returned. This prevents memory overflow caused by a large amount of data in the result of a single query.
• Files generated in the background are exported synchronously. Similar to the execution of a query SQL statement, the LIMIT clause is added. A maximum of 20,000 records can be exported. In addition, due to memory restrictions, the size of the query result exported at a time cannot exceed 200 MB (this is an approximate value).