創建表 · JAVA

[TOC] # 創建表另外需要注意的是傳統數據庫對表數據驗證是 schema on write（寫時模式），而 Hive 在load時是不檢查數據是否符合schema的，hive 遵循的是 schema on read（讀時模式），只有在讀的時候hive才檢查、解析具體的數據字段、schema。讀時模式的優勢是load data 非常迅速，因為它不需要讀取數據進行解析，僅僅進行文件的復制或者移動。寫時模式的優勢是提升了查詢性能，因為預先解析之后可以對列建立索引，并壓縮，但這樣也會花費要多的加載時間 ## 建表語法 ~~~ 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` 其中TEXTFILE為默認格式，建表時不指定默認為這個格式，導入數據時會直接把數據文件拷貝到hdfs上不進行處理。　　SEQUENCEFILE，RCFILE，ORCFILE格式的表不能直接從本地文件導入數據，數據要先導入到textfile格式的表中，然后再從表中用insert導入SequenceFile,RCFile,ORCFile表中。 6. clustered by 對于每一個表（table）或者分區， Hive可以進一步組織成桶，也就是說桶是更為細粒度的數據范圍劃分。Hive也是針對某一列進行桶的組織。Hive采用對列值哈希，然后除以桶的個數求余的方式決定該條記錄存放在哪個桶當中。把表（或者分區）組織成桶（Bucket）有兩個理由：（1）**獲得更高的查詢處理效率**。桶為表加上了額外的結構，Hive 在處理有些查詢時能利用這個結構。具體而言，連接兩個在（包含連接列的）相同列上劃分了桶的表，可以使用 Map 端連接（Map-side join）高效的實現。比如JOIN操作。對于JOIN操作兩個表有一個相同的列，如果對這兩個表都進行了桶操作。那么將保存相同列值的桶進行JOIN操作就可以，可以大大較少JOIN的數據量。（2）**使取樣（sampling）更高效**。在處理大規模數據集時，在開發和修改查詢的階段，如果能在數據集的一小部分數據上試運行查詢，會帶來很多方便 ## 分割 `fields terminated by`: 字段與字段之間的分割符 `collection items terminated by`:一個字段中各個子元素item的分隔符 ## 分區表分區表實際上就是對應一個hdfs文件系統上的獨立的文件夾,該文件夾下是該分區所有的數據文件. hive中的分就是分目錄,把一個大的數據集根據業務需要分割成小的數據集.在查詢的時通過where子句中的表達式選擇查詢所需要的指定分區,這樣的查詢效率會提高很多 # 具體實例 ## 文件載入表 ~~~ 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; ~~~ 如果要用load加載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; ~~~ **桶表插入** ~~~ student_ext表數據,用,分割下 95001,李勇,男,20,CS 95002,劉晨,女,19,IS 95003,王敏,女,22,MA 95004,張立,男,19,IS 95005,劉剛,男,18,MA ~~~ ~~~ //插入數據,需要后面有這樣的規則( distribute by sno sort by sno desc;)不然沒有按照分桶的規則,distribute分發的意思 //不要用clustered會報錯 insert overwrite table stu_buck select * from student_ext distribute by sno sort by sno desc; ~~~ **桶表抽樣查詢** ~~~ //查看下 select * from student_ext; //hive可以和hdfs有交互 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); ~~~ # 內部表和外部表的區別 Hive中內部表與外部表的區別： 1）創建表時：創建內部表時，會將數據移動到數據倉庫指向的路徑；若創建外部表，僅記錄數據所在的路徑，不對數據的位置做任何改變。 2）刪除表時：在刪除表的時候，內部表的元數據和數據會被一起刪除，而外部表只刪除元數據，不刪除數據。這樣外部表相對來說更加安全些，數據組織也更加靈活，方便共享源數據。 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 ~~~ `/hivedata` 是個目錄然后我們把文件放到這個目錄下面,發現是可以select出數據的然后我們看mysql表 TBLS(創建表單日期的一些數據)和COLUMNS_V2(表的一些字段信息)表 **如果我們把表drop掉的話,發現hdfs中還是有的,但是hive中是沒有的,表示連接斷開了,但是數據還是在的** ## 查看表的類型 ~~~ desc formatted t_ext; ~~~ Table Type中 MANAGED_TABLE管理表,表刪除了就都刪除了,表示不是外部表 # 表存儲格式 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; ~~~ 壓縮表是不能用外部文件load導入的,會報錯,會讓你檢查文件格式他的導入值,可以用其他表insert進去 ~~~ hive> insert overwrite table t_3 select * from t_2; ~~~ ### 區別 **TEXTFILE 格式** 默認格式，數據不做壓縮，磁盤開銷大，數據解析開銷大。可結合Gzip、Bzip2使用(系統自動檢查，執行查詢時自動解壓)，但使用這種方式，Hive不會對數據進行切分，從而無法對數據進行并行操作示例 ~~~ create table if not exists textfile_table( site string, url string, pv bigint, label string) row format delimited fields terminated by '\t' stored as textfile; ~~~ 插入數據 ~~~ Hive> Hive.exec.compress.output=true; Hive> set mapred.output.compress=true; Hive> set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; Hive> set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; Hive> insert overwrite table textfile_table select * from textfile_table ~~~ **SEQUENCEFILE 格式** SequenceFile是Hadoop API提供的一種二進制文件支持，其具有使用方便、可分割、可壓縮的特點。 SequenceFile支持三種壓縮選擇：NONE，RECORD，BLOCK。Record壓縮率低，一般建議使用BLOCK壓縮示例 ~~~ create table if not exists seqfile_table( site string, url string, pv bigint, label string) row format delimited fields terminated by '\t' stored as sequencefile; ~~~ 插入數據操作： ~~~ Hive> set Hive.exec.compress.output=true; Hive> set mapred.output.compress=true; Hive> set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; Hive> set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; Hive> SET mapred.output.compression.type=BLOCK; Hive> insert overwrite table seqfile_table select * from textfile_table; ~~~ **RCFILE 文件格式** RCFILE是一種行列存儲相結合的存儲方式。首先，其將數據按行分塊，保證同一個record在一個塊上，避免讀一個記錄需要讀取多個block。其次，塊數據列式存儲，有利于數據壓縮和快速的列存取 ~~~ create table if not exists rcfile_table( site string, url string, pv bigint, label string) row format delimited fields terminated by '\t' stored as rcfile; ~~~ 插入數據操作： ~~~ Hive> set Hive.exec.compress.output=true; Hive> set mapred.output.compress=true; Hive> set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; Hive> set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; Hive> insert overwrite table rcfile_table select * from textfile_table; ~~~ 相比TEXTFILE和SEQUENCEFILE，RCFILE由于列式存儲方式，數據加載時性能消耗較大，但是具有較好的壓縮比和查詢響應。數據倉庫的特點是一次寫入、多次讀取，因此，整體來看，RCFILE相比其余兩種格式具有較明顯的優勢。