sparksql hive作为数据源

根据官方文档的说法,要把hive-site.xml,core-site.xml,hdfs-site.xml拷贝到spark的conf目录下,保证mysql已经启动

java

public class Demo {
    private static SparkSession session = SparkSession.builder().appName("demo").enableHiveSupport()
            .config("spark.sql.warehouse.dir", "/user/hive/warehouse").getOrCreate();

    public static void main(String[] args) {
        session.sql("drop table if exists students_info");
        session.sql("create table if not exists students_info(name string,age int) "
                + "row format delimited fields terminated by '	' 
");

        // 将数据导入学生信息表
        session.sql(
                "load data local inpath '/opt/module/spark-test/data/student_infos.txt' into table default.students_info");

        session.sql("drop table if exists students_score");
        session.sql("create table if not exists students_score(name string,score int)  
"
                + "row format delimited fields terminated by '	' 
");

        // 将数据导入学生成绩表
        session.sql(
                "load data local inpath '/opt/module/spark-test/data/student_scores.txt' into table default.students_score");

        // 查询
        Dataset<Row> dataset = session.sql(
                "select s1.name,s1.age,s2.score from students_info s1 join students_score s2 on s1.name=s2.name where s2.score>80");

        // 将dataset中的数据保存到hive中
        session.sql("drop table if exists students_result");
        dataset.write().saveAsTable("students_result");

        // 将hive中的表转成dataset,查看数据是否成功保存
        Dataset<Row> table = session.table("students_result");
        table.show();

        session.stop();

    }
}

scala

object Demo {
  def main(args: Array[String]): Unit = {
    val session = SparkSession.builder().appName("demo").enableHiveSupport().config("spark.sql.warehouse.dir", "/user/hive/warehouse").getOrCreate()

    session.sql("drop table if exists students_info")
    session.sql("create table if not exists students_info(name string,age int) 
 row format delimited fields terminated by '	'")

    session.sql("load data local inpath '/opt/module/spark-test/data/student_infos.txt' into table default.students_info")

    session.sql("drop table if exists students_score")
    session.sql("create table if not exists students_score(name string,score int) 
 row format delimited fields terminated by '	'")

    session.sql("load data local inpath '/opt/module/spark-test/data/student_scores.txt' into table default.students_score")

    //保存到hive中
    session.sql("drop table if exists students_result")
    session.sql("select s1.name,s1.age,s2.score from students_info s1 join students_score s2 on s1.name=s2.name where s2.score >90").write.saveAsTable("students_result")

    //检查数据是否保存
    val df = session.table("students_result")
    df.show()

    session.stop()
  }
}