question Error while querying HIve tables from HUE in Support Questions

Error while querying HIve tables from HUE

bhara — Wed, 29 Apr 2020 14:20:14 GMT

Team ,

We are seeing below errors when querying hive from HUE .

Error 1 :-

[29/Apr/2020 07:07:22 -0700] middleware ERROR Error saving profile information
Traceback (most recent call last):
File "/usr/local/hue/apps/useradmin/src/useradmin/middleware.py", line 104, in process_request
profile.save()
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/base.py", line 808, in save
force_update=force_update, update_fields=update_fields)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/base.py", line 838, in save_base
updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/base.py", line 905, in _save_table
forced_update)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/base.py", line 955, in _do_update
return filtered._update(values) > 0
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 667, in _update
return query.get_compiler(self.db).execute_sql(CURSOR)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/sql/compiler.py", line 1204, in execute_sql
cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/sql/compiler.py", line 899, in execute_sql
raise original_exception
OperationalError: database is locked

ERROR 2:-

[29/Apr/2020 07:06:51 -0700] exception ERROR Internal Server Error: /hue/accounts/login
Traceback (most recent call last):
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/core/handlers/exception.py", line 41, in inner
response = get_response(request)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/core/handlers/base.py", line 249, in _legacy_get_response
response = self._get_response(request)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/core/handlers/base.py", line 187, in _get_response
response = self.process_exception_by_middleware(e, request)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/core/handlers/base.py", line 185, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/utils/decorators.py", line 185, in inner
return func(*args, **kwargs)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/django_axes-2.2.0-py2.7.egg/axes/decorators.py", line 271, in decorated_login
response = func(request, *args, **kwargs)
File "/usr/local/hue/desktop/core/src/desktop/auth/views.py", line 206, in dt_login
'user': request.user
File "/usr/local/hue/desktop/core/src/desktop/lib/django_util.py", line 241, in render
**kwargs
File "/usr/local/hue/desktop/core/src/desktop/lib/django_util.py", line 154, in _render_to_response
return django_mako.render_to_response(template, *args, **kwargs)
File "/usr/local/hue/desktop/core/src/desktop/lib/django_mako.py", line 127, in render_to_response
return HttpResponse(render_to_string(template_name, data_dictionary), **kwargs)
File "/usr/local/hue/desktop/core/src/desktop/lib/django_mako.py", line 116, in render_to_string_normal
result = template.render(**data_dict)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Mako-1.0.7-py2.7.egg/mako/template.py", line 462, in render
return runtime._render(self, self.callable_, args, data)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Mako-1.0.7-py2.7.egg/mako/runtime.py", line 838, in _render
**_kwargs_for_callable(callable_, data))
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Mako-1.0.7-py2.7.egg/mako/runtime.py", line 873, in _render_context
_exec_template(inherit, lclcontext, args=args, kwargs=kwargs)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Mako-1.0.7-py2.7.egg/mako/runtime.py", line 899, in _exec_template
callable_(context, *args, **kwargs)
File "/tmp/tmpVKDTTV/desktop/login.mako.py", line 176, in render_body
__M_writer(unicode( commonfooter(None, messages) ))
File "/usr/local/hue/desktop/core/src/desktop/views.py", line 559, in commonfooter
hue_settings = Settings.get_settings()
File "/usr/local/hue/desktop/core/src/desktop/models.py", line 130, in get_settings
settings, created = Settings.objects.get_or_create(id=1)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 464, in get_or_create
return self.get(**lookup), False
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 374, in get
num = len(clone)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 232, in __len__
self._fetch_all()
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 1121, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 53, in __iter__
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/sql/compiler.py", line 899, in execute_sql
raise original_exception
TransactionManagementError: An error occurred in the current transaction. You can't execute queries until the end of the 'atomic' block.

Any help is appreciated .

Regards

Bharad

Re: Error while querying HIve tables from HUE

lwang — Wed, 29 Apr 2020 16:43:56 GMT

Hi @bhara ,

Both error messages points out you need to tune your Hue performance.

First thing you need to check if you are using the default SQLite DB. If so, you should move the database from the default database across to another database backend such as MySql/Postgres/Oracle, which handles locking better than the default SQLite DB. Hue should not be run on SQLite in an environment with more than 1 concurrent user. Here is the doc on how to switch the DB:

https://docs.cloudera.com/documentation/enterprise/5/latest/topics/hue_dbs_0.html

You can also check this Hue performance tuning guide for more information on tuning Hue like load balancing etc:

https://gethue.com/performance-tuning/

Thanks and hope this helps!

Re: Error while querying Hive tables from HUE

bhara — Wed, 29 Apr 2020 23:19:53 GMT

Hi @lwang ,

I have installed HUE through Ambari by following below document with the help of @stevenmatison

https://github.com/steven-dfheinz/HDP2-Hue4-Service

I was able to successfully install and bring up the services . I chose my metadata database as oracle as attached . But when i look into oracle database i don't see and metadata tables created . How do i check where Hue desktop is having its repository .

Regards

Bharad

Re: Error while querying Hive tables from HUE

lwang — Wed, 29 Apr 2020 18:22:53 GMT

