Help Center/ Data Warehouse Service / More Documents/ User Guide (Ally Region)/ Connecting to a DWS Cluster/ Using a Visualization Tool to Connect to a DWS Cluster/ Using the SQL Editor to Connect to a DWS Cluster/ Data Development Operations
Updated on 2025-12-10 GMT+08:00
View PDF
Share

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 provided to query 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.

Figure 1 Metadata information hierarchy

Adding a Database

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Source. After a data source is connected, right-click the database name and click Create Database.

    Figure 2 Creating a database

  4. On the page for adding a database, set the parameters as required.

    • Database Name: Set the database name.
    • Owner: Select the new database owner from the drop-down list box.
    • Compatibility mode: Choose a database compatibility mode rom the drop-down list. The available options include Oracle, MySQL, and Teradata. The default setting is Oracle.
    • Encoding: Select the encoding mode of the new database from the drop-down list box. SQL_ASCII is recommended.
    • Connection Limit: The value cannot be less than -1. The value -1 indicates no limit.
    • Description: Description of the new database.
    • SQL Preview: You can click Preview to view the SQL syntax for creating the database.

  5. Click OK.

Adding a Schema

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Source to add a database. For details, see Adding a Database. The database contains the user schema, system schema, and external server.

    The system schema can only be viewed.

  4. Right-click the user mode name and click Add Schema.
  5. Set the parameters as required on the displayed page.

    • Schema Name: Set the schema name.
    • Owner: Select the owner of the new schema from the drop-down list box.
    • Description: Description of the new schema.
    • SQL Preview: Click Preview to display the SQL syntax for creating the schema.
    Figure 3 Adding a Schema

  6. Click OK.

Adding a Common Table

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Switch to the Data Source panel and add a schema (for details, see Adding a Schema). A schema contains structures such as common tables, foreign tables, views, functions, sequences, and synonyms.
  4. Right-click the name of the common table and click Create Common Table to add a table. The dialog box for adding a common table contains options such as Attribute, Column, Data Distribution, Partition, Index, and Constraint. The Attribute and Column fields are mandatory. You can click SQL Preview to query the SQL statement for creating a table.

    Table 1 Parameters for adding a data table

    Tab

    Description

    Attribute
    • Data Table Name: Set the data table name.
    • Table Orientation: You can select ROW or COLUMN.
    • Available or not Partition: Select whether to create a partitioned table.
    • Description: Description of the new data table.

    Column

    Click Add Column and set the following parameters:

    • Column Name: Set the column name.
    • Data Type: Select the data type of the new column from the drop-down list box.
    • Length: Total number of digits. If this parameter is dimmed, the length is fixed.
    • Precision: Number of decimal places. If this parameter is dimmed, the precision cannot be set.
    • Non-null: Select whether the new column is not null.
    • Unique: Select whether the new column is unique.

    Data Distribution

    The options are as follows:

    • ROUNDROBIN: Each row of data in the table is sent to each DN in sequence.
    • REPLICATION: Each row of data in the table exists on all DNs. Each DN has complete table data.
    • HASH: Specified columns are hashed and data is distributed to specified DNs through mapping.

    Partition

    On the Partition panel, you can select a partition type (range partition or list partition) and optional columns (corresponding to table fields). Click Add Partition and set the following parameters:

    • Partition Name: Set the partition name.
    • Partition Value: Select a value from the range based on the optional columns.

    Indexes

    Click Add Index and set the following parameters:

    • Index Name: Set the index name. You can select Unique Index.
    • Access Mode: Select an index access mode from the drop-down list box. B-tree indexes are recommended.
    • Index Type: The options are Column and Expression.
    • Condition Index: The WHERE condition constraint can be added.

    Table Constraints

    Click Add Constraint and set the following parameters:

    • Constraint Type: The value can be check, unique, or primary.
    • Expression (check): Enter field constraints.
    • Constraint Name: Set the constraint name.
    • Optional Column (unique\primary): Select an optional column from the drop-down list box.

    SQL Preview

    Click Preview to display the SQL syntax for creating a common table.
    Figure 4 Creating a common table

  5. Click OK.

