Hive加載數(shù)據(jù)與數(shù)據(jù)null值處理
Hive加載數(shù)據(jù)與數(shù)據(jù)null值處理
背景：load數(shù)據(jù)文件的數(shù)據(jù)流轉(zhuǎn)的雛形通常是：
業(yè)務(wù)庫(kù) -> 數(shù)據(jù)文件 -> load進(jìn)hive -> ods層
這里會(huì)面臨最基礎(chǔ)的兩個(gè)問(wèn)題：
通常我們需要一個(gè)跳板層，即將數(shù)據(jù)文件Load進(jìn)stage層(text)，然后通過(guò)查詢加載進(jìn)ODS層(ORC)；
數(shù)據(jù)文件生成時(shí)，如果直接使用命令行的形式，字段null值將被直接賦值為'NULL'字符串；
RC File 和 ORC File 的區(qū)別
RC File（Record Columnar File）和ORC File（Optimized Row Columnar File）都是Hive中的列式存儲(chǔ)格式，它們都旨在提高查詢性能和降低存儲(chǔ)成本，但有一些區(qū)別：
1.?存儲(chǔ)方式不同：
RC File是將每個(gè)記錄作為一個(gè)行存儲(chǔ)，但每列都單獨(dú)存儲(chǔ)，因此數(shù)據(jù)被列分隔。列的數(shù)據(jù)被壓縮在每個(gè)數(shù)據(jù)塊內(nèi)，可以根據(jù)需要使用Zlib、Snappy或LZO等算法進(jìn)行壓縮。
ORC File是將數(shù)據(jù)按行組織并存儲(chǔ)在行組中，每個(gè)行組可以包含數(shù)千行記錄，而每列單獨(dú)存儲(chǔ)，列的數(shù)據(jù)按列分隔存儲(chǔ)。列數(shù)據(jù)被壓縮在行組內(nèi)，可以使用Snappy、Zlib、LZO、LZ4等算法進(jìn)行壓縮。
2.?壓縮率和壓縮效率不同：
ORC比RC更優(yōu)秀的原因之一是，它可以基于數(shù)據(jù)類型和壓縮算法選擇更適合的壓縮算法，以實(shí)現(xiàn)更好的壓縮率和壓縮效率。ORC壓縮方式可以在每列和每行組上分別選擇，根據(jù)實(shí)際數(shù)據(jù)的分布情況來(lái)選擇更好的壓縮算法。
3.?某些情況下ORC File查詢效率更高，如以下情況：
數(shù)據(jù)壓縮：由于ORC File支持列存儲(chǔ)和列壓縮，可以在讀取數(shù)據(jù)時(shí)減少磁盤I/O和網(wǎng)絡(luò)帶寬占用，因此在數(shù)據(jù)量較大或者需要跨網(wǎng)絡(luò)傳輸時(shí)，ORC File的查詢速度更快。
列過(guò)濾：ORC File支持基于列的過(guò)濾，即在讀取數(shù)據(jù)時(shí)，可以只讀取查詢中涉及的列，而無(wú)需讀取所有的列數(shù)據(jù)。這可以進(jìn)一步提高查詢性能，特別是當(dāng)數(shù)據(jù)包含多個(gè)列或者某些列數(shù)據(jù)比其他列數(shù)據(jù)更大時(shí)。
列式存儲(chǔ)：ORC File支持列存儲(chǔ)，將相同的數(shù)據(jù)類型的數(shù)據(jù)放在一起存儲(chǔ)，使得在查詢中需要的列能夠快速訪問(wèn)，而無(wú)需掃描整個(gè)行。
但是在某些情況下，RC File的查詢效率又可能比ORC File更高。例如，如果數(shù)據(jù)量較小或者需要頻繁地更新或刪除數(shù)據(jù)，那么RC File可能比ORC File更適合。在選擇文件格式時(shí)，需要根據(jù)實(shí)際情況和使用場(chǎng)景進(jìn)行綜合考慮。
4.?適用場(chǎng)景不同：
RC文件通常適用于OLTP（聯(lián)機(jī)事務(wù)處理）類型的工作負(fù)載，因?yàn)樗鼈兺ǔＰ枰獔?zhí)行較少的全表掃描，并且需要高度壓縮，以減少I/O和網(wǎng)絡(luò)負(fù)載。
ORC文件通常適用于OLAP（聯(lián)機(jī)分析處理）類型的工作負(fù)載，因?yàn)樗鼈兺ǔＰ枰獔?zhí)行大量的全表掃描和分析查詢，因此需要更高的查詢性能和更高的壓縮比率，以便更快地檢索和分析大型數(shù)據(jù)集。
RC File的建表語(yǔ)句示例
CREATE TABLE hive_table_rc
(
? id int comment '編號(hào)',
? name string comment '名稱',
? money decimal(10, 2) comment '金錢'
) comment 'hive中rc格式的建表語(yǔ)句'
? STORED AS RCFILE;
ORC File的建表語(yǔ)句示例
CREATE TABLE hive_table_orc
(
? id int comment '編號(hào)',
? name string comment '名稱',
? money decimal(10, 2) comment '金錢'
) comment 'hive中orc格式的建表語(yǔ)句'
? STORED AS ORC;
可以創(chuàng)建發(fā)現(xiàn)不同格式的表只是修改指定類型即可。在實(shí)際使用中，我們通常會(huì)在創(chuàng)建表時(shí)指定該表更多的屬性，這可以解決如上述提到的null值問(wèn)題，會(huì)在下文中提到。
關(guān)于兩個(gè)基礎(chǔ)問(wèn)題
使用命令，將數(shù)據(jù)從業(yè)務(wù)庫(kù)寫出到數(shù)據(jù)文件
首先，我們使用cmd的方式將數(shù)據(jù)寫入到數(shù)據(jù)文件
mysql -h##hostname or IP address## -P##port## -u##username## -p##password## "##select * from table_test##" > ##table_test##
請(qǐng)注意，兩個(gè)##之間是要替換的內(nèi)容，如##port## -> 3306
mysql -h##hostname or IP address## -P##port## -u##username## -p##password## "##select * from table_test##" > ##table_test##
查看這個(gè)文件，會(huì)發(fā)現(xiàn)如果某個(gè)字段為null，該列將被寫為'NULL'字符串
cat ##table_test##
此時(shí)有兩種辦法解決，第一種是使用sed命令，直接將所有的NULL替換掉，如下
sed -i 's/NULL//g' ##table_test##
但這種方式容易改變到其他字符串中真的攜帶了NULL的內(nèi)容，所以更推薦第二種方式，如下
mysql -h##hostname or IP address## -P##port## -u##username## -p##password## "##select ifnull(name, '') from table_test##" > ##table_test##
這種方式將null轉(zhuǎn)為''空字符串，這期間還可以加入replace函數(shù)，替換CHAR(10)和CHAR(13)，避免換行符引起的數(shù)據(jù)錯(cuò)位。
將文件加載到HIVE
上述第3步中，我們已經(jīng)將null轉(zhuǎn)為''字符串，那么在創(chuàng)建HIVE表時(shí)(也可以修改已創(chuàng)建過(guò)的表)，我們需要一些額外的配置，使''默認(rèn)為null，我們的建表語(yǔ)句如下
CREATE TABLE my_table
(
? ?id ? ?int comment '編號(hào)',
? ?name ?string comment '名稱',
? ?money decimal(10, 2) comment '金錢'
)
? ?ROW FORMAT DELIMITED
? ? ? ?FIELDS TERMINATED BY ','
? ? ? ?LINES TERMINATED BY '\n'
? ? ? ?NULL DEFINED AS ''
? ?STORED ?AS TEXTFILE
? ?TBLPROPERTIES ("serialization.null.format"='');
NULL DEFINED AS ''將空字符串視為NULL值，'serialization.null.format'=''則用于指定NULL值在數(shù)據(jù)文件中的表示方式。通過(guò)這種方式，我們可以同時(shí)使用NULL DEFINED AS ''和serialization.null.format=''來(lái)將NULL值和空字符串表示在Hive中的一致性。
也可以修改已存在的表，如下
alter table my_table set serdeproperties('serialization.null.format' = '');
使用load語(yǔ)句，將數(shù)據(jù)加載進(jìn)hive表中
-- 注意，使用了overwrite時(shí)是覆蓋加載數(shù)據(jù)
LOAD DATA INPATH 'table_test' OVERWRITE INTO TABLE my_table;
