創(chuàng)建表：
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
創(chuàng)建一個新表，結(jié)構(gòu)與其他一樣
hive> create table new_table like records;
創(chuàng)建分區(qū)表：
hive> create table logs(ts bigint,line string) partitioned by (dt String,country String);
加載分區(qū)表數(shù)據(jù)：
hive> load data local inpath '/home/Hadoop/input/hive/partitions/file1' into table logs partition (dt='2001-01-01',country='GB');
展示表中有多少分區(qū)：
hive> show partitions logs;
展示所有表：
hive> SHOW TABLES;
lists all the tables
hive> SHOW TABLES '.*s';
顯示表的結(jié)構(gòu)信息
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;
刪除表中數(shù)據(jù)，但要保持表的結(jié)構(gòu)定義
hive> dfs -rmr /user/hive/warehouse/records;
從本地文件加載數(shù)據(jù)：
hive> LOAD DATA LOCAL INPATH '/home/hadoop/input/ncdc/micro-tab/sample.txt' OVERWRITE INTO TABLE records;
顯示所有函數(shù)：
hive> show functions;
查看函數(shù)用法：
hive> describe function substr;
查看數(shù)組、map、結(jié)構(gòu)
hive> select col1[0],col2['b'],col3.c from complex;
內(nèi)連接：
hive> SELECT sales., things. FROM sales JOIN things ON (sales.id = things.id);
查看hive為某個查詢使用多少個MapReduce作業(yè)
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的內(nèi)存來執(zhí)行連接操作
hive> SELECT /+ MAPJOIN(things) / sales., things. FROM sales JOIN things ON (sales.id = things.id);
INSERT OVERWRITE TABLE ..SELECT：新表預(yù)先存在
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：新表表預(yù)先不存在
hive>CREATE TABLE target AS SELECT col1,col2 FROM source;
創(chuàng)建視圖：
hive> CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999;
查看視圖詳細信息：
hive> DESCRIBE EXTENDED valid_records;
