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

查看CREATE TABLE查询的详细描述。

表的结构可以与原来的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_fs = 0, use_local_cache_for_remote_fs 默认为 false。

查询 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