从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。如何获取,请参见访问域名和数据中心

      重要

      请确保存储空间(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', '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外表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', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', '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', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', '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_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