07-hive---基础部分2

一、Hive的远程模式

比如：你想通过DataGrip操作hive，这个时候必须开启远程模式。

1、创建临时目录

[root@bigdata01 ~]# cd /opt/installs/hive/
[root@bigdata01 hive]# mkdir iotmp
[root@bigdata01 hive]# chmod 777 iotmp

2、前期准备工作

hive-site.xml

<!--Hive工作的本地临时存储空间-->
<property>
    <name>hive.exec.local.scratchdir</name>
    <value>/opt/installs/hive/iotmp/root</value>
</property>
<!--如果启用了日志功能，则存储操作日志的顶级目录-->
<property>
    <name>hive.server2.logging.operation.log.location</name>
    <value>/opt/installs/hive/iotmp/root/operation_logs</value>
</property>
<!--Hive运行时结构化日志文件的位置-->
<property>
    <name>hive.querylog.location</name>
    <value>/opt/installs/hive/iotmp/root</value>
</property>
<!--用于在远程文件系统中添加资源的临时本地目录-->
<property>
    <name>hive.downloaded.resources.dir</name>
    <value>/opt/installs/hive/iotmp/${Hive.session.id}_resources</value>
</property>

修改 core-site.xml【hadoop】的

<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.http.staticuser.user</name>
    <value>root</value>
</property>
<!-- 不开启权限检查 -->
<property>
   <name>dfs.permissions.enabled</name>
   <value>false</value>
</property>

修改集群的三个core-site.xml，记得修改一个，同步一下，并且重启hdfs

xsync.sh core-site.xml
stop-dfs.sh
start-dfs.sh

3、开始配置远程服务（两个）

1）配置hiveserver2服务

修改hive-site.xml

<property>
    <name>hive.server2.thrift.bind.host</name>
    <value>bigdata01</value>
    <description>Bind host on which to run the HiveServer2 Thrift service.</description>
  </property>
  <property>
    <name>hive.server2.thrift.port</name>
    <value>10000</value>
    <description>Port number of HiveServer2 Thrift interface when hive.server2.transport.mode is 'binary'.</description>
  </property>

可以启动：

1. 该服务端口号默认是10000
2. 可以单独启动此服务进程，供远程客户端连接；此服务内置metastore服务。
3. 启动方式：
   方法1：
       直接调用hiveserver2。会进入监听状态不退出。
   方法2：
       hive --service hiveserver2 &    # 进入后台启动
   方法3：
      nohup hive --service hiveserver2 >/dev/null 2>&1 & #信息送入黑洞。

演示第一种启动方式：hiveserver2

 可以使用beeline进行测试：

连接方式：
方式1：
   	step1. beeline 回车
   	step2. !connect jdbc:hive2://bigdata01:10000 回车
   	step3. 输入用户名 回车 数据库用户名
   	step4. 输入密码 回车  此处的密码是数据库密码
方法2(直连)：
	beeline -u jdbc:hive2://bigdata01:10000 -n 用户名
解析: 
	hive2，是Hive的协议名称
	ip:  Hiveserver2服务所在的主机IP。
	10000，是Hiveserver2的端口号
退出：
    Ctrl+ C 可以退出客户端

2) metastore 服务

metastore服务意义：为别人连接mysql元数据提供服务的。

警告：
假如   hive 直接进入的，操作了数据库，其实底层已经帮助创建了一个metastore服务器，可能叫ms01
通过hiveserver2 运行的命令，默认底层帮你创建了一个metastore服务器，可能叫ms02，假如有很多人连接我的mysql，就会有很多个metastore，非常的占用资源。
解决方案就是：配置一个专门的metastore,只有它可以代理mysql服务，别人必须经过它跟mysql进行交互。这样解决内存。
警告：只要配置了metastore以后，必须启动，否则报错！

修改hive-site.xml

 修改hive-site.xml的配置
   注意：想要连接metastore服务的客户端必须配置如下属性和属性值
    <property>
        <name>hive.metastore.uris</name> 
        <value>thrift://bigdata01:9083</value>
    </property>

    解析：thrift:是协议名称
         ip为metastore服务所在的主机ip地址
         9083是默认端口号

启动方式：

方法1：
		hive --service metastore &
	方法2：
    	nohup hive --service metastore 2>&1 >/dev/null &  #信息送入黑洞。
         解析：2>&1 >/dev/null   意思就是把错误输出2重定向到标准出书1，也就是屏幕，标准输出进了“黑洞”，也就是标准输出进了黑洞，错误输出打印到屏幕。
              Linux系统预留可三个文件描述符：0、1和2，他们的意义如下所示：
                0——标准输入（stdin）
                1——标准输出（stdout）
                2——标准错误（stderr）

测试：

没有启动metastore 服务器之前，hive进入报错！
hive> show databases;
FAILED: HiveException java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

