hive
- hive架构
- antrl语法树
- 执行流程图解
- hive数据类型
- 数据值
- 日期时间类型
- String类型
- 其他类型
- 复合类型
- 字符串
- hive数据库操作
- 创建数据库
- 删除数据库
- 修改数据库
- 使用数据库
- hive表的操作
- 内部表和外部表
- copy数据
- like && as创建表
- 创建分区
- 添加分区
- 删除分区:
- 使用load添加分区
- 同时向多个表插入数据
- 正则表达式
- 自定义函数
- 查看hive在hdfs上的存储
hive提供了HiveQL方言来查询存储在hadoop集群中的数据。hive可以将大多数的查询转换为MapReduce作业 |
hive最适合于数据仓库,使用数据仓库进行相关的静态数据分析,而不需要快速响应给出结果,而且数据本身不会频繁变化。 |
hive不是一个完整的数据库。其中最大的限制就是hive不支持记录级别的更新、插入或者删除操作。但是可以将查询生成新表或者将查询结果导入到文件中 |
hive查询延时比较严重 |
hive不支持事务。 |
HiveQL并不符合ANSI SQL标准,和Oracle、MySQL、SQL Server支持的常规SQL方言在很多方面存在差异,不过HiveQL和MySQL提供的SQL方言最接近。 |
Apache Hive™数据仓库软件有助于读取,编写和管理驻留在分布式存储中的大型数据集,并使用SQL语法进行查询。 |
hive架构
用户接口主要有三个:CLI,Client 和 WebUI(hwi)。其中最常用的是CLI,Cli启动的时候,会同时启动一个Hive副本。Client是Hive的客户端,用户连接至Hive Server。在启动 Client模式的时候,需要指出Hive Server所在节点,并且在该节点启动Hive Server。 WebUI是通过浏览器访问Hive。 |
Hive将元数据存储在数据库中,如mysql、derby。Hive中的元数据包括表的名字,表的列和分区及其属性,表的属性(是否为外部表等),表的数据所在目录等 |
解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中,并在随后有MapReduce调用执行 |
Hive的数据存储在HDFS中,大部分的查询、计算由MapReduce完成(包含*的查询,比如select * from tbl不会生成MapRedcue任务) |
antrl语法树
在执行流程2之后
执行流程图解
1、UI调用驱动程序的执行接口(图1中的步骤1)。
2、驱动程序为查询创建会话句柄,并将查询发送到编译器以生成执行计划(步骤2)。
3、编译器从Metastore获取必要的元数据(步骤3和4)。此元数据用于检查查询树中的表达式以及
基于查询谓词的修剪分区。
4、由编译器生成的计划(步骤5)是阶段的DAG(有向无环图),其中每个阶段是mapreduce作业或
者元数据操作或者对HDFS的操作。对于map/reduce阶段,计划包含map运算符树(在mapper上执行
的运算符树)和reduce运算符树(用于需要reducer的操作)。
5、执行引擎将这些阶段提交给适当的组件(步骤6,6.1,6.2和6.3)。在每个
(Mapper/Reducer)中,与表或中间输出相关联的反序列化器用于从HDFS文件中读取行,这些行通
过关联的运算符树传递。生成输出后,它将通过序列化程序写入临时HDFS文件(如果操作不需要
reducer,则会在mapper中发生)。临时文件用于向计划的后续mapreduce阶段提供数据。对于DML
操作,最终临时文件将移动到表的位置。此方案用于确保不读取脏数据(文件重命名是HDFS中的原子
操作)。
6、对于查询,执行引擎直接从HDFS读取临时文件的内容,作为来自驱动程序的查询的一部分(步骤
7,8和9)。
hive数据类型
数据值
TINYINT | (1-byte 有符号整数, 从-128到127) |
SMALLINT | (2-byte有符号整数, 从-32,768到32,767) |
INT/INTEGER | (4-byte有符号整数, 从-2,147,483,648到2,147,483,647) |
BIGINT | (8-byte有符号整数, 从-9,223,372,036,854,775,808到9,223,372,036,854,775,807) |
FLOAT | (4-byte单精度浮点数) |
DOUBLE | (8-byte双精度浮点数) |
DOUBLE PRECISION | (DOUBLE别名, 从Hive 2.2.0开始支持) |
DECIMAL | 从Hive 0.11.0开始支持的最大38位精度,Hive 0.13.0开始用户可以指定小数位。 |
Decimals:
Hive 0.11和0.12中DECIMAL类型限制为38位。
在Hive 0.13中用户可以指定位数和精度:DECIMAL(precision, scale). 如果小数位没有指定,默认为0,如果位数没有指定,则默认是10. precision指总位数,scale指小数位。
日期时间类型
TIMESTAMP | (从Hive 0.8.0开始支持) 年月日时分秒毫秒 |
DATE | (从Hive 0.12.0开始支持) 年月日 |
INTERVAL | (从Hive 1.2.0开始支持) |
Timestamp类型:
支持UNIX类型的时间精确到毫秒。System.currentTimeMills()
支持的转换:
整数类型:解释为UNIX中的秒 1234567887
浮点类型:解释为带小数的UNIX时间秒
String类型: JDBC中java.sql.Timestamp兼容格式"YYYY-MM-DD HH:MM:SS.fffffffff" (9位小数精确度)
>create table tb_timestamp
>(
>id int,
>mytime timestamp
>);
>from dual
>insert into tb_timestamp
>select 1, '1998-12-23 23:34:45' limit 1
>from dual
>insert into tb_timestamp
>select 2, cast('1999-1-12 12:34:45' as timestamp) limit 1
date:
DATE类型数据表示year/month/day, 格式为YYYY-MM-DD. 例如:DATE '2013-01-01'。 Date
类型不包含时间. Date类型支持从0000-0101到9999-12-31。
日期转换 | 结果 |
cast(date as date) | 相同的日期值 |
cast(timestamp as date) | 根据当前时区解析出timestamp中年/月/日作为日期类型 |
cast(string as date) | 如果string是’YYYY-MM-DD’格式, 返回其中的年/月/日。否则返回NULL。 |
cast(date as timestamp) | 返回date日期下的零点零分零秒的timestamp类型数据。 |
cast(date as string) | 年/月/日转换为’YYYY-MM-DD’类型的字符串。 |
>create table tb_datetest
>(
>id int,
>mytime date
>);
>from dual
>insert into tb_datetest
>select 2, cast('1998-12-23' as date) limit 1;
>from dual
>insert into tb_datetest
>select 3, '1998-12-23' limit 1;
>insert into tb_datetest values(4, '1998-12-23'); //少用
String类型
STRING | |
VARCHAR | (从Hve 0.12.0开始支持) |
CHAR | (从Hive 0.13.0开始支持 |
其他类型
BOOLEAN | |
BINARY | (从Hive 0.8.0开始支持) |
复合类型
数组 | ARRAY<data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.) |
maps | MAP<primitive_type, data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.) |
structs | STRUCT<col_name : data_type [COMMENT col_comment], …> |
union | UNIONTYPE<data_type, data_type, …> (Note: 从Hive 0.7.0.开始支持) |
字符串
是单引号或双引号引起来的字符序列。 “tom’s cat”
hive数据库操作
启动hive,hive --service metastore &(&表示可以继续输入)
hive命令行的清屏命令:
> !clear;
创建数据库
[ ]这里面的内容可写可不写
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment] //添加注释
[LOCATION hdfs_path] //指定在hdfs上的位置
[WITH DBPROPERTIES (property_name=property_value, …)];
hive> create database mydb;
hive> create database mydb1 comment 'my db one' location '/user/hive/mydb1';
hive> create database mydb2 comment 'my db two' location '/user/hive/mymydbdb2' with dbproperties ('key1'='value1', 'key2' = 'value2');
desc database <dbname>;查看数据库的描述信息
desc database extended <dbname>;查看数据库的详情
删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
如果数据库中有内容,则会报错:
drop database <dbname>;
FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask.
InvalidOperationException(message:Database mydb is not empty. One or more
tables exist.)
默认情况下是restrict模式, cascade级联删除
drop database <dbname> cascade;
修改数据库
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
alter database mydb1 set dbproperties('key1'='value1','key2'='value2');
alter database mydb1 set owner user myhive;
alter database mydb1 set owner role myrole;
使用数据库
USE database_name;
USE DEFAULT;
hive表的操作
//显示列名
set hive.cli.print.header=true;
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(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, …)
ON ((col_value, col_value, …), (col_value, col_value, …), …)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)]
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, …)]
[AS select_statement]; – (Note: not supported for external tables)
data_type
: primitive_type
| array_type
| map_type
| struct_type
| union_type
primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| STRING
| BINARY
| TIMESTAMP
| DECIMAL
| 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, ... >
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]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, ...)]
file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE
| ORC
| PARQUET
| AVRO
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
constraint_specification:
: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES
table_name(col_name, ...) DISABLE NOVALIDATE
for example:
id,name,likes,addresses
1,小明1,lol-book-movie,beijing:xisanqi-shanghai:pudong
2,小明2,lol-book-movie,beijing:xisanqi-shanghai:pudong
3,小明3,lol-book-movie,beijing:xisanqi-shanghai:pudong
4,小明4,lol-book-movie,beijing:xisanqi-shanghai:pudong
5,小明5,lol-movie,beijing:xisanqi-shanghai:pudong
6,小明6,lol-book-movie,beijing:xisanqi-shanghai:pudong
7,小明7,lol-book,beijing:xisanqi-shanghai:pudong
8,小明8,lol-book,beijing:xisanqi-shanghai:pudong
9,小明9,lol-book-movie,beijing:xisanqi-shanghai:pudong
create table tb_user1(
id int,
name string,
likes array<string>,
addrs map<string, string>
)
ROW FORMAT
DELIMITED
FIELDS TERMINATED BY ',' //列按,号分割
COLLECTION ITEMS TERMINATED BY '-'//数组按- 分割
MAP KEYS TERMINATED BY ':'//集合按 : 分割
LINES TERMINATED BY '\n'//行按 \n 分割
LOCATION '/opt/users/userdata';
内部表和外部表
创建内部表,如果内部表被删除没那么远程hdfs上的数据也会被一并删除。
创建外部表,可以指定存储在hdfs上的位置,如果没有指定位置,删除外部表,从hdfs上上传的数据会被一并删除,如果指定了存储位置,那么删除外部表,存储位置path上的数据不会被一并删除。
create table tablename1(字段名 类型,字段2 类型);
create external table tablename2(字段名 类型,字段2 类型) [location 'path'];
copy数据
from tb_user1
insert into table tb_user2
select id, name, likes, addrs;
like && as创建表
表结构一样,没有数据
create table new_user like tb_user1;
表结构内容跟指定相同
create table newnew_user as select id, name, likes from tb_user1;
创建分区
create table tb_user4 (
id int,
name string,
likes array<string>,
addrs map<string, string>
)
partitioned by (sex string comment 'gender', age int comment 'age years')
添加分区
alter table tb_user4 add partition (sex='male', age='25');
alter table tb_user4 add partition (sex='male', age='24');
alter table tb_user4 add partition (sex='male', age='23');
alter table tb_user4 add partition (sex='male', age='22');
alter table tb_user4 add partition (sex='male', age='21');
向不同分区添加数据
from tb_user1
insert into tb_user4 partition (sex='male', age='25')
select id, name, likes, addrs where id % 3 == 0;
from tb_user1
insert into tb_user4 partition (sex='female', age='25')
select id, name, likes, addrs where id % 2 == 0;
删除分区:
alter table tb_user4 drop partition (sex='female', age='21');
alter table tb_user4 drop partition (sex='male');
使用load添加分区
使用load不需要手动添加分区,会自己添加
local指本地文件,不加local指hdfs上的文件 hdfs dfs -put 上传到hdfs
load data local inpath '/root/user1.txt' into table tb_user5 partition (age=14, sex='female');
load data local inpath '/root/user2.txt' into table tb_user5 partition (age=15, sex='male');
load data local inpath '/root/user3.txt' into table tb_user5 partition (age=16, sex='female');
load data local inpath '/root/user4.txt' into table tb_user5 partition (age=44, sex='male');
overwrite表示覆盖掉该分区的原有数据
load data local inpath '/root/user1.txt' overwrite into table tb_user5 partition (age=82, sex='male');
load data inpath ‘<hdfs路径>’ [overwrite] into table ;
此时,hdfs的文件路径被修改了,而不是发生拷贝,hdfs路径有可能不是表放数据的目录
需要将hdfs路径的文件要么拷贝到表的目录中,要么复制到表的目录中,对于hdfs,直接修改了数
据文件的路径。
同时向多个表插入数据
from tb_user6
insert into table tb_user8
select id, name, likes, addrs where id in (1,2,3)
insert into table tb_user9
select id, name, likes, addrs where id in (4,5)
insert into table tb_user10
select id, name, likes, addrs where id in (6,7)
insert into table tb_user11
select id, name, likes, addrs where id in (8,9);
正则表达式
数据集:
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
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
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;
//转换时间格式
2009-05-04T13:42:11Z
//按秒
select unix_timestamp(“2009-05-04T13:42:11Z”, “yyyy-MM-dd’T’HH:mm:ss’Z’”) from dual;
//按指定格式
select from_unixtime(unix_timestamp(“2009-05-04T13:42:11Z”, “yyyy-MM-dd’T’HH:mm:ss’Z’”), ‘yyyy-MM-dd HH:mm:ss’) from dual;
将数据集导入下面数据库
create table myplayrec(
userid string,
playtime string,
musicbrainz_artist_id string,
artist_name string,
musicbrainz_track_id string,
track_name string
)
row format
delimited
fields terminated by '\t'
lines terminated by '\n';
create table mc(
userid string,
playtime timestamp,
musicbrainz_artist_id string,
artist_name string,
musicbrainz_track_id string,
track_name string
)
row format
delimited
fields terminated by '\t'
lines terminated by '\n';
insert into mc
select userid,
from_unixtime(unix_timestamp(playtime, "yyyy-MM-dd\'T\'HH:mm:ss\'Z\'"),
'yyyy-MM-dd HH:mm:ss'),
musicbrainz_artist_id, artist_name, musicbrainz_track_id, track_name
from myplayrec;
自定义函数
a)自定义UDF需要继承org.apache.hadoop.hive.ql.UDF。(org.apache.hadoop.hive.ql.exec.UDAF(函数类继承))(继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF,实现initialize, process, close三个方法。)
b)需要实现evaluate函数,evaluate函数支持重载。
3、步骤
a)把程序打包放到目标机器上去;
b)进入hive客户端,添加jar包:hive>add jar /run/jar/udf_test.jar;
c)创建临时函数:hive>CREATE TEMPORARY FUNCTION add_example AS ‘hive.udf.Add’;
d)查询HQL语句:
SELECT add_example(8, 9) FROM scores;
SELECT add_example(scores.math, scores.art) FROM scores;
SELECT add_example(6, 7, 8, 6.8) FROM scores;
e)销毁临时函数:hive> DROP TEMPORARY FUNCTION add_example;
查看hive在hdfs上的存储
hive> dfs -ls /;
Found 4 items
drwxr-xr-x - root supergroup 0 2021-04-09 17:45 /mr
drwxr-xr-x - root supergroup 0 2021-04-16 11:26 /root
drwx------ - root supergroup 0 2021-04-12 21:32 /tmp
drwxr-xr-x - root supergroup 0 2021-04-13 14:49 /user
脚本方式运行hive的参数:
>hive --service cli --help
usage: hive
-d,--define <key=value> Variable substitution to apply to Hive
commands. e.g. -d A=B or --define A=B
--database <databasename> Specify the database to use
-e <quoted-query-string> SQL from command line
-f <filename> SQL from files
-H,--help Print help information
--hiveconf <property=value> Use value for given property
--hivevar <key=value> Variable substitution to apply to Hive
commands. e.g. --hivevar A=B
-i <filename> Initialization SQL file
-S,--silent Silent mode in interactive shell
-v,--verbose Verbose mode (echo executed SQL to the
console)