10分钟快速熟悉hive操作命令

hive是什么

Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供简单的sql查询功能,可以将sql语句转换为MapReduce任务进行运行。HBase是Hadoop的数据库,一个分布式、可扩展、大数据的存储 。

Hive帮助熟悉SQL的人运行MapReduce任务。因为它是JDBC兼容的,同时,它也能够和现存的SQL工具整合在一起。运行Hive查询会花费很长时间,因为它会默认遍历表中所有的数据。虽然有这样的缺点,一次遍历的数据量可以通过Hive的分区机制来控制。分区允许在数据集上运行过滤查询,这些数据集存储在不同的文件夹内,查询的时候只遍历指定文件夹(分区)中的数据。这种机制可以用来,例如,只处理在某一个时间范围内的文件,只要这些文件名中包括了时间格式。

操作数据库

1、创建数据库

-- 创建数据库语法:
CREATE DATABASE IF NOT EXISTS database_name;
--例如:
CREATE DATABASE IF NOT EXISTS userdb;

2、删除数据库

-- 删除数据库语法:
DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name;
-- 例如:
DROP DATABASE IF EXISTS userdb;

3、进入数据库

-- 进入数据库
USE database_name;

show databases; # 查看某个数据库
show tables; # 展示所有表
desc 表名; # 显示表结构
show partitions 表名; # 显示表名分区
show create table_name; # 显示创建表的结构 

操作表属性

1、显示数据库下面所有表

SHOW TABLES;

2、删除表

DROP TABLE [IF EXISTS] table_name;

3、显示建表结果

SHOW CREATE TABLE table_name;

4、显示表分区

SHOW PARTITION table_name;

5、修改表属性

-- 内部表转外部表
ALTER TABLE table_name SET TBLPROPERTIES ('EXTERNAL'='TRUE');
-- 外部表转内部表
ALTER TABLE table_name SET TBLPROPERTIES ('EXTERNAL'='FALSE');

6、表重命名

ALTER TABLE table_name RENAME TO new_table_name;

7、新增列

ALTER TABLE table_name ADD COLUMNS (cloumn_name string);

8、删除列

ALTER TABLE name DROP [COLUMN] column_name;

9、修改列

ALTER TABLE name CHANGE column_name new_name new_type;

10、删除分区

ALTER TABLE table_name DROP IF EXISTS PARTITION(day=); -- 这里分区是day

11、增加分区

-- 分区字段包含:p_hour,p_city,p_loctype
ALTER TABLE table_name IF NOT EXISTS \
ADD PARTITION (p_hour='2017113003', p_city='573', p_loctype='MHA');

12、修改分区、LOCATION

-- 修改location
ALTER TABLE table_name SET LOCATION "new_location_name";
-- partition修改location路径
ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new_location_name";
-- 修改分区名
ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');

创建表

语法:

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

举例:

CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
salary String, destination String)
COMMENT ‘Employee details’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

LOAD DATA语句

语法如下:

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2 ...)]
-- 说明:
LOCAL是标识符指定本地路径。它是可选的。
OVERWRITE 是可选的,覆盖表中的数据
PARTITION 这是可选的

