全部产品
Search
文档中心

实时数仓Hologres:JDBC

更新时间:Apr 11, 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的负载均衡