搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > Jianpan > Hive进阶操作看过来

Hive进阶操作看过来

Jianpan 2018-06-28



接着上一篇Hive的基本操作,本篇文章介绍一些进阶的的操作。就Hive来说主要是实战,操作上感觉没啥好介绍的,主要就这些语法操作,故不做详细剖析。


1,二级分区表/管理表


create  table emp_part1(

     empno int,

     empname string,

     empjob string,

     mgrno int,

     birthday string,

     salary float,

     bonus float,

     deptno   int

)

partitioned by (day string,hour string)

row format delimited fields terminated by '\t';


load data local inpath '/emp.txt' into table emp_part1 partition (day='20170308',hour='9');


load data local inpath '/emp.txt' into table emp_part1 partition (day='20170308',hour='10');


load data local inpath '/emp.txt' into table emp_part1 partition (day='20170308',hour='14');


load data local inpath '/emp.txt' into table emp_part1 partition (day='20170309',hour='10');


select * from emp_part1;


select * from emp_part1 where day='20170308'


select * from emp_part1 where day='20170308' and hour='14';


分区可以理解为分类,通过分类把不同类型,时间,地域的数据放到不同的目录下。


分类的标准就是分区字段,可以一个,也可以多个。


分区表的意义在于优化查询。

查询时尽量利用分区字段。

如果不使用分区字段,就会全表扫描。


增加分区

alter table emp_part1 add partition (day='20170306',hour='0');


删除分区

alter table emp_part1 drop partition (day='20170306',hour='0');


2,桶表


将内部表,外部表和分区表进一步组织成桶表

可以将表的列通过Hash算法进一步分解成不同的文件存储


create table test_bucket_table(

    id int,

    name string

)

clustered by (id) into 5 bucket;


3,临时表 TEMPORARY


一、Hive创建表与加载表,导出的数据的方式

(一)创建表的方式

##方式一 create + load

  

create [external] table table_name(

        col1_name col1_type,

        ...

        coln_name coln_type

    )

 row  format delimited fields terminated  by '\t';


//load加载数据

laod data [local] inpth '本地文件(linux)/HDFS' [overwrite] into table table_name;


##方式二  like + load

##复制表结构

create table tableB like tableA;    //首先必须要有tableA


//load加载数据

laod data [local] inpth '本地文件(linux)/HDFS' [overwrite] into table  table_name;


##方式三  as 创建表的同时加载数据

create table tableB row format delimited filelds termianted by ','  as select * from tableA;   //首先必须要有tableA


create table emp_as row format delimited fields terminated by ',' as select empno,empname,salary from emp_part1;


##方式四 create  + insert

1.创建表

create table emp_insert(

    id int,

    name string,

    job string,

    salary float

 )

row format  delimited fields terminated by ',';


2.insert into 加载数据

insert into table emp_insert select empno,empname,empjob,salary from emp_part1 where day='20170308' and  hour='14’;


(二)加载数据的方式


加载方式一

1.加载本地文件到Hive表   --使用存储介质(移动硬盘)

laod data local inpth '本地文件(linux)' [overwrite] into table  table_name;


2.加载HDFS文件到hive表   --通过Flume等日志收集框架

laod data  inpth 'HDFS文件' [overwrite] into table  table_name;


3.加载数据示覆盖已有的数据

laod data [local] inpth '文件' overwrite  into table;

  

**hdfs  dfs -put 本地目录及文件 表在HDFS所在的目录

desc formatted table_name;  --找到table_name在HDFS上的目录


4.创建表时通过select查询语句加载数据

create table tableB row format delimited filelds termianted by ','  as select * from tableA;  


5.先创建表,通过insert into table table_namea  select  *  fom tableB


6.创建外部表示通过location指定数据所在目录


create extrnal table_name(

    col1_name col1_type,

         ...    

    coln_name coln_type

)

row  format delimited fields terminated  by '\t';

location  'HDFS上的目录’



(三)几种导出数据的方式

  1. insert overwrite ... 导出到本地目录


insert overwrite local directory '/export' row format delimited fields terminated by ' ' select * from emp_part1;


2.insert overwrite ... 导出到HDFS之上


insert overwrite directory '/export' select * from emp_part1 where day='20170308';


3.hive -e ''  >> test

bin/hive -e 'select * from db01.student' >> test.txt


4.sqoop



4,基本查询


SELECT [DISTINCT] select_expr, select_expr, ...

  FROM table_reference

  [WHERE where_condition]

  [GROUP BY col_list]

  [ORDER BY col_list]

  [CLUSTER BY col_list

    | [DISTRIBUTE BY col_list] [SORT BY col_list]

  ]

[LIMIT number]


1.查询表中的某几个字段

select empno,empname from emp;


2.distinct 去重

select distinct(depno) from emp;


3.count 统计计数

select count(empno) from emp;

select count(*) from emp;

select count(1) from emp;


4.别名

select  count(empno) as empnonum from emp a;


5.where

    ** and

    ** or

    ** between ... and ..

    ** + - * /

    ** < > = >= <=

    ** is null  is not null


select * from emp where depno=20;


select * from emp where depno=20 and salary > 2000;


select *  from emp where depno=20 or empno=7521;


select * from emp where salary between 2000 and 5000;


select * from emp where salary > 3000;


select empname, salary, bonus,(salary+bonus) as tolsal from emp;

select *


6.limit from emp limit 2;


7.聚合函数count


count(*) -所有值不全为null时,加1操作

count(1) -不管有没有值,只要有这条记录,值就加1

count(col) -col列里面的值为null,值不会加1,这个列的值不为null,才加1


select count(bonus) from emp;

    ** avg  平均值

    ** sum  求和

    ** max  最大值

    ** min 最小值


select avg(salary) salary from emp;


