2021-02-19《spark将数据写入到clickhouse》

import org.apache.spark.sql.{SaveMode, SparkSession}
import java.util
import java.util.Properties

import org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions

object ckDemo {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("ckDemo").master("local[*]").enableHiveSupport().getOrCreate()
    import spark.implicits._

    val df = spark.sql("""select * from db.table""") // 从hive表中获取数据。
    df.show()

    val properties = new Properties()
    properties.put("driver", "ru.yandex.clickhouse.ClickHouseDriver")
    properties.put("batchsize","100000")
    properties.put("socket_timeout","300000")
    properties.put("numPartitions","8")
    properties.put("rewriteBatchedStatements","true")

    val url = "jdbc:clickhouse://ip:port/database"
    val table = "dws_2g"

    df.write.mode(SaveMode.Append).option(JDBCOptions.JDBC_BATCH_INSERT_SIZE, 100000).jdbc(url, table, properties)
  }
}

pom.xml文件关键性配置如下：

<properties>
    <scala.version>2.11.12</scala.version>
  </properties>

  <repositories>
    <repository>
      <id>scala-tools.org</id>
      <name>Scala-Tools Maven3 Repository</name>
      <url>http://scala-tools.org/repo-releases</url>
    </repository>
  </repositories>

  <pluginRepositories>
    <pluginRepository>
      <id>scala-tools.org</id>
      <name>Scala-Tools Maven3 Repository</name>
      <url>http://scala-tools.org/repo-releases</url>
    </pluginRepository>
  </pluginRepositories>

  <dependencies>
    <dependency>
      <groupId>org.scala-lang</groupId>
      <artifactId>scala-library</artifactId>
      <version>${scala.version}</version>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-core_2.11</artifactId>
      <version>2.3.3</version>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-sql_2.11</artifactId>
      <version>2.3.3</version>
    </dependency>
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-hive_2.11</artifactId>
      <version>2.3.3</version>
      <!--<scope>provided</scope>-->
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.16</version>
    </dependency>
    <dependency>
      <groupId>ru.yandex.clickhouse</groupId>
      <artifactId>clickhouse-jdbc</artifactId>
      <version>0.2.6</version>
    </dependency>
  </dependencies>

另外，如果创建的项目没有resouces，需要在build里面构建

<build>
    <resources>
      <resource>
        <directory>${project.basedir}/src/main/resources</directory>
      </resource>
    </resources>
</build>

注意: resources 中需要将相关的hive 文件添加进去（默认情况下是本地的）：

image.png