ClickHouse(19)ClickHouse集成Hive表引擎详细解析

目录

• Hive集成表引擎
  
  
  
  • 创建表
    
  • 使用示例
    
    
    
    • 如何使用HDFS文件系统的本地缓存
      
    • 查询 ORC 输入格式的Hive 表
      
      
      
      • 在 Hive 中建表
        
      • 在 ClickHouse 中建表
        
      
      
    • 查询 Parquest 输入格式的Hive 表
      
      
      
      • 在 Hive 中建表
        
      • 在 ClickHouse 中建表
        
      
      
    • 查询文本输入格式的Hive表
      
      
      
      • 在Hive 中建表
        
      • 在 ClickHouse 中建表
        
      
      
    
    
  
  
• 资料分享
  
• 参考文章
  

Hive集成表引擎

Hive引擎允许对HDFS Hive表执行 SELECT 查询。目前它支持如下输入格式:
-文本:只支持简单的标量列类型，除了 Binary

• ORC:支持简单的标量列类型，除了char; 只支持 array 这样的复杂类型
  
• Parquet:支持所有简单标量列类型;只支持 array 这样的复杂类型
  

创建表

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

复制代码

表的结构可以与原来的Hive表结构有所不同:

• 列名应该与原来的Hive表相同，但你可以使用这些列中的一些，并以任何顺序，你也可以使用一些从其他列计算的别名列。
  
• 列类型与原Hive表的列类型保持一致。
  
• “Partition by expression”应与原Hive表保持一致，“Partition by expression”中的列应在表结构中。
  

引擎参数

• thrift://host:port — Hive Metastore 地址
  
• database — 远程数据库名.
  
• table — 远程数据表名.
  

使用示例

如何使用HDFS文件系统的本地缓存

我们强烈建议您为远程文件系统启用本地缓存。基准测试显示，如果使用缓存，它的速度会快两倍。
在使用缓存之前，请将其添加到 config.xml

1. <local_cache_for_remote_fs>
2.     <enable>true</enable>
3.     <root_dir>local_cache</root_dir>
4.     <limit_size>559096952</limit_size>
5.     <bytes_read_before_flush>1048576</bytes_read_before_flush>
6. </local_cache_for_remote_fs>

复制代码

• enable: 开启后，ClickHouse将为HDFS (远程文件系统)维护本地缓存。
  
• root_dir: 必需的。用于存储远程文件系统的本地缓存文件的根目录。
  
• limit_size: 必需的。本地缓存文件的最大大小(单位为字节)。
  
• bytes_read_before_flush: 从远程文件系统下载文件时，刷新到本地文件系统前的控制字节数。缺省值为1MB。
  

当ClickHouse为远程文件系统启用了本地缓存时，用户仍然可以选择不使用缓存，并在查询中设置 use_local_cache_for_remote_storage = 0, use_local_cache_for_remote_storage 默认为 1。
查询 ORC 输入格式的Hive 表

在 Hive 中建表

1. hive > CREATE TABLE `test`.`test_orc`(
2.   `f_tinyint` tinyint,
3.   `f_smallint` smallint,
4.   `f_int` int,
5.   `f_integer` int,
6.   `f_bigint` bigint,
7.   `f_float` float,
8.   `f_double` double,
9.   `f_decimal` decimal(10,0),
10.   `f_timestamp` timestamp,
11.   `f_date` date,
12.   `f_string` string,
13.   `f_varchar` varchar(100),
14.   `f_bool` boolean,
15.   `f_binary` binary,
16.   `f_array_int` array<int>,
17.   `f_array_string` array<string>,
18.   `f_array_float` array<float>,
19.   `f_array_array_int` array<array<int>>,
20.   `f_array_array_string` array<array<string>>,
21.   `f_array_array_float` array<array<float>>)
22. PARTITIONED BY (
23.   `day` string)
24. ROW FORMAT SERDE
25.   'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
26. STORED AS INPUTFORMAT
27.   'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
28. OUTPUTFORMAT
29.   'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
30. LOCATION
31.   'hdfs://testcluster/data/hive/test.db/test_orc'
33. OK
34. Time taken: 0.51 seconds
36. hive > 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)));
37. OK
38. Time taken: 36.025 seconds
40. hive > select * from test.test_orc;
41. OK
42. 1   2   3   4   5   6.11    7.22    8   2021-11-05 12:38:16.314 2021-11-05  hello world hello world hello world                                                                                             true    hello world [1,2,3] ["hello world","hello world"]   [1.1,1.2]   [[1,2],[3,4]]   [["a","b"],["c","d"]]   [[1.11,2.22],[3.33,4.44]]   2021-09-18
43. Time taken: 0.295 seconds, Fetched: 1 row(s)

