SPARK-SQL - 创建和操作 临时视图，全局视图

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class test20 {
    public static void main(String[] args) throws Exception {
        SparkSession spark = SparkSession
                .builder()
                .config("spark.driver.host", "localhost")
                .appName("TableTypeTest")
                .master("local")
                .getOrCreate();

        spark.sparkContext().setLogLevel("ERROR");

        //3：临时视图
        Dataset<Row> sessionDf = spark.read().parquet(Utils.BASE_PATH + "/trackerSession");
        //3.1：session级别的视图
        //第一种创建临时视图的方式
        sessionDf.createTempView("trackerSession");
        sessionDf.createOrReplaceTempView("trackerSession");
        Dataset<Row> sessionRecords = spark.sql("select * from trackerSession");
        sessionRecords.show();
//        +--------------------+-------------------+-------+------------+---------+--------------------+--------------+-----------+---------------+------------+
//        |          session_id|session_server_time| cookie|cookie_label|       ip|         landing_url|pageview_count|click_count|         domain|domain_label|
//        +--------------------+-------------------+-------+------------+---------+--------------------+--------------+-----------+---------------+------------+
//        |520815c9-bdd4-40c...|2017-09-04 12:00:00|cookie1|          固执|127.0.0.3|https://www.baidu...|             1|          2|  www.baidu.com|      level1|
//        |912a4b47-6984-476...|2017-09-04 12:45:01|cookie1|          固执|127.0.0.3|https://tieba.bai...|             1|          2|tieba.baidu.com|           -|
//        |79534f7c-b4dc-4bc...|2017-09-04 12:00:01|cookie2|         有偏见|127.0.0.4|https://www.baidu...|             3|          1|  www.baidu.com|      level1|
//        +--------------------+-------------------+-------+------------+---------+--------------------+--------------+-----------+---------------+------------+


        //第二种创建临时视图的方式
        spark.sql("CREATE TEMPORARY VIEW temp_cookie1 AS SELECT * FROM " +
                "trackerSession WHERE cookie = 'cookie1'");
        Dataset<Row> cookie1 = spark.sql("select * from temp_cookie1");
        cookie1.show();
//        +--------------------+-------------------+-------+------------+---------+--------------------+--------------+-----------+---------------+------------+
//        |          session_id|session_server_time| cookie|cookie_label|       ip|         landing_url|pageview_count|click_count|         domain|domain_label|
//        +--------------------+-------------------+-------+------------+---------+--------------------+--------------+-----------+---------------+------------+
//        |520815c9-bdd4-40c...|2017-09-04 12:00:00|cookie1|          固执|127.0.0.3|https://www.baidu...|             1|          2|  www.baidu.com|      level1|
//        |912a4b47-6984-476...|2017-09-04 12:45:01|cookie1|          固执|127.0.0.3|https://tieba.bai...|             1|          2|tieba.baidu.com|           -|
//        +--------------------+-------------------+-------+------------+---------+--------------------+--------------+-----------+---------------+------------+


        //3.2：全局级别的视图
        Dataset<Row>  personDF = spark.read().json(Utils.BASE_PATH + "/people.json");
        personDF.createOrReplaceGlobalTempView("person_global");
        //需要从spark sql的保留db global_temp 中查询这个全局视图
        spark.sql("select * from global_temp.person_global").show();
//        +---+-------+
//        |age|   name|
//        +---+-------+
//        | 29|Michael|
//        | 30|   Andy|
//        | 19| Justin|
//        +---+-------+

        //spark.newSession().sql("select * from trackerSession").show(); //会抛异常
        spark.newSession().sql("select * from global_temp.person_global").show();
//        +---+-------+
//        |age|   name|
//        +---+-------+
//        | 29|Michael|
//        | 30|   Andy|
//        | 19| Justin|
//        +---+-------+

        //catalog table相关元数据操作
        spark.catalog().listTables().show();
//        +--------------+--------+-----------+---------+-----------+
//        |          name|database|description|tableType|isTemporary|
//        +--------------+--------+-----------+---------+-----------+
//        |  temp_cookie1|    null|       null|TEMPORARY|       true|
//        |trackersession|    null|       null|TEMPORARY|       true|
//        +--------------+--------+-----------+---------+-----------+

        //删除临时视图
        spark.catalog().dropTempView("trackerSession");
        spark.catalog().dropGlobalTempView("person_global");
    }
}