Hive环境搭建及表创建

绝地灬酷狼 2023-02-21 12:28 4阅读 0赞

1、下载hive压缩包解压到指定的目录：

![20200630171857675.png][]

2、修改配置

进入hive配置文件夹

![20200630171952763.png][]

添加配置文件

mv hive-env.sh.template hive-env.sh
    
     vi hive-env.sh

配置 HADOOP\_HOME 路径

export HADOOP_HOME=/home/master/hadoop-2.9.2
    
    export HIVE_CONF_DIR=/home/master/hive-3.1.2/conf

（3）将配置好的hive分发到指定的服务器主机

scp -r  ./hive-3.1.2   root@slave2:/home/master/

（4）启动hive

a、本地脚本启动交互

bin/hive

b、使用hive的jdbc

c、使用hiveserver2启动为一个服务

hiveserver2

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70][]

另开启一个session窗口， 使用beeline远程连接hive数据仓库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 1][]

!connect  jdbc:hive2://master:10000

![20200822115932745.png][]

(5) 建表

文件love.txt内容如下：

1;liuping;henanxinxian
    2;liucui;henanlankao
    3;gejiali;henanyuzhou
    4;chenyingying;henanxinxian

上传指定目录/root

![20200806231354708.png][]

a、使用分隔符映射创建t\_user，此例使用；作为分隔

create  table  t_user(id int,name string,address string)  row format delimited  fields terminated by ';';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 2][]

![20200806230442671.png][]

b、将love.txt文件得内同容映射到t\_user表

![20200806231256635.png][]

查询表数据：

![20200806231458729.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 3][]

（6）分区创建表

有原文件内容如下

henan.txt
    1,郑州
    2,洛阳
    
    jiangsu.txt
    3,南京
    4,苏州
    5,无锡
    
    guangdong.txt
    6,广州
    7,深圳
    
    zhengjiang.txt
    8,杭州
    9,宁波

将以上的城市按照省份分区建表，建表语句：

create  table  t_city(id int,city string) partitioned by (province string) row format delimited  fields terminated by ',';

![20200816152423667.png][]

数据装载映射：

LOAD  DATA  local  INPATH  '/home/master/plugin/henan.txt'  INTO  TABLE   t_city partition(province='henan');
    
    
    LOAD  DATA  local  INPATH  '/home/master/plugin/jiangsu.txt'  INTO  TABLE   t_city partition(province='jiangsu');
    
    
    LOAD  DATA  local  INPATH  '/home/master/plugin/guangdong.txt'  INTO  TABLE   t_city partition(province='gaungdong');
    
    
    LOAD  DATA  local  INPATH  '/home/master/plugin/zhejiang.txt'  INTO  TABLE   t_city partition(province='zhejiang');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 4][]

（7）双分区创建表

例如： 按照省份和城市对县区数据进行分区:

zhengzhou.txt
    1,金水区
    2,管城区
    3,二七区
    
    luoyang.txt
    3,西公区
    4,洛龙区
    
    nanjing.txt
    1,江宁区
    2,雨花台

执行创建表语句，以省、市作为分区条件

create  table  t_county(id int,county string) partitioned by (province string,city string) row format delimited  fields terminated by ',';

装载数据

LOAD  DATA  local  INPATH  '/home/master/plugin/city/zhengzhou.txt'  INTO  TABLE   t_county partition(province='henan',city='zhengzhou');
    
    LOAD  DATA  local  INPATH  '/home/master/plugin/city/luoyang.txt'  INTO  TABLE   t_county partition(province='henan',city='luoyang');
    
    LOAD  DATA  local  INPATH  '/home/master/plugin/city/nanjing.txt'  INTO  TABLE   t_county partition(province='jiangsu',city='nanjing');

查询数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 5][]

(8)分桶创建表

设置开启分桶功能，开启分桶数据

#开启分桶
    set  hive.enforce.bucketing=true;
    #分成4个桶
    set  mapreduce.job.reduces=2;

数据源：

1,郑州,90
    2,洛阳,80
    3,开封,85
    4,信阳,89
    5,南阳,88
    6,焦作,86
    7,新乡,87
    8,安阳,84
    9,鹤壁,81
    10,商丘,96
    11,平顶山,92
    12,许昌,79
    13,周口,78
    14,驻马店,77
    15,三门峡,76
    16,漯河,75
    17,濮阳,74

先创建临时表t\_temp\_cities,将数据源装载到表中

create table  t\_temp\_cities(id int,city string,sorce int)  row format delimited  fields terminated by ',';  
       LOAD  DATA  local  INPATH  '/home/master/plugin/city/cities.txt'  INTO  TABLE   t\_temp\_cities；

分桶创建语句

create table  t_cities(id int,city string,sorce int) clustered by (id) into  4  buckets  row format delimited  fields terminated by ',';

分桶装载数据,使用桶表将临时表数据插入到t\_cites中

insert  overwrite  table  t_cities   select  * from  t_temp_cities cluster by(id);

(9)复杂数据建表映射

数据源

1,河南,郑州;洛阳;开封  
  2,江苏,南京;苏州;无锡

以逗号分隔字段，以分号分割集合

建表语句

create  table  t_compe(id int,pro string,list  array<string>)  row format delimited  fields terminated by ',' collection items terminated by ';';

装载数据  
hadoop  fs  -put  /home/master/plugin/city/complex.txt  /user/hive/warehouse/t\_compe;

查询数据

![20200823141331472.png][]

[20200630171857675.png]: https://img-blog.csdnimg.cn/20200630171857675.png
[20200630171952763.png]: https://img-blog.csdnimg.cn/20200630171952763.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200822114005185.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200822115833743.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg==,size_16,color_FFFFFF,t_70
[20200822115932745.png]: https://img-blog.csdnimg.cn/20200822115932745.png
[20200806231354708.png]: https://img-blog.csdnimg.cn/20200806231354708.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200806230408548.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg==,size_16,color_FFFFFF,t_70
[20200806230442671.png]: https://img-blog.csdnimg.cn/20200806230442671.png
[20200806231256635.png]: https://img-blog.csdnimg.cn/20200806231256635.png
[20200806231458729.png]: https://img-blog.csdnimg.cn/20200806231458729.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2020080623142453.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg==,size_16,color_FFFFFF,t_70
[20200816152423667.png]: https://img-blog.csdnimg.cn/20200816152423667.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200822221443688.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200822230443153.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg==,size_16,color_FFFFFF,t_70
[20200823141331472.png]: https://img-blog.csdnimg.cn/20200823141331472.png