Incubator-superset: Hive - SQLlab error: [Errno 61] Connection refused

[alvarojoao]

Make sure these boxes are checked before submitting your issue - thank you!

• [x] I have checked the superset logs for python stacktraces and included it here as text if any
• [x] I have reproduced the issue with at least the latest released version of superset
• [x] I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

{
"GIT_SHA": "",
"version": "0.15.4"
}

Expected results

The data from query

Actual results

This Appears inside the data box:

---

Sorry, something went wrong

500 - Internal Server Error

---

Stacktrace

---

 Traceback (most recent call last): File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app response = self.full_dispatch_request() File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request rv = self.handle_user_exception(e) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request return self.view_functionsrule.endpoint File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/flask_appbuilder/security/decorators.py", line 52, in wraps return f(self, args, kwargs) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/superset/models.py", line 2590, in wrapper value = f(args, *kwargs) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/superset/views.py", line 2503, in sql_json store_results=not query.select_as_cta) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/celery/app/task.py", line 453, in delay return self.apply_async(args, kwargs) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/celery/app/task.py", line 565, in apply_async *dict(self._get_exec_options(), *options) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/celery/app/base.py", line 354, in send_task reply_to=reply_to or self.oid, *options File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/celery/app/amqp.py", line 310, in publish_task *kwargs File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/kombu/messaging.py", line 172, in publish routing_key, mandatory, immediate, exchange, declare) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/kombu/connection.py", line 470, in _ensured interval_max) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/kombu/connection.py", line 382, in ensure_connection interval_start, interval_step, interval_max, callback) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/kombu/utils/__init__.py", line 246, in retry_over_time return fun(args, **kwargs) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/kombu/connection.py", line 250, in connect return self.connection File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/kombu/connection.py", line 756, in connection self._connection = self._establish_connection() File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/kombu/connection.py", line 711, in _establish_connection conn = self.transport.establish_connection() File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/kombu/transport/pyamqp.py", line 116, in establish_connection conn = self.Connection(opts) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/amqp/connection.py", line 165, in __init__ self.transport = self.Transport(host, connect_timeout, ssl) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/amqp/connection.py", line 186, in Transport return create_transport(host, connect_timeout, ssl) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/amqp/transport.py", line 299, in create_transport return TCPTransport(host, connect_timeout) File "/Users/asilvino/superset/venv/lib/python2.7/site-packages/amqp/transport.py", line 95, in __init__ raise socket.error(last_err) error: [Errno 61] Connection refused 

---

p.s.: Not HTML processed.

Steps to reproduce

1 - I connected to my hive database, tested the connection, seems OK.
2 - Create a table from a already existing table in the database.
3 - Go to SQL Lab tab.
4 - Try to query anything from the table the error pops up.
4.1 - The preview works.

[SQLadmin]

For me too

[sungjuly]

