Spark Hive集成

• 修改hive-site.xml

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://CentOS:3306/hive?createDatabaseIfNotExist=true</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>root</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>root</value>
</property>

<!--开启MetaStore服务，用于Spark读取hive中的元数据-->
<property>
  <name>hive.metastore.uris</name>
  <value>thrift://CentOS:9083</value>
</property>

• 启动metastore服务

[root@train spark-2.4.5]# ./bin/hive --service metastore >/dev/null 2>&1 &

• 导入以下依赖

<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-sql_2.11</artifactId>
    <version>2.4.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.spark/spark-hive -->
<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-hive_2.11</artifactId>
    <version>2.4.5</version>
</dependency>

• 编写如下代码

//配置spark
    val spark = SparkSession.builder()
      .appName("Spark Hive Example")
      .master("local[*]")
      .config("hive.metastore.uris", "thrift://train:9083")
      .enableHiveSupport() //启动hive支持
      .getOrCreate()

    import org.apache.spark.sql.functions._
    spark.sql("show databases").show()
    spark.sql("use baizhi")
    val empDF = spark.sql("select * from t_employee")

    val w = Window.partitionBy("deptno")
      .orderBy("sal")


    empDF.withColumn("sal_rank",dense_rank() over(w)).show()



    spark.close()

-----+---------+----+-------------------+-------+-------+------+--------+
|empno| ename|      job| mgr|           hiredate|    sal|   comm|deptno|sal_rank|
+-----+------+---------+----+-------------------+-------+-------+------+--------+
| 7369| SMITH|    CLERK|7902|1980-12-17 00:00:00| 800.00|   null|    20|       1|
| 7876| ADAMS|    CLERK|7788|1987-05-23 00:00:00|1100.00|   null|    20|       2|
| 7788| SCOTT|  ANALYST|7566|1987-04-19 00:00:00|1500.00|   null|    20|       3|
| 7566| JONES|  MANAGER|7839|1981-04-02 00:00:00|2975.00|   null|    20|       4|
| 7902|  FORD|  ANALYST|7566|1981-12-03 00:00:00|3000.00|   null|    20|       5|
| 7934|MILLER|    CLERK|7782|1982-01-23 00:00:00|1300.00|   null|    10|       1|
| 7782| CLARK|  MANAGER|7839|1981-06-09 00:00:00|2450.00|   null|    10|       2|
| 7839|  KING|PRESIDENT|null|1981-11-17 00:00:00|5000.00|   null|    10|       3|
| 7900| JAMES|    CLERK|7698|1981-12-03 00:00:00| 950.00|   null|    30|       1|
| 7521|  WARD| SALESMAN|7698|1981-02-22 00:00:00|1250.00| 500.00|    30|       2|
| 7654|MARTIN| SALESMAN|7698|1981-09-28 00:00:00|1250.00|1400.00|    30|       2|
| 7844|TURNER| SALESMAN|7698|1981-09-08 00:00:00|1500.00|   0.00|    30|       3|
| 7499| ALLEN| SALESMAN|7698|1981-02-20 00:00:00|1600.00| 300.00|    30|       4|
| 7698| BLAKE|  MANAGER|7839|1981-05-01 00:00:00|2850.00|   null|    30|       5|
+-----+------+---------+----+-------------------+-------+-------+------+--------+