必備知識

數據類型

基本類型

類型	寫法
字符	char, varchar, string?
整數	tinyint, smallint, int?, bigint?
小數	float, double, numeric(m,n), decimal(m,n)?
布爾值	boolean?
時間	date?, timestamp?

復雜類型(集合類型)

1、數組：array<T>	面向用戶提供的原始數據做結構化映射樣例： [] / |156,1778,42,138|	=> 描述同一個維度數據2、鍵值對：map<K,V>		樣例： |LogicJava:88,mysql:89|3、結構體：struct<name1:value1,name2:value2,....>樣例： 類json格式【以{}開頭結尾，且結構穩定】 => 結構化數據

【創建】表操作

一：hive建表【基本語法】

語法組成

組成一：建表 = 基本格式 + 行格式 + 額外處理
組成二：上傳數據

*基本格式

create table if not exists TABLE_NAME(FIELD_NAME DATA_TYPE,FIELD_NAME DATA_TYPE,....
)[comment '描述備注']

*行格式

形式一：row format delimited

1、應用場景：面向文本，非結構化與半結構化數據2、模擬數據：123,張三,16853210211116,true,26238.5,閱讀;跑步;唱歌,java:98;mysql:54,province:南京;city:江寧3、案例演示：create table if not exists TABLE_NAME(id int,name string,time bigint,isPartyMember boolean,hobby array<string>,scores map<string,int>,address struct<province:string,city:string>)row format delimitedfields terminated by ','collection items terminated by ';'map keys terminated by ':'lines terminated by '\n'4、講解：fields terminated by ','				列分隔符【字段: id,name...】collection items terminated by ';'		集合項內部間的分隔符map keys terminated by ':'				鍵值對[map]分隔符lines terminated by '\n'				行分隔符【默認，一般可以省略】

形式二：row format serde ‘CLASS_PATH’

1、應用場景：面向結構化數據，即：結構清晰的數據2、CLASS_PATH有以下幾種選擇：選擇一：CSV【簡單類型】數據呈現："1","2","Football""2","2","Soccer""3","2","Baseball & Softball"代碼：create table if not exists TABLE_NAME(id string,page string,word string)row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'with serdeproperties('separatorChar'=',','quoteChar'='"','escapeChar'='\\')選擇二：regex【正則】數據呈現：123,張三,16853210211116,true,26238.5,閱讀;跑步;唱歌,java:98;mysql:54,province:南京;city:江寧代碼：create table if not exists TABLE_NAME(id int,name string,time bigint,isPartyMember boolean,hobby array<string>,scores map<string,int>,address struct<province:string,city:string>)row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'with serdeproperties('input.regex'='^(//d+),(.*?),(//d+),(true|false),(\\d+\\.?\\d+?)$')選擇三：JsonSerDe數據呈現：{"name":"henry","age":22,"gender":"male","phone":"18014499655"}代碼：create table if not exists json(name string,age int,gender string,phone string)row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'

*額外處理

1、store【存儲】基本語法：stored as '存儲格式'存儲格式：textfile?,orc,parquet,sequencefile,...案例：stored as textfile2、tblproperties【表屬性】(通用)：案例【實際情況具體分析】：tblproperties('skip.header.line.count'='1'			【跳過表頭，即：第一行】...)

*上傳數據入表

方法一【不建議用】：hdfs dfs -put employee.txt /hive312/warehouse/yb12211.db/inner_table_employee方法二【有校驗過程】：?需知：local ：表示數據在虛擬機本地缺少local ：表示數據在hdfs上overload ：覆蓋缺少overload ：追加第一種【本地虛擬機】：load data local inpath '/root/file/employee.txt'overwrite into table yb12211.inner_table_employee;第二種【hdfs】：load data inpath '/hive_data/hive_cha01/employee/employee.txt'overwrite into table yb12211.inner_table_employee;方法三【只用于【外部表】】：?基本格式：location 'hdfs中存放文件的【目錄】的路徑'			外部掛載

針對性實踐操作

