Help Center/ Data Warehouse Service/ User Guide/ 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/ Developing DWS Database Objects
Updated on 2026-08-24 GMT+08:00
View PDF
Share

Developing DWS Database Objects

This section describes how to perform operations on databases, schemas, and common tables, including Creating a Database, Creating a Schema, Creating a Common Table, and Editing a Common Table.

Creating a Database

  1. Log in to the DWS console.
  2. In the navigation pane on the left, choose Data > SQL Editor.
  3. On the Data Source page, connect to a data source, right-click Database, and select Create Database.

    Figure 1 Creating a database

  4. On the Create Database page, set parameters as required.

    Table 1 Parameters required for creating a database

    Parameter

    Description

    Database Name

    Database name The value must start with a letter or underscore (_) and can contain a maximum of 63 characters, including letters, digits, and underscores (_).

    Owner

    Database owner. It has the highest management permissions on the database.

    Compatibility

    Database compatibility mode. The available options include Oracle, MySQL, and Teradata. The default setting is Oracle. The compatibility mode determines the behavior differences of SQL syntax and data types. It cannot be changed after the database is created. Select a compatibility mode that meets your requirements.

    Encoding

    Encoding mode of the database. SQL_ASCII is recommended. The encoding mode must be the same as the service data character set. It cannot be changed after the database is created.

    Connection Limit

    Maximum number of concurrent connections to the database. The value cannot be less than -1. Value -1 indicates that there is no limit.

    Description

    Description of the database. The description makes it easy to identify and maintain the database.

    SQL Preview

    You can click Preview to view the SQL syntax for creating the database.

  5. Confirm the information and click OK. Refresh the database list to view the created database.

Creating a Schema

  1. Log in to the DWS console.
  2. In the navigation pane on the left, choose Data > SQL Editor.
  3. On the Data Source page, the database created in Creating a Database contains user schemas, system schemas, and external servers. System schemas can only be viewed and cannot be edited or deleted.
  4. Right-click User Schemas and select Create Schema.
  5. On the Create Schema page, set parameters as required.

    Table 2 Parameters for creating a schema

    Parameter

    Description

    Schema Name

    Schema name. The value must start with a letter or underscore (_) and can contain a maximum of 63 characters, including letters, digits, and underscores (_). In addition, the value must be unique.

    Owner

    Schema owner. It has the highest management permissions on the schema.

    Description

    Description of the schema. The description makes it easy to identify and maintain the schema.

    SQL Preview

    Click Preview to display the SQL syntax for creating the schema.

  6. Click OK.

Creating a Common Table

  1. Log in to the DWS console.
  2. In the navigation pane on the left, choose Data > SQL Editor.
  3. On the Data Source page, the schema created in Creating a Schema contains common tables, foreign tables, views, functions, sequences, and synonyms.
  4. Right-click Common Tables and select Create Common Table. In the displayed dialog box, set Attribute, Column, Data Distribution, Partition, Index, and Constraint as needed. Attribute and Column are mandatory. You can click SQL Preview to query the SQL statement for creating a table.

    Table 3 Parameters for creating a table

    Tab

    Description

    Attributes
    • Data Table Name: Set the data table name.
    • Table Orientation: Select ROW (row storage) or COLUMN (column storage). Row storage is suitable for frequent addition, deletion, and modification operations, while column storage is suitable for aggregation, analysis, and query of large amounts of data.
    • Available or not Partition: Select whether to create a partitioned table. A partitioned table splits a large table into multiple physical partitions based on rules to improve query performance.
    • Description: Enter the description of the 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, such as INTEGER, VARCHAR, and DATE.
    • Length: Set the total number of digits in the data. If this parameter cannot be set, the length is fixed and cannot be changed.
    • Precision: Set the number of decimal places. If this parameter cannot be set, precision is not required.
    • Non-null: If this option is selected, NULL cannot be inserted into this column.
    • Unique: If this option is selected, the value in this column must be unique.

    Data Distribution

    The options are as follows:

    • ROUNDROBIN: Each row of data in the table is sent to each DN in sequence. Data is evenly distributed across the DNs. This mode is suitable when there is no explicit distribution key.
    • REPLICATION: Each row in the table exists on all DNs, that is, each DN has complete table data. This mode is suitable for associated query optimization of small tables (such as dimension tables).
    • HASH: Specified columns are hashed and data is distributed to specified DNs through mapping. This is the most commonly used distribution mode. Select the join key or filter condition column as the distribution column.

    Partition

    On the Partition page, 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 values of optional columns. For a range partition, specify the start and end values. For a list partition, specify enumerated values.

    Index

    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 is recommended, and it is suitable for equality and range queries).
    • Index Type: Select Column or Expression. Column indicates that an index is created based on a single column or multiple columns. Expression indicates that an index is created based on an expression.
    • Condition Index: Add a WHERE condition to create an index (partial index) only for rows that meet the condition.

    Table Constraint

    Click Add Constraint and set the following parameters:

    • Constraint Type: Select check, unique, or primary.
    • Expression (check): Enter field constraints., for example, age > 0.
    • 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 2 Creating a common table

  5. Click OK.

Editing a Common Table

  1. Log in to the DWS console.
  2. In the navigation pane on the left, choose Data > SQL Editor.
  3. On the Data Source page, you can edit the common table you created in Creating a Common Table.

    Figure 3 Editing a common table

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

    Table 4 Editing the data table

    Operation

    Description

    Modify

    Click Modify to change the name and schema of the table, and specify whether it is a partitioned table.

    Delete

    Click Delete to delete the common table. After the table is deleted, the data cannot be restored. Exercise caution when performing this operation.

    Operate Column

    Click Operate Column 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.

    Operate Index

    Click Operate Index to add indexes, edit indexes (index names), and delete indexes in batches. Right-click a specified index name and select Edit Index to modify the attributes.

    Operate Constraint

    Click Operate Constraint to add constraints, edit constraints (constraint names and optional columns), and delete constraints in batches. Right-click a specified constraint name and select Edit Constraint to modify the attributes.

    Operate Partition (unavailable for non-partitioned tables)

    Click Operate Partition (unavailable for non-partitioned tables) to add, edit, and delete partitions. You can edit the partition name. Batch adding of partitions is also available. Right-click a specified partition name and select Edit Partition to modify the attributes.

  5. Confirm the information and click OK.
Parent Topic: Using the SQL Editor to Connect to a DWS Cluster