Create an external table|V4.2.1|OceanBase Database| docs|Distributed Database

Create an external table

Last Updated：2026-07-10 03:29:09 Updated

You can execute the CREATE EXTERNAL TABLE statement to create an external table. When you create an external table, you must specify the path to the data file and the format of the data file to read data from external files.

Privilege requirements

To create an external table, the current user must have the CREATE privilege. To view the privileges of the current user, see View user privileges.

Create an external table

The SQL statement for creating an external table is as follows:

CREATE EXTERNAL TABLE table_name
( col_name col_type [AS (metadata$filecol{N})] 
  [ , col_name col_type [AS (metadata$filecol{N})] ]
  [ , ... ] )
LOCATION = '<string>'
FORMAT = (
  TYPE = 'CSV'
  LINE_DELIMITER = '<string>' | <expr>
  FIELD_DELIMITER = '<string>' | <expr>
  ESCAPE = '<character>' | <expr>
  FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>
  ENCODING = 'charset'
  NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...)
  SKIP_HEADER = <int>
  SKIP_BLANK_LINES = { TRUE | FALSE }
  TRIM_SPACE = { TRUE | FALSE }
  EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
)
[ PATTERN = '<regex_pattern>' ]

The following table describes the parameters.

col_name col_type [AS (metadata$filecol{N})]: specifies the columns. You can use AS (metadata$filecol{N}) to manually specify column mapping.

The column types supported by external tables are the same as those supported by regular tables. For more information about the data types supported in MySQL mode of OceanBase Database, see Overview.

By default, the columns in an external file are automatically mapped to those defined in the external table in order. That is, the first column in the external table corresponds to the first column of data in the external file.

For example, in the following example, column C1 in the external table ext_t1 is automatically mapped to the data in the first column of the external file; and column C2 is automatically mapped to the data in the second column of the external file.
```
CREATE EXTERNAL TABLE ext_t1 (
  C1 int,
  C2 int
  )
  LOCATION = 'oss://$ACCESS_ID:$ACCESS_KEY@$HOST/tpch_1g_data/lineitem/'
  FORMAT = (
  TYPE = 'CSV'
  FIELD_DELIMITER = '|'
  );
```
If the columns in the external file are in different orders from those defined in the external table, you can use a pseudo-column in the format of metadata$filecol{N} to specify that the Nth column in the external file corresponds to the column in the external table. Note that the columns in the file are numbered starting from 1.

For example, in the following example, C1 int AS (metadata$filecol2) indicates that the C1 column in the ext_t2 external table corresponds to the second column in the file; and C2 int AS (metadata$filecol4) indicates that the C2 column in the ext_t2 external table corresponds to the fourth column in the external file.
```
CREATE EXTERNAL TABLE ext_t2 (
            C1 int AS (metadata$filecol2),
            C2 int AS (metadata$filecol4)
  )
  LOCATION = 'oss://$ACCESS_ID:$ACCESS_KEY@$HOST/tpch_1g_data/lineitem/'
  FORMAT = (
  TYPE = 'CSV'
  FIELD_DELIMITER = '|'
  );
```
Notice

If you want to manually specify column mapping, the automatic column mapping feature will fail and you will need to manually specify the mapping for all columns.
LOCATION = '<string>': specifies the path where the external file is stored. Generally, the data files of an external table are stored in a separate directory, which can contain subdirectories. When you create an external table, the system automatically collects all files in the directory.

Two formats are supported:
- Local location format: LOCATION = '[file://] local_file_path'
  - local_file_path: can be a relative or absolute path. If you enter a relative path, the current directory must be the installation directory of OceanBase Database. In absolute paths, the path separator '/' must be used, and the path cannot contain the drive letter.
    
    Notice
    
    In the local location format, the value of secure_file_priv must be the parent directory of local_file_path. In other words, local_file_path must be a subdirectory of secure_file_priv.
  - For the local location format, when you use the system variable secure_file_priv to specify the file path that OceanBase Database can access, the value of secure_file_priv must be the parent directory of local_file_path. In other words, local_file_path must be a subdirectory of secure_file_priv.
    
    The system variable secure_file_priv specifies the path where OceanBase Database can access when importing data to or exporting data from a file. For more information about the secure_file_priv variable, see secure_file_priv.
- Remote location format: LOCATION = '{oss|cos}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'
  
  $ACCESS_ID, $ACCESS_KEY, and $HOST are the access information required to access Alibaba Cloud OSS or Tencent Cloud COS. These sensitive access information is stored in encrypted form in the system tables of the database.
  
  Notice
  
  When you use an object storage path, separate the parameters in the path with the & character. Make sure that the values of the parameters you entered contain only uppercase and lowercase letters, digits, and the /-_$+= characters and an asterisk (*). If you enter any other character, the setting may fail.
