CREATE EXTERNAL TABLE|V4.2.2| docs|Distributed Database

CREATE EXTERNAL TABLE

Last Updated：2025-12-02 02:50:56 Updated

Description

This statement is used to create an external table in a database.

An external table is a key feature in a database management system. Generally, a table in a database stores data in the storage space of the database, while an external table stores data in an external storage service.

When you create an external table, you must specify the file path and file format of the data. After that, you can use the external table to read data from the external storage service. An external table is read-only. You can use it in a query statement, but you cannot perform DML operations on it. You cannot define constraints or create indexes on an external table.

Syntax

CREATE EXTERNAL TABLE <table_name>
    ( [ <column_name> <column_type> [AS <expr>] ]
      [ , <column_name> <column_type> [AS <expr>] ]
      [ , ... ] )
    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>' ]

Parameter description

Parameter	Description
table_name	The name of the external table to be created.
column_name	The name of a column in the external table. By default, the data columns in the file are automatically mapped to the definition columns of the external table in order.
column_type	The type of a column in the external table. You cannot define constraints such as `DEFAULT`, `NOT NULL`, `UNIQUE`, `CHECK`, `PRIMARY KEY`, or `FOREIGN KEY` for an external table column.
AS	Manually specifies column mappings. When the order of columns in a file is inconsistent with the definition order of columns in the external table, you can specify the correspondence between a column in the external table and the Nth column in the file by using the pseudo-column `metadata$filecol{N}`. For example, `c2 INT AS (metadata$filecol4)` indicates that the `c2` column in the external table corresponds to the fourth column in the file. Note that if you specify manual column mappings, automatic mappings will become invalid, and mappings must be defined manually for all columns.
LOCATION	The path where the external table files are 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 specified directory. The local `LOCATION` is specified in the `LOCATION = '[file://] local_file_path'` format, where `local_file_path` can be a relative path or an absolute path. If an absolute path is used, the current directory must be the installation directory of OceanBase Database. The `secure_file_priv` variable specifies the file path that the OBServer node has the permission to access. `local_file_path` can only be a subpath of the path specified by `secure_file_priv`. The remote `LOCATION` is specified in the `LOCATION = '{oss\\|cos}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'` format, where `$ACCESS_ID`, `$ACCESS_KEY`, and `$HOST` are the access information required when you access OSS/COS. The 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 object storage path with `&`. Make sure that the characters in the parameter values you entered are in uppercase or lowercase Latin letters, digits, and contain only `/-_$+=` and wildcard characters. If you enter characters other than the aforesaid ones, the setting may fail.
FORMAT	The format of external files. `TYPE`: the type of external files. Currently, only the CSV format is supported. `LINE_DELIMITER`: the line delimiter for CSV files. The default value is `LINE_DELIMITER='\n'`. `FIELD_DELIMITER`: the field delimiter for CSV files. The default value is `FIELD_DELIMITER='\t'`. `ESCAPE`: the escape character for CSV files. It can be only 1 byte in length. The default value is `ESCAPE ='\'`. `FIELD_OPTIONALLY_ENCLOSED_BY`: the characters that enclose field values in CSV files. 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 the MySQL mode of OceanBase Database, see Character sets. If this parameter is not specified, the default value UTF8MB4 takes effect. `NULL_IF`: the string that is treated as `NULL`. 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. The default value is `FALSE`, which specifies not to remove leading and trailing spaces from fields. `EMPTY_FIELD_AS_NULL`: specifies whether to treat an empty string as `NULL`. The default value is `FALSE`, which specifies not to treat an empty string as `NULL`.
PATTERN	A regular expression pattern used to filter files in the `LOCATION` directory. For each file in the `LOCATION` directory, if the file path matches the pattern, the external table accesses the file. Otherwise, the external table skips the file. If this parameter is not specified, the external table, by default, accesses all files in the `LOCATION` directory. The external table stores the list of files that match the `PATTERN` in the system table of the database. When the external table scans files, it accesses external files based on this list.

Usage

If an external file is deleted, files in the file list of the external table will no longer exist. In this case, the external table will ignore the non-existent file.
If an external file is modified, the external table can access the latest data of the external file. If the modification of the external file is concurrent with the query of the external table, the result may not meet the expectations. Therefore, you are advised to avoid modifying the external file while querying the external table.
If a new file is added to the external directory, the external table will not access the new file. If you want to add the new file to the file list of the external table, you need to update the file list of the external table.

Examples

Prepare data. First, set the path of secure_file_priv to /home/admin/. Place the CSV file named extdata.csv, which contains the data to be imported as the external table, in the /home/admin/test directory on the local OBServer node that is connected.

Here is an example of setting the global secure path.
```
obclient> SET GLOBAL secure_file_priv = ""
Query OK, 0 rows affected
obclinet> \q
Bye
```
Note

Since secure_file_priv is a GLOBAL variable, you need to execute \q to make the setting take effect.

The CSV file contains the following data:
```
1,'Dave','Smith','dsmith@outlook.com','friend',32
2,'Xena','Johnson','xjonson@outlook.com','contact',45
3,'Fred','Jackon','fjackson@outlook.com','co-worker',19
4,'Alma','Tyler','atyler@outlook.com','friend',53
```

obclient> CREATE EXTERNAL TABLE contacts (
    id    INT,
    firstname  VARCHAR(100),
    lastname   VARCHAR(100),
    email      VARCHAR(255),
    category   CHAR(30),
    age        NUMBER
   )
   LOCATION = '/home/admin/test'
   FORMAT = (
     TYPE = 'CSV'
     FIELD_DELIMITER = ','
     FIELD_OPTIONALLY_ENCLOSED_BY ='\''
    )
  PATTERN = 'extdata.csv';

Query data in the contacts external table.

obclient> SELECT * FROM contacts;
+----+-----------+----------+----------------------+-----------+------+
| id | firstname | lastname | email                | category  | age  |
+----+-----------+----------+----------------------+-----------+------+
|  1 | Dave      | Smith    | dsmith@outlook.com   | friend    |   32 |
|  2 | Xena      | Johnson  | xjonson@outlook.com  | contact   |   45 |
|  3 | Fred      | Jackon   | fjackson@outlook.com | co-worker |   19 |
|  4 | Alma      | Tyler    | atyler@outlook.com   | friend    |   53 |
+----+-----------+----------+----------------------+-----------+------+
4 rows in set

References

Create an external table

Manage external files

Update the file list of an external table