全部產品
Search
文件中心

ApsaraDB for ClickHouse:通過JDBC方式串連ClickHouse

更新時間:Jun 30, 2024

本文通過程式碼範例說明如何通過JDBC方式串連ClickHouse進行應用開發。

操作步驟

  1. 使用Eclipse或其他IDE工具建立Maven專案並引入ClickHouse驅動依賴包。

    <dependency>
        <groupId>org.lz4</groupId>
        <artifactId>lz4-java</artifactId>
        <version>1.8.0</version>
    </dependency>
    <dependency>
        <groupId>com.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.4.6</version>
    </dependency>
  2. 編寫應用程式代碼。

    以下代碼示範了如何使用JDBC驅動串連叢集和建立表以及寫入資料。使用過程需修改叢集串連串和連接埠參數。如果應用和ClickHouse執行個體在同一個VPC環境,請使用叢集VPC地址,如果不在相同的VPC環境則需使用叢集外網地址,如何擷取外網地址,請參見申請和釋放外網地址

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.sql.Timestamp;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    public class Main4 {
      private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
      private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);
    
      public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException, ParseException {
        String url = "your url";  //雲資料庫ClickHouse的VPC地址或外網地址
        String username = "your username";   //雲資料庫ClickHouse的帳號
        String password = "your password";  //雲資料庫ClickHouse的密碼
    
        String connectionStr = "jdbc:clickhouse://" + url + ":8123";
    
        try (Connection connection = DriverManager.getConnection(connectionStr, username, password);
             Statement stmt = connection.createStatement()) {
    
          {
            String createTableDDL = "create table test_table on cluster default " +
                "(id UInt32, " +
                "dt_str String, " +
                "dt_col DateTime) " +
                "engine=ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')" +
                "partition by toYYYYMM(dt_col)" +
                "order by (id)" +
                "primary key (id)" +
                "sample by (id)" +
                "settings index_granularity = 8192;";
            stmt.execute(createTableDDL);
            System.out.println("create local table done.");
          }
          {
            String createTableDDL = "create table test_dist on cluster default " +
                "as default.test_table " +
                "engine=Distributed(default, default, test_table, rand());";
            stmt.execute(createTableDDL);
            System.out.println("create distributed table done");
          }
    
          System.out.println("write 100000 rows...");
          long startTime = System.currentTimeMillis();
    
          // Write 10 batch
          for (int batch = 0; batch < 10; batch++) {
            StringBuilder sb = new StringBuilder();
    
            // Build one batch
            sb.append("insert into test_dist values(" + (batch * 10000) + ", '2020-02-19 16:00:00', '2020-02-19 16:00:00')");
            for (int row = 1; row < 10000; row++) {
              sb.append(", (" + (batch * 10000 + row) + ", '2020-02-19 16:00:00', '2020-02-19 16:00:00')");
            }
    
            // Write one batch: 10000 rows
            stmt.execute(sb.toString());
          }
    
          long endTime = System.currentTimeMillis();
          System.out.println("total time cost to write 10W rows: " + (endTime - startTime) + "ms");
    
          Thread.sleep(2 * 1000);
    
          System.out.println("Select count(id)...");
          try (ResultSet rs = stmt.executeQuery("select count(id) from test_dist");) {
            while (rs.next()) {
              int count = rs.getInt(1);
              System.out.println("id count: " + count);
            }
          }
    
          try (ResultSet rs = stmt.executeQuery("select id, dt_str, dt_col from test_dist limit 10");) {
            while (rs.next()) {
              int id = rs.getInt(1);
              String dateStr = rs.getString(2);
              Timestamp time = rs.getTimestamp(3);
    
              String defaultDate = SIMPLE_DATE_FORMAT.format(new Date(time.getTime()));
              System.out.println("id: " + id
                  + ", date_str:" + dateStr
                  + ", date_col:" + defaultDate);
            }
          }
        }
      }
    }