Hi @bhara,

You can check the Hue configuration from Hue UI to confirm if Hue is using Oracle database.

Login as Hue admin user
change the URL to https://<hue-host>:8888/hue/desktop/dump_config
Then click desktop section
Then search database from the page

I attached a couple screenshots for above step 3 and 4.

Step 3 screenshot:

desktop section

Step 4 screenshot:

database configurations

Thanks,

Re: Error while querying HIve tables from HUE

stevenmatison — Wed, 29 Apr 2020 20:08:36 GMT

@bhara The error is very specific "OperationalError: database is locked"

Have you checked the database.table you are querying is locked or not?
If the error is indeed from the oracle metastore, is that database or table locked?

You could test another query on another table as well, to see if it is just that table locked. After removing the lock, does the query work again? Sometimes hive can lock tables, and never unlock them. You would need to query the hive metastore directly, and manually remove these locks if they persist.

Re: Error while querying Hive tables from HUE

bhara — Wed, 29 Apr 2020 23:20:59 GMT

Hi @lwang

I was able to get the details . But when i configure my oracle database i am seeing below error .I dont see any tables were also create in oracle metadata db .

[29/Apr/2020 12:36:36 +0000] exception ERROR Internal Server Error: /
Traceback (most recent call last):
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/core/handlers/exception.py", line 41, in inner
response = get_response(request)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/core/handlers/base.py", line 244, in _legacy_get_response
response = middleware_method(request)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/middleware/locale.py", line 25, in process_request
language = translation.get_language_from_request(request, check_path=i18n_patterns_used)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/utils/translation/__init__.py", line 211, in get_language_from_request
return _trans.get_language_from_request(request, check_path)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/utils/translation/trans_real.py", line 519, in get_language_from_request
lang_code = request.session.get(LANGUAGE_SESSION_KEY)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/contrib/sessions/backends/base.py", line 68, in get
return self._session.get(key, default)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/contrib/sessions/backends/base.py", line 207, in _get_session
self._session_cache = self.load()
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/contrib/sessions/backends/db.py", line 35, in load
expire_date__gt=timezone.now()
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 374, in get
num = len(clone)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 232, in __len__
self._fetch_all()
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 1121, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/query.py", line 53, in __iter__
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
File "/usr/local/hue/build/env/lib/python2.7/site-packages/Django-1.11.22-py2.7.egg/django/db/models/sql/compiler.py", line 899, in execute_sql
raise original_exception
DatabaseError: ORA-00942: table or view does not exist

Below are my databse detaisl i put in hue.ini

[[database]]
# Database engine is typically one of:
# postgresql_psycopg2, mysql, sqlite3 or oracle.
#
# Note that for sqlite3, 'name', below is a path to the filename. For other backends, it is the database name
# Note for Oracle, options={"threaded":true} must be set in order to avoid crashes.
# Note for Oracle, you can use the Oracle Service Name by setting "host=" and "port=" and then "name=(host):(port)/(service_name)".
# Note for MariaDB use the 'mysql' engine.
engine=oracle
host=
port=0
user=hue
password=xxxxx
# conn_max_age option to make database connection persistent value in seconds
# https://docs.djangoproject.com/en/1.11/ref/databases/#persistent-connections
## conn_max_age=0
# Execute this script to produce the database password. This will be used when 'password' is not set.
## password_script=/path/script
name=
options={"threaded":true}
# Database schema, to be used only when public schema is revoked in postgres
## schema=public

and i see the below coming up when i open hue UI with error page .

DATABASES

Re: Error while querying HIve tables from HUE

bhara — Wed, 29 Apr 2020 20:15:55 GMT

Hi @stevenmatison ,

I was able to run the same query from hive ,but from hue i see database lock . When i did some research i got to know that hue stores the user requests in metadata database . so when i checked repository data base i dont see any tables created . so i tried configuring metadata repository by configuring to oracle database and i am running into issues (in the above post ) .

Regards

Bharad

Re: Error while querying HIve tables from HUE

stevenmatison — Wed, 29 Apr 2020 20:22:27 GMT

Yah it seems that error is usually related to the stock db. If you can't see any tables in the oracle, then after configure for oracle and restart it must not have installed the metadata tables. You will need to get them installed and I suspect the lock error will go away.

Check here for some oracle steps:

https://docs.gethue.com/administrator/administration/database/#oracle

Re: Error while querying HIve tables from HUE

bhara — Wed, 29 Apr 2020 20:59:30 GMT

Hi @stevenmatison i am trying to get the backup of sqllite database to import into oracle database as i dont have database dump from ambari .

Regards

Bharad

Re: Error while querying HIve tables from HUE

stevenmatison — Thu, 30 Apr 2020 14:35:57 GMT

@bhara did you follow all the steps to use Oracle?

Re: Error while querying HIve tables from HUE

DarveshK — Fri, 26 Jun 2020 16:20:05 GMT

Hi,

We are also getting the same error almost every week for any database. Can we get any permanent solution for this? After waiting for sometime it get resolved itself. But i want to know the root cause for this and if it database or table gets locked, how i can release that lock for those DB and table in MySQL database.