Clickhouse系列之整合Hive數(shù)據(jù)倉庫示例詳解

更新時間：2022年10月13日 14:55:44 作者：阿杰筆記

這篇文章主要為大家介紹了Clickhouse系列之整合Hive數(shù)據(jù)倉庫示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

前言

什么是Hive？ Apache Hive 數(shù)據(jù)倉庫軟件便于使用SQL讀取、寫入和管理駐留在分布式存儲中的大型數(shù)據(jù)集。結構可以投射到已存儲的數(shù)據(jù)上。提供了一個命令行工具和JDBC驅動程序，用于將用戶連接到Hive。

Hive引擎允許您對HDFS配置單元表執(zhí)行SELECT查詢。目前支持如下輸入格式：

文本：僅支持簡單標量列類型，二進制除外；
ORC：支持除char以外的簡單標量列類型；僅支持數(shù)組等復雜類型；
parquet：支持所有簡單的標量列類型；僅支持數(shù)組等復雜類型。

正文

創(chuàng)建Hive引擎表詳細信息以及參數(shù)詳解

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]  
(  
name1 [type1] [ALIAS expr1],  
name2 [type2] [ALIAS expr2],  
...  
) ENGINE = Hive('thrift://host:port', 'database', 'table');  
PARTITION BY expr

表結構可以與原始配置單元表結構不同：

列名應該與原始配置單元表中的列名相同(推薦列名相同處理)，但您可以只使用其中的一些列，并且可以按任何順序使用，也可以使用從其他列計算的一些別名列。
列類型應與原始配置單元表中的列類型相同。
按表達式劃分應該與原始Hive表一致，按表達式劃分中的列應該在表結構中。

引擎參數(shù)：

thrift://host:port-配置單元元存儲地址
database—遠程數(shù)據(jù)庫名稱。
table—遠程表名稱。

實戰(zhàn)案例

為遠程文件系統(tǒng)啟用本地緩存。通過官方的基準測試表明，使用緩存的速度快了近兩倍。在使用緩存之前，將其添加到config.xml

<local_cache_for_remote_fs>
    <enable>true</enable>
    <root_dir>local_cache</root_dir>
    <limit_size>559096952</limit_size>
    <bytes_read_before_flush>1048576</bytes_read_before_flush>
</local_cache_for_remote_fs>

參數(shù)詳解：

enable:ClickHouse將在啟動后維護遠程文件系統(tǒng)（HDFS）的本地緩存（如果為true）。
root_dir：必需。用于存儲遠程文件系統(tǒng)的本地緩存文件的根目錄。
limit_size：必填。本地緩存文件的最大大?。ㄗ止?jié)）。
bytes_read_before_flush：從遠程文件系統(tǒng)下載文件時，在刷新到本地文件系統(tǒng)之前控制字節(jié)數(shù)。默認值為1MB。

盡管ClickHouse在啟用遠程文件系統(tǒng)本地緩存的情況下啟動時，我們仍然可以選擇不使用其查詢中設置為use_local_cache_for_remote_fs=0的緩存。use_local_cache_for_remote_fs默認為false。

ORC數(shù)據(jù)格式

Hive創(chuàng)建ORC數(shù)據(jù)格式表

CREATE TABLE `test`.`test_orc`(  
`f_tinyint` tinyint,  
`f_smallint` smallint,  
`f_int` int,  
`f_integer` int,  
`f_bigint` bigint,  
`f_float` float,  
`f_double` double,  
`f_decimal` decimal(10,0),  
`f_timestamp` timestamp,  
`f_date` date,  
`f_string` string,  
`f_varchar` varchar(100),  
`f_bool` boolean,  
`f_binary` binary,  
`f_array_int` array<int>,  
`f_array_string` array<string>,  
`f_array_float` array<float>,  
`f_array_array_int` array<array<int>>,  
`f_array_array_string` array<array<string>>,  
`f_array_array_float` array<array<float>>)  
PARTITIONED BY (  
`day` string)  
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://testcluster/data/hive/test.db/test_orc'

insert into test.test_orc partition(day='2021-09-18') select 1, 2, 3, 4, 5, 6.11, 7.22, 8.333, current_timestamp(), current_date(), 'hello world', 'hello world', 'hello world', true, 'hello world', array(1, 2, 3), array('hello world', 'hello world'), array(float(1.1), float(1.2)), array(array(1, 2), array(3, 4)), array(array('a', 'b'), array('c', 'd')), array(array(float(1.11), float(2.22)), array(float(3.33), float(4.44)));

Clickhouse創(chuàng)建Hive表引擎

