全部产品
Search

搜索本产品

    云数据库 ClickHouse：从OSS导入数据

    文档中心

    云数据库 ClickHouse：从OSS导入数据

    更新时间：Aug 02, 2024

    您可以通过表引擎或表函数将数据从OSS中导入至云数据库ClickHouse，实现日志查询分析和加工等操作。本文介绍如何将OSS数据导入至云数据库ClickHouse。

    前提条件

    • 已开通OSS服务。如何开通，请参见开通OSS服务。

    • 已创建存储空间（Bucket），且Bucket与云数据库ClickHouse位于同一地域。如何创建，请参见创建存储空间。

    • 访问OSS的账号已具备对OSS对象的读权限。如何设置权限，请参见访问控制概述。

    数据准备

    将如下测试数据存储为test.csv，并上传至OSS中。在导入数据时，云数据库ClickHouse支持的默认列分隔符为,。上传数据的具体操作，请参见上传文件。

    1,yang,32,shanghai,http://example1.com
2,wang,22,beijing,http://example2.com
3,xiao,23,shenzhen,http://example3.com
4,jess,45,hangzhou,http://example4.com
5,jack,14,shanghai,http://example5.com
6,tomy,25,hangzhou,http://example6.com
7,lucy,45,shanghai,http://example7.com
8,tengyin,26,shanghai,http://example8.com
9,wangli,27,shenzhen,http://example9.com
10,xiaohua,37,shanghai,http://example10.com

    操作步骤

    1. 连接云数据库ClickHouse集群。具体操作，请参见连接集群。

    2. 创建本地表oss_test_tbl_local。

      重要

      • 云数据库ClickHouse表的结构需与OSS外表的结构一致，并保证兼容OSS中的数据格式（尤其注意null字段等），防止出现因数据无法解析导致集群异常。

      • 请根据集群的副本配置选择对应的建表语句。您可以在控制台集群信息页面的集群属性区域查看副本配置。

      • 您可以根据业务需求选择合适的表引擎。表引擎的详细信息，请参见表引擎。

      单副本版集群

      CREATE TABLE oss_test_tbl_local ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = MergeTree()
ORDER BY id;

      双副本版集群

      CREATE TABLE oss_test_tbl_local ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