复制代码

在 ClickHouse 中建表

ClickHouse中的表，从上面创建的Hive表中获取数据:

1. CREATE TABLE test.test_orc
2. (
3.     `f_tinyint` Int8,
4.     `f_smallint` Int16,
5.     `f_int` Int32,
6.     `f_integer` Int32,
7.     `f_bigint` Int64,
8.     `f_float` Float32,
9.     `f_double` Float64,
10.     `f_decimal` Float64,
11.     `f_timestamp` DateTime,
12.     `f_date` Date,
13.     `f_string` String,
14.     `f_varchar` String,
15.     `f_bool` Bool,
16.     `f_binary` String,
17.     `f_array_int` Array(Int32),
18.     `f_array_string` Array(String),
19.     `f_array_float` Array(Float32),
20.     `f_array_array_int` Array(Array(Int32)),
21.     `f_array_array_string` Array(Array(String)),
22.     `f_array_array_float` Array(Array(Float32)),
23.     `day` String
24. )
25. ENGINE = Hive('thrift://localhost:9083', 'test', 'test_orc')
26. PARTITION BY day

复制代码

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

复制代码

1. SELECT *
2. FROM test.test_orc
3. SETTINGS input_format_orc_allow_missing_columns = 1
5. Query id: c3eaffdc-78ab-43cd-96a4-4acc5b480658
7. Row 1:
8. ──────
9. f_tinyint:            1
10. f_smallint:           2
11. f_int:                3
12. f_integer:            4
13. f_bigint:             5
14. f_float:              6.11
15. f_double:             7.22
16. f_decimal:            8
17. f_timestamp:          2021-12-04 04:00:44
18. f_date:               2021-12-03
19. f_string:             hello world
20. f_varchar:            hello world
21. f_bool:               true
22. f_binary:             hello world
23. f_array_int:          [1,2,3]
24. f_array_string:       ['hello world','hello world']
25. f_array_float:        [1.1,1.2]
26. f_array_array_int:    [[1,2],[3,4]]
27. f_array_array_string: [['a','b'],['c','d']]
28. f_array_array_float:  [[1.11,2.22],[3.33,4.44]]
29. day:                  2021-09-18
32. 1 rows in set. Elapsed: 0.078 sec.

复制代码

查询 Parquest 输入格式的Hive 表

在 Hive 中建表

1. hive >
2. CREATE TABLE `test`.`test_parquet`(
3.   `f_tinyint` tinyint,
4.   `f_smallint` smallint,
5.   `f_int` int,
6.   `f_integer` int,
7.   `f_bigint` bigint,
8.   `f_float` float,
9.   `f_double` double,
10.   `f_decimal` decimal(10,0),
11.   `f_timestamp` timestamp,
12.   `f_date` date,
13.   `f_string` string,
14.   `f_varchar` varchar(100),
15.   `f_char` char(100),
16.   `f_bool` boolean,
17.   `f_binary` binary,
18.   `f_array_int` array<int>,
19.   `f_array_string` array<string>,
20.   `f_array_float` array<float>,
21.   `f_array_array_int` array<array<int>>,
22.   `f_array_array_string` array<array<string>>,
23.   `f_array_array_float` array<array<float>>)
24. PARTITIONED BY (
25.   `day` string)
26. ROW FORMAT SERDE
27.   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
28. STORED AS INPUTFORMAT
29.   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
30. OUTPUTFORMAT
31.   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
32. LOCATION
33.   'hdfs://testcluster/data/hive/test.db/test_parquet'
34. OK
35. Time taken: 0.51 seconds
37. hive >  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)));
38. OK
39. Time taken: 36.025 seconds
41. hive > select * from test.test_parquet;
42. OK
43. 1   2   3   4   5   6.11    7.22    8   2021-12-14 17:54:56.743 2021-12-14  hello world hello world hello world                                                                                             true    hello world [1,2,3] ["hello world","hello world"]   [1.1,1.2]   [[1,2],[3,4]]   [["a","b"],["c","d"]]   [[1.11,2.22],[3.33,4.44]]   2021-09-18
44. Time taken: 0.766 seconds, Fetched: 1 row(s)

