hive复习草稿2，待整理

hive创建表单，装载数据，近源层数据装载，建表模板

分区，分桶表，序列化/反序列化器

hive创建外部表将不同格式的数据导入数仓

近源层

存储来自源系统的原始文本文件数据

这一层包含从源系统中提取的未经处理的原始数据。数据在这一层保持尽可能接近源系统的原始格式，通常不进行结构化或清洗。这一层的目标是捕获源系统中的所有信息，以备后续的处理和分析。

序列化/反序列化器(SerDe)

外部表external

以日期进行分区partitioned

数据是以,切分的 row format

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建外部(external)表 hive_
create external table if not exists hive_(
  	
)
-- 指定行格式为CSV格式的序列化/反序列化器
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
-- 指定CSV文件的序列化/反序列化器属性
with serdeproperties(
    'separatorChar'=',', -- CSV文件中的字段分隔符为逗号
    'quoteChar'='"',  	 -- CSV文件中的引号字符为双引号
    'escapeChar'='\\'    -- CSV文件中的转义字符为反斜杠
)
stored as textfile -- 指定存储格式为文本文件
location '/hive_data/hive_cha01/emp_base' -- 指定外部表的存储位置
tblproperties('skip.header.line.count'='1'); -- 指定表属性，跳过CSV文件中的第一行（表头）
1
2
3
4
5
6
7
-- 指定行格式为JSON格式的序列化/反序列化器
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
-- 指定存储格式为文本文件
stored as textfile
-- 指定外部表的存储位置
location '/hive_data/hive_cha01/json';

1
2
3
4
5
6
7
8
9
10
11
-- 指定行格式为正则表达式格式的序列化/反序列化器
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
-- 指定序列化/反序列化器的属性，包括输入正则表达式
with serdeproperties (
    'input.regex'='(\\d+);(.*?);(\\d{4}-\\d{1,2}-\\d{1,2} \\d{1,2}:\\d{1,2}:\\d{1,2});(\\d+\.?\\d+?)'
)
-- 指定存储格式为文本文件
stored as textfile
-- 指定外部表的存储位置
location '/hive_data/hive_cha01/regex';

1
2
3
4
5
6
7
8
9
10
-- 指定行格式为分隔符格式的序列化/反序列化器
row format delimited
fields terminated by '|'    		-- 指定字段分隔符为竖线字符 '|'
collection items terminated by ',' 	-- 指定集合项分隔符为逗号 ','
map keys terminated by ':' 			-- 指定映射键分隔符为冒号 ':'

stored as textfile  -- 指定存储格式为文本文件
-- 指定外部表的存储位置
location '/hive_data/hive_cha01/employee';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 创建分桶表，并按 id 列进行分桶，共分成 4 个桶
create table if not exists bucket_employee (
    -- 列定义
    id int,
    -- 其他列...
)
clustered by (id) into 4 buckets;

-- 指定行格式为分隔符格式的序列化/反序列化器
row format delimited
-- 指定字段分隔符为竖线字符 '|'
fields terminated by '|'
-- 指定集合项分隔符为逗号 ','
collection items terminated by ','
-- 指定映射键分隔符为冒号 ':'
map keys terminated by ':'
-- 指定存储格式为文本文件
stored as textfile;

-- 将本地路径下的 'employee_id.txt' 数据加载到表 'bucket_employee' 中
load data local inpath '/root/data/employee_id.txt' into table bucket_employee;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
JsonSerDe:

序列化器：org.apache.hive.hcatalog.data.JsonSerDe
用于处理JSON格式的数据。
OpenCSVSerde:

序列化器：org.apache.hadoop.hive.serde2.OpenCSVSerde
用于处理逗号分隔值（CSV）格式的数据。
LazySimpleSerDe:

序列化器：org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
用于处理文本文件，支持自定义的字段分隔符。
OrcSerde:

序列化器：org.apache.hadoop.hive.ql.io.orc.OrcSerde
用于处理ORC（Optimized Row Columnar）格式的数据。
ParquetHiveSerDe:

序列化器：org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
用于处理Parquet格式的数据。
AvroSerDe:

序列化器：org.apache.hadoop.hive.serde2.avro.AvroSerDe
用于处理Avro格式的数据。
RegexSerDe:

序列化器：org.apache.hadoop.hive.serde2.RegexSerDe
用于处理正则表达式匹配的数据。
ThriftDeserializer:

序列化器：org.apache.hadoop.hive.serde2.thrift.ThriftDeserializer
用于处理Thrift格式的数据。
1
在 Hive 中，tblproperties 语法用于为表指定一些属性，以便定制其行为。除了 'skip.header.line.count'='1' 这个例子之外，以下是一些常见的 tblproperties 语法和示例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
设置表的存储格式为文本文件:
tblproperties('external'='true', 'stored.as.subdirectories'='true');
指定表的压缩格式:
tblproperties('orc.compress'='SNAPPY');
指定表的文件格式:
tblproperties('parquet.compress'='GZIP');

