Hive[4] 數據定義 HiveQLHiveQL 是 Hive 查詢語言，它不完全遵守任一種 ANSI SQL 標準的修訂版，但它與 MySQL 最接近，但還有顯著的差異，Hive 不支持行級插入，更新和刪除的操作，也不支持事務，但 Hive 增加了在 Hadoop 背景下的可以提供更高性能的擴展，以前個性化的擴展，還有一些外部程序；4.1 Hive 中的數據庫Hive 數據庫本質上就是表的一個目錄或命名空間；如果用戶沒有顯示式的指定庫那么將會使用默認的數據庫 defaulthive> cretae database if not exists financials; 創建一個數據庫(如果不存在的話)注意：在所有的數據庫相關命令中，都可以使用 SCHEMA 這個關鍵字來代替關鍵字 TABLE;hive>show databases; 查看所有數據庫hive>show databases linke 'h.*' 查看以h 開頭其他字結尾的數據庫名Hive 會為每個數據庫創建一個目錄，數據庫中的表會以這個目錄的子目錄的形式存儲；數據庫所在的目錄位于屬性 hive.metasotre.warehouse.dir 所指定的頂層目錄之后；hive>create database>finacials comment '數據庫描述信息'>location '/my/PReferred/directory' ;來修改默認存儲的位置>describe database financials; 顯示數據庫的信息test2 hdfs://192.168.1.12:9000/user/hive/warehouse/test2.db root USER下面會顯示數據庫所在的文件目錄的位置路徑,如果用戶是偽分布式模式，那么主節點服務器的名稱就應該是 localhost；對于本地模式這個應該是一個本地路徑 ，例如： file://user/hive/warehouse/test2.db; 如果這部分省略了，Hive 將全使用 Hadoop 配置文件中的配置項 fs.default.name 作為 master-server 所對應的服務器名和端口號；注意：hdfs:///user/hive/warehouse/test.db和hdfs://master-server/user/hive/warehouse/test.db是等價的；create database test3 with dbproperties('creator'='Mark Moneybags','date'='2015-01-10') ;建立數據庫時給庫添加其屬性字段；describe databaseextendedtest3; 查看數據庫屬性hive> use test2; 切換到 test2 庫；hive> show tables; 顯示當前庫下所有表；hive> show tables 'empl.*' ; 顯示 empl 開頭的其它字符結尾的表；hive> show tables in mydb; 顯示 mydb 數據庫下的表(不在當前庫下也可以顯示其他庫下的表)；hive>drop database if exists test3; 刪除數據庫；if exists 可以避免因數據庫不存在而拋出警告信息；hive> drop database if exists test3 cascade ; 注意： Hive 是不允許用戶刪除一個包含有表的數據庫，用戶要么先刪除數據庫中的表，然后再刪除數據庫，要么在刪除命令的最后加上 cascade 這樣就可以先刪除數據庫的表；如果使用 restrict 這個關鍵字，那么就和默認的情況一樣，必須先刪除表再刪除數據庫，如果刪除了庫那么對應的目錄也會被刪除；4.2 修改數據庫hive>alter database databaseName set dbproperties ('edited-by'='Joe Dba') ; 修改數據庫描述，數據庫其他元數據信息都是不可更改的，包括數據庫名和數據庫所在的目錄位置，沒有辦法可以刪除或“重置”數據庫屬性；4.3 創建表Hive 創建表可自定義數據文件存儲的位置，使用什么存儲格式等；示例：CREATE TABLE IF NOT EXISTS test2.employees( name STRINGCOMMENT 'xing ming', salary FLOATCOMMENT 'gong zi', subordinates ARRAY<STRING>COMMENT 'xia shu', deductions MAP<STRING,FLOAT>COMMENT 'kou chu', address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>COMMENT 'di zhi')COMMENT 'Desciption of the table' TBLPROPERTIES ('creator'='me','create_at'='2014-12-17 21:10:50') --表描述【TBLPROPERTIES作用是按鍵值對的格式為表增加額外的說明，也可以表示數據庫鏈接的必要的元數據信息】LOCATION '/user/hive/warehouse/mydb.db/employees'; --為表中的數據指定一個存儲路徑（和元數據不同的是，元數據總是會保存這個路徑）默認的情況下 Hive 總是將創建的表的目錄放置在這個表所屬的數據庫目錄之后，但 default 庫例外，這個數據庫表的目錄會直接位一 /user/hive/warehouse 目錄之后（除了用戶明確指定為其他路徑）last_modified_by 保存著最后修改這個表的用戶的用戶名；hive 自動添加的表屬性last_modified_time最后一次修改的新紀元時間秒；hive 自動添加的表屬性hive>show tblproperties tableName ;顯示某表的tblproperties 信息hive>create table if not exists mydb.abc like mydb.aaa;直接拷貝表結構，不帶數據；hive>describe extended mydb.abc ; 查盾這個表的詳細表結構信息；hive>describe mydb.abc.salary; 顯示表里的某一列的信息；4.3 管理表以上我們建立的表都是管理表，也叫內部表；當然也有外部表；創建一個外部表來讀取位于 /data/stocks 目錄下的以逗號分隔的數據：create external table if not exista stocks(exchange string, symbol string, ymd string, price_open float,price_high float, price_low float, price_close float, volume int, price_adj_close float)row format delimited fields terminated by ','location '/data/stocks';external 告訴這個表是外部的，location 告訴Hive 數據位于哪個路徑下；注意：因為表是外部，所以 Hive并非認為基完全擁有這份數據，因此，刪除此表時并不會刪除掉這份數據，不過描述表的元數據信息會被刪除掉；describe extended tablename可以查看表是否是管理表或外部表；[如果輸出中有 tableType:MANAGED_TALBE表明是托管表，tableType:EXTERNAL_TALBE 外部表]create external table if not exists mydb.employees3 like mydb.employeeslocation '/path/to/data'; --對一張存在的表進行表結構復制（不會復制數據）注意：如果上面例子中省掉external 關鍵字而且源表是外部表搞亂 ，那么生成的表也是外部表，如果語句中省略掉external關鍵字而且源表是管理表的話，那么生成的表也是管理表。4.4 分區表，管理表通常使用分區來水平分散壓力，將數據從物理上轉移到和使用最頻繁的用戶更近的地方，以及實現其他目的；對數據分區最重要的原因就是為了更快的查詢，尋找個表周的人員時只需要掃描一個目錄下的內容即可；創建分區表：CREATE TABLE employeesPP(name STRING, salary FLOAT, subordinates ARRAY<STRING>, deductions MAP<STRING,FLOAT>,address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>)PARTITIONED BY(contry STRING, state STRING);分區表改變了 Hive 對數據存儲的組織方式； 具體不同 P80頁/employeesP/country=CA/state=AB/employeesP/country=CA/state=BC/employeesP/country=US/state=AL/employeesP/country=US/state=AKSELECT * FROMemployeesP WHERE country = 'US' NAD state = 'IL';查詢分區字段set hive.mapred.mode=strict;[nostrict] 將 Hive 設置為 strict 嚴格模式；show partitions employeesPP；查盾表中存在的所有分區；show partitions employeesPP partition(country='US',state='AK'); 想查看是否存儲某個特定分區鍵的分區；describe extended employeesPP 顯示分區鍵；通過載入數據的方式創建分區表：LOAD DATA LOCAL INPUTH '${env:HOME}/california-employees'INTO TABLE employees---注意 HiveQL中是如何引用 HOME 環境變量的partition (country='US', state='CA');4.4 外部分區表外部表上同樣可以使用分區；日志分析實例：時間戳，嚴重程度（error、warting、info）,服務器名稱和進程ID等；create external table if not exists log_messages(hms INT, serverity STRING, server STRING, process_id INT, message STRING) partitioned by(year INT, month INT, day INT)row format delimited fields terminated by '/t';上面語句是創建一個外部年月日的分區表alter table log_messages ADD PARTITION(year=2012,month=1,day=2)location 'hdfs://master_server/data/log_messages/2012/01/02'; --可以單獨進行增加分區；hadoop distcp /data/log_message/2011/12/01/ s3n://ourbucket/logs/2011/12/02 將分區下的數據拷貝到 S3中；alter table log_messages partition(year=2012,month=12,day=2) set location 's3n://ourbucket/logs/2012/12/1=02/'; 修改表將分區路徑指向到 S3路徑；hadoop fs -rmr /data/log_message/2012/01/02/; 刪除 HDFS 中的這個分區數據；Hive 不關心一個分區對應的分區目錄是否存在或者分區目錄下是否有文件，如果分區目錄不存在或者分區目錄下沒有文件，則對于這個過濾分區的查詢將沒有返回結果，當用戶想在另外一個進程開始往分區中寫數據之前創建好分區時，這樣做很方便的，數據一旦存在，對于這份靈氣的查詢就會有返回結果；這個功能所具有的另一個好處是：可以將新數據寫入到一個專用的目錄中，并與位于他目錄中的數據存在明顯的區別，同時，不管用戶是將舊數據轉移到一個“存檔”位置還是直接刪除掉，新數據被篡改的風險都被降低了，因為新數據的數據子集位于不同的目錄；和非分區外部表一樣，Hive 并不控制這些數據，即使表被刪除，數據也不會被刪除；show partitions log_messages;查看外部分區表的分區；describe extended log_messages; 會將分區鍵作為表的模式的一部分和 partitonKeys 列表的內容同時進行顯示[輸出會缺少分區數據的實際路徑]；describe extended log_messages partiton(year=2012,month=1,day=2); 會顯示出來分區數據的實際存儲路徑；我們通常會使用分區外表，因為它具有非常多的優點，如邏輯數據管理，高性能的查詢等；alter table ... add partition 語句并非只有對外部表才能夠使用，對于管理表，當有分區數據不是由 LOAD 和 INSERT 語句產生時，用戶同樣可以使用這個命令指定分區路徑；自定義表的存儲格式CREATE TABLE IF NOT EXISTS test2.employees( name STRINGCOMMENT 'xing ming', salary FLOATCOMMENT 'gong zi', subordinates ARRAY<STRING>COMMENT 'xia shu', deductions MAP<STRING,FLOAT>COMMENT 'kou chu', address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>COMMENT 'di zhi')COMMENT 'biao shuo ming' TBLPROPERTIES ('creator'='me','create_at'='2014-12-17 21:10:50') 表說明標簽ROW FORMAT DELIMITEDFIELDS TERMINATED BY '/001' --列與列之間的分隔符；COLLECTION ITEMS TERMINATED BY '/002'--數組分隔符設定；MAP KEYS TERMAINATED '/003' -- MAP 分隔符LINES TERMINATED '/N' --行與行之間的分隔符；STROED AS TEXTFILE; --文本格式存儲表數據，意味著每一行被認為是一個單獨的記錄；LOCATION ‘/user/hive/warehouse/mydb.db/employees';數據存放地址用戶可以將 textfile 替換為其他 Hive 所支持的內置文件格式，包括 sequencefile 和 rcfile 這兩種文件格式都是使用二進制編碼和壓縮（可選），來優化磁盤空間使用以及 I/O帶寬性能的；用戶還可以指定第三方的輸入和輸出格式以及 SerDe 這個功能允許用戶自定義 Hive 本身不支持的其他廣泛的文件格式；以下例子使用了自定義 SerDe，輸入格式也輸出格式，可以通過 Avro 協議訪問這些文件：create table kstpartitioned by (ds STRING)row format serde 'com.linkedin.haivvreo.AvroSerDe' --指定了SerDewith serdeproperties('schema.url'='http://schema_provider/kst.avsc') --with serdeproperties 允許用戶傳遞配置信息給 SerDe【每個屬性都需要用引號】stored asinputfomat 'com.linkedin.haivvreo.AvroContainerInputFormat' --指定用于輸入格式的java類outputformat 'com.linkedin.haivvreo.AvroContainerOutputFormat'; --指定用于輸出格式的Java類注意：如果用戶要指定，就兩個都必須指定；describe extended table；會在 detailed table informationg 部分列舉出輸入和輸出格式以有 SerDe 和 SerDe 所自帶的屬性信息。drop table if exists employees;刪除表，對于管理表，表的元數據信息和表內的數據都會被刪除；對于外部表，表的元數據信息會被刪除，但是表中的數據不會被刪除；提示：如果用戶開啟了 Hadoop 回收站功能（默認是關閉的）那么數據將會被轉移到用戶在分布式文件系統中的用戶根目錄下的.Trash 目錄下，也就是 HDFS 中的 /$USER/.Trash 目錄，如果要開啟這個功能，需要修改配置 fs.trash.interval 的值設置為一個合理的正整數即可；這個值是 “回收站檢查點”間的時間間隔，單位是分鐘。修改表可以通過 alter table 語句來進行修改，這操作會修改元數據，但不會修改數據本身。alter table log_messages rename to logmsgs; 修改表名alter table log_messages add if not exists --在同一個查詢中可以同時增加多個分區（0.8以后的版本可以支持此查詢）partiton(year=2015,month=1,day=10) location '/logs/2015/01/10'partiton(year=2015,month=1,day=9) location '/logs/2015/01/09'partiton(year=2015,month=1,day=9) location '/logs/2015/01/08'alter table log_messages partition(year=2012,month=12,day=2)set location 's3n://ourbucket/logs/2011/01/02' ;用戶可以通過高效地移動位置來修改某個分區的路徑；但這個命令不會將數據從舊的路徑轉移走，也不會刪除舊的數據；如果用戶此句增加分區，分區內的數據也是會同時和元數據信息一起被刪除的（外部表則不會）alter table log_messages drop if exists partition(year=2012,month=12,day=2) ; 刪除某個分區；alter table log_messageschange column hms hours_minutes_seconds int--給字段進行重命名 （把hms字段改成hours_minutes_seconds并）comment 'The houses, minutes, and seconds part of the timesamp' after severity; --修改位置，類型或者注釋 （位置在 severity 字段之后 ）注意：即使字段名或者字段類型都沒有改變，用戶也需要完全指定舊的字段名，并給出新的字段名及新的字段類型，關鍵字 column 和 comment 子句都是可選的。如果把改后的字段改放到第一個位置只需要把 after 改成 first 即可 ；這個命令只會修改元數據信息，如果移動字段，那么數據也應當和新的模式匹配或者通過其他些方法修改數據以使其能夠和模式匹配；alter table log_message ADD COLUMNS (app name STRING COMMENT 'application name',session_id LONG COMMENT 'The current session id'); 增加列alter table log_messages replace columns(hours_mins_secs INT COMMON '',severity STRING COMMON '',message STRING COMMON ''); 重命名了之前的 hms 字段并且從之前的表定義的模式中移除了字段 server 和 process_id 只改變表的元數據信息；alter table log_messages SET tblproperties('notes'='The process id is no longer captured'); 修改表屬性，但無法刪除屬性；alter table log_msg partition(year=2015,month=1,day=10) SET fileformat sequencefile; 修改表的存儲格式為 sequence file 存儲格式alter table talbe_using_JSON_storageSET SERDE 'com.example.JSONSerDE' WITH serdeproperties('prop1'='value1','prop2'='value2'); serdeproperties 中的屬性會被傳遞給 SerDe 模塊；alter talbe table_useing_JSON_storage SET SERDEPROPERTIES('prop3'='value3','prop4'='value4');為已經存在著的 SerDe 增加新的SERDEPROPERTIES 屬性；alter table stocks clustered by (exchange,symbol) sorted by(symbol) into 48 BICLETS;修改數據分桶表；alter table log_msg TOUCH partition(year=2015,month=1,day=1);當表中存儲的文件在 Hive 之外被修改了，就會觸發鉤子的執行；alter table log_msg archive partiton(year=2015,month=1,day=1);會將這個分區內的文件打成一個 Hadoop（HAR） 壓縮包，這樣僅僅可以降低文件系統中的文件數據以及減輕 NameNode 的壓力，而不會減少任何的存儲空間；使用 UNARCHIVE 可以反向操作（只能用于分區表中獨立的分區）；alter table log_msg archive partiton(year=2015,month=1,day=1) ENABLE NO_DROP; --防止被刪除 DISENABLE 反向操作 只能用于分區表alter table log_msg archive partiton(year=2015,month=1,day=1) ENABLE NO_OFFLINE; --防止被查詢 DISENABLE 反向操作只能用于分區表
