hive用什么软件 用什么工具进行hive开发

适用场景

1.海量数据的存储处理

2.数据挖掘

3.海量数据的离线分析

3.1目前的Hive的Thrift服务端通常使用HiveServer2，它是HiveServer2改进版本，它提供了新的ThriftAPI来处理JDBC或者ODBC客户端，可以进行Kerberos身份验证，支持多个客户端并发。

3.2BeeLine

HiveServer2还提供了新的CLI：BeeLine，它是Hive 0.11引入的新的交互式CLI，基于SQLLine，可以作为Hive JDBC Client 端访问HievServer2。

通过BeeLine连接hive

hive安装目录/bin/./beeline -u jdbc:hive2://hiveServer2所在ip:端口号 -n 用户名

例如： ./beeline -u jdbc:hive2://127.0.0.1:10000 -n root

Hive数据库

类似传统数据库的DataBase，在元数据库里实际上是一张表，对应于HDFS上的数据仓库目录下是一个文件夹。数据仓库目录路径，由hive-site.xml中${hive.metastore.warehouse.dir}参数指定

创建数据库示例：create database 数据库名

元数据库中查询数据库列表select * from dbs; 如下图

内部表

内部表与关系数据库中的Table在概念上类似。每一个Table在Hive中都有一个相应的目录存储数据。所有的Table数据（不包括External Table）都保存在这个目录中。删除表时，元数据与数据都会被删除。
   元数据库中查询数据表列表: 
   ![在这里插入图片描述]()
   HDFS下对应存储目录：
   ![在这里插入图片描述]()

外部表

外部表指向已经在HDFS中存在的数据，可以创建Partition。它和内部表在元数据的组织上是相同的，而实际数据的存储则有较大的差异。内部表的创建过程和数据加载过程这两个过程可以分别独立完成，也可以在同一个语句中完成，在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除。而外部表只有一个过程，加载数据和创建表同时完成（CREATE EXTERNAL TABLE ……LOCATION），实际数据是存储在LOCATION后面指定的 HDFS 路径中，并不会移动到数据仓库目录中。当删除一个External Table时，仅删除该链接。

如何选择使用内部表或外部表？

如果所有处理都由hive来完成，则使用内部表
如果需要用hive和外部其他工具处理同一组数据集，则使用外部表。

分区，表分区位于表目录的下级目录

Partition对应于关系数据库中的Partition列的密集索引，但是Hive中Partition的组织方式和数据库中的很不相同。在Hive中，表中的一个Partition对应于表下的一个目录，所有的Partition的数据都存储在对应的目录中。例如pvs表中包含ds和city两个Partition，则

对应于ds = 20090801, city= jinan 的HDFS子目录为：/wh/pvs/ds=20090801/city=jinan ；

桶，

Buckets是将表的列通过Hash算法进一步分解成不同的文件存储。它对指定列计算hash，根据hash值切分数据，目的是为了并行，每一个Bucket对应一个文件。分区是粗粒度的划分，桶是细粒度的划分，这样做为了可以让查询发生在小范围的数据上以提高效率。适合进行表连接查询、适合用于采样分析。

例如将user列分散至32个bucket，首先对user列的值计算hash，则
对应hash值为0的HDFS目录为：/wh/pvs/ds=20090801/ctry=US/part-00000；
对应hash值为20的HDFS目录为：
/wh/pvs/ds=20090801/ctry=US/part-00020。
如果想应用很多的Map任务这样是不错的选择。

Hive的视图

视图与传统数据库的视图类似。视图是只读的，它基于的基本表，如果改变，数据增加不会影响视图的呈现；如果删除，会出现问题。如果不指定视图的列，会根据select语句后的生成。

视图的简单示例：

创建表：create view test_view as select * from test;

查看数据：select * from test_view;

CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。

EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），

有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。

表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。

可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。

默认的字段分隔符为ascii码的控制符\001(^A)

tab分隔符为 \t。只支持单个字符的分隔符。

