title: How to manage the Hue database with the shell author: admin type: post date: 2014-02-03T17:59:00+00:00 url: /how-to-manage-the-hue-database-with-the-shell/ tumblr_gethue_permalink:
Last update on March 9 2016
First, backup the database. By default this is this SqlLite file:
{{< highlight bash >}}cp /var/lib/hue/desktop.db ~/{{< /highlight >}}
Then if using CM, export this variable in order to point to the correct database:
{{< highlight bash >}}HUE_CONF_DIR=/var/run/cloudera-scm-agent/process/-hue-HUE_SERVER-id
echo $HUE_CONF_DIR
export HUE_CONF_DIR{{< /highlight >}}
Where 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:
{{< highlight bash >}}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}'`"{{< /highlight >}}
Then go in the Database. From the Hue root (/use/lib/hue by default):
{{< highlight bash >}}root@hue:hue# build/env/bin/hue dbshell{{< /highlight >}}
Note:
You might hit some permissions error about the logs:
{{< highlight bash >}}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
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
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'
{{< /highlight >}}
A "workaround" is to run the command as root:
{{< highlight bash >}}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{{< /highlight >}}
And you can start typing SQL queries:
{{< highlight bash >}}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{{< /highlight >}}
Or migrating the database manually:
{{< highlight bash >}}build/env/bin/hue syncdb
build/env/bin/hue migrate{{< /highlight >}}
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.
Transfer Oozie workflows belonging to the user Bob to Joe.
until Hue 3.8
{{< highlight bash >}}# 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.objects.filter(owner=u1).update(owner=u2)
1
Job.objects.filter(owner=u1)
[]
Job.objects.filter(owner=u2)
[]
wfs = Job.objects.filter(owner=u2)
{{< /highlight >}}
For Hue 3.9+
{{< highlight bash >}}# 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.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')
[]
wfs = Document2.objects.filter(owner=u2, type='oozie-workflow2')
{{< /highlight >}}
For both
{{< highlight bash >}}# 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.objects.filter(object_id__in=wfs)
[]
Document.objects.filter(object_id__in=wfs).update(owner=u2)
[]
{{< /highlight >}}
Note: it will change again in Hue 3.10 and be easier.