ApsaraDB for OceanBase:Create a table

Create a table in the physical database

In the following example, a table named employee is created in the ODC console. The table contains the emp_no,birthday, name, and gender columns.

Step 1: Specify the basic information

1. Log on to the ODC console and click the name of the desired connection to go to the corresponding connection management page. You can click Table in the left-side navigation pane to view tables. To create a table, click the plus sign (+) in the upper-right corner of the table list or click Create in the top navigation bar.

2. In the Basic Info section, set the Table Name and Description parameters.

   

   Note

   • In MySQL mode, you also need to set theDefault Character SetandDefault Collationparameters.

   • After you specify the basic information and go to the "Set columns" step, the basic information is submitted.

Step 2: Set columns

The following figure and table show the information that you need to specify when you add a column.

The following table describes operations that you can perform on the column setting page.

Step 3: Set indexes

If a table contains a large amount of data, you can use indexes to accelerate data queries. An index is a data structure that pre-sorts the values of one or more columns in a table. By using indexes, you can directly locate records that meet the conditions.

The following figure and table show the information that you need to specify when you set an index.

The index setting page provides the following icons.

Step 4: Set constraints

Constraints are used to specify data rules for a table. A data operation that violates the constraints is terminated.

ODC supports the following four types of table constraints:

• PRIMARY KEY constraint: defines a primary key to uniquely identify each row of data in the table. A PRIMARY KEY constraint can be a field or a group of fields. You can set only one PRIMARY KEY constraint for a table, and you cannot modify the PRIMARY KEY constraint after you configure the constraint.

• UNIQUE constraint: ensures that the data in a field or a group of fields is unique in the table. You can set multiple UNIQUE constraints in one table.

• FOREIGN KEY constraint: associates one or more columns in two tables. A FOREIGN KEY constraint is used to maintain the data consistency and integrity between associated tables. After you complete the setting of FOREIGN KEY constraints, you cannot create new constraints or modify existing constraints.

• CHECK constraints: checks the data in the database based on the configured check rules when you edit the data. Data modification is allowed only after the check is passed.

OceanBase Database supports different constraints in MySQL and Oracle modes, and different constraints require different information. Therefore, you need to specify the required information based on the constraint you selected and the requirements on the page.

The constraint setting page provides the following icons.

Step 5: Set partitioning rules

You can partition a table that contains a large amount of data. After a table is partitioned, data in the table is stored in multiple tablespaces. The database does not scan the entire table for a query.

• In MySQL mode, OceanBase Database supports the following partitioning methods: KEY, HASH, RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS.

• In Oracle mode, OceanBase Database supports the following three partitioning methods: List, Range, and Hash.

Step 6: Confirm the SQL statement

After you click Execute, you can view the statement on the SQL confirmation page. You can check and format the statement.

The syntax is as follows:

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

The following table describes the fields in the syntax.

Step 7: Complete the table creation

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

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

The syntax is as follows:

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

The following table describes the fields in the syntax.

Here is an example:

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

Create an external table

The example in this section describes how to create an external table named employee in the odc_test database managed by ODC in the SQL window by mapping the specified file in Alibaba Cloud Object Storage Service (OSS) to the odc_test database.

1. Create an external file.

   1. Create a directory named external table on the Use Alibaba Cloud accounts to log on to the OSS console.

      

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

      

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

      

2. In the SQL window of ODC, execute the following SQL statement to create an external table named 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 for storing the external table.

     • Local location format: LOCATION = '[file://] local_file_path'. When you use the local location format, you must set the system variable secure_file_priv to specify an accessible path. For more information, see secure_file_priv.

     • Remote location format: LOCATION = '{oss|cos|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'. In this format, $ACCESS_ID, $ACCESS_KEY, and $HOST are required for accessing Alibaba Cloud OSS, Tencent Cloud Object Storage (COS), or Amazon Simple Storage Service (S3).

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

     • TYPE: the type of the external file.

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

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

     • ESCAPE: the escape character for the CSV file, which can be only 1 byte in length. The default value is '\'.

     • FIELD_OPTIONALLY_ENCLOSED_BY: the characters that enclose the field values in the CSV file. By default, this parameter is left empty.

     • ENCODING: the character set encoding used by the file. For more information about all character sets supported in MySQL mode, see Character sets. If this parameter is not specified, the default value UTF8MB4 takes effect.

     • NULL_IF: the strings to be treated as NULL values. By default, this parameter is left empty.

     • SKIP_HEADER: specifies to skip the file header, and specifies the number of lines to skip.

     • SKIP_BLANK_LINES: specifies whether to skip blank lines. The default value is FALSE, which specifies not to skip blank lines.

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

     • EMPTY_FIELD_AS_NULL: specifies whether to treat empty strings as NULL values. The default value is FALSE, which specifies not to treat empty strings as NULL values.

   • The PATTERN option specifies a regular pattern string for filtering files in the LOCATION directory. For each file in the directory specified by LOCATION, if the file path matches the pattern string, the external table accesses the file. Otherwise, the external table skips the file. By default, if this parameter is not specified, all files in the directory specified by LOCATION are accessible. The external table stores the list of files that match the string specified by PATTERN in the system table of the database. During a scan, the external table accesses external files based on this list.

3. In the left-side navigation pane, view the employee table in the external table directory.