复制代码

在 ClickHouse 中建表

ClickHouse 中的表， 从上面创建的Hive表中获取数据:

1. CREATE TABLE test.test_parquet
2. (
3.     `f_tinyint` Int8,
4.     `f_smallint` Int16,
5.     `f_int` Int32,
6.     `f_integer` Int32,
7.     `f_bigint` Int64,
8.     `f_float` Float32,
9.     `f_double` Float64,
10.     `f_decimal` Float64,
11.     `f_timestamp` DateTime,
12.     `f_date` Date,
13.     `f_string` String,
14.     `f_varchar` String,
15.     `f_char` String,
16.     `f_bool` Bool,
17.     `f_binary` String,
18.     `f_array_int` Array(Int32),
19.     `f_array_string` Array(String),
20.     `f_array_float` Array(Float32),
21.     `f_array_array_int` Array(Array(Int32)),
22.     `f_array_array_string` Array(Array(String)),
23.     `f_array_array_float` Array(Array(Float32)),
24.     `day` String
25. )
26. ENGINE = Hive('thrift://localhost:9083', 'test', 'test_parquet')
27. PARTITION BY day

复制代码

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

复制代码

1. SELECT *
2. FROM test_parquet
3. SETTINGS input_format_parquet_allow_missing_columns = 1
5. Query id: 4e35cf02-c7b2-430d-9b81-16f438e5fca9
7. Row 1:
8. ──────
9. f_tinyint:            1
10. f_smallint:           2
11. f_int:                3
12. f_integer:            4
13. f_bigint:             5
14. f_float:              6.11
15. f_double:             7.22
16. f_decimal:            8
17. f_timestamp:          2021-12-14 17:54:56
18. f_date:               2021-12-14
19. f_string:             hello world
20. f_varchar:            hello world
21. f_char:               hello world
22. f_bool:               true
23. f_binary:             hello world
24. f_array_int:          [1,2,3]
25. f_array_string:       ['hello world','hello world']
26. f_array_float:        [1.1,1.2]
27. f_array_array_int:    [[1,2],[3,4]]
28. f_array_array_string: [['a','b'],['c','d']]
29. f_array_array_float:  [[1.11,2.22],[3.33,4.44]]
30. day:                  2021-09-18
32. 1 rows in set. Elapsed: 0.357 sec.

复制代码

查询文本输入格式的Hive表

在Hive 中建表

1. hive >
2. CREATE TABLE `test`.`test_text`(
3.   `f_tinyint` tinyint,
4.   `f_smallint` smallint,
5.   `f_int` int,
6.   `f_integer` int,
7.   `f_bigint` bigint,
8.   `f_float` float,
9.   `f_double` double,
10.   `f_decimal` decimal(10,0),
11.   `f_timestamp` timestamp,
12.   `f_date` date,
13.   `f_string` string,
14.   `f_varchar` varchar(100),
15.   `f_char` char(100),
16.   `f_bool` boolean,
17.   `f_binary` binary,
18.   `f_array_int` array<int>,
19.   `f_array_string` array<string>,
20.   `f_array_float` array<float>,
21.   `f_array_array_int` array<array<int>>,
22.   `f_array_array_string` array<array<string>>,
23.   `f_array_array_float` array<array<float>>)
24. PARTITIONED BY (
25.   `day` string)
26. ROW FORMAT SERDE
27.   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
28. STORED AS INPUTFORMAT
29.   'org.apache.hadoop.mapred.TextInputFormat'
30. OUTPUTFORMAT
31.   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
32. LOCATION
33.   'hdfs://testcluster/data/hive/test.db/test_text'
34. Time taken: 0.1 seconds, Fetched: 34 row(s)
37. hive >  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)));
38. OK
39. Time taken: 36.025 seconds
41. hive > select * from test.test_text;
42. OK
43. 1   2   3   4   5   6.11    7.22    8   2021-12-14 18:11:17.239 2021-12-14  hello world hello world hello world                                                                                             true    hello world [1,2,3] ["hello world","hello world"]   [1.1,1.2]   [[1,2],[3,4]]   [["a","b"],["c","d"]]   [[1.11,2.22],[3.33,4.44]]   2021-09-18
44. Time taken: 0.624 seconds, Fetched: 1 row(s)

