2020-11-20

Hive常用命令

Hive中定义变量

• 内置命名空间

Hive内置命名空间包含了hivevar、hiveconf、system和env。

• 在Hive中写入hivevar变量

hive --define/--hivevar key=value

• 显示变量

set env:HOME
set hivevar:key
set key

• 给变量赋值

set key=value
set hivevar:key=value

• 在sql语句中调用变量

create table table_name(i int, ${hivevar:key} string)
create table table_name(i int, ${key} string)

只执行一次的Hive脚本

echo "one row" > /root/path/myfile 
hive -e "load data local inpath '/root/path/myfile' into table table_name"

在Hive中执行shell命令

hive>!pwd

在Hive中使用hadoop

hive>dfs -ls

JDBC时间和UTC时间之间的相互转换

• 指定了格式的转换

from_unixtime(unix_timestamp('20180930',"yyyyMMdd"),'yyyyMMdd')

• 为空生成当前的unix时间戳

hive> select unix_timestamp();
unix_timestamp(void) is deprecated. Use current_timestamp instead.
OK
1547438004

• 转换成当前的UTC时间戳

hive> select from_unixtime(unix_timestamp());
unix_timestamp(void) is deprecated. Use current_timestamp instead.
OK
2019-01-14 13:27:12

• UTC时间格式为

YYYY-MM-DD hh:mm:ss.ffffffff

集合数据类型

• struct：需要事前定义好格式
• map: 类似于struct，但不是事前定义好格式的
• array： 数组数据类型

create table employees( 
name string, 
salary float, 
subordinates array, 
deductions map<string, float>, 
address struct<street:string,city:string,state:string,zip:int> 
)

Hive数据库的基本操作

• 指定Hive数据库的路径

create database database_name
location '/my/preferred/directory'

• 查看Hive数据库的路径

describe database database_name

• 创建Hive数据库的扩展信息

create database database_name
WITH DBPROPERTIES ('creator' = 'name', 'date' = '2019-01-09');

• 查看所创建的扩展信息

describe database extended database_name

• 修改数据库属性

ALTER DATABASE database_name SET DBPROPERTIES ('edited-by' = 'tjm', 'date' = '2019-01-09')

Hive数据表的基本操作

• 表的创建

create table if not exists employee(
name string comment 'Employee name',
salary float comment 'Employee salary',
subordinates array<string> comment 'Names of subordinates',
deductions map<string, float> comment 'Keys are deductions name,values are percnetages',
address struct<street:string, city:string, state:string,zip:int> comment 'Home address')
COMMENT 'Description of the table'
TBLPROPERTIES ('creator'='name', 'date'='2019-01-09')

• 用复制表结构的方式创建表

create table if not exists employee2
LIKE employee

• 查看所创建表的基本信息

关键字：show、describe、formatted、extended

show tables in database_name  --查看指定库内的所有表

describe employees  --描述表的字段信息

describe extended employees  --显示所创建表的扩展信息

describe formatted employee2  --最全面的信息查看方式，能够显示出最多的信息

show create table employee  --显示建表时候的建表脚本

show tables 'empl.*' --通过模糊匹配的方式进行查询

• 使用外部表来建表

外部表的重要作用是当我们对一张表进行删除的时候，仅仅对表结构进行删除，不会影响到建表时使用到的原始数据。

关键字：external

create external table if not exists stocks (
exchange string,
symbol string,
ymd string,
price_open float,
price_high float,
price_low float,
price_close float,
volume int,
price_adj_close float)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

• 使用分区来管理表

关键字：partitioned by

create table employees(
name string,
salary float,
subordinates array<string>,
deductions map<string, float>,
address struct<street:string, city:string, state:string, zip:int>
)
PARTITIONED BY (country string, state string);

• 通过分区来载入数据

关键字：load、local

load data local inpath '${env:HOME}/directory' into table employees
partition (country='US',state='CA')

• 表重命名

关键字：alter

alter table table_name rename to new_table_name;

• 增加、修改和删除表分区

增加表分区

alter table table_name add if not exists 
partition(year=2011, month=1, day=1) location '/logs/2011/01/01' 
partition(year=2011, month=1, day=2) location '/logs/2011/01/02'
partition(year=2011, month=1, day=3) location '/logs/2011/01/03';

修改表分区

alter table table_name partition(year=2011, month=12, day=2)
set location 'hdfs://ip_address/logs/2011/01/02';

删除表分区

alter table table_name drop if exists 
partition(year=2011, month=1, day=1);

• 增加和修改表的列信息

修改列

alter table employees
change column name new_name string comment 'change to  new column name'
first --first替换后放到最前，如果使用after，那么紧接列名，表示移动到这个字段之后

增加列

