本文介绍如何通过持久化外表和一次性查询的方式将 S3/TOS 外表中的数据导入 ByteHouse CDW。

ByteHouse 云数仓版 (CDW) 提供了三种从 S3/TOS 外表中导入数据的方式：

ByteHouse CDW 支持的文件类型及压缩格式说明如下：

• 文件类型：支持 CSV 、TSVWithNames、TSV、CSVWithNames、JSONEachRow、Parquet、ORC 等常用数据类型。
• 压缩格式：支持 none、gzip、deflate、br、xz、zstd、snappy 压缩格式。

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

• ByteHouse 云数仓服务的访问权限：
  • 建议具备 AccountAdmin 或 SystemAdmin 的角色。
  • 对新用户授予 ByteHouseFullAccess 的 IAM 策略，请参考权限授予。
• 您使用的对象存储桶的访问权限：
  • 获取 Access Key ID（AK） 与 Secret Access Key（SK）。如果您使用的是火山引擎对象存储服务（TOS），获取详情请参考 Access Key（密钥）管理。
  • 如果您使用 TOS 时出现 “Access Denied” 报错，请排查权限问题，详情请参考对象存储 Access Denied 排查建议。
• 在 ByteHouse 中创建好目标库和目标表，详情请参考数据库管理。

1. 安全性：确保 Access Key ID 与 Secret Access Key 的存储安全，不要将其暴露给未经授权的人员。
2. 性能：在执行大数据量导入时，请注意数据库和网络的负载情况，可以分批次导入以减小负载。
3. 费用：将数据从对象存储服务导入至 ByteHouse 时，如果您使用公网访问，将会产生相关费用。建议您在使用前详细了解您使用服务的计费规则。
4. S3 URL 链接正确性：使用 TOS 外表导入数据时需注意火山引擎 TOS 提供的 endpoint 与 S3 endpoint 有差异。如下图所示，从火山引擎 TOS 控制台复制的链接 https://tos-{REGION}.volces.com/xxx 无法直接使用，需要将域名更换为 S3 Endpoint 域名，修改为https://tos-s3-{REGION}.volces.com/xxx格式，详情参见火山引擎 TOS 支持的地域和访问域名。
   

本节介绍通过 S3 外表、CncnS3 函数、INSERT INFILE 导入数据使用的基本语法，便于您理解核心语句的功能和其中参数的使用方法。如需了解具体使用示例，可参考操作步骤章节。

通过创建 S3 外表导入数据

创建表

# 创建 ByteHouse 目标表
CREATE TABLE [db_name.]table_name (
    column1 type1,
    column2 type2,
    column3 type3,
    ...
) 
ENGINE=CnchMergeTree;

# 创建 ByteHouse 目标表对应的 S3 外表
CREATE TABLE [db_name.]table_name_s3 AS table_name
ENGINE=CnchS3(
  'http://....' or 'https://', 
  'FORMAT',
  'COMPRESSION', 
  'access_key_id', 
  'secret_access_key'
);

参数说明

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

CREATE TABLE db.table_name_s3 (
    id String,
    name String,
    city String
) ENGINE=CnchS3(
    'https://bucket.endpoint/source/*', 
    'FORMAT', 
    'COMPRESSION'
    'access_key_id', 
    'secret_access_key'
) 

插入数据

INSERT INTO [db_name.]table_name
SELECT column1, column2, ...
FROM table_name_s3
[WHERE _path like '%{path}%']
[SETTINGS parameter1 = value1, parameter2 = value2, ...]

参数说明

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

通过 CnchS3 函数导入数据

INSERT INTO [db_name.]table_name
SELECT column1, column2, ...
FROM CnchS3(
    'http://....' or 'https://', 
    'column1 type1, column2 type2, ...',
    'format',
    'compression',
    'access_key_id', 
    'secret_access_key'
)
[WHERE _path like '%{path}%']
[SETTINGS parameter1 = value1, parameter2 = value2, ...]

参数说明

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

通过 INSERT INFILE 子句导入数据

INSERT INTO [db_name.]table_name
FORMAT data_format INFILE 'object_storage_path' COMPRESSION 'compression_format' 
SETTINGS
s3_ak_id = 's3_ak',
s3_ak_secret = 's3_sk',
s3_region = 's3_region',
s3_endpoint= 's3_endpoint',
s3_use_virtual_hosted_style=1;

参数说明

步骤一：连接到 ByteHouse CDW

您可通过登录 ByteHouse 云数仓版控制台，使用 ByteHouse CLI 工具，或使用数据库客户端工具（如 DBeaver、DataGrip 或命令行工具）连接到 ByteHouse。操作详情可参考以下说明或文档：

• ByteHouse 控制台
  登录 ByteHouse 控制台，单击 SQL 工作表，在 SQL 编辑区域输入命令。
  
• ByteHouse CLI
  操作说明请参见ByteHouse CLI 快速入门。
• 数据库客户端工具
  您可参考 ByteHouse 生态工具文档，按需使用生态工具连接至 ByteHouse。
  或者您可在 ByteHouse 控制台上，单击顶部租户管理，在生态工具页签下，查看 ByteHouse 已支持的生态工具，单击已支持的类型，跳转查看操作说明文档。
  

