从OSS导入数据

前提条件

• 已开通OSS服务。如何开通，请参见开通OSS服务。

• 已在云数据库ClickHouse所在地域创建存储空间（Bucket）。如何创建，请参见创建存储空间。

• 访问OSS的账号已具备对OSS对象的读权限。如何设置权限，请参见访问控制概述。

注意事项

步骤一：将测试数据上传至OSS中

1. 准备测试数据，将如下示例数据存储为test.csv。

   1,yang,32,shanghai,http://example.com
   2,wang,22,beijing,http://example.com
   3,xiao,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,wangli,27,shenzhen,http://example.com
   10,xiaohua,37,shanghai,http://example.com

2. 登录OSS管理控制台。

3. 单击左侧导航栏的Bucket列表。

4. 在Bucket列表页面，单击目标Bucket名称。

5. 在文件管理页签，单击上传文件。

6. 在上传文件页面，上传示例数据集test.csv。

   说明

   详细参数配置，请参见上传文件。

步骤二：在云数据库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,
   ac
   cess_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());

步骤三：导入OSS数据至云数据库ClickHouse

云数据库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>', '<col_name> <col_type>(,...)');

insert into oss_test_tbl_distributed select * from oss('https://<BucketName>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

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');

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');

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='|';

步骤四：查询导入云数据库ClickHouse的OSS数据

执行查询语句。

select * from oss_test_tbl_distributed; 

返回结果：

┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
│  1 │  yang     │   32  │  shanghai  │   http://example.com  │
│  2 │  wang     │   22  │  beijing   │   http://example.com  │
│  3 │  xiao     │   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 │  wangli   │   27  │  shenzhen  │   http://example.com  │
│ 10 │  xiaohua  │   37  │  shanghai  │   http://example.com  │
└────┴───────────┴───────┴────────────┴───────────────────────┘

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

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://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