ClickHouse（二十三）：Java Spark读写ClickHouse API

 进入正文前，感谢宝子们订阅专题、点赞、评论、收藏！关注IT贫道，获取高质量博客内容！

🏡个人主页：含各种IT体系技术，IT贫道_Apache Doris,大数据OLAP体系技术栈,Kerberos安全认证-CSDN博客

📌订阅：拥抱独家专题，你的订阅将点燃我的创作热情！

👍点赞：赞同优秀创作，你的点赞是对我创作最大的认可！

⭐️ 收藏：收藏原创博文，让我们一起打造IT界的荣耀与辉煌！

✏️评论：留下心声墨迹，你的评论将是我努力改进的方向！

---

目录

​​​​​​1. Java 读写ClickHouse API

2. Spark 写入 ClickHouse API

---

​​​​​​1. Java 读写ClickHouse API

Java读取ClickHouse中的数据API 。

1） 首先需要加入maven依赖

<!-- 连接ClickHouse需要驱动包-->
<dependency><groupId>ru.yandex.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.2.4</version>
</dependency>

2) Java 读取ClickHouse单节点表数据

ClickHouseProperties props = new ClickHouseProperties();
props.setUser("default");
props.setPassword("");
BalancedClickhouseDataSource dataSource = new BalancedClickhouseDataSource("jdbc:clickhouse://node1:8123/default", props);
ClickHouseConnection conn = dataSource.getConnection();
ClickHouseStatement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select id,name,age from test");
while(rs.next()){int id = rs.getInt("id");String name = rs.getString("name");int age = rs.getInt("age");System.out.println("id = "+id+",name = "+name +",age = "+age);
}

3. Java 读取ClickHouse集群表数据

ClickHouseProperties props = new ClickHouseProperties();
props.setUser("default");
props.setPassword("");
BalancedClickhouseDataSource dataSource = new BalancedClickhouseDataSource("jdbc:clickhouse://node1:8123,node2:8123,node3:8123/default", props);
ClickHouseConnection conn = dataSource.getConnection();
ClickHouseStatement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select id,name from t_cluster");
while(rs.next()){int id = rs.getInt("id");String name = rs.getString("name");System.out.println("id = "+id+",name = "+name );
}

4) Java向ClickHouse 表中写入数据。

# API 操作：ClickHouseProperties props = new ClickHouseProperties();
props.setUser("default");
props.setPassword("");
BalancedClickhouseDataSource dataSource = new BalancedClickhouseDataSource("jdbc:clickhouse://node1:8123/default", props);
ClickHouseConnection conn = dataSource.getConnection();
ClickHouseStatement statement = conn.createStatement();
statement.execute("insert into test values (100,'王五',30)");//可以拼接批量插入多条#查询default库下 test表 数据：node1 :) select * from test;┌──id─┬─name─┬─age─┐│ 100 │ 王五   │  30 │└─────┴──────┴─────┘┌─id─┬─name─┬─age─┐│  1  │ 张三  │  18 ││  2  │ 李四  │  19 │└────┴──────┴─────┘

2. Spark 写入 ClickHouse API

SparkCore写入ClickHouse，可以直接采用写入方式。下面案例是使用SparkSQL将结果存入ClickHouse对应的表中。在ClickHouse中需要预先创建好对应的结果表。

1) 导入依赖

<!-- 连接ClickHouse需要驱动包-->
<dependency><groupId>ru.yandex.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.2.4</version><!-- 去除与Spark 冲突的包 --><exclusions><exclusion><groupId>com.fasterxml.jackson.core</groupId><artifactId>jackson-databind</artifactId></exclusion><exclusion><groupId>net.jpountz.lz4</groupId><artifactId>lz4</artifactId></exclusion>
</exclusions>
</dependency><!-- Spark-core -->
<dependency><groupId>org.apache.spark</groupId><artifactId>spark-core_2.11</artifactId><version>2.3.1</version>
</dependency>
<!-- SparkSQL -->
<dependency><groupId>org.apache.spark</groupId><artifactId>spark-sql_2.11</artifactId><version>2.3.1</version>
</dependency>
<!-- SparkSQL  ON  Hive-->
<dependency><groupId>org.apache.spark</groupId><artifactId>spark-hive_2.11</artifactId><version>2.3.1</version>
</dependency>

2) 代码编写：

val session: SparkSession = SparkSession.builder().master("local").appName("test").getOrCreate()
val jsonList = List[String]("{\"id\":1,\"name\":\"张三\",\"age\":18}","{\"id\":2,\"name\":\"李四\",\"age\":19}","{\"id\":3,\"name\":\"王五\",\"age\":20}"
)//将jsonList数据转换成DataSet
import session.implicits._
val ds: Dataset[String] = jsonList.toDS()val df: DataFrame = session.read.json(ds)
df.show()//将结果写往ClickHouse
val url = "jdbc:clickhouse://node1:8123/default"
val table = "test"
val properties = new Properties()
properties.put("driver", "ru.yandex.clickhouse.ClickHouseDriver")
properties.put("user", "default")
properties.put("password", "")
properties.put("socket_timeout", "300000")
df.write.mode(SaveMode.Append).option(JDBCOptions.JDBC_BATCH_INSERT_SIZE, 100000).jdbc(url, table, properties)

---

👨‍💻如需博文中的资料请私信博主。

---