本文介绍云数据库ClickHouse如何访问阿里云对象存储OSS中的数据。

背景信息

OSS是阿里云提供的低成本对象存储服务,常用于海量日志、视频、照片的存储。云数据库ClickHouse支持访问多种格式的OSS文件,如CSV、TSV、JSON、XML、Protobuf、Parquet、ORC等。

云数据库ClickHouse提供了两种方式来访问OSS中的数据。分别是:
  • 表函数:提供一个表函数oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<column-definitions>'),直接对OSS数据进行读取操作。
  • 表引擎:提供一个表引擎oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>'),对OSS数据进行读操作,也能够进行写操作。
前置要求:
  • 为了确保ClickHouse后端节点与您的OSS服务之间网络通畅,表函数或表引擎参数中填写的oss-endpoint必须是VPC网络类型的endpoint。且OSS bucket必须与您的ClickHouse实例在同一地域,比如都在上海Region。
  • 表函数中填写的acces-key-id、access-key-secret必须对相应的oss-file-path有读取权限。
  • 表引擎中填写的acces-key-id、access-key-secret必须对相应的oss-file-path有读取权限。如果需要执行insert操作,还需要具备写入权限。
  • oss-file-path参数的格式需要满足OSS路径规范,一般格式为oss://<bucket-name/<path-to-file>
  • file-format-name及column-definitions要与实际的文件格式保持一致,否则会解析报错。其中file-format-name可以是ClickHouse支持的任意格式, 具体取值请参见ClickHouse支持的文件格式
  • 如果需要对OSS上的已有文件进行insert操作,由于OSS上只有Appendable Object才能够进行写入操作,所以需要确保该文件是通过AppendObject接口上传至OSS中的。

以下结合实际例子展示如何从OSS中导入数据到ClickHouse。

