ApsaraDB for ClickHouse:Use JDBC to connect to an ApsaraDB for ClickHouse cluster

Procedure

1. Use an integrated development environment (IDE) tool such as Eclipse to create a Maven project and install the dependency that is provided by ApsaraDB for ClickHouse for JDBC drivers.

   <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. Write code for your application.

   The following sample code shows how to use a JDBC driver to connect to an ApsaraDB for ClickHouse cluster, create a table, and write data. Use the endpoint and port number of your own cluster. If your application and ApsaraDB for ClickHouse cluster are in the same virtual private cloud (VPC), use the VPC endpoint of the cluster. Otherwise, use the public endpoint of the cluster. For more information about how to apply for a public endpoint, see Apply for and release a public endpoint.

   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"; // The VPC endpoint or public endpoint of the ApsaraDB for ClickHouse cluster.
       String username = "your username"; // The database account that is used to log on to the database in the ApsaraDB for ClickHouse cluster.
       String password = "your password"; // The password that is used to log on to the database in the ApsaraDB for ClickHouse cluster.
   
       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);
           }
         }
       }
     }
   }