Manage external files

When a external table is created, the system saves the list of files that match the PATTERN in the path specified by LOCATION to a system table in OceanBase Database. During a scan of the external table, external files are accessed based on this list. You can view and update the files accessible to an external table.

View files of an external table

After an external table is created, the sys tenant can view information about all external tables in the cluster through the CDB_OB_EXTERNAL_TABLE_FILES view. User tenants can view external table information through the ALL_OB_EXTERNAL_TABLE_FILES and DBA_OB_EXTERNAL_TABLE_FILES views. Example statements are as follows:

• Scenario where there is only one file in the external directory

  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
  

• Scenario where there are multiple files in the external directory

  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
  

Where:

• TABLE_NAME: The name of the external table.

• TABLE_SCHEMA: The name of the database where the external table is located.

• PARTITION_NAME: The name of the partition of the external table.

• FILE_URL: The URL of the file accessed by the external table.

• FILE_SIZE: The size of the file accessed by the external table.

Update files of an external table

After an external table is created, if other files are added to the external directory (these files are in the path specified by LOCATION and match the PATTERN), you need to perform an operation to update the external table files so that the new files can be accessed through the external table.

The SQL statement for updating external table files is as follows:

ALTER EXTERNAL TABLE table_name REFRESH;

The following is a simple example to illustrate. Suppose there is an external table named ext_t4, whose external files include data1.csv and data2.csv. Then, a new file named data3.csv is added to the same directory.

1. Before updating the external table files, query all files in 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 is located and update the external table file.

   ALTER EXTERNAL TABLE test.ext_t4 REFRESH;
   

3. Query all files in this external table again.

   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 and external files
• Create and query external tables
• CREATE EXTERNAL TABLE
• Update the external file list Expand all 1 rows