How to manage the Hue database with the shell

How to manage the Hue database with the shell

Last update on March 9 2016

 

First, backup the database. By default this is this SqlLite file:

cp /var/lib/hue/desktop.db ~/

Then if using CM, export this variable in order to point to the correct database:

HUE_CONF_DIR=/var/run/cloudera-scm-agent/process/-hue-HUE_SERVER-id
echo $HUE_CONF_DIR
export HUE_CONF_DIR

Where <id> is the most recent ID in that process directory for hue-HUE_SERVER.

A quick way to get the correct directory is to use this script:

export HUE_CONF_DIR="/var/run/cloudera-scm-agent/process/`ls -alrt /var/run/cloudera-scm-agent/process | grep HUE | tail -1 | awk '{print $9}'`"

Then go in the Database. From the Hue root (/use/lib/hue by default):

[email protected]:hue# build/env/bin/hue dbshell

Note:
You might hit some permissions error about the logs:

build/env/bin/hue dbshell
Traceback (most recent call last):
  File "/opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/build/env/bin/hue", line 9, in <module>
    load_entry_point('desktop==3.6.0', 'console_scripts', 'hue')()
  File "/opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/desktop/core/src/desktop/manage_entry.py", line 41, in entry
    from desktop import settings, appmanager
  File "/opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/desktop/core/src/desktop/settings.py", line 55, in <module>
    desktop.log.basic_logging(os.environ[ENV_HUE_PROCESS_NAME])
  File "/opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/desktop/core/src/desktop/log/__init__.py", line 146, in basic_logging
    logging.config.fileConfig(log_conf)
  File "/usr/lib/python2.7/logging/config.py", line 78, in fileConfig
    handlers = _install_handlers(cp, formatters)
  File "/usr/lib/python2.7/logging/config.py", line 156, in _install_handlers
    h = klass(*args)
  File "/usr/lib/python2.7/logging/handlers.py", line 118, in __init__
    BaseRotatingHandler.__init__(self, filename, mode, encoding, delay)
  File "/usr/lib/python2.7/logging/handlers.py", line 65, in __init__
    logging.FileHandler.__init__(self, filename, mode, encoding, delay)
  File "/usr/lib/python2.7/logging/__init__.py", line 897, in __init__
    StreamHandler.__init__(self, self._open())
  File "/usr/lib/python2.7/logging/__init__.py", line 916, in _open
    stream = open(self.baseFilename, self.mode)
IOError: [Errno 13] Permission denied: '/tmp/logs/dbshell.log'

A “workaround” is to run the command as root:

sudo HUE_CONF_DIR=/var/run/cloudera-scm-agent/process/9679-hue-HUE_SERVER /opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hue/build/env/bin/hue dbshell

And you can start typing SQL queries:

sqlite> .tables
auth_group                         oozie_dataset
auth_group_permissions             oozie_decision
auth_permission                    oozie_decisionend
auth_user                          oozie_distcp
auth_user_groups                   oozie_email
auth_user_user_permissions         oozie_end
beeswax_metainstall                oozie_fork
beeswax_queryhistory               oozie_fs
beeswax_savedquery                 oozie_generic
beeswax_session                    oozie_history
desktop_document                   oozie_hive
desktop_document_tags              oozie_java
desktop_documentpermission         oozie_job
desktop_documentpermission_groups  oozie_join
desktop_documentpermission_users   oozie_kill
desktop_documenttag                oozie_link
desktop_settings                   oozie_mapreduce
desktop_userpreferences            oozie_node
django_admin_log                   oozie_pig
django_content_type                oozie_shell
django_openid_auth_association     oozie_sqoop
django_openid_auth_nonce           oozie_ssh
django_openid_auth_useropenid      oozie_start
django_session                     oozie_streaming
django_site                        oozie_subworkflow
jobsub_checkforsetup               oozie_workflow
jobsub_jobdesign                   pig_document
jobsub_jobhistory                  pig_pigscript
jobsub_oozieaction                 search_collection
jobsub_ooziedesign                 search_facet
jobsub_ooziejavaaction             search_result
jobsub_ooziemapreduceaction        search_sorting
jobsub_ooziestreamingaction        south_migrationhistory
oozie_bundle                       useradmin_grouppermission
oozie_bundledcoordinator           useradmin_huepermission
oozie_coordinator                  useradmin_ldapgroup
oozie_datainput                    useradmin_userprofile
oozie_dataoutput

Or migrating the database manually:

build/env/bin/hue syncdb
build/env/bin/hue migrate

If you want to switch to another database (we recommend MySql), this guide details the migration process.

The database settings in Hue are located in the hue.ini.

Note, you also query the database by pointing the DB Query App to the Hue database.

In developer mode (runserver command), you can also access the /admin page for using the Django Admin.

Examples

Transfer Oozie workflows belonging to the user Bob to Joe.

until Hue 3.8

# First move the objects
from oozie.models import Job
from django.contrib.auth.models import User

u1 = User.objects.get(username='bob')
u2 = User.objects.get(username='joe')

Job.objects.filter(owner=u1)
> [<Job: MyWf - bob>]

Job.objects.filter(owner=u1).update(owner=u2)
> 1

Job.objects.filter(owner=u1)
> []

Job.objects.filter(owner=u2)
> [<Job: MyWf - joe>]

wfs = Job.objects.filter(owner=u2)

For Hue 3.9+

