Starting from OceanBase Database V4.3.5 BP1, you can directly read data from CSV, Parquet, ORC files, or an ODPS table by using the SELECT statement without manually creating an external table. This feature is suitable for the following typical AP scenarios:
- Data exploration
- Rapid data loading
URL external tables are an unregistered external data access feature provided by OceanBase Database. You can directly specify the data file path and format by using the SELECT or LOAD DATA statement. The system dynamically parses the data and returns the result. You do not need to create an external table in advance.
Supported data sources and formats
Data source type |
Access method |
Supported format |
Description |
|---|---|---|---|
| Local / object storage file | FILES() |
CSV / Parquet / ORC | Supports HDFS, OSS, S3, and object storage systems that are compatible with the S3 protocol, as well as local files |
| MaxCompute (ODPS) | SOURCE() |
ODPS table | Directly connects to the ODPS Tunnel without requiring export |
Notice
- CSV query results are of the
VARCHARtype by default (you can use theCASTfunction to convert the data type later).
- Parquet/ ORC automatically infers the schema, including nested structures.
- There is no limit on the size of a single file that can be read.
- ODPS does not support the
FILES()function. You must use theSOURCE()function.
Query external data
General syntax
Method 1: Use the location URL format (simple)
SELECT * FROM 'outfiles/'
(
FORMAT (TYPE = 'format_type', FIELD_DELIMITER = ','),
PATTERN = 'regex'
);
Method 2: Use the table function format (flexible and recommended)
SELECT * FROM FILES(
LOCATION = 'outfiles/',
FORMAT (TYPE = 'format_type', FIELD_DELIMITER = ','),
PATTERN = 'regex'
);
'outfiles/': specifies the path where the external files are stored. Subdirectories can be recursively scanned.FORMAT: specifies the format and parsing options for the external files.PATTERN: specifies a regular expression pattern to filter files in theLOCATIONdirectory. If not specified, all files are read by default.
Query examples in different formats
CSV files
General syntax
The URL external table supports two equivalent syntax forms for reading CSV files, with TYPE = 'CSV' specified:
Location URL form (concise)
SELECT * FROM 'outfiles/'
(
FORMAT (
TYPE = 'CSV',
FIELD_DELIMITER = ','
),
PATTERN = 'data$'
);
Table function form (flexible, recommended)
SELECT * FROM FILES(
LOCATION = 'outfiles/',
FORMAT (
TYPE = 'CSV',
FIELD_DELIMITER = ','
),
PATTERN = 'data$'
);
Note
outfiles/specifies the path where the external table files are stored, supporting recursive scanning of subdirectories.
FORMATspecifies the external file format as CSV and related parsing options.
PATTERNspecifies the regular expression pattern for filtering files in theLOCATIONdirectory; if not specified, all files are read.
- Important: When using the URL external table to read CSV files, all column data types default to VARCHAR (which can be converted to other types using CAST later).
The LOCATION parameter can be specified as a string path (e.g., 'oss://bucket/path/', 'outfiles/') or the name of an existing Location object (without quotes).
Example 1: Create a persistent external table and reference a Location object
This example is suitable for scenarios where the external table needs to be used long-term. The 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/';
-- Referenced 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 in a temporary query using FILES()**
```sql
-- 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
);
FORMAT (TYPE = 'PARQUET'),
PATTERN = '.*\.parquet$' -- Match all .parquet files );
```sql
-- 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$'
);
SELECT * FROM FILES(
LOCATION = '/data/',
FORMAT (TYPE = 'CSV', FIELD_DELIMITER = ',', SKIP_BLANK_LINES = TRUE), PATTERN = '^datafiles.*\.csv$'
);
**Support for header parsing**
When accessing a CSV file using the FILES() function, if the table structure (column names and count) is not explicitly defined, the system automatically infers the table structure:
- Column count: Sample the first line of the CSV file in the specified directory.
- Column names: Default to `'c1'`, `'c2'`, etc.
Many CSV files have the first line as the header (column names), with the subsequent lines containing the actual data. OceanBase Database provides the `PARSE_HEADER` configuration parameter to control whether the first line is parsed as the header.
- If the first line of the CSV file contains column names: Set `PARSE_HEADER = TRUE`.
- If the first line of the CSV file contains actual data: Keep `PARSE_HEADER = FALSE` (default).
```sql
-- Basic query. The first row of the CSV file contains data (no header). Use the default column names 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.
)
);
);
**Limitations**: `PARSE_HEADER` and `SKIP_HEADER` are mutually exclusive and cannot be used together.
```sql
-- Location URL Format
SELECT * FROM 'outfiles/'
(
FORMAT (TYPE = 'PARQUET'),
PATTERN = 'data$'
);
-- Table Function
SELECT * FROM FILES(
LOCATION = 'outfiles/',
FORMAT (TYPE = 'PARQUET'),
PATTERN = 'data$'
);
FORMAT (TYPE = 'PARQUET'), PATTERN = 'data$' ); ```
-- 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$'
);
SELECT * FROM FILES( LOCATION = '/data/', FORMAT = (TYPE = 'PARQUET'), PATTERN = 'datafiles$' );
```sql
-- URL Location Format
SELECT * FROM 'outfiles/'
(
FORMAT (TYPE = 'ORC'),
PATTERN = 'data$'
);
-- Table Function
SELECT * FROM FILES(
LOCATION = 'outfiles/',
FORMAT (TYPE = 'ORC'),
PATTERN = 'data$'
);
-- Table function format
SELECT * FROM FILES( LOCATION = 'outfiles/', FORMAT (TYPE = 'ORC'), ```sql -- Example: Read an ORC file from HDFS SELECT * FROM 'hdfs://mycluster/data/orc/' ( FORMAT (TYPE = 'ORC'), PATTERN = 'clicks_202504.*' );
```sql
-- Example: Read ORC files from HDFS
SELECT * FROM 'hdfs://mycluster/data/orc/'
(
FORMAT (TYPE = 'ORC'),
PATTERN = 'clicks_202504.*'
);
```
- [Data type mapping in MySQL-compatible mode](../../700.reference/300.database-object-management/100.manage-object-of-mysql-mode/200.manage-tables-of-mysql-mode/1000.manage-external-tables-of-mysql-mode/400.data-type-mapping-of-mysql-mode.md)
- [Data type mapping in Oracle-compatible mode](../../700.reference/300.database-object-management/200.manage-object-of-oracle-mode/100.manage-tables-of-oracle-mode/1000.manage-external-tables-of-oracle-mode/400.data-type-mapping-of-oracle-mode.md)
### MaxCompute tables (directly connected to MaxCompute)
ODPS data sources are not in file format and do not support `FILES()`. Only `SOURCE()` is supported:
```sql
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)
Import external data into an internal table of OceanBase Database efficiently. Parallel import and direct write are supported.
### Syntax
```sql
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:
```sql
FILES (
LOCATION = '<string>',
{
FORMAT = (
TYPE = 'CSV',
LINE_DELIMITER = '<string>' | <expr>,
FIELD_DELIMITER = '<string>' | <expr>,
ESCAPE = '<character>' | <expr>,
FIELD_OPTIONALLY_ENCLOSED_BY = '
NULL_IF = ('
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 = '
ACCESSKEY = '
ENDPOINT = '
TUNNEL_ENDPOINT = 'LOCATION specifies the file path, which supports recursive scanning of subdirectories. * For a local path, the format is
[file:// | sfile://] local_file_path. The path must be a subdirectory of the
secure_file_priv directory. *
file://: This protocol header indicates that when files with the same logical path are encountered on multiple OBServer nodes, they are treated as different files and processed separately. Each node independently reads the file from its local path.
sfile://: This protocol header indicates that when files with the same logical path are encountered on multiple OBServer nodes, the system performs deduplication based on the logical path to ensure only one copy of data is read (supporting multi-server load balancing).
Notice
- When using
sfile://, the system performs consistency verification of file sizes. If files under the same logical path have inconsistent sizes across different OBServer nodes, the system returns the errorOB_INVALID_EXTERNAL_FILEto prevent incorrect results caused by data inconsistency. - For version V4.4.2, support for the
sfile://protocol header was added to the local Location path format in theLOAD DATAsyntax starting from V4.4.2 BP2.
Note
Sensitive credentials, such as AccessKey, are encrypted and stored in system tables by OceanBase Database. You can omit them in the SQL statement by reusing the preconfigured LOCATION object.
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);
INTO TABLE csv_ex_t1 (col1, col2);
```
#### Import Parquet files
```sql
LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM FILES(
LOCATION = 'data/parquet',
FORMAT = (TYPE = 'PARQUET'),
PATTERN = 'data1.parquet'
)
INTO TABLE parquet_ex_t1;
Import ORC files
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;