Home

Configuring Hive Metastore read-only user permissions

Unravel daemons need READ permission on the Hive metastore. To define a read-only Hive metastore user, follow these steps.

  1. Get details about the location of the Hive Metastore.

    • In CDH, use the following Cloudera Manager API to get the Hive metastore database name and port. By default, in CDH and HDP, the hive metastore database name is hive.

      • For TLS-secured Cloudera Manager

        curl -k -u admin:admin "https://cloudera-manager-host:7183/api/v11/clusters/cluster-name/services/hive/config"

      • For non-TLS secured Cloudera Manager

        curl -k -u admin:admin "http://cloudera-manager-host:7183/api/v11/clusters/cluster-name/services/hive/config"

        or

        curl -k -u admin:admin "http://cloudera-manager-host:7180/api/v12/cm/deployment"

      Look for hive_metastore_database_host, hive_metastore_database_port, hive_metastore_database_user and hive_metastore_database_password in the JSON response body.

    • For HDP, contact your cluster administrator.

    • For MapR, contact your cluster administrator.

  2. Connect to the Hive metastore using the normal conversational interface for your underlying database (MySQL, psql, Oracle, and so on) as an administrator or root user that can create new users and grant privileges.

    For PostgreSQL, run the psql command as admin user cloudera-scm on Cloudera embedded PostgreSQL server within the Cloudera Manager node.

    psql -U cloudera-scm -p 7432 -h localhost -d postgres

  3. Create a new database user, such as unravelka, grant the user READ access to the database, grant that user SELECT privileges on all tables in the Hive database, and give that user access from the Unravel server host.

    • For MySQL, run the following command to create a new user unravelka and grant it SELECT privilege on the Hive metastore database:

      GRANT SELECT ON hive.* to 'unravelk'@'%' identified by 'unravelk_password';
flush privileges;

    • For PostgreSQL, run the following commands:

      CREATE USER unravelk WITH ENCRYPTED PASSWORD 'unravelk_password';

GRANT CONNECT ON DATABASE hive TO unravelk;
GRANT USAGE ON SCHEMA public TO unravelk;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO unravelk;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO unravelk;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO unravelk;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO unravelk;

      Note

      The default PostgreSQL admin password created by Cloudera Manager is stored in /var/lib/cloudera-scm-server-db/data/generated_password.txt.

    • For Oracle, see Configuring access for an Oracle database.

  4. As the new user, use the conversational interface (MySQL, psql, Oracle, and so on) from Unravel Server to verify that the new user has access.

  5. In /usr/local/unravel/etc/unravel.properties, update Hive metastore access information with the read-only username and the values you retrieved in step 1.

    For example,

    javax.jdo.option.ConnectionURL=jdbc:mysql://congo.unraveldata.com/hive javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver 
javax.jdo.option.ConnectionUserName=unravelka
javax.jdo.option.ConnectionPassword=hadoop

    If you are using Microsoft JDBC Driver 7.0 for SQL Server set the properties as follows.

    javax.jdo.option.ConnectionURL=jdbc:sqlserver://jdbc_url
javax.jdo.option.ConnectionDriverName=com.microsoft.sqlserver.jdbc.SQLServerDriver
javax.jdo.option.ConnectionUserName=username
javax.jdo.option.ConnectionPassword=password

  6. Update /usr/local/unravel/etc/unravel.properties to use simple JDBC calls to get read-only details from Hive Metastore.

    com.unraveldata.metastore.use.jdbc=true

  7. Restart Unravel server.

    sudo /etc/init.d/unravel_all.sh restart

  8. Go to Unravel UI's Applications > Applications page to confirm that Hive queries are displayed. Approximately twenty-four hours after configuration the Reports > Data Insights > Details page displays a list of your Hive Metastore tables along with their KPIs and other details.

In this section: