Hive常用sql语句总结

目录

1. 创建hive表示指定location参数

2. DISTRIBUTE BY的使用

3. hive中 distribute by 、group by 、order by、sort by、cluster by的比较

4. 修改字段类型、添加字段

5. hive中rand()函数

6. Hive中数据取整函数

7. 处理hive中的表，将其中的某些数值字段加上一个随机整数

8. 在hive中创建udf

9. Hive数据导入导出

---

---

1. 创建hive表示指定location参数

例如下面创建外表的语句，指定了表数据文件保存的hdfs目录，同时指定了hive数据文件的类型

create external table if not exists tableName (

column01 date,

column02 int,

column03 string

) 

stored as sequencefile

location 'hdfs://tmp/tableName'

2. DISTRIBUTE BY的使用

在hive中，每个hive表的数据存储目录下都会包含若干个文件（例如有5个小文件），当执行sql语句对这张表进行MR操作时，hadoop会启动5的mapper来处理数据（即mapper的个数与hive表下的小文件个数相同）。

• 表下面的小文件重新组合

现在假如一个hive表下面有多个大小不同的小文件，我们现在想对这些文件进行重新组合，例如使用INSERT OVERWRITE TABLE … DISTRIBUTE BY RAND()，使表数据随机分发数据到reducer（每个reducer会生成一个小文件），得到大小相近的文件，则分发语句是 DISTRIBUTE BY RAND()。

或者我们想以某个字段来进行数据重新划分的依据，例如根据user_id字段，将user_id相同的数据都重新分发到同一个小文件中，则可以使用的分发语句是  DISTRIBUTE BY USER_ID。

例子一：对表mytable中数据进行重新随机分配，生成新的文件中，每个文件数据条数大致相同

INSERT OVERWRITE TABLE mytable SELECT * FROM mytable DISTRIBUTE BY RAND();

例子二：对表mytable中数据进行重新分配，将user_id字段作为重新划分的依据，相同user_id的数据会被从新分配到相同的文件中（同一文件中可能会包含多个相同的user_id数据，因为distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区）

INSERT OVERWRITE TABLE mytable SELECT * FROM mytable DISTRIBUTE BY USER_ID;

例子三：将表中的数据提取到小文件中，例如先按照部门编号分区，再按照员工编号降序排序。相同部门编号的数据会被分配到同一个小文件中，且每个文件中的数据按照员工编号降序排序

hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

注意：

1．distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。

2．Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

• 参考链接 

http://kylin.apache.org/cn/docs/howto/howto_optimize_build.html

https://www.cnblogs.com/Tunan-Ki/p/11808562.html

https://wenwen.sogou.com/z/q825576387.htm

3. hive中 distribute by 、group by 、order by、sort by、cluster by的比较

• distribute by

distribute by 是控制在map端如何拆分数据给到下游的reduce端，拆分方式是根据指定的字段按照hashcode值对reduce的个数取模。

例如指定在执行hive语句时指定reducer的个数，set mapreduce.job.reduces=3;那么就会对3进行取模。相当于mapreduce程序中的patition分区过程

使用场景：map输出的文件大小不均，合并小文件、拆分大文件等等。

• group by

对检索结果进行分组，一般经常和聚合函数（avg、count、min）等一起使用

• order by 

会对数据进行全局排序，和mysql、oracle等关系型数据库中的order by效果一样。该order by操作只在一个reduce中进行，所以当全局数据量特别大时，效率会非常低。当设置了

set hive.mapred.mode=strict的时候，执行select语句时需要执行 limit 对查询条数进行限制，否则会报异常提示：limit must also be specified。

• sort by

sort by是局部有序，即在各自单独的reduce中进行排序，所有不能保证全局有序，除非设置mapred.reduce.tasks=1，这时候适合order by的效果一样的。如果reduce大于 1 ，则会分成几个文件输出，每个文件中的数据按照指定的字段排序，不保证全局有序。注意：sort by 不受 set hive.mapred.mode=strict的影响，（strict和nostrict都一样）。

• cluster by

distrubute by 和 sort by一起合用就相当于cluster by，但是cluster by的排序方式只有desc 倒序排列，而不能指定为asc。

• 参考链接

https://wenwen.sogou.com/z/q825576387.htm

http://kylin.apache.org/cn/docs/howto/howto_optimize_build.html

https://www.cnblogs.com/Tunan-Ki/p/11808562.html

---

4. 修改字段类型、添加字段

alter table 表名  change column 原字段名称  现字段名称  数据类型

alter table 表名 add columns(字段名 数据类型)

---

5. hive中rand()函数

直接执行 select rand()

就会返回一个 0-1之间的小数

加入想要0-100之间的随机数，则执行下面的语句即可

select rand() * 100

参考资料

---

6. Hive中数据取整函数

参考资料

---

7. 处理hive中的表，将其中的某些数值字段加上一个随机整数

处理过程如下

• 1、复制表结构 

    CREATE TABLE IF NOT EXISTS mydb.mytable_bak like mydb.mytable

• 2、插入bak表随机数 ，下面是对整数列加上随机整数，rand()的值为0-1，这里扩大整数倍，整数 CEILING( (0-1) * 10000000) 向上取整

insert overwrite table mydb.mytable_bak 
select 
    dealmonth, 
    cityname, 
    mcc_type,
    CEILING(deal_num + rand() * 10000000),
    CEILING(deal_amt  + rand() * 10000000 )  
from mydb.mytable

• 3、删除正式表

drop table mydb.mytable;

• 4、在hdfs路径上，删除原来表对应的文件夹和数据文件
• 5、对bak表重命名为正式表

alter table mydb.mytable_bak  rename to mydb.mytable;

---

8. 在hive中创建udf

• 添加加解密udf操作

有的加解密函数，同时需要用到.so文件，这时候操作命令可参考如下：

（1）将jar包和.so文件，放到hdfs集群的/user/proc_user/udf-jar/目录下。
（2）登陆后台linux服务器的终端，进入到hive命令行，然后执行如下命令：
create   function dbname.sm4en as 'com.xx.SM4EN' using  JAR 'hdfs:///user/test_user/udf/YourUDF.jar',
FILE 'hdfs:///user/test_user/udf/sm4xx.so';
（3）在hue页面上用管理员用户执行：
        reload function
（4）验证下：
-- 加密
select dbname.sm4en('65asdfasdfad4t5345fsEF29E072B')

• 参考资料

hive官网UDF地址
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateFunction
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inOperators

https://blog.csdn.net/liuzhoulong/article/details/77969224

---

9. Hive数据导入导出

https://www.cnblogs.com/duanxz/p/9015937.html