Hive实践

发表于 | 更新于 | 分类于 | 评论数： | 阅读次数：

What

Hive起源于Facebook，它使得针对Hadoop进行SQL查询成为可能，从而非程序员也可以方便地使用。 hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。 <!-- more -->

  • hive元数据信息存在关系型数据库上
    • 表 的名字， 权限，字段，创建者，数据目录
  • hive接口
    • cli
    • client
    • wui webui

3中运行模式

  • 内嵌模式
  • 本地模式

    mysql和hive在一台服务器上

  • 远程模式
    • 远程一体

      mysql和hive不在一台服务器上,hive 的server和client在一台机器上

    • 远程分开

      mysql和hive不在一台服务器上,hive 的server和client也在不同的机器上

执行mapreduce 时机

  • select * from t 单表不执行
  • select * from t where t>1 单表字段不执行
  • select count(1) from t 包含函数执行mapreduce任务

Why ?

数据库与数据仓库

非Java编程者对HDFS的数据做mapreduce操作。 使用SQL来快速实现简单的MapReduce 统计，不必开发专门 的MapReduce 应用，学习成本低，十分适合数据仓库的统计 分析。

Where

http://hive.apache.org

How 安装？

  1. 安装mysql 参考博客中的另一篇2017-12-26-centos安装mysql

不支持mysql5.1

  1. tar zxvf apache-hive-1.2.1-bin.tar.gz

  2. 集群上所有服务器都删 

    1
    rm -rf /opt/soft/hadoop-2.5.1/share/hadoop/yarn/lib/jline-0.9.94.jar

  3. 从hive里拷贝jar到hadoop里 

    1
    cp /opt/soft/apache-hive-1.2.1-bin/lib/jline-2.12.jar /opt/soft/hadoop-2.5.1/share/hadoop/yarn/lib/

  4. 拷贝到集群上所有机器 

    1
    scp -r  /opt/soft/apache-hive-1.2.1-bin/lib/jline-2.12.jar sj-node2:/opt/soft/hadoop-2.5.1/share/hadoop/yarn/lib/

  5. 上传mysql驱动包 mysql-connector-java-5.1.32-bin.jar 到目录/opt/soft/apache-hive-1.2.1-bin/lib

  6. 修改配置文件 vi /opt/soft/apache-hive-1.2.1-bin/conf/hive-site.xml输入以下内容

    • 本地模式 

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      <?xml version="1.0"?>
      <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
      <configuration>
        <property>
            <name>hive.metastore.warehouse.dir</name>
            <value>/user/hive_local/warehouse</value>
        </property>
        <property>
            <name>hive.metastore.local</name>
            <value>true</value>
        </property>
        <property>
            <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://sj-node1/hive_remote?createDatabaseIfNotExist=t
      rue</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>

    • 远程一体模式 将上述里的这个属性改下 

      1
      2
      3
      4
      <property>
          <name>hive.metastore.local</name>
          <value>false</value>
      </property>

- 远程分开模式
  - client 端 在sj-node1上
    
1
2
3
4
5
6
7
8
<property>
    <name>hive.metastore.local</name>
    <value>false</value>
</property>
<property>
    <name>hive.metastore.uris</name>
    <value>thrift://sj-node2:9083</value>
</property>


  - server端 sj-node2 去掉`hive.metastore.local` 这个属性
    - 服务端启动方式 
      `hive --service metastore`
  1. 环境变量

    • vi ~/.bash_profile 加入如下内容 

      1
      2
      export HADOOP_HOME=/opt/soft/hadoop-2.5.1
      export HIVE_HOME=/opt/soft/apache-hive-1.2.1-bin

    • source ~/.bash_profile

提供jdbc服务供链接

hiverserver2

启动服务： $HIVE_HOME/bin/hiverserver2 hive --service metastore

1
2
3
4
5
6
7
8
9
 public static void main(String[] args) {
  try{
    Class.forName("org.apache.hive.jdbc.HiveDriver");
    Connection conn = DriverManager.getConnection("jdbc:hive2://node5:10000/default")
    Statement st = conn.createStatement();
    ResultSet res = st.executeQuery("select count(*) from t_test");

  }
}

HQL简介

数据类型

  • primitive_type 原始数据类型
    • TINYINT
    • SMALLINT
    • INT
    • BIGINT
    • BOOLEAN
    • FLOAT
    • DOUBLE
    • DOUBLE PRECISION
    • STRING 搞定一切
    • BINARY
    • TIMESTAMP
    • DECIMAL(precision, scale)
    • DATE
    • VARCHAR
    • CHAR
  • array_type 数组数据类型
    • ARRAY < data_type >
  • map_type
    • MAP < primitive_type, data_type >
  • struct_type
    • STRUCT < col_name : data_type [COMMENT col_comment], ...>
  • union_type
    • UNIONTYPE < data_type, data_type, ... >

DDL

  • create database testdata;

  • drop database testdata;

  • use testdata;

  • 删除表 DROP TABLE [IF EXISTS] table_name [PURGE];

  • 重命名表 ALTER TABLE table_name RENAME TO new_table_name;

  • 创建表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    create table t_p3(
        id int ,
        name string,
        age int,
        likes array<string> comment 'your like things',
        address map<string,string> comment 'your address'
    )
    row format delimited fields terminated by ',' 
    collection items terminated by '-'
    map keys terminated by ':'
    lines terminated by '\n';

