Hive入门——hive介绍

2019独角兽企业重金招聘Python工程师标准>>>

Hive介绍

1.由facebook开源，最初用于解决海量结构化的日志数据统计问题，它可以作为ETL工具

2.它是构建hadoop之上的数据仓库

    1)数据计算是MapReduce

    2)数据存储在HDFS

3.可以将结构化的数据文件映射为一张表，并提供类sql查询功能

4.它适合离线数据处理

5.它是将HQL转化为MR的语言翻译器

Hive场景应用举例

1.日志分析

    1).统计网站一个时间段内的PV,UV

    2).从不同维度进行数据分析

2.海量结构化数据离线分析

Hive优缺点

Hive优点

1. 简单容易上手

2.它是为超大数据集而设计的计算和扩展能力

3.提供统一的元数据管理

4.延展性：hive支持用户自定义函数，用户可根据自己的需求来实现自己的函数

5.容错：良好的容错性，节点出现问题SQL仍可完成执行

Hive缺点

1.HQL表达能力有限

• 迭代式算法无法表达，比如pagerank.
• 数据挖掘方面，比如kmeans

2.效率比较低

• hive自动生成的MapReduce作业，通常情况下不够智能化
• hive调优困难
• hive可控性比较差

基本组件

 

 

• 用户接口：

            CLI：在linux终端上操作hive

            WebUI：Web远程界面访问hive

            JDBC：类似mysql和oracle的驱动，用java调用jdbc驱动去执行sql语句

• 云数据存储（Metasotre）：默认derby数据库，真实环境中一般使用mySql数据库
• 驱动器（driver）:解释器，编译器，优化器，执行器
• hadoop分布式集群：利用MR分布式计算，利用HDFS分布式存储

Hive三种安装方式

1、内嵌模式（元数据保存在内嵌的derby种，允许一个会话链接，尝试多个会话链接时会报错，不适合开发环境）

 2、本地模式（本地安装mysql 替代derby存储元数据）

 3、远程模式（远程安装mysql 替代derby存储元数据）

HQL语法解析

Hive数据类型

原子数据类型：tinyint smallint int bigint float double boolean string

集合数据类型：struct map array

示例：

{

    "name": "John Doe", // String 类型

    "salary": 100000.0 , // double类型

    "subordinates": ["Mary Smith" , "Todd Jones"] , // 列表Array类型,

    "deductions": { // 键值Map类型

    "Federal Taxes": 0.2 ,

    "State Taxes": 0.05,

    "Insurance": 0.1

}

    "address": { // 结构Struct类型

        "street": "1 Michigan Ave." ,

        "city": "Chicago" ,

        "state": "IL" ,

        "zip": 60600

    }

}

DDL

Create Database

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

  [COMMENT database_comment]

  [LOCATION hdfs_path]

  [WITH DBPROPERTIES (property_name=property_value, ...)];

eg:

create database if not exists sopdm

comment ‘this is test database’

with dbproperties(‘creator’=’gxw’,’date’=’2014-11-12’)--数据库键值对属性信息

location ‘/my/preferred/directory’;

Describe Database

DESCRIBE DATABASE [EXTENDED] db_name;

eg:

--查看数据库的描述信息和文件目录位置路径信息

describe database sopdm;

--查看数据库的描述信息和文件目录位置路径信息(加上数据库键值对的属性信息)

describe database extended sopdm;

Drop Database

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

eg:

--删除数据库

drop database if exists sopdm;

--级联删除数据库(当数据库还有表时，级联删除表后在删除数据库),默认是restrict

drop database if exists sopdm cascade;

Alter Database

修改数据库的键值对属性

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)

修改数据库所属用户

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)

修改数据存储位置

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)

Use Database

USE database_name;

USE DEFAULT;

 

Create Table

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)

  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]

  [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]

  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]

     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)

     [STORED AS DIRECTORIES]

  [

   [STORED AS file_format]

     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)

  ]

  [LOCATION hdfs_path]

  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)

  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

基础写法举例

数据：

编号，姓名，爱好1-爱好2，地址1：详细地址1-地址2：详细地址2

1,xiaoming,book-tv-code,beijing:chaoyang-shanghai:pudong

2,lilei,book-code,beijing:haidian-shanxi-xian

HQL:

create table psn1{

    id int,

    name string,

    likes array<String>,

    address map <String,String>

}

row format delimited

fielids terminated by ',' // 每一列数据的分隔符

collection items terminated by '-' // 数组array的分隔符

map keys terminated by ':';// map类型的分隔符

 

[EXTERNAL] 创建外部表

创建外部表，在Locacion中指定表存放的位置，默认存放在hive.metastore.warehouse.dir中指定的路径中，创建一个和表明相同的目录

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

  LIKE existing_table_or_view_name

  [LOCATION hdfs_path];

Hive中能存在两种表：

内部表（managed table），数据文件、统计文件、元数据都由Hive自己管理，换句话说，这个表数据存储在哪里我们不用关心，也不用提供，Hive默认存储在HDFS。Hive能管理原始数据的整个生命周期。Hive表删除后，数据也随之删除。

