Hive External Table of Doris

Hive External Table of Doris provides Doris with direct access to Hive external tables, which eliminates the need for cumbersome data import and solves the problem of analyzing Hive tables with the help of Doris’ OLAP capabilities:

  1. support for Hive data sources to access Doris
  2. Support joint queries between Doris and Hive data sources to perform more complex analysis operations
  3. Support access to kerberos-enabled Hive data sources

This document introduces how to use this feature and the considerations.

Glossary

Noun in Doris

  • FE: Frontend, the front-end node of Doris, responsible for metadata management and request access.
  • BE: Backend, the backend node of Doris, responsible for query execution and data storage

How To Use

Create Hive External Table

  1. -- Syntax
  2. CREATE [EXTERNAL] TABLE table_name (
  3.   col_name col_type [NULL | NOT NULL] [COMMENT "comment"]
  4. ) ENGINE=HIVE
  5. [COMMENT "comment"] )
  6. PROPERTIES (
  7.   'property_name'='property_value',
  8.   ...
  9. );
  11. -- Example 1: Create the hive_table table under hive_db in a Hive cluster
  12. CREATE TABLE `t_hive` (
  13.   `k1` int NOT NULL COMMENT "",
  14.   `k2` char(10) NOT NULL COMMENT "",
  15.   `k3` datetime NOT NULL COMMENT "",
  16.   `k5` varchar(20) NOT NULL COMMENT "",
  17.   `k6` double NOT NULL COMMENT ""
  18. ) ENGINE=HIVE
  19. COMMENT "HIVE"
  20. PROPERTIES (
  21. 'hive.metastore.uris' = 'thrift://192.168.0.1:9083',
  22. 'database' = 'hive_db',
  23. 'table' = 'hive_table'
  24. );
  26. -- Example 2: Create the hive_table table under hive_db in a Hive cluster with HDFS HA configuration.
  27. CREATE TABLE `t_hive` (
  28.   `k1` int NOT NULL COMMENT "",
  29.   `k2` char(10) NOT NULL COMMENT "",
  30.   `k3` datetime NOT NULL COMMENT "",
  31.   `k5` varchar(20) NOT NULL COMMENT "",
  32.   `k6` double NOT NULL COMMENT ""
  33. ) ENGINE=HIVE
  34. COMMENT "HIVE"
  35. PROPERTIES (
  36. 'hive.metastore.uris' = 'thrift://192.168.0.1:9083',
  37. 'database' = 'hive_db',
  38. 'table' = 'hive_table',
  39. 'dfs.nameservices'='hacluster',
  40. 'dfs.ha.namenodes.hacluster'='n1,n2',
  41. 'dfs.namenode.rpc-address.hacluster.n1'='192.168.0.1:8020',
  42. 'dfs.namenode.rpc-address.hacluster.n2'='192.168.0.2:8020',
  43. 'dfs.client.failover.proxy.provider.hacluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
  44. );
  46. -- Example 3: Create the hive external table under hive_db in Hive cluster with HDFS HA and enable kerberos authentication. 
  47. CREATE TABLE `t_hive` (
  48.   `k1` int NOT NULL COMMENT "",
  49.   `k2` char(10) NOT NULL COMMENT "",
  50.   `k3` datetime NOT NULL COMMENT "",
  51.   `k5` varchar(20) NOT NULL COMMENT "",
  52.   `k6` double NOT NULL COMMENT ""
  53. ) ENGINE=HIVE
  54. COMMENT "HIVE"
  55. PROPERTIES (
  56. 'hive.metastore.uris' = 'thrift://192.168.0.1:9083',
  57. 'database' = 'hive_db',
  58. 'table' = 'hive_table',
  59. 'dfs.nameservices'='hacluster',
  60. 'dfs.ha.namenodes.hacluster'='n1,n2',
  61. 'dfs.namenode.rpc-address.hacluster.n1'='192.168.0.1:8020',
  62. 'dfs.namenode.rpc-address.hacluster.n2'='192.168.0.2:8020',
  63. 'dfs.client.failover.proxy.provider.hacluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider',
  64. 'hadoop.security.authentication'='kerberos',
  65. 'dfs.namenode.kerberos.principal'='hadoop/_HOST@REALM.COM'
  66. 'hadoop.kerberos.principal'='doris_test@REALM.COM',
  67. 'hadoop.kerberos.keytab'='/path/to/doris_test.keytab'
  68. );
  70. -- Example 4: Create the hive_table table under hive_db in a Hive cluster with data stored on S3
  71. CREATE TABLE `t_hive` (
  72.   `k1` int NOT NULL COMMENT "",
  73.   `k2` char(10) NOT NULL COMMENT "",
  74.   `k3` datetime NOT NULL COMMENT "",
  75.   `k5` varchar(20) NOT NULL COMMENT "",
  76.   `k6` double NOT NULL COMMENT ""
  77. ) ENGINE=HIVE
  78. COMMENT "HIVE"
  79. PROPERTIES (
  80. 'hive.metastore.uris' = 'thrift://192.168.0.1:9083',
  81. 'database' = 'hive_db',
  82. 'table' = 'hive_table',
  83. 'AWS_ACCESS_KEY'='your_access_key',
  84. 'AWS_SECRET_KEY'='your_secret_key',
  85. 'AWS_ENDPOINT'='s3.us-east-1.amazonaws.com',
  86. 'AWS_REGION'='us-east-1'
  87. );

