OceanBase AI Database supports reading CSV, Parquet, and ORC files directly by using SELECT or LOAD DATA, and supports ODPS data sources through SOURCE() or LOAD DATA.
External tables via URL are a metadata-free registration external data access capability provided by OceanBase AI Database. You specify the data file path and format directly in SELECT or LOAD DATA statements, and the system dynamically parses the data and returns the results, without the need to create an external table in advance.
Supported data sources and formats
Data source type |
Access method |
Supported formats |
Description |
|---|---|---|---|
| Local or object storage files | FILES() |
CSV / Parquet / ORC | Supports HDFS, S3, and S3-compatible object storage, as well as local files. |
| MaxCompute (ODPS) | SOURCE() |
ODPS table | Connects directly to ODPS Tunnel. No export is required. |
Notice
- The default data type of query results in a CSV file is
VARCHAR. You can convert the columns to other data types by using theCASTfunction. - Parquet and ORC files automatically infer schemas, including nested schemas.
- The number of files that can be read at one time is subject to cluster resources, external storage capacity, and network conditions. This document does not specify an upper limit on the number or size of files.
- ODPS does not support the
FILES()function. You must use theSOURCE()function.
Query external data
General syntax
Method 1: Using a location URL (simplified)
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 where the external table files are stored. Recursive scanning of subdirectories is supported.FORMAT: Specifies the format and parsing options for the external file.PATTERN: Specifies a regular expression pattern string to filter files in theLOCATIONdirectory. 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 (simplified)
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$'
);
Notice
outfiles/specifies the path where external table files are stored. Recursive scanning of subdirectories is supported.FORMATspecifies the external file format as CSV and the related parsing options.PATTERNspecifies a regular expression pattern string to filter files in theLOCATIONdirectory. If this parameter is 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 use the
CASTfunction to convert the columns to other data types.
The LOCATION parameter can be a string path (such as 's3://bucket/path/' or 'outfiles/') or the name of a created Location object (referenced by using @location_name). The syntax is based on 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 the 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 via 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, -- Use the @ prefix to reference a defined location.
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 via the FILES() function, if the table structure (column names and count) is not explicitly defined, the system automatically infers it:
- Number of columns: Sample the first row of a CSV file in the specified directory.
- Column names: By default, named sequentially as
'c1','c2', ...
Because the first row of many CSV files contains column names (the header) and subsequent rows contain data, OceanBase AI Database 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 c1, c2, ... are used.
SELECT * FROM FILES(
LOCATION = 's3://my-bucket/logs/',
FORMAT (
TYPE = 'CSV',
FIELD_DELIMITER = ',',
SKIP_BLANK_LINES = TRUE
),
PATTERN = 'user_log_202504.*\\.csv$'
);
-- The first row of a 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 row 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 format
SELECT * FROM FILES(
LOCATION = 'outfiles/',
FORMAT (TYPE = 'PARQUET'),
PATTERN = 'data$'
);
'outfiles/'specifies the path for storing external table files. Recursive scanning of subdirectories is supported.FORMATspecifies the file format asPARQUET.PATTERNis 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
-- Location URL format
SELECT * FROM 'outfiles/'
(
FORMAT (TYPE = 'ORC'),
PATTERN = 'data$'
);
-- Table function format
SELECT * FROM FILES(
LOCATION = 'outfiles/',
FORMAT (TYPE = 'ORC'),
PATTERN = 'data$'
);
'outfiles/'specifies the path, which supports subdirectories.FORMATspecifies the format asORC.PATTERNis 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)
The ODPS data source is not file-based and does not support FILES(). You must use 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 AI Database table, with optimizations such as parallelism and direct writing.
Syntax structure
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>'
)
LOCATIONspecifies the file path, supporting recursive scanning of subdirectories.- Local path: in the format
file://path/or/path/. The path must be a subdirectory of the path configured bysecure_file_priv. - Remote path: for example,
s3://bucket/path/. Sensitive information (such as the AccessKey) is encrypted by OceanBase AI Database and stored in system tables.
Note
Sensitive credentials (such as the AccessKey) are encrypted by OceanBase AI Database and stored in system tables. You can reuse preconfigured Location objects to avoid entering the credentials repeatedly in SQL statements. The syntax of Location is based on 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/ |
The path must be a subdirectory of the path configured by secure_file_priv. |
| S3 | s3://bucket/path/ |
Supports IAM roles and AccessKeys. |
| Object storage compatible with the S3 protocol (such as Alibaba Cloud OSS) | s3://bucket/path/ |
Configure the AccessKey on OBServer. |
| HDFS | hdfs://namenode:8020/path/ |
The Java SDK and Kerberos (if enabled) must be deployed. |
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. This is fast, but the operation cannot be rolled back.PARALLEL(N): Reads files in parallel (N ≤ number of files).