Hive语法相关

最新推荐文章于 2022-09-06 21:14:58 发布

yoohhwz

最新推荐文章于 2022-09-06 21:14:58 发布

阅读量182

点赞数

分类专栏： Hive 文章标签： Hive DDL DML functions

本文链接：https://blog.csdn.net/yoohhwz/article/details/96503476

版权

Hive 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一些常用的hive相关的语法，详细请查看官网：https://cwiki.apache.org/confluence/display/Hive

DDL

create
创建数据库：create database [if not exists] 数据库名;
创建表：create table [external] 表名
(column1 type1,
column2 type2,
…)
row format delimited fields terminated by ‘列分隔符’
stored as textfile/rcfile；
如果是内部表，默认存储在/user/hive/warehouse下，如果是external表，如果不想让其存放在hive默认目录下的话，可使用location指定需要在hdfs上的存储路径，如果此时再把需要加载的数据放在external表在hdfs上的存储目录下的话，数据将会直接加载到external表中，不需要再load加载数据。
复制表结构,只能复制全部表结构：create table 新表 like 旧表;
复制表结构及表数据，可选择部分字段复制：create table 新表 as select * from 旧表;
drop
删除数据库(cascade强制删除数据库)：drop database 数据库名 [cascade] ;
删除表：drop table [if exists]表名；
alter
修改表名：alter table table_name rename to new_table_name;
truncate
清空表数据：truncate table 表名;
show
查看数据库：show databases；
查看当前数据库下所有表：show tables；
查看某个表的建表语句：show create table 表名；
查看某个表的字段：show columns from 表名;
describe
查看某表的所有字段及字段类型：desc 表名；
desc formated 表名;

DML
1.load
加载表数据：load data [local] inpath ‘本地路径数据/hdfs路径数据’ [overwrite] into table 表名;
2.insert
插入表数据(该表1必须先创建)：insert into table 表1 select column1,column2…| * from 表2;
导出表中的数据到本地或者hdfs：
insert overwrite [local] directory ‘本地路径/hdfs路径’
[row format delimited fields terminated by ‘,’]
select column1,column2…| * from 表名;
3.update/
4.delete/
5.merge
6.import
7.export,
8.explain plan

关于hive中order by，sort by，cluster by，distribute by的区别？
1.order by是全局排序，当使用order by的时候reduce task的个数为1个即最终输出文件也会只有一个，那么此时就是全局排序,如果此时hive为严格模式的话需要加limit。 set hive.mapred.mode=xx;
2.sort by是局部排序，比如说设置reduce task的个数为3个的时候即最终输出的文件也会有三个，那么此时使用sort by即可实现每个文件内的数据是有序的，不能保证全局有序，但是如果reduce task的个数为一个的时候，那么此时也相当于全局有序。。set mapred.reduce.tasks=xx;默认-1 表示1个。

eg:首先将reduce的个数设置为3个，然后使用sort by实现三个输出文件内分别升序，最终结果将会生成三个文件，并且通过查看，每个文件内将会按照sal字段升序

    hive (hwzhdb)> set mapred.reduce.tasks=3;
    hive (hwzhdb)> insert overwrite local directory './data/sortby' 
                 > row format delimited fields terminated by ','
                 > select * from emp sort by sal ;
                .....执行mr....
    [hadoop@hadoop001 data]$ cd sortby/
    [hadoop@hadoop001 sortby]$ ll
    total 12
    -rw-r--r--. 1 hadoop hadoop 367 Jul 20 14:34 000000_0
    -rw-r--r--. 1 hadoop hadoop 361 Jul 20 14:34 000001_0
    -rw-r--r--. 1 hadoop hadoop 116 Jul 20 14:34 000002_0

但是这三个文件是根据哪个字段进行划分的呢？
其实是因为hive对单独使用sort by的这种有个算法，是随机生成的这三个文件，并不是根据某个字段值进行分区的，所以针对同一份数据集，无论你执行多少次这个语句，最终生成的三个文件中的内容都是一样的，不会改变