FORMAT: specifies the format of external files.
- TYPE: specifies the type of external files. Only CSV files are supported.
- LINE_DELIMITER: specifies the line delimiter of a file. If you do not specify a delimiter, the default value LINE_DELIMITER='\n' is used.
- FIELD_DELIMITER: specifies the column delimiter of a file. If you do not specify a delimiter, the default value FIELD_DELIMITER='\t' is used.
- ESCAPE: specifies the escape character of a file. For example, ESCAPE ='*' specifies to use an asterisk (*) as the escape character, replacing the default escape character (). If you do not specify an escape character, the default value ESCAPE ='\' is used.
- FIELD_OPTIONALLY_ENCLOSED_BY: specifies the characters that enclose field values in a file. For example, ESCAPE = '"' specifies to enclose field values in double quotation marks. If you do not specify characters to enclose field values, no characters are used by default.
- ENCODING: specifies the character set encoding format of a file. For more information about the character sets supported in MySQL mode of OceanBase Database, see Character sets. If you do not specify a character set encoding format, the default value UTF8MB4 is used.
- NULL_IF: specifies the strings that are to be treated as NULL values. If you do not specify any strings, no string is treated as a NULL value by default.
- SKIP_HEADER: specifies the number of lines to skip in the header of a file. If you do not specify a value, the default is to skip no lines.
- SKIP_BLANK_LINES: specifies whether to skip blank lines. If you do not specify a value, the default is FALSE.
- TRIM_SPACE: specifies whether to remove leading and trailing spaces from fields in an external file. If you do not specify a value, the default is FALSE.
- EMPTY_FIELD_AS_NULL: specifies whether to treat empty strings as NULL values. If you do not specify a value, the default is FALSE.
PATTERN: specifies a regular pattern string to filter files in the directory specified in LOCATION. For each file in the directory specified in LOCATION, if the file matches the pattern string, the external table can access the file. Otherwise, the external table will skip the file. If you do not specify this parameter, the external table can access all files in the directory specified in LOCATION by default.

Assume that a file named data.csv exists in the /home/admin/oceanbase/ directory on the local server, and the file contains the following data.

1,"lin",98
2,"hei",90
3,"ali",95

On the OBServer node, connect to the MySQL tenant of the cluster as the tenant administrator through the local Unix socket.

The connection example is as follows:
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********
```
For more information about how to connect to OceanBase Database through the local Unix socket, see secure_file_priv.
Specify the path /home/admin/oceanbase/ as the directory that OceanBase Database can access.
```
SET GLOBAL secure_file_priv = "/home/admin/oceanbase/";
```
After the command is executed, you need to restart the session for the modification to take effect.

Reconnect to the database and create an external table named ext_t3.

CREATE EXTERNAL TABLE ext_t3(id int, name char(10),score int)
LOCATION = '/home/admin/oceanbase/'
FORMAT = (
TYPE = 'CSV'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY ='"'
)
PATTERN = 'data.csv';

After the external table is created, you can execute the SHOW CREATE TABLE statement to view the definition of the table, just like you do with a regular table.

SHOW CREATE TABLE ext_t3;

The query result is as follows:

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ext_t3 | CREATE EXTERNAL TABLE `ext_t3` (
  `id` int(11) GENERATED ALWAYS AS (metadata$filecol1),
  `name` char(10) GENERATED ALWAYS AS (metadata$filecol2),
  `score` int(11) GENERATED ALWAYS AS (metadata$filecol3)
)
LOCATION='file:///home/admin/oceanbase/'
PATTERN='data.csv'
FORMAT (
  TYPE = 'CSV',
  FIELD_DELIMITER = ',',
  FIELD_OPTIONALLY_ENCLOSED_BY = '"',
  ENCODING = 'utf8mb4'
)DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

You can also access the external table like a regular table. When you query an external table, the system reads the external file through the driver of the external table and parses the file according to its format. Then, the system converts the data in the external file into the data types used in OceanBase Database and returns the data rows. Here is an example of how to query the external table lineitem that you have just created.

SELECT * FROM ext_t3;

The query result is as follows:

+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | lin  |    98 |
|  2 | hei  |    90 |
|  3 | ali  |    95 |
+----+------+-------+
3 rows in set

You can also combine an external table with regular tables for queries. Assume that the current database contains a regular table named info, whose data is as follows:

+------+--------+------+
| name | sex    | age  |
+------+--------+------+
| lin  | male   |    8 |
| hei  | male   |    9 |
| li   | female |    8 |
+------+--------+------+
3 rows in set

Here is an example of how to combine the external table ext_t3 and the regular table info for queries.

SELECT info.* FROM info, ext_t3 WHERE info.name = ext_t3.name AND ext_t3.score > 90;

The query result is as follows:

+------+--------+------+
| name | sex    | age  |
+------+--------+------+
| lin  | male   |    8 |
| li   | female |    8 |
+------+--------+------+
2 rows in set

For more information about queries, see Read data.

Considerations for using an external table

An external table can only be queried, and does not support DML operations.
When you query an external table, if the external file accessed by the external table is deleted, the system does not return an error, but returns an empty result set.
If the external storage system fails, an error is returned when you query the external table because the file managed by the external storage system does not exist.

What to do next

When you create an external table, the system saves the file list that matches the specified PATTERN in the LOCATION directory to a system table in OceanBase Database. The system uses this file list to locate external files when the database scans the external table. If you add new files to the external directory, you must perform an operation to add the new files to the file list of the external table. For more information, see Manage external files of an external table.

You can use the DROP TABLE statement to drop an external table, just like you drop a regular table. For more information, see Drop a table.

OceanBase

Customer Stories

Documentation

Create an external table

Privilege requirements

Create an external table

Notice

Notice

Notice

Considerations for using an external table

What to do next

References