步骤二：选择目标数据库

执行以下 SQL 语句，选择目标数据库。

USE demo_db;

步骤三：创建 ByteHouse 目标表

在 ByteHouse 中创建目标表，与源表保持相同的表结构，示例如下：

CREATE TABLE demo_db.demo_table (
    id String,
    name String,
    city String,
  ) ENGINE = CnchMergeTree
ORDER BY
  id

步骤四：导入数据

方法一：创建 S3 外表后写入数据

1. 通过执行以下 SQL 创建 ByteHouse 目标表对应的 S3 外表。使用时，请使用实际参数值替换命令中的占位符。

   CREATE TABLE demo_db.demo_table_s3 AS demo_db.demo_table
   ENGINE=CnchS3(
     'https://bucket.endpoint/file_path/file', 
     'CSV', -- 替换为您实际导入文件的格式
     'none',  -- 替换为您需要的压缩格式
     'access_key_id', 
     'secret_access_key'
   ) 
   

2. 请参考如下 SQL 直接从已经建好的表导入数据到 ByteHouse 表中。参数说明请参见通过创建 S3 外表导入数据。

   INSERT INTO demo_db.demo_table
   SELECT col0, col1, ... 
   FROM demo_db.demo_table_s3
   WHERE _path like '%{path}%'
   SETTINGS 
       receive_timeout = 10800000, 
       max_execution_time = 10800000, 
       exchange_timeout = 10800000, 
       send_timeout = 10800000;
   

方法二：通过 CnchS3 表函数写入数据

执行以下 SQL 语句，使用 S3 表函数将数据从对象存储导入到 ByteHouse 表中。使用时，请使用实际参数值替换命令中的占位符。参数说明请参见通过 CnchS3 函数导入数据。

INSERT INTO demo_db.demo_table
SELECT col0, col1... 
FROM CnchS3(
    'https://bucket.endpoint/file_path/test_outfile.csv', 
    'id String, name String, city String', 
    'FORMAT CSV',   -- 替换为您实际导入文件的格式
    'COMPRESSION none'   -- 替换为您需要的压缩格式
    '{your_access_key_id}', 
    '{your_secret_access_key}'
)
WHERE _path like '%{path}%'
SETTINGS 
    receive_timeout = 10800000, 
    max_execution_time = 10800000, 
    exchange_timeout = 10800000, 
    send_timeout = 10800000;

方法三：通过 INSERT INFILE 子句写入数据

执行以下 SQL 语句，将数据从对象存储桶导入到 ByteHouse 表中。使用时，请使用实际参数值替换命令中的占位符。参数说明请参见通过 INSERT INFILE 子句导入数据。

INSERT INTO demo_db.demo_table
FORMAT CSV INFILE 's3://s3_bucket/test_outfile.csv' COMPRESSION 'none'  -- 替换为您实际导入文件的格式和所需的压缩格式
SETTINGS
s3_ak_id = '{your_access_key_id}',
s3_ak_secret = '{your_secret_access_key}',
s3_region = '{your_s3_region}',
s3_endpoint= '{your_s3_endpoint}',
s3_use_virtual_hosted_style=1;

步骤五：数据验证

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

SELECT * FROM demo_db.demo_table LIMIT 10;

完成上述步骤，即可将数据从对象存储桶导入到 ByteHouse 表中。如有问题，可联系系统管理员或数据库管理员获取支持。

导入数据时，ByteHouse 支持对最后一层路径进行字符串正则匹配，从而允许批量指定文件。目前支持以下几种用法：

/* 解析一个字符串，该字符串用于生成分片（shards）和副本（replicas）。分隔符是 | 或 , 这两个字符中的一个，
 * 具体使用取决于生成的是分片还是副本。
 * 例如：
 * host1,host2,...      - 生成由 host1, host2, ... 组成的分片集合。
 * host1|host2|...      - 生成由 host1, host2, ... 组成的副本集合。
 * abc{8..10}def        - 生成分片集合：abc8def, abc9def, abc10def。
 * abc{08..10}def       - 生成分片集合：abc08def, abc09def, abc10def。
 * abc{x,yy,z}def       - 生成分片集合：abcxdef, abcyydef, abczdef。
 * abc{x|yy|z} def      - 生成副本集合：abcxdef, abcyydef, abczdef。
 * abc{1..9}de{f,g,h}   - 是笛卡尔积（直积），生成 27 个分片。
 * abc{1..9}de{0|1}     - 是笛卡尔积（直积），生成 9 个分片，每个分片包含 2 个副本。

 */

使用示例如下：

INSERT INTO numbers 
FORMAT CSV 
INFILE 's3://bucket/data_path/clickhouse_outfile_{1..100}.csv'

除了这种纯正则的解析，目前已经支持在 Local/S3 文件系统内 ListFile，支持 */ ? 等通配符匹配。

INSERT INTO numbers 
FORMAT CSV 
INFILE 's3://bucket/data_path/clickhouse_outfile_*.csv'