更新时间:2024-07-24 GMT+08:00

导入DWS表数据至ClickHouse

ClickHouse支持CSV、JSON等格式文件的数据导入导出操作。本章节主要介绍怎么把DWS数据仓库服务中的表数据导出到CSV文件,再把CSV文件数据导入到ClickHouse表中。

前提条件

  • ClickHouse集群和实例状态正常。
  • DWS集群已创建,已获取到相关表所在的数据库用户名和密码。
  • 已安装MRS客户端,例如安装目录为“/opt/client”。以下操作的客户端目录只是举例,请根据实际安装目录修改。在使用客户端前,需要先下载并更新客户端配置文件,确认Manager的主管理节点后才能使用客户端。

DWS服务数据导入到ClickHouse

  1. 参考下载Data Studio图形界面客户端中的“Data Studio图形界面客户端”下载Data Studio工具。
  2. 使用已创建好的DWS集群中的数据库用户名、密码等信息,参考使用Data Studio工具连接章节连接DWS数据库。
  3. 将DWS数据库中的表数据导出到CSV格式文件。

    1. (可选)如果DWS数据库对应的表和数据已经存在,该步骤请忽略。本文通过演示在DWS创建测试表,并插入测试数据进行演示。
      使用Data Studio创建测试表warehouse_t1,并插入测试数据。
      CREATE TABLE warehouse_t1 
       (  
         W_WAREHOUSE_SK INTEGER NOT NULL, 
         W_WAREHOUSE_ID CHAR ( 16 ) NOT NULL, 
         W_WAREHOUSE_NAME VARCHAR ( 20 ), 
         W_WAREHOUSE_SQ_FT INTEGER, 
         W_STREET_NUMBER CHAR ( 10 ), 
         W_STREET_NAME VARCHAR ( 60 ), 
         W_STREET_TYPE CHAR ( 15 ), 
         W_SUITE_NUMBER CHAR ( 10 ), 
         W_CITY VARCHAR ( 60 ), 
         W_COUNTY VARCHAR ( 30 ), 
         W_STATE CHAR ( 2 ), 
         W_ZIP CHAR ( 10 ), 
         W_COUNTRY VARCHAR ( 20 ), 
         W_GMT_OFFSET DECIMAL ( 5,2 ),
         W_DATE DATE  
       ); 
      
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name1', 2324, 123, 'STREET_NAME1', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:07');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name2', 2324, 123, 'STREET_NAME2', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:08');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name3', 2324, 123, 'STREET_NAME3', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:09');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name4', 2324, 123, 'STREET_NAME4', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:00');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name5', 2324, 123, 'STREET_NAME5', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:01');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name6', 2324, 123, 'STREET_NAME6', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:02');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name7', 2324, 123, 'STREET_NAME7', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:03');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name8', 2324, 123, 'STREET_NAME8', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:04');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name9', 2324, 123, 'STREET_NAME9', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:05');
      INSERT INTO warehouse_t1 VALUES(1314, 123, 'name0', 2324, 123, 'STREET_NAME0', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:06');
      INSERT INTO warehouse_t1(W_WAREHOUSE_SK, W_WAREHOUSE_ID, W_WAREHOUSE_NAME, W_DATE) VALUES(1314, 123, 'name0', '2021-07-05 17:45:06');
    2. 导出DWS表数据为CSV格式文件。

      在Data Studio左侧的“对象浏览器”中,右键要导出的表,选择“导出表数据”。在导出界面选择具体的导出路径,格式选择CSV、分隔符选择逗号,在安全免责声明下选择“我同意”,单击“确定”完成数据导出。例如,本文导出表warehouse_t1数据文件为“warehouse_t1.csv”。

  4. 使用WinSCP工具将导出的CSV文件上传到ClickHouse实例节点主机目录下。比如,当前上传“warehouse_t1.csv”文件到/opt目录下。
  5. 以客户端安装用户,登录安装ClickHouse客户端的节点。
  6. 执行以下命令,切换到客户端安装目录。

    cd /opt/client

  7. 执行以下命令配置环境变量。

    source bigdata_env

  8. 如果当前集群已启用Kerberos认证,执行以下命令认证当前用户,当前用户需要具有创建ClickHouse表的权限,具体请参见创建ClickHouse角色章节,为用户绑定对应角色。如果当前集群未启用Kerberos认证,则无需执行本步骤。

    1. 如果是MRS 3.1.0版本集群,则需要先执行:export CLICKHOUSE_SECURITY_ENABLED=true
    2. kinit 组件业务用户

      例如,kinit clickhouseuser。

  9. 执行以下命令连接到要导入数据的ClickHouse实例节点。

    clickhouse client --host ClickHouse的实例IP --user 登录名 --password --port ClickHouse的端口号 --database 数据库名

    输入用户密码

  10. 在ClickHouse实例节点上创建和DWS表结构相同的表。

    例如,当前执行以下建表语句,在ClickHouse实例上的默认数据库和用户下创建和3中相同表结构的ReplicatedMergeTree表warehouse_t1。
    CREATE TABLE warehouse_t1
    (
        `W_WAREHOUSE_SK` Int32 NOT NULL,
        `W_WAREHOUSE_ID` String NOT NULL,
        `W_WAREHOUSE_NAME` String,
        `W_WAREHOUSE_SQ_FT` Int32,
        `W_STREET_NUMBER` String,
        `W_STREET_NAME` String,
        `W_STREET_TYPE` String,
        `W_SUITE_NUMBER` String,
        `W_CITY` String,
        `W_COUNTY` String,
        `W_STATE` String,
        `W_ZIP` String,
        `W_COUNTRY` String,
        `W_GMT_OFFSET` Decimal(5, 2),
        `W_DATE` DateTime
    )
    ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/warehouse_t1', '{replica}')
    PARTITION BY toYear(W_DATE)
    ORDER BY (W_DATE, W_WAREHOUSE_ID);

  11. 退出ClickHouse客户端。

    exit;

  12. 执行以下命令,将导出的CSV文件数据导入到ClickHouse表中。

    clickhouse client --host ClickHouse实例IP地址 --database 数据库名 --port 端口号 --format_csv_delimiter="csv文件数据分隔符" --query="INSERT INTO 数据表名 FORMAT CSV" < csv文件所在主机路径

    例如,导入以逗号分隔的CSV文件“warehouse_t1.csv”数据到默认数据库和用户下的表warehouse_t1

    clickhouse client --host 10.248.12.10 --format_csv_delimiter="," --query="INSERT INTO warehouse_t1 FORMAT CSV" < /opt/warehouse_t1.csv

  13. 导入完成后,登录ClickHouse客户端连接导入数据的ClickHouse实例节点,执行查询命令查看导入的结果。

    例如,导入完成后查询表warehouse_t1数据,结果如下:

    clickhouse client --host ClickHouse的实例IP --user 登录名 --password --port ClickHouse的端口号 --database 数据库名

    输入用户密码

    select * from warehouse_t1;