Parameter Description

  • External Table Columns
    • Column names should correspond to the Hive table
    • The order of the columns should be the same as the Hive table
    • Must contain all the columns in the Hive table
    • Hive table partition columns do not need to be specified, they can be defined as normal columns.
  • ENGINE should be specified as HIVE
  • PROPERTIES attribute.
    • hive.metastore.uris: Hive Metastore service address
    • database: the name of the database to which Hive is mounted
    • table: the name of the table to which Hive is mounted
    • hadoop.username: the username to visit HDFS (need to specify it when the authentication type is simple)
    • dfs.nameservices: the logical name for this new nameservice. See hdfs-site.xml
    • dfs.ha.namenodes.[nameservice ID]：unique identifiers for each NameNode in the nameservice. See hdfs-site.xml
    • dfs.namenode.rpc-address.[nameservice ID].[name node ID]：the fully-qualified RPC address for each NameNode to listen on. See hdfs-site.xml
    • dfs.client.failover.proxy.provider.[nameservice ID]：the Java class that HDFS clients use to contact the Active NameNode, usually it is org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider
  • For a kerberos enabled Hive datasource, additional properties need to be set:
    • dfs.namenode.kerberos.principal: HDFS namenode service principal
    • hadoop.security.authentication: HDFS authentication type please set kerberos, default simple
    • hadoop.kerberos.principal: The Kerberos pincipal that Doris will use when connectiong to HDFS.
    • hadoop.kerberos.keytab: HDFS client keytab location.
    • AWS_ACCESS_KEY: AWS access key id.
    • AWS_SECRET_KEY: AWS secret access key.
    • AWS_ENDPOINT: S3 endpoint. e.g. s3.us-east-1.amazonaws.com
    • AWS_REGION: AWS region. e.g. us-east-1

Note:

  • To enable Doris to access the hadoop cluster with kerberos authentication enabled, you need to deploy the Kerberos client kinit on the Doris all FE and BE nodes, configure krb5.conf, and fill in the KDC service information.
  • The value of the PROPERTIES property hadoop.kerberos.keytab needs to specify the absolute path of the keytab local file and allow the Doris process to access it.
  • The configuration of the HDFS cluster can be written into the hdfs-site.xml file. The configuration file is in the conf directory of fe and be. When users create a Hive table, they do not need to fill in the relevant information of the HDFS cluster configuration.

Data Type Matching

The supported Hive column types correspond to Doris in the following table.

HiveDorisDescription
BOOLEANBOOLEAN
CHARCHAROnly UTF8 encoding is supported
VARCHARVARCHAROnly UTF8 encoding is supported
TINYINTTINYINT
SMALLINTSMALLINT
INTINT
BIGINTBIGINT
FLOATFLOAT
DOUBLEDOUBLE
DECIMALDECIMAL
DATEDATE
TIMESTAMPDATETIMETimestamp to Datetime will lose precision

Note:

  • Hive table Schema changes are not automatically synchronized and require rebuilding the Hive external table in Doris.
  • The current Hive storage format only supports Text, Parquet and ORC types
  • The Hive version currently supported by default is 2.3.7、3.1.2, which has not been tested in other versions. More versions will be supported in the future.

Query Usage

After you finish building the Hive external table in Doris, it is no different from a normal Doris OLAP table except that you cannot use the data model in Doris (rollup, preaggregation, materialized view, etc.)

  1. select * from t_hive where k1 > 1000 and k3 = 'term' or k4 like '%doris';