设置表的分桶数:
tblproperties('bucketing_version'='2', 'numBuckets'='4');

指定表的排序列:
tblproperties('SORTBUCKETCOLSPREFIX'='col1', 'SORTBUCKETCOLSPREFIX'='col2');

指定表的连接信息:
tblproperties('hive.jdbc.write.connection'='jdbc:mysql://localhost:3306/dbname');

1
2
3
partitioned by (data_time string)
row format delimited fields terminated by ','
STORED AS parquet

ODS层主要存储原始数据（一般都是来源于各个app的日志或者线下门店的表格，txt文本等）

1
2
3
4
5
6
7
8
9
10
11
create table yb12211_1.hive_zipper_pc_order(
       order_id bigint,
       user_id bigint,
       order_create_dt timestamp,
       order_modify_dt timestamp,
       order_money decimal(10,2),
       current_status int
) partitioned by(year int,month int,day int)
    clustered by(order_create_dt) into 4 buckets
    row format delimited fields terminated by ','
    stored as orc tblproperties("transactional"="true");
1
2
3
4
5
6
7
8
9
10
create table if not exists stu_score(
          score_id int,
          stu_name string,
          stu_subject string,
          score int
)
row format delimited fields terminated by ',';  -- 指定了字段之间的分隔符是逗号

load data local inpath '/root/data/data.txt'
overwrite into table stu_score;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])] //列名
  [COMMENT table_comment]   //列注释
  
  //根据列名分区
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  
  
  //根据列名分桶
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]   
  
  //创建倾斜列，通过指定经常出现的值（严重倾斜），hive将会在元数据中记录这些倾斜的列名和值，在join时能够进行优化
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]  
  
   //倾斜列名称和值
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)    
     [STORED AS DIRECTORIES]
  [

   [ROW FORMAT row_format]    //行格式
   [STORED AS file_format]    //表数据的存储格式
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]        //存储的HDFS路径
  
  //实际上就是table properties,TBLPROPERTIES允许开发者定义一些自己的键值对信息。可以对TBLPROPERTIES进行查看和修改（部分可修改）
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  
  //用于创建普通表或临时表，并物化select的结果(慎用)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
  [AS select_statement];
1
2
3
4
5
6
7
8
9
10
11
（1） CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常，可使用 IF NOT EXISTS 选项来忽略这个异常;

（2） TEMPORARY 表示是临时表，在当前会话内，这张表有效，当会话结束，则这张表失效。EXTERNAL 表示是外部表，在建表的同时指定一个指向实际数据的路径。删除的表的时候，只会删除元数据，不会删除表数据;

（3）PARTITIONED BY 表示按什么字段来分区;

（4）CLUSTERED BY 表示分桶表，按什么字段分区和排序。INTO 表示根据这个字段分多少个桶。（分区表和分桶表，后续会分专题讲）;

（5）SKEWED BY 表示指定某些列上有倾斜值，Hive 会记录下这些值，在查询的时候，会有更好的性能表现;

（6）STORED AS 表示以什么压缩格式来存储
1
2
3
4
5
6
7
8
创建外部表
其中 row format delimited 表示定义格式

fields terminated by ','   表示字段按 ',' 来分割

LINES TERMINATED BY '\n'  行按回车符来分割，默认，一般不写

location '/user/hdfs/source/hive_test'    表示这个外部表的数据时放在这个目录下面
1
2
3
4
创建分区表
使用 partitioned by (dt string） 来表示定义分区字段

stored as 表示以 textfile 来存储
1
创建外部分区表，一般用于日志的存储
1
2
创建桶表
这里我们创建了一个页面浏览表，以 userid 的值分成32个桶，插入数据的时候，会把 userid 取 hash，并对32取模，放到32个桶里面去。
1
2
create table as 语法
表示以目标的查询结果来创建表
1
2
create table like 语法
表示以 like 后面的表来创建表结构，不写数据进去
1
2
定义数据倾斜字段和值
使用 SKEWED BY 语法来指定表中某些字段的倾斜值，以提高表的查询性能
1
2
创建临时表
临时表，表示在当前用户会话内才有效，数据全都存在用户临时目录中，一旦退出对话，表和数据都会被清除掉
1
2
使用指定的序列化反序列类来读取行数据
下面的例子，我们使用正则表达式，来读取apache的日志。并且定义了要使用的正则
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE apachelog (
  host STRING,
  identity STRING,
  user STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^]*) ([^]*) ([^]*) (-|\\[^\\]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\".*\") ([^ \"]*|\".*\"))?"
)
STORED AS TEXTFILE;
大数据 > hive
#bigData #hive #sql
hive复习草稿2，待整理
https://leaf-domain.gitee.io/2024/01/25/hiveTest/
作者
叶域
发布于
2024年1月25日
许可协议