案例一：/*1|henry|1.81|1995-03-18|江蘇,南京,玄武,北京東路68號|logicjava:88,javaoop:76,mysql:80,ssm:82|beauty,money,joke2|arill|1.59|1996-7-30|安徽,蕪湖,南山,西湖東路68號|logicjava:79,javaoop:58,mysql:65,ssm:85|beauty,power,sleeping3|mary|1.72|1995-09-02|山東,青島,長虹,天山東路68*/drop table if exists students;create table if not exists students(number int,name string,height decimal(3,2),birthday date,house struct<province:string,city:string,district:string,street:string>,scores map<string,int>,hobby array<string>)row format delimitedfields terminated by "|"collection items terminated by ","map keys terminated by ":"stored as textfile;load data inpath '/zhou/students.txt'overwrite into table zhou.students;案例二：/*user_id,auction_id,cat_id,cat1,property,buy_mount,day
786295544,41098319944,50014866,50022520,21458:86755362;13023209:3593274;10984217:21985;122217965:3227750;21477:28695579;22061:30912;122217803:3230095,2,123434123*/drop table if exists sam_mum_baby_trade;create external table if not exists sam_mum_baby_trade(user_id bigint,auction_id bigint,cat_id bigint,cat1 bigint,property map<bigint,bigint>,buy_mount int,day bigint)row format delimitedfields terminated by ","collection items terminated by ";"map keys terminated by ":"stored as textfiletblproperties ('skip.header.line.count'='1');load data inpath '/zhou/sam_mum_baby_trade.csv'into table zhou.sam_mum_baby_trade;案例三:/*"1","2","Football""2","2","Soccer""3","2","Baseball & Softball"*/drop table if exists categories;create table if not exists categories(id string,page string,word string)row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'with serdeproperties('separatorChar'=',','quoteChar'='"','escapeChar'='\\')stored as textfile;load data inpath '/zhou/categories.csv'overwrite into table zhou.categories;select * from categories;案例四：/*{"name":"henry","age":22,"gender":"male","phone":"18014499655"}*///Jsondrop table if exists json;create table if not exists json(name string,age int,gender string,phone string)row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'stored as textfile;load data inpath '/zhou/json.log'overwrite into table zhou.json;案例五：/*125;男;2015-9-7 1:52:22;1521.84883;男;2014-9-18 5:24:42;6391.45652;女;2014-5-4 5:56:45;9603.79*/create external table if not exists test1w(user_id int,user_gender string,order_time timestamp,order_amount decimal(6,2))row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'with serdeproperties('input.regex'='(\\d+);(.*?);(\\d{4}-\\d{1,2}-\\d{1,2} \\d{1,2}:\\d{1,2}:\\d{1,2});(\\d+\.?\\d+?)')stored as textfilelocation '/zhou/test1w';select * from test1w;

二：hive建表【高階語法】

1：CTAS

【本質】：在原有表的基礎上查詢并創建新表
基本語法：create table if not exists NEW_TABLE_NAME as select ... from OLD_TABLE_NAME ...
案例：原有的表：hive_ext_regex_test1w語句：create table if not exists hive_ext_test_before2015 asselect * from hive_ext_regex_test1wwhere year(order_time)<=2015;

2：CTE

【本質】：對表進行層層篩選，最終形成新表
基本語法：as with....select...
案例：場景：2015年之前的所有數據 以及 2015年之后男性5個以上訂單數或5w以上訂單總額的訂單數據。原有的表：hive_ext_regex_test1w語句：create table hive_test_before2015_and_male_over5or5w aswithbefore2015 as (select * from hive_ext_regex_test1wwhere year(order_time)<=2015),agg_male_over5or5w as (select user_idfrom hive_ext_regex_test1wwhere year(order_time)>2015 and user_gender='男'group by user_idhaving count(*)>=5 or sum(order_amount)>=50000),male_over5or5w as (select A.*from  hive_ext_regex_test1w Ainner join agg_male_over5or5w Bon year(A.order_time)>2015 and A.user_id=B.user_id)select * from before2015union all							【注意：union all => 將表并在一起且不去重】select * from male_over5or5w;

3：CTL

【本質】：復制原表的表結構
基本語法：create table NEW_TABLE_NAME like OLD_TABLE_NAME;
案例：create table hive_test1w_like like hive_ext_regex_test1w;

【修改】表操作

提前需知

1、查看表字段基本信息：desc 表名;2、查看表字段詳細信息：desc formatted 表名;	=> 由此可查看表中可修改的屬性3、查看建表流程：show create 表名;

基本語法

alter table TABLE_NAMErename to NEW_NAME;set tblproperties('key'='value')		-- 修改表屬性：包括各種分隔符,SerDe,...ser fileformat FORMAT;					-- 修改文件格式change old_name new_name TYPE;			-- 修改字段名column(field_name TYPE)					-- 添加列