Hive语法相关

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

DDL

1. 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 旧表;

2. drop

   删除数据库(cascade强制删除数据库)：drop database 数据库名 [cascade] ;

   删除表：drop table [if exists]表名；

3. alter

   修改表名：alter table table_name rename to new_table_name;

4. truncate

   清空表数据：truncate table 表名;

5. show

   查看数据库：show databases；

   查看当前数据库下所有表：show tables；

   查看某个表的建表语句：show create table 表名；

   查看某个表的字段：show columns from 表名;

6. 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....
    [[email protected] data]$ cd sortby/
    [[email protected] 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
 > [[email protected] 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