External tables via URL|V4.6.0|OceanBase Database| docs|Distributed Database

External tables via URL

Last Updated：2026-07-23 10:04:28 Updated

Starting from V4.3.5 BP1 (MySQL-compatible mode/Oracle-compatible mode), OceanBase Database supports directly reading CSV, Parquet, and ORC files using SELECT or LOAD DATA. Starting from V4.3.5 BP1, it also supports ODPS data sources (SOURCE() / LOAD DATA). This capability is suitable for the following typical AP scenarios:

Temporary data exploration
Rapid data loading

External tables via URL are a metadata-free registration external data access capability provided by OceanBase. Users directly specify the data file path and format using SELECT or LOAD DATA statements. The system dynamically parses and returns the results, without the need to pre-create an external table.

Supported data sources and formats

Data Source Type	Access Method	Supported formats	Description
Local/Object Storage Files	`FILES()`	CSV / Parquet / ORC	Supports HDFS, OSS, S3, and S3-compatible object storage, as well as local files.
MaxCompute (ODPS)	`SOURCE()`	ODPS table	Connect directly to ODPS Tunnel, no export required

Notice

The default data type of query results in a CSV file is VARCHAR (you can convert it to another data type by using the CAST function).
Parquet and ORC files automatically infer schemas, including nested schemas.
The amount of data that can be read at one time is limited by cluster resources, external storage, and network conditions. This document does not specify the maximum number or size of files that can be read.
ODPS does not support the FILES() function. You must use the SOURCE() function (currently, the syntax is mainly based on the LOAD DATA syntax of MySQL mode).
URL external tables (FILES() / Location URL) are supported in both MySQL mode and Oracle mode. For specific syntax, see the SQL reference of each mode.

Query external data

General syntax

Method 1: Using a location URL (brief)

SELECT * FROM 'outfiles/'
(
    FORMAT (TYPE = 'format_type', FIELD_DELIMITER = ','),
    PATTERN = 'regex'
);

Method 2: Using a table function (flexible, recommended)

SELECT * FROM FILES(
    LOCATION = 'outfiles/',
    FORMAT (TYPE = 'format_type', FIELD_DELIMITER = ','),
    PATTERN = 'regex'
);

'outfiles/': Specifies the path for storing external table files. Recursive scanning of subdirectories is supported.
FORMAT: Specifies the format and parsing options of the external file.
PATTERN: Specifies a regular expression pattern string to filter files in the LOCATION directory. If not specified, all files are read by default.

Query examples for each format

CSV files

General syntax

URL external tables support two equivalent syntax forms for reading CSV files. You must specify TYPE = 'CSV':

Using a location URL (brief)

SELECT * FROM 'outfiles/'
(
    FORMAT (
        TYPE = 'CSV',
        FIELD_DELIMITER = ','
    ),
    PATTERN = 'data$'
);

Using a table function (flexible, recommended)

SELECT * FROM FILES(
    LOCATION = 'outfiles/',
    FORMAT (
        TYPE = 'CSV',
        FIELD_DELIMITER = ','
    ),
    PATTERN = 'data$'
);

Note

outfiles/ specifies the path for storing external table files. Recursive scanning of subdirectories is supported.

FORMAT specifies the external file format as CSV and related parsing options.

PATTERN specifies a regular expression pattern string to filter files in the LOCATION directory. If not specified, all files are read.

Important: When you use a URL external table to read a CSV file, the data type of all columns is VARCHAR by default (you can convert it to another type by using the CAST function later).

The LOCATION parameter can specify a path in string format (such as 'oss://bucket/path/' or 'outfiles/') or the name of an existing Location object (referenced by using @location_name). The syntax is subject to the SQL reference of the current version.

Example 1: Create a persistent external table and reference a Location object

Suitable for external table scenarios requiring long-term use, where table metadata is stored in the database.

-- First, define a Location object named my_hdfs_loc.
CREATE LOCATION my_hdfs_loc URL = 'hdfs://namenode:8020/data/';

-- Reference this location when creating an external table
CREATE EXTERNAL TABLE ex_t1 (
    c1 INT,
    c2 INT,
    c3 INT
)
LOCATION = @my_hdfs_loc
FORMAT (
    TYPE = 'csv',
    FIELD_DELIMITER = ',',
    LINE_DELIMITER = '\n'
);

Example 2: Reference a Location object using FILES() in a temporary query

Suitable for one-time analysis of external files without creating a persistent table.

-- Assume a Location object named my_hdfs_loc already exists.
CREATE LOCATION my_hdfs_loc URL = 'hdfs://namenode:8020/data/';

-- Reference directly in the FILES table function
SELECT * FROM FILES(
    LOCATION = @my_hdfs_loc,      -- Reference a predefined location by prefixing it with @
    FORMAT (TYPE = 'PARQUET'),
    PATTERN = '.*\\.parquet$'     -- Matches all .parquet files
);

Example 3: Specify the path directly without using a Location object

