Help Center/ GaussDB(DWS)/ Management Guide/ SQL Editor Development Tool/ Data Development Operations
Updated on 2024-09-05 GMT+08:00
View PDF

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 GaussDB(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 GaussDB(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 GaussDB(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. Btree is 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 GaussDB(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 Constraint to add constraints, edit constraints (constraint names and optional columns), and delete constraints in batches.

    Operating partitions

    Click Operate Constraint (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 GaussDB(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 GaussDB(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 GaussDB(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 or OBS bucket 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.
    • obs import: Choose a file from the OBS bucket or directory. Supported file types include CSV and TEXT. Set the parameters of the foreign table to be created for importing data to the OBS bucket. Use the OBS foreign table to write the OBS bucket file to the selected common table.

      Starting from version 8.2.0.100, the hybrid data warehouse (single-node deployment) can import OBS files.

      Table 3 OBS import parameters

      Parameter

      Description

      Example Value

      storage location

      Choose a file from the OBS bucket.

      -

      file format

      Select a file format from the drop-down list. Supported formats include CSV and TEXT.

      CSV

      file encoding

      Select a file encoding mode from the drop-down list. UTF8 is recommended.

      UTF8

      Delimiter

      Commas (,) are the default separators for CSV files, while tab characters are the default separators for TXT files.

      ,

      quote (CSV format)

      Quotation marks are used for CSV files. The value should be a single-byte character and cannot be the same as the delimiter or null parameter.

      #

      newline character (TEXT format)

      When importing data in TEXT format, you can specify the newline character style. The maximum length of the newline character is 10 bytes, and multi-character newline characters are supported. The supported newline characters include common ones like \r, \n, and \r\n, as well as other characters or character strings like $ and #.

      \r

      Whether not to escape (TEXT format)

      You can specify whether to escape the backslash (\) and its following characters in the TEXT format.

      Yes

      Null value

      You can specify how null values are represented in a data file.

      $

      Number of data format errors

      Maximum number of data format errors allowed during data import. The value -1 indicates that the number of errors is not limited.

      -1

      Does it contain a header (CSV format)

      You can specify if the exported CSV file should contain a header row that describes each column in the table. This parameter only applies to CSV files.

      Yes

      Whether to ignore missing fields

      Enabling this function sets the last column of a row in a data source file to NULL if it is missing, without reporting an error message.

      Yes

      ignore extra data

      You can specify whether to ignore excessive columns when the number of columns in a source data file exceeds that defined in the foreign table.

      Yes

      compatible illegal chars

      Enabling this function allows for invalid characters during data import.

      Yes
      Figure 8 Importing OBS bucket file data to a common table

  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.

Creating a Directory

  1. Log in to the GaussDB(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 9 Creating a directory

  4. Confirm the information and click OK.

Adding a Script

  1. Log in to the GaussDB(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.
    • OBS Bucket: Name of the OBS bucket for storing script files. If no OBS bucket is available, click View OBS Bucket to access the OBS console and create one. For details, "Managing Buckets" > "Creating a Bucket" in Object Storage Service Console Operation Guide.
    • Path: User-defined directory for storing script files on OBS. Multi-level directories can be separated by slashes (/). The value is a string containing 1 to 50 characters, which cannot start with a forward slash (/). If you do not set this parameter, the system automatically adds a path by default.
    Figure 10 Adding a script