HIVE 介绍

（1）hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

（2）Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。

要理解hive，必须先理解hadoop和mapreduce，如果有不熟悉的童鞋，可以百度一下。

使用hive的命令行接口，感觉很像操作关系数据库，但是hive和关系数据库还是有很大的不同，下面我就比较下hive与关系数据库的区别，具体如下：

1. hive和关系数据库存储文件的系统不同，hive使用的是hadoop的HDFS（hadoop的分布式文件系统），关系数据库则是服务器本地的文件系统；
2. hive使用的计算模型是mapreduce，而关系数据库则是自己设计的计算模型；
3. 关系数据库都是为实时查询的业务进行设计的，而hive则是为海量数据做数据挖掘设计的，实时性很差；实时性的区别导致hive的应用场景和关系数据库有很大的不同；
4. Hive很容易扩展自己的存储能力和计算能力，这个是继承hadoop的，而关系数据库在这个方面要比数据库差很多。

以上都是从宏观的角度比较hive和关系数据库的区别，下面介绍一下在实际工作中遇到的一些常用语句和方法。

HIVE 基础

hive 常用命令

假设有数据库 fm_data，里面有表格 shield_fm_feature_item_ctr

show databases; //列出数据库

desc database fm_data; // 展示数据库 fm_data 的信息

use fm_data; // 使用某个数据库 fm_data\

set hive.cli.print.current.db=true; 显示列头
set hive.cli.print.current.db=false; 关闭列头

show tables; // 展示这个数据库里面的所有表格

show tables in fm_data; // 展示数据库 fm_data 里面的所有表格

show tables like ‘*ctr*’; // 模糊查找

show create table shield_fm_feature_item_ctr; // 获得表格 shield_fm_feature_item_ctr 的建表语句，其中包括表格的字段，HDFS 的 location 等信息

desc shield_fm_feature_item_ctr; // 展示表格 shield_fm_feature_item_ctr 的字段以及字段类型

desc formatted shield_fm_feature_item_ctr; // 详细描述表格 shield_fm_feature_item_ctr，包括表格的结构，所在的 database，owner，location，表格的类型 (Managed Table or External Table)，存储信息等

内部表与外部表

hive 的表格分两种，一种是 managed tables，另一种是 external tables。hive 创建表格时，默认创建的是 managed table，这种表会把数据移动到自己的数据仓库目录下；另外一种是 external tables，它关联的数据不是 hive 维护的，也不在 hive 的数据仓库内。

创建内部表格和外部表格：
create table test(name string);
create external table test(name string); 创建外部表格需要加上external；

修改表属性：
alter table test set tblproperties (‘EXTERNAL’=’TRUE’); 内部表转外部表
alter table test set tblproperties (‘EXTERNAL’=’FALSE’); 外部表转内部表

归纳一下Hive中表与外部表的区别：

1. 在导入数据到外部表，数据并没有移动到自己的数据仓库目录下(如果指定了location的话)，也就是说外部表中的数据并不是由它自己来管理的！而内部表则不一样；

2. 在删除内部表的时候，Hive将会把属于表的元数据和数据全部删掉；而删除外部表的时候，Hive仅仅删除外部表的元数据，数据是不会删除的！换言之，内部表DROP时会删除HDFS上的数据；外部表DROP时不会删除HDFS上的数据。

3. 在创建内部表或外部表时加上location 的效果是一样的，只不过表目录的位置不同而已，加上partition用法也一样，只不过表目录下会有分区目录而已，load data local inpath直接把本地文件系统的数据上传到hdfs上，有location上传到location指定的位置上，没有的话上传到hive默认配置的数据仓库中。

4. 使用场景：内部表：HIVE中间表，结果表，一般不需要从外部（如本地文件，HDFS上 load 数据）的情况；外部表：源表，需要定期将外部数据映射到表格中。

创建表格