Editing a Common Table

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Source. You can edit the created common table. For details about how to create a common table, see Adding a Common Table.

    Figure 5 Editing a common table

  4. Right-click the name of the common table name to modify it. The following table describes the modification operations.

    Table 2 Table modification operations

    Operation

    Description

    Modifying a common table

    You can click Modify to modify the name and schema of a table, and specify whether it is a partitioned table.

    Deleting a common table

    Click Delete to delete a common table.

    Operating columns

    Click the corresponding operation button to add, edit, and delete columns. You can edit the column name, data type, length, and specify whether it is a non-NULL column. Batch adding of columns is also available.

    Operating indexes

    Click Operate Index to add indexes, edit indexes (index names), and delete indexes in batches.

    Operating constraints

    Click Operate constraints to add constraints, edit constraints (constraint names and optional columns), and delete constraints in batches.

    Operating partitions

    Click Operate partitions (unavailable for non-partitioned tables) to add, edit, and delete partitions. You can edit the partition name. Batch adding of partitions is also available.

    You can also right-click a constraint, index, or partition name and choose Operate Constraint/Index/Partition from the shortcut menu to modify the corresponding attributes.

  5. Confirm the information and click OK.

Viewing Data in a Common Table

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Source and right-click the data table name.
  4. Click View Details to add, filter, edit, and delete data in a common table.

    Figure 6 Viewing data in a common table

    Right-click a partition name and choose View Details to add, filter, edit, or delete partition data.

Checking Views

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Source, right-click a view name, and choose View Details from the shortcut menu to check the views of the database.

    Figure 7 Checking views

Importing Data

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Source and right-click the common table name. Click Import Data to import data from the local Excel file to the common table.

    • Local import: When uploading an Excel file, ensure it is under 30 MB. For CSV files, select separators to divide data in each row and indicate if there is a table header. If there is no header, input data in each row according to the chosen table fields.

  4. Click OK.
  5. In the upper right corner of the page, choose Common Functions > Import data list and check whether the import is successful.

Submitting a SQL Task

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Sources, log in to the data source for which you want to submit the SQL task, and select the corresponding database and schema.
  4. Enter a non-query SQL statement in the text box and click Submit SQL task to submit the selected SQL statement to the background task for execution.

    A maximum of 100 non-query SQL statements can be submitted at a time. A maximum of five SQL tasks can be executed by a user.

    Figure 8 Submitting a SQL task

  5. Choose Common Functions > SQL task list in the upper right corner of the page to view the executed SQL tasks.
  6. Click Details in the Operation column to view the execution status of each SQL statement.

    Figure 9 Viewing SQL task details

Diagnosing a Plan

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Sources, log in to the data source for which you need to enter SQL statements, and select the corresponding database and schema.
  4. Enter a query SQL statement in the text box and click Planned diagnostics. You can select EXPLAIN or PERFORMANCE.

    Only one query SQL statement can be diagnosed at a time. If you enter multiple query SQL statements, the first SQL statement is diagnosed by default. If you select PERFORMANCE, the entered SQL statement is executed and the result is returned.

    Figure 10 Selecting PERFORMANCE

  5. Click OK. The Planned diagnostics page is displayed. You can view the SQL statement diagnosis result and plan diagnosis visualization result.

    • Click SQL diagnostics to view the SQL statement formatting and diagnosis items.
      Figure 11 Viewing SQL statement diagnosis results
    • Click Planned diagnostics to display the plan tree node and plan diagnosis result of the SQL statement.
      Figure 12 Viewing SQL plan diagnosis results

Viewing Statistics About Databases, Schemas, and Tables

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor.
  3. Click Data Source and double-click a database name. On the database list page that is displayed, you can search and check the detailed information about a database.

    Figure 13 Viewing database details

  4. Click a database name in the database list to go to the schema list and view the total number of tables, total table size, and index size.

    Figure 14 Viewing schema information

  5. Click a schema name in the schema list to go to the common table list. You can view the number of rows in the table, table size, and index size.

    Figure 15 Viewing common table information

Creating a Directory

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor to switch to the script panel.
  3. Click Create Directory.

    • Save to Directory: Select a parent directory from the drop-down list box. If this parameter is left blank, a level-1 directory is created by default.
    • Directory Name: Set the directory name. The value can contain only letters, digits, and underscores (_).
    Figure 16 Creating a directory

  4. Confirm the information and click OK.

Adding a Script

  1. Log in to the DWS console.
  2. In the navigation tree, choose Data > SQL Editor to switch to the script panel.
  3. Click Create Script.

    • Save to Directory: Select the new directory from the drop-down list box. This option is optional.
    • Script Name: Set the script name. Only letters, digits, and underscores (_) are supported.