Hive创建外部表导入不同格式的数据至数据仓库

Hive创建外部表导入不同格式的数据至数据仓库

一、数据仓库的层级结构

原始数据层：ODS（Operational Data Store）

数据明细层：DWD（Data Warehouse Detail）

数据汇总层：DWS（Data Warehouse Summary）

数据集市层：DWM （Data Warehouse Market）

二、外部表的定义（创建外部表）

在Hive中，外部表是一种用于逻辑映射外部数据源的表，无需将数据复制到Hive仓库中。外部表提供了对原始数据的逻辑视图，允许在数据仓库中引用原始数据，而不必在本地存储数据。

创建外部表的过程主要发生在数据仓库的原始数据层（ODS）和数据明细层（DWD）。这两个层级负责采集、存储、清洗、集成和加工原始数据，因此在这两个阶段通常会创建外部表来管理数据。

以下是创建外部表的基本语法：

-- external table
CREATE EXTERNAL TABLE IF NOT EXISTS table_name (
    -- 列定义
    column1 data_type,
    column2 data_type,
    ...
)
-- 指定了使用的序列化/反序列化器 (SerDe), OpenCSVSerde
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
-- 指定 SerDe 的属性
WITH SERDEPROPERTIES (    
    'separatorChar' = ',', -- OpenCSVSerde默认项下同
    'quoteChar' = '"',
    'escapeChar' = '\\'
)
-- 指定了输入数据的格式
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
-- 指定了输出数据的格式
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
-- 可选：表的存储位置
LOCATION '/your/hdfs/path/table_name'
-- 可选：指定分区列（如果表是分区表）
PARTITIONED BY (partition_column1 data_type, partition_column2 data_type, ...)
-- 可选：设置一般表的元数据属性
TBLPROPERTIES (
    'key1' = 'value1',
    'key2' = 'value2',
    ...
);

三、数据装载（导入数据）

1、创建表单，用LOCATION指定数据存储路径

创建并指定数据存储路径

LOCATION 子句用于指定外部表在HDFS中的存储路径。具体语法如下

CREATE EXTERNAL TABLE IF NOT EXISTS table_name (
    -- 列定义
    column1 data_type,
    column2 data_type,
    ...
)
LOCATION '/your/hdfs/path/table_name';

数据存储在HDFS上，外部表(EXTERNAL)不会拥有自己的数据目录，而是引用这个指定的位置。

2、使用LOAD DATA命令可以将本地文件系统或HDFS中的数据加载到Hive表中

基本语法:

LOAD DATA [LOCAL] INPATH 'source_path' [OVERWRITE] INTO TABLE table_name [PARTITION (partition_column=value, ...)];

LOCAL代表本地文件系统，不加代表HDFS文件系统

OVERWRITE代表覆盖已有数据，不加代表在原有数据的基础上追加新的数据

PARTITION用于指定将数据加载到分区表的特定分区中

示例:

如果要从本地文件系统的/local/path/data.txt加载数据到表my_table中

LOAD DATA LOCAL INPATH '/local/path/data.txt' INTO TABLE my_table;

如果要从HDFS的/hdfs/path/data.txt加载数据到表my_table中并覆盖已有数据：

LOAD DATA INPATH '/hdfs/path/data.txt' OVERWRITE INTO TABLE my_table;

如果要将数据加载到分区表partitioned_table的分区(date='2022-01-01')中：

LOAD DATA INPATH '/hdfs/path/partition_data.txt' INTO TABLE partitioned_table PARTITION (date='2022-01-01');

3、使用INSERT INTO TABLE语句从其他表选择插入

基本语法:

1.可以通过执行INSERT INTO TABLE语句从一个表中选择数据插入另一个表

INSERT INTO TABLE target_table [PARTITION (partition_column=value, ...)]
SELECT * FROM source_table [WHERE condition];

示例:

从源表 source_data 中选择所有数据插入到目标表 target_table

INSERT INTO TABLE target_table 
SELECT * FROM source_data;

如果目标表是分区表，将数据插入到分区 (date=‘2022-01-01’) 中

INSERT INTO TABLE partitioned_table PARTITION (date='2022-01-01')
SELECT * FROM source_data WHERE date_column = '2022-01-01';

4、通过外部工具或脚本：

使用外部工具（如Sqoop）或脚本（如Shell脚本）将数据导入到 Hive 表中。

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

指定序列化/反序列化器的属性：附带的是默认项

1、行格式为分隔符格式

ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','  -- 指定字段分隔符为逗号
LINES TERMINATED BY '\n'  -- 指定行分隔符为换行符

2、处理JSON格式的数据

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
WITH SERDEPROPERTIES (
    'ignore.malformed.json' = 'true',  		--指定是否忽略格式不正确的 JSON 数据。
    'mapping.someProperty' = 'jsonProperty' --指定 JSON 对象属性的映射关系。
)

3、处理文本文件，支持自定义的字段分隔符

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
    'field.delim' = ',',  		  --指定字段分隔符，即文本数据中不同字段之间的分隔符。
    'serialization.format' = ','  --指定序列化格式，即在序列化文本数据时如何表示字段之间的分隔。
)

4、逗号分隔值（CSV）格式的数据

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    'separatorChar'=',', -- CSV文件中的字段分隔符为逗号
    'quoteChar'='"',  	 -- CSV文件中的引号字符为双引号
    'escapeChar'='\\'    -- CSV文件中的转义字符为反斜杠
)

5、正则表达式匹配的数据

ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'input.regex' = '(\\S+) (\\S+) (\\S+)',   --指定输入数据的正则表达式
    'output.format.string' = '%1$s %2$s %3$s' --指定输出数据的格式化字符串
)

6、Thrift格式的数据

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.thrift.ThriftSerDe'
WITH SERDEPROPERTIES (
    'thrift.protocol.id' = '1',   		--指定 Thrift 协议的ID，设置为 '1'，表示使用的是二进制协议。
    'thrift.column.info' = 'columnInfo' --指定 Thrift 对象的列信息
)

7、ORC（Optimized Row Columnar）格式的数据

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'

8、Parquet格式的数据

ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'

9、Avro格式的数据

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'

五、设置一般的表信息

TBLPROPERTIES 中的属性键值对表示表的元数据属性，用于设置一般的表信息。

TBLPROPERTIES (
    'external' = 'true',                    -- 表示这是一个外部表
    'stored.as.subdirectories' = 'true',    -- 存储为子目录
    'skip.header.line.count'='1'			-- 跳过 CSV 文件中的第一行（表头）
    'external.table.purge' = 'false',       -- 当删除外部表时是否删除底层数据，默认为false
    'storage_handler' = 'org.apache.hadoop.hive.ql.storage.DynamoDBStorageHandler', -- 如果使用外部存储处理程序，可以指定
    'orc.compress' = 'SNAPPY',              -- ORC 文件压缩格式为 SNAPPY
    'parquet.compress' = 'GZIP',            -- Parquet 文件压缩格式为 GZIP
    'bucketing_version' = '2',             	-- 分桶版本
    'numBuckets' = '4',                     -- 分桶数
    'SORTBUCKETCOLSPREFIX' = 'col1',        -- 排序列1
    'SORTBUCKETCOLSPREFIX' = 'col2',        -- 排序列2
    'hive.jdbc.write.connection' = 'jdbc:mysql://localhost:3306/dbname',  -- 连接信息
    'key1' = 'value1',                      -- 自定义键值对1
    'key2' = 'value2'                       -- 自定义键值对2
);

六、示例

且看下回分析