LOAD_FILE

Purpose

The LOAD_FILE function reads files from external storage to the database through an external table interface. It supports reading files from local storage, HDFS, OSS, COS, and S3.

Before using this function, you must create a location object using CREATE LOCATION and specify the location name and file name when calling the function.

Syntax

LOAD_FILE(location_name, file_name)

Parameters:

If either location_name or file_name is NULL, the output will be NULL.

Return value:

• The content of the external storage file as BLOB data.

Limitations and considerations

The LOAD_FILE function is constrained by configuration items and scenarios to ensure security and stability.

When you use the LOAD_FILE expression to read an external file, the following two configuration items jointly constrain the size of the external file. The maximum size of the external file that can be read is the smaller of the two values, that is, effective maximum file size = min(max_allowed_packet, document_ai_file_max_size).

The following table describes the configuration items.

Examples

1. Read a local file (<= 16 MB)

   -- Configure the location path
   CREATE OR REPLACE LOCATION local_data
   URL = 'file:///home/';
   
   -- Read the local file
   SELECT load_file('local_data', 'test_16MB.pdf');
   

2. Read a local file (50 MB)

   -- Configure the max_allowed_packet parameter to 100 MB.
   SET GLOBAL max_allowed_packet=104,857,600;
   
   -- Read the local file
   select load_file('load_file', 'test_50MB.pdf');
   

3. Read an OSS file.

   CREATE LOCATION oss_t
   URL = 'oss://dir'
   CREDENTIAL = (
   ACCESSID = '123'
   ACCESSKEY = '456'
   HOST = '789'
   );
   -- Read the OSS file
   SELECT load_file('oss_t', 'test_oss.pdf');
   

4. Read an HDFS file.

   CREATE LOCATION hdfs_user
   URL = 'hdfs://namenode:8020/'
   CREDENTIAL (
   USER = 'hdfs_user_name'
   );
   -- Read the HDFS file
   SELECT load_file('hdfs_user', 'test_hdfs.pdf');
   

References

• For information about how to create and manage location objects, see CREATE LOCATION, ALTER LOCATION, and DROP LOCATION.
• document_ai_file_max_size.
• For information about how to use AI function services, see Overview of AI function services.