Hive其三，数据库操作，小技巧设置，加载数据等操作

一、操作数据库

二、关于表的操作

1）关于字符类型的

2）创建表

3) 修改表

4）删除表

5) 小案例演示

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

四、加载数据

1）加载本地数据：

2）从HDFS加载到Hive中：

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

4）从其他表中加载数据

5) 克隆表数据

五、通过hive进行流量统计

一、操作数据库

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

ps -ef| grep hiveserver2 | grep -v grep | awk '{print $2}' | xargs kill -9也可以这么干：
hive-service-manager.sh stop hiveserver2

标识符规则：

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

语句：

--创建数据库
hive> create database databaseName01;
--先判断数据库是否存在再创建
hive> create database if not exists databaseName02;
--判断数据库并在创建数据库时添加注释
hive> create database if not exists databaseName03 comment 'this is a database of databaseName';hive默认元数据是放在mysql数据库中的，但是mysql数据库在刚开始的时候是不支持中文的，hive的元数据的数据库名字叫hive,创建该数据库的时候使用的字符集是latin 字符，不支持中文。
不信的话，创建一个带有中文解释的数据库：
create database if not exists databaseName02 comment "大数据"

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

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

查看所有数据库：

show databases;

切换数据库：

use default;

查看数据库结构：

语法1：desc database databaseName;
语法2：desc database extended databaseName;
语法3：describe database extended databaseName;这三个语句的展示效果一样。hive> describe database extended databaseName;
OK
databaseName            hdfs://bigdata01:9820/user/hive/warehouse/databaseName.db       root    USER    
Time taken: 0.067 seconds, Fetched: 1 row(s)

删除数据库：

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

二、关于表的操作

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 databasename.t_user(id int,name string);语法3：指定分隔规则形式
create table databasename.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
databasename02切换数据库：use databasename;

查看所有表：
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上多了一个文件夹：

查看表结构：

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;
alter table stu rename to new_stu;- 修改列名：change column    和修改字段类型是同一个语法alter table tableName change column oldName newName colType;alter table tableName change column colName colName colType;alter table new_stu change name name varchar(200);alter table new_stu change name uname varchar(200);- 修改列的位置:  注意，2.x版本后，必须是相同类型进行移动位置。alter table tableName change column colName colName colType after colName1;   alter table new_stu change column uname uname varchar(200)  after city;alter table tableName change column colName colName colType first;- 增加字段：add columnsalter table tableName add columns (sex int,...);alter table new_stu add columns(city varchar(20));   - 删除字段：replace columns  #注意，2.x版本后，注意类型的问题，替换操作，其实涉及到位置的移动问题。alter table tableName replace columns(id int,name int,size int,pic string);alter table new_stu replace columns(id int,uname string);注意：实际上是保留小括号内的字段。

4）删除表

drop table 表的名字;

5) 小案例演示

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

三、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;

测试：

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

假如你使用的是DataGrip，需要在console中，输入以下命令设置本地模式：

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;

假如遇到运行失败的语句，记得查看日志：/tmp/root/hive.log 文件。

假如是如下错误：

2024-08-21T22:26:11,380  WARN [99af8a48-832e-460a-a7e3-19b6db1f84cb main] metastore.HiveMetaStoreClient: Got error flushing the cache
org.apache.thrift.transport.TTransportException: nullat org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:132) ~[hive-exec-3.1.2.jar:3.1.2]

说法是：

在执行Hive插入数据时遇到org.apache.hadoop.hive.ql.metadata.HiveException: org.apache.thrift.transport.TTransport错误。官方认为这是由于在创建表时列属性设置不当导致的bug。解决方法包括在建表前设置特定属性，或者如果表已创建，可通过执行load数据操作从本地加载数据，从而解决问题。

因为不影响结果，另外我们以后也很少使用 insert 插入数据，所以不予解决。

假如查看到是这个错误：

Caused by: java.lang.OutOfMemoryError: Java heap spaceat org.apache.hadoop.mapred.MapTask$MapOutputBuffer.init(MapTask.java:1001) ~[hadoop-mapreduce-client-core-3.3.1.jar:?]at org.apache.hadoop.mapred.MapTask.createSortingCollector(MapTask.java:409) ~[hadoop-mapreduce-client-core-3.3.1.jar:?]at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:455) ~[hadoop-mapreduce-client-core-3.3.1.jar:?]at org.apache.hadoop.mapred.MapTask.run(MapTask.java:350) ~[hadoop-mapreduce-client-core-3.

说明Hive的内存不够用了，需要在hive-env.sh 中设置：

重新启动服务，即可。

假如出现如下错误：

hive 的 log 日志的位置：

/tmp/root/hive.log

要么设置启用本地模式：

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

要么在hadoop的yarn-site.xml中，添加如下配置：

<property><name>yarn.scheduler.minimum-allocation-mb</name><value>2048</value><description>default value is 1024</description>
</property>

记得，将yarn-site.xml分发到其他两个服务器上。然后重启yarn即可。

四、加载数据

1）加载本地数据：

创建一个文件夹，将来把所有的数据，都放在这个文件下：

mkdir /home/hivedata

创建一个文件

添加如下数据：

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/databaseName.db/t_user

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

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

4）从其他表中加载数据

语法格式：

insert into table tableName2 select [.....] from tableName1;扩展内容：向多张表中插入数据的语法from tableName1insert 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/databaseName.db/t_user2';

五、通过hive进行流量统计

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

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

hive (databaseName)> load data local inpath '/home/hivedata/HTTP_20130313143750.dat' into table flow;
Loading data to table databaseName.flow
OK
Time taken: 0.741 seconds
hive (databaseName)> 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;