文稿hive學(xué)習(xí)筆記

1、目錄HIVE 結(jié)構(gòu)61.1.11.21.31.4HIVE 架構(gòu)6Hive 和 Hadoop 關(guān)系7Hive 和普通關(guān)系數(shù)據(jù)庫的異同8HIVE 元數(shù)據(jù)庫91.4.11.4.2DERBY9101.51.6HIVE 的數(shù)據(jù)11其它HIVE 操作11HIVE 基本操作122.2.1create table122.1.12.1.22.1.32.1.42.1.5總述12語法12基本例子14創(chuàng)建分區(qū)15其它例子162.2Alter Table172.2.12.2.22.2.32.2.42.2.5Add Partitions17Drop Partitions17Rename Table17Change Col

2、umn18Add/Replace Columns182.32.42.52.6Create View18Show19Load19Insert212.6.1Inserting datao Hive Tables from queries212.6.2Writing datao filesystem from queries222.7Cli232.7.12.7.22.7.32.7.4HiveHived line Options23eractive Sd24Hive Resour25等語言26調(diào)用、s2.82.9DROP27其它272.9.12.9.22.9.3Limit27Top k27REGEX

3、Column Specification273.Hive Select283.13.2Group By28Order /Sort By294.5.6.Hive Join29HIVE 參數(shù)設(shè)置32HIVE UDF336.1基本函數(shù)33關(guān)系操作符33代數(shù)操作符35邏輯操作符36復(fù)雜類型操作符36內(nèi)建函數(shù)36數(shù)學(xué)函數(shù)36集合函數(shù)36類型轉(zhuǎn)換36日期函數(shù)36條件函數(shù)37字符串函數(shù)376.1.16.1.26.1.36.1.46.1.56.1.66.1.76.1.86.1.96.1.106.1.116.2UDTF396.2.1Explode40HIVE 的MAP/REDUCE417.7.17.27.3JO

4、IN41GROUP BY42DISTINCT42使用HIVE8.438.18.28.38.48.58.68.78.8字符集43壓縮43count(distinct)43JOIN43DML 操作44ING44子查詢44Join 中處理null 值的語義區(qū)別44優(yōu)化與技巧479.9.1全排序479.1.1例 1489.1.2例 2519.29.39.49.59.69.79.8怎樣做積54怎樣寫 exist/in 子句54怎樣決定 reducer 個數(shù)55合并 MapReduce 操作55Bucket 與 sampling56Partition57JOIN58JOIN 原則58Map Join58大

5、表Join 的數(shù)據(jù)偏斜609.8.19.8.29.8.3合并小文件62Group By6210.HIVE FAQ：621. HIVE 結(jié)構(gòu)Hive 是建立在 Hadoop 上的數(shù)據(jù)倉庫基礎(chǔ)構(gòu)架。它提供了一系列的工具，可以用來進行數(shù)據(jù)提取轉(zhuǎn)化加載（ETL），這是一種可以、查詢和分析在 Hadoop 中的大規(guī)模數(shù)據(jù)的機制。Hive 定義了簡單的類 SQL 查詢語言，稱為 QL，它允許熟悉 SQL 的用戶查詢數(shù)據(jù)。同時，這個語言也允許熟悉 MapReduce 開發(fā)者的開發(fā)自定義的 mapper 和 reducer 來處理內(nèi)建的 mapper 和 reducer 無法完成的復(fù)雜的分析工作。1.1HIV

6、E 架構(gòu)Hive 的結(jié)構(gòu)可以分為以下幾部分：用戶接口：包括 CLI, Cnt, WUI在關(guān)系數(shù)據(jù)庫如元數(shù)據(jù)。通常是, derby 中解釋器、編譯器、優(yōu)化器、執(zhí)行器Hadoop：用 HDFS 進行，利用 MapReduce 進行計算1、 用戶接口主要有三個：CLI，Cnt 和 WUI。其中最常用的是 CLI，Cli 啟動的時候，會同時啟動一個 Hive 副本。Cnt 是 Hive 的客戶端，用戶連接至 Hive Server。 Hive Server 所在節(jié)點，并且在該節(jié)點啟動 HiveHive。在啟動 Cnt 模式的時候，需要Server。 WUI 是通過瀏覽器2、 Hive 將元數(shù)據(jù)在數(shù)據(jù)庫

7、中，如、derby。Hive 中的元數(shù)據(jù)包括表的名字，表的列和分區(qū)及其屬性，表的屬性（是否為外部表等），表的數(shù)據(jù)所在目錄等。3、 解釋器、編譯器、優(yōu)化器完成 HQL 查詢語句從詞法分析、語法分析、編譯、優(yōu)化以及查詢計劃的生成。生成的查詢計劃在 HDFS 中，并在隨后有 MapReduce 調(diào)用執(zhí)行。4、 Hive 的數(shù)據(jù)在 HDFS 中，大部分的查詢由 MapReduce 完成（包含 * 的查詢，比如 select * from tbl 不會生成 MapRedcue 任務(wù)）。1.2Hive 和 Hadoop 關(guān)系Hive 構(gòu)建在 Hadoop 之上，HQL 中對查詢語句的解釋、優(yōu)化、生成查詢計

