hql 操作

本文主要是介绍hql 操作，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Hive
   HDFS
       存储数据
   YARN
       资源管理
   MapReduce
       处理数据

日志
日志内容，统一的规范
   * 每一行数据就是一条数据（RDBMS）
   * 很多列，统一的标识符，进行分割
schema
   模式
   约束

Hive
   * 处理的数据存储在HDFS
   * 分析数据底层的实现MapReduce
   * 执行程序运行的YARN

RDBMS
   表的概念
   create table bf_log(
       ip string,
       user string,
       date string,
       ......
   )

分析
HQL
   HiveQL
   select * from bf_log limit 10 ;
   select substring(ip,0,4) ip_prex from bg_log ;

SQL On HADOOP

============================================================
HQL
| Engine --Hive
MapReduce

表的元数据
bf_log

============================================================
show databases ;
use default;
show tables ;
create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath '/opt/datas/student.txt'into table student ;
select * from student ;
select id from student ;

# rpm -qa|grep mysql
# rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm

mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
UPDATE user SET Password=PASSWORD('123456') where USER='root';

拷贝mysql驱动jar包，到Hive安装目录的lib下
$ cp mysql-connector-java-5.1.27-bin.jar /opt/modules/hive-0.13.1/lib/

配置的hive metastore
Mysql
与我们hive安装在同一台机器上

============================================================
show databases ;
create database db_hive ;
create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
show tables ;
desc student ;
desc extended student ;
desc formatted student ;

use db_hive ;
load data local inpath '/opt/datas/student.txt'into table db_hive.student ;

show functions ;
desc function upper ;
desc function extended upper ;
select id ,upper(name) uname from db_hive.student ;

============================================================
create table IF NOT EXISTS db_hive.bf_log_1212
(
ip string COMMENT'ip地址',
user string,
req_url string COMMENT'user 请求url地址'
)
COMMENT 'beifeng web logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' COLLECTION ITEMS TERMINATED BY '\n'
stored as textfile;
location '/user/beifeng/hive/warehouse/bf_log_1212';
create table if not exists default.bf_log_1812_sa
AS select ip,user from default.bf_log_1212

create table IF NOT EXISTS default.bf_log_1
like default.bf_log_1212;

分表

Hive数据仓库位置配置
   default
       /user/hive/warehouse
   注意事项
       * 在仓库目录下，没有对默认的数据库default创建文件夹
       * 如果某张表属于default数据库，直接在数据仓库目录下创建一个文件夹
   <property>
       <name>hive.metastore.warehouse.dir</name>
       <value>/user/hive/warehouse</value>
   </property>
$ $HADOOP_HOME/bin/hadoop fs -mkdir /tmp
$ $HADOOP_HOME/bin/hadoop fs -mkdir /user/hive/warehouse
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /tmp
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /user/hive/warehouse

Hive运行日志信息位置
   $HIVE_HOME/conf/hive-log4j.properties
       hive.log.dir=/opt/modules/hive-0.13.1/logs
       hive.log.file=hive.log

指定hive运行时显示的log日志的级别
$HIVE_HOME/conf/hive-log4j.properties
hive.root.logger=INFO,DRFA

在cli命令行上显示当前数据库，以及查询表的行头信息
   $HIVE_HOME/conf/hive-site.xml
       <property>
           <name>hive.cli.print.header</name>
           <value>true</value>
           <description>Whether to print the names of the columns in query output.</description>
       </property>

       <property>
           <name>hive.cli.print.current.db</name>
           <value>true</value>
           <description>Whether to include the current database in the Hive prompt.</description>
       </property>

在启动hive时设置配置属性信息
$ bin/hive --hiveconf <property=value>

查看当前所有的配置信息
hive > set ;

   hive (db_hive)> set system:user.name ;
       system:user.name=beifeng
   hive (db_hive)> set system:user.name=beifeng ;

此种方式，设置属性的值，仅仅在当前会话session生效

============================================================

