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");
}
}