Haddoop大数据教程笔记_08_Hive搭建及使用

目录

Hive 操作笔记 

Hive 安装

1、Mysql安装(hive元数据记录可以使用内嵌derby数据库,但一般选择MySQL)

2、hive安装：

3.hive使用方式

最基本使用方式

启动一个hive交互shell

启动hive服务使用

脚本化运行

4.hive建库建表与数据导入

4.1.建库

4.2.建表

5.hive查询语法

6.hive常见参数

7.hive 其他常用操作：

---

Hive 操作笔记 

 

Hive是基于Hadoop的一个数据仓库工具(离线)，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

Hive是一个单机程序，其查询工作原理是将HQL解析成MapReduceTask提交job给Yarn去执行hdfs文件系统上的数据映射(hive2.+以上版本使用的MapReduce框架是spark)。

因此在Hive使用要建立在yarn和hdfs集群正常工作基础上。

 

Hive 安装

 

1、Mysql安装(hive元数据记录可以使用内嵌derby数据库,但一般选择MySQL)

MySQL安装不赘述,需注意以下几点：

1)权限授予 

    grant all privileges on *.* to 'root'@'%' identified by 'root';
    flush privileges;

2)字符集修改

vi /etc/my.cnf  增加以下内容：

    [client]
    default-character-set=utf8
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci

2、hive安装：

1.安装：直接解压一个hive安装包即可

2.上传一个mysql的驱动jar包到hive的安装目录的lib中(mysql-connector-java-5.1.39.jar)

3.配置hive源数据库配置 hive 的目录到环境变量中 

vi apps/apache-hive-1.2.1-bin/conf/hive-site.xml

    <configuration>
    <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
    <description>JDBC connect string for a JDBC metastore</description>
    </property>
    
    <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
    </property>
    
    <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
    <description>username to use against metastore database</description>
    </property>
    
    <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>root</value>
    <description>password to use against metastore database</description>
    </property>
    </configuration>

4.配置HADOOP_HOME 和HIVE_HOME到系统环境变量中：

    /etc/profile  
    #eg.
    export HIVE_HOME=/root/apps/apache-hive-1.2.1-bin
    export PATH=.:$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin            
    source /etc/profile

5.命令启动hive交互界面:

    [root@hdp20-04 ~]# hive

启动时SSL报错解决办法：

    在连接数据库的URL中加上：?useUnicode=true&characterEncoding=utf-8&useSSL=false该语句即可
    Xml文件中不能使用&，要使用他的转义&来代替。

3.hive使用方式

最基本使用方式

启动一个hive交互shell

    bin/hive
    hive>

设置一些基本参数，让hive使用起来更便捷，比如：

1、让提示符显示当前库：

    hive>set hive.cli.print.current.db=true;

2、显示查询结果时显示字段名称：

    hive>set hive.cli.print.header=true; 

但是这样设置只对当前会话有效，重启hive会话后就失效，解决办法：

在linux的当前用户目录中，编辑一个.hiverc文件，将参数写入其中：

    vi .hiverc
    set hive.cli.print.header=true;
    set hive.cli.print.current.db=true;

启动hive服务使用

启动hive的服务：

    bin/hiveserver2 -hiveconf hive.root.logger=DEBUG,console

上述启动，会将这个服务启动在前台，如果要启动在后台，则命令如下：

    nohup bin/hiveserver2 1>/dev/null 2>&1 &

./[].sh 1> /root/[].log 2> /root/[].err &   某脚本进程启动后输出重定向到[1正常输出][2异常输出] 

fg 1  q切换到前台展示

nohup ./[].sh 1> /dev/null 2>&1 &     永久启动某进程,不打印日志,[nohup  防止用户退出 导致的进程结束]

启动成功后，可以在别的节点上用beeline去连接

方式（1）

    [root@hdp20-04 hive-1.2.1]# bin/beeline  回车，进入beeline的命令界面

输入命令连接hiveserver2

    beeline> !connect jdbc:hive2://hdp-03:10000
    （hdp-03是hiveserver2所启动的那台主机名，端口默认是10000）

方式（2）

启动时直接连接：

    bin/beeline -u jdbc:hive2://hdp-03:10000 -n root

接下来就可以做正常sql查询了

 

脚本化运行

大量的hive查询任务，如果用交互式shell来进行输入的话，显然效率及其低下，因此，生产中更多的是使用脚本化运行机制：