8、劃是由 Hive 完成的所有的數(shù)據(jù)都是在 Hadoop 中查詢計劃被轉(zhuǎn)化為 MapReduce 任務(wù)，在 Hadoop 中執(zhí)行（有些查詢沒有 MR 任務(wù)，如：select * from table）Hadoop 和 Hive 都是用 UTF-8 編碼的1.3Hive和普通關(guān)系數(shù)據(jù)庫的異同HiveRDBMS查詢語言 數(shù)據(jù)HDFSRaw Device or Local FS索引無有執(zhí)行MapReduceExcutor執(zhí)行延遲處理數(shù)據(jù)規(guī)模大小1.查詢語言。由于 SQL 被廣泛的應(yīng)用在數(shù)據(jù)倉庫中，因此，專門針對 Hive 的特性設(shè)計了類 SQL 的查詢語言 HQL。熟悉 SQL 開發(fā)的開發(fā)者可以很方便

9、的使用 Hive 進行開發(fā)。2.數(shù)據(jù)位置。Hive 是建立在 Hadoop 之上的，所有 Hive 的數(shù)據(jù)都是在 HDFS 中的。而數(shù)據(jù)庫則可以將數(shù)據(jù)保存在塊設(shè)備或者本地文件系統(tǒng)中。數(shù)據(jù)格式。Hive 中沒有定義專門的數(shù)據(jù)格式，數(shù)據(jù)格式可以由用戶指定，用戶定義數(shù)據(jù)格式需要指定三個屬性：列分隔符（通常為空格、”t”、”x001）、行分隔符3.（”n”）以及文件數(shù)據(jù)的方法（Hive 中默認有三個文件格式TextFile，SequenceFile 以及 RCFile）。由于在加載數(shù)據(jù)的過程中，不需要從用戶數(shù)據(jù)格式到Hive 定義的數(shù)據(jù)格式的轉(zhuǎn)換，因此，Hive 在加載的過程中不會對數(shù)據(jù)本身進行任何修

10、改，而只是將數(shù)據(jù)內(nèi)容或者移動到相應(yīng)的 HDFS 目錄中。而在數(shù)據(jù)庫中，不同的數(shù)據(jù)庫有不同的引擎，定義了自己的數(shù)據(jù)格式。所有數(shù)據(jù)都會按照一定的組織，因此，數(shù)據(jù)庫加載數(shù)據(jù)的過程會比較耗時。數(shù)據(jù)更新。由于 Hive 是針對數(shù)據(jù)倉庫應(yīng)用設(shè)計的，而數(shù)據(jù)倉庫的內(nèi)容是讀多寫少的。因此，Hive 中不支持對數(shù)據(jù)的改寫和添加，所有的數(shù)據(jù)都是在加載的時候中確定好的。而數(shù)據(jù)庫中的數(shù)據(jù)通常是需要經(jīng)常進行修改的，因此可以使用 INSERTO .VALUES 添加數(shù)據(jù)，使用 UPDATE . SET 修改數(shù)據(jù)。索引。之前已經(jīng)說過，Hive 在加載數(shù)據(jù)的過程中不會對數(shù)據(jù)進行任何處理，甚至不會4.5.對數(shù)據(jù)進行掃描，因此也沒

11、有對數(shù)據(jù)中的某些 Key 建立索引。Hive 要數(shù)據(jù)中滿足條件的特定值時，需要掃描整個數(shù)據(jù)，因此延遲較高。由于 MapReduce 的引入， Hive 可以并行數(shù)據(jù)，因此即使沒有索引，對于大數(shù)據(jù)量的，Hive 仍然可以體現(xiàn)出優(yōu)勢。數(shù)據(jù)庫中，通常會針對一個或者幾個列建立索引，因此對于少量的特定條件的數(shù)據(jù)的，數(shù)據(jù)庫可以有很高的效率，較低的延遲。由于數(shù)據(jù)的延遲較高，決定了 Hive 不適合數(shù)據(jù)查詢。6.執(zhí)行。Hive 中大多數(shù)查詢的執(zhí)行是通過 Hadoop 提供的 MapReduce 來實現(xiàn)的（類似select * from tbl 的查詢不需要 MapReduce）。而數(shù)據(jù)庫通常有自己的執(zhí)行引擎。

