本文以 ORC 文件导入为例，介绍如何使用 ByteHouse S3 外表读取数据。
TOS 与 S3 协议兼容，导入数据到 ByteHouse 的操作方法一致，也可以参考本文来完成。

前提条件

在开始操作前，请确保您已经具备以下必要的权限和信息：

• ByteHouse 云数仓服务的访问权限：
  • 建议具备 AccountAdmin 的角色或者 SystemAdmin 的角色；
  • 对新用户授予 ByteHouseFullAccess 的 IAM 策略，请参考权限授予。
• TOS 桶的访问权限：
  • 获取 Access Key 与 Secret Key：API 访问密钥(Access Key)；
  • 权限问题排查：对象存储 Access Denied 排查建议。
• 在 ByteHouse 中创建好目标库和目标表。

注意事项

• 安全性：确保 Access Key 与 Secret Key 的存储安全，不要将其暴露给未经授权的人员。
• 性能：在执行大数据量导入时，请注意数据库和网络的负载情况，可以分批次导入以减小负载。

基本用法

建表

# 当使用S3://bucket时，将使用config配置的endpoint值
# 若不指定[access_key_id, access_key_secret]则默认使用config配置的值
# 若指定了[access_key_id, access_key_secret]，后续可通过select * from setting key=value修改
# 模糊匹配不支持bucket
CREATE TABLE s3_engine_table (name String, value UInt32) ENGINE=CnchS3('http://....' or 'https://' or 's3://', '[format]'，'[compression]'，'[accesss_id, access_key]') setting key=value
CREATE TABLE s3_engine_table_glob_uri (name String, value UInt32) ENGINE=CnchS3('http://....' or 'https://' or 's3://', '[format]'，'[compression]', '[accesss_id, access_key]') setting key=value

查询

# 通过S3外表查询
select * from s3_engine_table
# 通过CnchS3函数查询
select * from CnchS3('http://....' or 'https://' or 's3://', '[format]'，'[compression]'，'[accesss_id, access_key]') setting key=value

插入

# 通过S3外表插入数据
insert into s3_engine_table values ('one', 1), ('two', 2), ('three', 3)
# 通过CnchS3函数插入数据
insert into function CnchS3('http://....' or 'https://' or 's3://', 'city String, name String', '[format]', '[compression]'，'[accesss_id, access_key]') values ('one', '1'), ('two', '2'), ('three', '3')

操作步骤

连接到 ByteHouse 云数仓版

登录 ByteHouse 云数仓版控制台，或使用数据库客户端工具（如 DBeaver、DataGrip 或命令行工具）连接到 ByteHouse。

ByteHouse 云数仓版控制台：

选择目标数据库

如果您还未选择目标数据库，请先执行以下 SQL 语句选择数据库：

USE dbname;

导入数据

方法一：通过 S3 表函数读取数据

请参考以下 SQL 语句，将数据从 S3 / TOS 导入到 ByteHouse 表中：

select col0, col1... from CnchS3('https://bucket.endpoint/file_path/file', structure, 'ORC')
where _path like '%{path}%'
settings s3_access_key_id = 'xxx',  
         s3_access_key_secret = 'xxxx',
         input_format_orc_allow_missing_columns = 1,
         receive_timeout = 10800000, 
         max_execution_time = 10800000, 
         exchange_timeout = 10800000, 
         send_timeout = 10800000;

方法二：创建 S3 外表后读取数据

1. 通过执行以下 SQL 创建 S3 外表：

CREATE TABLE s3_source_table (structure) 
ENGINE=CnchS3('https://bucket.endpoint/file_path/file', 'ORC') 

如需导入多个文件的数据源，请使用通配符替换对应的"file_path/file"路径。例如想导入"/source/"目录下的所有文件，可以写成：

CREATE TABLE s3_source_table (structure)  
ENGINE=CnchS3('https://bucket.endpoint/source/*', 'ORC') 

2. 请参考如下 SQL 直接从已经建好的表导入数据到 ByteHouse 表中：

insert into db.table select col0, col1... from s3_source_table 
where _path like '%{path}%'
settings s3_access_key_id = 'xxx',  
         s3_access_key_secret = 'xxxx',
         input_format_orc_allow_missing_columns = 1,
         receive_timeout = 10800000, 
         max_execution_time = 10800000, 
         exchange_timeout = 10800000, 
         send_timeout = 10800000;

参数说明

• 参数 input_format_orc_allow_missing_columns 意味着 schema 不一致时，当 ORC 文件中缺少需要的列，会插入默认值。
• receive_timeout, max_execution_time, exchange_timeout, send_timeout 单位均为秒，用于防止 SQL 执行超时，可根据实际情况调整。
• where _path like '%{path}%'用于剪枝过滤不需要读取的文件，例如某个路径下包含很多天的数据：

/source/20240601/xxx
/source/20240602/xxx
....

例如在查询的时写上：where _path like '%20240602%'，则引擎仅会扫描20240602路径下的文件。

数据验证

执行导入操作后，可以通过查询目标表来验证数据是否正确导入。例如：

SELECT * FROM db.table LIMIT 10;

通过以上步骤，您应能顺利地将数据从 S3/TOS 桶导入到 ByteHouse 表中。如有进一步的问题，请联系您的系统管理员或数据库管理员获取支持。