复制代码

在 ClickHouse 中建表

ClickHouse中的表， 从上面创建的Hive表中获取数据:

1. CREATE TABLE test.test_text
2. (
3.     `f_tinyint` Int8,
4.     `f_smallint` Int16,
5.     `f_int` Int32,
6.     `f_integer` Int32,
7.     `f_bigint` Int64,
8.     `f_float` Float32,
9.     `f_double` Float64,
10.     `f_decimal` Float64,
11.     `f_timestamp` DateTime,
12.     `f_date` Date,
13.     `f_string` String,
14.     `f_varchar` String,
15.     `f_char` String,
16.     `f_bool` Bool,
17.     `day` String
18. )
19. ENGINE = Hive('thrift://localhost:9083', 'test', 'test_text')
20. PARTITION BY day

复制代码

1. 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

复制代码

1. SELECT *
2. FROM test.test_text
3. SETTINGS input_format_skip_unknown_fields = 1, input_format_with_names_use_header = 1, date_time_input_format = 'best_effort'
5. Query id: 55b79d35-56de-45b9-8be6-57282fbf1f44
7. Row 1:
8. ──────
9. f_tinyint:   1
10. f_smallint:  2
11. f_int:       3
12. f_integer:   4
13. f_bigint:    5
14. f_float:     6.11
15. f_double:    7.22
16. f_decimal:   8
17. f_timestamp: 2021-12-14 18:11:17
18. f_date:      2021-12-14
19. f_string:    hello world
20. f_varchar:   hello world
21. f_char:      hello world
22. f_bool:      true
23. day:         2021-09-18

复制代码

资料分享

ClickHouse经典中文文档分享
参考文章

• ClickHouse(01)什么是ClickHouse,ClickHouse适用于什么场景
  
• ClickHouse(02)ClickHouse架构设计介绍概述与ClickHouse数据分片设计
  
• ClickHouse(03)ClickHouse怎么安装和部署
  
• ClickHouse(04)如何搭建ClickHouse集群
  
• ClickHouse(05)ClickHouse数据类型详解
  
• ClickHouse(06)ClickHouse建表语句DDL详细解析
  
• ClickHouse(07)ClickHouse数据库引擎解析
  
• ClickHouse(08)ClickHouse表引擎概况
  
• ClickHouse(09)ClickHouse合并树MergeTree家族表引擎之MergeTree详细解析
  
• ClickHouse(10)ClickHouse合并树MergeTree家族表引擎之ReplacingMergeTree详细解析
  
• ClickHouse(11)ClickHouse合并树MergeTree家族表引擎之SummingMergeTree详细解析
  
• ClickHouse(12)ClickHouse合并树MergeTree家族表引擎之AggregatingMergeTree详细解析
  
• ClickHouse(13)ClickHouse合并树MergeTree家族表引擎之CollapsingMergeTree详细解析
  
• ClickHouse(14)ClickHouse合并树MergeTree家族表引擎之VersionedCollapsingMergeTree详细解析
  
• ClickHouse(15)ClickHouse合并树MergeTree家族表引擎之GraphiteMergeTree详细解析
  
• ClickHouse(16)ClickHouse日志引擎Log详细解析
  
• ClickHouse(17)ClickHouse集成JDBC表引擎详细解析
  
• ClickHouse(18)ClickHouse集成ODBC表引擎详细解析
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！