每次我执行转换/操作时，Spark都会连接到数据库？

Question

我在Windows10上运行Spark 2.1.0。我连接到一个MySQL数据库，使用JDBC将数据放入spark。如下所示，每当我执行一个操作时，我都会收到以下警告，这让我想知道每个操作都是从数据库中检索数据的。

scala> val jdbcDF2 = spark.read.jdbc("jdbc:mysql:dbserver", "schema.tablename", connectionProperties)
Wed Mar 29 15:05:23 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
jdbcDF2: org.apache.spark.sql.DataFrame = [id: bigint, site: bigint ... 15 more fields]

scala> jdbcDF2.count
Wed Mar 29 15:09:09 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

如果是这样的话，有没有一种方法可以将数据保存在spark本地对象中，比如DataFrame，这样它就不必一直连接到数据库了？

我尝试使用cache the table，它成功地运行了，但是我无法在表上使用Spark-SQL

scala> jdbcDF2.cache()
res6: jdbcDF2.type = [id: bigint, site: bigint ... 15 more fields]
scala> val unique = sql("SELECT DISTINCT site FROM jdbcDF2")
org.apache.spark.sql.AnalysisException: Table or view not found: jdbcDF2;

Answer 1

直接在DataFrame上使用缓存后，可以执行查询

val unique = jdbcDF2.selectExpr("count(distinct site)")

或

val unique = jdbcDF2.select("site").distinct.count

或者从DataFrame创建临时视图并通过sqlContext访问它

jdbcDF2.createOrReplaceTempView("jdbcDF2")
val unique = sql("SELECT DISTINCT site FROM jdbcDF2")

Answer 2

您缓存DataFrame以供以后重用，并且不在每个Spark操作(collect、count、first，...)时查询数据库，这是正确的。

但是要使用SQL查询您的DataFrame，首先您必须这样做：

jdbcDF2.createOrReplaceTempView("my_table")

然后：

sql("SELECT DISTINCT site FROM my_table")