ByteHouse 研发的 CnchS3 引擎集成了对象存储，支持通过 ByteHouse 管理存储在 S3 兼容对象存储上的数据。本文将介绍如何在 ByteHouse 云数仓版中使用 S3 外表。

ByteHouse 借助 CnchS3 引擎实现与 S3 兼容对象存储的对接，无需将数据迁移至 ByteHouse 内部，即可对对象存储上的原始数据执行查询、写入等操作。ByteHouse 提供了两种使用方式：CnchS3 表引擎（持久化外表）和 CnchS3 表函数，您可根据业务场景灵活选择。
CnchS3 表引擎和 CnchS3 表函数两种方式对比如下：

费用说明 #

外部表的功能旨在提供除 ByteHouse 内部表以外的其他数据的处理能力。通过一条简单的 DDL 语句，即可在 ByteHouse 上创建一张外部表，建立 ByteHouse 表与外部数据源的关联。

• 外部表在 ByteHouse 侧只会产生计算费用，遵循 ByteHouse 计算资源计费规则。
• 使用外部表功能时，外部表的数据不会复制一份存在 ByteHouse 上并产生存储费用。存储费用请关注外部数据源存储侧计费规则。
• 使用 ByteHouse 外部表访问外部数据源时，外部数据源可能会产生计算、访问、数据传输等费用，具体以外部数据源计费方式为准，请关注相应产品的说明。

在开始操作前，请确保您已经具备您使用的对象存储桶的访问权限：

• 获取 Access Key ID（AK） 与 Secret Access Key（SK）。如果您使用的是火山引擎对象存储服务（TOS），获取详情请参考 Access Key（密钥）管理。
• 如果您使用 TOS 时出现 “Access Denied” 报错，请排查权限问题，详情请参考对象存储 Access Denied 排查建议。

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

创建表 #

语法

CREATE TABLE [db_name.]table_name (
    column1 type1, column2 type2, ...
) ENGINE=CnchS3(
  'http://....' or 'https://' or 's3://', 
  '[FORMAT]',
  '[COMPRESSION]', 
  'access_key_id', 
  'secret_access_key'
);

示例

• 创建单文件表

  CREATE TABLE
      s3_engine_table(name String, value UInt32) ENGINE = CnchS3(
          'https://bucket.endpoint/file_path/fileSample.csv',
          'CSV',
          'none',
          'access_key_id',
          'secret_access_key'
      );
  

• 使用通配符创建多文件表

  CREATE TABLE
      s3_engine_table_glob_uri(name String, value UInt32) ENGINE = CnchS3(
          'https://bucket.endpoint/file_path/fileSample*.csv',
          'CSV',
          'none',
          'access_key_id',
          'secret_access_key'
      );
  

写入数据 #

您可通过 INSERT INTO 语句向 S3 外表写入数据，用法与普通表的写入操作一致。
示例如下：

INSERT INTO s3_engine_table 
VALUES ('one', 1), ('two', 2), ('three', 3);

查询数据 #

您可通过 SELECT 语句查询 S3 外表中的数据，用法与普通表的查询操作一致。ByteHouse 也支持通过 _path和_file 列进行剪枝查询。示例如下：

• 查询数据

  SELECT * FROM s3_engine_table;
  

• 使用 _file 列进行剪枝查询

  SELECT * FROM s3_engine_table_glob_uri WHERE _file = 'fileSample.csv';
  

修改配置 #

默认情况下，外表的 resources_assign_type **** 值为 server_push，在该模式下，数据文件会被发往 Worker 进行查询。如果您想在 Server 本地查询，可以修改 resources_assign_type 为 server_local。

ALTER TABLE s3_engine_table MODIFY SETTING resources_assign_type='server_local'

变更 AK/SK #

您可通过 SELECT 语句更新 AK/SK。

SELECT * FROM s3_engine_table SETTINGS s3_access_key_id = 'your_new_ak', s3_access_key_secret = 'your_new_sk'

写入数据 #

语法

INSERT INTO FUNCTION CnchS3(
    'http://....' or 'https://', 
    'column1 type1, column2 type2, ...',
    'FORMAT',
    'COMPRESSION',
    'access_key_id', 
    'secret_access_key'
) VALUES (value1_1, value1_2, ...),    
    (value2_1, value2_2, ...),  
    (...);

示例

INSERT INTO FUNCTION
    CnchS3(
        'http://bucket_endpoint/bucket/normal_write.csv', 
        'name String, id String',
        'CSV',
        'none',
        'your_access_key_id', 
        'your_secret_access_key'
     ) VALUES ('one', '1'), 
     ('two', '2'), 
     ('three', '3');

分区级写入数据 #

分区级写入数据的语法与普通写入数据一致，但需在对象存储路径中包含 '{_partition_id}' 字段，其他参数请参考上文参数说明。
示例如下：

INSERT INTO FUNCTION
    CnchS3(
       'http://bucket_endpoint/bucket/{_partition_id}_partition_write.csv', 
       'name String, id String',
       'CSV',
       'none',
        'your_access_key_id', 
        'your_secret_access_key'
       )
PARTITION BY 
    name VALUES ('one', '1'),
    ('one', '2'),
    ('two', '1'),
    ('two', '2');

查询数据 #

使用 CnchS3 函数直接查询 S3 外表。
语法

SELECT *
FROM
    CnchS3(
        ''http://....' or 'https://',
        'column1 type1, column2 type2, ...',
        '[FORMAT]',
        '[COMPRESSION]',
        'access_key_id', 
        'secret_access_key'
    )
[WHERE where_clause];

示例

• 查询单份文件

  SELECT *
  FROM
      CnchS3(
          'http://bucket_endpoint/bucket/fileSample.csv',
          'city String, name String',
          'CSV',
          'none',
          'your_access_key_id', 
          'your_secret_access_key'
      );
  

• 使用通配符查询多份文件，并进行剪枝查询

  SELECT *
  FROM
      CnchS3(
          'http://bucket_endpoint/bucket/fileSample.csv',
          'city String, name String',
          'CSV',
          'none',
          'your_access_key_id', 
          'your_secret_access_key'
      )
  WHERE
      _path LIKE '%test%';