您的位置: 首页  >  文章  >  HiveSQL基本操作--实战篇

HiveSQL基本操作--实战篇

分类: 文章 • 2024-12-28 17:05:22

HiveSQL基本操作

HiveSQL基本操作--实战篇

HIve

目录

  • 一、Hive数据类型
  • 1、基本类型
  • 2、复杂类型
  • 二、存储格式
  • 1、textfile
  • 2、SequenceFile
  • 3、RCFile
  • 4、ORCFile
  • 5、Parquet
  • 三、操作数据库
  • 1、创建数据库
  • 2、删除数据库
  • 3、进入数据库
  • 四、操作表属性
  • 1、显示数据库下面所有表
  • 2、删除表
  • 3、显示建表结果
  • 4、显示表分区
  • 5、修改表属性
  • 6、表重命名
  • 7、新增列
  • 8、删除列
  • 9、修改列
  • 10、删除分区
  • 11、增加分区
  • 12、修改分区、LOCATION
  • 四、创建表
  • 五、LOAD DATA语句
  • 六、hive内置运算符
  • 1、算术运算符
  • 2、逻辑运算符
  • 3、复杂运算符
  • 七、hive视图与索引
  • 八、实例
  • 1、把本地文件放在新建表中
  • 2、把hdfs文件放在新建表中
  • 3、把查询结果放在一个新建表中
  • 4、把查询结果放在本地文件中
  • 5、把查询结果放在HDFS文件中
  • 6、差集使用左右连接
  • 7、其他
  • 8、shell 运行hive
  • 九、注意

一、Hive数据类型

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

  • 基本类型
  • 复杂类型

1、基本类型

HiveSQL基本操作--实战篇

 

2、复杂类型

HiveSQL基本操作--实战篇

 

二、存储格式

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也是一种列式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。

三、操作数据库

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;

四、操作表属性

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"

九、注意

上述实例，创建表时，使用外部表，否则删除表时，会将原始数据删掉；

若有收获，欢迎关注转发。

相关推荐