本文以 ORC 文件导入为例,介绍如何使用 ByteHouse S3 外表读取数据。
TOS 与 S3 协议兼容,导入数据到 ByteHouse 的操作方法一致,也可以参考本文来完成。
在开始操作前,请确保您已经具备以下必要的权限和信息:
# 当使用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 云数仓版控制台,或使用数据库客户端工具(如 DBeaver、DataGrip 或命令行工具)连接到 ByteHouse。
说明
ByteHouse 云数仓版控制台:
如果您还未选择目标数据库,请先执行以下 SQL 语句选择数据库:
USE dbname;
请参考以下 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;
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')
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 表中。如有进一步的问题,请联系您的系统管理员或数据库管理员获取支持。