This topic describes how to create and query a file-based external table in Oracle-compatible mode. For the complete syntax and descriptions of parameters such as LOCATION and FORMAT, refer to CREATE EXTERNAL TABLE.
If you only need to temporarily read from an external file, use the FILES table function.
Prerequisites
- You have deployed a cluster, created a tenant in Oracle-compatible mode, and connected to the database.
- The current user has the
CREATEprivilege. For information about how to check the current user's privileges, see Check user privileges. If you do not have theCREATE TABLEprivilege, contact the administrator to grant it. For operations related to granting privileges, see Grant privileges directly. - The local file path must be within the scope permitted by secure_file_priv.
- To access HDFS, you must complete the configuration for Deploy the OceanBase Database Java SDK environment.
Examples
Create a local CSV file external table
Assume a data.csv file is stored under /home/admin/oceanbase/ on the local machine, containing the following content.
1,"lin",98
2,"hei",90
3,"ali",95
On an OBServer node, the tenant administrator connects to the Oracle-compatible tenant of the cluster via a local Unix Socket.
A connection example is as follows:
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********For specific operations and descriptions on connecting to OceanBase Database via a local Unix Socket, see secure_file_priv.
Configure the path
/home/admin/oceanbase/accessible by the database.SET GLOBAL secure_file_priv = "/home/admin/oceanbase/";After the command executes successfully, you need to restart the session for the modification to take effect.
After reconnecting to the database, create the external table
ext_t3.CREATE EXTERNAL TABLE ext_t3(ID NUMBER(32), NAME VARCHAR2(30),SCORE NUMBER(32)) LOCATION = '/home/admin/oceanbase/' FORMAT = ( TYPE = 'CSV' FIELD_DELIMITER = ',' FIELD_OPTIONALLY_ENCLOSED_BY ='"' ) PATTERN = 'data.csv';
After the external table is successfully created, you can use the SHOW CREATE TABLE statement to view its definition, just like a regular table.
SHOW CREATE TABLE ext_t3;
The query result is as follows:
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXT_T3 | CREATE EXTERNAL TABLE "EXT_T3" (
"ID" NUMBER(32) GENERATED ALWAYS AS (METADATA$FILECOL1),
"NAME" VARCHAR2(30) GENERATED ALWAYS AS (METADATA$FILECOL2),
"SCORE" NUMBER(32) GENERATED ALWAYS AS (METADATA$FILECOL3)
)
LOCATION='file:///home/admin/oceanbase/'
PATTERN='data.csv'
FORMAT (
TYPE = 'CSV',
FIELD_DELIMITER = ',',
FIELD_OPTIONALLY_ENCLOSED_BY = '"',
ENCODING = 'utf8mb4'
)COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
You can also access it like a regular table. When querying an external table, the system reads the external file directly through the external table's driver layer, parses it according to the file format, converts it into internal data types of OceanBase Database, and then returns the data rows. The following is an example of querying the newly created external table ext_t3.
SELECT * FROM ext_t3;
The query result is as follows:
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | lin | 98 |
| 2 | hei | 90 |
| 3 | ali | 95 |
+----+------+-------+
3 rows in set
In addition, external tables can also be combined with regular tables for queries. Suppose there is a regular table named info in the current database, and its data is as follows:
+------+--------+------+
| name | sex | age |
+------+--------+------+
| lin | male | 8 |
| hei | male | 9 |
| li | female | 8 |
+------+--------+------+
3 rows in set
The following example shows how to perform a combined query using the external table ext_t3 and the regular table info.
SELECT info.* FROM info, ext_t3 WHERE info.name = ext_t3.name AND ext_t3.score > 90;
The query result is as follows:
+------+--------+------+
| name | sex | age |
+------+--------+------+
| lin | male | 8 |
| li | female | 8 |
+------+--------+------+
2 rows in set
For more information about queries, see Read data.
What to do next
- Refresh the file list:
ALTER EXTERNAL TABLE table_name REFRESH;, see Manage external files and ALTER EXTERNAL TABLE.