Manage external files

When you create an external table, the system saves the list of files in the directory specified by LOCATION matching PATTERN in a system table of OceanBase Database. The system will access external files based on this list during a scan on the external table. You can view and update the list of external files accessible to the external table.

View the file list of an external table

After you create an external table, you can query the ALL_OB_EXTERNAL_TABLE_FILES and DBA_OB_EXTERNAL_TABLE_FILES views for the information about the external table. Here are some examples:

• The specified file directory contains only one external file.

  SELECT * FROM SYS.DBA_OB_EXTERNAL_TABLE_FILES;
  

  The query result is as follows:

  +------------+-------+----------------+------------------------------+-----------+
  | TABLE_NAME | OWNER | PARTITION_NAME | FILE_URL                     | FILE_SIZE |
  +------------+-------+----------------+------------------------------+-----------+
  | EXT_T3     | SYS   | P0             | xx.xx.xx.208:2882%data.csv   |        29 |
  +------------+-------+----------------+------------------------------+-----------+
  1 row in set
  

• The specified file directory contains multiple external files.

  SELECT * FROM SYS.DBA_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME= 'EXT_T4';
  

  The query result is as follows:

  +------------+-------+----------------+-------------------------------+-----------+
  | TABLE_NAME | OWNER | PARTITION_NAME | FILE_URL                      | FILE_SIZE |
  +------------+-------+----------------+-------------------------------+-----------+
  | EXT_T4     | SYS   | P0             | xx.xx.xx.208:2882%data2.csv   |        34 |
  | EXT_T4     | SYS   | P0             | xx.xx.xx.208:2882%data1.csv   |        33 |
  +------------+-------+----------------+-------------------------------+-----------+
  2 rows in set
  

where:

• TABLE_NAME specifies the name of the external table.

• OWNER specifies the owner user of the external table.

• PARTITION_NAME specifies the partition name of the external table.

• FILE_URL specifies the URL of the file accessible to the external table.

• FILE_SIZE specifies The size of the file accessible to the external table.

Update the file list of an external table

After an external table is created, if a file is added to the directory specified by LOCATION and the file matches the regular expression specified by PATTERN, you must add the file to the file list of the external table so that you can access this file from the external table.

The SQL syntax for updating the file list of an external table is as follows:

ALTER EXTERNAL TABLE table_name refresh;

Here provides an example. Assume that the file list of the external table ext_t4 contains the data1.csv and data2.csv external files, and then the data3.csv file is added to the directory.

1. Query the file list of the external table.

   SELECT * FROM SYS.DBA_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME= 'EXT_T4';
   

   The query result is as follows:

   +------------+-------+----------------+-------------------------------+-----------+
   | TABLE_NAME | OWNER | PARTITION_NAME | FILE_URL                      | FILE_SIZE |
   +------------+-------+----------------+-------------------------------+-----------+
   | EXT_T4     | SYS   | P0             | xx.xx.xx.208:2882%data2.csv   |        34 |
   | EXT_T4     | SYS   | P0             | xx.xx.xx.208:2882%data1.csv   |        33 |
   +------------+-------+----------------+-------------------------------+-----------+
   2 rows in set
   

2. Access the database where the external table resides and update its file list.

   ALTER EXTERNAL TABLE EXT_T4 REFRESH;
   

3. Query the updated file list of the external table.

   SELECT * FROM SYS.DBA_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME= 'EXT_T4';
   

   The query result is as follows:

   +------------+-------+----------------+-------------------------------+-----------+
   | TABLE_NAME | OWNER | PARTITION_NAME | FILE_URL                      | FILE_SIZE |
   +------------+-------+----------------+-------------------------------+-----------+
   | EXT_T4     | SYS   | P0             | xx.xx.xx.208:2882%data3.csv   |        33 |
   | EXT_T4     | SYS   | P0             | xx.xx.xx.208:2882%data2.csv   |        34 |
   | EXT_T4     | SYS   | P0             | xx.xx.xx.208:2882%data1.csv   |        33 |
   +------------+-------+----------------+-------------------------------+-----------+
   3 rows in set
   

References

External table

Create an external table