alter table employees add columns(
app_name string comment 'application name',
session_id bigint comment 'session_id name'
)

• 更新表的属性

alter table employees set tblproperties(
'creator'='name',
'date'='2019-01-10'
)

操作完成后旧表的属性会被新表的属性所覆盖。

数据的导入

• 向已经创建的表中导入数据

指定的路径是一个目录或者文件，如果创建的表不是分区表那么就不需要添加partition。 导入使用了overwrite字段的话，如果要导入的表在数据库中不存在，那么首先进行新建，如果表在数据库中已经存在，就进行重写，如果没有overwrite关键字，仅仅是把新文件增加到目标文件夹中而不会删除之前的数据。

load data local inpath '${env:HOME}/directory' overwrite into table employees
partition (country='US',state='CA')

load data local inpath '${env:HOME}/directory' into table employees
partition (country='US',state='CA')

• 通过查询语句向表中插入数据

insert overwrite table employees
partition (country='US',state='CA')
select * from staged_employees se
where se.city='US' and se.st='CA'

insert into table employees
partition (country='US',state='CA')
select * from staged_employees se
where se.city='US' and se.st='CA';

这里可以选用overwrite或者into，这两个字段的意义和之前所说的一样。

• 一次扫描完成全部分区数据的插入

都从原表读取数据的情况。

insert overwrite table sales select * from history where action='purchased'
insert overwrite table credits select * from history where action='returned'

只进行一次查询的情况。

from history 
insert overwrite table sales select * where action='purchased'
insert overwrite table credits select * where action='returned'

区别于对数据完成一次扫描然后进行一次数据插入，我们还可以在进行一次扫描之后完成我们想要的全部数据的插入。

• 使用单个查询语句创建表并导入数据

关键字：create...as...

create table table_name as 'select no,age from
use_table where age>50'

数据的导出

• 使用hadoop命令

hadoop fs -cp source_path target_path

这个命令适用于同一个服务器之间的数据拷贝，如果是不同的服务器之间通过hadoop来进行数据的拷贝，如下：

hadoop fs -get file_path --原服务器
hadoop fs -put file_path --目标服务器

• 使用insert...directory...导出文件到指定目录

insert overwrite/into local directory '/tmp/ca_employees'
select name.salary,address 
from table_name 
where se.cnty-''US and se.st='OR';

• 同时对多个文件进行导出

from staged_employees se
insert overwrite/into local directory '/tmp/or_employees'
select *  where se.city='US' and se.st='OR';
insert overwrite/into local directory '/tmp/ca_employees'
select *  where se.city='US' and se.st='CA';

Hive中的类型转换

使用关键字cast进行数据类型的转换。

hive> select cast(anum as float) from littlebigdata;
OK
NULL
2.0

此例是将整型数据转换为了浮点型数据。

Hive中的抽样查询

• 随机抽样

select * from table_name tablesample(bucket 3 out of 10 on rand()) s;

10表示对整个数据划分出的bucket个数，3表示从第三个bucket开始抽样。

• 基于百分比的数据块抽样

select * from table_name tablesample(0.1 percent) s;

注：这种抽样并不适用于所有文件的格式。

Hive创建查询视图

使用Hive的视图，可以避免select语句的多重嵌套，增加可读性。

• 不使用视图的脚本

select a.head_no,a.bank_name from (select no, name from table_name) a limit 2;

• 视图脚本

关键字：create view

create view table_view as (select no,name from table_name)  --创建视图

select no, name from table_view limit 2  --基于视图的查询

查看Hive函数的描述

• 发现所有函数

hive> show functions;
OK
!
!=
$sum0
%
&
*
+
-
/
<
<=
<=>
<>
=
==
>
>=
^
abs
acos
add_months
aes_decrypt
.
.
.

• 查看指定函数的描述

hive> describe function abs;
OK
abs(x) - returns the absolute value of x

• 查看带有实例的更详细的描述

hive> describe function extended abs;
OK
abs(x) - returns the absolute value of x
Example:
  > SELECT abs(0) FROM src LIMIT 1;
  0
  > SELECT abs(-5) FROM src LIMIT 1;
  5
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs
Function type:BUILTIN

• 表生成函数

hive> select array(1,2,3);
OK
[1,2,3]

hive> select explode(array(1,2,3));
OK
1
2
3

自定义Hive文件和记录格式

修改如下语句，替代为指定的格式以更改存储格式。

STORED AS 
INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

• 记录格式：SerDe

SerDe是序列化、反序列化的简写，一个SerDe是将一条记录的非结构化字节转化成Hive可以使用的一条记录的过程。

• 以ORC文件格式进行存储

ORC格式：

ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'

 

参考：《Hive编程指南》