The CnchS3 engine developed by ByteHouse integrates object storage and supports managing data stored in S3-compatible object storage via ByteHouse. This article will introduce how to use S3 external tables in ByteHouse cloud data warehouse edition.

ByteHouse, using the CnchS3 engine, enables integration with S3-compatible object storage without needing to migrate data into ByteHouse, allowing queries, writes, and other operations on the original data in object storage. ByteHouse provides two usage methods: CnchS3 table engine (persistent external table) and CnchS3 table function, which you can flexibly choose according to business scenarios.
The comparison between CnchS3 table engine and CnchS3 table function is as follows:

Fee description #

The purpose of external tables is to provide processing capabilities for data other than ByteHouse internal tables. With a simple DDL statement, you can create an external table in ByteHouse, establishing an association between ByteHouse tables and external data sources.

• External tables in ByteHouse only incur computation fees, following ByteHouse's computation resource billing rules.
• When using the external table feature, the data of the external table will not be copied to ByteHouse and incur storage fees. For storage fees, please refer to the billing rules of the external data source's storage side.
• When using ByteHouse external tables to access external data sources, the external data source may incur computation, access, data transfer, and other fees. The specific fees depend on the external data source's billing method. Please refer to the relevant product's description.

Before starting, please ensure you have access privileges to the object storage bucket you use:

• Obtain Access Key ID (AK) and Secret Access Key (SK). If you are using BytePlus object storage service (TOS), for details please refer to Access Key (key) management.
• If you encounter an “Access Denied” error when using TOS, please check privilege issues. For details, please refer to object storage Access Denied troubleshooting suggestions.

1. Security: Ensure the security of Access Key ID and Secret Access Key, and do not expose them to unauthorized personnel.
2. Performance: When performing large-volume data import, pay attention to the database and network load. You can import data in batches to reduce the load.
3. S3 URL correctness: When using TOS external tables to import data, note that the endpoint provided by BytePlus TOS differs from the S3 endpoint. As shown below, the link https://tos-{REGION}.volces.com/xxx copied from the BytePlus TOS console cannot be used directly. The domain name must be changed to the S3 endpoint domain name and modified to the format https://tos-s3-{REGION}.volces.com/xxx. For details, see BytePlus TOS supported regions and access domain names.

Create table #

Syntax

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'
);

Example

• Create single file table

  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'
      );
  

• Use wildcard character to create multi-file table

  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'
      );
  

Write data #

You can use the INSERT INTO statement to write data to S3 external tables. The usage is the same as writing to normal tables.
Examples are as follows:

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

Query data #

You can use the SELECT statement to query data in S3 external tables. The usage is the same as querying normal tables. ByteHouse also supports pruning queries by the _path and _file columns. Examples are as follows:

• Query data

  SELECT * FROM s3_engine_table;
  

• Use the _file column for pruning queries

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

Modify configuration #

By default, the resources_assign_type **** value of external tables is server_push. In this mode, data files are sent to workers for querying. To query locally on the server, change resources_assign_type to server_local.

ALTER TABLE s3_engine_table MODIFY SETTING resources_assign_type='server_local'

Change AK/SK #

You can update AK/SK by using the SELECT statement.

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

Write data #

Syntax

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, ...),  
    (...);

Example

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-level write data #

The syntax for partition-level data writing is the same as normal data writing, but the object storage path must contain the '{_partition_id}' field. For other parameters, please refer to the parameter description above.
Examples are as follows:

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');

Query data #

Use the CnchS3 function to directly query S3 external tables.
Syntax

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

Example

• Query a single file

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

• Use wildcard character to query multiple files and perform pruning queries

  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%';