Clickhouse系列之整合Hive數(shù)據(jù)倉庫示例詳解
前言
什么是Hive? Apache Hive 數(shù)據(jù)倉庫軟件便于使用SQL讀取、寫入和管理駐留在分布式存儲(chǔ)中的大型數(shù)據(jù)集。結(jié)構(gòu)可以投射到已存儲(chǔ)的數(shù)據(jù)上。提供了一個(gè)命令行工具和JDBC驅(qū)動(dòng)程序,用于將用戶連接到Hive。
Hive引擎允許您對(duì)HDFS配置單元表執(zhí)行SELECT查詢。目前支持如下輸入格式:
- 文本:僅支持簡(jiǎn)單標(biāo)量列類型,二進(jìn)制除外;
- ORC:支持除char以外的簡(jiǎn)單標(biāo)量列類型;僅支持?jǐn)?shù)組等復(fù)雜類型;
- parquet:支持所有簡(jiǎn)單的標(biāo)量列類型;僅支持?jǐn)?shù)組等復(fù)雜類型。
正文
創(chuàng)建Hive引擎表詳細(xì)信息以及參數(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
表結(jié)構(gòu)可以與原始配置單元表結(jié)構(gòu)不同:
- 列名應(yīng)該與原始配置單元表中的列名相同(推薦列名相同處理),但您可以只使用其中的一些列,并且可以按任何順序使用,也可以使用從其他列計(jì)算的一些別名列。
- 列類型應(yīng)與原始配置單元表中的列類型相同。
- 按表達(dá)式劃分應(yīng)該與原始Hive表一致,按表達(dá)式劃分中的列應(yīng)該在表結(jié)構(gòu)中。
引擎參數(shù):
- thrift://host:port-配置單元元存儲(chǔ)地址
- database—遠(yuǎn)程數(shù)據(jù)庫名稱。
- table—遠(yuǎn)程表名稱。
實(shí)戰(zhàn)案例
為遠(yuǎn)程文件系統(tǒng)啟用本地緩存。通過官方的基準(zhǔn)測(cè)試表明,使用緩存的速度快了近兩倍。在使用緩存之前,將其添加到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將在啟動(dòng)后維護(hù)遠(yuǎn)程文件系統(tǒng)(HDFS)的本地緩存(如果為true)。
- root_dir:必需。用于存儲(chǔ)遠(yuǎn)程文件系統(tǒng)的本地緩存文件的根目錄。
- limit_size:必填。本地緩存文件的最大大?。ㄗ止?jié))。
- bytes_read_before_flush:從遠(yuǎn)程文件系統(tǒng)下載文件時(shí),在刷新到本地文件系統(tǒng)之前控制字節(jié)數(shù)。默認(rèn)值為1MB。
盡管ClickHouse在啟用遠(yuǎn)程文件系統(tǒng)本地緩存的情況下啟動(dòng)時(shí),我們?nèi)匀豢梢赃x擇不使用其查詢中設(shè)置為use_local_cache_for_remote_fs=0的緩存。use_local_cache_for_remote_fs默認(rèn)為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é)
本節(jié)主要講解了Clickhouse整合Hive數(shù)倉,利用了Hive引擎并通過thrift方式去連接,需要注意這種連接參數(shù)的設(shè)置以及代表意義。另外,這個(gè)過程我們需要注意的是,推薦開啟緩存,這樣查詢速度會(huì)快很多。與此同時(shí),也對(duì)Hive常用的三種數(shù)據(jù)類型ORC,Parquet,TextFile進(jìn)行了一個(gè)實(shí)戰(zhàn)案例操作,更多關(guān)于Clickhouse整合Hive數(shù)據(jù)倉庫的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
數(shù)據(jù)庫設(shè)計(jì)的完整性約束表現(xiàn)在哪些方面
數(shù)據(jù)完整性是指數(shù)據(jù)的正確性、完備性和一致性,是衡量數(shù)據(jù)庫質(zhì)量好壞的規(guī)范。數(shù)據(jù)庫完整性由各式各樣的完整性約束來確保,因而可以說數(shù)據(jù)庫完整性規(guī)劃即是數(shù)據(jù)庫完整性約束的規(guī)劃。那么,數(shù)據(jù)庫設(shè)計(jì)的完整性約束表現(xiàn)哪些方面?2015-10-10powerdesigner?for?mysql腳本要求字段、表名有注釋操作
在PowerDesigner中,可以通過修改DBMS設(shè)置為MySQL數(shù)據(jù)庫添加字段和表名的注釋,具體步驟包括編輯當(dāng)前的DBMS設(shè)置,并在相應(yīng)的Script選項(xiàng)下調(diào)整Column和Table的配置,本文給大家介紹powerdesigner?for?mysql腳本要求字段、表名有注釋操作,感興趣的朋友跟隨小編一起看看吧2023-07-07Navicat快速導(dǎo)入和導(dǎo)出sql文件的方法
Navicat是MySQL非常好用的可視化管理工具,功能非常強(qiáng)大,能滿足我們?nèi)粘?shù)據(jù)庫開發(fā)的所有需求。今天教大家如何導(dǎo)入和導(dǎo)出SQL文件,感興趣的朋友跟隨小編一起看看吧2021-05-05TDSQL 安裝部署附圖的實(shí)現(xiàn)(圖文)
這篇文章主要介紹了TDSQL 安裝部署附圖的實(shí)現(xiàn)(圖文),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-10-10