CREATE TABLE test.test_orc
(
    `f_tinyint` Int8,
    `f_smallint` Int16,
    `f_int` Int32,
    `f_integer` Int32,
    `f_bigint` Int64,
    `f_float` Float32,
    `f_double` Float64,
    `f_decimal` Float64,
    `f_timestamp` DateTime,
    `f_date` Date,
    `f_string` String,
    `f_varchar` String,
    `f_bool` Bool,
    `f_binary` String,
    `f_array_int` Array(Int32),
    `f_array_string` Array(String),
    `f_array_float` Array(Float32),
    `f_array_array_int` Array(Array(Int32)),
    `f_array_array_string` Array(Array(String)),
    `f_array_array_float` Array(Array(Float32)),
    `day` String
)
ENGINE = Hive('thrift://202.168.117.26:9083', 'test', 'test_orc')
PARTITION BY day

通過Clickhouse查詢Hive數(shù)據(jù)

SELECT * FROM test.test_orc settings input_format_orc_allow_missing_columns = 1\G

Parquet數(shù)據(jù)格式

Hive創(chuàng)建Parquet數(shù)據(jù)格式表

CREATE TABLE `test`.`test_parquet`(  
`f_tinyint` tinyint,  
`f_smallint` smallint,  
`f_int` int,  
`f_integer` int,  
`f_bigint` bigint,  
`f_float` float,  
`f_double` double,  
`f_decimal` decimal(10,0),  
`f_timestamp` timestamp,  
`f_date` date,  
`f_string` string,  
`f_varchar` varchar(100),  
`f_char` char(100),  
`f_bool` boolean,  
`f_binary` binary,  
`f_array_int` array<int>,  
`f_array_string` array<string>,  
`f_array_float` array<float>,  
`f_array_array_int` array<array<int>>,  
`f_array_array_string` array<array<string>>,  
`f_array_array_float` array<array<float>>)  
PARTITIONED BY (  
`day` string)  
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'  
LOCATION  
'hdfs://testcluster/data/hive/test.db/test_parquet'

insert into test.test_parquet partition(day='2021-09-18') select 1, 2, 3, 4, 5, 6.11, 7.22, 8.333, current_timestamp(), current_date(), 'hello world', 'hello world', 'hello world', true, 'hello world', array(1, 2, 3), array('hello world', 'hello world'), array(float(1.1), float(1.2)), array(array(1, 2), array(3, 4)), array(array('a', 'b'), array('c', 'd')), array(array(float(1.11), float(2.22)), array(float(3.33), float(4.44)));

Clickhouse創(chuàng)建Hive表引擎

CREATE TABLE test.test_parquet  
(  
`f_tinyint` Int8,  
`f_smallint` Int16,  
`f_int` Int32,  
`f_integer` Int32,  
`f_bigint` Int64,  
`f_float` Float32,  
`f_double` Float64,  
`f_decimal` Float64,  
`f_timestamp` DateTime,  
`f_date` Date,  
`f_string` String,  
`f_varchar` String,  
`f_char` String,  
`f_bool` Bool,  
`f_binary` String,  
`f_array_int` Array(Int32),  
`f_array_string` Array(String),  
`f_array_float` Array(Float32),  
`f_array_array_int` Array(Array(Int32)),  
`f_array_array_string` Array(Array(String)),  
`f_array_array_float` Array(Array(Float32)),  
`day` String  
)  
ENGINE = Hive('thrift://localhost:9083', 'test', 'test_parquet')  
PARTITION BY day

通過Clickhouse查詢Hive數(shù)據(jù)

SELECT * FROM test.test_parquet settings input_format_parquet_allow_missing_columns = 1\G

TextFile數(shù)據(jù)格式

Hive創(chuàng)建TextFile數(shù)據(jù)格式表

CREATE TABLE `test`.`test_text`(  
`f_tinyint` tinyint,  
`f_smallint` smallint,  
`f_int` int,  
`f_integer` int,  
`f_bigint` bigint,  
`f_float` float,  
`f_double` double,  
`f_decimal` decimal(10,0),  
`f_timestamp` timestamp,  
`f_date` date,  
`f_string` string,  
`f_varchar` varchar(100),  
`f_char` char(100),  
`f_bool` boolean,  
`f_binary` binary,  
`f_array_int` array<int>,  
`f_array_string` array<string>,  
`f_array_float` array<float>,  
`f_array_array_int` array<array<int>>,  
`f_array_array_string` array<array<string>>,  
`f_array_array_float` array<array<float>>)  
PARTITIONED BY (  
`day` string)  
ROW FORMAT SERDE  
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  
STORED AS INPUTFORMAT  
'org.apache.hadoop.mapred.TextInputFormat'  
OUTPUTFORMAT  
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'  
LOCATION  
'hdfs://testcluster/data/hive/test.db/test_text'

insert into test.test_text partition(day='2021-09-18') select 1, 2, 3, 4, 5, 6.11, 7.22, 8.333, current_timestamp(), current_date(), 'hello world', 'hello world', 'hello world', true, 'hello world', array(1, 2, 3), array('hello world', 'hello world'), array(float(1.1), float(1.2)), array(array(1, 2), array(3, 4)), array(array('a', 'b'), array('c', 'd')), array(array(float(1.11), float(2.22)), array(float(3.33), float(4.44)));

