創建表 · 大數據

[TOC] ## 創建表 ### 建表語法 ~~~ CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path] ~~~ 說明： 1. CREATE TABLE 創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常；用戶可以用 IF NOT EXISTS 選項來忽略這個異常。 2. EXTERNAL關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際數據的路徑（LOCATION），Hive 創建內部表時，會將數據移動到數據倉庫指向的路徑；若創建外部表，僅記錄數據所在的路徑，不對數據的位置做任何改變。在刪除表的時候，內部表的元數據和數據會被一起刪除，而外部表只刪除元數據，不刪除數據。 3. LIKE 允許用戶復制現有的表結構，但是不復制數據。 4. ROW FORMAT ~~~ DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] ~~~ 用戶在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe，Hive通過 SerDe 確定表的具體的列的數據。 5. STORED AS `SEQUENCEFILE|TEXTFILE|RCFILE` 如果文件數據是純文本，可以使用 STORED AS TEXTFILE。如果數據需要壓縮，使用 STORED AS SEQUENCEFILE 6. CLUSTERED BY 對于每一個表（table）或者分區， Hive可以進一步組織成桶，也就是說桶是更為細粒度的數據范圍劃分。Hive也是針對某一列進行桶的組織。Hive采用對列值哈希，然后除以桶的個數求余的方式決定該條記錄存放在哪個桶當中。把表（或者分區）組織成桶（Bucket）有兩個理由：（1）**獲得更高的查詢處理效率**。桶為表加上了額外的結構，Hive 在處理有些查詢時能利用這個結構。具體而言，連接兩個在（包含連接列的）相同列上劃分了桶的表，可以使用 Map 端連接（Map-side join）高效的實現。比如JOIN操作。對于JOIN操作兩個表有一個相同的列，如果對這兩個表都進行了桶操作。那么將保存相同列值的桶進行JOIN操作就可以，可以大大較少JOIN的數據量。（2）**使取樣（sampling）更高效**。在處理大規模數據集時，在開發和修改查詢的階段，如果能在數據集的一小部分數據上試運行查詢，會帶來很多方便。 ### 具體實例 #### 文件載入表 ~~~ hive> create table student(id int,name string,age int) > row format delimited > fields terminated by ','; OK ~~~ 創建表的時候指定行分割和每個字段分割創建文本 ~~~ [root@master ~]# cat student.txt 1,jdxia,17 2,user2,20 ~~~ 然后上傳上去(后面就是hadoop的路徑) ~~~ hdfs dfs -put student.txt /user/hive/warehouse/db1.db/student/ ~~~ 然后查詢下表 ~~~ hive> select * from student; OK 1 jdxia 17 2 user2 20 Time taken: 0.082 seconds, Fetched: 2 row(s) ~~~ 如果表不這樣指定行分割和列分割,會顯示null 我們再次上傳下看下 ~~~ [root@master ~]# cp student.txt student1.txt [root@master ~]# hdfs dfs -put student1.txt /user/hive/warehouse/db1.db/student/ ~~~ 然后select看下發現又多了數據 #### hdfs載入表但是這樣做不好,我們一般這么做 **inpath載入** 創建表 ~~~ hive> create table t_user(id int,name string,age int) > row format delimited > fields terminated by ','; OK Time taken: 0.088 seconds ~~~ 把本地的東西載入進去 ~~~ hive> load data local inpath '/root/student.txt' into table t_user; ~~~ 如果要加載hdfs上面的數據我們先把這個加載到hadoop中 ~~~ hdfs dfs -put student1.txt / ~~~ 然后我們在hive中操作 ~~~ load data inpath '/student1.txt' into table t_user; ~~~ 這樣就可以用hdfs中的文件,載入進表中 #### 創建分桶表分桶表不要load,不然你去hdfs上看還是一個文件開啟分桶機制,默認是關閉的 ~~~ set hive.enforce.bucketing=true; //查看 set hive.enforce.bucketing; ~~~ clustered by表示按什么分桶 ~~~ hive> create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string) > clustered by(Sno) > sorted by(Sno DESC) > into 4 buckets > row format delimited > fields terminated by ','; ~~~ ~~~ //清空表數據,可以用這個 truncate table stu_buck; ~~~ **桶表插入** ~~~ //插入數據,需要后面有這樣的規則( distribute by sno sort by sno desc;)不然沒有按照分桶的規則 //不要用clustered會報錯 insert overwrite table stu_buck select * from student_ext distribute by sno sort by sno desc; ~~~ ~~~ //查看下 select * from student_ext; dfs -cat /user/hive/warehouse/db1.db/stu_buck/000000_0 ~~~ **桶表抽樣查詢** ~~~ Select * from student tablesample(bucket 1 out of 2 on id) tablesample是抽樣語句，語法：TABLESAMPLE(BUCKET x OUT OF y)? y必須是table總bucket數的倍數或者因子。hive根據y的大小，決定抽樣的比例. 如，table總共分了64份，當y=32時，抽取(64/32=)2個bucket的數據，當y=128時，抽取(64/128=)1/2個bucket的數據。 x表示從哪個bucket開始抽取。例如，table總bucket數為32，tablesample(bucket 3 out of 16)，表示總共抽取（32/16=）2個bucket的數據，分別為第3個bucket和第（3+16=）19個bucket的數據 ~~~ ~~~ //查詢其中一個桶,和直接cat查詢文件是一樣的,這是取1個桶的 select * from stu_buck tablesample (bucket 1 out of 4 on sno); //取2個桶的,1和3這2個桶 select * from stu_buck tablesample (bucket 1 out of 2 on sno); ~~~ ### 內部表和外部表的區別 external #### 外部表 ~~~ hive> create external table t_ext(id int,name string,age int) > row format delimited > fields terminated by ','; OK ~~~ 創建外部表可以加個local屬性指定路徑,他可以加載外部的東西,不像內部表 ~~~ hive> create external table t_ext(id int,name string,age int) > row format delimited > fields terminated by ',' > location "/hivedata"; OK ~~~ 然后我們把文件放到這個目錄下面,發現是可以select出數據的然后我們看mysql表 TBLS(創建表單日期的一些數據)和COLUMNS_V2(表的一些字段信息)表 **如果我們把表drop掉的話,發現hdfs中還是有的,但是hive中是沒有的,表示連接斷開了,但是數據還是在的** ### 分區表 **創建表** ~~~ hive> create table t_patition(ip string,duration int) > partitioned by(country string) > row format delimited > fields terminated by ','; OK Time taken: 0.086 seconds ~~~ **查看表的詳情** ~~~ hive> desc t_patition; ~~~ 我們來用文件來填充數據注意我們要指定分區的,因為他的分區表 ~~~ hive> load data local inpath '/root/ip.txt' into table t_patition partition(country="china"); ~~~ 然后查詢的時候,后面會顯示分區 ~~~ hive> select * from t_patition; OK 192.168.1.100 1 china 192.168.1.200 2 china ~~~ 也可以只查指定分區的數據 ~~~ hive> select * from t_patition where country="china"; ~~~ 還可以創建多級分區 ~~~ hive> create table t_patition(ip string,duration int) > partitioned by(country string,city string) > row format delimited > fields terminated by ','; ~~~ 數據又分成了一半,在country文件夾下在分city文件夾 ### 表存儲格式 stored as **創建表** ~~~ create table t_2(id int,name string) row format delimited fields terminated by ',' stored as textfile; ~~~ 填充外部文件數據 ~~~ [root@master ~]# cat name.txt 1,jdxia 2,xiaozhan ~~~ ~~~ hive> load data local inpath '/root/name.txt' into table t_2; ~~~ ~~~ STORED AS `SEQUENCEFILE|TEXTFILE|RCFILE` 如果文件數據是純文本，可以使用 STORED AS TEXTFILE。如果數據需要壓縮，使用 STORED AS SEQUENCEFILE ~~~ 默認是TEXTFILE 創建個壓縮的 ~~~ hive> create table t_3(id int,name string) > row format delimited > fields terminated by ',' > stored as SEQUENCEFILE; ~~~ 壓縮表是不能用外部文件導入的,會報錯他的導入值,可以用其他表insert進去 ~~~ hive> insert overwrite table t_3 select * from t_2; ~~~