参数描述:

  • LOCAL是标识符,指本地路径,它是可选的
  • 命令中带LOCAL,表示从本地文件系统中加载数据,可以是相对路径,绝对路径,目录,URL(file:///user/hive/project/data)
  • 命令中不带LOCAL,表示从HDFS加载文件,可以是完整的URL方式或者使用fa.default.name定义的值
  • FILEPATH 文件路径,可以是:
  • 相对路径,如:project/data/file.txt
  • 绝对路径,如:/user/hive/project/data/file.txt
  • 完整的URL,如:hdfs://namenode:9000/user/hive/project/data/file.txt
  • 目录,如:/user/project/data;表示加载目录下的所有文件
  • OVERWRITE:
  • 命令中带OVERWRITE,加载数据之前会先清空目标表或分区中的内容
  • 命令中不带OVERWRITE,向目标表后者分区追加内容

例如:

  • 本地数据加载到表
load data local inpath '/opt/test/emp.txt' \
into table emp_part partition (year='2016', month='3');

hive内置运算符

1、算术运算符

2、逻辑运算符

3、复杂运算符

七、hive视图与索引

八、实例

1、把本地文件放在新建表中

-- 创建表时加载
CREATE TABLE table_name (year string, temperature int) \
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOAD DATA LOCAL INPATH 'xxx/input.txt' \
OVERWRITE INTO TABLE table_name;

2、把hdfs文件放在新建表中

-- 创建表时加载
CREATE TABLE table_name (year string, temperature int) \
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' \
LOAD DATA INPATH '/emp.txt' INTO TABLE table_name partition (year='2016', month='6');

3、把查询结果放在一个新建表中

描述:

create table 表名 as sql

举例:

create table meiying_rate as \
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b \
on a.pkg_name=b.packageid \
where b.day=20151101 and a.rank<=100000 \
order by a.rank;

4、把查询结果放在本地文件中

描述:

insert overwrite local directory ‘路径’ row format delimited fields terminated by ‘|’ sql

举例:

-- 第一种方式
insert overwrite local directory '${dir_in}rate_${DATE}/' \
row format delimited fields terminated by '|' 、
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank;
-- 第二种方式
hive>
hive -e 'select * from testdb2.emp' >> ./emp_export.txt

5、把查询结果放在HDFS文件中

insert overwrite directory '${dir_in}rate_${DATE}/' \
row format delimited fields terminated by '|' 、
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank;

6、差集使用左右连接

select from 表a right(left) outer join 表b on 条件 where

create table diff_in_bj_idc_mangguo as SELECT BB.uid FROM (SELECT dw_user_lastlogin.uid FROM report_dw_mdp.dw_user_lastlogin where dw_user_lastlogin.day=20151028 and dw_user_lastlogin.app_id ='X0LuNCmE6t6VuvA1b3EyW4') AA RIGHT OUTER JOIN (SELECT user_bj_idc_20151028.uid FROM yuankai.user_bj_idc_20151028) BB ON (AA.uid= BB.uid) WHERE AA.uid IS NULL;

7、其他

去重:distinct

分组:group by

排序:order by (若有where放在其后)

8、shell 运行hive

hive>hive -e “insert overwrite local directory ‘${dir_in}rate_${DATE}/’ row format delimited fields terminated by ‘|’ select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank”

Hive数据类型

Hive所有数据类型分为两种:

  • 基本类型
  • 复杂类型

1、基本类型

2、复杂类型

存储格式

Hive会为每个创建的数据库在HDFS上创建一个目录,该数据库的表会以子目录形式存储,表中的数据会以表目录下的文件形式存储。对于default数据库,默认的缺省数据库没有自己的目录,default数据库下的表默认存放在/user/hive/warehouse目录下。

1、textfile

textfile为默认格式,存储方式为行存储。数据不做压缩,磁盘开销大,数据解析开销大。

2、SequenceFile

SequenceFile是Hadoop API提供的一种二进制文件支持,其具有使用方便、可分割、可压缩的特点。

SequenceFile支持三种压缩选择:NONE, RECORD, BLOCK。 Record压缩率低,一般建议使用BLOCK压缩。

3、RCFile

一种行列存储相结合的存储方式。

4、ORCFile

数据按照行分块,每个块按照列存储,其中每个块都存储有一个索引。hive给出的新格式,属于RCFILE的升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快 快速列存取。

5、Parquet

Parquet也是一种列式存储,同时具有很好的压缩性能;同时可以减少大量的表扫描和反序列化的时间。

赞(0) 打赏
特别声明:除特殊标注,本站文章均为原创,遵循CC BY-NC 3.0,转载请注明出处。三伏磨 » 10分钟快速熟悉hive操作命令

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