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

CREATE EXTERNAL TABLE

Last Updated：2025-12-02 02:51:29 Updated

Description

You can execute this statement to create an external table in a database.

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

When you create an external table, you must specify the file path and file format of the data. Then, you can read the data of the file from the external storage service by using the external table. 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>' ]

Parameters

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.
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 the external table columns.
AS	Manually specifies the column mapping. If the order of columns in the file does not match that in the external table, you can use the pseudo-column `metadata$filecol{N}` to specify the mapping between the Nth column in the file and the external table column. 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 mapping, automatic mapping relationships will become invalid, and you must define mappings for all columns manually.
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` parameter is specified in the format of `LOCATION = '[file://] local_file_path'`, where `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. The `secure_file_priv` parameter specifies the file path that OBServer nodes have the permission to access. `local_file_path` must be a subpath of `secure_file_priv`. The remote `LOCATION` parameter is specified in the format of `LOCATION = '{oss\\|cos}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'`, where `$ACCESS_ID`, `$ACCESS_KEY`, and `$HOST` are required for accessing OSS/COS. These sensitive access information will be encrypted and stored in the system tables of the database. Notice When you use object storage paths, separate the parameters in `&`. Make sure that the characters in the value of each parameter are uppercase or lowercase letters, digits, and characters from the `/-_$+=` set, or wildcard characters. If you enter characters other than the preceding ones, the setting may fail.
FORMAT	The format of external files. `TYPE`: the type of external files. Only the CSV type 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 only be 1 byte in length. The default value is `ESCAPE ='\'`. `FIELD_OPTIONALLY_ENCLOSED_BY`: the characters that enclose the field values in CSV files. The default value is an empty string. `ENCODING`: the character set encoding format of files. For more information about the character sets supported in Oracle mode, see Character sets and collations. If this parameter is not specified, the default value UTF8MB4 takes effect. `NULL_IF`: the strings that are 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 files. The default value is `FALSE`, which specifies not to remove leading and trailing spaces from fields in files. `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.
PATTERN	A regular pattern string used to filter 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, the external table accesses all files in the directory specified by `LOCATION`. The external table stores the list of files that match the `LOCATION` parameter value in the database system table and accesses external files during a scan based on this list. The file list can be automatically updated or manually updated.

Usage notes

If an external file is deleted, the file no longer exists in the file list when you access the file list. 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 the external file concurrent with a query to the external table returns unexpected results, do not modify the external file when querying the external table.
The external table accesses only the files listed 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, you need to update the file list of the external table.

Examples

Prepare data. You need to set the value of secure_file_priv to /home/admin/. Place the extdata.csv file, which corresponds to the external table data to be imported, in the /home/admin/test directory on the current locally connected OBServer node.

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, you must execute \q to make the setting take effect.

The content of the CSV file is as follows:
```
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