hive hql格式 hive source sql

Hive数据源实战

Spark SQL支持对Hive中存储的数据进行读写。操作Hive中的数据时，必须创建HiveContext，而不是SQLContext。HiveContext继承自SQLContext，但是增加了在Hive元数据库中查找表，以及用HiveQL语法编写SQL的功能。除了sql()方法，HiveContext还提供了hql()方法，从而用Hive语法来编译sql。
使用HiveContext，可以执行Hive的大部分功能，包括创建表、往表里导入数据以及用SQL语句查询表中的数据。查询出来的数据是一个Row数组。

特别说明：将hive-site.xml拷贝到spark/conf目录下，将mysql connector拷贝到spark/lib目录下
示例：

HiveContext sqlContext = new HiveContext(sc);
sqlContext.sql("CREATE TABLE IF NOT EXISTS students (name STRING, age INT)");
sqlContext.sql("LOAD DATA LOCAL INPATH '/usr/local/spark-study/resources/students.txt' INTO TABLE students");
Row[] teenagers = sqlContext.sql("SELECT name, age FROM students WHERE age<=18").collect();

将数据保存到表中

Spark SQL还允许将数据保存到Hive表中。调用DataFrame的saveAsTable命令，即可将DataFrame中的数据保存到Hive表中。与registerTempTable不同，saveAsTable是会将DataFrame中的数据物化到Hive表中的，而且还会在Hive元数据库中创建表的元数据。默认情况下，saveAsTable会创建一张Hive Managed Table，也就是说，数据的位置都是由元数据库中的信息控制的。当Managed Table被删除时，表中的数据也会一并被物理删除。registerTempTable只是注册一个临时的表，只要Spark Application重启或者停止了，那么表就没了。而saveAsTable创建的是物化的表，无论Spark Application重启或者停止，表都会一直存在。调用HiveContext.table()方法，还可以直接针对Hive中的表，创建一个DataFrame。

案例：查询分数大于80分的学生的完整信息
java版本

package cn.spark.study.sql;


import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.hive.HiveContext;


/**
 * Hive数据源:spark -SQL Hive数据源复杂综合案例实战
 * @author leizq120310
 *
 */


public class HiveDataSource {


	public static void main(String[] args) {
		// TODO Auto-generated method stub
		// 首先还是先创建SparkConf
		SparkConf conf = new SparkConf()
				.setAppName("HiveDataSource");
		JavaSparkContext sc = new JavaSparkContext(conf);
		// 创建HiveContext，注意，这里，它接收的是SparkContext作为参数，不是JavaSparkContext
		HiveContext hiveContext = new HiveContext(sc.sc());
		
		// 第一个功能，使用HiveContext的sql()方法，可以执行Hive中能够执行的HiveQL语句
		// 判断是否存在student_infos表，如果存在则删除
		hiveContext.sql("DROP TABLE IF EXISTS student_infos");
		// 判断student_infos表是否不存在，如果不存在，则创建该表
		hiveContext.sql("CREATE TABLE IF NOT EXISTS student_infos (name STRING, age INT)");
		// 将学生基本信息数据导入student_infos表
		hiveContext.sql("LOAD DATA "
				+ "LOCAL INPATH '/usr/local/spark-study/resources/student_infos.txt'"
				+ "INTO TABLE student_infos");
		// 用同样的方式给student_scores导入数据
		hiveContext.sql("DROP TABLE IF EXISTS student_scores");
		hiveContext.sql("CREATE TABLE IF NOT EXISTS student_scores (name STRING, score INT)");
		hiveContext.sql("LOAD DATA "
				+ "LOCAL INPATH '/usr/local/spark-study/resources/student_scores.txt'"
				+ "INTO TABLE student_scores");
		
		// 第二个功能，执行sql还可以返回DataFrame, 用于查询
		
		// 执行SQL查询，关联两张表，查询成绩大于80分的学生
		DataFrame goodStudentsDF = hiveContext.sql("SELECT si.name, si.age, ss.score "
				+ "FROM student_infos si "
				+ "JOIN student_scores ss ON si.name=ss.name "
				+ "WHERE ss.score>=80");
		// 第三个功能，可以将DataFrame中的数据，理论上来说，DataFrame对应的RDD的元素，是Row即可
		
		// 接着将dataframe中的数据保存到good_student_infos表中
		hiveContext.sql("DROP TABLE IF EXISTS good_student_infos");
		goodStudentsDF.saveAsTable("good_student_infos");
		
		// 第四个功能，可以用table()方法，针对hive表，直接创建DataFrame
		
		// 然后针对good_student_infos表，直接创建DataFrame
		Row[] goodStudentRows = hiveContext.table("good_student_infos").collect();
		for (Row goodStudentRow: goodStudentRows) {
			System.out.println(goodStudentRow);
		}
		
		sc.close();
	}
}

