本文介绍如何通过云数据库ClickHouse查询阿里云对象存储OSS(Object Storage Service)数据以及如何写入数据至OSS。

注意事项

  • 云数据库ClickHouse支持访问多种格式的OSS文件,除不支持Protobuf和CapnProto外,其他均支持,具体请参见ClickHouse支持的文件格式
  • 请确认OSS存储空间(Bucket)与您的云数据库ClickHouse集群在同一地域,并使用阿里云VPC网络访问OSS。
  • 通过云数据库ClickHouse写入数据至OSS,会覆盖原来的OSS文件数据。

前提条件

  • 已开通OSS服务。如何开通,请参见开通OSS服务
  • 已在云数据库ClickHouse所在地域创建存储空间(Bucket)。如何创建,请参见创建存储空间
  • 已上传文件至OSS。如何上传,请参见上传文件
    说明 本文以1,tick,32,shanghai,http://example.com为示例数据,存储为ceshi.csv。
  • 访问OSS的账号已具备对OSS对象的读写权限。如何设置权限,请参见访问控制概述

操作步骤

  1. 登录云数据库ClickHouse数据库
    1. 登录云数据库ClickHouse控制台
    2. 集群列表页面,单击目标集群ID。
    3. 单击右上方导航栏的登录数据库
    4. 登录实例页面,输入数据库账号和密码,单击登录
  2. 通过云数据库ClickHouse查询OSS数据
    云数据库ClickHouse提供了表引擎和表函数两种方法查询OSS数据。
    • 方法一:通过表引擎查询OSS数据
      说明 如果您需要写入数据至OSS,请使用此方法先创建外表,再查询数据。
      1. 创建OSS外表。
        建表语法如下。
        CREATE TABLE <table_name> [on cluster default]
        (
        'col_name1' col_type1,
        'col_name2' col_type2,
        ...
        )
        ENGINE = OSS('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>');
        参数说明如下。
        参数名 描述
        table_name 表名。
        col_name1,col_name2 列名。
        col_type1,col_type2 列类型。
        说明 OSS外表的结构类型需与OSS数据对应。
        oss-endpoint ECS的VPC网络访问(内网)的Endpoint。如何获取,请参见访问域名和数据中心
        说明 请确保OSS存储空间(Bucket)与您的云数据库ClickHouse集群在同一地域。
        access-key-id 访问OSS数据的AccessKey ID。
        access-key-secret 访问OSS数据的AccessKey Secret。
        oss-file-path 文件的存储路径。一般格式为oss://<bucket-name>/<path-to-file>
        说明 oss-file-path参数支持通过通配符进行模糊匹配。更多信息,请参见通配符模糊匹配OSS的存储路径
        file-format-name 文件的格式,本文为CSV。
        示例如下。
        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/ceshi.csv', 'CSV');
      2. 查询外表数据,示例如下。
        select * from oss_test_tbl;
        查询结果如下。上传结果
    • 方法二:通过表函数查询OSS数据
      查询语法如下。
      SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<column-definitions>');
      参数说明如下。
      参数名 描述
      oss-endpoint ECS的VPC网络访问(内网)的Endpoint。如何获取,请参见访问域名和数据中心
      说明 请确保OSS存储空间(Bucket)与您的云数据库ClickHouse集群在同一地域。
      access-key-id 访问OSS数据的AccessKey ID。
      access-key-secret 访问OSS数据的AccessKey Secret。
      oss-file-path 文件的存储路径。一般格式为oss://<bucket-name>/<path-to-file>
      说明 oss-file-path参数支持通过通配符进行模糊匹配。更多信息,请参见通配符模糊匹配OSS的存储路径
      file-format-name 文件的格式,本文为CSV。
      column-definitions OSS数据的列名、列类型。
      示例如下。
      SELECT * FROM oss('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', 'oss://testBucketName/ceshi.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
      查询结果如下。上传结果
  3. 通过云数据库ClickHouse写入数据至OSS
    说明 请确认您已经参照步骤2创建了OSS外表oss_test_tbl
    insert into oss_test_tbl values(11, 'tick', 25, 'shanghai', 'http://example.com');
  4. 查询OSS外表数据
    select * from oss_test_tbl;
    查询结果如下。写入查询