本文详细介绍了Hive的文件格式、建表与插入数据、查询优化、窗口函数和数据处理等基础语法。包括如何创建表存储为textfile、Python读写Hive ORC文件、使用"local fetch"查询模式、建表与插入数据的多种方法、窗口函数的应用以及数据去重等操作。此外，还探讨了Hive中不同连接类型、子查询、数据排序与分桶等高级语法。
hive查询和存储模式
文件格式
hive表存储格式
Parquet: 最常用列式存储格式， 压缩比高，查询速率快，支持多种嵌套类型，推荐使用。
Text: 可读存储格式，内容中包含换行符等特殊符号时数据会失真，不推荐使用。
ORC: 一种列式存储格式，支持压缩，查询速率比Parquet高，但支持数据类型少，使用较少。
创建表存储为textfile（而不是orc）
set hive.exec.compress.output=false; --禁用压缩
create temporary table tmp_table_name STORED AS textfile as
select * from ***;
Note: 当禁用输出压缩时，输出数据的大小会增加（比如文本类可能增加3倍多），这可能会导致后表需要更多的Map任务来处理tmp_table_name中的这些数据。但禁用输出压缩也可以提高查询的执行速度，特别是在数据已经被压缩或者查询结果本身不适合进行压缩的情况下。禁用输出压缩还可以减少CPU使用，因为不再需要进行压缩和解压缩的操作。[chatgpt]
python读写并转换hive orc文件
在hdfs中将hive orc文件转成文本文件用于训练
关于hive raid编码
hadoop存储时，用多个（eg. 3个）副本存储。raid编码采用raid冗余编码来完成纠错，可能在hdfs上使用1.5个副本存储，节省一半存储。但是纠错时需要解码，所以raId只适合冷数据，一般设置3天前的数据使用raid编码。
"local fetch"查询模式
一般情况下，Hive查询的结果集较大时，结果集会被存储在计算节点中，然后客户端通过网络从计算节点获取结果集。但在"local fetch"模式下，Hive引擎会直接从计算节点将结果集传输到客户端，减少了通过网络传输的时间，提高了查询的效率。
"local fetch"模式通常在查询结果集较小时适用，因为如果结果集非常大，将结果集传输到客户端可能会导致客户端的资源不足。因此，当查询结果集较小时，Hive选择采用"local fetch"模式，提高查询性能和响应速度。
可通过参数 set hive.fetch.task.conversion=none; 关闭local fetch模式
建表和插入表
表的定义DDL
DROP TABLE IF EXISTS table_name ;
CREATE TABLE IF NOT EXISTS table_name
(
id BIGINT -- COMMENT 'id'
,gender STRING -- COMMENT '性别'
)
COMMENT 'table name'
PARTITIONED BY
(
ds string COMMENT 'yyyymmdd'
,action_id string COMMENT '行为类型，0为浏览，1为购买，2为收藏，3为加购'
)
LIFECYCLE 600;
完整的建表语句可能是这样的：
CREATE TABLE **.table_name(
`id` bigint COMMENT 'ID')
COMMENT '***'
PARTITIONED BY (
`pt` string COMMENT '分区日期')
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'
LOCATION
'hdfs://***/hive/warehouse/**.db/table_name'
TBLPROPERTIES (
'last_modified_by'='**',
'last_modified_time'='123456',
'tbl_uuid'='123456',
'transient_lastDdlTime'='123456')
表加时间后缀 table_name_${env.YYYYMMDD8}
表的创建
不定义，直接继承创建
CREATE TABLE IF NOT EXISTS table_name LIFECYCLE 2 AS;
快速创建有数据的临时表
方式1：
select explode(ARRAY ('春装', '长裙', '小包')) str1;
方式2：
SELECT * FROM VALUES VALUES (1, 'one'), (2, 'two'), (3, 'three') t(column1, column2);
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
将返回一个两列三行的表。它实际上等效于
方式3：
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
Note: UNION ALL不保证合并后顺序，可能反向。
[PostgreSQL 中文文档 - 7.7. VALUES 个列表 | Docs4dev]
从hdfs中构建临时表
CREATE TEMPORARY external TABLE tmp_base(
id STRING,
label BIGINT,
score DOUBLE
) row format delimited fields terminated by ',' STORED AS TEXTFILE LOCATION
'hdfs://.../predict/ctr/2022-02-05/';
表的插入
INSERT INTO table_name VALUES(7,'003'),(8,'004'),(9,'005');
INSERT INTO table_name PARTITION(ds='${bizdate}', action_id='0')
SELECT t0.id AS FROM table_name;
INSERT OVERWRITE TABLE table_name PARTITION(ds='${bizdate}', action_id='0')
SELECT t0.id AS FROM table_name;
INSERT OVERWRITE TABLE table_name PARTITION(ds='${bizdate}', action_id='0')
SELECT t0.id AS FROM table_name;
INSERT OVERWRITE TABLE tpp_pv_log partition(pt="${bizdate}",appid)
select *,appid(相对于ddl多出一个) from a;
数据插入表
INSERT OVERWRITE TABLE ads_model_alibeeshop_itemcmp_item_sku_pairs_nd
PARTITION(ds='${bizdate}')
SELECT seller_id
,item_sku_pairs
FROM ads_model_alibeeshop_itemcmp_item_sku_pairs_nd
WHERE ds = '${bizdate}'
AND seller_id <> '2248206888'
UNION ALL
SELECT *
FROM VALUES ( '2248206888' ,'{"item_cmp_pair": [{"item_list": "631602364150@629787026510", "freq": 18}, {"item_list": "626086284001@631602364150", "freq": 1}], "sku_item": [{"item_id": "626086284001", "sku_compare": [{"value_list": "3232478@132476@28332", "freq": 1, "propertyId": "1627207"}, {"value_list": "3232478@132476", "freq": 1, "propertyId": "1627207"}, {"value_list": "3267942@3267944", "freq": 1, "propertyId": "20509"}]}, {"item_id": "631602364150", "sku_compare": [{"value_list": "4266701@28332", "freq": 1, "propertyId": "1627207"}]}]}' ) t(seller_id ,item_sku_pairs)
;
表的修改
ALTER TABLE table_name DROP IF EXISTS PARTITION(ds='20180918');
--删除和添加分区，需要一个个删除分区，分区全删了，表定义还在
ALTER TABLE table_name DROP IF EXISTS PARTITION(ds='20180918') purge;
alter table ${user_name}.predict_scores ADD IF NOT EXISTS PARTITION(version =\"${exp_name}\");
--删除分区，不添加到回收站
TRUNCATE TABLE table_name;
--删除非分区表，表定义不删除？
ALTER TABLE table_name SET LIFECYCLE 8 ;
ALTER TABLE table_name CHANGE COLUMN aaa RENAME TO bbb ;
ALTER TABLE table_name CHANGE COLUMN column_name COMMENT 'comment';
--加字段
ALTER TABLE **.table_name ADD COLUMNs(col_name STRING COMMENT 'col_name', ...);
-- 修改字段名, 字段类型，字段备注
ALTER TABLE 数据库名.表名 CHANGE COLUMN 字段名 新的字段名(如果不变就保持原字段) 字段类型(若不变就采用原来的字段) COMMENT '新的字段备注';
不能改可以先drop表再重建。
查询查看表信息
--project及表授权查看
show grants;
SHOW partitions table_name; --查看分区
READ seller_feature_table partition (ds='${bizdate}') 20; --查看数据
Hive 表导出
导出包含标题的 CSV 文件
hive -e "set hive.cli.print.header=true;set hive.resultset.use.unique.column.names=false; select * from hive_table" > hive_table.csv;
hive -e "select * from **.hive_table where pt = '2023-08-05'" > hive_table.csv;
其中set hive.cli.print.header=true;表示带表头，即将列名作为输出的第一行。但是可能列名包含了“表名.”，输出看起来像 Mytable.col1, Mytable.col2 ...。
如果不希望表名包含列名，可使用 set hive.resultset.use.unique.column.names=false;。输出的第一行将类似于 col1, col2 ...
Note: 执行hive -e
出错：Unable to determine Hadoop version information. 'hadoop version' returned:
继而执行hadoop version
出错：/opt/client/hadoop/bin/hadoop: line 182: /usr/lib/jvm/java-8-openjdk-amd64/bin/java: No such file or directory
看上去是java路径问题
继而执行java -version不存在java
解决：$ sudo apt-get install openjdk-8-jre openjdk-8-jdk
文件下载后直接查看需要分割
基本语法
在Hive中，AND运算符的优先级高于OR运算符。 这意味着在SQL查询中，AND运算符比OR运算符更先计算。
with as子查询语句
某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL~
with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名。
WITH t1 AS (
SELECT *
FROM abc
),
t2 AS (
SELECT *
FROM t1
)
SELECT *
FROM t1, t2
Note:
同级with关键字只能使用一次，多个只能用逗号分割；
最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割，with 子句的查询必须用括号括起来。
如果定义了with子句，但其后没有跟select查询(前面可有insert语句)，则会报错！（要整体作为一条sql查询）；
with子句后面貌似只能接一个select语句，否则第2个select语句无语法错误也会提示有！
所以还不如创建一张表！
几种连接：内连接、左联接、右连接、全连接、交叉连接
内连接 join = inner join
左连接 left join
全连接 full join = full outer join
[SQL的几种连接：内连接、左联接、右连接、全连接、交叉连接]
left join (on 和 where条件放置的区别)
1. 对于
最低0.47元/天 解锁文章
扫一扫
2513
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