[beifeng@hadoop-senior hive-0.13.1]$ bin/hive -help
usage: hive
-d,--define <key=value> Variable subsitution to apply to hive
commands. e.g. -d A=B or --define A=B
--database <databasename> Specify the database to use
-e <quoted-query-string> SQL from command line
-f <filename> SQL from files
-H,--help Print help information
-h <hostname> connecting to Hive Server on remote host
--hiveconf <property=value> Use value for given property
--hivevar <key=value> Variable subsitution to apply to hive
commands. e.g. --hivevar A=B
-i <filename> Initialization SQL file
-p <port> connecting to Hive Server on port number
-S,--silent Silent mode in interactive shell
-v,--verbose Verbose mode (echo executed SQL to the
console)

* bin/hive -e <quoted-query-string>
eg:
bin/hive -e "select * from db_hive.student ;"

* bin/hive -f <filename>
eg:
   $ touch hivef.sql
       select * from db_hive.student ;
   $ bin/hive -f /opt/datas/hivef.sql
   $ bin/hive -f /opt/datas/hivef.sql > /opt/datas/hivef-res.txt

* bin/hive -i <filename>
与用户udf相互使用

在hive cli命令窗口中如何查看hdfs文件系统
hive (default)> dfs -ls / ;

在hive cli命令窗口中如何查看本地文件系统
hive (default)> !ls /opt/datas ；
==================================================================

一、database
创建：ILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database bigdater already exists

create database if not exists db_hive_02;

create database if not exists db_hive_03 location '/user/root/hive/warehouse/db_hive_03.db';

二、table
[ROW FORMAT row_format], row_format格式：
       delimited fields terminated by '\001' collection terminated by '\002' map keys terminated by '\003' lines terminated by '\004' NULL DEFINED AS '\N'
   [STORED AS file_format], file_format格式：
       sequencefile
       textfile(default)
       rcfile
       orc
       parquet
       avro
   案例2：
       先将data2.txt文件上传到hdfs的/customers文件夹中。
       hdfs dfs -mkdir /customers
       hdfs dfs -put data2.txt /customers
   案例3：
       先将data3.txt文件上传到hdfs的/complex_table_test文件夹中。
       hdfs dfs -mkdir /complex_table_test
       hdfs dfs -put data2.txt /complex_table_test
   案例4：
       hive和hbase关联
       create external table hive_users(key string,id int, name string, phone string) row format serde 'org.apache.hadoop.hive.hbase.HBaseSerDe' stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with serdeproperties('hbase.columns.mapping'=':key,f:id,f:name,f:phone') tblproperties('hbase.table.name'='users');

三、前置条件：

hive启动
   root用户登录密码123456
   启动mysql：service mysqld restart
   使用hadoop用户登录，启动metastore：hive --service metastore &
数据文件准备
   将文档文件夹中的classes.txt和students.txt移动到linux机器和hdfs文件系统上。
   命令：hdfs dfs -put ./13 /beifeng/
创建hive相关表准备：
create database beifeng13;
create table students(studentId int comment 'this is student id, is not null', classId int comment 'this is class id, can set to null', studentName string comment 'this is student name') row format delimited fields terminated by ',';
create table classes(
classId int comment 'this is class id, is not null',
className string comment 'this is class name'
)
row format delimited fields terminated by ','
collection items terminated by '\n'
;

show databases like 'db_hive*';
show database extended db_hive;
drop database db_hive_08;数据库中有表存在不能这样删除。
drop database if exists db_hive_08 cascade;用级联删除。

四、数据类型：
tinyint smallint int bigint float double decimal
timestamp date
string varchar char boolean binary
arrarys maps structs union

load data local inpath '/opt/datas/emp.txt' overwrite into table emp ;
truncate table dept_cats;
hive 中有两种表类型：管理表托管表（外部表）。


五、导入数据
load data [本地local; hdfs上什么都不用写] inpath '文件路径' [overwrite] into table 表名 [partition(key=value),...]；
1. 分别导入local和hdfs的数据
       a. 分别从linux机器上导入数据
           load data local inpath '/home/hadoop/datas/13/classes.txt' into table classes;

           load data local inpath '/home/hadoop/datas/13/classes.txt' overwrite into table classes;
       b. 从hdfs上导入数据
           load data inpath '/beifeng/13/students.txt' into table students;
           dfs -put /home/hadoop/datas/13/students.txt /beifeng/13/
           load data inpath '/beifeng/13/students.txt' overwrite into table students;