ORDER BY id;

    3. （可选）创建分布式表oss_test_tbl_distributed。

      说明

      如果您想同时将数据分发到集合中的各个本地表时需要创建分布式表。

      CREATE TABLE oss_test_tbl_distributed ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = Distributed(default, default, oss_test_tbl_local, rand());

    4. 导入OSS数据至云数据库ClickHouse。

      云数据库ClickHouse提供了表引擎和表函数两种方法导入OSS数据。

      重要

      请根据集群的内核版本选择对应的建表语句。您可以在控制台集群信息页面的集群属性区域查看版本。

      方法一：通过表引擎导入OSS数据

      1. 创建OSS外表oss_test_tbl。

        22.8以下版本的建表语法

        CREATE TABLE <table_name> [ON CLUSTER cluster]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>');

        22.8及以上版本的建表语法

        CREATE TABLE <table_name> [ON CLUSTER cluster]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('https://<BucketName>.<oss-endpoint>/<file-name>', '<access-key-id>', '<access-key-secret>', '<file-format-name>');

        参数说明如下：

        参数名

        描述

        table_name

        表名。

        ON CLUSTER cluster

        在每一个节点上都创建一个本地表，固定为ON CLUSTER default。

        col_name1,col_name2

        列名。

        col_type1,col_type2

        列类型。

        重要

        OSS外表的结构类型需与OSS数据对应。

        BucketName

        Bucket的名称。

        oss-endpoint

        访问OSS的Endpoint。如何获取，请参见访问域名和数据中心。

        重要

        请确保存储空间（Bucket）与云数据库ClickHouse集群在同一地域，并使用阿里云VPC网络访问OSS。

        file-name

        文件名称。

        access-key-id

        访问OSS数据的Accesskey ID。如何获取，请参见如何获取AccessKey。

        access-key-secret

        访问OSS数据的AccessKey Secret。如何获取，请参见如何获取AccessKey。

        oss-file-path

        文件的存储路径。一般格式为oss://<bucket-name>/<path-to-file>。

        说明

        oss-file-path参数支持通过通配符进行模糊匹配。更多信息，请参见通配符模糊匹配OSS的存储路径。

        file-format-name

        文件的格式，本文为CSV。

        示例语句：

        22.8以下版本的示例语句

        CREATE TABLE oss_test_tbl on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', 'oss://testBucketName/test.csv', 'CSV');

        22.8及以上版本的示例语句

        CREATE TABLE oss_test_tbl on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****','CSV')

      2. 将OSS外表oss_test_tbl数据导入至分布式表oss_test_tbl_distributed中。

        说明

        如果您只需要导入OSS数据至本地表，请将导入语句中的分布式表名更换为本地表名。

        INSERT INTO oss_test_tbl_distributed SELECT * FROM oss_test_tbl;

        如果您导入的CSV文件里指定的分隔符不是,，则需要在导入语句中通过format_csv_delimiter指定其他列分隔符。例如CSV文件列与列之间使用竖线分割，则命令示例如下。

        INSERT INTO oss_test_tbl_distributed SELECT * FROM oss_test_tbl settings format_csv_delimiter='|';

      方法二：通过表函数导入OSS数据

      22.8以下版本的导入语法

      INSERT INTO <table_name> SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<col_name> <col_type>(,...)');

      22.8及以上版本的导入语法

      INSERT INTO <table_name> SELECT * FROM oss('https://<BucketName>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

      参数的详细信息，请参见参数说明。

      示例语句：

      22.8以下版本的示例语句

      INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', 'oss://testBucketName/test.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

      22.8及以上版本的示例语句

      INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

      如果您导入的CSV文件里指定的分隔符不是,，则需要在导入语句中通过format_csv_delimiter指定其他列分隔符。例如CSV文件列与列之间使用竖线分割，则示例语句如下。

      INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>',  '<col_name> <col_type>(,...)') settings format_csv_delimiter='|';

    5. 查询分布式表oss_test_tbl_distributed中数据，验证OSS数据是否成功导入至云数据库ClickHouse。

      SELECT * FROM oss_test_tbl_distributed;

      返回结果如下：

      ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
│  1 │  yang     │   32  │  shanghai  │  http://example1.com  │
│  2 │  wang     │   22  │  beijing   │  http://example2.com  │
│  3 │  xiao     │   23  │  shenzhen  │  http://example3.com  │
│  4 │  jess     │   45  │  hangzhou  │  http://example4.com  │
│  5 │  jack     │   14  │  shanghai  │  http://example5.com  │
│  6 │  tomy     │   25  │  hangzhou  │  http://example6.com  │
│  7 │  lucy     │   45  │  shanghai  │  http://example7.com  │
│  8 │  tengyin  │   26  │  shanghai  │  http://example8.com  │
│  9 │  wangli   │   27  │  shenzhen  │  http://example9.com  │
│ 10 │  xiaohua  │   37  │  shanghai  │  http://example10.com │
└────┴───────────┴───────┴────────────┴───────────────────────┘

    通配符模糊匹配OSS的存储路径

    OSS中通常存在多个具备相同命名规则的小文件，为了简化对于小文件的分析，oss-file-path参数支持通过如下通配符进行模糊匹配。

    • *：匹配任意文件名、目录名。如/dir/*匹配/dir下的所有文件。

    • {x, y, z}：匹配大括号中的任意一个值。如file_{x,y,z}匹配file_x、file_y或file_z。

    • {num1..num2}：匹配[num1,num2]展开后的任意一个值。如file_{1..3}匹配file_1、file_2或file_3。

    • ?：匹配任意一个单字符。如file_?匹配file_a、file_b、file_c等等。

    示例

    上传文件的目录结构如下。

    oss://testBucketName/
               doc-data/
                    oss-import/
                        small_files/
                            access_log_csv_1.txt
                            access_log_csv_2.txt
                            access_log_csv_3.txt

    oss-file-path参数的部分参考取值如下。

    • oss://testBucketName/doc-data/oss-import/small_files/*

    • oss://testBucketName/doc-data/oss-import/small_files/access*

    • oss://testBucketName/doc-data/oss-import/small_files/access_log_csv_{1,2,3}.txt

    • oss://testBucketName/doc-data/oss-import/*/access_log_csv_{1,2,3}.txt

    • oss://testBucketName/doc-data/oss-import/*/*

    • oss://testBucketName/doc-data/oss-import/*/access_log_csv_{1..3}.txt

    • oss://testBucketName/doc-data/oss-import/*/access_log_csv_?.txt