外部表（external table），数据文件存储在其他系统中，可能是HDFS，也可能是HBase、ASV等，HIve只保留映射关系，但Hive表删除后，数据不会丢失，仍然存在于其他系统中。

 

[AS select_statement]

会同步表数据：create table psn3 as select id,name,likes,address from psn1;

不会同步表数据：create table psn3 like select id,name,likes,address from psn1;

row_format（行数据的格式化）:  

: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]

        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)

  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]



eg:

row format delimited

fielids terminated by ',' // 每一列数据的分隔符

collection items terminated by '-' // 数组array的分隔符

map keys terminated by ':';// map类型的分隔符

[STORED AS file_format]（数据存放格式）: 

 : SEQUENCEFILE

  | TEXTFILE    -- 文本格式(Default, depending on hive.default.fileformat configuration)

  | RCFILE      --按列存储 (Note: Available in Hive 0.6.0 and later)

  | ORC         --是RCFile的改进，主要在压缩编码、查询性能上进行了升级

  | PARQUET     --对JSON，thrift等个数数据的列存储

  | AVRO        -- (Note: Available in Hive 0.14.0 and later)

  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)

  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

数据存放格式是指在读取HDFS文件时，使用的HDFS接口类型。如TextInputFormat等。

stored as file_format其实包含三个部分：输入格式输入格式以及序列化格式

 

eg:

// stored as textfile 实际代表：

STORED AS TEXTFILE

INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

// STORED AS SEQUENCEFILE代表：

STORED AS

INPUTFORMAT 'org.apache.hadoop.mapred.SequenceFileInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.mapred.SequenceFileOutputFormat'

 

Hive中，默认使用的是TextInputFormat，一行表示一条记录。在每条记录(一行中)，默认使用^A分割各个字段。‘hive.default.fileformat’设置默认存储格式。也可以使用自定义的SerDe或者Hive自带的SerDe。

 

constraint_specification key，foreign key 等约束:

  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]

    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

DDL分区

分区是将HDFS中不同分区的数据文件存放于不同的目录下。例如一个Hive内部表数据保存于HDFS中/user/hive/warehouse/mytest.db/下，这个目录下全是数据文件（可能是文本文件格式，也可能是其他格式，根据file_format指定）。这时候若引入一个字段用于分区，则将会在这个目录下产生新的目录，每个目录对应一个分区。每个分区数据分别存在自己对应的目录下。这样查询效率也会更高。

分区表实际是一个文件夹，表名称即文件夹名，每个分区实际是表名这个文件夹下面的不同文件。

常见的有时间分区和业务分区。

分区字段不能和表中已有字段重复

create table psn1{

id int,

name string,

likes array<String>,

address map <String,String>

}

partitioned by (sex string )

row format delimited

fielids terminated by ','

collection items terminated by '-'

map keys terminated by ':';

 

添加分区

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];

 partition_spec:

  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

eg:

alter table psn5 add partition (sex='girl');

如果创建多分区，则分区的数据按照创建目录的顺序在hdfs上建立目录文件夹。

eg:

alter table psn5 add partition (sex='girl',age=1);

则在hdfs上的目录结构为： ../girl/age/数据

在删除girl分区的时候，其字分区也会被删除

 

删除分区

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]

  [IGNORE PROTECTION] [PURGE];

eg: alter table drop partition (sex='girl');

将数据导入指定分区：

LOAD DATA LOCAL INPATH '/root/data' INTO TABLE psn1 partition (sex='boy')

分区目录在metaStore中的存储

DML

Loading data

https://cwiki.apache.org/confluence/display/Hive/Tutorial#Tutorial-LoadingData

上传本地数据：LOAD DATA LOCAL INPATH '/root/data' INTO TABLE psn1

如果上传目录是hdfs目录，则会将hdfs的数据移动到表的目录中。如果上传的是本地文件，则先将文件拷贝到hdfs的临时文件，再将文件移动到hive表所在目录

hdfs dfs -put data1 /user

上传HDFS上文件：load data inpath 'user/data1' into table psn1;

覆盖HDFS上文件：load data inpath 'user/data1' overwrite into table psn1;

如上图所示：将hdfs中data1数据/user/data1经过load后，被移动到了hive的存储路径下：/user/hive/warehouse/psn1中

如果上传的数据文件名称相同，则会创建一个xxx_copy_1的文件夹，不会覆盖原有数据

 

对分析的结果进行保存

from page_view_stg pvs

       insert into table page_view select pvs.viewTimve,pvs.userid

等价于：

insert into table page_view select pvs.viewTimve,pvs.userid from page_view_stg

 

Hive数据加载注意问题

分隔符问题:

    分隔符默认只有单个字符。如果有多个字符，默认取第一个字符作为分隔符。

数据类型对应问题：

    Load数据数据，字段类型不能互相转化时，查询结果返回NULL。而实际的数据仍然存在。

    Select查询插入，字段类型不能互相转化时，插入数据为NULL。而实际的数据也为NULL。

