vim /etc/profileexport HIVE_HOME=/export/servers/hive...export PATH=:$HIVE_HOME/bin:$PATH
前台启动hive：hive --service hiveserver2客户端连接：beeline!connect jdbc:hive2://node03:10000
create database [if not exists] myhive location '/myhive2';use myhive;desc database myhive2;desc database extended myhive2;drop database myhive2 [cascade];
create table [if not exists] stu (sid string,sname string);
create table stu2 as select * from stu;create table stu3 like stu;desc [formatted] stu2;
create external table [if not exists] student (sid string,sname string,ssex string,sbirth string) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu';
load data local inpath '/export/servers/hivedatas/score.csv' [overwrite] into table score;
load data inpath '/hivedatas/score.csv' into table score partition(year='2018',month='06',day='18');
insert overwrite table score5 partition(month='201801') select sid,sname,sscore from score; (注意不能使用select * from score，否则报错：Error: Error while compiling statement: FAILED: SemanticException [Error 10044]: Line 1:23 Cannot insert into target table because column number/types are different ''201902'': Table insclause-0 has 3 columns, but query has 4 columns. (state=42000,code=10044))
select * from score where month='201806' union all select * from score where month='201807';
show partitions score;
alter table score add partition(month='201808') partition(month='201809') ;
alter table score drop partition(month='201809');
msck repair table score4;==============================================truncate table score6;(只能清空管理表)
from score insert overwrite table score_fir partition(month='201806') select sid,cid insert overwrite table score_sec partition(month='201806') select cid,sscore;
小结：create as select , insert select。
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
select sid as myid, cid myid2 from score;
select count(distinct sid) from score;
select max(sscore),min(sscore),sum(sscore),avg(sscore) from score;
select * from score where sscore like '_9%';
select * from score where sscore rlike '9';
select sid,avg(sscore) from score group by sid;
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
select s.sid,s.sscore,stu.sname,stu.sbirth from score s left join student stu on s.sid = stu.sid;
select * from student s left | right | full [outer] join score b on s.sid = b.sid; 左右全 外连接
select sid, avg(sscore) avgScore from score group by sid order by sid, avgScore desc; 按照别名排序
set mapreduce.job.reduces=3;
select * from score sort by sscore;
insert overwrite local directory '/export/servers/hivedatas/sort' row format delimited fields terminated by '\t' select * from score sort by sscore;
set mapreduce.job.reduces=7;
insert overwrite local directory '/export/servers/hivedatas/sort' row format delimited fields terminated by '\t' select * from distribute by sid sort by sscore;
insert overwrite local directory '/export/servers/hivedatas/cluster' row format delimited fields terminated by '\t' select * from score cluster by sid;
bin/hive -hiveconf hive.root.logger=INFO,console
show functions;
show function [extended] split;
set hive.exec.compress.intermediate=true;set mapreduce.map.output.compress=true;set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;set hive.exec.compress.output=true;set mapreduce.output.fileoutputformat.compress=true;set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;set mapreduce.output.fileoutputformat.compress.type=BLOCK;
转载于:https://www.cnblogs.com/mediocreWorld/p/11061858.html
相关资源：JAVA上百实例源码以及开源项目