select depno,avg(salary) salary from emp group by depno;


select depno,max(salary) salary from emp group by depno;


select depno,min(salary) salary from emp group by depno;


8.group by


* 求每个部门的薪资总和

select depno,sum(salary) sum_salary from emp group by depno;


* 求每个部门的薪资最高的员工薪水


【错误】select empname,max(salary) salary from emp group by depno;

FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname'


Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname' (state=42000,code=10025)


注意:group by 后面没有出现的字段,不能直接放到select后面,为了构成语法,可以结合聚合函数使用。


FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname'


【语法正确-结果错误,须用子查询方式】

select max(empname),max(salary) max_sal from emp group by  deptno;


select e.empno,e.empname,e.salary,e.deptno from emp e where e.salary in (select max(salary) salary from emp group by deptno);


select e.empname from emp e where e.deptno in (select d.deptno from dept d where d.deptname='SALES' or d.deptname='ACCOUNTING');


Hive的子查询

    Hive只支持where和from后面的子查询

    -语法中的括号

    -合理的书写风格

    -hive只支持where和from语句中的子查询

    -主查询和子查询可以不是同一张表

    -子查询中的空值问题


9.having 条件过滤

select depno, avg(salary) avg_sal from emp group by depno hvaing  avg_sal >3000;


10.union  union all

必须保证union all 前后select 查询字段的个数和字段类型相同


select depno from emp

union all

select depno from dept;


11 join  (map join, reduce join, SMB join)

* on  条件  --等值连接

查询员工姓名、部门名称及员工薪资  

select a.empname,b.deptname,a.salary from emp a join dept b on a.depno=b.depno;


//左连

select a.empname,b.deptname,a.salary from emp a left join dept b on a.depno=b.depno;


//右连

select a.empname,b.deptname,a.salary from emp a right join dept b on a.depno=b.depno;


两张表中没有共同字段做join

select * from student join dept on 1=1;


5,数据类型  


https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types


基本数据类型 和 复杂类型


基本数据类型

Numeric Types

    TINYINT

    SMALLINT

    INT/INTEGER

    BIGINT

    FLOAT

    DOUBLE

    DOUBLE PRECISION

    DECIMAL

Date/Time Types

    TIMESTAMP

    DATE

    INTERVAL

String Types

     STRING

    VARCHAR

    CHAR

Misc Types

    BOOLEAN

    BINARY (Note: Only available starting with Hive 0.8.0)


复杂数据类型:

- Array:数组类型,由一系列相同数据类型的元素组成


- Map:集合类型,包括key -> Value 键值对,可以通过key来访问元素[key相同的会被覆盖]


- Struct:结构类型,可以包含不同数据类型的元素。这些元素可以通过“点语法”的方式来得到所需要的元素


【扩展】

create table people_movie(  

    name string,                  --学生名称

    movie map<string,string>  --电影名称,女一号

)   

row format delimited fields terminated by "\t"


collection items terminated by ","


map keys terminated by ":";

vi movie.txt

A    ABC:2016-05,EFG:2016-09

B    OPQ:2015-06,XYZ:2016-04


select * from people_movie;

A    {"ABC":"2016-05","EFG":"2016-09"}

B    {"OPQ":"2015-06","XYZ":"2016-04"}


create table student(

    sid int,

    sname string,

    grade arrary<float>

)


id   name  grade(英语,语文,数学)

{1   Tom   [80, 90, 75]}

create table student3(

    sid int,

    info struct<name:string,age:int,sex:string>

);

{1, {'Tom',10,'男'}}



6,Hiveserver2  应用场合:beeline  java代码JDBC


hiveserver:这个可以让Hive以提供Trift服务的服务器形式来运行,可以允许许多不同语言编写(C python java)的客户端进行通信。使用需要启动HiveServer服务以和客户端联系,我们可以通过设置HIVE_PORT环境变量来设置服务器所监听的端口号,在默认的情况下,端口为10000。

可以通过下面方式来启动HiveServer:

bin/hive --service hiveserver2 &hiveServer/HiveServer2


1:简单介绍     

两者都允许远程客户端使用多种编程语言,通过HiveServer或者HiveServer2,客户端可以在不启动CLI的情况下对Hive中的数据进行操作,连这个和都允许远程客户端使用多种编程语言如java,python等向hive提交请求,取回结果(从hive0.15起就不再支持hiveserver了),但是在这里我们还是要说一下hiveserver


HiveServer或者HiveServer2都是基于Thrift的,但HiveSever有时被称为Thrift server,而HiveServer2却不会。


既然已经存在HiveServer,为什么还需要HiveServer2呢?这是因为HiveServer不能处理多于一个客户端的并发请求,这是由于HiveServer使用的Thrift接口所导致的限制,不能通过修改HiveServer的代码修正。因此在Hive-0.11.0版本中重写了HiveServer代码得到了HiveServer2,进而解决了该问题。


HiveServer2支持多客户端的并发和认证,为开放API客户端如JDBC、ODBC提供更好的支持。


 2:两者的区别

Hive进阶操作看过来


吐槽一下,这个包含大量英语字母的排版真是想吐!

吐槽一下,这个包含大量英语字母的排版真是想吐!

吐槽一下,这个包含大量英语字母的排版真是想吐!


Hive进阶操作看过来


推荐阅读:


   







如果觉得文章不错,欢迎转发点赞,另外有错误欢迎留言指出,谢谢。

Hive进阶操作看过来



Jianpan


更多好文,敬请期待!


       


版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《Hive进阶操作看过来》的版权归原作者「Jianpan」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注Jianpan微信公众号

Jianpan微信公众号:Jianpan--

Jianpan

手机扫描上方二维码即可关注Jianpan微信公众号

Jianpan最新文章

精品公众号随机推荐

下一篇 >>

Hive(一)