如果文件数据是纯文本，可以使用 STORED AS

TEXTFILE。如果数据需要压缩，使用 STORED

AS SEQUENCE 。

Hive开发使用-Hive加载数据命令

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。如果表中存在分区，则必须指定分区名
加载本地数据，指定LOCAL关键字，即本地，可以同时给定分区信息 。
load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI，比如：file:///user/hive/project/data1.
例如：加载本地数据，同时给定分区信息：
hive> LOAD DATA LOCAL INPATH ‘file:///examples/files/kv2.txt’ OVERWRITE INTO TABLE invites PARTITION (ds=‘2008-08-15’);
加载DFS数据 ，同时给定分区信息：
如果 filepath 可以是相对路径 URI路径，对于相对路径，Hive 会使用在 hadoop 配置文件中定义的 fs.defaultFS 指定的Namenode 的 URI来自动拼接完整路径。
例如：加载数据到hdfs中，同时给定分区信息
hive> LOAD DATA INPATH ‘/user/myname/kv2.txt’ OVERWRITE INTO TABLE invites
PARTITION (ds=‘2008-08-15’);
OVERWRITE
指定 OVERWRITE ,目标表（或者分区）中的内容（如果有）会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

内部表

建表示例：

例如： 创建人员信息表person_inside，列以逗号","分隔。

create table person_inside (id string,name string,sex string,age int)

row format delimited fields terminated by ‘,’ stored as textfile;

加载数据： 本地数据位置： /tmp/person.txt

load data local inpath ‘file:///tmp/person.txt’ into table person_inside;

外部表
例如： 创建人员信息表person_ex，列以逗号","分隔。
外部表对应路径：hdfs://mycluster/hivedb/person.txt
建表示例：
create external table person_ext
(id string,name string,sex string,age int)
row format delimited fields terminated by ‘,’
stored as textfile
location ‘/hivedb’; (注意：location后面跟的是目录，不是文件，hive将依据默认配置的hdfs路径，自动将整个目录下的文件都加载到表中)
hive 默认数据仓库路径下，不会生成外部表的文件目录，
查看表信息： desc formatted person_ext; 查看location指向。
查询数据：select * from person_ext;
删除表：drop table person_ext;
只删除逻辑表，不删除数据文件，数据文件依然存在

分区表

例如： 创建人员信息表person_part，列以逗号","分隔。建立city为分区。
建表示例：
create table person_part
(id string,name string,sex string,age int)
partitioned by (city string)
row format delimited fields terminated by ‘,’
stored as textfile;
加载数据：本地数据位置： /tmp/person.txt
load data local inpath ‘file:///tmp/person.txt’ into table
person_part partition(city=‘jinan’);
数据存储在以分区 city='jinan’为目录的路径下
根据分区查询数据：hive 会自动判断where语句中是否包含分区的字段。而且可以使用大于小于等运算符
select * from person_part where city=‘jinan’;

分桶表

例如： 创建人员信息表person_bucket，列以逗号","分隔，在年龄age字段上建5个桶。
建表示例：
create table person_bucket
(id string,name string,sex string,age int) partitioned by (city string)
clustered by (age) sorted by(name) into 5 buckets
row format delimited fields terminated by ‘,’
stored as textfile;
打开桶参数： set hive.enforce.bucketing = true;
加载数据：insert into table person_bucket partition (city=‘jinan’) select * from person_inside;

数据加载到桶表时，会对字段取hash值，然后与桶的数量取模。把数据放到对应的文件中。

抽样查询：查询5个桶中的第2个桶，即000001_0 文件
select * from person_bucket tablesample(bucket 2 out of 5 on age);

分桶表：

注意：
要生成桶的数据，只能是由其他表通过insert into 或是insert overwrite导入数据，如果使用LOAD DATA 加载数据，则不能生成桶数据。
定义桶可以使用整型字段或是string类型字段。
若表没有定义桶也可以进行随机抽样，但是要全表扫描，速度慢。
必须先set hive.enforce.bucketing = true，才可以将数据正常写入桶中。

