springboot+clickhouse批量插入数据

问题：在clickhouse-server中批量插入数据；我选择的表引擎是MergeTree引擎

1. 解决方案：
   通过file引擎建表，同时指定数据文件；
   数据文件是存储在/var/lib/clickhouse/data/数据库名/表名/
2. 数据文件一开始就上传到data目录下，通过file引擎建表；
3. 通过执行sql语句 INSERT INTO anothertable select * from onetable把数据导入MergeTree引擎的表里边；

package com.shi.dataopen.utils;

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.*;

@Component
public class FtpUtils {
    //ftp服务器ip地址
    private static final String FTP_ADDRESS = "192.168.248.128";
    //端口号
    private static final int FTP_PORT = 21;
    //用户名
    private static final String FTP_USERNAME = "root";
    //密码
    private static final String FTP_PASSWORD = "123456";
     private static final String FTP_BASEPATH = "/var/lib/clickhouse/data/default/";
    //参数传过来了文件和文件的输入流

    public String getPath(String tableName){
        return FTP_BASEPATH+tableName+"/";
    }
    public boolean uploadFile(String tableName,String originFileName, InputStream input) {
        boolean success = false;
        FTPClient ftp = new FTPClient();//这是最开始引入的依赖里的方法
        ftp.setControlEncoding("utf-8");
        try {
            int reply;
            ftp.connect(FTP_ADDRESS, FTP_PORT);// 连接FTP服务器
            ftp.login(FTP_USERNAME, FTP_PASSWORD);// 登录
            reply = ftp.getReplyCode();//连接成功会的到一个返回状态码
            ftp.setFileType(FTPClient.BINARY_FILE_TYPE);//设置文件类型
            ftp.changeWorkingDirectory(getPath(tableName));//修改操作空间
            ftp.storeFile(originFileName, input);//这里开始上传文件
            if (!FTPReply.isPositiveCompletion(reply)) {
                ftp.disconnect();
                System.out.println("连接失败");
                return success;
            }
            System.out.println("连接成功！");
            input.close();
            ftp.logout();
            success = true;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (ftp.isConnected()) {
                try {
                    ftp.disconnect();
                } catch (IOException ioe) {
                }
            }
        }
        return success;
    }
}

使用了其他博主写好的工具类=。=

在clickhouse数据库，主要是操作数据集；

构建sql语句；

package com.shi.dataopen.utils;


import org.springframework.stereotype.Component;
import java.sql.*;


@Component
public class CHConnectUtils {
    /*    * 在静态池中创建CH连接
     * */
    private static Connection connection = null;
    static {
        try {
            Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
            String url = "jdbc:clickhouse://192.168.248.128:8123/default";
            String user = "";
            String password = "";
            connection = DriverManager.getConnection(url,user,password);
        } catch (Exception e) {
            e.printStackTrace( );
        }
    }
    /*执行查询语句*/
    public void exeSelSql(String sql) throws Exception {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        ResultSetMetaData metaData = resultSet.getMetaData( );
        int columnCount = metaData.getColumnCount( );
        while (resultSet.next()){
            for (int i = 1; i <= columnCount; i++) {
                System.out.println(metaData.getColumnName(i)+":"+resultSet.getString(i));
            }
        }
    }
    /*执行插入,修改,删除语句*/
    public void exeInsertSql(String sql) throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
    }
    public void exeUseSql(String sql) throws SQLException {
        Statement statement = connection.createStatement();
        Boolean flag = statement.execute(sql);
        if (flag){
            System.out.println("执行成功");
        }else {
            System.out.println("执行失败");
        }
    }
    /*动态拼接建表语句*/
    public String CreateTablesql(String tablename,String dataName,String dataType,String partition,String OrderBy){
        String datanames[] = dataName.split(",");
        String dataTypes[] = dataType.split(",");
        System.out.println(datanames);
        System.out.println(dataTypes);
        String sql = "create table IF NOT EXISTS "+tablename+" (";
        if (datanames.length == dataTypes.length&&datanames!=null||dataTypes==null){
            for (int i = 0;i<datanames.length-1;i++){
                sql = sql+ datanames[i]+" "+dataTypes[i]+",";
            }
            sql = sql+datanames[datanames.length-1]+" "+dataTypes[datanames.length-1]+")engine=MergeTree() PARTITION BY "+partition+" ORDER BY "+OrderBy+";";
        }
        System.out.println(sql);
        return sql;
    }
    /*动态的插入语句*/
    public String InsertSql(String MergeTreeTableName,String tableName){
        String sql = "insert into "+MergeTreeTableName+" select * from "+tableName;
        return sql;
    }

    /*创建临时表语句*/
    public String InsertFileSql(String tableName,String dataName,String dataType){
        String datanames[] = dataName.split(",");
        String datatypes[] = dataType.split(",");
        String sql = "create table if not exists "+tableName+"(";
        if (datanames.length == datatypes.length){
            for (int i = 0;i<datanames.length-1;i++){
                sql = sql+ datanames[i]+" "+datatypes[i]+",";
            }
            sql = sql+datanames[datanames.length-1]+" "+datatypes[datanames.length-1]+") engine=File('CSV');";
        }
        return sql;
    }
}

控制器类：

    @PostMapping("/upload")
    public String upload(MultipartFile file,
                         @RequestParam("OrderBy") String OrderBy,
                         @RequestParam("dataName") String dataName,
                         @RequestParam("dataType")String dataType,
                         @RequestParam("partition") String partition,
                         HttpSession session,Model model) throws Exception {

        Date date = new Date();
        /*获取文件名称*/
        String fileName = file.getOriginalFilename();
        String type = file.getContentType();
        String ext ="."+ FilenameUtils.getExtension(fileName);
        opendatafile.setOldFileName(null);
        /*获取文件名后缀*/
        opendatafile.setExt(ext);
        /*获取文件大小*/
        Long size = file.getSize();
        opendatafile.setSize(size);
        /*文件类型*/
        opendatafile.setType(type);
        opendatafile.setUploadTime(new Date());
        /*数据表名*/
        String newFileName =  new SimpleDateFormat("yyyyMMddHHmmss").format(date)+"-"+fileName;
        System.out.println("------------------------"+newFileName);
        opendatafile.setFilename(newFileName);
        String filePath = "/home/ubuntu/Desktop/CHFiles/";
        opendatafile.setFilepath(filePath);
        User user = (User) session.getAttribute("user");
        opendatafile.setUid(user.getUid());
        fileService.insertFile(opendatafile);
        InputStream inputStream = file.getInputStream();

        /*建file引擎的表*/
        String tableName = newFileName.replace(".","_").replace("-","_");
        /*文件上传到ubuntu桌面文件夹上*/
        chConnectUtils.exeUseSql(chConnectUtils.InsertFileSql(tableName,dataName,dataType));
        Boolean flag = ftpUtils.uploadFile(tableName,"data.CSV",inputStream);//主要就是这里实现了ftp的文件上传

        /*建MergeTree引擎的表*/
        String MergeTreeTableName = tableName+"_CH";
        chConnectUtils.exeUseSql(chConnectUtils.CreateTablesql(MergeTreeTableName,dataName,dataType,partition,OrderBy));
        /*插入MergeTree引擎的表*/
        chConnectUtils.exeUseSql(chConnectUtils.InsertSql(MergeTreeTableName,tableName));
        return "redirect:/UserDetail";
    }

控制器就很乱…