1. /**
    2.  * @author itwen
    3.  * @Description
    4.  * @create 2021-08-05 17:34
    5.  */
    6. @SpringBootTest
    7. public class Instl {
    9.     @Autowired
    10.     RatingMapper mapper;
    12.     String path = "D:\\ratings.csv";
    14.     /**
    15.      * 初始化数据集
    16.      *
    17.      * @return 数据集
    18.      * @throws IOException
    19.      */
    20.     List<Rating> initdata() throws IOException {
    21.         BufferedReader read = new BufferedReader(new FileReader(path));
    22.         Rating rating;
    23.         List<Rating> ratings = new ArrayList<>();
    24.         boolean sign = false;
    25.         for (int i = 0; i < 300000; i++) {
    26.             String line = read.readLine();
    27.             StringTokenizer st = new StringTokenizer(line, ",");
    28.             if (st.hasMoreTokens() && sign) {
    29.                 rating = new Rating();
    30.                 rating.setUserId(Integer.valueOf(st.nextToken().trim()));
    31.                 rating.setMovieId(Integer.valueOf(st.nextToken().trim()));
    32.                 rating.setRating(Double.valueOf(st.nextToken().trim()));
    33.                 rating.setTime(Integer.valueOf(st.nextToken().trim()));
    34.                 ratings.add(rating);
    35.             } else {
    36.                 sign = true;
    37.             }
    38.         }
    39.         read.close();
    40.         return ratings;
    41.     }
    43.     /**
    44.      * 单机
    45.      * 采用mybatis方式Batch插入10W条 耗时=182662毫秒
    46.      *
    47.      * @throws IOException
    48.      */
    49.     @Test
    50.     void inserUrlLog() throws IOException {
    51.         long startTime = System.currentTimeMillis();
    52.         mapper.insertUrlLog(initdata());
    53.         long entTime = System.currentTimeMillis();
    54.         System.out.println("当前程序耗时：" + (entTime - startTime) + "ms");
    55.     }
    57.     /**
    58.      * 单机
    59.      * 采用官方http-JDBC方式Batch插入10W条 耗时=1026毫秒
    60.      * 采用官方http-JDBC方式Batch插入30W条 耗时=5217毫秒
    61.      * 应注意分区数，分区越多速度越慢
    62.      * 另外还有第三方优化过的http、tcp的JDBC驱动
    63.      *
    64.      * @throws IOException
    65.      */
    66.     @Test
    67.     void inserCkJDBC() throws IOException, SQLException {
    68.         List<Rating> initdata = initdata();
    70.         String url = "jdbc:clickhouse://47.115.29.129:8123/test";
    71.         ClickHouseProperties properties = new ClickHouseProperties();
    72.         properties.setUser("ckadmin");
    73.         properties.setPassword("zjzl@2021");
    75.         ClickHouseDataSource source = new ClickHouseDataSource(url, properties);
    77.         long startTime = System.currentTimeMillis();
    78.         final String sql = "INSERT INTO test.ratings(userId,movieId,rating,initTime) VALUES(?,?,?,?)";
    79. //            ClickHouseConnection connection = clickHouseDataSource.getConnection();
    80. //            ClickHouseStatement statement = connection.createStatement();
    81. //            statement.write().sql("INSERT INTO default.my_table (a,b,c)");
    82.         Connection connection = source.getConnection();
    83.         connection.setAutoCommit(false);
    84.         PreparedStatement statement = connection.prepareStatement(sql);
    85.         for (Rating rating : initdata) {
    86.             statement.setLong(1, rating.getUserId());
    87.             statement.setLong(2, rating.getMovieId());
    88.             statement.setDouble(3, rating.getRating());
    89.             statement.setLong(4, rating.getTime());
    90.             statement.addBatch();
    91.         }
    92.         statement.executeBatch();
    93.         connection.commit();
    94.         statement.close();
    95.         connection.close();
    96.         long entTime = System.currentTimeMillis();
    97.         System.out.println("当前程序耗时：" + (entTime - startTime) + "ms");
    98.     }
    99. }