全部產品
Search
文件中心

Hologres:JDBC

更新時間:Jun 30, 2024

Hologres為您提供與PostgreSQL完全相容的串連介面(JDBC/ODBC),可通過該介面將SQL用戶端工具串連至Hologres,方便進行資料開發。本文將介紹如何使用JDBC串連Hologres進行資料開發。

注意事項

  • 通過JDBC寫入資料至Hologres需要使用42.3.2及以上的Postgres JDBC Driver。

  • 使用JDBC串連Hologres後,如果您需要測試寫入資料的效能,建議使用VPC網路。公用網路由於自身原因,無法達到效能測試的目標。

  • Hologres當前不支援在一個事務中多次寫入,因此需要設定autoCommittrue(JDBC的autoCommit預設為true),不要顯式的在代碼裡面進行Commit操作。如果出現ERROR: INSERT in transaction is not supported now 報錯,則需要設定autoCommittrue,如下所示。

    Connection conn = DriverManager.getConnection(url, user, password); 
    conn.setAutoCommit(true);

通過JDBC串連Hologres

通過JDBC串連Hologres的步驟如下。

  1. 下載配置。

    用戶端工具預設整合了PostgreSQL資料庫的驅動,直接使用工具內建的驅動即可。如果未整合驅動,需要下載並安裝。

    使用PostgreSQL驅動,請至官網下載PostgreSQL JDBC Driver,需要使用42.3.2以上版本的JDBC驅動,建議您使用最新版本的JDBC。下載成功後需要至Maven倉庫配置樣本如下。

    <dependencies>
            <dependency>
                <groupId>org.postgresql</groupId>
                <artifactId>postgresql</artifactId>
                <version>42.3.2</version>                            
            </dependency>
    </dependencies>
  2. 串連Hologres。

    • 使用以下串連串聯接Hologres。

      jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?user={ACCESS_ID}&password={ACCESS_KEY}
    • 參數說明如下。

      參數

      描述

      ENDPOINT

      Hologres執行個體的網路地址。

      進入Hologres管理主控台執行個體詳情頁擷取網路地址。

      PORT

      Hologres執行個體的連接埠。

      進入Hologres管理主控台執行個體詳情頁擷取連接埠。

      DBNAME

      Hologres建立的資料庫名稱。

      ACCESS_ID

      當前帳號的使用者名稱。

      建議您使用環境變數的方式調用使用者名稱和密碼,降低密碼泄露風險。具體操作見下文樣本。

      ACCESS_KEY

      當前帳號的登入密碼。

      建議您使用環境變數的方式調用使用者名稱和密碼,降低密碼泄露風險。具體操作見下文樣本。

    • 串連Hologres建議如下。

      • 建議JDBC的URL加上ApplicationName參數,此參數為選擇性參數。使用該方式串連資料庫時有助於您在慢Query清單中根據ApplicationName快速定位您的發起請求的應用,串連串如下。

        jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}
      • 建議在JDBC URL中添加reWriteBatchedInserts=true配置,系統會以批量寫入的方式提交作業,效能更好,串連串如下。

        jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true
      • 建議使用Prepared Statement方式來進行資料讀取和寫入,以實現更高的吞吐。

      • Hologres中開啟外部表格自動載入後,MaxCompute的Project名稱將被自動對應為Hologres中的同名Schema,如您需要直接查詢該Schema下的外部表格,建議在JDBC URL裡添加currentSchema參數,以便於映射到對應的MaxCompute Project。串連串樣本如下:

        jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?currentSchema={SCHEMA_NAME}&user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}
      • 建議使用環境變數的方式調用使用者名稱和密碼,降低密碼泄露風險。以Linux系統為例,可以在bash_profile檔案中增加以下命令來配置環境變數。

        export ALIBABA_CLOUD_USER=<ACCESS_ID>
        export ALIBABA_CLOUD_PASSWORD=<ACCESS_KEY>
    • 串連樣本如下。

      public class HologresTest {
      
          private void jdbcExample() throws SQLException {
              String user= System.getenv("ALIBABA_CLOUD_USER");
              String password = System.getenv("ALIBABA_CLOUD_PASSWORD");
              String url = String.format("jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?currentSchema={SCHEMA_NAME}&user=%s&password=%s", user, password);
              try (Connection conn = DriverManager.getConnection(url)) {
                  try (Statement st = conn.createStatement()) {
                      String sql = "SELECT * FROM table where xxx limit 100";
                      try (ResultSet rs = st.executeQuery(sql)) {
                          while (rs.next()) {
                              //擷取資料表中第一列資料值
                              String c1 = rs.getString(1);
                          }
                      }
                  }
              }
          }
      
          private void jdbcPreparedStmtExample() throws SQLException {
              String user= System.getenv("ALIBABA_CLOUD_USER");
              String password = System.getenv("ALIBABA_CLOUD_PASSWORD");
              String url = String.format("jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?currentSchema={SCHEMA_NAME}&user=%s&password=%s", user, password);
              try (Connection conn = DriverManager.getConnection(url)) {
                  String sql = "insert into test values" +
                          "(?, ?), " +
                          "(?, ?), " +
                          "(?, ?), " +
                          "(?, ?), " +
                          "(?, ?), " +
                          "(?, ?), " +
                          "(?, ?), " +
                          "(?, ?), " +
                          "(?, ?), " +
                          "(?, ?)";
                  try (PreparedStatement st = conn.prepareStatement(sql)) {
                      for (int i = 0; i < 10; ++i) {
                          for (int j = 0; j < 2 * 10; ++j) {
                              st.setString(j + 1, UUID.randomUUID().toString());
                          }
                          System.out.println("affected row => " + st.executeUpdate());
                      }
                  }
              }
          }
       }