启动之后，直接测试，发现可以使用。
hive> show databases;
OK
default
Time taken: 1.211 seconds, Fetched: 1 row(s)

4、使用客户端连接工具连接hive

常见的Hive连接工具有：IDEA、DBeaver、DataGrap

推荐大家使用DataGrap

请检查你的 metastore和hiveserver2是否启动
ps -ef|grep metastore
ps -ef|grep hiveserver2

假如没有启动：
nohup hive --service metastore 2>&1 >/dev/null &
nohup hive --service hiveserver2 2>&1 >/dev/null &

由于没办法看到4个session ID,等一下。

下载驱动包：

二、Hive基础

为了节约资源，我们每次只启动metastore服务即可使用。

杀死hiveserver2

ps -ef| grep hiveserver2 | grep -v grep | awk '{print $2}' | xargs kill -9

标识符规则：

大小写规则:
1. hive的数据库名、表名都不区分大小写
2. 建议关键字大写
命名规则：
1. 名字不能使用数字开头
2. 不能使用关键字
3. 尽量不使用特殊符号

1、操作数据库

语句：

--创建数据库
hive> create database yhdb01;
--先判断数据库是否存在再创建
hive> create database if not exists yhdb02;
--判断数据库并在创建数据库时添加注释
hive> create database if not exists yhdb03 comment 'this is a database of yunhe';

hive默认元数据是放在mysql数据库中的，但是mysql数据库在刚开始的时候是不支持中文的，hive的元数据的数据库名字叫hive,创建该数据库的时候使用的字符集是latin 字符，不支持中文。
不信的话，创建一个带有中文解释的数据库：
create database if not exists yhdb02 comment "大数据"

创建hive数据库之后，在你的hdfs上会多一个文件夹：

而且，在mysql的元数据库，查看数据：

查看所有数据库：

show databases;

切换数据库：

use yhdb;
use default;

查看数据库结构：

语法1：desc database databaseName;
语法2：desc database extended databaseName;
语法3：describe database extended databaseName;


这三个语句的展示效果一样。

hive> describe database extended yhdb;
OK
yhdb            hdfs://bigdata01:9820/user/hive/warehouse/yhdb.db       root    USER    
Time taken: 0.067 seconds, Fetched: 1 row(s)

删除数据库：

语法1：drop database databasename;         	  # 这个只能删除空库
语法2：drop database databasename cascade;    # 如果不是空库，则可以加cascade强制删除

2、关于表的操作

1）关于字符类型的

Hive的数据类型分为基本数据类型和复杂数据类型,下面是基本数据类型：

只需要简单的记忆几个即可：
INT  ,STRING
因为varchar可以存储的字段长度有限，String可以存储高达2G的字符串长度，而且是可变长度的。
create table stu(name varchar(20));
create table stu(name string);

 2）创建表

语法1: 
    create table t_user(id int,name string);  

语法2：使用库.表的形式
    create table yhdb.t_user(id int,name string);

语法3：指定分隔规则形式
create table yhdb.t_user2(
 id int,
 name string,
 age int,
 height double
)
comment "这是一个学生表"
row format delimited 
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

查看你当前在哪个数据库下面：

hive> select current_database();
OK
yhdb02

切换数据库：use yhdb;

查看所有表：
show tables;

-- 实战：创建一个emp表
create table if not exists emp(
eno int,
ename string,
job string, 
mgr int,
hiredate int,
salary int,
comm int,
deptno int
)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

创建完一个表之后，在你的hdfs上多了一个文件夹：

查看mysql中的元数据： 

查看表结构：

desc tableName
desc extended tableName;
describe extended tableName;



hive> desc t_user2;
OK
id                      int                                         
name                    string                                      
age                     int                                         
height                  double                                      
Time taken: 0.185 seconds, Fetched: 4 row(s)

3) 修改表 

- 修改表名
alter table oldTableName rename to newTableName;

- 修改列名：change column    和修改字段类型是同一个语法
    alter table tableName change column oldName newName colType;
    alter table tableName change column colName colName colType;

- 修改列的位置:  注意，2.x版本后，必须是相同类型进行移动位置。
    alter table tableName change column colName colName colType after colName1;   
    alter table tableName change column colName colName colType first;

- 增加字段：add columns
    alter table tableName add columns (sex int,...);

- 删除字段：replace columns  #注意，2.x版本后，注意类型的问题，替换操作，其实涉及到位置的移动问题。
    alter table tableName replace columns(
    id int,
    name int,
    size int,
    pic string
    );
    注意：实际上是保留小括号内的字段。

4）删除表

drop table 表的名字;

5) 小案例演示

create table stu(id int, name string);
insert into stu values(1,"张三");
drop table stu;

3、Hive中经常使用的小技巧的设置

1）配置打印当前数据库