导出到本地文件系统
insert overwrite local directory ‘/tmp/exporttest/’ select * from person_inside;
注意：导出路径为文件夹路径，不必指定文件名。执行语句后，会在本地目录的/tmp/exporttest/下 生成一个000000_0结果集数据文件。
导出的数据列之间的分隔符默认是^A(ascii码是\001)。

导出到HDFS中
insert overwrite directory ‘/hivedb’ select * from person_inside;
注意：导出路径为文件夹路径，不必指定文件名。执行语句后，会在HDFS目录的/hivedb下 生成一个000000_0结果集数据文件。

导出到Hive的另一个表中
insert into table person_part partition (city=‘jinan’) select * from person_inside;

基于Partition的查询
例如：分区为 city
SELECT * FROM person_part WHERE city=‘jinan’;
限制条数查询 LIMIT
Limit可以限制查询的记录数。查询的结果是随机选择的。下面的查询语句从t1表中随机查询5条记录：
SELECT * FROM person_inside LIMIT 5;
Top N查询
下面的查询语句查询年龄最大的5个人。
set mapred.reduce.tasks= 2; 设置mapReduce任务数为2 个
Hive多表关联使用join…on语句
Hive只支持等值连接，即ON子句中使用等号连接，不支持非等值连接。
如果连接语句中有WHERE子句，会先执行JOIN子句，再执行WHERE子句。
可以 join 多个表。

创建employee表
创建表
create table employee(employee_id string,name string)
row format delimited fields terminated by ‘,’ stored as textfile;
加载数据： 本地数据位置： /tmp/employee.txt
load data local inpath ‘file:///tmp/employee.txt’ into table employee;
创建job表
创建表
create table job (job_id string,job string,employee_id string)
row format delimited fields terminated by ‘,’ stored as textfile;
加载数据： 本地数据位置： /tmp/job.txt
load data local inpath ‘file:///tmp/job.txt’ into table job ;

内连接

指的是把符合两边连接条件的数据查询出来。
查询语句
select * from employee join job on employee.employee_id=job.employee_id;

左外连接

如果 左边有数据，右边没有数据，则左边有数据的记录的对应列返回为空。
查询语句
select * from employee left outer join job on employee.employee_id=job.employee_id;
注意：不能使用left join，只能使用left outer join。

右外连接

如果 左边没有数据，右边有数据，则右边有数据的记录对应列返回为空。
查询语句
select * from employee right outer join job on employee.employee_id=job.employee_id;
注意：不能使用right join，只能使用right outer join。

全外连接

显示左外连接，右外连接的合集。
查询语句
select * from employee full outer join job on employee.employee_id=job.employee_id;

左半连接

左半连接与in操作或者exists操作，效果一样。
查询语句
select * from employee left semi join job on employee.employee_id=job.employee_id;
上面语句相当于如下语句:
select * from employee where employee_id in (select employee_id from job);

hive 0.9.0版本开始支持 in、not in 、like、not like in

in

左边的表在右边表的范围内。与left semi join 效果一样。
select * from employee where employee_id in (select employee_id from job);

not in

左边的表不在右边表的范围内。
select * from employee where employee_id not in (select employee_id from job);

like

查询左右模糊匹配的所有结果。

select * from employee where name like ‘张%’;

not like

查询左右模糊匹配以外的所有结果。

select * from employee where name not like ‘张%’;

查询数据库：show databases;

模糊搜索表：show tables like ‘name’;

删除数据库：drop database dbname;

删除数据表：drop table tablename;

查看表结构信息：desc table_name;

查看详细表结构信息： desc formatted table_name;

查看分区信息： show partitions table_name;

查看hdfs文件列表信息：hadoop fs -ls /user/hive/warehouse/

查看hdfs文件内容：hadoop fs -cat /user/hive/warehouse/file.txt