使用JDBC開發

JDBC串連Hologres成功後,您可以使用標準的開發語句來開發Hologres,包括寫入和讀取資料。

  • 寫入資料

    您可以通過JDBC的Statement或Prepared Statement模式寫入資料。通常情況下,推薦您使用Prepared Statment模式,並且設定批量寫入資料的條數為256的倍數(建議最低設定為256條)。因為使用Prepared Statment模式時,服務端會緩衝SQL編譯的結果,從而降低寫入資料的延時,並提高輸送量。

    使用Prepared Statement模式寫入資料的樣本如下。

    • 使用Prepared Statement模式批量寫入資料,命令語句如下。

      /*通過Prepared Statement模式批量寫入資料*/
      /*該樣本中,設定的批量寫入大小batchSize為256*/
      private static void WriteBatchWithPreparedStatement(Connection conn) throws Exception {
          try (PreparedStatement stmt = conn.prepareStatement("insert into test_tb values (?,?,?,?)")) {
              int batchSize = 256;
              for (int i = 0; i < batchSize; ++i) {
                  stmt.setInt( 1, 1000 + i);
                  stmt.setString( 2, "1");
                  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                  Date parsedDate = dateFormat.parse("1990-11-11 00:00:00");
                  stmt.setTimestamp( 3, new java.sql.Timestamp(parsedDate.getTime()));
                  stmt.setDouble( 4 , 0.1 );
                  stmt.addBatch();
              }
              stmt.executeBatch();
          }
      }
    • 使用Prepared Statement模式寫入資料的同時,添加Postgres的INSERT ON CONFLICT功能,實現寫入時更新覆蓋原有資料。命令語句如下。

      說明

      使用INSERT ON CONFLICT語句時目標表必須定義主鍵。

      private static void InsertOverwrite(Connection conn) throws Exception {
          try (PreparedStatement stmt = conn.prepareStatement("insert into test_tb values (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?) on conflict(pk) do update set f1 = excluded.f1, f2 = excluded.f2, f3 = excluded.f3")) {
              int batchSize = 6;
              for (int i = 0; i < batchSize; ++i) {
                  stmt.setInt(i * 4 + 1, i);
                  stmt.setString(i * 4 + 2, "1");
                  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                  Date parsedDate = dateFormat.parse("1990-11-11 00:00:00");
                  stmt.setTimestamp(i * 4 + 3, new java.sql.Timestamp(parsedDate.getTime()));
                  stmt.setDouble(i * 4 + 4, 0.1);
              }
              int affected_rows = stmt.executeUpdate();
              System.out.println("affected rows => " + affected_rows);
          }
      }
  • 資料查詢

    資料寫入完成之後,可以對資料進行查詢。您也可以根據業務需求查詢已有表的資料。