12、批注 1:高低HQLSQL7.執(zhí)行延遲。之前提到，Hive 在查詢數(shù)據(jù)的時候，由于沒有索引，需要掃描整個表，因此延遲較高。另外一個導(dǎo)致 Hive 執(zhí)行延遲高的是 MapReduce 框架。由于MapReduce 本身具有較高的延遲，因此在利用 MapReduce 執(zhí)行 Hive 查詢時，也會有較高的延遲。相對的，數(shù)據(jù)庫的執(zhí)行延遲較低。當(dāng)然，這個低是有條件的，即數(shù)據(jù)規(guī)模較小，當(dāng)數(shù)據(jù)規(guī)模大到超過數(shù)據(jù)庫的處理能力的時候，Hive 的并行計算顯然能體現(xiàn)出優(yōu)勢?？蓴U展性。由于 Hive 是建立在 Hadoop 之上的，因此 Hive 的可擴展性是和 Hadoop的可擴展性是一致的（世界上最大的 Hado

13、op 集群在 Yahoo!，2009 年的規(guī)模在 4000 臺節(jié)點左右）。而數(shù)據(jù)庫由于 ACID 語義的嚴格限制，擴展行非常有限。目前最先進的并行數(shù)據(jù)庫 Oracle 在理論上的擴展能力也只有 100 臺左右。數(shù)據(jù)規(guī)模。由于 Hive 建立在集群上并可以利用 MapReduce 進行并行計算，因此可以支持很大規(guī)模的數(shù)據(jù)；對應(yīng)的，數(shù)據(jù)庫可以支持的數(shù)據(jù)規(guī)模較小。8.9.1.4HIVE 元數(shù)據(jù)庫Hive 將元數(shù)據(jù)在 RDBMS 中,一般常用的有和 DERBY。1.4.1 DERBY啟動HIVE 的元數(shù)據(jù)庫 進入到hive 的安裝目錄 Eg:1、啟動 derby 數(shù)據(jù)庫/home/admin/caon

14、a/hive/build/dist/運行 startNetworkServer -h 2、連接 Derby 數(shù)據(jù)庫進試查看/home/admin/caona/hive/build/dist/conf/hive-default.xml。找到j(luò)avax.jdo.option.ConnectionURLjdbc:derby:/hadoop1:1527/meta_db;create=trueJDBC connect string for a JDBC meta進入derby 安裝目錄/home/admin/caona/hive/build/dist/db-derby-bin/bin輸入./ijConn

15、ect jdbc:derby:/hadoop1:1527/meta_db;create=true;3、元數(shù)據(jù)庫數(shù)據(jù)字典表名說明關(guān)聯(lián)鍵BUCKETING_COLSCOLUMNSHive 表字段信息(字段注釋，字段名，字段類型，字段序號)SD_ID從上面幾張表的內(nèi)容來看，hive 整個創(chuàng)建表的過程已經(jīng)比較清楚了1.用戶提交 hive 語句，對其進行，分解為表、字段、分區(qū)等 hive 對象2. 根據(jù)到的信息構(gòu)建對應(yīng)的表、字段、分區(qū)等對象，從 SEQUENCE_TABLE 中獲取構(gòu)建對象的最新 ID，與構(gòu)建對象信稱，類型等)一同通過方法寫入到元數(shù)據(jù)表中去，成功后將 SEQUENCE_TABLE 中對應(yīng)

16、的最新 ID+5。實際上常見的 RDBMS 都是通過這種方法進行組織的，典型的如tgresql，其系統(tǒng)表中和hive 元數(shù)據(jù)一樣 露了這些 id 信息(oid,cid 等),而 Oracle 等商業(yè)化的系統(tǒng)則隱藏了這些具體的ID。通過這些元數(shù)據(jù)可以很容易的讀到數(shù)據(jù)諸如創(chuàng)建一個表的數(shù)據(jù)字典信息，比如導(dǎo)出建表語名等。導(dǎo)出建表語句的s見附一 待完成1.4.2將存放元數(shù)據(jù)的 Derby 數(shù)據(jù)庫遷移到步驟：數(shù)據(jù)庫DBS元數(shù)據(jù)庫信息，存放 HDFS 路徑信息DB_IDPARTITION_KEYSHive 分區(qū)表分區(qū)鍵PART_IDSDS所有hive 表、表分區(qū)所對應(yīng)的 hdfs 數(shù)據(jù)目錄和數(shù)據(jù)格式。SD_

17、ID,SERDE_IDSD_PARAMS序列化反序列化信息，如行分隔符、列分隔符、NULL 的表示字符等SERDE_IDSEQUENCE_TABLESEQUENCE_TABLE 表保存了hive 對象的下一個可用ID，如.apache.hadoop.hive.meta.m.MTable, 21，則下一個新創(chuàng)建的hive 表其 TBL_ID 就是 21，同時 SEQUENCE_TABLE 表中 271786 被更新為 26(這里每次都是+5?)。同樣，COLUMN，PARTITION 等都有相應(yīng)的SERDESSERDE_PARAMSSORT_COLSTABLE_PARAMS表級屬性，如是否外部表

