Hive基本命令整理

標籤：des   http   java   使用   檔案   資料   

建立表： hive> CREATE TABLE pokes (foo INT, bar STRING);         Creates a table called pokes with two columns, the first being an integer and the other a string

建立一個新表，結構與其他一樣 hive> create table new_table like records;

建立分區表： hive> create table logs(ts bigint,line string) partitioned by (dt String,country String);

載入分區表資料： hive> load data local inpath ‘/home/hadoop/input/hive/partitions/file1‘ into table logs partition (dt=‘2001-01-01‘,country=‘GB‘);

展示表中有多少分區： hive> show partitions logs;

展示所有表： hive> SHOW TABLES;         lists all the tables hive> SHOW TABLES ‘.*s‘;

lists all the table that end with ‘s‘. The pattern matching follows Java regular expressions. Check out this link for documentation http://java.sun.com/javase/6/docs/api/java/util/regex/Pattern.html

顯示表的結構資訊 hive> DESCRIBE invites;         shows the list of columns

更新表的名稱： hive> ALTER TABLE source RENAME TO target;

添加新一列 hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT ‘a comment‘);   刪除表： hive> DROP TABLE records; 刪除表中資料，但要保持表的結構定義 hive> dfs -rmr /user/hive/warehouse/records;

從本地檔案載入資料： hive> LOAD DATA LOCAL INPATH ‘/home/hadoop/input/ncdc/micro-tab/sample.txt‘ OVERWRITE INTO TABLE records;

顯示所有函數： hive> show functions;

查看函數用法： hive> describe function substr;

查看數組、map、結構 hive> select col1[0],col2[‘b‘],col3.c from complex;

內串連： hive> SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

查看hive為某個查詢使用多少個MapReduce作業 hive> Explain SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

外串連： hive> SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id); hive> SELECT sales.*, things.* FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id); hive> SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id);

in查詢：Hive不支援，但可以使用LEFT SEMI JOIN hive> SELECT * FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);

Map串連：Hive可以把較小的表放入每個Mapper的記憶體來執行串連操作 hive> SELECT /*+ MAPJOIN(things) */ sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

INSERT OVERWRITE TABLE ..SELECT：新表預先存在 hive> FROM records2     > INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year     > INSERT OVERWRITE TABLE records_by_year SELECT year, COUNT(1) GROUP BY year     > INSERT OVERWRITE TABLE good_records_by_year SELECT year, COUNT(1) WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9) GROUP BY year;  

CREATE TABLE ... AS SELECT：新表表預先不存在 hive>CREATE TABLE target AS SELECT col1,col2 FROM source;

建立視圖： hive> CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999;

查看視圖詳細資料： hive> DESCRIBE EXTENDED valid_records;