# First move the objects
from desktop.models import Document2
from django.contrib.auth.models import User

u1 = User.objects.get(username='bob')
u2 = User.objects.get(username='joe')

Document2.objects.filter(owner=u1, type='oozie-workflow2')
> [<Document2: MyWf - bob>]

Document2.objects.filter(owner=u1, type='oozie-workflow2').update(owner=u2)
> 1

Document2.objects.filter(owner=u1, type='oozie-workflow2')
> []

Document2.objects.filter(owner=u2, type='oozie-workflow2')
> [<Document2: MyWf - joe>]

wfs = Document2.objects.filter(owner=u2, type='oozie-workflow2')

For both

# The list of ALL the workflows (will also list the already known ones) of the second user

# Then move the documents
from desktop.models import Document

Document.objects.filter(object_id__in=wfs)
> [<Document: workflow MyWf bob>]

Document.objects.filter(object_id__in=wfs)
> [<Document: workflow MyWf bob>]

Document.objects.filter(object_id__in=wfs).update(owner=u2)
> [<Document: workflow MyWf joe>]

Note: it will change again in Hue 3.10 and be easier.

9 Comments

  1. Miles Y. 2 years ago

    Very helpful!
    Can this be updated for Hue 3.9/CDH 5.5?

    This can also open up Hue for automated admin and application deployment. Would love to see some examples/use cases for implementing application development and operational processes.

    • Hue Team 2 years ago

      This is the same or very similar in Hue 3.9/CDH5.5 AFAIK!

  2. Nikolai Koustov 1 year ago

    I am getting an error when trying this on CDH 5.5 QuickStart VM:

    sudo HUE_CONF_DIR=”/var/run/cloudera-scm-agent/process/`sudo ls -alrt /var/run/cloudera-scm-agent/process | grep HUE | tail -1 | awk ‘{ print $9 }’`” /usr/lib/hue/build/env/bin/hue dbshell
    Traceback (most recent call last):
    File “/usr/lib/hue/build/env/bin/hue”, line 12, in
    load_entry_point(‘desktop==3.9.0’, ‘console_scripts’, ‘hue’)()
    File “/usr/lib/hue/desktop/core/src/desktop/manage_entry.py”, line 57, in entry
    execute_from_command_line(sys.argv)
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.6.10-py2.6.egg/django/core/management/__init__.py”, line 399, in execute_from_command_line
    utility.execute()
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.6.10-py2.6.egg/django/core/management/__init__.py”, line 392, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.6.10-py2.6.egg/django/core/management/__init__.py”, line 261, in fetch_command
    commands = get_commands()
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.6.10-py2.6.egg/django/core/management/__init__.py”, line 107, in get_commands
    apps = settings.INSTALLED_APPS
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.6.10-py2.6.egg/django/conf/__init__.py”, line 54, in __getattr__
    self._setup(name)
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.6.10-py2.6.egg/django/conf/__init__.py”, line 49, in _setup
    self._wrapped = Settings(settings_module)
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.6.10-py2.6.egg/django/conf/__init__.py”, line 128, in __init__
    mod = importlib.import_module(self.SETTINGS_MODULE)
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.6.10-py2.6.egg/django/utils/importlib.py”, line 40, in import_module
    __import__(name)
    File “/usr/lib/hue/desktop/core/src/desktop/settings.py”, line 309, in
    “PASSWORD” : desktop.conf.get_database_password(),
    File “/usr/lib/hue/desktop/core/src/desktop/conf.py”, line 1219, in get_database_password
    password = DATABASE.PASSWORD_SCRIPT.get()
    File “/usr/lib/hue/desktop/core/src/desktop/lib/conf.py”, line 140, in get
    return self.config.get_value(data, present=present, prefix=self.prefix, coerce_type=True)
    File “/usr/lib/hue/desktop/core/src/desktop/lib/conf.py”, line 256, in get_value
    return self._coerce_type(raw_val, prefix)
    File “/usr/lib/hue/desktop/core/src/desktop/lib/conf.py”, line 276, in _coerce_type
    return self.type(raw)
    File “/usr/lib/hue/desktop/core/src/desktop/conf.py”, line 66, in coerce_password_from_script
    raise subprocess.CalledProcessError(p.returncode, script)
    subprocess.CalledProcessError: Command ‘/var/run/cloudera-scm-agent/process/50-hue-HUE_SERVER/altscript.sh sec-5-password’ returned non-zero exit status 126

  3. Tej 1 year ago

    How to change ownership for particular workflows created in Oozie? As the person has left the organization now I need to transfer the ownership to another user ( ownership of files under it also to be transferred).

    • Hue Team 1 year ago

      There is currently no easy way. Workflows could be shared with everybody, could be exported then re-imported as another user, or the owner fields of the Document2 of the oozie workflow modified to point to another user.

      The good news though is that Hue 4 will propose it out of the box.

  4. Reetika 3 months ago

    What can be the issue/reason,I have got HUE-3.12 installed.I am able to run hive queries and access file browser as expected but I login to hue config check page is not coming , it shows nothing in that page?

    • Author
      Hue Team 3 months ago

      This article http://gethue.com/solr-search-ui-only/ shows how to configure Hue to not show certain apps. The list of all the apps is available on the /desktop/dump_config page of Hue.

      You could try to remove ‘hbase’ that might get stuck in your case.

Leave a reply

Your email address will not be published. Required fields are marked *

*