三个文件，用户文件users.dat,电影文件movies.dat评论文件ratings.dat

百万级电影评论数据分析代码

//创建movie表。电影信息表
 create table movie
 (movie_id int,movie_name string,movie_leixing string)
 row format delimited fields terminated by ‘^’
 stored as textfile
 //从本地填充数据进入内部表
 load data local inpath ‘/home/cloudera/Desktop/movie.dat’ into table movie如法炮制其他两张表
 //创建user表，用户信息表
 create table user
 (user_id int,user_sex string,user_age int,user_zhiye string,user_youbian string)
 row format delimited fields terminated by ‘^’
 stored as textfile
 //从本地填充数据进入内部表
 load data local inpath ‘/home/cloudera/Desktop/user.dat’ into table user//创建rating表，评论信息表
 create table rating
 (user_id int,movie_id int,rating_pingfen int,rating_shijian string)
 row format delimited fields terminated by ‘^’
 stored as textfile;
 //从本地填充数据进入内部表
 load data local inpath ‘/home/cloudera/Desktop.rating.dat’ into table rating//利用内连接进行数据合并
 第一步合并评论数据表和用户数据表
 即合并rating表和user表
 1.建表填充数据法，首先创建合并表的表即rating_user表
 create table rating_user
 (user_id int,movie_id int,rating_pingfen string,rating_shijian string,user_sex string,user_age int,user_zhiye string,user_youbian string)
 row format delimited fields terminated by ‘^’
 stored as textfile;
 //将查询结果插入到此表中
 insert into table rating_user select rating.user_id,movie_id,rating_pingfen,rating_shijian,user_sex,user_age,user_zhiye,user_youbian
 from rating join user on rating.user_id=user.user_id
 2.查询建表法，
 create table rating_user
 row format serde ‘org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe’
 stored as rcfile
 as
 select rating.user_id,movie_id,rating_pingfen,rating_shijian,user_sex,user_age,user_zhiye,user_youbian
 from rating join user on rating.user_id=user.user_id3.不建表法，通过将第一次join的结果作为第二次join的条件，一次查出所有数据的合集
 select rating_user.user_id,rating_user.movie_id,rating_pingfen,rating_shijian,user_sex,user_age,user_zhiye,user_youbian,movie_name,movie_leixing from
 (select rating.user_id,movie_id,rating_pingfen,rating_shijian,user_sex,user_age,user_zhiye,user_youbian from rating join user on rating.user_id=user.user_id)
 rating_user join movie on rating_user.movie_id=movie.movie_id2.一步到位查询建表法，
 create table rating_user_movie
 row format serde ‘org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe’
 stored as rcfile
 as
 select rating_user.user_id,rating_user.movie_id,rating_pingfen,rating_shijian,user_sex,user_age,user_zhiye,user_youbian,movie_name,movie_leixing from
 (select rating.user_id,movie_id,rating_pingfen,rating_shijian,user_sex,user_age,user_zhiye,user_youbian from rating join user on rating.user_id=user.user_id)
 rating_user join movie on rating_user.movie_id=movie.movie_id//查询合并后的表的数据条数
 select count(*) from rating_user_movie;//对数据进行合并查询