Druid串連池配置

  • 注意事項

    • 建議配置keepAlive=true,可以複用串連和避免短連結。

    • 您需使用Druid 1.1.12以上的版本串連Hologres。

    • Druid 1.2.12至1.2.21版本存在connectTimeoutsocketTimeout參數未指定配置的情況下預設為10秒的問題,如果您遇到類似問題,請升級Druid版本,詳情請參見Druid

  • 配置Druid串連池

    說明

    其中initialSizeminIdlemaxActive請根據執行個體大小和實際業務進行設定。

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
      <!-- jdbc_url是Hologres執行個體的串連地址URL,可以在控制台的執行個體配置頁面擷取串連URL。-->
      <property name="url" value="${jdbc_url}" />
      <!-- jdbc_user是Hologres執行個體中的使用者賬戶的Access ID。 -->
      <property name="username" value="${jdbc_user}" />
      <!-- jdbc_password是Hologres執行個體中使用者帳號對應的Access Secret。 -->
      <property name="password" value="${jdbc_password}" />
      <!-- 配置初始化串連池大小、最小串連數、最大串連數。 -->
      <property name="initialSize" value="5" />
      <property name="minIdle" value="10" />
      <property name="maxActive" value="20" />
      <!-- 配置擷取串連等待逾時的時間。 -->
      <property name="maxWait" value="60000" />
      <!-- 配置間隔多久進行一次檢測,檢測需要關閉的空閑串連,單位毫秒。 -->
      <property name="timeBetweenEvictionRunsMillis" value="2000" />
      <!-- 配置一個串連在串連池中的最小存留時間,單位毫秒。 -->
      <property name="minEvictableIdleTimeMillis" value="600000" />
      <property name="maxEvictableIdleTimeMillis" value="900000" />
      <property name="validationQuery" value="select 1" />
      <property name="testWhileIdle" value="true" />
      <!-- 配置從串連池擷取串連時,是否檢查串連有效性,true每次都檢查;false不檢查。 -->
      <property name="testOnBorrow" value="false" />
      <!-- 配置向串連池歸還串連時,是否檢查串連有效性,true每次都檢查;false不檢查。 -->
      <property name="testOnReturn" value="false" />
      <property name="keepAlive" value="true" />
      <property name="phyMaxUseCount" value="100000" />
      <!-- 配置監控統計攔截的filters。 -->
      <property name="filters" value="stat" />
    </bean>

效能調優實踐

使用JDBC時想要達到比較好的效能,有以下需要注意的事項。

  • 盡量使用VPC網路,避免使用公網,以避免公網帶來的網路開銷。

  • 通過JDBC驅動寫入資料時,JDBC URL中添加reWriteBatchedInserts=true配置,系統會以批量寫入的方式提交作業,效能更好。實踐證明攢批配置256的倍數(建議最低設定為256條)效果會更好。同時您也可以使用Hologres的Holo Client會自動攢批。

    jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true
  • 使用Prepared Statment模式,此模式下服務端會緩衝SQL編譯的結果,從而降低寫入資料的延時,並提高輸送量。

JDBC配置GUC

有時候需要在Session層級設定GUC參數,GUC參數詳情請參見GUC參數。推薦使用如下方式設定GUC參數,樣本中將Session層級的statement_timeout參數設定為12345毫秒,同時將Session層級的idle_in_transaction_session_timeout參數設定為12345毫秒。

import org.postgresql.PGProperty;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class gucdemo {
    public static void main(String[] args) {
        // 設定Hologres執行個體的串連地址
        String hostname = "hgpostcn-cn-xxxx-cn-hangzhou.hologres.aliyuncs.com";
        // 設定Hologres執行個體的串連連接埠
        String port = "80";
        // 設定待串連的資料庫名
        String dbname = "demo";
        String jdbcUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname;
        Properties properties = new Properties();
        // 設定串連資料庫的使用者名稱
        properties.setProperty("user", "xxxxx");
        //設定串連資料庫的密碼
        properties.setProperty("password", "xxxx");
        // 設定GUC
        PGProperty.OPTIONS.set(properties,"--statement_timeout=12345 --idle_in_transaction_session_timeout=12345");
        try {
            Class.forName("org.postgresql.Driver");
            Connection connection = DriverManager.getConnection(jdbcUrl, properties);
            PreparedStatement preparedStatement = connection.prepareStatement("show statement_timeout" );
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ResultSetMetaData rsmd = resultSet.getMetaData();
                int columnCount = rsmd.getColumnCount();
                Map map = new HashMap();
                for (int i = 0; i < columnCount; i++) {
                    map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
                }
                System.out.println(map);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}

基於JDBC的負載平衡

Hologres從 V1.3版本開始,支援在JDBC配置多個唯讀從執行個體以支援簡單的負載平衡,詳情請參見基於JDBC的負載平衡