CREATE EXTERNAL TABLE t_zr9558 (
id INT,
ip STRING COMMENT ‘访问者IP’,
avg_view_depth DECIMAL(5,1),
bounce_rate DECIMAL(6,5)
) COMMENT ‘test.com’
PARTITIONED BY (day STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
STORED AS textfile
LOCATION ‘hdfs://cdh5/tmp/zr9558/’;

（1）关键字EXTERNAL：表示该表为外部表，如果不指定EXTERNAL关键字，则表示内部表

（2）关键字COMMENT：为表和列添加注释

（3）关键字PARTITIONED BY：表示该表为分区表，分区字段为day，类型为string

（4）关键字ROW FORMAT DELIMITED：指定表的分隔符，通常后面要与以下关键字连用：
FIELDS TERMINATED BY ‘,’ //指定每行中字段分隔符为逗号
LINES TERMINATED BY ‘\n’ //指定行分隔符
COLLECTION ITEMS TERMINATED BY ‘,’ //指定集合中元素之间的分隔符
MAP KEYS TERMINATED BY ‘:’ //指定数据中Map类型的Key与Value之间的分隔符

（5）关键字STORED AS：指定表在HDFS上的文件存储格式，可选的文件存储格式有：
TEXTFILE //文本，默认值
SEQUENCEFILE // 二进制序列文件
RCFILE //列式存储格式文件 Hive0.6以后开始支持
ORC //列式存储格式文件，比RCFILE有更高的压缩比和读写效率，Hive0.11以后开始支持
PARQUET //列出存储格式文件，Hive0.13以后开始支持

（6）关键词LOCATION：指定表在HDFS上的存储位置。

注：hive 建表的时候默认的分隔符是’\001’，如果建表的时候没有指定分隔符，load文件的时候的分隔符是’\001’。如果需要在建表的时候指定分隔符，需要如下操作：
create table pokes(foo int,bar string)
row format delimited fields terminated by ‘\t’ lines terminated by ‘\n’ stored as textfile;
load data local inpath ‘/root/pokes.txt’ into table pokes;

修改表格

alter table shield_fm_feature_item_ctr add columns ( reporttime STRING COMMENT ‘上报日期时间’) //为表格增加列

alter table test rename to test2; //修改表名

alter table test add partition (day=20160301); //增加分区

alter table test drop partition (day=20160301); //删除分区

alter table test partition (day=20160301) rename to partition (day=20160302); //修改分区

load data local inpath ‘/liguodong/hivedata/datatest’ overwrite into table test;  // 从文件加载数据：覆盖原来数据

load data local inpath ‘/liguodong/hivedata/datatest’ into table test; // 从文件加载数据：添加数据

insert overwrite directory ‘tmp/csl_rule_cfg’ select a.* from test a; // 导出数据到文件

查询和分析数据

dfs -ls /user/hive/warehouse/fm_data.db/shield_fm_feature_item_ctr // 查看 hdfs 文件信息

 

（iv）数值函数：Scalar Functions

MOD(x,y)：取模 x%y

ln(double a)：返回给定数值的自然对数

power(double a, double b)：返回某数的乘幂

sqrt(double a)：开平方

sin/cos/asin/acos：三角函数

 

（v）字符串函数

字符串函数（替换，拼接，逆序等）

（vi）日期函数

进行unix的时间转换等

 

hive命令行操作

[avilazhang@hadoop-bigdata-hive ~]$ hive -e ‘select * from fm_data.shield_fm_feature_item_ctr where day=20160508;’

[avilazhang@hadoop-bigdata-hive ~]$ hive -S -e ‘select * from fm_data.shield_fm_feature_item_ctr where day=20160508;’ 终端的输出不会有mapreduce的进度，只会输出结果。

执行sql文件：hive -f hive_sql.sql

杀掉任务

 

FS Shell

fs 查看目录下文件夹或者文件的大小：

//单位Byte：
hadoop fs -du / | sort -n
//单位MB：
hadoop fs -du / | awk -F ‘ ‘ ‘{printf “%.2fMB\t\t%s\n”, $1/1024/1024,$2}’ | sort -n
//单位GB，大于1G：
hadoop fs -du / | awk -F ‘ ‘ ‘{num=$1/1024/1024/1024; if(num>1){printf “%.2fGB\t\t%s\n”, num, $2} }’ | sort -n

sort -n 表示按照文件大小，从小到大排列顺序。

hadoop fs -du -h hdfs_path    

使用-h显示hdfs对应路径下每个文件夹和文件的大小,文件的大小用方便阅读的形式表示，例如用64M代替67108864

其余FS Shell命令：

hadoop fs -cat hdfs_path //将路径指定的文件内容输出到 stdout

hadoop fs -tail hdfs_path //将文件尾部1k字节的内容输出到 stdout

hadoop fs -stat hdfs_path //返回指定路径的统计信息

hadoop fs -du hdfs_path //返回目录中所有文件的大小，或者只指定一个文件时，显示该文件的大小

详细可见：https://hadoop.apache.org/docs/r1.0.4/cn/hdfs_shell.html

DistCp 概述

DistCp（分布式拷贝）是用于大规模集群内部和集群之间拷贝的工具。 它使用Map/Reduce实现文件分发，错误处理和恢复，以及报告生成。 它把文件和目录的列表作为map任务的输入，每个任务会完成源列表中部分文件的拷贝。 由于使用了Map/Reduce方法，这个工具在语义和执行上都会有特殊的地方。 这篇文档会为常用DistCp操作提供指南并阐述它的工作模型。

详细可见：https://hadoop.apache.org/docs/r1.0.4/cn/distcp.html