select count(movie_id) from rating_user_movie
 //通过电影id进行分组，将统计出的数据条数作为新的列，对组内数据进行合并，最后对所有数据进行排序，倒序排序，取出前20条
 select movie_id,count(movie_id) as m1
 from rating_user_movie
 group by movie_id
 order by m1 desc
 limit 20;
 2.//通过电影id和电影name进行分组，将统计出的数据条数作为新的列，对组内数据进行合并，最后对所有数据进行排序，倒序排序，取出前20条
 select movie_name,count(movie_id) as m1
 from rating_user_movie
 group by movie_id,movie_name
 order by m1 desc
 having m1 >= 20
 limit 20;
 3.//通过电影id和电影name进行分组，将统计出的数据平均值作为新的列，对组内数据进行合并，
 计数，做条件判断，取平均值，最后对数据进行排序
 select movie_name,count(movie_id) as m1,avg(rating_pingfen) as m2
 from rating_user_movie
 group by movie_id,movie_name
 having m1 >= 100
 order by m2 desc
 limit 10;//查看合并数据条数是否正确
 select count(*) from rating_user_movie;
 select count(1) from rating_user_movie group by movie_id;
 4.//通过电影id和性别进行分组，经统计出的数据平均值作为新的列，对组内数据做合并，
 //通过电影id，电影名字，电影类型，用户性别，进行分组，统计出
 select movie_id,movie_name,movie_leixing,user_sex,avg(rating_pingfen) as f
 from rating_user_movie where user_sex like ‘F’
 group by movie_id,movie_name,movie_leixing,user_sex
 limit 5;//通过电影的id，用户的性别来对电影表做数据平均
 select movie_id,movie_name,movie_leixing,user_sex,avg(rating_pingfen) as f
 from rating_user_movie
 group by movie_id,movie_name,movie_leixing,user_sex
 limit 5;
 4.平均值过后对数据进行处理时，先根据性别进行分割在join连接到一起。
 select m1.movie_id,m1.movie_name,m1.movie_leixing,f,m from
 (select movie_id,movie_name,movie_leixing,user_sex,avg(rating_pingfen) as f
 from rating_user_movie where user_sex like ‘F’
 group by movie_id,movie_name,movie_leixing,user_sex) f1 join
 (select movie_id,movie_name,movie_leixing,user_sex,avg(rating_pingfen) as m
 from rating_user_movie where user_sex like ‘M’
 group by movie_id,movie_name,movie_leixing,user_sex) m1 on f1.movie_id=m1.movie_id 
select movie_id,movie_name,count(movie_id) as m1
 from rating_user_movie
 group by movie_id,movie_name
 order by m1 desc
 limit 50;select movie_id,user_age,avg(rating_pingfen) as f1
 from rating_user_movie
 group by movie_id,movie_name,user_age5.1//通过两次筛选，对第一次筛选出的评论数量前50条电影数据做处理后，
 连接第二次筛选出的每部电影每个年龄段的平均分，最终七个年龄段连接出350条数据
 并将其生成data_age表
 create table data_age
 row format serde ‘org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe’
 stored as rcfile
 as
 select t1.movie_name,t2.user_age,pingjunfen from
 (select movie_id,movie_name,count(movie_id) as m1
 from rating_user_movie
 group by movie_id,movie_name
 order by m1 desc
 limit 50) t1 join
 (select movie_id,user_age,avg(rating_pingfen) as pingjunfen
 from rating_user_movie
 group by movie_id,movie_name,user_age) t2 on
 t1.movie_id=t2.movie_id5.2//行转列
 select movie_name, case 0<user_age and user_age<10 when “(1-9]” then pingjunfen else 0 end as (1-9],
 case user_age when 9<user_age and user_age<19 then pingjunfen else 0 end as (1-9],
 case user_age when 19<user_age and user_age<29 then pingjunfen else 0 end as (1-9],
 case user_age when 29<user_age and user_age<39 then pingjunfen else 0 end as (1-9],
 case user_age when 39<user_age and user_age<49 then pingjunfen else 0 end as (1-9],
 case user_age when 49<user_age and user_age<59 then pingjunfen else 0 end as (1-9],
 from data_age;select movie_name,
 case user_age when 1 then pingjunfen else 0 end as 1,
 case user_age when 18 then pingjunfen else 0 end as 18,
 case user_age when 25 then pingjunfen else 0 end as 25,
 case user_age when 35 then pingjunfen else 0 end as 35,
 case user_age when 45 then pingjunfen else 0 end as 45,
 case user_age when 50 then pingjunfen else 0 end as 50,
 case user_age when 56 then pingjunfen else 0 end as 56,
 from data_age;