Page tree

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.





  1. Example (No LLAP or Hive Warehouse):


This section describes how to enable the 

D s platform
 to read sources in Hive and write results back to Hive.

  • A Hive source is a single table in a selected Hive database.
  • Apache Hive is a data warehouse system for managing queries against large datasets distributed across a Hadoop cluster. Queries are managed using HiveQL, a SQL-like querying language. See
  • The platform can publish results to Hive as part of any normal job or on an ad-hoc basis for supported output formats.
  • Hive is also used by the 
    D s platform
     to publish metadata results. This capability shares the same configuration described below.
  • ORC tables managed through Hive can be used as datasources for the platform.

Supported Versions:

Hive VersionMaster namenodeNotes
Hive 1.xHiveServer2All supported Hadoop deployments
Hive 2.xHiveServer2 (Interactive)Supported on HDP 2.6 only.



  1. HiveServer2 and your Hive databases must already be installed in your Hadoop cluster.


    NOTE: For JDBC interactions, the 

    D s platform
     supports HiveServer2 only.

  2. You have verified that Hive is working correctly.
  3. You have acquired and deployed the hive-site.xml configuration file into your 
    D s item
    . See Configure for Hadoop.


  1. Only one global connection to Hive is supported.
  2. Changes to the underlying Hive schema are not picked up by the 
    D s platform
     and will break the source and datasets that use it.
  3. During import, the JDBC data types from Hive are converted to 
    D s item
    itemdata types
    . When data is written back to Hive, the original Hive data types may not be preserved. For more information, see Type Conversions
  4. Publish to unmanaged tables in Hive is supported, except for the following actions:
    1. Create table
    2. Drop & load table
  5. Publish to partitioned tables in Hive is supported.
    1. The schema of the results and the partitioned table must be the same.
    2. If they do not match, you may see an SchemaMismatched Exception error in the UI. You can try a drop and republish action on the data. However, the newly generated table does not have partitions.
    3. For errors publishing to partitioned columns, additional information may be available in the logs. 
  6. Writing or publishing to ORC tables through Hive is not supported.

NOTE: Running user-defined functions for an external service, such as Hive, is not supported from within a recipe step. As a workaround, you may be able to execute recipes containing such external UDFs on the Photon running environment. Performance issues should be expected on larger datasets.

Configure for Hive

Hive user

The user with which Hive connects to read from the backend datastore should be a member of the user group

D s defaultuser
 or whatever group is used to access storage from the 
D s platform

Verify that the Unix or LDAP group

D s defaultuser
has read access to the Hive warehouse directory.

Hive user for Spark:


NOTE: If you are executing jobs in the Spark running environment, additional permissions may be required. If the Hive source is a reference or references to files stored elsewhere in backend storage, the Hive user or its group must have read and execute permissions on the source directories or files.


Enable Data Service

In platform configuration, the  

D s item
data service
data service
  must be enabled.
D s config

Please verify the following:

Code Block
"data-service.enabled": true,

Locate the Hive JDBC Jar

In platform configuration, you must verify that the following parameter is pointing to the proper location for the Hive JDBC JAR file. The example below identfies the location for Cloudera 5.10:


NOTE: This parameter varies for each supported distribution and version.

Code Block
"data-service.hiveJdbcJar": "hadoop-deps/cdh-5.10/build/libs/cdh-5.10-hive-jdbc.jar",

Enable Hive Support for Spark Job Service

If you are using the Spark running environment for execution and profiling jobs, you must enable Hive support within the Spark Job Service configuration block.


NOTE: The Spark running environment is the default running environment. When this change is made, the platform requires that a valid hive-site.xml cluster configuration file be installed on the

D s node


  1. D s config
  2. Locate the following setting and verify that it is set to true:

    Code Block
    "spark-job-service.enableHiveSupport" : true,

  3. Modify the following parameter to point to the location where Hive dependencies are installed. This example points to the location for Cloudera 5.10:


    NOTE: This parameter value is distribution-specific. Please update based on your specific distribution.

    Code Block

  4. Save your changes.

Enable Hive Database Access for Spark Job Service

The Spark Job Services requires read access to the Hive databases. Please verify that the Spark user can access the required Hive databases and tables.

For more information, please contact your Hive administrator.

Configure managed table format


D s platform
publishes to Hive using managed tables. When writing to Hive, the platform pushes to an externally staged table. Then, from this staging table, the platform selects and inserts into a managed table.

By default, the platform published to managed tables in Parquet format. As needed, you can apply the following values into platform configuration to change the format to which the platform writes when publishing a managed table:

  • PARQUET (default)
  • AVRO

To change the format, please modify the following parameter.


  1. D s config
  2. Locate the following parameter and modify it using one of the above values:

    Code Block
    "data-service.hiveManagedTableFormat": "PARQUET",

  3. Save your changes and restart the platform.

Additional configuration for HDP 3.x

If you are integrating with an HDP 3.x cluster, please add the following the Spark Job Service classpath:

  1. D s config
  2. Add the following value to Spark Job Service classpath.:

Code Block
D s property overflow

