Hive（一）表操作

一、Hive 安装

是基于Hadoop的一个数据仓库基础工具
前往apache下载Hive

# 解压文件
tar zxvf apache-hive-0.14.0-bin.tar.gz

# 将解压文件复制到/usr/local/hive目录下
mv apache-hive-0.14.0-bin /usr/local/hive

设置hive环境

# 在〜/.bashrc文件中添加
export HIVE_HOME=/usr/local/hive
export PATH=$PATH:$HIVE_HOME/bin
export CLASSPATH=$CLASSPATH:/usr/local/Hadoop/lib/*:.
export CLASSPATH=$CLASSPATH:/usr/local/hive/lib/*:.

更新环境变量，立即生效

source ~/.bashrc

配置hive

# hive-env.sh 添加以下内容
export HADOOP_HOME=/usr/local/hadoop

二、Hive数据库

1.创建数据库

创建数据库

# 创建数据库
create database userdb;  # 直接创建
create database [if not exists] userdb;  # 查找创建，如果不存在创建

创建数据库并指定位置

# 创建数据库并指定位置
create database myhive2 location '/myhive2';

显示所有数据库

# 显示所有数据库
show databases;

查看数据库基本信息

desc database myhive2;

查看数据库更多详细信息

desc database extended myhive2;

修改数据库
使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括
数据库的名称以及数据库所在的位置

alter database myhive2 set dbproperties('createtime'='20180611');

删除数据库

# 删除数据库（数据库没有数据时也就是空数据库）
drop database if exists userdb;

# 强制删除数据库，包含数据库下面的表一起删除
drop database userdb cascade;

# schema 查询删除数据库
drop schema userdb;

python连接Hive数据库

from pyhive import hive
from TCLIService.ttypes import TOperationState

# 打开hive连接
hiveConn = hive.connect(host='192.168.0.1',port=11111)
cursor = hiveConn.cursor()

# 执行sql语句
sql = ''' sql '''
cursor.execute(sql, async=True)

# 得到执行语句的状态
status = cursor.poll().operationState
print("status:",status)

print(cursor.fetchone())
print(cursor.fetchall())

# 关闭hive连接
cursor.close()
hiveConn.close()

三、Hive数据表

1. create table创建表

# 创建表语法
create [temmporary] [external]  table [if not exists] [db_name] table_name 
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

# 创建表并指定字段之间的分隔符
create table if not exists db_name (eid int, name String,salary String...)
comment ‘Employee details’
row format delimited
fields terminated by  ‘\t’
lines terminated by ‘\n’
stored as textfile;

根据已经存在的表结构创建表

# 根据已经存在的表结构创建表
create table stu4 like table01;

查询表的类型

# 查询表的类型
desc formatted table01;

管理表的操作，如：

use myhive; # 选择数据库
create table stu(id int,name string);  # 创建数据表
insert into stu values (1,"zhangsan");  # 插入数据
select * from stu;  # 查看所以数据

外部表的操作
外部表：外部表指定其他的hdfs路径的数据加载到表中，所以hive表认为非自己的数据，删除hive
表时，数据仍然存在hdfs当中，不会删掉。
1.创建表

create table if not exists student (eid int, name String,salary String...)
comment ‘Employee details’
row format delimited
fields terminated by  ‘\t’
lines terminated by ‘\n’
stored as textfile;

2.要加载数据hdfs上需先有数据

cd /export/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put student.csv /hivedatas/  # 上传

3、加载数据

load data local inpath '/export/servers/hivedatas/student.csv' into table student;

加载数据并覆盖已有数据

# 如有数据，覆盖已有数据
load data local inpath '/export/servers/hivedatas/student.csv' overwrite into table student;

2. alter table修改表

2.1 重命名

alter table old_table_name rename to new_table_name;

如：把表 tb01 修改成 tb02

alter table tb01 rename to tb02;

2.2 增加/修改列信息

查询表结构

desc score5;

添加列

alter table tb01 add columns (mycol string, mysco string);

更新列

alter table tb01 change column mysco mysconew int;

删除表

# 删除表tb01
drop table tb01;

分区表

把大的文件切割划分成多个小的文件，这样每次操作一个小的文件会很容易

1.建立分区表
单分区表：其中分区字段是partdate，注意分区字段不能和表字段一样，否则会报重复的错

create table test_t2(words string,frequency string)
partitioned by (partdate string)
row format delimited fields terminated by '\1';

多分区表：id在前，address在后，注意多个分区字段时，是有先后顺序的

create table test_table_hive(name string,school string) 
partitioned by (id int,address string) 
row format delimited 
fields terminated by '\1';

2. 新建分区：建立分区表之后，此时没有数据，也没有分区，需要建立分区

查看分区命令show partitions：

show partitions test_table_hive;

建立单分区

# 方式1
alter table test_t2 add IF NOT EXISTS 
partition (partdate='20191031'); #避免重复建立分区出错
# 方式2
alter table test_t2 add IF NOT EXISTS 
partition (partdate='20191031') 
partition (partdate='20191101'); #一次建立多个分区

建立多字段分区：

# 创建多字段分区
alter table test_table_hive add partition(id=1,adress='chengdu');
# 显示分区
show partitions test_table_hive;

建立多个多字段分区，注意多字段时，必须填满这多个分区字段

alter table test_table_hive 
add partition(id=2,adress='beijing') 
partition(id=3,adress='shanghai');

show partitions test_table_hive;

删除分区 drop

alter table test_t2 drop partition(partdate='20191030');

加载数据到分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

加载数据到多分区表中

load data local inpath '/export/servers/hivedatas/score.csv' 
into table score2 partition(year='2018',month='06',day='01');

多分区表联合查询(使用 union all )

# 使用 union all 不会去除重复值
select * from score where month = '201806' union all select * from score where month = '201806';

分桶表

将数据按照指定的字段进行分成多个桶中，将数据按照字段进行划分，也就是将数据按照字段划分到多个文
件当中去。

1、开启 Hive 的分桶功能

set hive.enforce.bucketing=true;

2、设置 Reduce 个数

set mapreduce.job.reduces=3;

3、创建桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets 
row format delimited fields terminated by '\t';

4、创建普通表，用于数据加载

# 创建普通表
create table course_common (c_id string,c_name string,t_id string) 
row format delimited fields terminated by '\t';

5、向普通表中加载数据

load data local inpath '/export/servers/hivedatas/course.csv' into table course_common;

6、通过insert overwrite从普通表 中向 桶表加载数据

insert overwrite table course select * from course_common cluster by(c_id);