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

前提条件

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

注意事项

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

步骤一:登录数据库

  1. 登录云数据库ClickHouse控制台
  2. 集群列表页面,选择默认实例列表,单击目标集群ID。
  3. 集群信息页面,单击右上方导航栏的登录数据库
  4. 登录实例页面,输入数据库账号和密码,单击登录

步骤二:创建OSS外表

  1. 创建OSS外表。
    • 21.8及以下版本的建表语法:
      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>');
    • 22.8版本的建表语法:
      CREATE TABLE <table_name> [on cluster default]
      (
      '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 表名。
      col_name1,col_name2 列名。
      col_type1,col_type2 列类型。
      重要 OSS外表的结构类型需与OSS数据对应。
      BucketName Bucket的名称。
      oss-endpoint ECS的VPC网络访问(内网)的Endpoint。如何获取,请参见访问域名和数据中心
      重要 请确保OSS存储空间(Bucket)与您的云数据库ClickHouse集群在同一地域。
      file-name 文件名称。
      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。
    示例语句:
    • 21.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外表的数据。
    select * from oss_test_tbl;
    返回结果:
    ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
    │  1 │  tick     │   32  │  shanghai  │   http://example.com  │
    └────┴───────────┴───────┴────────────┴───────────────────────┘

步骤三:写入数据至OSS

insert into oss_test_tbl values(11, 'tick', 25, 'shanghai', 'http://example.com');

步骤四:查询数据

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

  • 通过表引擎查询OSS数据。
    查询OSS外表的数据:
    select * from oss_test_tbl;
    返回结果:
    ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
    │ 11 │  tick     │   25  │  shanghai  │   http://example.com  │
    └────┴───────────┴───────┴────────────┴───────────────────────┘
  • 通过表函数查询OSS数据。
    • 21.8及以下版本的查询语法:
      SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<col_name> <col_type>(,...)');
    • 22.8版本的查询语法:
      SELECT * FROM oss('https://<bucketName>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

      参数的详细信息,请参见参数说明

    示例语句:
    • 21.8及以下版本的查询语句:
      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版本的查询语句:
      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')
      返回结果:
      ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
      │ 11 │  tick     │   25  │  shanghai  │   http://example.com  │
      └────┴───────────┴───────┴────────────┴───────────────────────┘