通过外表 INSERT INTO SELECT 导入

通常，数据库中的表数据存放在数据库的存储空间中，而外表的数据存储在外部存储服务中。OceanBase 数据库引入了外表（External Table）的功能，用户可以直接查询存储在外部系统的数据，也可以通过外表将外部数据快速导入 OceanBase 数据库中。通过外部表功能，OceanBase 可以连接并访问如 HDFS、OSS 、S3 等文件系统中的数据，支持包括 CSV、ORC、Parquet 等多种格式的数据文件。

若数据已存放在本地路径、对象存储或 HDFS 等外部位置，并希望通过单条 SQL 导入，可使用 FILES 表函数访问外部文件；相关用法参见 INSERT INTO SELECT FROM FILES 与 LOAD DATA FROM FILES。

注意事项

• 当外部数据文件目录存在分层明确的目录结构时，如按时间、省市等维度分层，优先考虑指定为外部分区表，便于在外表查询时进行分区裁剪提速。外部分区表的创建语法见 MySQL 模式下创建外表分区 和 Oracle 模式下创建外表分区。

• 创建外表前，需确认外部数据源和 OBServer 间网络是连通的。

适用场景

外表是在分析型业务中被较常使用的一种数据库特性，一些典型的使用场景为：

• 日志分析：企业每天会产生大量的日志信息，包括访问日志、错误日志等。这些日志往往首先被保存在对象存储上。通过创建指向这些日志文件的外表，可以快速地对这些日志进行复杂的查询操作，比如统计特定时间段内的访问量、查找错误发生频率最高的模块等。
• 历史数据分析：对于一些需要长期保存但不经常访问的历史数据，可以选择将其存放在成本更低的对象存储或 HDFS 中，通过建立外表的方式，在必要时直接查询这部分数据，既节省了存储空间也保证了数据访问效率。
• 多源数据集成：现代企业的业务流程往往涉及多个不同的信息系统，每个系统都可能产生各自格式的数据。利用外表，可以从不同来源加载各种格式的数据文件（CSV、Parquet、ORC等），然后在一个统一的平台上执行跨系统的联合查询，帮助企业获得更全面的数据洞察力。
• 数据湖分析：随着数据湖架构日益流行，越来越多的企业开始构建自己的数据湖来集中管理和分析来自各个渠道的原始或半结构化数据。在这种情况下，可以通过创建指向数据湖中特定位置的外表，直接参与到数据湖的数据探索活动中去，从而加速决策过程。
• 湖仓加速：数据湖中的离线数仓可以提供非实时的查询分析，但部分场景下可能无法满足业务的实时性要求。通过外表将数据快速导入到内部表，可以提供时效性更好、性能更好的查询分析能力。

示例

以下示例模拟了通过外表对本机日志文件进行读取分析的场景。

1. 本地文件下存在一个文件夹，其中的文件是 CSV 格式，这里可以用分区外表管理组织这些文件。文件组织如下。

   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.csv
   

   这里以 server_log1.csv 文件为例，文件的内容如下：

   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 success
   

2. 设置导入的文件路径。

   设置系统变量 secure_file_priv，配置导入或导出文件时可以访问的路径。更多设置操作，参见 secure_file_priv。

   注意

   由于安全原因，设置系统变量 secure_file_priv 时，只能通过本地 Unix Socket 连接数据库执行修改该全局变量的 SQL 语句。

3. 连接数据库后，创建自动分区外表来访问该目录文件，SQL 命令如下。

   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)
   ;
   

   METADATA$FILEURL 列中记录的是路径和文件名信息，这里是本地文件会返回 ip:port%2023-06-01/server_log2.csv。

4. 查询数据。

   • 查询外表数据。

     指定分区键的值或者范围进行查询，此时会进行分区裁剪，外表只会读取该分区下的文件。

     obclient> select * from ex_t1 where date_key = '2023-06-01';
     

   • 查询外部表的文件列表。

     外表创建后，也可以通过 ALL_OB_EXTERNAL_TABLE_FILES 视图来查看文件列表。

     SELECT * FROM oceanbase.ALL_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME='ex_t1';
     

5. 通过外表导入数据至内部表。

   可以通过修改配置项 default_load_mode 确定导入数据的行为。更多信息，参见 default_load_mode。

   1. 创建内部表。

      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')
       );
      

   2. 将外部数据导入内部表。

      obclient> insert into t1 select * from ex_t1;
      

• 更多关于 URL 外部表的信息，请参见 URL 外部表。

与数据湖章节的衔接

文件外表、Catalog 与 ODPS 等能力与 数据湖 文档互为补充，您可按场景查阅下列文档：

• 数据湖概述
• 文件外表
• ODPS 外表

相关文档

• 关于外表与外部文件（MySQL 模式）

• 创建和查询外表（MySQL 模式）

• 关于外表与外部文件（Oracle 模式）

• 创建和查询外表（Oracle 模式）

• MySQL 模式下创建外表分区

• Oracle 模式下创建外表分区

• MySQL 模式下管理外部文件

• Oracle 模式下管理外部文件