Create an external table

This topic describes how to create an external table by using SQL statements. It also describes the prerequisites, overview, and considerations for creating an external table, and provides examples.

Overview

An external table is a logical table object. Its data is stored in an external storage system instead of the database.

For more information about external tables, see About external tables.

Prerequisites

Before you create an external table, make sure that:

• You have deployed an OceanBase cluster and created a MySQL tenant. For more information about how to deploy an OceanBase cluster, see Deployment overview.

• You have connected to a MySQL tenant of OceanBase Database. For more information about how to connect to the database, see Overview of connection methods.

• You have created a database. For more information, see Create a database.

• You have the CREATE privilege. To view the privileges of the current user, perform the relevant operation as described in View user privileges. If you do not have the required privilege, contact the administrator to assign the required privilege to you. For more information, see Grant privileges.

Considerations

• An external table can only be queried, and DML operations are not supported.

• When you query an external table, if the external file accessed by the table is deleted, the system does not return an error, but instead returns empty rows.

• If the external storage system that manages the file accessed by the external table becomes unavailable, an error is returned when you query the external table.

• External table data is stored in an external data source. Therefore, factors such as network latency and file system performance affect the query performance. When you create an external table, select an appropriate data source and optimization strategy to improve query efficiency.

Create an external table

You can execute the CREATE EXTERNAL TABLE statement to create an external table.

Define an external table name

When you create an external table, you must first define a table name. To avoid confusion and ambiguity, we recommend that you use specific naming rules or prefixes to distinguish external tables from regular tables when naming external tables. For example, you can add a suffix such as _csv to the name of an external table.

Here is an example:

Create an external table named students_csv to store student information.

CREATE EXTERNAL TABLE students_csv external_options

Define columns

You cannot define constraints such as DEFAULT, NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY for columns of an external table.

The column types supported for an external table are the same as those for a regular table. For more information about the data types supported in the MySQL mode of OceanBase Database and details about these data types, see Overview of data types.

Define LOCATION

The LOCATION option specifies the path where the files of the external table are stored. Generally, the data files of an external table are stored in a dedicated directory, which can contain subdirectories. When you create an external table, the system automatically collects all files in the specified directory.

OceanBase Database supports the following two path formats:

• Local location format: LOCATION = '[file://] local_file_path'

  Notice

  For scenarios that 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'

  $ACCESS_ID, $ACCESS_KEY, and $HOST are required for accessing Alibaba Cloud OSS, Tencent Cloud COS, and Amazon S3 respectively. These sensitive access information is encrypted and stored in system tables of the database.

Define FORMAT

• FORMAT = ( TYPE = 'CSV'... ) specifies the CSV format for external files. The parameters are as follows:
  • TYPE: the type of the external file.
  • LINE_DELIMITER: the line delimiter for the CSV file. The default value is LINE_DELIMITER='\n'.
  • FIELD_DELIMITER: the field delimiter for the CSV file. The default value is FIELD_DELIMITER='\t'.
  • ESCAPE: the escape character for the CSV file, which can be only 1 byte in length. The default value is ESCAPE ='\'.
  • FIELD_OPTIONALLY_ENCLOSED_BY: the characters that enclose the field values in the CSV file. The default value is an empty string.
  • ENCODING: the character set encoding format of the file. For more information about the character sets supported in MySQL mode, see Character set and collation. If this parameter is not specified, the default value UTF8MB4 takes effect.
  • NULL_IF: the strings to be treated as NULL values. The default value is an empty string.
  • 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.
  • 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.
• FORMAT = ( TYPE = 'PARQUET'... ) specifies the Parquet format for external files.

(Optional) Define PATTERN

The PATTERN option specifies a regular pattern string to filter files in the LOCATION directory. For each file under the LOCATION directory, the external table accesses the file if the file path matches the pattern string, and skips the file if the file path does not match the pattern string. If this parameter is not specified, the external table accesses all files under the LOCATION directory by default. The external table stores the file list that matches the LOCATION directory and the PATTERN string in the system table of the database. During a scan, the external table accesses external files based on this list.

(Optional) Define partitions of the external table

Automatically define partitions of the external table

The external table automatically defines partitions based on the partitioning key expression. You can specify the value or range of the partitioning key in a query. In this case, the partitions are pruned, and the external table reads files only in the specified partitions.

Manually define partitions of the external table

If you want to manually add and drop partitions without letting the external table automatically manage partitions, specify the PARTITION_TYPE = USER_SPECIFIED option.

Examples

The following example describes how to create an external table in MySQL mode of OceanBase Database, where the external file is stored locally and on a remote server. The steps are as follows:

1. Create an external file.

   Execute the following command to create a file named test_tbl1.csv in the /home/admin directory of the server where you want to log in to.

   [admin@xxx /home/admin]# vi test_tbl1.csv
   

   The content of the file is as follows:

   1,'Emma','2021-09-01'
   2,'William','2021-09-02'
   3,'Olivia','2021-09-03'
   

2. Set the path of the imported file.

   Notice

   For security reasons, when you set the system variable secure_file_priv, you can connect to the database only by using a local socket and execute the SQL statement to modify the global variable. For more information, see secure_file_priv.

   1. Execute the following command to log in to the server where the OBServer node is located.

      ssh admin@10.10.10.1
      

   2. Execute the following command to connect to the mysql001 tenant by using a local Unix socket.

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

   3. Execute the following SQL command to set the import path to /home/admin.

      SET GLOBAL secure_file_priv = "/home/admin";
      

3. Reconnect to the mysql001 tenant.

   Here is an example:

   obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A -Dtest
   

4. Execute the following SQL command to create an external table named test_tbl1_csv.

   CREATE EXTERNAL TABLE test_tbl1_csv ( 
       id INT, 
       name VARCHAR(50), 
       c_date    DATE
       )
       LOCATION = '/home/admin'
       FORMAT = (
         TYPE = 'CSV'
         FIELD_DELIMITER = ','
         FIELD_OPTIONALLY_ENCLOSED_BY ='\''
         )
       PATTERN = 'test_tbl1.csv';
   

5. Execute the following SQL command to view the data of the external table named test_tbl1_csv.

   SELECT * FROM test_tbl1_csv;
   

   The return result is as follows:

   +------+---------+------------+
   | id   | name    | c_date     |
   +------+---------+------------+
   |    1 | Emma    | 2021-09-01 |
   |    2 | William | 2021-09-02 |
   |    3 | Olivia  | 2021-09-03 |
   +------+---------+------------+
   3 rows in set
   

References

• You can use the same method to drop an external table as you would for a regular table. For more information, see Drop a table.
• For more information about how to view and update the information about an external file, see Manage external files.