注意：从hdfs上上传的数据文件，例如：students.txt ,在执行load data ... 后，就会被删除。
   2. 导入其他表的数据(多表插入)
       将学生表students的学生id和classid分别导出到不同表中，

       create table test1(id int);
       create table test2(id int);
       from students insert into table test1
select studentid insert overwrite table test2 select distinct classid where classid is not null;
insert overwrite 会覆盖已经存在的数据，假如原始表使用overwrite 上述的数据，先现将原始表的数据remove，再插入新数据。最后表的数据只有001 20 zhangsan这一条记录。

insert into 只是简单的插入，不考虑原始表的数据，直接追加到表中。最后表的数据是原始数据和新插入的 001 20 zhangsan

修复分区表 1. msck repair table dept_part;
2. alter table dept_part add partition(day = '20150913');

静态分区：分区的值是确定的【假设有一个程序，每天都统计销售额，按照日期进行分区每天的插入到制定的日期分区】
动态分区：分区的值是不确定的，由输入的数据确定【比如：京东有很多二级类目，每一个二级类目对应多个产品，按照二级类目分区】
是否是动态分区由set hive.exec.dynamic.partition决定【=true时为动态分区】

load data [本地local; hdfs上什么都不用写] inpath '文件路径' [overwrite] into table 表名 [partition(key=value),...]；

insert into table default.emp_ci select * from default.emp;

六、导出数据

把目录中的文件hive_exp_emp的内容，输出出来。
1. insert overwrite local directory '/opt/datas/hive_exp_emp'
select * from default.emp;
2. insert overwrite local directory '/opt/datas/hive_exp_emp'
row format delimited fields terminate by '\t'
collection items terminated by '\n';

   1. 导出表关联后的班级名称和学生名称(loca&hdfs)。（导出全部不为空的信息）
       班级1，学生1
       from (select classes.classname as col1, students.studentname as col2 from classes join students on classes.classid = students.classid) as tmp insert overwrite local directory '/home/hadoop/result/13/01' select col1,col2 insert overwrite directory '/beifeng/result/13/01/' select col1,col2 ;

       格式化：
       from (select classes.classname as col1, students.studentname as col2 from classes join students on classes.classid = students.classid) as tmp insert overwrite local directory '/home/hadoop/result/13/01' row format delimited fields terminated by ',' select col1,col2 ;
   2. 同时分别将已经分配班级的学生和未分配班级的学生导出到不同的文件夹中。(作业5)

七、select语法介绍
   from语法
       1. 正常from:
           select * from students;
       2. from语句提前:
           from students select *;
   cte语法：
       1. 获取班级号为1的学生信息:
           with tmp as (select studentid as sid,classid as cid,studentname as name from students where classid=1) from tmp select *;
       2. 获取总学生数、已经分配班级的学生数、未分配班级的学生数(作业1)。
           分析；
               总学生数：studentid的总数
               分配班级的学生数：classid不为空的学生总数
               未分配的学生数: classid为空的学生数
           结果: 12 7 5
   where & group by语法实例：
       group语句只能返回对应的group列&进行聚合的value。
       1. 获取学生数大于3的班级id
           from students select classid where classid is not null group by classid having count(studentid) > 3;
注意：group by 后面的字段，必须在select 中被查询到。
   排序语法：
       1. 使用order by根据学生id倒序。
           select * from students order by studentid desc;
       2. 设置hive.mapred.mode为strict，然后在进行order by操作。
           set hive.mapred.mode=strict;
           select * from students order by studentid desc; 会出现异常
           select * from students order by studentid desc limit 5;
       3. 使用sort by根据学生id排序。
           select * from students sort by studentid desc;
       4. 设置mapreduce.job.reduces个数为两个，然后再使用sort by进行排序。
           set mapreduce.job.reduces=2;
           select * from students sort by studentid desc;

where 是针对单条记录进行筛选的；
having 是针对分组结果进行筛选的；
八、join语法