18、，表注釋等TBL_IDTBLS所有 hive 表的基本信息TBL_ID,SD_ID1.5HIVE 的數(shù)據(jù)首先，Hive 沒有專門的數(shù)據(jù)格式，也沒有為數(shù)據(jù)建立索戶可以非常的組織 Hive 中的表，只需要在創(chuàng)建表的時候告訴 Hive 數(shù)據(jù)中的列分隔符和行分隔符，Hive就可以數(shù)據(jù)。其次，Hive 中所有的數(shù)據(jù)都Table，Partition，Bucket。在 HDFS 中，Hive 中包含以下數(shù)據(jù)模型：Table，External1.Hive 中的 Table 和數(shù)據(jù)庫中的 Table 在概念上是類似的，每一個 Table 在 Hive中都有一個相應(yīng)的目錄數(shù)據(jù)。例如，一個表 xiaojun，它在

19、HDFS 中的路徑為：/ warehouse /xiaojun，其中，wh 是在 hive-site.xml 中由$hive.meta.warehouse.dir 指定的數(shù)據(jù)倉庫的目錄，所有的 Table 數(shù)據(jù)（不包括 External Table）都保存在這個目錄中。Partition 對應(yīng)于數(shù)據(jù)庫中的 Partition 列的密集索引，但是 Hive 中 Partition 的組織方式和數(shù)據(jù)庫中的很不相同。在 Hive 中，表中的一個 Partition 對應(yīng)于表下的一2.個目錄，所有的 Partition 的數(shù)據(jù)都在對應(yīng)的目錄中。例如：xiaojun 表中包含 dt和 city 兩個 P

20、artition，則對應(yīng)于 dt = 20100801, ctry = US 的 HDFS 子目錄為：/ warehouse /xiaojun/dt=20100801/ctry=US；對應(yīng)于 dt = 20100801, ctry = CA 的 HDFS子目錄為；/ warehouse /xiaojun/dt=20100801/ctry=CABuckets 對指定列計算 hash，根據(jù) hash 值切分數(shù)據(jù)，目的是為了并行，每一個 Bucket 對應(yīng)一個文件。將 user 列分散至 32 個 bucket，首先對 user 列的值計算 hash，對應(yīng) hash 值為 0 的 HDFS 目錄為：

21、/ warehouse /xiaojun/dt=20100801/ctry=US/part-00000；hash 值為 20 的 HDFS 目錄為：/ warehouse/xiaojun/dt =20100801/ctry=US/part-00020External Table 指向已經(jīng)在 HDFS 中存在的數(shù)據(jù)，可以創(chuàng)建 Partition。它和 Table3.4.在元數(shù)據(jù)的組織上是相同的，而實際數(shù)據(jù)的則有較大的差異。Table 的創(chuàng)建過程和數(shù)據(jù)加載過程（這兩個過程可以在同一個語句中完成），在加載數(shù)據(jù)的過程中，實際數(shù)據(jù)會被移動到數(shù)據(jù)倉庫目錄中；之后對數(shù)據(jù)對將會直接在數(shù)據(jù)倉庫目錄中完成。刪除表

22、時，表中的數(shù)據(jù)和元數(shù)據(jù)將會被同時刪除。External Table 只有一個過程，加載數(shù)據(jù)和創(chuàng)建表同時完成（CREATE EXTERNALTABLE LOCATION），實際數(shù)據(jù)是在 LOCATION 后面指定的 HDFS 路徑中，并不會移動到數(shù)據(jù)倉庫目錄中。當(dāng)刪除一個 External Table 時，僅刪除1.6其它 HIVE 操作1、 啟動 HIVE 的WEB 的界面sh $HIVE_HOME/bin/hive -service hwi2、查看 HDFS 上的文件數(shù)據(jù)hadoop fs -text /user/admin/daiqf/createspu_fp/inpateinfo |he

23、ad2. HIVE 基本操作2.1create table2.1.1 總述CREATE TABLE 創(chuàng)建一個指定名字的表。如果相同名字的表已經(jīng)存在，則拋出異常；用戶可以用 IF NOT EXIST 選項來忽略這個異常。EXTERNAL 關(guān)鍵字可以讓用戶創(chuàng)建一個外部表，在建表的同時指定一個指向?qū)嶋H數(shù)據(jù)的路徑（LOCATION），Hive 創(chuàng)建內(nèi)部表時，會將數(shù)據(jù)移動到數(shù)據(jù)倉庫指向的路徑；若創(chuàng)建外部表，僅數(shù)據(jù)所在的路徑，不對數(shù)據(jù)的位置做任何改變。在刪除表的時候，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會被一起刪除，而外部表只刪除元數(shù)據(jù)，不刪除數(shù)據(jù)。LIKE 允許用戶現(xiàn)有的表結(jié)構(gòu)，但是不數(shù)據(jù)。用戶在建表的時候可以。如果沒

