hive纯命令行
vim /etc/profile
export 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;
最新文章
- Office2016打开doc字符间距过小
- 关于xfce中桌面没法显示回收站以及thunar中无法进行卷管理的解决办法
- 自动生成form Scripts
- [2013 Final] Colors
- ESXi cron jobs
- HDU 4888 (网络流)
- 关于编程语言(转/收藏)-原文作者:韩天峰(Rango)
- 我所经历的SAP选型
- dedecms获取栏目下的频道列表
- 速卖通---发布商品aeopAeProductPropertys这个字段值报07004013的错误
- rsync同步配置
- 【CSS】如何用css做一个爱心
- HDU 3565 Bi-peak Number(数位DP)题解
- 【Java】Java-UTC-时间戳处理
- PHP Rabbitmq 报错Broken pipe
- centos 部署 SparkR
- 隐函数画图with R
- 【海龟汤策略】反趋势交易策略源代码分享(基于BOTVS)
- Problem D: 结构体:计算输入日期是该年的第几天
- jsp el 自定义方法 tld 说明
热门文章
- 【POJ 2152】 Fire
- hdu 5119 (类似于划分数的状态定义) (DP中的计数问题)
- bzoj3638
- bzoj 1661: [Usaco2006 Nov]Big Square 巨大正方形【枚举】
- spoj 287 NETADMIN - Smart Network Administrator【二分+最大流】
- 关于数学函数中的abs——————————————杭电2057——————————————————————————
- maven学习-搭建环境
- Drawable新属性
- BZOJ 1396||2865 识别子串
- 贪心 Codeforces Round #273 (Div. 2) C. Table Decorations