AmosCloud

Library

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Hive

Hive

  • 概述
    The Apache Hive ™ data warehouse software facilitates reading, writing, and managing large datasets residing in distributed storage using SQL. Structure can be projected onto data already in storage. A command line tool and JDBC driver are provided to connect users to Hive.

Apache Hive™数据仓库软件有助于使用SQL读取,写入和管理驻留在分布式存储中的大型数据集。可以将结构投影到已经存储的数据上。提供了命令行工具和JDBC驱动程序以将用户连接到Hive。

安装

  • 解压

  • 修改配置文件
    hive-env.sh

    export HADOOP_HOME=/opt/hadoop-2.10.1
    export HIVE_CONF_DIR=/opt/hive-1.2.2/conf
    export JAVA_HOME=/opt/jdk1.8
  • 配置环境变量

export HIVE_HOME=/opt/hive-1.2.2
export PATH=$HIVE_HOME/bin:$PATH
  • 启动
hive
  • 问题: 我们发现hive默认使用的derby数据库,

    1. 每次如果换不同的路径启动hive,就会产生多个数据库文件
    2. derby不允许多用户同时登陆
  • 解决办法
    安装Mysql数据库来替代hive自带的derby数据库

  • 安装Mysql
    http://www.amoscloud.com/?p=1562

  • 修改hive-site.xml让hive连接到mysql

<configuration>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://bd0403/metastore?createDatabaseIfNotExist=true</value>
    </property>
    <property>
<name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>123456</value>
    </property>
</configuration>
  • 创建数据库并且初始化
schematool -initSchema -dbType mysql

Hive的操作

Hive SQL

  • DDL
    -- 创建库
    create database db2;
    -- 查看库信息
    desc database db2;
    -- 删除库
    -- 使用cascade递归删除非空库
    drop database db2 [cascade];
--       外部表            判断存在性     db.table
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
--  列名    字段类型    字段描述
[(col_name data_type [COMMENT col_comment], ...)]
-- 表描述
[COMMENT table_comment]
-- 设置分区字段
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
-- 设置分桶
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
-- 行 格式化
[ROW FORMAT row_format]
 -- row_format
 -- 设置字段之间的分隔符
DELIMITED [FIELDS TERMINATED BY char]
 -- 集合中元素的分隔符
[COLLECTION ITEMS TERMINATED BY char]
 -- 设置Map的key和value之间的分隔符
[MAP KEYS TERMINATED BY char]
 -- 设置行分隔符 默认 '\n'
[LINES TERMINATED BY char]
 -- 设置数据的序列化和反序列化,通常使用这种方式时,数据都不直接存储在hdfs上,例如使用hive读取hbase
   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 -- 设置文件类型
[STORED AS file_format]
 -- 常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、ORCFILE(列式存储格式文件)