24、有指定 ROWFORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe確定表的具體的列的數(shù)據(jù)。如果文件數(shù)據(jù)是純文本，可以使用有分區(qū)的表可以在創(chuàng)建的時候使用 PARTITIONED BY 語句。一個表可以擁有一個或者多個分區(qū)，每一個分區(qū)單獨存在一個目錄下。而且，表和分區(qū)都可以對某個列進行中。也可以利用SORT BY 對數(shù)據(jù)進行排序。這樣可以為特定應(yīng)用提高性能。表名和列名不區(qū)分大小寫，SerDe 和屬性名區(qū)分大小寫。表和列的注釋是字符串。2.1.2 語

25、法CLUSTERED BY 操作，將若干個列放入一個桶（bucket）自定義 SerDe 或者使用自帶的 SerDeCREATE EXTERNAL TABLE IF NOT EXISTS table_name(col_name data_type COMMENTment, .) COMMENTmentPARTITIONED BY (col_name data_type COMMENTment, .) CLUSTERED BY (col_name, col_name, .) SORTED BY (col_nameASC|DESC, .)O num_buckets BUCKETSROW FORMA

26、T row_format D AS file_format批注 5: ？批注 3: ？D AS SEQUENCE。批注 4: ？D AS TEXTFILE。如果數(shù)據(jù)需要壓縮，使用批注 2: serde 什么意思|ame WITH SERDEPROPERTIES(.) (Note: only available starting with 0.6.0)LOCATION hdfs_pathTBLPROPERTIES (property_name=property_value, .) (Note: only available starting with 0.6.0)Aect_sement (Not

27、e: this feature is only available starting with 0.5.0.)CREATE EXTERNAL TABLE IF NOT EXISTS table_nameLIKE existing_table_name LOCATION hdfs_pathdata_type: primitive_type| array_type| map_type| struct_typeprimitive_type: TINY| SMALL| BIG| FLOAT| DOUBLE| STRINGarray_type: ARRAY map_type: MAP struct_ty

28、pe: STRUCT row_format: DELIMITED FIELDS TERMINATED BY char COLLECTION ITEMS TERMINATEDBY charMAP KEYS TERMINATED BY char LINES TERMINATED BY char| SERDE serde_name WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, .)D BY storage.handler.cla目前在hive 中常用的數(shù)據(jù)類型有:BIG 主要用于狀態(tài)

29、,類別,數(shù)量的字段, 如sus/option/type/tyDOUBLE 主要用于金額的字段, 如fee/price/bidSTRING 除上述之外的字段基本都使用String, 尤其是id 和日期時間這樣的字段2.1.3 基本例子1、如果一個表已經(jīng)存在，可以使用 if not exists2、 create table xiaojun(id,cont string) row format delimited fields terminatedby 005d as textfile;terminated by：關(guān)于來源的文本數(shù)據(jù)的字段間隔符如果要將自定義間隔符的文件讀入一個表，需要通過創(chuàng)建表的

30、語句來指明輸入文件間隔符，然后 load data 到這個表。4、Alibaba 數(shù)據(jù)庫常用間隔符的的常用間隔符一般是 Ascii 碼 5，Ascii 碼 7 等。在 hive 中 Ascii 碼 5 用005表示，Ascii 碼 7 用007表示，依此類推。5、裝載數(shù)據(jù)查看一下：Hadoop fs -lsLOAD DATA INPATH /user/admin/xiaojun/a.txt OVERWRITE 6、如果使用 external 建表和普通建表區(qū)別A、指定一個位置，而不使用默認的位置。如：O TABLE xiaojun;create EXTERNAL table xiaojun(i

31、d,cont string) row format delimited fieldsterminated by 005d as textfile location /user/admin/xiaojun/;check 結(jié)果ij select LOCATION from tbls a,sds b where a.sd_id=b.sd_id and tbl_name=xiaojun;LOCATIONhdfs:/hadoop1:7000/user/admin/xiaojunfile_format: SEQUENCEFILE| TEXTFILE| RCFILE(Note: only available

32、 starting with 0.6.0)| INPUTFORMAT input_format_claame OUTPUTFORMAToutput_format_claameij select LOCATION from tbls a,sds b where a.sd_id=b.sd_id and tbl_name=c;LOCATIONhdfs:/hadoop1:7000/user/hive/warehouse/cB、對于使用 create table external 建表完成后，再drop 掉表，表中的數(shù)據(jù)還在文件系統(tǒng)中。如：hive create EXTERNAL table xiaoj

