spark代码连接hive_SparkSQL连接Hive

1.将$HIVE_HOME/conf/hive-site.xml文件复制一份到$SPARK_HOME/conf/hive-site.xml

cp $HIVE_HOME/conf/hive-site.xml $SPARK_HOME/conf

2.直接启动spark-shell就能帮我们自动连接

./spark-shell --master local[2] --jars /usr/local/jar/mysql-connector-java-5.1.47.jar # --jars:是指定jar包

3.直接启动spark-shell就能帮我们自动连接

./spark-sql --master local[2] --jars /usr/local/jar/mysql-connector-java-5.1.47.jar --driver-class-path /usr/local/jar/mysql-connector-java-5.1.47.jar

4.我们可以启动一个启动thriftserver服务器server,7*24一直running

cd $SPARK_HOME/sbin

./start-thriftserver.sh --master local --jars /usr/local/jar/mysql-connector-java-5.1.47.jar # 启动默认监听端口10000

5.通过内置了一个客户端工具连接

cd $SPARK_HOME/bin/beeline

./beeline -u jdbc:hive2://192.168.104.94:10000

6.也可以使用代码连接

package com.imooc.bigdata.chapter06

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}

object JDBCClientApp {

def main(args: Array[String]): Unit = {

// 加载驱动

Class.forName("org.apache.hive.jdbc.HiveDriver")

val conn: Connection = DriverManager.getConnection("jdbc:hive2://192.168.104.94:10000")

val pstmt: PreparedStatement = conn.prepareStatement("show tables")

val rs: ResultSet = pstmt.executeQuery()

while(rs.next()) {

println(rs.getObject(1) + " : " + rs.getObject(2))

}

}

}

package com.imooc.bigdata.chapter06

import java.util.Properties

import com.typesafe.config.ConfigFactory

import org.apache.spark.sql.{DataFrame, SparkSession}

object HiveSourceApp {

def main(args: Array[String]): Unit = {

// 如果你想使用Spark来访问Hive的时候，一定需要开启Hive的支持

val spark: SparkSession = SparkSession.builder().master("local").appName("HiveSourceApp")

.enableHiveSupport() //切记：一定要开启

.getOrCreate()

// 走的就是连接 default数据库中的pk表，如果你是其他数据库的，那么也采用类似的写法即可

//spark.table("default.pk").show()

// input(Hive/MySQL/JSON...) ==> 处理 ==> output (Hive)

import spark.implicits._

val config = ConfigFactory.load()

val url = config.getString("db.default.url")

val user = config.getString("db.default.user")

val password = config.getString("db.default.password")

val driver = config.getString("db.default.driver")

val database = config.getString("db.default.database")

val table = config.getString("db.default.table")

val sinkTable = config.getString("db.default.sink.table")

val connectionProperties = new Properties()

connectionProperties.put("user", user)

connectionProperties.put("password", password)

val jdbcDF: DataFrame = spark.read

.jdbc(url, s"$database.$table", connectionProperties).filter($"cnt" > 100)

//jdbcDF.show()

jdbcDF.write.saveAsTable("browser_stat_hive")

jdbcDF.write.insertInto("browser_stat_hive_1")

spark.stop()

}

}