--- 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: - http://gethue.tumblr.com/post/75496233379/how-to-manage-the-hue-database-with-the-shell tumblr_gethue_id: - 75496233379 sf_thumbnail_type: - none sf_thumbnail_link_type: - link_to_post sf_detail_type: - none sf_page_title: - 1 sf_page_title_style: - standard sf_no_breadcrumbs: - 1 sf_page_title_bg: - none sf_page_title_text_style: - light sf_background_image_size: - cover sf_social_sharing: - 1 sf_sidebar_config: - left-sidebar sf_left_sidebar: - Sidebar-2 sf_right_sidebar: - Sidebar-1 sf_caption_position: - caption-right slide_template: - default categories: - Administration - Development --- _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][1] details the migration process. The database settings in Hue are located in the [hue.ini][2]. 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 {{< 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. [1]: http://www.cloudera.com/content/cloudera-content/cloudera-docs/CDH5/latest/CDH5-Installation-Guide/cdh5ig_hue_database.html [2]: https://github.com/cloudera/hue/blob/branch-3.5/desktop/conf.dist/hue.ini#L185