3.distribute by 不是排序，而是可以按照一定的规则将数据分发(此处的分发规则是%reduce的个数然后根据余数将数据分发到同一个reduce上)到不同的reduce上，然后可以结合使用sort by进行局部排序

 hive (hwzhdb)> set mapred.reduce.tasks=3;
 hive (hwzhdb)> insert overwrite local directory './data/distributeByAndSortBy' 
                     > row format delimited fields terminated by ','
                     > select * from emp distribute by deptno sort by sal ;
  这种方式最终也是生成三个文件，只不过跟单独使用sort by不同的是这三个文件是根据对
  deptno进行hash，然后%reduce的个数，将数据分散到三个文件中的。

4.cluster by 是distribute by+sort by的结合，就是说如果你的distribute by后面的字段跟sort by后面的字段一致的话就可以简写为cluster by，但是这种方式只能倒序，不能指定asc或者desc

hive内置函数相关？
查看内置函数命令：show functions;
查看某一个函数说明：desc function 方法名;
查看某一个函数说明并且带有example的：desc function extended 方法名;

类Oracle创建一个dual表，供我们方便使用
hive (hwzhdb)> create table dual(x string);
OK
Time taken: 0.857 seconds
hive (hwzhdb)> insert into table dual values('');
			...mr...

时间函数相关：yyyy-MM-dd HH:mm:ss
current_date：2019-07-20

current_timestamp：2019-07-20 16:40:05.975

unix_timestamp：传入一个date，然后指定时间格式，返回一个timestamp
eg：
hive (hwzhdb)> select unix_timestamp('2019-7-21 11:26','yyyy-MM-dd HH:mm') from dual;
OK
_c0
1563679560

from_unixtime:传入一个timestamp，然后指定要返回的date格式，返回一个date
eg：
hive (hwzhdb)> select from_unixtime(1563679560,'yyyy-MM-dd HH:mm:ss') from dual;
OK
_c0
2019-07-21 11:26:00

-----------------
date_add：增加/减少天数
add_months：增加/减少月份
-----------------
year：返回一个date的年份
month：返回一个date的月份
day：返回一个date的天
hour：返回一个date的小时
minute：返回一个date的分钟
second：返回一个date的秒

数值相关的：
abs：取绝对值
ceil：对于小数，向上取整
floor：对于小数，向下取整
round：对于小数，四舍五入，可以传入第二个参数设置精度(即小数点后保留几位)
least：传入多个数字，返回其中最小的一个数字
greatest：传入多个数字，返回其中最大的一个数字

字符串相关的：
substr：字符串截取
eg：
SELECT substr(‘Facebook’, 5) FROM dual; --从正数第五个一直截取到末尾
‘book’
SELECT substr(‘Facebook’, -5) FROM dual; --从倒数第五个的位置一直截取到末尾
‘ebook’
SELECT substr(‘Facebook’, 5, 1) FROM dual; --从正数第五个的位置，截取一个
‘b’
concat：字符串拼接
eg：
SELECT concat(‘abc’,‘def’) FROM dual;
‘abcdef’
concat_ws:字符串拼接，能够指定拼接符
eg：
SELECT concat_ws(’.’, ‘www’, array(‘facebook’, ‘com’)) FROM dual;
‘www.facebook.com’

其它一些常用函数：
1.to_date:将字符串日期转换为date格式
2.cast：类型转换函数，类似java中Inter.parse,可以将字符串数值转为数值类型
3.split：字符串切分函数
eg： SELECT split(‘oneAtwoBthreeC’, ‘[ABC]’) FROM dual;
[“one”, “two”, “three”]

4.explode：行转列函数，一行变多行，可以和split结合使用实现hql的wc功能，

 > eg： 造一份数据模拟hql实现wc
 > [hadoop@hadoop001 data]$ cat wc.txt  
 > yuwen#shuxue#yingyu 
 > yuwen#shuxue#shuxue 
 > yingyu
  hive (hwzhdb)> create table wc(
              > subjects string
              > );
 hive (hwzhdb)> load data local inpath '/home/hadoop/data/wc.txt' into table wc;
 hive (hwzhdb)> select tmp.word,count(1)  conum
 				from
                 (
                 select explode(split(subjects,'#')) as word 
                 from wc
                 ) tmp 
                 group by tmp.word
                 order by conum desc;
         result:
         tmp.word        conum
         shuxue  3
         yuwen   2
         yingyu  2