使用clickhouse-client导入数据

当您需要将本地文件的数据导入到云数据库ClickHouse时,本文将指导您如何通过clickhouse-client工具进行数据导入,以帮助您快速的完成数据导入。

注意事项

支持导入到云数据库ClickHouse的常见文件格式为TabSeparated、TabSeparatedWithNames、TabSeparatedWithNamesAndTypes、CSV和CSVWithNames。常见文件格式说明,详情请参见常见文件格式。若您的文件为其他格式,需满足ClickHouse社区标准,更多信息请参见文件格式及说明

前提条件

  • 已将本地机器的IP地址添加到云数据库ClickHouse的白名单中。如何添加白名单,详情请参见设置白名单

  • 已安装与云数据库ClickHouse集群版本对应的clickhouse-client工具。下载链接,请参见clickhouse-client

步骤一:准备测试数据

在clickhouse-client客户端的安装目录下,创建文件testdata.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

步骤二:创建云数据库ClickHouse

  1. 登录云数据库ClickHouse控制台

  1. 在页面左上角,选择目标集群所在的地域。

  1. 集群列表页面,根据您的集群类型选择对应的页签,单击目标集群ID。

  1. 单击右上方导航栏的登录数据库

  1. 登录实例页面,输入数据库账号和密码,单击登录

  1. 创建表。

    说明

    云数据库ClickHouse表的结构类型需与本地文件数据类型对应。

    • 创建本地表:

      • 如果您的集群是单副本版,请输入如下建表语句。

        CREATE TABLE 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 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;
    • 创建分布式表。

      说明

      如果您只需要导入本地文件数据至本地表,可跳过此步骤。

      CREATE TABLE test_tbl_distributed ON cluster default
      (
      id UInt8,
      user_name String,
      age UInt16,
      city String,
      access_url String
      )
      ENGINE = Distributed(default, default, test_tbl_local, rand());

步骤三:导入本地文件数据

在clickhouse-client客户端的安装目录下执行如下命令。

cat <本地文件名> | ./clickhouse-client --host=<数据库连接地址> --port=<TCP端口号> --user=<数据库账号> --password=<数据库账号的密码> --query="INSERT INTO <ClickHouse表名> FORMAT <本地文件格式>";
说明

外网导入数据速度较慢,如果数据量较大或者测试导入性能,您可以采用如下方法。

  • 内网导入:购买与云数据库ClickHouse集群在同一个VPC网络的ECS实例,从ECS通过VPC地址连接云数据库ClickHouse集群并导入数据。

  • 多进程导入:将原始文件等分为多份,启动多个client多进程并发导入。

步骤四:查询云数据库ClickHouse

  1. 通过DMS连接集群,详情请参见通过DMS连接ClickHouse

  1. 执行查询语句。

    SELECT * FROM test_tbl_distributed; 
    说明

    如果您导入的是本地表,请将查询语句中的分布式表名更换为本地表名,再进行查询。

    查询结果如下。查询结果

常见文件格式

云数据库ClickHouse支持常见文件格式如下。

常见文件格式

说明

TabSeparated

数据按行写入文本文件中,列与列之间通过Tab分割。需要对Tab、换行符、反斜线进行转义处理,分别对应为:\t\n\\。 NULL值使用\N进行表示。

TabSeparatedWithNames

与TabSeparated类似,区别是第一行是列名。解析时,第一行会被忽略,因此不能够使用第一行列名来决定列的位置。

TabSeparatedWithNamesAndTypes

与TabSeparated类似,区别是第一行是列名,第二行是类型。解析时,第一行、第二行都会被忽略。

CSV

数据按行写入文件中。字符串类型使用英文双引号括起来,字符串本身的双引号用两个双引号进行转义,数值类型不使用双引号。默认列分隔符为,,您也可以通过--format_csv_delimiter指定其他列分隔符,例如列与列之间使用竖线分割,则命令示例如下。

cat testdata.csv | ./clickhouse-client --format_csv_delimiter="|" --host=cc-bp163l724nkf8****.clickhouse.ads.aliyuncs.com --port=3306 --user=test --password=123456Aa --query="INSERT INTO test_tbl_distributed FORMAT CSV";

CSVWithNames

与CSV类似,区别是第一行表示列名。解析时,第一行会被忽略,因此不能够使用第一行列名来决定列的位置。