该机制的核心点是：hive可以用一次性命令的方式来执行给定的hql语句

    hive -e "insert into table t_dest select * from t_src;"

然后，进一步，可以将上述命令写入shell脚本中，以便于脚本化运行hive任务，并控制、调度众多hive任务，示例如下：

    vi t_order_etl.sh
    #!/bin/bash
    hive -e "select * from db_order.t_order"
    hive -e "select * from default.t_user"
    hql="create table  default.t_bash as select * from db_order.t_order"
    hive -e "$hql"

如果要执行的hql语句特别复杂，那么，可以把hql语句写入一个文件：

    vi x.hql
    select * from db_order.t_order;
    select count(1) from db_order.t_user;

然后，用hive -f /root/x.hql 来执行

 

4.hive建库建表与数据导入

4.1.建库

hive中有一个默认的库：

库名：default

库目录：hdfs://hdp20-01:9000/user/hive/warehouse

 

新建库：

create database db_order;

库建好后，在hdfs中会生成一个库目录：

hdfs://hdp20-01:9000/user/hive/warehouse/db_order.db

 

4.2.建表

4.2.1.基本建表语句

    use db_order;
    create table t_order(id string,create_time string,amount float,uid string);

表建好后，会在所属的库目录中生成一个表目录

/user/hive/warehouse/db_order.db/t_order

只是，这样建表的话，hive会认为表数据文件中的字段分隔符为 ^A

正确的建表语句为：

    create table t_order(id string,create_time string,amount float,uid string)
    row format delimited
    fields terminated by ',';

这样就指定了，我们的表数据文件中的字段分隔符为 ","

4.2.2.删除表

drop table t_order;

删除表的效果是：

hive会从元数据库中清除关于这个表的信息；

hive还会从hdfs中删除这个表的表目录；

 

4.2.3.内部表与外部表

内部表(MANAGED_TABLE)：表目录按照hive的规范来部署，位于hive的仓库目录/user/hive/warehouse中

外部表(EXTERNAL_TABLE)：表目录由建表用户自己指定

    create external table t_access(ip string,url string,access_time string)
    row format delimited
    fields terminated by ','
    location '/access/log';

 

外部表和内部表的特性差别：

1、内部表的目录在hive的仓库目录中 VS 外部表的目录由用户指定

2、drop一个内部表时：hive会清除相关元数据，并删除表数据目录

3、drop一个外部表时：hive只会清除相关元数据；

 

一个hive的数据仓库，最底层的表，一定是来自于外部系统，为了不影响外部系统的工作逻辑，在hive中可建external表来映射这些外部系统产生的数据目录；

然后，后续的etl操作，产生的各种表建议用managed_table

 

 

4.2.4.分区表

分区表的实质是：在表目录中为数据文件创建分区子目录，以便于在查询时，MR程序可以针对分区子目录中的数据进行处理，缩减读取数据的范围。

 

比如，网站每天产生的浏览记录，浏览记录应该建一个表来存放，但是，有时候，我们可能只需要对某一天的浏览记录进行分析

这时，就可以将这个表建为分区表，每天的数据导入其中的一个分区；

当然，每日的分区目录，应该有一个目录名（分区字段）

 

一个分区字段的实例：

示例如下：

1、创建带分区的表

    create table t_access(ip string,url string,access_time string)
    partitioned by(dt string)
    row format delimited
    fields terminated by ',';  

注意：分区字段不能是表定义中的已存在字段

2、向分区中导入数据

    load data local inpath '/root/access.log.2017-08-04.log' into table t_access partition(dt='20170804');
    load data local inpath '/root/access.log.2017-08-05.log' into table t_access partition(dt='20170805');

3、针对分区数据进行查询

a、统计8月4号的总PV：

    select count(*) from t_access where dt='20170804';

实质：就是将分区字段当成表字段来用，就可以使用where子句指定分区了

b、统计表中所有数据总的PV：

    select count(*) from t_access;

实质：不指定分区条件即可

多个分区字段示例

建表：

    create table t_partition(id int,name string,age int)
    partitioned by(department string,sex string,howold int)
    row format delimited fields terminated by ',';

导数据：

    load data local inpath '/root/p1.dat' into table t_partition partition(department='xiangsheng',sex='male',howold=20);

 

4.2.5.CTAS建表语法

可以通过已存在表来建表：

