Create a table

Create an internal table

This section provides an example of how to create a table named employee in the odc_test database. The employee table includes the employee ID (emp_no), birthday (birthday), name (name), and gender (gender).

Step 1: Specify basic information

1. After you log on to the database and go to the SQL development window, click the Tables tab in the navigation pane on the left to view the table list. In the upper-right corner of the table list, click + to create a table.

2. On the Basic Information tab, enter the Table Name, select the Storage Mode, and enter a Description for the table.

   

   Note

   • In MySQL mode, you must also select a default character set and a default collation.

   • After you specify the basic information and switch to the Set Columns step, the basic information is submitted.

Step 2: Set columns

As shown in the following figure, specify the following information when you add a column.

The Set Columns page provides the following three features:

Step 3: Set indexes

When a table contains a large amount of data, you can use an index to query data faster. An index is a data structure that pre-sorts the values in one or more columns of a table. Using an index lets you directly locate records that meet the specified conditions.

As shown in the following figure, you must specify the following information.

Step 4: Set constraints

Constraints define data rules for a table. If a data operation violates a constraint, the operation is terminated.

ODC supports the following four types of table-level constraints.

• PRIMARY KEY constraint: Defines a primary key to uniquely identify each row in a table. A primary key constraint can be a single field or a group of fields. A table can have only one primary key constraint, which cannot be edited after it is set.

• UNIQUE constraint: Ensures that the data in a field or a group of fields is unique in the table. A table can have multiple unique constraints.

• Foreign key constraint: Establishes a connection between the data in two tables (can be one or more columns). It aims to maintain the consistency and integrity of data between associated tables. You cannot add or edit foreign key constraints after they are set.

• CHECK constraint: Checks the data against the specified rules when you edit database data. Data can be modified only after it passes the check.

Step 5: Set partitioning rules

When a table contains a large amount of data, you can partition the table. After a table is partitioned, its data is stored in multiple tablespaces. A full table scan is not required for each query.

• In OceanBase, MySQL mode supports six partitioning methods: key, Hash, Range, Range Columns, List, and List Columns.

• Oracle mode supports three partitioning methods: List, Range, and Hash.

Step 6: Confirm the SQL statement

After you click Submit, view the statement on the SQL Confirmation page. You can use the SQL Check and Format features to make the statement easier to read.

The syntax is as follows.

CREATE TABLE table_name (column_name column_type, column_name column_type,.......);

Parameters

Step 7: Complete the table creation

Click Execute. After the table is created, the new employee table appears in the table list in the navigation pane on the left.

You can use the SELECT keyword to query data in the new table.

Syntax format

SELECT
 column_name,
 column_name
FROM
 table_name [WHERE Clause] [LIMIT N] [ OFFSET M]

Parameters:

Example

SELECT `emp_no`, `birthdate`, `name`, `gender` FROM `employee`;

Create an external table

This section provides an example of how to create an external table named employee for OSS. This example creates the external table employee in the odc_test database. You can map a specified file path on OSS to the odc_test database in ODC.

1. Prepare the external file.

   1. Create a folder named external table in the OSS Management Console.

   2. Upload the file employee.csv to the external table folder.

   3. Obtain the storage path of the external table employee: https://ob*********.oss-cn-shanghai.aliyuncs.com/doc/img/odc/433/external%20table/employee.csv.

2. In the ODC SQL window, run the following SQL command to create the external table employee.

   

   CREATE EXTERNAL TABLE `employee` (`emp_no` int(120),`birthday` date,`name` varchar(120))
      LOCATION = 'ob*********.oss-cn-shanghai.aliyuncs.com/doc/img/odc/433/500.sql-development/700.database-objects/100.web-odc-table-objects/employee/employee.csv'
      FORMAT = (TYPE = 'CSV'
         FIELD_DELIMITER = ','
         FIELD_OPTIONALLY_ENCLOSED_BY ='"'
         ENCODING = 'utf8mb4')
      PATTERN = 'employee.csv';

   • The LOCATION option specifies the path where the external table file is stored.

     • Local Location format: LOCATION = '[file://] local_file_path'. For scenarios that use the local Location format, you must set the secure_file_priv system variable to configure the accessible path. For more information, see secure_file_priv.

     • Remote Location format: LOCATION = '{oss|cos|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'. $ACCESS_ID, $ACCESS_KEY, and $HOST are the access information required to access Alibaba Cloud OSS, Tencent Cloud COS, and S3.

   • The FORMAT = ( TYPE = 'CSV'... ) option specifies that the format of the external file is CSV. The parameters are as follows.

     • TYPE: Specifies the type of the external file.

     • LINE_DELIMITER: Specifies the row delimiter for the CSV file. The default value is LINE_DELIMITER='\n'.

     • FIELD_DELIMITER: Specifies the column delimiter for the CSV file. The default value is FIELD_DELIMITER='\t'.

     • ESCAPE: Specifies the escape character for the CSV file. It can only be 1 byte. The default value is ESCAPE ='\'.

     • FIELD_OPTIONALLY_ENCLOSED_BY: Specifies the character that encloses field values in the CSV file. The default value is empty.

     • ENCODING: Specifies the character set encoding format of the file. For all character sets currently supported in MySQL mode, see Character sets. If not specified, the default value is UTF8MB4.

     • NULL_IF: Specifies the string that is treated as NULL. The default value is empty.

     • SKIP_HEADER: Skips the file header and specifies the number of rows to skip.

     • SKIP_BLANK_LINES: Specifies whether to skip blank lines. The default value is FALSE, which means blank lines are not skipped.

     • TRIM_SPACE: Specifies whether to remove leading and trailing spaces from fields in the file. The default value is FALSE, which means leading and trailing spaces are not removed.

     • EMPTY_FIELD_AS_NULL: Specifies whether to treat empty strings as NULL. The default value is FALSE, which means empty strings are not treated as NULL.

   • The PATTERN option specifies a regular expression pattern to filter files in the LOCATION directory. For each file path in the LOCATION directory, if the path matches the pattern, the external table accesses the file. Otherwise, the external table skips the file. If this parameter is not specified, all files in the LOCATION directory can be accessed by default. The external table saves the list of files that match the PATTERN in the path specified by LOCATION to a system table. When the external table is scanned, it accesses the external files based on this list.

3. View the employee table in the external table list in the navigation pane on the left.