AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云数据库 ClickHouse 操作指南 数据迁移同步 从OSS导入数据

从OSS导入数据

更新时间：
产品详情
我的收藏

您可以通过表引擎或表函数将数据从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。

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

    单副本版集群

    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。如何获取，请参见OSS地域和访问域名。

      重要

      请确保存储空间（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', 'LTAI****************', 'yourAccessKeySecret', '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', 'STS.****************', 'STS.****************','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', 'LTAI****************', 'yourAccessKeySecret', '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', 'STS.****************', 'STS.****************', '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

上一篇：使用无感集成同步RDS MySQL至ClickHouse集群下一篇：通过外表访问OSS数据