Suitable for simple or temporary scenarios. When the path does not need to be reused, you can directly write the URL or path into the query. Two equivalent syntaxes are supported.

-- Method 1: Use the path as the table name (short form)
SELECT * FROM '/data/'
(
    FORMAT (TYPE = 'CSV', FIELD_DELIMITER = ',', SKIP_BLANK_LINES = TRUE),
    PATTERN = '^datafiles.*\\.csv$'  -- Note: PATTERN is a regular expression.
);

-- Method 2: Use the FILES table function (explicit, recommended for complex configurations)
SELECT * FROM FILES(
    LOCATION = '/data/',
    FORMAT (TYPE = 'CSV', FIELD_DELIMITER = ',', SKIP_BLANK_LINES = TRUE),
    PATTERN = '^datafiles.*\\.csv$'
);

Supports header parsing

When accessing a CSV file using the FILES() function, if the table structure (column names and number) is not explicitly defined, the system will automatically infer it:

Number of columns: Sample the first line of a CSV file in the specified directory.
Column names: By default, named sequentially as 'c1', 'c2', ...

Since the first row of many CSV files contains column names (Header) and subsequent rows contain data, OceanBase provides the PARSE_HEADER configuration parameter to control whether to parse the first row as column names.

If the first row of the CSV file contains column names: Set PARSE_HEADER = TRUE.
If the first row of the CSV file contains actual data: Keep PARSE_HEADER = FALSE (default).

-- For basic queries, the first row of a CSV file contains data (no header), and the default column names are c1, c2, and so on.
SELECT * FROM FILES(
    LOCATION = 'oss://my-bucket/logs/',
    FORMAT (
        TYPE = 'CSV',
        FIELD_DELIMITER = ',',
        SKIP_BLANK_LINES = TRUE
    ),
    PATTERN = 'user_log_202504.*\\.csv$'
);

-- The first row of the CSV file contains column names (such as name, age, and city). Enable PARSE_HEADER.
SELECT * FROM FILES(
    LOCATION = '/data/sales.csv',
    FORMAT (
        TYPE = 'CSV',
        FIELD_DELIMITER = ',',
        PARSE_HEADER = TRUE  -- Parse the first line as column names
    )
);

Limitation: PARSE_HEADER and SKIP_HEADER are mutually exclusive and cannot be used together.

Parquet files (schema inferred automatically)

-- Location URL Format
SELECT * FROM 'outfiles/'
(
    FORMAT (TYPE = 'PARQUET'),
    PATTERN = 'data$'
);

-- Table Function
SELECT * FROM FILES(
    LOCATION = 'outfiles/',
    FORMAT (TYPE = 'PARQUET'),
    PATTERN = 'data$'
);

'outfiles/' specifies the path for storing external table files, supporting recursive scanning of subdirectories.
FORMAT specifies the file format as PARQUET.
PATTERN is used for file filtering (regular expression matching).

-- Practical example: Read a Parquet partitioned file from S3
SELECT * FROM FILES(
    LOCATION = 's3://analytics-bucket/events/',
    FORMAT (TYPE = 'PARQUET'),
    PATTERN = 'part-.*\\.parquet$'
);

-- Read all Parquet files starting with "datafiles" under the '/data/' path.
SELECT * FROM '/data/' (
    FORMAT = (TYPE = 'PARQUET'),
    PATTERN = 'datafiles$'
);

SELECT * FROM FILES(
    LOCATION = '/data/',
    FORMAT = (TYPE = 'PARQUET'),
    PATTERN = 'datafiles$'
);

ORC files

-- URL Location Format
SELECT * FROM 'outfiles/'
(
    FORMAT (TYPE = 'ORC'),
    PATTERN = 'data$'
);

-- Table Function
SELECT * FROM FILES(
    LOCATION = 'outfiles/',
    FORMAT (TYPE = 'ORC'),
    PATTERN = 'data$'
);

'outfiles/' specifies the path, which supports subdirectories.
FORMAT specifies the format as ORC.
PATTERN is used for file filtering.

-- Example: Read an ORC file from HDFS
SELECT * FROM 'hdfs://mycluster/data/orc/'
(
    FORMAT (TYPE = 'ORC'),
    PATTERN = 'clicks_202504.*'
);

ODPS tables (direct connection to MaxCompute)

ODPS data sources are not in file format and do not support FILES(), only SOURCE():

SELECT * FROM SOURCE(
    TYPE = 'ODPS',
    ACCESSID = 'LTAI5tXXXXXX',
    ACCESSKEY = 'xxxxxxxxxxxxxx',
    ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
    TUNNEL_ENDPOINT = 'http://dt.cn-hangzhou.maxcompute.aliyun.com',
    PROJECT_NAME = 'sales_analytics',
    TABLE_NAME = 'user_behavior'
);

Import external data (LOAD DATA)

Efficiently import external data into an internal OceanBase table, supporting optimizations such as parallelism and direct writing.

Syntax

