hive操作大全
2020-01-09 本文已影响0人
云彩修建站站长
1. sql语句
1.1 创建表语句说明
有三种创建表的方法：
1.1.1 最常用创建方式
CREATE TABLE `hdp_mis_dd_defaultdb.a_realtime_call_detail35`(
`rt_call_id` string COMMENT '业务id',
`rt_opp_id` string COMMENT '实时话务，商机id',
`rt_system_type` string COMMENT '用于区分实时话务的系统类型，JY-代表吉亚话务系统，HL-代表合力话务系统')
PARTITIONED BY (
`partition_dt` string COMMENT 'yyyyMMdd,分区存储全量数据')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\u0001 '
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://hdp-58-cluster/home/hdp_mis_dd/warehouse/hdp_mis_dd_defaultdb/a_realtime_call_detail35'
TBLPROPERTIES (
'transient_lastDdlTime'='1512466921')
其中
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
和STORED AS TEXTFILE一致，或者直接省略，默认也是TEXTFILE。
一共有四种存储格式：
- TEXTFILE
- SEQUENCEFILE
- RCFILE
- ORCFILE(0.11以后出现)
1.1.2 其他创建方式
- create table x as select * from ..
- create table x like table_a
1.2 从外部导入数据
1.2.1 外部表 & 内部表
- 外部表是由HDFS管理（Location由自己制定），内部表是由Hive自身管理
- 删除内部表会直接删除元数据及存储数据；删除外部表只是删除了元数据，hdfs上的数据依然存在。
如果不需要分开管理的话，没有必要使用外部表。
1.2.2 从本地导入数据
load data local inpath 'abs_path' into table {table_name};
1.2.3 从hdfs导入数据
load data inpath 'data/load_data_hdfs.txt' into table load_data_hdfs;
1.2.4 从其他表中导入数据
如果是想动态分区，需要加上下面的代码：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
1.3 导出数据
1.3.1 导出到本地文件系统
方案1：
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/output' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from testA;
方案2：
hive -e > target.file 后面跟语句
hive -f > target.file 后面跟sql文件
1.3.2 导出到HDFS
和1.3.1相比，去掉了LOCAL
INSERT OVERWRITE DIRECTORY '/home/hadoop/output' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from testA;
1.4 alter修改
1.4.1 添加字段
alter table hdp_teu_dia_guessulike.ai_voice_check_voice_tab add columns(agent_no varchar (64));
1.4.2 修改字段
alter table detail_flow_conversion_base_raw change column original_union_id original_union_id string COMMENT'原始设备唯一性标识’;
1.4.3 删除，增加分区
alter table table1 add partition(dt = '2019-02-12',du='1');
alter table table1 drop partition(dt = '2019-02-12');
2. 数据类型 和 内置函数
2.1 数据类型
## 整型：
tinyint — 微整型，只占用1个字节，只能存储0-255的整数。
smallint– 小整型，占用2个字节，存储范围–32768 到 32767。
int– 整型，占用4个字节，存储范围-2147483648到2147483647。
bigint– 长整型，占用8个字节，存储范围-2^63到2^63-1。
## 字符：
string 变长字符串
varchar 变长字符串，和string的区别是string是没有最大长度限制的，而varchar有。
## 日期
timestamp 时间戳，纳秒精度
date 日期，对应年月日，例如"2016-03-29"
2.2 内置函数
2.2.1 日期函数
日期相减：
两个日期之间的差值：datediff(‘日期1’,’日期2’)
获取“日期1”和“日期2”之间的差值，日期的格式必须是：‘yyyy-MM-dd HH:mm:ss’或者‘yyyy-MM-dd’
增加，减少几天：日期的格式都是 YYYY-mm-dd
-
date_add and date_sub
image.png
2.2.2 字符函数
- low() 转小写
- length() 字符串长度
- trim() 前后去空格
- substr(a, b, c) 从字符串a中，第b为开始取，取c个字符，如果c不设置的话，则取b向右的所有字符。这里下标是从1，而不是0开始的。
image.png
2.2.3 条件函数
case...when... 条件表达式
image.png
3. 进阶
3.1 view表
1、视图是一个虚表，一个逻辑概念，可以跨越多张表。表是物理概念，数据放在表中，视图是虚表，操作视图和操作表是一样的，所谓虚，是指视图下不存数据。
2、视图是建立在已有表的基础上，视图赖以建立的这些表称为基表
3、视图可以简化复杂的查询
个人感觉，复杂地查询可以使用view标，复杂的sql简化成简单的sql。这种查询频率应该比较高才能用得到。
错误记录：
- 查询条件没有设置partition_dt而报错。
如果设置了set hive.mapred.mode=strict，那么必须有where条件，里面要限制partition_dt，不然会报错。
no partition predicate found for alias