33、un(id,cont string) row format delimitedfields terminated by 005d as textfile;OKhive LOAD DATA INPATH /user/admin/xiaojun OVERWRITEO TABLE xiaojun;Loading daOKo table xiaojunhive drop table xiaojun;OKadminhadoop1 bin$ ./hadoop fs -ls hdfs:/hadoop1:7000/user/hive/warehouse/xiaojun Found 1 items使用普通的建表

34、DROP 后則找不到2.1.4 創(chuàng)建分區(qū)HIVE 的分區(qū)通過在創(chuàng)建表時啟用 partition by 實現(xiàn)，用來 partition 的維度并不是實際數(shù)據(jù)的某一列，具體分區(qū)的標(biāo)志是由內(nèi)容時給定的。當(dāng)要查詢某一分區(qū)的內(nèi)容時可以采用where 語句，形似 where tablename.partition_key a 來實現(xiàn)。創(chuàng)建含分區(qū)的表。命令原型：CREATE TABLE page_view(viewTime, userid BIG,page_url STRING, referrer_url STRING,ip STRING COMMENT IP Address of the User) CO

35、MMENT This is the page view table PARTITIONED BY(dt STRING, country STRING) CLUSTERED BY(userid) SORTED BY(viewTime) O 32 BUCKETSROW FORMAT DELIMITEDFIELDS TERMINATED BY 001Eg:建表：CREATE TABLE c02_clicks_fatdt1(yyyymmdd idip country_idpage_idstring,string, string, string, string ,string, stringclicks

36、_url_idquery_stringrefer)PARTITIONED BY(dt STRING)row format delimited fields terminated by 005d as textfile;裝載數(shù)據(jù)：LOAD/user/aO TABLE c02_clicksDATAClicks/20101101/19/clicks_fatdt1INPATH_gp_fatdt0/0 OVERWRITEPARTITION(dt=20101101);某一個分區(qū)SELECT count(*) FROM c02_clicks_fatdt1 aWHERE a.dt = 20101101 AND

37、 a.dt 20101102;2.1.5 其它例子1、指定 LOCATION 位置2、一個空表CREATE EXTERNAL TABLE page_view(viewTime, userid BIG, page_url STRING, referrer_url STRING,ip STRING COMMENT IP Address of the User, country STRING COMMENT country of origination)COMMENT This is the staging page view table ROW FORMAT DELIMITED FIELDS TE

38、RMINATED BY 054D AS TEXTFILELOCATION ;COLLECTION ITEMS TERMINATED BY 002 MAP KEYS TERMINATED BY 003D AS SEQUENCEFILE;2.2Alter Table2.2.1 Add PartitionsEg:ALTER TABLE c02_clicks PARTITION_fatdt1 ADD(dt=20101202)location/user/hive/warehouse/c02_clicks PARTITION/user/hive/warehouse/c02_clicks_fatdt1/pa

39、rt20101202 (dt=20101203)_fatdt1/part20101203;location2.2.2 Drop PartitionsALTER TABLE table_name DROP partition_spec, partition_spec,.ALTER TABLE c02_clicks_fatdt1 DROP PARTITION (dt=20101202);2.2.3 Rename TableALTER TABLE table_name RENAME T_table_name這個命令可以讓用戶為表更名。數(shù)據(jù)所在的位置和分區(qū)名并不改變。換而言之，老的表名并未“”，對老表

40、的更改會改變新表的數(shù)據(jù)。ALTER TABLE table_name ADD IF NOT EXISTS partition_spec LOCATION location1 partition_spec LOCATION location2 .partition_spec: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, .)CREATE TABLE empty_key_value_LIKE key_value_;2.2.4 Change ColumnALTER TABLE

41、table_name CHANGE COLUMN col_old_name col_new_namecolumn_type COMMENTment |AFTER column_name這個命令可以允許改變列名、數(shù)據(jù)類型、注釋、列位置或者它們的任意組合Eg:2.2.5 Add/Replace ColumnsALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type COMMENT ment, .)ADD 是代表新增一字段，字段位置在所有列后面(partition 列前);REPLACE 則是表示替換表中所有字段。Eg:hive d

42、esc xi; OKidcontstringdw_ins_datestringTime taken: 0.061 secondshive create table xibak like xi; OKTime taken: 0.157 secondshive alter table xibak replace columns (ins_date string); OKTime taken: 0.109 seconds hive desc xibak;OKins_datestring2.3Create ViewCREATE VIEW IF NOT EXISTS view_name (column_

43、name COMMENT ment, .) COMMENTmentAECT .TBLPROPERTIES (property_name = property_value, .)批注 6: ？2.4Show查看表名查看表名，部分匹配查看某表的所有 Partition，如果沒有就報錯：查看某表結(jié)構(gòu)：查看分區(qū)內(nèi)容查看有限行內(nèi)容，同Greenplum，用 limit查看表分區(qū)定義2.5LoadHIVE 裝載數(shù)據(jù)沒有做任何轉(zhuǎn)換加載到表中的數(shù)據(jù)只是進入相應(yīng)的配置單元表的位置移動數(shù)據(jù)文件。純加載操作/移動操作。3.1 語法Load 操作只是單純的/移動操作，將數(shù)據(jù)文件移動到 Hive 表對應(yīng)的位置。file