Clickhouse創(chuàng)建Hive表引擎

CREATE TABLE test.test_text  
(  
`f_tinyint` Int8,  
`f_smallint` Int16,  
`f_int` Int32,  
`f_integer` Int32,  
`f_bigint` Int64,  
`f_float` Float32,  
`f_double` Float64,  
`f_decimal` Float64,  
`f_timestamp` DateTime,  
`f_date` Date,  
`f_string` String,  
`f_varchar` String,  
`f_char` String,  
`f_bool` Bool,  
`day` String  
)  
ENGINE = Hive('thrift://localhost:9083', 'test', 'test_text')  
PARTITION BY day

通過Clickhouse查詢Hive數(shù)據(jù)

SELECT * FROM test.test_text settings input_format_skip_unknown_fields = 1, input_format_with_names_use_header = 1, date_time_input_format = 'best_effort'\G

總結

本節(jié)主要講解了Clickhouse整合Hive數(shù)倉，利用了Hive引擎并通過thrift方式去連接，需要注意這種連接參數(shù)的設置以及代表意義。另外，這個過程我們需要注意的是，推薦開啟緩存，這樣查詢速度會快很多。與此同時，也對Hive常用的三種數(shù)據(jù)類型ORC,Parquet,TextFile進行了一個實戰(zhàn)案例操作，更多關于Clickhouse整合Hive數(shù)據(jù)倉庫的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

由拖庫攻擊談口令字段的加密策略(數(shù)據(jù)庫加密)
我不得不慘痛地寫在前面的是，這是一個安全崩盤的時代。過去一年，已經(jīng)證實的遭遇入侵、并導致關鍵數(shù)據(jù)被竊或者被泄露的公司，包括索尼、世嘉這樣的大型游戲設備廠商;包括花旗銀行這樣的金融機構，也包括了RSA這樣的安全廠商
2012-01-01
多種獲取遠程連接access數(shù)據(jù)庫的方法
多種獲取遠程連接access數(shù)據(jù)庫的方法...
2007-03-03
DBeaver復制數(shù)據(jù)庫圖文教程(數(shù)據(jù)庫表結構以及內容)
DBeaver提供一個圖形界面用來查看數(shù)據(jù)庫結構、執(zhí)行SQL查詢和腳本,下面這篇文章主要給大家介紹了關于DBeaver復制數(shù)據(jù)庫(數(shù)據(jù)庫表結構以及內容)的相關資料,需要的朋友可以參考下
2024-01-01
顏值與實用性并存的數(shù)據(jù)庫建模工具Chiner教程
這篇文章主要為大家介紹了一款顏值與實用性并存的數(shù)據(jù)庫建模工具Chiner，推薦大家使用，有需要的朋友可以共同學習參考下，希望能夠有所幫助，祝大家多多進步
2022-03-03
Hadoop 2.x偽分布式環(huán)境搭建詳細步驟
這篇文章主要為大家詳細介紹了Hadoop 2.x偽分布式環(huán)境搭建詳細步驟，感興趣的朋友可以參考一下
2016-05-05
關于ADOX的相關知識
關于ADOX的相關知識...
2006-12-12
數(shù)據(jù)庫中union 與union all 的區(qū)別
當我們要對兩個或兩個以上的表進行操作時。我們經(jīng)常會用到union 與union all
2009-11-11
2023年最新Navicat永久激活安裝使用教程
Navicat中文版是強大的可視化數(shù)據(jù)庫管理工具,用于開發(fā)和管理MySQL|SQL?Server|SQLite|Oracle等數(shù)據(jù)庫,Navicat以直覺化的圖形用戶界面符合數(shù)據(jù)庫管理員、開發(fā)人員及中小企業(yè)的需要，本文給大家分享2023年最新Navicat永久激活安裝使用教程，感興趣的朋友一起看看吧
2023-05-05
淺析sql server 公共表達式的簡單應用
本文主要對sql server 公共表達式的簡單應用進行介紹，具有一定的參考價值，有需要的可以看下
2016-12-12
數(shù)據(jù)庫設計的完整性約束表現(xiàn)在哪些方面
數(shù)據(jù)完整性是指數(shù)據(jù)的正確性、完備性和一致性，是衡量數(shù)據(jù)庫質量好壞的規(guī)范。數(shù)據(jù)庫完整性由各式各樣的完整性約束來確保，因而可以說數(shù)據(jù)庫完整性規(guī)劃即是數(shù)據(jù)庫完整性約束的規(guī)劃。那么，數(shù)據(jù)庫設計的完整性約束表現(xiàn)哪些方面?
2015-10-10