DML

5种导入方式,字段数相同，类型需匹配

  • LOAD
    • local load data local inpath '/root/data.txt' overwrite into table t_person;
    • hdfs
      • hdfs dfs -put data.txt /test
      • load data inpath '/test/data.txt' overwrite into table t_person;
  • INSERT INTO
  • From person t1 Insert Owerwrite Table person1 select id,name,age inert owerwrite table per2 select id,name,age;
  • 更新数据 UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
  • 删除数据 DELETE FROM tablename [WHERE expression]
#### 文本数据 data.txt -> t_person

1
2
3
1,zshang,18,game-girl-book,stu_addr:beijing-work_addr:shanghai
2,lishi,16,shop-boy-book,stu_addr:hunan-work_addr:shanghai
3,wang2mazi,20,fangniu-eat,stu_addr:shanghai-work_addr:tianjing


data2.txt -> t_p2

1
2
3
1,zshang,18
2,lishi,16
3,wang2mazi,20


#### 执行sql查询

1
2
3
from t_person t1,t_p2
insert overwrite table t_p3
select t1.id,t_p2.name,t_p2.age,t1.likes,t1.address where t1.id = t_p2.id;
保存结果

  1. 保存数据到本地 

    1
    hive> insert overwrite local directory '/root/hive2local.txt' row format delimited fields terminated by ',' select * from testhive.t_person;

    teshive为数据名

  2. 保存数据到 HDFS 上: 

    1
    insert overwrite directory '/test/testback' select * from testhive.t_p3;

  3. 在 shell 中将数据重定向到文件中 

    1
    hive -e "select * from testhive.t_person;" > result.txt

备份数据或还原数据

  1. 备份数据: 

    1
    EXPORT TABLE testhive.t_person TO '/test/testexport';

  2. 删除再还原数据 

    1
    2
    3
    drop table testhive.t_person;
    show tables from testhive;
    IMPORT table testhive.t_person FROM '/test/testexport' ; ;

外部表(重要)

外部关键字 EXTERNAL 允许您创建一个表,并提供一个位 置,以便 hive 不使用这个表的默认位置。这方便如果你已经生成的数 据。当删除一个外部表,表中的数据不是从文件系统中删除。外部表指向任何 HDFS 的存储位置,而不是存储在配置属性指定的文件夹 hive.metastore.warehouse.dir.中

HIVEserDe序列化

HiveSerDe-SerializerandDeserializer SerDe用于做序列化和反序列 化。构建在数据存储和执行引擎之间，对两者实现解耦。 

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE logtbl (
    host STRING,
    identity STRING,
    t_user STRING,
    time STRING,
    request STRING,
    referer STRING,
    agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
)
STORED AS TEXTFILE;

  • 原始数据 access_log.txt 

    1
    2
    3
    4
    5
    6
    192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-upper.png HTTP/1.1" 304 -
    192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-nav.png HTTP/1.1" 304 -
    192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /asf-logo.png HTTP/1.1" 304 -
    192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-button.png HTTP/1.1" 304 -
    192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-middle.png HTTP/1.1" 304 -
    192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217

  • 导入 

    1
    load data local inpath '/root/access_log.txt' into table  testhive.logtbl;

beeline和hiveserver2

  • server 端启动 hiveserver2
  • client端启动 beeline
    • 输入!connect jdbc:hive2://sj-node2:10000 root 123456连接

hive的jdbc

启动hiveserver2

hive的分区

必须要在表定义的时候创建partition

  • 分区字段内容 最好不要特殊字符，否则在hdfs上操作时转义过的就不能查看了

  • 分区里的分区字段不能与表的字段同名

  • 分区删除的时候最好指定一级二级分区名，否则删除不相关分区的子分区

  • 分区重命名，多级分区的重命名，指明父子结构分区名，相当于移动到新的目录树下

分区分为静态分区和动态分区

hive的自定义函数

  • UDF User-Defined-Function
    • 单输入单输出
  • UDAF(User- Defined Aggregation Funcation)
    • 多输入单输出
  • UDTF(User-Defined Table-Generating Functions)
    • 一进多输出

分桶

分桶表是对列值取哈希值的方式，将不同的数据放到不同的文件中

  • 桶的个数相当于reduce的个数
  • 抽样

调优

  • 去格式化无用的-
  • hive内数据迁移 先建表结构，然后用文件直接传送 ， 提高效率
  • 把hive sql 当做MapReduce程序去优化
  • explain
  • 小文件一般128m内 可以设为本地模式跑，速度会快点
  • 严格模式 nostrict
    • 限制查询
      • 对分区表查询是，必须添加where条件对分区字段查询，否则不能查询
      • order by 语句必须包含limit限制
  • hive排序
    • order by
    • sort by
    • distribute by
    • cluster by
  • hive join
    • join 时将小表（驱动表）放在join左边
    • 在map端join
      1. sql方式 select /* +mapjoin(smalltable) */ smalltable.key bigtable.value from smalltable join bigtable on smalltable.key=bigtable.key
      2. 开启自动mapjoin set hive.auto.convert.join=true

参考

疑问

  1. select * from tbl 大表会不会死机 不可以这么操作
  2. 原始数据包含分区字段的时候如何建表导入 动态分区导入
  3. 备注字段？ -- 可以在sql文件中注释
Juforg wechat
欢迎您扫一扫上面的微信公众号，订阅我的博客！