you guys need to set up HiveServer2 first, the superset leverages pyHive lib(https://github.com/dropbox/PyHive) from Dropbox, that tool is just client lib for HiveServer2. And then please refer this line to configure Hive url on superset!
https://github.com/airbnb/superset/blob/master/tests/model_tests.py#L34

[mistercrunch]

Make sure you put the right authentication mechanism in your connection.
Related: https://github.com/dropbox/PyHive/issues/40

[denmak]

the same with me with MS SQL DB .
preview works fine, but Run Query button rise error similar to mentioned above:

/root/venv/local/lib/python2.7/site-packages/amqp/connection.py", line 186, in Transport return create_transport(host, connect_timeout, ssl) File "/root/venv/local/lib/python2.7/site-packages/amqp/transport.py", line 299, in create_transport return TCPTransport(host, connect_timeout) File "/root/venv/local/lib/python2.7/site-packages/amqp/transport.py", line 95, in __init__ raise socket.error(last_err) error: [Errno 111] Connection refused

it seems like a wrong configuration of Celery (Distributed task queue). This part missed in documentation, may be it needs in some configuration after installation?

[mistercrunch]

Oh disregard my comment above, this has to do with amqp, which is Celery/RabbitMQ/Redis related. You can set the DB to only support synchronous query to test independently from Celery.

I also think @sungjuly were off target as the error message was misleading.

[evinhas]

My error is with an Impala connection.

It seems very weird, because the connection works when you create it, but it seems that something is wrong when it is used inside the "SQL Labs" for running a query. I could preview the table, but as soon as I am trying to run a custom query, the error is raising.

I have checked all the configuration in Impala/Hive/Hadoop and it is OK (as I said before, the connection works when it is being created, and the tables are retrieved), but you cannot run any query with this Impala connection.

In my case, it is a kerberos-secured cluster.

[hungrytortoise]

i meet the same issue, have you solved that?
@denmak

[evinhas]

You should use Celery and Redis for running queries pressing the button. I can share the details if you are interested.

[alvarojoao]

Please ! It would be perfect

On 19 de abr de 2017, at 01:21, Eduardo Vinhas [e-mail] wrote:

You should use Celery and Redis for running queries pressing the button. I can share the details if you are interested.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or mute the thread.

[mistercrunch]

Celery is only required where Allow Run Async is checked. Knowing that a typical web request timeout is < 1 minute, you need to run queries outside of a web request scope if you want to run queries that take longer than that. And with connections to databases like Impala, Presto or Hive, you typically want that. For small OLTP-type applications, you can usually get away with the non-async setup.

If you check Allow Run Async, it assumes you have Celery setup. Essentially this means that you have configured a message queue and starter one or many superset worker processes.

[evinhas]

As mistercrunch was saying, when you try to run queries pressing the button to run them as "async", you should first install Celery and any broker, for instance Redis (I tested with Redis and it worked).

Steps:

1. in your virtualenv, install "celeris"

#Install Celery in python
pip install celery

1. install redis. In my case, I installed it in the same machine, but it could be installed separately. In my case, I am using red-hat 7 as OS:

# Install the EPEL repository
rpm -Uvh dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-*.rpm

#Install redis
yum install redis

1. Start Redis (note: it depends on your OS, in my case, it is Red-Hat):
   

   Start the Redis Service

systemctl start redis.service

1. Back to Python, and install the additional dependency to be able to use Redis in Celeris
   pip install -U "celery[redis]"

2. Now, you should make some changes in the Celeris configuration. So you should edit the superset_config.py file in the following directory:
   <path_to_your_virtualenv>/lib/python2.7/site-packages/superset/static/assets/superset_config.py
   Note: In my case, I've created the virtualenv "venv" in my home directory:
   /home/my_user/venv/lib/python2.7/site-packages/superset/static/assets/superset_config.py
   Note: after updating the config file, you should compile the superset_config.py file to get a *.pyc file running the following commands in python:

>>> import py_compile
>>> py_compile.compile('<path_to_your_virtualenv>/lib/python2.7/site-packages/superset/static/assets/superset_config.py')

1. Make the following changes (bear in mind that in my case, I've installed redis in the same machine, in the port by default 6379. If you have done in a diffferent hostname/port, replace "localhost:6379" by your values):

# Configure the class "CeleryConfig", by adding the URL of your Redis installation (in my case, localhost:6379):
class CeleryConfig(object):
  BROKER_URL =  'redis://localhost:6379/'
  CELERY_IMPORTS = ('superset.sql_lab', )
  CELERY_RESULT_BACKEND =  'redis://localhost:6379/'
  CELERY_ANNOTATIONS = {'tasks.add': {'rate_limit': '10/s'}}
CELERY_CONFIG = CeleryConfig
HTTP_HEADERS = {
    'super': 'header!'
}

# comment the current RESULTS_BACKEND value
#RESULTS_BACKEND = None

# assign a new value to RESULTS_BACKEND
RESULTS_BACKEND = FileSystemCache('/tmp/sqllab_cache', default_timeout=60*24*7)

Save the changes and restart.

1. It is ready. You should run the celeris worker. It can be done running the following command inside your virtualenv:
   superset worker

2. Start the Superset server running in your virtualenv the command is below:
   superset runserver

Now, when you run any async query (pressing the button), Superset will make use of Celery, and the results of your query will be retrieved using Celery and Redis.

As soon as I have some spare time, I will update the Superset installation document with this info.

[brozena]

Super valuable. Thank you for posting this.

[mistercrunch]

Notice: this issue has been closed because it has been inactive for 350 days. Feel free to comment and request for this issue to be reopened.