Data Development Operations

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.
     Figure 8 Importing Local Excel Data to a Common Table
     
   • 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 9 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.

Viewing Statistics About Databases, Schemas, and Tables

1. Log in to the GaussDB(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 10 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 11 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 12 Viewing common table information
   

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 13 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 14 Adding a script
   

Reference Syntax

• Syntax reference for adding a database: CREATE DATABASE
• Syntax reference for adding a schema: CREATE SCHEMA
• Syntax reference for adding a common table: CREATE TABLE