-- 如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
-- 设置表的存储位置
[LOCATION hdfs_path]
  • 行格式化设置字段分隔符

    create table log3(ip string,dt string,rcode string)
    row format delimited fields terminated by ' ';
  • 设置一个表使用制表符切分字段使用|切分集合元素

    create table tmp2(name string,hobby array<string>)
    row format DELIMITED FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY '|';
  • 设置一个表使用:作为map的kv分隔符,使用|作为集合元素分隔符

    create table tmp3(map1 map<string,string>)
    row format DELIMITED
    COLLECTION ITEMS TERMINATED BY '|'
    MAP KEYS TERMINATED BY ':';
  • 表的文件格式对比

    create database db3;
    -- 1.创建一个原始表用来存储原始文件 t1_o
    create table t1_o(line string);
    -- 2.将数据加载到 t1_o
    hdfs dfs -put '/root/logs/*' '/user/hive/warehouse/db3.db/t1_o'
    -- 3.创建一个textfile格式的表t1_text
    create table t1_text(line string);
    -- 4.从t1_o查询所有数据写入到t1_text中
    insert into t1_text select * from t1_o ;
    -- 5.创建一个orc格式的表t1_orc
    create table t1_orc(line string)
    stored as orc;
    -- 6.从t1_o查询所有数据写入到t1_orc中
    insert into t1_orc select * from t1_o ;
    -- 7.分别运行一个查询,比较速度
    -- 8.查看HDFS文件的大小
  • DML

    • 数据导入
      1. Load
        Load操作
        load data [local] inpath '/root/data/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];
        load data:表示加载数据
        local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表
        inpath:表示加载数据的路径
        into table:表示加载到哪张表
        student:表示具体的表
        overwrite:表示覆盖表中已有数据,否则表示追加
        partition:表示上传到指定分区
    1. insert

      通过查询语句向表中插入数据(Insert)
      创建一张分区表
      create table student(id string, name string) partitioned by (month string) row format delimited fields terminated by '\t';
      -- 基本插入数据
      insert into table  student partition(month='202005') values('01','amos');
      -- 基本模式插入(根据单张表查询结果)
      insert overwrite table student partition(month='202004')
           select id, name from student where month='202005';
      -- 多插入模式(根据多张表查询结果)
      from student
      insert overwrite table student partition(month='202001')
      select id, name where month='202005'
      insert overwrite table student partition(month='202002')
      select id, name where month='202005';
    2. as select

      -- hive中如果需要将一个查询结果保存在表中,不需要提前创建表
      -- 通过查询结果直接创建表
      create table 表名 as select
      -- 查询语句中创建表并加载数据(As Select)
      create table [if not exists] student3
      as select id, name from student;
    3. 使用HDFS的客户端导入数据

      -- 由于hive的表内容数据存储在HDFS上,所以我们可以直接将表内容文件上传到表所在的hdfs的location
      -- 先查看表的存储位置
      desc 表名;
      desc extended 表名;
      desc formatted 表名;
      -- 非分区表直接上传
      hdfs dfs -put 文件  表的hdfs路径
      -- 分区表直接上传后,有可能无法加载到数据
      -- 需要先进行修复
      msck repair table 表名;
      -- 创建一个分区表
      create table t1_par (line string)
      partitioned by (dt string);
      -- 1.使用load加载数据,尝试查询
      load data local inpath '/root/access.log-20201201' into table t1_par partition (dt='20201201');
      -- 2.手动创建hdfs目录,put数据,尝试查询
      hdfs dfs -mkdir /user/hive/warehouse/db2.db/t1_par/dt=20201202
      hdfs dfs -put /root/access.log-20201202 /user/hive/warehouse/db2.db/t1_par/dt=20201202
      -- 3.修复表的分区信息,再尝试查询
      msck repair table t1_par;
    • 数据导出
    1. Insert导出
      将查询的结果导出到本地
      insert overwrite local directory '/root/data/export/student'
         select * from student;
      将查询的结果格式化导出到本地  |
      insert overwrite local directory '/root/data/export/student1'
      ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '\n'
               select * from student;
      将查询的结果导出到HDFS上(没有local)
      insert overwrite directory '/user/root/hive/warehouse/student2'
           ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '\n'
           select * from student;
  • DQL

