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

CREATE EXTERNAL TABLE

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

Description

This statement creates a new external table in the database.

External tables are a key feature in a database management system. Data of a regular table is stored in the storage space of the database, while an external table refers to data stored 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` in this parameter.
AS	Manually specifies column mappings. When the order of the columns in the file is different from that defined 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 manually specify column mappings, automatic mappings become invalid. You must manually specify mappings for all columns.
LOCATION	The path where the external table is stored. Generally, the data file of an external table is stored in a separate directory. The directory can contain subdirectories. When you create an external table, the system automatically collects all files in the specified directory. The local `LOCATION` parameter is in the format of `LOCATION = '[file://] local_file_path'`. `local_file_path` can be a relative path or an absolute path. If you specify a relative path, the current directory must be the installation directory of OceanBase Database. `secure_file_priv` specifies the file path that the OBServer node has the permission to access. `local_file_path` must be a subpath of `secure_file_priv`. The remote `LOCATION` parameter is in the format of `LOCATION = '{oss\\|cos}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'`. `$ACCESS_ID`, `$ACCESS_KEY`, and `$HOST` are required when you access OSS/COS. These sensitive access information is stored in encrypted form in the system tables of the database. Notice When you use object storage paths, separate the parameters in the object storage path with `&`. Make sure that the characters in the value of each parameter in the object storage path are uppercase or lowercase letters, digits, or `/-_$+=`. If you enter characters other than the preceding ones, the configuration may fail.
FORMAT	The format of the external file. `TYPE`: the type of the external file. Only the CSV format is supported at present. `LINE_DELIMITER`: the line delimiter for a CSV file. The default value is `LINE_DELIMITER='\n'`. `FIELD_DELIMITER`: the field delimiter for a CSV file. The default value is `FIELD_DELIMITER='\t'`. `ESCAPE`: the escape character for a CSV file. It can be only 1 byte in length. The default value is `ESCAPE ='\'`. `FIELD_OPTIONALLY_ENCLOSED_BY`: the characters that enclose field values in a CSV file. The default value is an empty string. `ENCODING`: the character set encoding format of the file. For more information about character sets supported in Oracle mode, see Character set and collation. If you do not specify the encoding, 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 indicates 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 pattern string that is used to filter files in the directory specified by the `LOCATION` parameter. For each file in the directory specified by the `LOCATION` parameter, 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 you do not specify this parameter, the external table, by default, accesses all files in the directory specified by the `LOCATION` parameter. The external table stores the list of files that match the `PATTERN` parameter in the specified `LOCATION` in the system table of the database. When the external table is scanned, it accesses external files based on this list. The file list can be automatically updated or manually updated.

Considerations

If an external file is deleted, the file no longer exists in the file list when you access the external table. In this case, the external table ignores the missing file.
If an external file is modified, the external table accesses the latest data of the external file. If the modification of an external file conflicts with a query on the external table, the result of the query may be unexpected. Therefore, avoid modifying external files while querying the external table.
The external table accesses only the files in the file list when a new file is added to the external directory. To add the new file to the file list of the external table, execute the ALTER EXTERNAL TABLE statement.

Examples

Set the secure_file_priv path to /home/admin/ and place the CSV file extdata.csv to be imported 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

After you set the secure_file_priv variable to a global variable, you must execute the \q statement 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 from 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