本文只是總結一些在Hive中常用的命令，并且假設需要的目錄或者數據已經存在。

創建表，\t作為列的分隔符

create table trade_detail (id bigint,income double,expenses double,time string) row formate delimited fields terminated by '\t';

create table user_info(id bigint, account string, name string, age int) row format delimited fields terminated by '\t';

接下來是稍復雜的語句，創建表的的同時進行賦值

create table result row format delimited fields terminated by '\t' as select t1.account, t1.income, t1.expenses, t1.surplus, t2.name from user_info t2 join (select account, sum(income) as income, sum(expenses) as expenses, sum(income-expenses) as surplus from trade_detail group by account) t1 on(t1.account = t2.account);

加載本地文件到數據表中

load data local inpath '/home/hadoop/data/student.txt' overwrite into table student;

load data local inpath '/home/hadoop/data/user_info.doc' overwrite into table user_info;

創建外部表 ，創建外部表的一般情況指的是：先有文件存放著數據，之后我們再來創建表，也就是說創建一張表，然后指向這個有數據的目錄。以后只要是向這個目錄中上傳符合格式的數據會被自動裝在到數據庫表中，因為在metastore(元數據)會記錄這些信息

create external table t_detail(id bigint, account string, income double, expenses double, time string) ) row format delimited fields terminated by '\t' location '/hive/td_partition';

創建分區表，一般用于數據量比較大的情況下， partitioned by (logdate string)用來指定按照什么進行分區

create external table t_detail(id bigint, account string, income double, expenses double, time string) row format delimited fields terminated by '\t' location '/hive/td_partition' partitioned by (logdate string);

將mysql中的數據直接保存到Hive中

sqoop export --connect jdbc:mysql://192.168.8.103:3306/hmbbs --username root --password hadoop --export-dir '/user/hive/warehouse/pv_2013_05_31/000000_0' --table pv

基本的插入語法

insert overwrite table tablename [partiton(partcol1=val1,partclo2=val2)]select_statement from t_statement

insert overwrite table test_insert select * from test_table;

更新表的名稱

hive> alter table source RENAME TO target;

添加新一列

alter table invites add columns (new_col2 INT COMMENT 'a comment');

刪除表：

DROP TABLE records;

刪除表中數據，但要保持表的結構定義

dfs -rmr /user/hive/warehouse/records;

顯示所有函數

show functions;

查看函數用法

describe function substr;

內連接

SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

查看hive為某個查詢使用多少個MapReduce作業

Explain SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

外連接

SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);

SELECT sales.*, things.* FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);

SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id);

創建視圖

hive> CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999;

查看視圖詳細信息

hive> DESCRIBE EXTENDED valid_records;

總結

以上是生活随笔為你收集整理的hive 语句总结_Hive常用命令总结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。