本文介绍如何将数据从阿里云对象存储OSS(Object Storage Service)导入至云数据库ClickHouse

注意事项

  • OSS服务为付费服务。计费详情,请参见计量计费概述
  • 请确保OSS存储空间(Bucket)与您的云数据库ClickHouse集群在同一地域,并使用阿里云VPC网络访问OSS。

前提条件

  • 已开通OSS服务。如何开通,请参见开通OSS服务
  • 已在云数据库ClickHouse集群所在地域创建存储空间(Bucket)。如何创建,请参见创建存储空间
  • 访问OSS的账号已具备对OSS对象的读权限。如何设置权限,请参见访问控制概述

操作步骤

  1. 准备测试数据
    将如下示例数据,存储为ceshi.csv。
    1,tick,32,shanghai,http://example.com
    2,wangl,22,beijing,http://example.com
    3,xiaoh,23,shenzhen,http://example.com
    4,jess,45,hangzhou,http://example.com
    5,jack,14,shanghai,http://example.com
    6,tomy,25,hangzhou,http://example.com
    7,lucy,45,shanghai,http://example.com
    8,tengyin,26,shanghai,http://example.com
    9,cuos,27,shenzhen,http://example.com
    10,wangsh,37,shanghai,http://example.com
  2. 上传数据至OSS
    1. 登录OSS管理控制台
    1. 单击左侧导航栏的Bucket列表
    2. 单击目标Bucket名称。
    3. 文件管理页签,单击上传文件
    4. 上传文件页面,上传示例数据集ceshi.csv。
      说明 详细参数配置,请参见上传文件
    5. 单击上传文件
  3. 创建云数据库ClickHouse
    1. 登录云数据库ClickHouse控制台
    2. 集群列表页面,单击目标集群ID。
    3. 单击右上方导航栏的登录数据库
    4. 登录实例页面,输入数据库账号和密码,单击登录
    5. 创建本地表。
      说明 云数据库ClickHouse表的结构类型需与OSS数据对应。
      • 如果您的集群是单副本版,请输入如下建表语句。
        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;
    6. 创建分布式表。
      说明 如果您只需要导入OSS数据至本地表,可跳过此步骤。
      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
    • 方法一:通过表函数导入OSS数据
      导入语法如下。
      insert into oss_test_tbl_distributed select * from oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<column-definitions>');
      CSV文件默认列分隔符为,,如果您导入的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>', '<column-definitions>') settings format_csv_delimiter='|';
      参数说明如下。
      参数名 描述
      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数据的列名、列类型。
      示例如下。
      insert into oss_test_tbl_distributed 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');
    • 方法二:通过表引擎导入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. 将OSS外表数据导入至云数据库ClickHouse。
        insert into oss_test_tbl_distributed select * from oss_test_tbl;
        CSV文件默认列分隔符为,,如果您导入的CSV文件为其他列分隔符,需要在导入语句中通过format_csv_delimiter指定其他列分隔符。例如CSV文件列与列之间使用竖线分割,则命令示例如下。
        insert into oss_test_tbl_distributed select * from oss_test_tbl settings format_csv_delimiter='|';
  5. 查询云数据库ClickHouse
    执行查询语句。
    select * from oss_test_tbl_distributed; 
    说明 如果您导入的是本地表,请将查询语句中的分布式表名更换为本地表名,再进行查询。
    查询结果如下。查询结果

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

OSS中通常存在多个具备相同命名规则的小文件,为了简化对于小文件的分析,oss-file-path参数支持通过如下通配符进行模糊匹配。
  • *:匹配任意文件名、目录名。如/dir/*匹配/dir下的所有文件。
  • {x, y, z}:匹配大括号中的任意一个值。如file_{x,y,z}匹配file_xfile_yfile_z
  • {num1..num2}:匹配[num1,num2]展开后的任意一个值。如file_{1..3}匹配file_1file_2file_3
  • ?:匹配任意一个单字符。如file_?匹配file_afile_bfile_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