"classpath": "%(topOfTree)/etc/hive/conf:%(topOfTree)s/ \
services/spark-job-server/server/build/libs/ \
spark-job-server-bundle.jar:%(sparkBundleJar)s:/ \ 
etc/hadoop/conf/:/etc/hive/conf/: \ 
  • Save your changes. Before restarting the platform, please review the following section.
  • Additional configuration for Hive 3.0 on HDP 3.x


    NOTE: Hive 3.0 is supported only on Hortonworks HDP 3.x using the Hive Warehouse Connector to read from Hive.

    Tables in Hive 3.0 are ACID-compliant, transactional tables. Since Spark cannot natively read transactional tables, the

    D s platform
    must utilize Hive Warehouse Connector to query the Hive 3.0 datastore for tabular data.


    NOTE: If Ranger is deployed on the cluster, Spark respects any column- or row-level security that Ranger enforces on the Hive tables. Queries for unauthorized data in a table fail in the

    D s platform


    Please complete the following steps to integrate the

    D s platform
    with Hive 3.0 through HDP 3.x and LLAP.


    NOTE: Before you begin, please verify that you have performed the extra configuration for using Spark on HDP 3.x. For more information, see Configure for Spark.


    1. D s config

    2. Enable use of the Hive Warehouse Connector:

      Code Block
      "spark-job-service.useHiveWarehouseConnector": true

    3. Add the Hive Warehouse Connector to the Spark Job Service classpath. Example:


      NOTE: If you have already configured for HDP 3.x, then the (sparkBundleJar) update below may have already been added.

      Code Block
      "classpath": "%(topOfTree)s/services/spark-job-server/server/build/libs/spark-job-server-bundle.jar:%(sparkBundleJar)s:/etc/hadoop/conf/:/etc/hive/conf/:%(topOfTree)s/%(hadoopBundleJar)s:/usr/hdp/current/hive_warehouse_connector/*"

    4. The following properties and values must be inserted in the spark.props section:


      NOTE: These properties must be added to the

      D s platform
      configuration. They cannot be read from Ambari.

      Code Block
      "spark.datasource.hive.warehouse.load.staging.dir": "/tmp",
      "spark.datasource.hive.warehouse.metastoreUri": "thrift://hdp30.example:9083",
      "spark.driver.extraLibraryPath": "/usr/hdp/current/hadoop-client/lib/native:/usr/hdp/current/hadoop-client/lib/native/Linux-amd64-64",
      "spark.executor.extraJavaOptions": "-XX:+UseNUMA",
      "spark.executor.extraLibraryPath": "/usr/hdp/current/hadoop-client/lib/native:/usr/hdp/current/hadoop-client/lib/native/Linux-amd64-64",
      "spark.hadoop.hive.llap.daemon.service.hosts": "@llap0",
      "spark.hadoop.hive.zookeeper.quorum": "hdp30.example:2181",
      "spark.sql.hive.hiveserver2.jdbc.url": "jdbc:hive2://hdp30.example:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2-interactive",
      "spark.sql.hive.hiveserver2.jdbc.url.principal": "hive/_HOST@HORTONWORKS",
      "": "true",
      "spark.yarn.jars": "local:/usr/hdp/current/spark2-client/jars/*"
      "spark.driver.extraClassPath": "/usr/hdp/current/spark2-client/jars/guava-14.0.1.jar"
      "spark.executor.extraClassPath": "/usr/hdp/current/spark2-client/jars/guava-14.0.1.jar"

      The properties listed below require information from your HDP cluster. For the other properties, please use the listed values, unless otherwise required.



      URI for the Hive metastore. Copy the value from hive.metastore.uris. Example value:

      Code Block


      A list of Zookeeper hosts used by LLAP. Copy the value from Advanced hive-site in Ambari: hive.zookeeper.quorum


      The URL for HiveServer2 Interactive. In Ambari, copy the value from the following: Services > Hive > Summary > HIVESERVER2 INTERACTIVE JDBC URL.

      "spark.sql.hive.hiveserver2.jdbc.url.principal"This property must be equal to hive.server2.authentication.kerberos.principal. In Ambari, copy the value for this property from the following: Services > Hive >Configs > Advanced > Advanced hive-site. The property value is in hive.server2.authentication.kerberos.principal.

      For more information on these properties, see

    5. Save your changes and restart the platform.

    Create Hive Connection


    NOTE: High availability for Hive is supported through configuration of the Hive connection.

    For more information, see Create Hive Connections.

    Optional Configuration

    Depending on your Hadoop environment, you may need to perform additional configuration to enable connectivity with your Hadoop cluster.

    Additional Configuration for Secure Environments

    For secure impersonation



    NOTE: You should have already configured the

    D s platform
    to use secure impersonation.For more information on basic configuration, see Configure for Secure Impersonation.

    You must add the Hive principal value to your Hive connection. Add the following principal value to the Connect String Options textbox.

    Code Block
      "connectStrOpts": ";principal=<principal_value>",


    For Kerberos with secure impersonation


    1. Download and unzip the following dataset: Dataset-HiveExampleData.
    2. Store the dataset in the following example directory: 

      Code Block

    3. Use the following command to create your table:

      Code Block
      create table test (name string, id bigint, id2 bigint, randomName string, description string, dob string, title string, corp string, fixedOne bigint, fixedTwo int) row format delimited fields terminated by ',' STORED AS TEXTFILE;

    4. Add the example dataset to the above test table:

      Code Block
      load data local inpath '/tmp/hiveTest_5mb' into table test;