Spark JDBC连接ClickHouse读写操作

一、依赖

  	<!--    spark-->
    <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-core -->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-core_2.11</artifactId>
      <version>2.3.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-sql -->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-sql_2.11</artifactId>
      <version>2.3.4</version>
    </dependency>
    
    
    <!--    clickhouse-->
    <!-- https://mvnrepository.com/artifact/ru.yandex.clickhouse/clickhouse-jdbc -->
    <dependency>
      <groupId>ru.yandex.clickhouse</groupId>
      <artifactId>clickhouse-jdbc</artifactId>
      <version>0.2.4</version>
    </dependency>

二、读

现有表创建如下

create table ck_test.myOperator(
id String COMMENT '主键',
name String COMMENT '名称',
is_deleted Int32 COMMENT '是否删除 0:否 1:是',
gmt_created DateTime COMMENT '创建时间',
gmt_modified DateTime COMMENT '修改时间'
)ENGINE = Memory;

insert into ck_test.myOperator values(1,'马云',1,'2019-01-01 00:00:00','2019-01-01 00:00:00') ;

object spark_ClickHouse {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName("spark_ck")
      .master("local[*]")
      .getOrCreate()

    val ck_params_Map = Map[String,String](
      "driver"->"ru.yandex.clickhouse.ClickHouseDriver",
    "url"-> "jdbc:clickhouse://192.168.56.20:8123/ck_test",
      "dbtable"-> "myOperator"
    )
    
    spark.read.format("jdbc").options(ck_params_Map).load().show(false)
    spark.stop()
  }
}

二、写

object spark_ClickHouse {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName("spark_ck")
      .master("local[*]")
      .getOrCreate()

      //生成df
    val external_info = spark.createDataFrame(Seq(
      (2,"马化腾",0,"2019-01-01 00:00:00","2019-01-01 00:00:00"),
      (3,"周杰伦",0,"2019-01-01 00:00:00","2019-01-01 00:00:00")
    )).toDF("id","name","is_deleted","gmt_created","gmt_modified")

      //写入参数
    val write_maps = Map[String,String](
      "batchsize"->"2000",
      "isolationLevel"->"NONE",
      "numPartitions"->"1"
    )
    val url = "jdbc:clickhouse://192.168.56.20:8123/ck_test"
    val dbtable = "myOperator"
    val pro = new Properties()
    pro.put("driver","ru.yandex.clickhouse.ClickHouseDriver")
    
      //jdbc方式写入
    external_info.write.mode(SaveMode.Append)
        .options(write_maps)
        .jdbc(url,dbtable,pro)
    spark.stop()
  }
}

再次使用jdbc读取的方式展示数据，发现数据已经以Append的方式增加进去