---
title: 外表支持读取 orc、parquet 格式文件的使用说明-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于外表支持读取 orc、parquet 格式文件的使用说明相关的常见问题和使用技巧，帮助您快速解决外表支持读取 orc、parquet 格式文件的使用说明的难题。
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

# 外表支持读取 orc、parquet 格式文件的使用说明

更新时间：2026-05-21 01:56

适用版本： V4.3.x 内容类型：TechNote  

外表是数据库管理系统中的一项关键功能，通常数据库中的表存放于数据库的存储空间中，而外表的数据存储于外部存储介质中。

创建外表时需要定义数据的文件路径和文件格式，之后用户可以通过外表从外部存储服务中读取文件的数据。外表是只读的，可以在查询语句使用，但是不能执行 DML 操作。外表不支持定义约束和创建索引。

从 OceanBase 数据库 V4.3.5 版本开始，外表在原有的支持 CSV 格式文件的基础上，还支持读取 orc 和 parquet 格式文件。

## 详细说明

### 测试版本

OceanBase 数据库 V4.3.5 LTS

### 测试表结构

t1 表为非分区表，表中的行数为 320000，测试数据构造如下。

```shell
[root@observer ~]# obclient -h127.0.0.1 -uroot@mysql#kunpeng_clu -P2883 -pxxx -A -c test
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 341
Server version: OceanBase 4.3.5.0 (r100010012025011314-c5d637e8a6116ad5d62d23f3a337459fcc144c9f) (Built Jan 13 2025 14:48:17)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient(root@mysql)[test]> create table test (
name varchar(2000),
sex char(10),
birth_date datetime
);
Query OK, 0 rows affected (0.189 sec)

obclient(root@mysql)[test]> delimiter $$
obclient(root@mysql)[test]> create procedure insert_test(num int)
begin
  declare fname char(1);
  declare name1 char(1);
  declare name2 char(1);
  declare v_name varchar(2000);
  declare v_sex int;
  declare v_birth date;
  declare i int default 1;
  while i <= num do
    set fname = substring('赵钱孙李周吴郑王孙',floor(1+8*rand()),1);  -- 姓
    set name1 = substring('一二三四五六七八九十甲乙丙丁',floor(1+14*rand()),1); -- 抽一个字
    set v_name = rpad(fname,350,name1);
    set v_sex = round(rand());
    set v_birth = date_add('2000-01-01', interval round(rand() * 31*12*22) day);
    insert into test(name,sex,birth_date) values(v_name,v_sex,v_birth);  -- 插入数据
    set i = i + 1;
    end while;
end
$$
obclient(root@mysql)[test]> delimiter ;
Query OK, 0 rows affected (0.143 sec)

obclient(root@mysql)[test]> call insert_test(10000);
Query OK, 1 row affected (8.884 sec)

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 10000 rows affected (0.170 sec)
Records: 10000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 20000 rows affected (0.344 sec)
Records: 20000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 40000 rows affected (0.686 sec)
Records: 40000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 80000 rows affected (1.495 sec)
Records: 80000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 160000 rows affected (2.735 sec)
Records: 160000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> commit;
Query OK, 0 rows affected (0.001 sec)

obclient(root@mysql)[test]> select count(*) from test;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.345 sec)

```

### Case 1）OceanBase 数据库 V4.3.5 版本中外表支持读取 orc 格式的文件

```shell
obclient(root@mysql)[test]> SELECT * FROM test
INTO outfile '/home/admin/test_dir/test'
FORMAT(
  TYPE = 'ORC'
) single=false;
Query OK, 320000 rows affected (0.912 sec)

obclient(root@mysql)[test]> Bye

[root@observer ~]# ls -ltr /home/admin/test_dir/
total 332612
-rw-r--r-- 1 admin admin 324237806 Feb  5 11:32 test_0.orc
-rw-r--r-- 1 admin admin  16352573 Feb  5 11:32 test_1.orc
[root@observer ~]#

obclient(root@mysql)[test]> CREATE EXTERNAL TABLE test_external (
    name varchar(2000),
    sex char(10),
    birth_date datetime
)
LOCATION = '/home/admin/test_dir/'
FORMAT = (
  TYPE = 'ORC'
)
PATTERN = 'test_[0-9]*.orc';
Query OK, 0 rows affected (0.138 sec)

obclient(root@mysql)[test]> select count(*) from test_external;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.015 sec)

```