比如：在shell中，可以查看到当前数据库的名字
在hive的家目录下的conf文件夹下，创建  .hiverc 文件
cd /opt/installs/hive/conf 下面
touch .hiverc

在这个.hiverc 文件中，添加：
set hive.cli.print.current.db=true;

 2）开启本地化模式

以下这个配置是使用本地资源跑MR任务：

set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.inputbytes.max=134217728;
set hive.exec.mode.local.auto.input.files.max=4;

测试：

同样插入一个表的数据，本地模式插入速度远远快于集群模式。

4、加载数据

1）加载本地数据：

创建一个文件夹，将来把所有的数据，都放在这个文件下：
mkdir /home/hivedata
创建一个文件 user.txt
添加如下数据：
1,廉德枫
2,刘浩
3,王鑫
4,司翔

先有数据，根据数据的格式，和字段数量以及类型，创建一个表：

create table t_user(
id int,
name string
)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

加载本地数据：

load data local inpath "/home/hivedata/user.txt" into table t_user;

查看数据是否加载成功：
select * from t_user limit 10;

你能够看到里面的数据，并且没有乱码，原因是stored as textfile。

 2）从HDFS加载到Hive中：

hdfs dfs -put /home/hivedata/user.txt /home

将hdfs的数据导入到t_user 表中：

load data inpath '/home/user.txt' into table t_user;

就比之前少了一个 local 关键字。

查看hdfs上的user.txt 发现不见了，去哪里了，被移动走了！ 

思考一下：为什么是移动，而不是复制？

因为hdfs上的数据，默认都以为比较大，所以如果相同的数据占2份，非常的消耗空间。

 查看数据，发现有两份，想覆盖怎么办？

load data local inpath '/home/hivedata/user.txt' overwrite into table t_user;

3）将数据直接放入表对应的文件夹下

再思考一个问题：既然hive中的数据是在hdfs上的，我们也可以手动的上传数据，能上传至/home，为何不能上传至：/user/hive/warehouse/yhdb.db/t_user

[root@bigdata01 hivedata]# cp user.txt user2.txt 
[root@bigdata01 hivedata]# hdfs dfs -put /home/hivedata/user2.txt /user/hive/warehouse/yhdb.db/t_user

hive中的数据，不要load 也可以被正常使用。

4）从其他表中加载数据

语法格式：

insert into table tableName2 select [.....] from tableName1;

扩展内容：向多张表中插入数据的语法
    from tableName1
    insert into tableName2 select * where 条件
    insert into tableName3 select * where 条件

实战：
insert into table t_user2 select * from t_user;
这个sql的前提条件是：必须先创建一个t_user2
快速创建一个同样的表，只要表结构：
create table t_user2 like t_user;
创建完之后再运行
insert into table t_user2 select * from t_user;


创建t_user3和 4
create table t_user5 like t_user;
create table t_user4 like t_user;

from t_user2
insert into t_user4 select *
insert into t_user5 select id,name;

5) 克隆表数据

- create table if not exists tableName2 as select [....] from tableName1;
- create table if not exists tableName2 like tableName1 location 'tableName1的存储目录的路径'     # 新表不会产生自己的表目录，因为用的是别的表的路径
​
扩展内容：只复制表结构
create table if not exists tableName2 like tableName1;

实战：
create table t_user6 as select * from t_user2;
create table t_user7 like t_user2 location '/user/hive/warehouse/yhdb.db/t_user2';

5、通过hive进行流量统计

根据数据结构，创建表结构：

CREATE TABLE flow(
id             string ,
phonenumber     string,
mac             string,
ip               string,
url              string,
urltype          string,
uppacket         int,
downpacket       int,
upflow            int,
downflow         int,
issuccess    int
)ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ' ';

hive (yhdb)> load data local inpath '/home/hivedata/HTTP_20130313143750.dat' into table flow;
Loading data to table yhdb.flow
OK
Time taken: 0.741 seconds
hive (yhdb)> select * from flow limit 3;
OK
1       15649428888     00-0C-29-CD-75-8C:CMCC-EASY     192.169.17.8    www.bjfkfu.com  88      25 751      2694    200     1446307200
2       13243983434     06-0C-29-CD-79-8C:CMCC-EASY     192.169.0.46    www.fgd.com     99      88 1913     3440    200     1446307200
3       15642428887     00-7C-29-CD-79-8C:CMCC-EASY     192.169.139.91  www.blfy.com    16      75 938      1720    200     1446307200
Time taken: 0.331 seconds, Fetched: 3 row(s)

需求：统计每一个手机号码的上行和下行流量以及总流量。
 

select phonenumber,sum(upflow) ,sum(downflow) ,sum(upflow+downflow) from flow group by phonenumber;

再搞一个需求：统计访问次数最多的三个url 是什么？
 

select url,count(1) from flow group by url order by count(1) desc limit 3;