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 CDB_OB_EXTERNAL_TABLE_FILES view from the sys tenant for the information about all external tables in the cluster, and the ALL_OB_EXTERNAL_TABLE_FILES and DBA_OB_EXTERNAL_TABLE_FILES views from a user tenant for the information about the external tables in the current tenant. Here are some examples:

• The specified file directory contains only one external file.

  SELECT * FROM oceanbase.DBA_OB_EXTERNAL_TABLE_FILES;
  

  The query result is as follows:

  +------------+--------------+----------------+------------------------------+-----------+
  | TABLE_NAME | TABLE_SCHEMA | PARTITION_NAME | FILE_URL                     | FILE_SIZE |
  +------------+--------------+----------------+------------------------------+-----------+
  | ext_t3     | test         | P0             | xx.xx.xx.208:2882%data.csv   |        34 |
  | ext_t2     | test         | P0             | xx.xx.xx.208:2882%data.csv   |        34 |
  | ext_t1     | test         | P0             | xx.xx.xx.208:2882%data.csv   |        34 |
  +------------+--------------+----------------+------------------------------+-----------+
  3 rows in set
  

• The specified file directory contains multiple external files.

  SELECT * FROM oceanbase.DBA_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME= 'ext_t4';
  

  The query result is as follows:

  +------------+--------------+----------------+-------------------------------+-----------+
  | TABLE_NAME | TABLE_SCHEMA | PARTITION_NAME | FILE_URL                      | FILE_SIZE |
  +------------+--------------+----------------+-------------------------------+-----------+
  | ext_t4     | test         | P0             | xx.xx.xx.208:2882%data1.csv   |        33 |
  | ext_t4     | test         | P0             | xx.xx.xx.208:2882%data2.csv   |        34 |
  +------------+--------------+----------------+-------------------------------+-----------+
  2 rows in set
  

The parameters in the query result are described as follows:

• TABLE_NAME: the name of the external table.

• TABLE_SCHEMA: the name of the database where the external table resides.

• PARTITION_NAME: the partition name of the external table.

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

• FILE_SIZE: 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 is 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 oceanbase.DBA_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME= 'ext_t4';
   

   The query result is as follows:

   +------------+--------------+----------------+-------------------------------+-----------+
   | TABLE_NAME | TABLE_SCHEMA | PARTITION_NAME | FILE_URL                      | FILE_SIZE |
   +------------+--------------+----------------+-------------------------------+-----------+
   | ext_t4     | test         | P0             | xx.xx.xx.208:2882%data1.csv   |        33 |
   | ext_t4     | test         | P0             | xx.xx.xx.208:2882%data2.csv   |        34 |
   +------------+--------------+----------------+-------------------------------+-----------+
   2 rows in set
   

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

   ALTER EXTERNAL TABLE test.ext_t4 REFRESH;
   

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

   SELECT * FROM oceanbase.DBA_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME= 'ext_t4';
   

   The query result is as follows:

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

References

External tables

Create an external table