配置hive時(shí)出現(xiàn)不能加載自己修改的hive-site.xml等配置文件的問題昧廷。發(fā)現(xiàn)它總是加載默認(rèn)的配置文件。
解決：
hadoop的配置文件hadoop-env.sh中加上export HADOOP_CLASSPATH=$HIVE_HOVE/conf:$HADOOP_CLASSPATH
還有一個(gè)問題：運(yùn)行其他hadoop子項(xiàng)目時(shí)總會(huì)出現(xiàn)找不到類文件偎箫，必須把相關(guān)jar包拷貝到hadoop的lib目錄下木柬，導(dǎo)致其lib目錄會(huì)越來越大。
至今不知道怎樣將其他jar包加入到classpath中,網(wǎng)上說用export HADOOP_CLASSPATH=“”淹办，但是好像不行
hive --config /root/etc/hive(注：/root/etc/hive是指hive-site.xml的存放目錄）
HiveQL以分號(hào)結(jié)束眉枕。可以跨行怜森。
在hive的shell上可以使用dfs命令執(zhí)行HDFS文件操作速挑。
dfs -ls /user/hive/warehouse;
hive語(yǔ)句必須以分號(hào)“；”結(jié)束副硅。
不支持更新姥宝，索引和事務(wù)。
表名恐疲，列名不區(qū)分大小寫腊满。
在hive的shell上可以使用dfs命令執(zhí)行HDFS文件的操作套么。>>dfs -ls /user/hive/warehouse/;
查看和設(shè)置臨時(shí)變量：>>set fs.default.name[=hdfs://zhaoxiang:9000];
導(dǎo)入jar包: >>add jar hivejar.jar;
創(chuàng)建函數(shù): >>create temporary function udfTest as 'com.cstore.udfExample';
【在pig中使用UDF，先用register語(yǔ)句注冊(cè)jar文件碳蛋，之后可以通過完全的java類名調(diào)用胚泌，或者用define語(yǔ)句為UDFding 指定一個(gè)名稱：
register pigjar.jar;
define UPPER org.pigjar.string.UPPER();
B = foreach a generate UPPER($0); 】
可以在本地命令行運(yùn)行hive的shell:
$ hive -e 'select * from userinfo' (執(zhí)行hiveQL語(yǔ)句)
$ hive --config /hive-0.9.0/conf (重新載入新的配置文件)
$ hive --service hiveserver 50000(啟動(dòng)服務(wù))
create table cite(citing int, cited int) row format delimited fields terminated by ',' stored as textfile; //sequencefle
load data (local) inpath 'cite75_99.txt' overwrite into table cite;//若不加local則默認(rèn)為HDFS路徑
select * from cite limit 10;
show tables;
describe cite;
select count(1)/count() from cite; //count(1)相當(dāng)于SQL中的count()
create table cite_count (cited int, count int);
insert overwrite table cite_count select cited , count(citing) from cite group by cited;
select * from cite_count where count > 10 limit 10;
drop table cite_count;
create table page_view(viewTime int, userid bigint,
page_url string, referrer_url string,
ip string comment 'ip address of user')
comment 'this id the page view table'
partitioned by (dt string, country string)//注意table中的列不能和partition中的列重合
clustered by (userid) into 32 buckets //桶
row format delimited
fields terminated by ','
collection items terminated by '\002'
map keys terminated by '\003'
lines terminated by '\n'
stored as textfile;
//取樣
select avg(viewTime) from page_view tablesample(bucket 1 out of 3 [on id]);
//創(chuàng)建外部表，指定目錄位置肃弟，刪除外部表時(shí)會(huì)刪除元數(shù)據(jù)玷室，表中的數(shù)據(jù)需手動(dòng)刪除
create external table page_view(viewTime int, userid bigint,
page_url string, referrer_url string,
ip string comment 'ip address of user')
location 'path/to/existing/table/in/HDFS';
//修改表
alter table page_view rename to pv;
alter table pv add columns (newcol string);
alter table pv drop partition (dt='2009-09-01');
show tables 'page_.*';
load data local inpath 'page_view.txt'
overwrite into table page_view
partition (dt='2009-09-01',country='US');
在hive的shell上執(zhí)行unix命令：命令前加感嘆號(hào)(！),命令尾加分號(hào)(;).
hive> ! ls ;
hive> ! head hive_result;
//執(zhí)行查詢：
insert overwrite table query-result; //eg: insert overwrite table query_result select * from page_view where country='US';
insert overwrite (local) directory '/hdfs-dir(local-dir)/query_result' query;
select country , count(distinct userid) from page_view group by countey;
//子查詢愕乎，只能在from子句中出現(xiàn)子查詢
select teacher, max(class-num) from
(select teacher, count(classname) as class-num from classinfo group by teacher)subquery
group by teacher;
//連接
select pv., choice., f.friends from page_view pv
join user u on (pv.userid=u.id)
join friends-list f on (u.id=f.uid);
//多表插入
create table mutil1 as select id, name from userinfo;
create table mutil2 like mutil1;
from userinfo insert overwrite table mutil1 select id, name
insert overwrite table mutil2 select count(distint id),name group by name;
//創(chuàng)建視圖
create view teacher_classnum as select teacher, count(classname) from classinfo group by teacher;