44、path 可以是：o 相對路徑，例如：project/data1LOAD DATA LOCAL INPATH filepath OVERWRITEO TABLE tablenamePARTITION (partcol1=val1, partcol2=val2 .)批注 8: 什么意思？DESCRIBEEXTENDEDpage_vieRTITION (ds=2008-08-08);SELECT a.foo FROM invites a limit 3;批注 7: 是列還是其他？SELECT a.foo FROM invites a WHERE a. =2008-08-15;dsDESCRIBE

45、invites;SHORTITIONS page_view;SHOW TABLES page.*; SHOW TABLES .*view;SHOW TABLES;絕對路徑，例如： /user/hive/project/data1包含模式的完整 URI，例如： hdfs:/namenode:9000/user/hive/project/data1加載的目標(biāo)可以是一個表或者分區(qū)。如果表包含分區(qū)，必須指定每一個分區(qū)的分區(qū)名。filepath 可以一個文件（這種情況下，Hive 會將文件移動到表所對應(yīng)的目錄中）或者是一個目錄（在這種情況下，Hive 會將目錄中的所有文件移動至表所對應(yīng)的目錄中）。如果指

46、定了 LOCAL，那么：load 命令會去查找本地文件系統(tǒng)中的 filepath。如果發(fā)現(xiàn)是相對路徑，則路徑會被解釋為相對于當(dāng)前用戶的當(dāng)前路徑。用戶也可以為本地文件指定一個完整的 URI，比如： file:/user/hive/project/data1.load 命令會將 filepath 中的文件到目標(biāo)文件系統(tǒng)中。目標(biāo)文件系統(tǒng)由表的位置屬性決定。被對應(yīng)的位置。的數(shù)據(jù)文件移動到表的數(shù)據(jù)如果沒有指定 LOCAL 關(guān)鍵字，如果 filepath 指向的是一個完整的 URI， hive 會直接使用這個 URI。 否則：如果沒有指定 schema 或者 authority，Hive 會使用在 had

47、oop配置文件中定義的 schema 和 authority， 指定了 Namenode 的 URI。如果路徑不是絕對的，Hive 相對于 /user/ 進行解釋。Hive 會將 filepath 中指定的文件內(nèi)容移動到 table （或者partition）所指定的路徑中。如果使用了 OVERWRITE 關(guān)鍵字，則目標(biāo)表（或者分區(qū)）中的內(nèi)容（如果有）會被刪除，然后再將 filepath 指向的文件/目錄中的內(nèi)容添加到表/分區(qū)中。如果目標(biāo)表（分區(qū)）已經(jīng)有一個文件，并且文件名和 filepath 中的文件名，那么現(xiàn)有的文件會被新文件所替代。從本地導(dǎo)入數(shù)據(jù)到表格并追加原表從本地導(dǎo)入數(shù)據(jù)到表格并追加

48、從hdfs 導(dǎo)入數(shù)據(jù)到表格并覆蓋原表LOAD DATA INPATH/user/aClicks/20101101/18/clicks_gp_fatdt0/0O table c02_clicks_fatdt1 OVERWRITE PARTITION (dt=20101201);LOAD DATA LOCAL INPATH ./examples/files/kv1.txtO TABLE pokes;LOAD DATA LOCAL INPATH /tmp/pv_2008-06-08_us.txtO TABLE c02PARTITION(date=2008-06-08, country=US)關(guān)于來源

49、的文本數(shù)據(jù)的字段間隔符如果要將自定義間隔符的文件讀入一個表，需要通過創(chuàng)建表的語句來指明輸入文件間隔符，然后 load data 到這個表就ok 了。2.6Insert2.6.1 Inserting datao Hive Tables from queriesStandard syntax:INSERT OVERWRITE TABLE tablename1 PARTITION (partcol1=val1,partcol2=val2 .) select_sement1 FROM from_sementHive exten FROM from_s(multiple inserts): ementI

50、NSERT OVERWRITE TABLE tablename1 PARTITION (partcol1=val1,partcol2=val2 .) select_sement1INSERTOVERWRITE TABLE tablename2 PARTITION .select_sement2 .Hive exten(dynamic partition inserts):INSERT OVERWRITE TABLE tablename PARTITION (partcol1=val1,partcol2=val2 .) select_sement FROM from_sementInsert 時

