最近更新时间:2023.11.30 16:24:05
首次发布时间:2022.05.09 10:22:34
本文介绍如何通过 JDBC 方式连接并访问 ByteHouse 云数仓。
请访问 github 项目主页,获取ByteHouse JDBC 驱动最新的文档和发布版本。
支持 Java 1.8.0_261 或更高版本 (需要 TLSv1.3 支持)
通过 github repo 获取最新编译jar文件。
只需将编译后的jar文件添加到您的项目中,或者使用您选择的依赖项管理工具将此项目添加为依赖项。然后,您可以导入并使用Java程序中的类。
implementation "com.bytedance.bytehouse:driver-java:1.1.24"
<dependency> <groupId>com.bytedance.bytehouse</groupId> <artifactId>driver-java</artifactId> <version>1.1.24</version> </dependency>
Implements | Class |
---|---|
java.sql.Driver | com.bytedance.bytehouse.jdbc.ByteHouseDriver |
请参考通过驱动连接到 ByteHouse,了解如何通过API Token或用户名+密码的方式连接到ByteHouse。
可参考下面代码使用JDBC进行数据插入的操作,注意替换连接语句中的Host
、Port
和 API_KEY
字段。
import com.bytedance.bytehouse.jdbc.ByteHouseDriver; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; public class SimpleQuery { public static void main(String[] args) throws Exception { String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?user=bytehouse&password={API_KEY}"); //If user wishes to specify the database in url // String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?secure=true&user=bytehouse&password={API_KEY}&database={DATABASE}"); Properties properties = new Properties(); //If user wishes to specify the vw to be used // properties.setProperty("vw", {VIRTUAL_WAREHOUSE}) properties.setProperty("secure", "true"); Driver driver = new ByteHouseDriver(); try (Connection connection = driver.connect(url, properties)) { createDatabase(connection); createTable(connection); insertTable(connection); insertBatch(connection); } catch (SQLException ex) { ex.printStackTrace(); } } public static void createDatabase(Connection connection) { try (Statement stmt = connection.createStatement()) { stmt.execute("CREATE DATABASE IF NOT EXISTS inventory"); } catch (SQLException ex) { ex.printStackTrace(); } } public static void createTable(Connection connection) { try (Statement stmt = connection.createStatement()) { stmt.execute( "CREATE TABLE IF NOT EXISTS inventory.orders\n" + "(" + " OrderID String," + " OrderName String," + " OrderPriority Int8" + ")" + " engine = CnchMergeTree()" + " partition by OrderID" + " order by OrderID" ); } catch (SQLException ex) { ex.printStackTrace(); } } public static void insertTable(Connection connection) { try (Statement stmt = connection.createStatement()) { stmt.executeUpdate( "INSERT INTO inventory.orders VALUES ('54895','Apple',12)" ); } catch (SQLException ex) { ex.printStackTrace(); } } public static void insertBatch(Connection connection) { String insertQuery = "INSERT INTO inventory.orders (OrderID, OrderName, OrderPriority) VALUES (?,'Apple',?)"; try (PreparedStatement pstmt = connection.prepareStatement(insertQuery)) { int insertBatchSize = 10; for (int i = 0; i < insertBatchSize; i++) { pstmt.setString(1, "ID" + i); pstmt.setInt(2, i); pstmt.addBatch(); } pstmt.executeBatch(); } catch (SQLException ex) { ex.printStackTrace(); } } }
可参考下面代码使用JDBC进行数据查询的操作,注意替换连接语句中的Host
、Port
和 API_KEY
字段。
import com.bytedance.bytehouse.jdbc.ByteHouseDriver; import java.sql.Connection; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; public class DQL { public static void main(String[] args) throws Exception { String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?api_key={API_KEY}"); //If user wishes to specify the database in url // String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?secure=true&user=bytehouse&password={API_KEY}&database={DATABASE}"); Properties properties = new Properties(); //If user wishes to specify the vw to be used // properties.setProperty("vw", {VIRTUAL_WAREHOUSE}) properties.setProperty("secure", "true"); Driver driver = new ByteHouseDriver(); try (Connection connection = driver.connect(url, properties)) { selectTable(connection); } catch (SQLException ex) { ex.printStackTrace(); } } public static void selectTable(Connection connection) { try (Statement stmt = connection.createStatement()) { ResultSet rs = stmt.executeQuery("SELECT * FROM inventory.orders"); ResultSetMetaData rsmd = rs.getMetaData(); int columnsNumber = rsmd.getColumnCount(); while (rs.next()) { for (int i = 1; i <= columnsNumber; i++) { if (i > 1) System.out.print(", "); String columnValue = rs.getString(i); System.out.print(columnValue); } System.out.println(); } } catch (SQLException ex) { ex.printStackTrace(); } } }
当前 ByteHouse JDBC Driver 已经开源,详细使用方式和参数说明可以参考 GitHub。
下面举例介绍如何通过 ByteHouse JDBC 驱动程序与 Hikari CP 连接。
首先,我们需要添加以下基本依赖项:
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> <exclusions> <exclusion> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> </exclusion> <exclusion> <groupId>org.slf4j</groupId> <artifactId>org.slf4j.api</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>com.bytedance.bytehouse</groupId> <artifactId>driver-java</artifactId> <version>1.1.24</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> </dependencies>
spring-boot-starter-jdbc
使用 JDBC 功能。 在生产环境中,我们还可以使用 spring-boot-starter-data-jpa
来使用 ORM 框架。spring-boot-starter-web
为网络主机提供web服务。driver-java
ByteHouse JDBC 驱动程序。spring-boot-starter-actuator
Hikari 指标(可选)。@RequestMapping("/query") public List<Long> numbers() { List<Long> ans = new ArrayList<>(10); try (Connection connection = bhSource.getConnection(); PreparedStatement pstmt = connection.prepareStatement("select count(*), toDate(start_time)\n" + "from bytehouse.query_history\n" + "group by toDate(start_time) limit 5;") ) { ResultSet rs = pstmt.executeQuery(); while (rs.next()) { ans.add(rs.getLong(1)); } } catch (Exception e) { log.error("Error hits when executing", e); } return ans; }
为了启用Hikari CP,我们需要让 Spring 引导获取Hikari Data Source的实例。以下是定义方式:
@Bean public DataSource getDataSource() { HikariConfig config = new HikariConfig(); //standard Hikari configs config.setJdbcUrl("jdbc:bytehouse://{HOST}:{PORT}"); /*two different ways to set username First way: config.setUsername("<ACCOUNT_ID>::<USERNAME>"); config.setPassword("<PASSWORD>"); Second way: config.setUsername("bytehouse"); config.setPassword("<API_KEY>"); */ config.setMaximumPoolSize(3); config.setMaximumPoolSize(5); config.setMinimumIdle(60000); config.setPoolName("BH-Pool"); //additional driver property goes here config.addDataSourceProperty("secure", "true"); return new HikariDataSource(config); }
HikariConfig
允许传递其他非jdbc属性,例如secure:true
。
可以使用一些与池相关的配置,例如max_pool_size
、min_pool_ssize
、min_idle_timeout
。我们可以引用com.zaxxer.hikari.HikariConfig.class
来查看完整的配置列表。
请注意,这里我们将pool的名称定义为BH-Pool
。
@SpringBootApplication public class ConnectionPoolExample{ public static void main(String[] args) { SpringApplication.run(ConnectionPoolExample.class, args); } }
一旦我们在本地运行项目,就可以通过 localhost:8080/query 访问。
在项目运行期间,我们可以查看到到主程序中的日志:
2023-09-04 10:55:39.750 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool : BH-Pool - Added connection com.bytedance.bytehouse.jdbc.ByteHouseConnection@34783ae9 2023-09-04 10:55:40.033 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool : BH-Pool - Added connection com.bytedance.bytehouse.jdbc.ByteHouseConnection@2acef7 2023-09-04 10:55:40.264 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool : BH-Pool - Added connection com.bytedance.bytehouse.jdbc.ByteHouseConnection@4a0a3261 2023-09-04 10:55:40.571 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool : BH-Pool - Added connection com.bytedance.bytehouse.jdbc.ByteHouseConnection@55d992e1 2023-09-04 10:55:40.571 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool : BH-Pool - After adding stats (total=5, active=0, idle=5, waiting=0) 2023-09-04 10:56:09.544 DEBUG 58183 --- [ool housekeeper] com.zaxxer.hikari.pool.HikariPool : BH-Pool - Pool stats (total=5, active=0, idle=5, waiting=0) 2023-09-04 10:56:09.548 DEBUG 58183 --- [ool housekeeper] com.zaxxer.hikari.pool.HikariPool : BH-Pool - Fill pool skipped, pool is at sufficient level. 2023-09-04 10:56:39.551 DEBUG 58183 --- [ool housekeeper] com.zaxxer.hikari.pool.HikariPool : BH-Pool - Pool stats (total=5, active=0, idle=5, waiting=0) 2023-09-04 10:56:39.552 DEBUG 58183 --- [ool housekeeper] com.zaxxer.hikari.pool.HikariPool : BH-Pool - Fill pool skipped, pool is at sufficient level.
这表示连接池已准备就绪,并且每30秒打印一次池统计信息。
现在我们执行命令,例如:
time for i in {1..10000}; do curl -s http://localhost:8080/query & done;
上面的命令将向 ByteHouse 发送 10,000 个并发查询。
执行之后,我们可以使用以下命令来查看连接情况:
curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.active
输出示例如下:
{"name":"hikaricp.connections.active","description":"Active connections","baseUnit":null,"measurements":[{"statistic":"VALUE","value":5.0}],"availableTags":[{"tag":"pool","values":["BH-Pool"]}]}%
我们可以看到连接限制为 5
,这个值是在 max-pool-size
属性中定义的。