1、create table t_user_2 like t_user;

新建的t_user_2表结构定义与源表t_user一致，但是没有数据

2、在建表的同时插入数据

    create table t_access_user 
    as
    select ip,url from t_access;

t_access_user会根据select查询的字段来建表，同时将查询的结果插入新表中

4.3.数据导入导出

4.3.1.将数据文件导入hive的表

方式1：导入数据的一种方式：

手动用hdfs命令，将文件放入表目录；

方式2：在hive的交互式shell中用hive命令来导入本地数据到表目录

    hive>load data local inpath '/root/order.data.2' into table t_order;

方式3：用hive命令导入hdfs中的数据文件到表目录

    hive>load data inpath '/access.log.2017-08-06.log' into table t_access partition(dt='20170806');

注意：导本地文件和导HDFS文件的区别：

本地文件导入表：复制

hdfs文件导入表：移动

 

4.3.2.将hive表中的数据导出到指定路径的文件

1、将hive表中的数据导入HDFS的文件

    insert overwrite directory '/root/access-data'
    row format delimited fields terminated by ','
    select * from t_access;

2、将hive表中的数据导入本地磁盘文件

    insert overwrite local directory '/root/access-data'
    row format delimited fields terminated by ','
    select * from t_access limit 100000;

4.3.3.hive文件格式

HIVE支持很多种文件格式：SEQUENCE FILE | TEXT FILE | PARQUET FILE | RC FILE

    create table t_pq(movie string,rate int)  stored as textfile;
    create table t_pq(movie string,rate int)  stored as sequencefile;
    create table t_pq(movie string,rate int)  stored as parquetfile;

演示：

1、先建一个存储文本文件的表

    create table t_access_text(ip string,url string,access_time string)
    row format delimited fields terminated by ','
    stored as textfile;

导入文本数据到表中：

    load data local inpath '/root/access-data/000000_0' into table t_access_text;

2、建一个存储sequence file文件的表：

    create table t_access_seq(ip string,url string,access_time string)
    stored as sequencefile;

从文本表中查询数据插入sequencefile表中，生成数据文件就是sequencefile格式的了：

    insert into t_access_seq select * from t_access_text;

3、建一个存储parquet file文件的表：

    create table t_access_parq(ip string,url string,access_time string)
    stored as parquetfile;

4.4.数据类型

4.4.1.数字类型

TINYINT (1-byte signed integer, from -128 to 127)

SMALLINT (2-byte signed integer, from -32,768 to 32,767)

INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)

BIGINT (8-byte signed integer, from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)

FLOAT (4-byte single precision floating point number)

DOUBLE (8-byte double precision floating point number)

示例：

create table t_test(a string ,b int,c bigint,d float,e double,f tinyint,g smallint)

4.4.2.日期时间类型

TIMESTAMP (Note: Only available starting with Hive 0.8.0)

DATE (Note: Only available starting with Hive 0.12.0)

示例，假如有以下数据文件：

1,zhangsan,1985-06-30

2,lisi,1986-07-10

3,wangwu,1985-08-09

那么，就可以建一个表来对数据进行映射

create table t_customer(id int,name string,birthday date)

row format delimited fields terminated by ',';

然后导入数据

load data local inpath '/root/customer.dat' into table t_customer;

然后，就可以正确查询

 

4.4.3.字符串类型

STRING

VARCHAR (Note: Only available starting with Hive 0.12.0)

CHAR (Note: Only available starting with Hive 0.13.0)

 

4.4.4.混杂类型

BOOLEAN

BINARY (Note: Only available starting with Hive 0.8.0)

 

4.4.5.复合类型

array数组类型

arrays: ARRAY<data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)

 

示例：array类型的应用

假如有如下数据需要用hive的表去映射：

战狼2,吴京:吴刚:龙母,2017-08-16

三生三世十里桃花,刘亦菲:痒痒,2017-08-20

设想：如果主演信息用一个数组来映射比较方便

 

建表：

    create table t_movie(moive_name string,actors array<string>,first_show date)
    row format delimited fields terminated by ','
    collection items terminated by ':';

导入数据：

    load data local inpath '/root/movie.dat' into table t_movie;

查询：

    select * from t_movie;
    select moive_name,actors[0] from t_movie;
    select moive_name,actors from t_movie where array_contains(actors,'吴刚');
    select moive_name,size(actors) from t_movie;

map类型