操作步骤

  1. 准备测试数据集。点击下载示例数据集

    该数据集是模拟产生的日志数据集合,以CSV格式存储。每行包括id, user_name, age, city, access_url等,各个字段之间通过逗号分割,每行以换行符结束。文件内容示例如下:

    1,tick,32,shanghai,http://xdbdsd.com/xgwgwe
    2,wangl,22,beijing,http://ghwbw.com/xgwgwe
  2. 上传数据集到OSS。
    将示例数据集传输到您希望的OSS指定路径下。您也可以直接使用我们预先准备好的公开可读的OSS地址:oss://clickhouse-release-open-access/doc-data/oss-import/access_log_csv.txt
  3. 参见t1847671.html#task_2355399连接云数据库ClickHouse。
  4. 通过表函数对OSS数据进行读取操作。
    SELECT *
    FROM oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/access_log_csv.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
    
    ┌─id─┬─user_name─┬─age─┬─city─────┬─access_url───────────────┐
    │  1 │ tick      │  32 │ shanghai │ http://xdbdsd.com/xgwgwe │
    │  2 │ wangl     │  22 │ beijing  │ http://ghwbw.com/xgwgwe  │
    │  3 │ xiaoh     │  23 │ shenzhen │ http://holko.com/xgwgwe  │
    │  4 │ jess      │  45 │ hangzhou │ http://jopjop.com/xgwgwe │
    │  5 │ jack      │  14 │ shanghai │ http://wewsd.com/xgwgwe  │
    │  6 │ tomy      │  25 │ hangzhou │ http://sbedr.com/xgwgwe  │
    │  7 │ lucy      │  45 │ shanghai │ http://ghhwed.com/xgwgwe │
    │  8 │ tengyin   │  26 │ shanghai │ http://hewhe.com/xgwgwe  │
    │  9 │ cuos      │  27 │ shenzhen │ http://yoiuj.com/xgwgwe  │
    │ 10 │ wangsh    │  37 │ shanghai │ http://hhou.com/xgwgwe   │
    └────┴───────────┴─────┴──────────┴──────────────────────────┘
    进行任意SQL查询分析。
    SELECT *
    FROM oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/access_log_csv.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String')
    WHERE id = 9;
    
    ┌─id─┬─user_name─┬─age─┬─city─────┬─access_url──────────────┐
    │  9 │ cuos      │  27 │ shenzhen │ http://yoiuj.com/xgwgwe │
    └────┴───────────┴─────┴──────────┴─────────────────────────┘
    
    
    SELECT
        user_name,
        count(*) AS uv
    FROM oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/access_log_csv.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String')
    GROUP BY user_name;
    
    ┌─user_name─┬─uv─┐
    │ tick      │  1 │
    │ lucy      │  1 │
    │ tengyin   │  1 │
    │ wangl     │  1 │
    │ wangsh    │  1 │
    │ jess      │  1 │
    │ tomy      │  1 │
    │ cuos      │  1 │
    │ xiaoh     │  1 │
    │ jack      │  1 │
    └───────────┴────┘
    说明 通过表函数访问OSS,数据并未同步到ClickHouse中,而是仍旧保留在OSS里。在海量数据场景下,可能由于远程读取OSS的带宽限制等导致性能低下。如果需要高性能分析,建议参考下文,将数据同步到ClickHouse中。
  5. 通过表引擎对OSS数据进行读取操作。
    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', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/access_log_csv.txt', 'CSV');
    
    
    SELECT *
    FROM oss_test_tbl
    
    ┌─id─┬─user_name─┬─age─┬─city─────┬─access_url───────────────┐
    │  1 │ tick      │  32 │ shanghai │ http://xdbdsd.com/xgwgwe │
    │  2 │ wangl     │  22 │ beijing  │ http://ghwbw.com/xgwgwe  │
    │  3 │ xiaoh     │  23 │ shenzhen │ http://holko.com/xgwgwe  │
    │  4 │ jess      │  45 │ hangzhou │ http://jopjop.com/xgwgwe │
    │  5 │ jack      │  14 │ shanghai │ http://wewsd.com/xgwgwe  │
    │  6 │ tomy      │  25 │ hangzhou │ http://sbedr.com/xgwgwe  │
    │  7 │ lucy      │  45 │ shanghai │ http://ghhwed.com/xgwgwe │
    │  8 │ tengyin   │  26 │ shanghai │ http://hewhe.com/xgwgwe  │
    │  9 │ cuos      │  27 │ shenzhen │ http://yoiuj.com/xgwgwe  │
    │ 10 │ wangsh    │  37 │ shanghai │ http://hhou.com/xgwgwe   │
    └────┴───────────┴─────┴──────────┴──────────────────────────┘
    
    
    SELECT
        city,
        count(*) AS pv
    FROM oss_test_tbl
    GROUP BY city
    
    ┌─city─────┬─pv─┐
    │ shanghai │  5 │
    │ shenzhen │  2 │
    │ hangzhou │  2 │
    │ beijing  │  1 │
    └──────────┴────┘
    说明 通过表引擎访问OSS,数据并未同步到ClickHouse中,而是仍旧保留在OSS里。在海量数据场景下,可能由于远程读取OSS的带宽限制等导致性能低下。如果需要高性能分析,建议参考下文,将数据同步到ClickHouse中。
  6. 通过表引擎对OSS数据进行写入操作。

    为了测试以下功能,需要您对示例数据集所在OSS bucket具有写入权限。上文中展示的oss://clickhouse-release/doc-data/oss-import/access_log_csv.txt仅仅是公开可读的,您不具备写权限,所以需要您将示例数据集上传到您自己的OSS中,并且替换建表语句中的oss-endpoint, oss-file-path为相应的值。

    阿里云OSS中文件分为Normal Object, Appendable Object。其中通过PutObject上传的文件为Normal Object,一旦上传完毕即为静态,不可更改。而通过AppendObject接口上传的文件,后续仍旧可以修改。如果需要通过表引擎对OSS文件进行写入操作,请参照如下方法使用AppendObject接口将文件传输到OSS上。OSS命令行工具ossutil的具体安装和参数含义,请参见OSS帮助文档

    ./ossutil appendfromfile /<your-loocal-path>/access_log_csv.txt oss://<your-oss-bucket>/<your-dir>/access_log_csv.txt
    参照如下方法进行写入操作。
    create table oss_test_tbl_appenable on cluster default
    (
    id UInt8,
    user_name String,
    age UInt16,
    city String,
    access_url String
    )
    engine = OSS('<your-oss-vpc-endpoint>', '<your-access-key-id>', '<your-access-key-secret>', 'oss://<your-oss-bucket>/<your-dir>/access_log_csv.txt', 'CSV');
    
    select * from oss_test_tbl_appenable;
    
    insert into oss_test_tbl_appenable values(11, 'wangw', 25, 'beijing', 'http://asewg.com/jhlue');
    
    select * from oss_test_tbl_appenable;
    说明 通过表引擎访问OSS,数据并未同步到ClickHouse中,而是仍旧保留在OSS里。在海量数据场景下,可能由于远程读取OSS的带宽限制等导致性能低下。如果需要高性能分析,建议参考下文,将数据同步到ClickHouse中。
  7. 将数据同步到ClickHouse中。
    在ClickHouse中建表,并且通过Insert From Select进行数据同步。
    -- 如果您的集群是单副本系列,则参照如下创建本地表
    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/db/default/tables/oss_test_tbl_local/{shard}', '{replica}')
    order by id;
    
    -- 创建分布式表
    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());
    
    -- 导入数据
    insert into oss_test_tbl_distributed select * from oss_test_tbl;
    
    select * from oss_test_tbl_distributed; 
  8. oss-file-path支持通配符。
    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://clickhouse-release-open-access/
                   doc-data/
                        oss-import/
                            small_files/
                                access_log_csv_1.txt
                                access_log_csv_2.txt
                                access_log_csv_3.txt
    通过如下方法来进行SQL查询,请关注oss-file-path参数的变化:
    
    select * from oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/small_files/*', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
    
    select * from oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/small_files/access*', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
    
    select * from oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/small_files/access_log_csv_{1,2,3}.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
    
    select * from oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/*/access_log_csv_{1,2,3}.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
    
    select * from oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/*/*', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
    
    select * from oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/*/access_log_csv_{1..3}.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
    
    select * from oss('oss-cn-shanghai-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://clickhouse-release-open-access/doc-data/oss-import/*/access_log_csv_?.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');