臨時表 · Hadoop2.x

臨時表在hive0.14.0及以上版本支持。 <br/> **臨時表的作用：** 臨時表是應用程序自動管理在復雜查詢期間生成的中間數據的方法。 <br/> **臨時表的特點：** （1）臨時表只對當前session有效，session退出后自動刪除；（2）假如在hivebook數據庫中創建employee臨時表，如果早已存在其他類型的表（內部表、外部表）同名，則當前session使用的是剛剛創建的臨時表，只有`drop`或`rename`臨時表才會使用其他類型的表。（3）臨時表的存儲位置由下面兩個屬性共同決定 ```xml -- {hive_home}/conf/hive-site.xml  <property> <name>hive.exec.scratchdir</name>   <value>/home/hadoop/hive/data/hive-${user.name}</value> <description>Scratch space for Hive jobs</description> </property> <property> <name>hive.exec.local.scratchdir</name> <value>/home/hadoop/hive/data/${user.name}</value> <description>Local scratch space for Hive jobs</description> </property> 則 employee 臨時表的存儲根目錄為：/home/hadoop/hive/data/hive-root/root/ ``` （4）臨時表不支持分區字段和創建索引；（5）hive1.1及以上版本支持臨時表在內存、ssd卡中存儲，通過在 {hive_home}/conf/hive-site.xml 中指定`hive.exec.temporary.table.storage`屬性來配置，分別有`memory、ssd、default`三種取值。 <br/> **在 hivebook 數據庫中創建 employee 臨時表，并導入本地的 employee.txt 數據到該表中：** *`employee.txt`* ```xml Michael|Montreal,Toronto|Male,30|DB:80|Product:Lead,Developer:Lead Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead Shelley|New York|Female,27|Python:80|Test:Lead,COE:Architect Lucy|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead ``` ```sql ####（1）根據 employee.txt 格式創建 employee 臨時表 #### create temporary table if not exists employee( emp_name string, work_place array<string>, sex_age struct<sex:string, age:int>, skills_score map<string, int>, depart_title map<string, array<string>> ) row format delimited fields terminated by '|' collection items terminated by ',' map keys terminated by ':' ; 0: jdbc:hive2://hadoop101:10000> show create table employee; +----------------------------------------------------+--+ | createtab_stmt | +----------------------------------------------------+--+ | CREATE TEMPORARY TABLE `employee`( | | `emp_name` string, | | `work_place` array<string>, | | `sex_age` struct<sex:string,age:int>, | | `skills_score` map<string,int>, | | `depart_title` map<string,array<string>>) | | ROW FORMAT SERDE | | 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' | | WITH SERDEPROPERTIES ( | | 'colelction.delim'=',', | | 'field.delim'='|', | | 'mapkey.delim'=':', | | 'serialization.format'='|') | | STORED AS INPUTFORMAT | | 'org.apache.hadoop.mapred.TextInputFormat' | | OUTPUTFORMAT | | 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' | | LOCATION | | 'hdfs://hadoop101:9000/home/hadoop/hive/data/hive-root/root/5189eaf7-d7c8-4422-94a7-8aca0b1bb8e0/_tmp_space.db/15fa5bcb-e1e6-477c-877d-4fb38b9b6591' | | TBLPROPERTIES ( | | 'COLUMN_STATS_ACCURATE'='true', | | 'numFiles'='1', | | 'totalSize'='234') | +----------------------------------------------------+--+ ####（2）往表中裝載 employee.txt 數據 #### 0: jdbc:hive2://hadoop101:10000> load data local inpath "/employee.txt" into table employee; ####（3）查詢 #### 0: jdbc:hive2://hadoop101:10000> select * from employee; +--------------------+-------------------------+----------------------------+------------------------+--------------------------------------------+--+ | employee.emp_name | employee.work_place | employee.sex_age | employee.skills_score | employee.depart_title | +--------------------+-------------------------+----------------------------+------------------------+--------------------------------------------+--+ | Michael | ["Montreal","Toronto"] | {"sex":"Male","age":30} | {"DB":80} | {"Product":["Lead"],"Developer":["Lead"]} | | Will | ["Montreal"] | {"sex":"Male","age":35} | {"Perl":85} | {"Product":["Lead"],"Test":["Lead"]} | | Shelley | ["New York"] | {"sex":"Female","age":27} | {"Python":80} | {"Test":["Lead"],"COE":["Architect"]} | | Lucy | ["Vancouver"] | {"sex":"Female","age":57} | {"Sales":89,"HR":94} | {"Sales":["Lead"]} | +--------------------+-------------------------+----------------------------+------------------------+--------------------------------------------+--+ ```