Typically, table data in a database is stored within the database's own storage space, while data in an external table is stored in an external storage service. OceanBase Database supports external tables, allowing you to directly query data stored in external systems or quickly import external data into OceanBase Database. With the external table feature, OceanBase can connect to and access data in file systems such as HDFS, OSS, and S3, supporting various data file formats including CSV, ORC, and Parquet.
If data is already stored in external locations such as local paths, object storage, or HDFS, and you want to import it with a single SQL statement, you can use the FILES table function to access the external files. For more information, see INSERT INTO SELECT FROM FILES.
Considerations
When an external data file directory has a well-structured hierarchy, such as by time or region, consider specifying it as an external partitioned table to enable partition pruning for faster queries. Syntax for creating an external partitioned table is described in Create an external table partition in MySQL-compatible mode and Create an external table partition in Oracle-compatible mode.
Before creating an external table, ensure the network between the external data source and the OBServer is connected.
Application scenarios
External tables are a commonly used feature in analytical business scenarios. Typical applications include:
- Log analysis: Enterprises generate large volumes of log data daily, including access logs and error logs. These logs are often initially stored in object storage. By creating external tables that reference these log files, you can perform complex queries on them quickly, such as counting visits within a specific period or identifying modules with the highest error rates.
- Historical data analysis: For historical data that needs long-term retention but is not frequently accessed, consider storing it in lower-cost object storage or HDFS. By establishing external tables, you can directly query this data when needed, saving storage space while ensuring efficient data access.
- Multi-source data integration: Modern enterprise workflows often involve multiple different information systems, each generating data in its own format. Using external tables, you can load data files of various formats (CSV, Parquet, ORC, etc.) from different sources, then execute cross-system joint queries on a unified platform, helping enterprises gain a more comprehensive understanding of their data.
- Data lake analytics: As the data lake architecture becomes increasingly popular, more enterprises are building their own data lakes to centrally manage and analyze raw or semi-structured data from various channels. In this case, by creating external tables that reference specific locations in the data lake, you can directly participate in data exploration activities within the data lake, thereby accelerating the decision-making process.
- Lakehouse acceleration: The offline data warehouse in a data lake provides non-real-time query analytics, but in some scenarios, it may not meet real-time business requirements. By quickly importing data into internal tables using external tables, you can provide query analytics with better timeliness and performance.
Examples
The following example simulates reading and analyzing local log files using external tables.
A folder containing CSV-format files exists in the local directory. You can use a partitioned external table to organize these files. The file organization is as follows.
external_table_mock_log ├── 2023-06-01 │ ├── server_log1.csv │ └── server_log2.csv ├── 2023-06-02 │ └── server_log1.csv ├── 2023-06-03 │ ├── server_log1.csv │ ├── server_log2.csv │ └── server_log3.csv └── 2023-07-01 └── server_log1.csvTaking the server_log1.csv file as an example, its content is as follows:
2023-06-01 14:42:37.568624, INTERNAL ERROR, -4007, Not supported 2023-06-01 14:42:38.861356, ITER END, -4008, traverse map failed 2023-06-01 14:42:39.931161, NEED WAIT, -4076, query and update last id fail 2023-06-01 14:42:39.931161, SUCCESS, 0, do flush cache successSet the import file path.
Configure the system variable
secure_file_privto specify the accessible paths for import or export files. For more information, see secure_file_priv.Note
For security reasons, when setting the system variable
secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable.After connecting to the database, create an auto-partitioned external table to access the directory files. The SQL command is as follows.
obclient> create external table ex_t1 ( time date, errstate varchar(30), errcode int, errcontent varchar(100), date_key date as (substr(substr(metadata$fileurl, instr(metadata$fileurl, '%') + 1), 1, 10)) ) location='/home/admin/external_table_mock_log' FORMAT ( type = 'csv', field_delimiter = ',', SKIP_BLANK_LINES = TRUE ) partition by (date_key) ;The
METADATA$FILEURLcolumn records the path and filename information. For local files, it returnsip:port%2023-06-01/server_log2.csv.Query the data.
Query data from an external table.
Specify the value or range of the partitioning key for the query. Partition pruning is performed in this case, and the external table only reads files from that partition.
obclient> select * from ex_t1 where date_key = '2023-06-01';Query the file list of an external table.
After an external table is created, you can also view its file list through the
ALL_OB_EXTERNAL_TABLE_FILESview.SELECT * FROM oceanbase.ALL_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME='ex_t1';
Import data from an external table to an internal table.
You can modify the
default_load_modeparameter to determine the data import behavior. For more information, see default_load_mode.Create an internal table.
obclient> create table t1 ( time date, errstate varchar(30), errcode int, errcontent varchar(100), date_key date ) partition by list columns(date_key) (partition p20230601 values in ('2023-06-01'), partition p20230602 values in ('2023-06-02'), partition p20230603 values in ('2023-06-03'), partition p20230701 values in ('2023-07-01') );Import external data into the internal table.
obclient> insert into t1 select * from ex_t1;
- For more information about URL external tables, see URL external tables.
Link to the data lake topic
File external tables, Catalog, and capabilities such as ODPS complement the Data Lake documentation. You can refer to the following documents based on your scenario: