Create and query external tables

This topic describes how to create and query file-based external tables in MySQL-compatible mode. For complete syntax and descriptions of parameters such as LOCATION, FORMAT, and PROPERTIES, see CREATE EXTERNAL TABLE.

If you only need to temporarily read from an external file without creating a table object, use the FILES table function.

Prerequisites

• You have deployed a cluster, created a MySQL-compatible mode tenant, and connected to the database.
• You have created a database.
• The current user has the CREATE privilege. For more information, see View user privileges.
• Local files: The data has been placed in a path accessible to the OBServer node, and secure_file_priv is configured (modifying this variable typically requires a local Unix Socket connection).
• HDFS / ODPS: The OceanBase Database Java SDK environment has been deployed and configured.

Examples

The following examples are divided into two categories: local file-based external tables (accessing CSV files on OBServer via LOCATION + FORMAT) and ODPS external tables (accessing MaxCompute tables via PROPERTIES, requiring the Java SDK). After both types of external tables are successfully created, you can use SHOW CREATE TABLE to view their definitions and SELECT to query data.

Create a local CSV file-based external table

Assume a data.csv file exists under /home/admin/oceanbase/ on the local machine, containing the following content.

1,"lin",98
2,"hei",90
3,"ali",95

1. On the OBServer node, connect to the MySQL-compatible mode tenant of the cluster via a local Unix Socket connection.

   Example connection:

   obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********
   

   For specific operations and instructions on connecting to OceanBase Database via a local Unix Socket, see secure_file_priv.

2. 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 changes to take effect.

3. After reconnecting to the database, create the external table ext_t3.

   CREATE EXTERNAL TABLE ext_t3(id int, name char(10),score int)
   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` int(11) GENERATED ALWAYS AS (metadata$filecol1),
  `name` char(10) GENERATED ALWAYS AS (metadata$filecol2),
  `score` int(11) GENERATED ALWAYS AS (metadata$filecol3)
)
LOCATION='file:///home/admin/oceanbase/'
PATTERN='data.csv'
FORMAT (
  TYPE = 'CSV',
  FIELD_DELIMITER = ',',
  FIELD_OPTIONALLY_ENCLOSED_BY = '"',
  ENCODING = 'utf8mb4'
)DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

It can also be accessed like a regular table. When querying an external table, the system reads the external file directly through the driver layer of the external table, 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 used in combined queries with regular tables. 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 is an example of 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.

Create an ODPS Java SDK external table

This example maps the TPC-H table lineitem under the schema tpch_10g of the MaxCompute public dataset bigdata_public_dataset (for table structure and sample data description, see Practical Tutorial on Querying and Reading/Writing MaxCompute (ODPS) Data with OceanBase. Before creating the ODPS external table, you must have deployed the OceanBase Database Java SDK environment on all OBServer nodes and prepared the ACCESSID/ACCESSKEY with MaxCompute read permissions.

1. Connect to a MySQL-compatible tenant as a user with the CREATE privilege and switch to the target database (the example database name is test).

   USE test;
   

2. Create the ODPS external table ext_lineitem, ensuring its column definitions match those of the MaxCompute source table.

   CREATE EXTERNAL TABLE ext_lineitem (
       l_orderkey      BIGINT,
       l_partkey       BIGINT,
       l_suppkey       BIGINT,
       l_linenumber    BIGINT,
       l_quantity      DECIMAL(15,2),
       l_extendedprice DECIMAL(15,2),
       l_discount      DECIMAL(15,2),
       l_tax           DECIMAL(15,2),
       l_returnflag    CHAR(1),
       l_linestatus    CHAR(1),
       l_shipdate      DATE,
       l_commitdate    DATE,
       l_receiptdate   DATE,
       l_shipinstruct  CHAR(25),
       l_shipmode      CHAR(10),
       l_comment       VARCHAR(44)
   )
   PROPERTIES = (
       TYPE = 'ODPS',
       ACCESSID = '***********',
       ACCESSKEY = '***********',
       ENDPOINT = 'https://service.cn-hangzhou.maxcompute.aliyun.com/api',
       PROJECT_NAME = 'bigdata_public_dataset',
       SCHEMA_NAME = 'tpch_10g',
       TABLE_NAME = 'lineitem',
       QUOTA_NAME = '',
       COMPRESSION_CODE = 'zstd'
   );
   

   If using the Storage API (within the VPC and with MaxCompute Storage API permissions enabled), you can add API_MODE = 'storage_api' and SPLIT = 'byte' to PROPERTIES, and change ENDPOINT to an address within the same region's VPC (for example, https://service.cn-hangzhou-vpc.maxcompute.aliyun-inc.com/api)`).

3. Check the external table definition to confirm that PROPERTIES and column mapping have taken effect.

   SHOW CREATE TABLE ext_lineitem;
   

4. Query the ODPS external table. The system accesses MaxCompute via the Java SDK and returns the results according to the external table's column definitions.

   SELECT * FROM ext_lineitem LIMIT 5;
   

   The following example shows the query result (the first 5 rows are consistent with those of the MaxCompute source table):

   +------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
   | l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice  | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct     | l_shipmode | l_comment                        |
   +------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
   |          1 |   1551894 |     76910 |            1 |      17.00 |         33078.94 |       0.04 |  0.02 | N            | O            | 1996-03-13 | 1996-02-12 | 1996-03-22  | DELIVER IN PERSON  | TRUCK      | egular courts above the          |
   |          1 |    673091 |     73092 |            2 |      36.00 |         38306.16 |       0.09 |  0.06 | N            | O            | 1996-04-12 | 1996-02-28 | 1996-04-20  | TAKE BACK RETURN   | MAIL       | ly final dependencies: slyly bold|
   |          1 |    636998 |     36999 |            3 |       8.00 |         15479.68 |       0.10 |  0.02 | N            | O            | 1996-01-29 | 1996-03-05 | 1996-01-31  | TAKE BACK RETURN   | REG AIR    | riously. regular, express dep    |
   |          1 |     21315 |     46316 |            4 |      28.00 |         34616.68 |       0.09 |  0.06 | N            | O            | 1996-04-21 | 1996-03-30 | 1996-05-16  | NONE               | AIR        | lites. fluffily even de          |
   |          1 |    240267 |     15274 |            5 |      24.00 |         28974.00 |       0.10 |  0.04 | N            | O            | 1996-03-30 | 1996-03-14 | 1996-04-01  | NONE               | FOB        |  pending foxes. slyly re         |
   +------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
   5 rows in set
   

5. Combine an ODPS external table with a local regular table for querying (using the same JOIN method as ext_t3 and info in the previous example). Assume a regular table named orders_summary already exists in the database, recording the order keys that require key analysis. Example:

   SELECT o.order_id, l.l_quantity, l.l_extendedprice
   FROM orders_summary o
   JOIN ext_lineitem l ON o.order_id = l.l_orderkey
   WHERE l.l_shipdate >= '1996-03-01'
   LIMIT 10;
   

What to do next

• After adding an external file: Execute ALTER EXTERNAL TABLE table_name REFRESH;, or refer to Manage external files.
• Manage partitions by directory: Refer to Create an external table partition.
• Column types: Refer to Data type mappings.
• Drop an external table: Use DROP TABLE, which is the same as for a regular table. Refer to Drop a table.

References

• About external tables and external files
• CREATE EXTERNAL TABLE
• FILES table function
• Manage external files