51、，from 子句既可以放在 select 子句后，也可以放在 insert 子句前，下面兩句是等價的hive FROM invites a INSERT OVERWRITE TABLE event count(*) WHERE a.foo 0 GROUP BY a.bar;ECT a.bar,hive INSERT OVERWRITE TABLE event a WHERE a.foo 0 GROUP BY a.bar;ECT a.bar, count(*) FROM inviteshive 沒 有 直 接 條 數(shù) 據(jù) 的 sql ， 不 過 可 以 通 過 其 他 方 法 實 現(xiàn) ：假設(shè)有一

52、張表 B 至少有一條數(shù)據(jù)，想向表 A（法，string）中實一條數(shù)據(jù)，可以用下面from的方現(xiàn)：Binsert table A select 1，abc limit 1；我覺得 hive 好像不能夠一個，因為每次你寫 insert 語句的時候都是要將整個表的值overwrite。這個應(yīng)該是與 hive 的 storage layer 是有關(guān)系的，因為它的層是 HDFS，一個數(shù)據(jù)要全表掃描，還不如用整個表的替換來的快些。Hive 不支持一條一條的用 insert 語句進行操作，也不支持 update 的操作。數(shù)據(jù)是以 load的方式，加載到建立好的表中。數(shù)據(jù)一旦導(dǎo)入，則不可修改。要么 drop

53、掉整個表，要么建立新的表，導(dǎo)入新的數(shù)據(jù)。2.6.2 Writing datao filesystem from queries導(dǎo)出文件到本地導(dǎo)出文件到HDFS一個源可以同時到多個目標(biāo)表或目標(biāo)文件，多目標(biāo) insert 可以用一句話來完成Eg:from xiinsert overwrite insert overwritetable test2 select 1,2,3 limit 1table d select 4,5,6 limit 1;FROM srcINSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key =100 and src.k

54、ey = 200 and src.key = 300;INSERT OVERWRITE DIRECTORY/user/aClicks/20101101/19/clicks_gp_fatdt0/0SELECT a.* FROM c02_clicks_fatdt1 a WHERE dt=20101201;INSERT OVERWRIOCAL DIRECTORY /tmp/local_out SELECT a.* FROM pokesa;Standard syntax:INSERT OVERWRITE LOCAL DIRECTORY directory1 SELECT . FROM .Hive ex

55、ten(multiple inserts):FROM from_sementINSERT OVERWRITE LOCAL DIRECTORY directory1 select_sement1INSERT OVERWRITE LOCAL DIRECTORY directory2 select_sement2 .2.7Cli2.7.1 Hived line Options$HIVE_HOME/bin/hive 是一個s詢。 語法：工具，它可以用來運行于交互或批處理方式配置單元查運行一個查詢：Example of setting hive configuration variables將查詢結(jié)果導(dǎo)

56、出到一個文件批注 10:$HIVE_HOME/bin/hive -e select a.col from tab1 a -hiveconfhive.exec.scratchdir=/home/my/hive_scratch -hiveconfmapred.reduce.tasks=32$HIVE_HOME/bin/ hive -e select count(*) from c02_clicks_fatdt1Usage: hive -hiveconf x=y* * | -S-i Initialization Sql from file (executedautomatically and sil

57、ently before any otherds)-e quoted query string Sql fromd line-f Sql from file-SSilent mode ineractive swhere onlydata is emitted-hiveconf x=yUse this to set hive/hadoop configuration variables.-e and -f cannot be specified together.he absence of these options,eractive sis started.However, -i can be

58、 used winy other options. Multiple instan of -i can be used to execute multiple init scripts.To see this usage help, run hive -h批注 9: ？運行一個Example of running an initialization script before entering eractive mode2.7.2 HiveeractiveSdEg:hive set i=32; hive set i;hive select a.* from xiaojun a; hive !l

59、s;dDescriptionquit使用 quit or exit 退出set =使用這個方式來設(shè)置特定的配置變量的值。有一點需要注意的是，如果你拼錯了變量名，CLI 將不會顯示錯誤。set這將打印的配置變量，如果沒有指定變量則由顯示HIVE和用戶變量。如set I 則顯示i 的值,set 則顯示hive內(nèi)部變量值set -vThis will give allsible hadoop/hive configuration variables.add FILE *Adds a file to the list of resour.list FILElist all the resouralre

60、ady addedlist FILE *Check given resourare already added or not.! execute a sd from hive sdfs execute dfsdd from hive sexecutes hive query and prs results to stdoutHIVE_HOME/bin/hive -i /home/my/hive-init.sqlHIVE_HOME/bin/hive -f /home/my/hive-script.sqlHIVE_HOME/bin/hive -S -e select count(*) from c