select (各种hive函数)
from (子查询)
where
group by
having
order by (sort by 、distrabuted by 、cluster by )
limit
  • HiveSQL的函数

    -- 查看函数列表
    show functions;
    -- 查看某个函数的说明
    show functions split;
  • 字符串和日期相关的函数 至少敲一遍
    https://www.cnblogs.com/yuejiaming/p/10572358.html

  • UserDefindFunction

    1. 创建maven工程 导入hive依赖
    2. 创建一个类继承org.apache.hadoop.hive.ql.exec.UDF
    3. 声明一个名为evaluate的方法,在方法中实现业务逻辑
    4. 将项目打成Jar包 上传到HDFS
    5. hive注册函数并使用
      -- 创建udf
      create [temporary] function 函数名 as "全类名" by jar "jar包路径";
      create temporary function my_dateformat as "com.xahj.hive.udf.MyDateFormat" using jar "hdfs://bd0401:9000/jars/hive-udf-1.0.jar";
      -- 删除udf
  • DCL

  • 网站日志分析案例

  1. 创建库

    create database db1;
  2. 创建表

    create table log(line string);
  3. 加载数据

    load data local inpath '/root/access.log-20200520' into table log;
  4. 写HQL实现需求

    1. 统计IP的数量

      select
      t1.ip ip,
      count(1) count
      from
      (select
      split(line," ")[0] ip
      from
      log) t1
      group by
      t1.ip;
    2. 统计每个ip的正常访问时 的 访问次数、上传流量、下载流量

      select
      t2.ip ip,
      count(1) count,
      sum(t2.up) up,
      sum(t2.down) down
      from
      (select
      t1.strs[0] ip,
      t1.strs[8] rcode,
      cast(t1.strs[9] as bigint) up,
      cast(t1.strs[size(t1.strs)-1] as bigint) down
      from
      (select split(line," ") strs from t1_orc) t1) t2
      where
      t2.rcode="200"
      group by
      t2.ip
      order by
      count desc
      limit 10;
insert into t2_orc 
select
 t2.ip ip,
 count(1) count,
 sum(t2.up) up,
 sum(t2.down) down
from
  (select
   t1.strs[0] ip,
   t1.strs[8] rcode,
   cast(t1.strs[9] as bigint) up,
   cast(t1.strs[size(t1.strs)-1] as bigint) down
  from
   (select split(line," ") strs from t1_orc) t1) t2
where
 t2.rcode="200"
group by
 t2.ip
order by
 count desc;
 -- 创建四个字段的orc表
create table t2_orc (ip string, count int, up bigint, down bigint) stored as orc;

Hive的核心概念


  • Hive默认有一个数据库叫default
    default默认使用hdfs的/user/hive/warehouse目录

创建的其他数据库所在的hdfs路径是/user/hive/warehouse/库名.db

  • 配置修改
-- hive命令行客户端中可以使用set查看全部的配置信息
set;
-- 可以使用set 参数名查看单个参数的值
hive (db1)> set hive.cli.print.current.db;
hive.cli.print.current.db=true
-- 可以使用set 参数名=参数值;  进行参数的临时修改,临时修改的参数只在当前这一次客户端内生效
-- 永久修改可以直接修改hive-site.xml
  • 外部表和内部表(管理表)
外部表 内部表
创建时 需要加上EXTERNAL 不加关键字
删除时 只删除mysql中的元数据 会同时删除元数据(mysql)和内容数据(HDFS)

生产环境多数情况同一份HDFS上的数据会有多人共同使用,这时外部表对于HDFS数据的管理更加安全,自己做一些临时的中间结果表时可以使用内部表。

  • 分区表

分区表就是将一个表以多个维度分成多级目录,避免每次都直接加载全表数据。

-- 创建使用日期分区的分区表
create table log_partitioned (line string)
partitioned by (dt string);
-- 加载数据到分区中
load data local inpath '/root/access.log-20201201' into table log_partitioned partition (dt='20201201');
load data local inpath '/root/access.log-20201202' into table log_partitioned partition (dt='20201202');
load data local inpath '/root/access.log-20201203' into table log_partitioned partition (dt='20201203');
-- 创建多级分区
create table log_partitioned2 (line string)
partitioned by (year string, month string ,day string);
load data local inpath '/root/access.log-20201203' into table log_partitioned2 partition (year='2020',month='12',day='03');
  • hive命令行中执行linux命令和HDFS命令

    # !开头 ;结尾  中间写linux命令操作本地文件系统
    hive>! clear;
    # dfs开头  ;结尾 操作hdfs
    hive>dfs -ls /;
  • hive linux命令行中的两种常用HQL提交方式

    # hive -e 'hql语句'
    hive -e 'select * from db2.t1_par limit 3'
    # hive -f hql脚本文件