scala版本

package cn.spark.study.sql


import org.apache.spark.SparkConf;
import org.apache.spark.SparkContext;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.types.StructType;
import org.apache.spark.sql.types.StructField;
import org.apache.spark.sql.types.StringType;
import org.apache.spark.sql.types.IntegerType;
import org.apache.spark.sql.Row;


object JSONDataSource {
  def main(args: Array[String])
  {
    val conf = new SparkConf()
      .setAppName("JSONDataSource");
    val sc = new SparkContext(conf);
    val sqlContext = new SQLContext(sc);
    
		// 创建学生成绩DataFrame
		val studentScoresDF = sqlContext.read.json(
				"hdfs://spark1:9000/spark-study/students.json");
		
		// 查询出分数大于80分的学生成绩信息，以及学生姓名
		studentScoresDF.registerTempTable("student_scores");
		val goodStudentScoreDF = sqlContext.sql(
				"select name, score from student_scores where score >= 80");
    val goodStudentNames = goodStudentScoreDF.rdd.map{row => row(0)}.collect();
    
    // 创建学生基本信息DataFrame
    val studentInfoJSONs = Array("{\"name\":\"Leo\", \"age\":18}",
        "{\"name\":\"Marry\", \"age\":17}",
        "{\"name\":\"Jack\", \"age\":19}");
    
    val studentInfoJSONsRDD = sc.parallelize(studentInfoJSONs, 3);
    val studentInfosDF = sqlContext.read.json(studentInfoJSONsRDD);
    // 查询分数大于80分的学生的基本信息
    studentInfosDF.registerTempTable("student_infos");
    var sql = "select name, age from student_infos where name in ("
    for (i <- 0 until goodStudentNames.length) {
      sql += "'" + goodStudentNames(i) + "'"
      if (i < (goodStudentNames.length - 1)){
        sql += ","
      }
    }
    sql += ")"
    
    val goodStudentInfosDF = sqlContext.sql(sql);
    // 将分数大于80分的学生的成绩信息与基本信息进行join
    val goodStudentsRDD =  goodStudentScoreDF.rdd.map{row => (row.getAs[String]("name"), row.getAs[Long]("score"))}
      .join(goodStudentInfosDF.rdd.map{row => (row.getAs[String]("name"), row.getAs[Long]("age"))})
    // 将rdd转换为dataframe
    val goodStudentRowsRDD = goodStudentsRDD.map(
        info => Row(info._1, info._2._1.toString().toInt, info._2._2.toString().toInt))  
      
    val structType = StructType(Array(
        StructField("name", StringType, true), 
        StructField("score", IntegerType, true),
        StructField("age", IntegerType, true)))
        
    val goodStudentsDF = sqlContext.createDataFrame(goodStudentRowsRDD, structType)   
    // 将DataFrame中的数据保存到json中
    goodStudentsDF.write.format("json").save("hdfs://spark1:9000/spark-study/good-students-scala")
  }
}

---