LOAD DATA
    [/*+ INSERT HINT */]
    [REPLACE | IGNORE]
    FROM {
        <url_table_function_expr> |
        ( SELECT expression_list FROM <url_table_function_expr> )
    }
    INTO TABLE table_name
    [PARTITION (partition_name1, [partition_name2 ...])]
    [(column_name_var [, column_name_var] ...)]

Where <url_table_function_expr> is:

FILES (
    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 }
        )
        | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
    },
    [PATTERN = '<regex_pattern>']
)
| SOURCE (
    TYPE = 'ODPS',
    ACCESSID = '<string>',
    ACCESSKEY = '<string>',
    ENDPOINT = '<string>',
    TUNNEL_ENDPOINT = '<string>',
    PROJECT_NAME = '<string>',
    SCHEMA_NAME = '<string>',
    TABLE_NAME = '<string>',
    QUOTA_NAME = '<string>',
    COMPRESSION_CODE = '<string>'
)

LOCATION specifies the file path, supporting recursive scanning of subdirectories.
Local path: in the format file://path/ or /path/, and must be a subdirectory of the path configured by secure_file_priv.
Remote path: for example, oss://bucket/path/. Sensitive information (such as the AccessKey) is encrypted by OceanBase and stored in system tables.

Note

Sensitive credentials (such as the AccessKey) are encrypted by OceanBase and stored in system tables. You can reuse preconfigured Location objects to avoid repeated manual entry in SQL statements. (The syntax for Location objects follows the SQL reference.)

Import examples

Import CSV (all columns or specified columns)

-- Import all columns from data1.csv
LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM FILES(
    LOCATION = 'data/csv',
    FORMAT = (
        TYPE = 'CSV',
        FIELD_DELIMITER = ',',
        SKIP_BLANK_LINES = TRUE
    ),
    PATTERN = 'data1.csv'
)
INTO TABLE csv_ex_t1;

-- Import only columns c1 and c2 to target columns col1 and col2
LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM (
    SELECT c1, c2 FROM FILES(
        LOCATION = 'data/csv',
        FORMAT = (
            TYPE = 'CSV',
            FIELD_DELIMITER = ',',
            SKIP_BLANK_LINES = TRUE
        ),
        PATTERN = 'data1.csv'
    )
)
INTO TABLE csv_ex_t1 (col1, col2);

Import Parquet

LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM FILES(
    LOCATION = 'data/parquet',
    FORMAT = (TYPE = 'PARQUET'),
    PATTERN = 'data1.parquet'
)
INTO TABLE parquet_ex_t1;

Import ORC

LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM FILES(
    LOCATION = 'data/orc',
    FORMAT = (TYPE = 'ORC'),
    PATTERN = 'data1.orc'
)
INTO TABLE orc_ex_t1;

Import from ODPS

LOAD DATA
FROM SOURCE (
    TYPE = 'ODPS',
    ACCESSID = '$ODPS_ACCESSID',
    ACCESSKEY = '$ODPS_ACCESSKEY',
    ENDPOINT = '$ODPS_ENDPOINT',
    PROJECT_NAME = 'example_project',
    SCHEMA_NAME = '',
    TABLE_NAME = 'example_table',
    QUOTA_NAME = '',
    COMPRESSION_CODE = ''
)
INTO TABLE odps_ex_t1;

Parameters

LOCATION path format

Type	Format example	Description
Local File	`file://data/`or`/data/`	To be configured`secure_file_priv`, whose path is its subdirectory
OSS	`oss://bucket/path/`	Configure AccessKey on OBServer
S3	`s3://bucket/path/`	Supports IAM roles and AccessKeys
HDFS	`hdfs://namenode:8020/path/`	Java SDK and Kerberos required (if enabled)

CSV format options (common)

Parameter	Default Value	Description
`FIELD_DELIMITER`	`,`	Field delimiter
`PARSE_HEADER`	`FALSE`	Whether to use the first row as column names
`SKIP_BLANK_LINES`	`FALSE`	Skip blank lines
`EMPTY_FIELD_AS_NULL`	`FALSE`	Convert empty fields to NULL
`ENCODING`	`utf8`	File Encoding

Performance hints (LOAD DATA only)

/*+ DIRECT(TRUE) PARALLEL(8) */

DIRECT(TRUE): Bypasses the transaction layer and writes directly to the storage engine (high speed but non-scrollable).
PARALLEL(N): Reads files in parallel (N ≤ number of files).

OceanBase

Customer Stories

Documentation

External tables via URL

Supported data sources and formats

Notice

Query external data

General syntax

Method 1: Using a location URL (brief)

Method 2: Using a table function (flexible, recommended)

Query examples for each format

CSV files

Note

Parquet files (schema inferred automatically)

ORC files

ODPS tables (direct connection to MaxCompute)

Import external data (LOAD DATA)

Syntax

Note

Import examples

Import CSV (all columns or specified columns)

Import Parquet

Import ORC

Import from ODPS

Parameters

LOCATION path format

CSV format options (common)

Performance hints (LOAD DATA only)

References