左链接：左表有字段对应的右表没有字段也打印出来。

   内连接语法
       1. 获取学生和班级之间完全匹配的数据。
           select students.*,classes.* from classes join students on classes.classid=students.classid;
           select students.*,classes.* from classes cross join students on classes.classid=students.classid;
   外链接语法：
       1. 获取全部学生的班级信息，如果该学生没有分配班级，那么班级信息显示为null。
           select students.*, classes.* from students left join classes on students.classid = classes.classid;
       2. 获取全部班级的学生信息，如果某个班级没有学生，那么学生信息显示为null。(作业2)
       3. 获取全部信息，如果没有匹配数据的显示null。(作业3)
   半连接：
       1. 获取学生表中班级id在班级表中的所有学生信息。
           sql: select students.* from students where classid in (select distinct classid from classes);
           原hql: select students.* from students join classes on students.classid = classes.classid;
           新hql: select students.* from students left semi join classes on students.classid=classes.classid;
   mapjoin:
       select /*+ mapjoin(classes) */ * from students join classes on students.classid=classes.classid;

注意：
hive和其它关系数据库一样，支持count(distinct)操作，
但是对于大数据量中，如果出现数据倾斜时，会使得性能非常差，解决办法为设置数据负载均衡，其设置方法为设置hive.groupby.skewindata参数
hive (default)> set hive.groupby.skewindata;
hive.groupby.skewindata=false
默认该参数的值为false，表示不启用，要启用时，
可以set hive.groupby.skewindata=ture;进行启用。
当启用时，能够解决数据倾斜的问题，但如果要在查询语句中对多个字段进行去重统计时会报错。
hive> set hive.groupby.skewindata=true;
hive> select count(distinct id),count(distinct x) from test;
FAILED: SemanticException [Error 10022]: DISTINCT on different columns not supported with skew in data
下面这种方式是可以正常查询
hive>select count(distinct id, x) from test;

九、子查询
   1. 获取学生数最多的班级，学生的个数。
       第一步：获取每个班级的学生总数
       第二步：选择学生数最多的班级学生数
       from (select count(studentid) as sc from students where classid is not null group by classid) as tmp select max(sc);
   2. 获取学生数最多的班级信息。(作业4)
       第一步：获取每个班级的学生总数
       第二步：选择学生数最多的班级学生数
       第三步：根据最多的学生数和第一步获取的表数据进行比较，获取班级信息。

十、其他命令
   1. 在students和classes表上创建一个视图，视图包含两列分别是：班级名称，学生名称
       create view viewname as select classes.classname as cname, students.studentname as sname from classes join students on classes.classid = students.classid
   2. 在linux系统中通过命令hive -f/-e将所有学生信息保存到一个文件中。
       新建一个文件，文件内容为:select * from students
       执行：hive --database beifeng13 -f test.sql >> result.txt

将本地文件中的数据导入到创建的表中：
create table db_hive.emp like default.emp;
import table db_hive.emp from '/usr/beifeng/hive/export/emp_exp';