maps: MAP<primitive_type, data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)

1)假如有以下数据：

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28

2,lisi,father:mayun#mother:huangyi#brother:guanyu,22

3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29

4,mayun,father:mayongzhen#mother:angelababy,26

可以用一个map类型来对上述数据中的家庭成员进行描述

2)建表语句：

    create table t_person(id int,name string,family_members map<string,string>,age int)
    row format delimited fields terminated by ','
    collection items terminated by '#'
    map keys terminated by ':';

3)查询

    select * from t_person;

## 取map字段的指定key的值

    select id,name,family_members['father'] as father from t_person;

## 取map字段的所有key

    select id,name,map_keys(family_members) as relation from t_person;

## 取map字段的所有value

    select id,name,map_values(family_members) from t_person;
    select id,name,map_values(family_members)[0] from t_person;

## 综合：查询有brother的用户信息

    select id,name,father 
    from 
    (select id,name,family_members['brother'] as father from t_person) tmp
    where father is not null;

struct类型(结构体,使用了C语言类似java对象object的数据结构)

structs: STRUCT<col_name : data_type, ...>

 

1)假如有如下数据：

1,zhangsan,18:male:beijing

2,lisi,28:female:shanghai

 

其中的用户信息包含：年龄：整数，性别：字符串，地址：字符串

设想用一个字段来描述整个用户信息，可以采用struct

 

2)建表：

    create table t_person_struct(id int,name string,info struct<age:int,sex:string,addr:string>)
    row format delimited fields terminated by ','
    collection items terminated by ':';

3)查询

    select * from t_person_struct;
    select id,name,info.age from t_person_struct;

4.5.修改表定义

仅修改Hive元数据，不会触动表中的数据，用户需要确定实际的数据布局符合元数据的定义。

修改表名：

    ALTER TABLE table_name RENAME TO new_table_name
    示例：alter table t_1 rename to t_x;

修改分区名：

    alter table t_partition partition(department='xiangsheng',sex='male',howold=20) rename to partition(department='1',sex='1',howold=20);

添加分区：

    alter table t_partition add partition (department='2',sex='0',howold=40); 

删除分区：

    alter table t_partition drop partition (department='2',sex='2',howold=24); 

修改表的文件格式定义：

    ALTER TABLE table_name [PARTITION partitionSpec] SET FILEFORMAT file_format
    alter table t_partition partition(department='2',sex='0',howold=40 ) set fileformat sequencefile;

 

修改列名定义：

    ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENTcol_comment] [FIRST|(AFTER column_name)]  
    alter table t_user change price jiage float first;

增加/替换列：

    ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type[COMMENT col_comment], ...)  
    alter table t_user add columns (sex string,addr string);
    alter table t_user replace columns (id string,age int,price float);

 

5.hive查询语法

提示：在做小数据量查询测试时，可以让hive将mrjob提交给本地运行器运行，可以在hive会话中设置如下参数：

  hive> set hive.exec.mode.local.auto=true;

 

5.1.基本查询示例

    select * from t_access;    select count(*) from t_access;    select max(ip) from t_access;

        5.2.条件查询

    select * from t_access where access_time<'2017-08-06 15:30:20'    select * from t_access where access_time<'2017-08-06 16:30:20' and ip>'192.168.33.3';

5.3.join关联查询示例

1、inner join（join） 内连接

2、left outer join（left join） 左(内)连接

3、right outer join（right join） 右(内)连接

4、full outer join（full join） 全连接 

5.4.left semi join  左表中,右表存在的数据.

hive中不支持exist/IN子查询，可以用left semi join来实现同样的效果

注意：left semi join的 select子句中，不能有右表的字段

 

5.5.group by分组聚合  (group by是分组聚合运算,一组数据计算一次,因此只能与分组字段,或其他聚合函数一起select)

    select dt,count(*),max(ip) as cnt from t_access group by dt;
    select dt,count(*),max(ip) as cnt from t_access group by dt having dt>'20170804';
    select 
    dt,count(*),max(ip) as cnt 
    from t_access 
    where url='http://www.edu360.cn/job'
    group by dt having dt>'20170804';

               注意：一旦有group by子句，那么，在select子句中就不能有 （分组字段，聚合函数） 以外的字段

 

## 为什么where必须写在group by的前面，为什么group by后面的条件只能用having

因为，where是用于在真正执行查询逻辑之前过滤数据用的