### Case 2）OceanBase 数据库 V4.3.5 版本中外表也支持读取 parquet 格式的文件

```shell
obclient(root@mysql)[test]> SELECT * FROM test
INTO outfile '/home/admin/test_dir/test'
FORMAT(
  TYPE = 'PARQUET'
) single=false;
Query OK, 320000 rows affected (0.984 sec)

[root@observer ~]# ls -ltr /home/admin/test_dir/
total 992
-rw-r--r-- 1 admin admin 1013517 Feb  5 11:42 test_0.parquet
[root@observer ~]#

obclient(root@mysql)[test]> CREATE EXTERNAL TABLE test_external (
    name varchar(2000),
    sex char(10),
    birth_date datetime
)
LOCATION = '/home/admin/test_dir/'
FORMAT = (
  TYPE = 'PARQUET'
)
PATTERN = 'test_[0-9]*.parquet';
Query OK, 0 rows affected (0.134 sec)

obclient(root@mysql)[test]> select count(*) from test_external;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.011 sec)

```

### Case 3）即使 orc、parquet 文件内部压缩过的，也是支持的

```shell
obclient(root@mysql)[test]> SELECT * FROM test
INTO outfile '/home/admin/test_dir/test'
FORMAT(
  TYPE = 'ORC'
  COMPRESSION = 'ZSTD'
  COMPRESSION_BLOCK_SIZE = '64KB'
) single=false;
Query OK, 320000 rows affected (0.791 sec)

[root@observer ~]# ls -ltr /home/admin/test_dir/
total 3032
-rw-r--r-- 1 admin admin 3102074 Feb  5 11:57 test_0.orc
[root@observer ~]#

obclient(root@mysql)[test]> CREATE EXTERNAL TABLE test_external (
    name varchar(2000),
    sex char(10),
    birth_date datetime
)
LOCATION = '/home/admin/test_dir/'
FORMAT = (
  TYPE = 'ORC'
)
PATTERN = 'test_[0-9]*.orc';
Query OK, 0 rows affected (0.132 sec)

obclient(root@mysql)[test]> select count(*) from test_external;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.010 sec)

obclient(root@mysql)[test]> SELECT * FROM test
INTO outfile '/home/admin/test_dir/test'
FORMAT(
  TYPE = 'PARQUET'
  COMPRESSION = 'SNAPPY'
  COMPRESSION_BLOCK_SIZE = '64KB'
) single=false;
Query OK, 320000 rows affected (1.003 sec)

[root@observer ~]# ls -ltr /home/admin/test_dir/
total 432
-rw-r--r-- 1 admin admin 439411 Feb  5 11:46 test_0.parquet
[root@observer ~]#

obclient(root@mysql)[test]> CREATE EXTERNAL TABLE test_external (
    name varchar(2000),
    sex char(10),
    birth_date datetime
)
LOCATION = '/home/admin/test_dir/'
FORMAT = (
  TYPE = 'PARQUET'
)
PATTERN = 'test_[0-9]*.parquet';
Query OK, 0 rows affected (0.137 sec)

obclient(root@mysql)[test]> select count(*) from test_external;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.009 sec)

```

## 影响租户

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

## 适用版本

OceanBase 数据库 V4.3.5 及之后版本。

上一篇

[系统表 __all_virtual_table 中 table_type 等字段的含义](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207747)

下一篇

[创建的表 Leader 始终分布在同一个 observer](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000006142502) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线