Description
The LOAD_FILE function reads external storage files locally into the database based on an external table interface. It supports reading files from local disks, HDFS, COS, and S3 interfaces.
Before use, you must create a Location object by calling CREATE LOCATION, and specify the location name and file name when making the call.
Syntax
LOAD_FILE(location_name, file_name)
Parameter description:
Parameter |
Description |
Type |
Nullable |
|---|---|---|---|
| location_name | The name of the external storage file. | VARCHAR(128) | Yes |
| file_name | The filename of the external storage file. | VARCHAR(4096) | No |
If either the location_name or file_name parameter is NULL, the output is NULL.
Return value:
- Data of the BLOB type, which stores the content of external files.
Considerations
Expression types and visibility
- When called in contexts such as
SELECTlist expressions, it has the same semantics asLOAD_FILE(location_name, file_name)described in this document. - Cannot be used for generated columns.
Errors and edge cases
Scenario |
Behavior |
|---|---|
location_name or file_name Empty string |
Error OB_INVALID_ARGUMENT. |
| Location does not exist. | Error OB_LOCATION_OBJ_NOT_EXIST. |
| The current session has no read permission on the location. | An error occurred (permission verification failed). |
| File does not exist. | Error OB_OBJECT_NAME_NOT_EXIST. |
| File size is 0 | Error OB_INVALID_DATA(Considered an invalid file). |
File size exceeds document_ai_file_max_size |
Error OB_NOT_SUPPORTED. |
Parameter and scenario constraints
LOAD_FILE must be constrained by parameters and scenarios to ensure security and stability.
When using the LOAD_FILE expression to read an external file, the following two parameters jointly constrain the file size to ensure it remains manageable. The actual maximum allowed file size is the smaller of the two values: Effective Maximum File Size = min(max_allowed_packet, document_ai_file_max_size).
The specific parameter information is as follows:
Parameter |
Scope |
Default Value |
Value range |
unit |
Description |
|---|---|---|---|---|---|
| max_allowed_packet | The global network packet size limit. | 16,777,216(16 MB) | [1048576, 1073741824](1 MB ~ 1 GB) | Byte | Controls the maximum size of all interactive data packets between the database and external entities, including clients and other OceanBase AI Database nodes. Any data transmitted in a single operation (including the content read by LOAD_FILE) must not exceed this value. |
| document_ai_file_max_size | The maximum size of a file dedicated to LOAD_FILE. |
100 MB | [0, 500MB] | Byte | Used to limit the maximum size of a file that the LOAD_FILE function can process at one time. Setting it to 0 means that reading files by using LOAD_FILE is prohibited. |
Examples
Read local files (≤ 16 MB)
-- Configure the location path CREATE OR REPLACE LOCATION local_data URL = 'file:///home/'; -- Read local file SELECT load_file('local_data', 'test_16MB.pdf');Read a local file (50 MB)
-- Configure max_allowed_packet to 100 MB. SET GLOBAL max_allowed_packet=104,857,600; -- Read local file select load_file('load_file', 'test_50MB.pdf');Read HDFS files
CREATE LOCATION hdfs_user URL = 'hdfs://namenode:8020/' CREDENTIAL ( USER = 'hdfs_user_name' ); -- Read HDFS Files SELECT load_file('hdfs_user', 'test_hdfs.pdf');
References
- Create and manage location objects. For more information, see CREATE LOCATION, ALTER LOCATION, and DROP LOCATION.
- document_ai_file_max_size.
- For more information about how to use it with the AI function service, see Overview of AI Function Service.