其他：

    Select查询插入数据，字段值顺序要与表中字段顺序一致，名称可不一致。

    Hive在数据加载时不做检查，查询时检查。

    外部分区表需要添加分区才能看到数据。

 

BeeLine

Hive客户端工具后续将使用Beeline 替代HiveCLI ，并且后续版本也会废弃掉HiveCLI 客户端工具。

Beeline是Hive新的命令行客户端工具。

Beeline是从 Hive 0.11版本引入的。

HiveServer2 支持一个新的命令行Shell，称为Beeline，它是基于SQLLine CLI的JDBC客户端。

Beeline支持嵌入模式(embedded mode)和远程模式(remote mode)。在嵌入式模式下，运行嵌入式的Hive(类似Hive CLI)，而远程模式可以通过Thrift连接到独立的HiveServer2进程上。从Hive 0.14版本开始，Beeline使用HiveServer2工作时，它也会从HiveServer2输出日志信息到STDERR。

连接方式1：

> beeline

> !connect jdbc:hive ://node3:1000

> show tables

> !quit

连接方式2：

> beeline -u jdbc:hive2://node3:1000 -n root

Hive JDBC

通过java调用hive的jdbc驱动来查询数据。

public class HiveJdbcClient {

// 驱动类

private static String driverName = "org.apache.hive.jdbc.HiveDriver";

public static void main(String[] args) throws SQLException {

try {

Class.forName(driverName);

} catch (ClassNotFoundException e) {

e.printStackTrace();

System.exit(1);

}

Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "hive", "");

Statement stmt = con.createStatement();

String tableName = "testHiveDriverTable";

stmt.execute("drop table if exists " + tableName);

stmt.execute("create table " + tableName + " (key int, value string)");

// show tables

String sql = "show tables '" + tableName + "'";

System.out.println("Running: " + sql);

ResultSet res = stmt.executeQuery(sql);

if (res.next()) {

System.out.println(res.getString(1));

}

// describe table

sql = "describe " + tableName;

System.out.println("Running: " + sql);

res = stmt.executeQuery(sql);

while (res.next()) {

System.out.println(res.getString(1) + "\t" + res.getString(2));

}

// load data into table

// NOTE: filepath has to be local to the hive server

// NOTE: /tmp/a.txt is a ctrl-A separated file with two fields per line

String filepath = "/tmp/a.txt";

sql = "load data local inpath '" + filepath + "' into table " + tableName;

System.out.println("Running: " + sql);

stmt.execute(sql);

// select * query

sql = "select * from " + tableName;

System.out.println("Running: " + sql);

res = stmt.executeQuery(sql);

while (res.next()) {

System.out.println(String.valueOf(res.getInt(1)) + "\t" + res.getString(2));

}

// regular hive query

sql = "select count(1) from " + tableName;

System.out.println("Running: " + sql);

res = stmt.executeQuery(sql);

while (res.next()) {

System.out.println(res.getString(1));

}

}

}

hive正则表达式

可以通过正则表达式对数据进行抽取和清洗

CREATE TABLE apachelog (

  host STRING,

  identity STRING,

  user STRING,

  requet 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;

 

regexp

语法: A REGEXP B 

操作类型: strings 

描述: 功能与RLIKE相同

select count(*) from olap_b_dw_hotelorder_f where create_date_wid not regexp '\\d{8}'

 

regexp_extract

语法: regexp_extract(string subject, string pattern, int index)

返回值: string

说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

hive> select regexp_extract('IloveYou','I(.*?)(You)',1) from test1 limit 1;

 

regexp_replace

语法: regexp_replace(string A, string B, string C)

返回值: string

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。

hive> select regexp_replace("IloveYou","You","") from test1 limit 1;

HIVE函数

Hive常用函数大全（一）（关系/数学/逻辑/数值/日期/条件/字符串/集合统计/复杂类型）

Hive常用函数大全（二）窗口函数、分析函数、增强group

Hive常见内置函数及其使用

 

UDF（user defined function）

自定义函数

1.自定义函数类，继承类UDF,实现evaluate方法

2.打jar包，并将jar包上传至HIVE服务器：hive> add jar /root/tuomi.jar;

3.创建临时函数：hive> create temporary function tuomi as 'com.sxt.hive.TuoMin'

4.调用函数：hvie> select id,tuomin(name),name,likes from psn1;

5.销毁临时函数：hive> drop temporary function tuomin;

UDAF 内置聚合函数

UDAF(user defined aggregation function)用户自定义聚合函数，针对记录集合

永久函数

方式1、 修改源码添加相应的函数类

方式2、 hive -i ‘file’

方式3、 新建hiverc文件 

HIVE QL优化

思路： HQL会转换为MR,所以应结合数据场景，根据对应的MR程序执行效率来写HQL

为什么不建议使用insert插入数据

Hive优化策略

Hive分区表与分桶

metaStore

 

 

 

转载于:https://my.oschina.net/freelili/blog/1858858