Map/Reduce是在物理执行计划的地方生成的。
很简单，ReduceSinkOpertor之前的在Map执行，ReduceSinkOperator之后的在Reduce执行，ReduceSinkOperator的作用是把数据从Map发到Reduce.具体点:FROM(TableScanOperator），WHERE（FilterOperator）,MapJoinOperator，ReduceSinkOperator在Map端执行。
GROUP BY（GroupByOperator），JOIN（JoinOperator），Distribute By，Sort By, Distinct，Having（FilterOperator）在Reduce端执行。
在Reduce端执行的重要特点就是有分发的key，比如group by key, join on a.key=b.key, count(distinct key), distrbute by key, cluster by key...

十一、order by 、Sort by 、distribute by、cluster by;
--------------------------------------------
order by:对全局数据的排序，只有一个reduce，数据量大的时候慎用。

Hive中的order by跟传统的sql语言中的order by作用是一样的，会对查询的结果做一次全局排序，所以说，只有hive的sql中制定了order by所有的数据都会到同一个reducer进行处理（不管有多少map，也不管文件有多少的block只会启动一个reducer）。但是对于大量数据这将会消耗很长的时间去执行。
这里跟传统的sql还有一点区别：如果指定了hive.mapred.mode= strict（默认值是nonstrict）,这时就必须指定limit 来限制输出条数，原因是：所有的数据都会在同一个reducer端进行，数据量大的情况下可能不能出结果，那么在这样的严格模式下，必须指定输出的条数。
-------------------------------------

Sort by ：对每一个reduce内部数据进行排序的，全局结果集来说不是有序的,除非只有一个reduce。好处是：执行了局部排序之后可以为接下去的全局排序提高不少的效率（其实就是做一次归并排序就可以做到全局排序了）。
reduce 的个数和设置有关 set mapreduce.job.reduce =3;

--------------------------------------------
distribute by: 对它后面的字段进行分区

distribute by和sort by一起使用

ditribute by是控制map的输出在reducer是如何划分的，举个例子，我们有一张表，mid是指这个store所属的商户，money是这个商户的盈利，name是这个store的名字

store:
mid   money   name
AA   15.0   商店1
AA   20.0   商店2
BB   22.0   商店3
CC   44.0   商店4

执行hive语句：
select mid, money, name from store distribute by mid sort by mid asc, money asc
我们所有的mid相同的数据会被送到同一个reducer去处理，这就是因为指定了distribute by mid，这样的话就可以统计出每个商户中各个商店盈利的排序了（这个肯定是全局有序的，因为相同的商户会放到同一个reducer去处理）。这里需要注意的是distribute by必须要写在sort by之前。
----------------------------
cluster by

distribute by和sort by后面的字段相同时，可用cluster by代替。

cluster by的功能就是distribute by和sort by相结合，如下2个语句是等价的：
select mid, money, name from store cluster by mid
select mid, money, name from store distribute by mid sort by mid
如果需要获得与3中语句一样的效果：
select mid, money, name from store cluster by mid sort by money
注意被cluster by指定的列只能是降序，不能指定asc和desc。

===========================================================
hive自定义udf依赖的包：hadoop基础包&hive-exec
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>0.13.1</version>
</dependency>

创建Function命令: CREATE FUNCTION [db_name.]function_name AS class_name [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ]; file_uri可以为hdfs上文件路径
删除: drop function function_name;
显示: show functions ['regex']

使用database-beifeng13

一、自定义UDF(一个输入，一个输出)
   1. 实现一个大小写转换的自定义函数。
       实现完成后
       1. 添加jar：add jar /home/hadoop/jobs/beifeng14-0.0.1.jar;
       2. 创建function:
           临时function： create temporary function temp_f as 'com.beifeng.hive.ql.UDFLowerOrUpperCase';
           永久function: create function lower_upper as 'com.beifeng.hive.ql.UDFLowerOrUpperCase';
       3. 使用
           select [dbname.]lower_upper(studentname) from beifeng13.students;
二、自定义UDAF(多个输入，一个输出) 和group by一起操作
   1. UDAF介绍
       PARTIAL1：iterate&terminatePartial map的输入阶段
       PARTIAL2: merge&terminatePartial map输出阶段
       FINAL: merge&terminate reducer输入&reducer输出阶段
       COMPLETE: iterate&terminate 没有reducer的map输出阶段
   2. UDAF实例：实现自定义sum函数
       create function self_sum as 'com.beifeng.hive.ql.UDAFSumCase';
三、自定义UDTF(一个输入，多个输出)
   1. UDTF实例：解析爬虫数据，从数据中读取产品id、产品名称、价格。
       1. 实现udtf,并打包jar，copy到linux机器上。
       2. 创建hbase关联表
           create external table hive_data(rowkey string,content string) row format serde 'org.apache.hadoop.hive.hbase.HBaseSerDe' stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with serdeproperties('hbase.columns.mapping'=':key,f:content') tblproperties ('hbase.table.name'='data');
       3. 创建函数
           add jar /home/hadoop/jobs/beifeng14-0.0.1.jar;
           create function f1 as 'com.beifeng.hive.ql.UDTFCase';
四、hive集成自定义函数的各种不同的方式
   1. 修改hive-site.xml
       <property>
<name>hive.aux.jars.path</name>
<value>file:///home/hadoop/jobs/beifeng14-0.0.1.jar</value>
</property>
   最常用的：使用将jar上传到hdfs中，然后再进行操作的方式。
       1. 上传文件到hdfs: dfs -put /home/hadoop/jobs/beifeng14-0.0.1.jar /beifeng/beifeng14-0.0.1.jar
       2. 创建函数:create function f2 as 'com.beifeng.hive.ql.UDTFCase' using jar 'hdfs://hh:8020/beifeng/beifeng14-0.0.1.jar';
       3. 正常使用

sbin/mr-jobhistory-daemon.sh start historyserver