having是对group by聚合之后的结果进行再过滤；

 

上述语句的执行逻辑：

1、where过滤不满足条件的数据

2、用聚合函数和group by进行数据运算聚合，得到聚合结果

3、用having条件过滤掉聚合结果中不满足条件的数据

 

5.6.子查询

 

    select id,name,father     from     (select id,name,family_members['brother'] as father from t_person) tmp    where father is not null;

6.hive常见参数

 

  显示列名称 set hive.cli.print.header=true;  显示数据库名称 set hive.cli.print.current.db=true;  本地模式 set hive.exec.mode.local.auto=true;  开启分桶 set hive.enforce.bucketing=true;  开启动态分区支持 set hive.exec.dynamic.partition=true;  动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示 允许所有的分区字段都可以使用动态分区。  set hive.exec.dynamic.partition.mode=nonstrict;    允许所有的分区列都是动态分区列  set hive.exec.dynamic.partition.mode=nostrict;    在每个执行 MR 的节点上，最大可以创建多少个动态分区，如果超过了这个数量就会报错 hive.exec.max.dynamic.partitions.pernode （缺省值 100）：在所有执行 MR 的节点上，最大一共可以创建多少个动态分区  hive.exec.max.dynamic.partitions （缺省值 1000）：    整个 MR Job 中，最大可以创建多少个 HDFS 文件  hive.exec.max.created.files （缺省值 100000）：    对分区表查询必须加分区过滤,不允许笛卡尔积查询，order by 后面必须有 limit 限制  set hive.mapred.mode=strict;  不严格模式  set hive.mapred.mode=nostrict;  开启任务并行执行  set hive.exec.parallel=true;  开启 map join  set hive.auto.convert.join=true;  set hive.mapjoin.smalltable.filesize=25000000;    忽略 MAPJOIN 标记  set hive.ignore.mapjoin.hint=true;

 

7.hive 其他常用操作：

 

时间戳函数

 

    select current_date from dual;    select current_timestamp from dual;    select unix_timestamp() from dual;    1491615665    select unix_timestamp('2011-12-07 13:01:03') from dual;    1323234063    select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from dual;    1323234063    select from_unixtime(1323234063,'yyyy-MM-dd HH:mm:ss') from dual;

 

获取日期、时间

 

    select year('2011-12-08 10:03:01') from dual;    2011    select year('2012-12-08') from dual;    2012    select month('2011-12-08 10:03:01') from dual;    12    select month('2011-08-08') from dual;    8    select day('2011-12-08 10:03:01') from dual;    8    select day('2011-12-24') from dual;    24    select hour('2011-12-08 10:03:01') from dual;    10    select minute('2011-12-08 10:03:01') from dual;    3    select second('2011-12-08 10:03:01') from dual;    1

 

日期增减

 

    select date_add('2012-12-08',10) from dual;    2012-12-18    date_sub (string startdate, int days) : string    例：    select date_sub('2012-12-08',10) from dual;    2012-11-28

 

json函数

    create table t_rate 
    as 
    select uid,movie,rate,year(from_unixtime(cast(ts as bigint))) as year,month(from_unixtime(cast(ts as bigint))) as month,day(from_unixtime(cast(ts as bigint))) as day,hour(from_unixtime(cast(ts as bigint))) as hour,
    minute(from_unixtime(cast(ts as bigint))) as minute,from_unixtime(cast(ts as bigint)) as ts
    from 
    (select 
    json_tuple(rateinfo,'movie','rate','timeStamp','uid') as(movie,rate,ts,uid)
    from t_json) tmp
    ;

分组topn

    select *,row_number() over(partition by uid order by rate desc) as rank from t_rate;
    select uid,movie,rate,ts
    from 
    (select uid,movie,rate,ts,row_number() over(partition by uid order by rate desc) as rank from t_rate) tmp
    where rank<=3;

网页URL数据解析函数：parse_url_tuple

    select parse_url_tuple("http://www.edu360.cn/baoming/youhui?cookieid=20937219375",'HOST','PATH','QUERY','QUERY:cookieid') 
    from dual;

行转列

concat_ws(',',collect_set(column)) collect_set去重,collect_set不去重

列转行

lateral view explode()

窗口分析函数  sum() over() ：可以实现在窗口中进行逐行累加

sum(sum_times) over(partition by uid order by month rows between unbounded preceding and current row) sum_all