Hive常见表结构

最新推荐文章于 2024-02-19 11:45:21 发布

时下握今

最新推荐文章于 2024-02-19 11:45:21 发布

阅读量697

点赞数

分类专栏：数据存储文章标签：大数据 hive

本文链接：https://blog.csdn.net/weixin_37536020/article/details/114625719

版权

数据存储专栏收录该内容

21 篇文章 2 订阅

订阅专栏

Hive

Hive本质是一种计算引擎，依赖于HDFS存储数据，依赖MR处理数据。同时也依赖Sqoop组件，Sqoop是在Hadoop 和关系数据库服务器之间传送数据的一个工具。用于离线计算的场景：离线ETL

函数

开窗函数

普通聚合函数每组只返回一个值
开窗函数每组返回多个值，因为开窗函数执行聚合计算的行集组是窗口

用户自定义函数

方法一
在这里插入图片描述
2.写好的类打jar包，注册到hive
hive > add jar /目录/xxxx.jar
3. 为jar包中的驱动程序起别名
hive > create temporay function strToUpper ‘类的全路径名’
4. 使用
hive > select strToUpper(“abcdefghijilmnopqrstuvwxyz”)

方法二

val sparkConf = new SparkConf().setAppName("area").setMaster("local[*]")

val sparkSession = SparkSession.builder().config(sparkConf).enableHiveSupport().getOrCreate()

sparkSession.udf.register("concat_long_string", (v1:Long, v2:String, split:String) =>{
      v1 + split + v2
    })

表结构

外部表

先将文件上传HDFS，再通过Hive外部表来访问
表删除后，数据还在HDFS上
CREATE EXTERNAL TABLE access_log( ip STRING,user_id STRING,request STRING,response STRING, status_code INT) LOCATION ‘’

内部表

删除表时会连数据一起删除, LOCATION默认是在HDFS的
/user/hive/warehouse的目录下，为了管理方便内部表创建时不要指定LOCATION（使用默认值）
create table car(car_no int, car_name string)
load data local inpath ‘/本地文件目录/本地文件’ into table car
hive里，表示字符串用的是 string，不用char和varchar
load data local inpath 从本地磁盘加载文件，若不加 local，表示从HDFS加载文件

分区表

把一个目录中一个文件的数据，根据分区字段的值，分到不同的目录去存储。

缩小了硬盘扫描数据的区域
将表数据存储在几个可以独立管理(创建、删除)的子目录中

静态分区

分区字段的值固定

INSERT  OVERWRITE  TABLE  P_TAB
PARTITION(year='2021',month='05',day='26',hour='10')
SELECT ...

动态分区

分区字段的值不确定

INSERT  OVERWRITE  TABLE  P_TAB
PARTITION(year,month,day,hour)
SELECT ...

使用动态分区，首先开启动态分区
SET hive.exec.dynamic.partition.mode=nonstrict;
下面参数可选
SET hive.exec.max.dynamic.partitions=2048; 
SET hive.exec.max.dynamic.partitions.pernode=256; 
SET hive.exec.max.created.files=10000; 
SET hive.error.on.empty.partition=true;

混合分区

INSERT  OVERWRITE  TABLE  P_TAB
PARTITION(year='2021' month,day,hour)
SELECT ...

分区字段是表中不存在的列
SELECT 语句中字段顺序要和分区字段保持一致

分桶表

一个分区目录下一般只有一个文件，
按照这个文件中的某个字段分桶，可以把这个文件中的数据，平均分配到多个文件
分桶字段是INT型，就对字段HASH后取模，按模数分桶
分桶字段是String型，取字段HASH值，按HASH值分桶
版本2.0之前,INSERT 操作使用分桶表，需要开启分桶机制：
set hive.enforce.bucketing=true;
set mapred.reduce.tasks = 200;
可以进一步对分桶表中的数据排序，方便表的JOIN操作

这个是hive2.0及之后版本的SQL语句
CREATE TABLE dept(did int, dname string) 
PARTITION BY(JOIN_DATE String)
CLUSTERED BY  (did) 
SORTED BY (JOIN_TIME) INTO 200  buckets 
ROW FORMATE DELIMITED FIELDS TERMINATED BY '|';

LOAD DATE LOCAL inpath '/usr/soft/apache-hive-1.2.0-bin/examples/files/dept.txt' overwrite  into table dept;

select * from dept tablesample(bucket 1 out of 3 on did)

这个是2.0之前版本的SQL语句
set hive.enforce.bucketing=true;
set mapred.reduce.tasks = 200;
CREATE TABLE dept(did int, dname string) 
PARTITION BY(JOIN_DATE String)
DISTIBUTE BY  did
SORTE BY JOIN_TIME INTO 200  buckets 
ROW FORMATE DELIMITED FIELDS TERMINATED BY '|';

桶数为200
bucket 1 out of 310 on did 表示从第1个桶开始，抽取 200/10=20个桶，所以抽取的样本为桶1、桶20、桶40、桶60…

表的记录行存储格式

ROW FORMAT 有DELIMITED和SERDE两种值
① 一般用于用分隔符分隔的文本文件

CREATE TABLE access_log()
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'

② 一般用于比较复杂格式的文本文件，比如JSON格式行、正则表达式可以匹配出的行,像访问日志

CREATE EXTERNAL TABLE original_access_logs ( ) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' WITH SERDEPROPERTIES ( 'input.regex' = '([^ ]*) - - \\[([^\\]]*)\\] "([^\ ]*) ([^\ ]*) ([^\ ]*)" (\\d*) (\\d*) "([^"]*)" "([^"]*)"', 'output.format.string' = "%1$$s %2$$s %3$$s %4$$s %5$$s %6$$s %7$$s %8$$s %9$$s") 
LOCATION '/user/hive/warehouse/original_access_logs_0508'; -- 
-- SELECT遇到ClassNotFound的错误, 说明需要将contrib包引入，根据自己服务器路径的不同做修改
ADD JAR add jar /opt/cloudera/parcels/CDH/jars/hive-contrib-2.1.1-cdh6.3.2.jar;

访问日志示例
123.65.150.10 - - [23/Aug/2010:03:50:59 +0000] "POST /wordpress3/wp-admin/admin-ajax.php
HTTP/1.1" 200 2 "http://www.example.com/wordpress3/wp-admin/post-new.php" 
"Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_4; en-US) AppleWebKit/534.3 
(KHTML, like Gecko) Chrome/6.0.472.25 Safari/534.3“

表的文件存储格式

STORED AS 指定表的文件存储格式
默认TEXT FILE（文本文件）格式存储
其它常用存储格式 Parquet(列式)，Avro，ORC(列式)，Sequence File，INPUT FORMAT & OUTPUT FORMAT (二进制)

CREATE TABLE tab_dataset ()
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS CSV
;

列式存储

行逻辑上分块，块内按列存储，先存第一列，再存第二列
区别于按行存储