Incubator-superset: drill sqlAlchemt url

[ISLEMNEDDAR]

i wanted to use drill in superset but after writting the url : drill+sadrill://localhost:8047/mongo/test
but i had the problem : Invalid driver Can't load plugin: sqlalchemy.dialects:drill.sadrill
meanwhile i unstalled https://github.com/JohnOmernik/sqlalchemy-drill that can help but same problem
ubutnu18.04
apache-superset 0.36.0
how can i handle it ?

[issue-label-bot[bot]]

Issue-Label Bot is automatically applying the label #question to this issue, with a confidence of 0.70. Please mark this comment with :thumbsup: or :thumbsdown: to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[villebro]

Ping @cgivre

[cgivre]

@ISLEMNEDDAR
This is looking like somehow the dialect is not installed correctly or in your python environment. Can you try running some of the test scripts in the drill dialect and see if those work?

[ISLEMNEDDAR]

@cgivre
you are right
the python setup.py install does not install inside venv i dont know why ?
do you have an idea ?
and after your response i will close the issue because it's not a reallt an issue

[ISLEMNEDDAR]

thank you for help
it's solved

[omejiasq]

Hi guys, i have the same issue: ERROR:root:Can't load plugin: sqlalchemy.dialects:drill.sadrill , can anybody tell me please how can i to fix it?

[omejiasq]

@cgivre
you are right
the python setup.py install does not install inside venv i dont know why ?
do you have an idea ?
and after your response i will close the issue because it's not a reallt an issue

Hello Islemneddar, can you share me please, what did you to fix it?

[cgivre]

@omejiasq
You have to start the virtual environment first:

source venv/bin/activate

Then, only after you've activated the virtual environment, install the sqlalchemy-drill driver. This is now available on Pypi and can be installed with:

pip install sqlalchemy-drill

[omejiasq]

@omejiasq
You have to start the virtual environment first:

source venv/bin/activate

Then, only after you've activated the virtual environment, install the sqlalchemy-drill driver. This is now available on Pypi and can be installed with:

pip install sqlalchemy-drill

Hello cgvre thank you. I did what you told me, but when i run the command pip install sqlalchemy-drill, i caught some issues. I show this log:
Building wheels for collected packages: sqlalchemy-drill
Running setup.py bdist_wheel for sqlalchemy-drill ... error
Complete output from command /home/edgarubuntustudio/venv/bin/python3 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-dezp45fe/sqlalchemy-drill/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" bdist_wheel -d /tmp/tmpc57728k8pip-wheel- --python-tag cp36:
/usr/lib/python3.6/distutils/dist.py:261: UserWarning: Unknown distribution option: 'long_description_content_type'
warnings.warn(msg)
usage: -c [global_opts] cmd1 [cmd1_opts] [cmd2 [cmd2_opts] ...]
or: -c --help [cmd1 cmd2 ...]
or: -c --help-commands
or: -c cmd --help

error: invalid command 'bdist_wheel'

---

Failed building wheel for sqlalchemy-drill
Running setup.py clean for sqlalchemy-drill
Failed to build sqlalchemy-drill
Installing collected packages: sqlalchemy-drill
Running setup.py install for sqlalchemy-drill ... done
Successfully installed sqlalchemy-drill-0.1.dev0

After of this i restart superset and set this SQLAlchemy URI:drill+sadrill://localhost:8047/dfs?use_ssl=False and when i click over test Connection button i get this error:AttributeError: module 'sqlalchemy_drill.drilldbapi' has no attribute 'Error' do you know please how can to fix it?

[cgivre]

ok.. this is a bug on the version on Pypi. I will fix this today.
In the meantime, you should be able to do the following:

1. source venv/bin/activate
2. Clone or d/l the Drill dialect from github
3. Navigate to the sqlalchemy-drill directory
4. python setup.py install

Just make sure you are in the correct virtual environment when you run the last step and it should work.
-- C

On Aug 9, 2020, at 10:45 AM, omejiasq [e-mail] wrote:

@omejiasq https://github.com/omejiasq
You have to start the virtual environment first:

source venv/bin/activate
Then, only after you've activated the virtual environment, install the sqlalchemy-drill driver. This is now available on Pypi and can be installed with:

pip install sqlalchemy-drill
Hello cgvre thank you. I did what you told me, but when i run the command pip install sqlalchemy-drill, i caught some issues. I show this log:
Building wheels for collected packages: sqlalchemy-drill
Running setup.py bdist_wheel for sqlalchemy-drill ... error
Complete output from command /home/edgarubuntustudio/venv/bin/python3 -u -c "import setuptools, tokenize;file='/tmp/pip-build-dezp45fe/sqlalchemy-drill/setup.py';f=getattr(tokenize, 'open', open)(file);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, file, 'exec'))" bdist_wheel -d /tmp/tmpc57728k8pip-wheel- --python-tag cp36:
/usr/lib/python3.6/distutils/dist.py:261: UserWarning: Unknown distribution option: 'long_description_content_type'
warnings.warn(msg)
usage: -c [global_opts] cmd1 [cmd1_opts] [cmd2 [cmd2_opts] ...]
or: -c --help [cmd1 cmd2 ...]
or: -c --help-commands
or: -c cmd --help

error: invalid command 'bdist_wheel'

Failed building wheel for sqlalchemy-drill
Running setup.py clean for sqlalchemy-drill
Failed to build sqlalchemy-drill
Installing collected packages: sqlalchemy-drill
Running setup.py install for sqlalchemy-drill ... done
Successfully installed sqlalchemy-drill-0.1.dev0

After of this i restart superset and set this SQLAlchemy URI:drill+sadrill://localhost:8047/dfs?use_ssl=False and when i click over test Connection button i get this error:AttributeError: module 'sqlalchemy_drill.drilldbapi' has no attribute 'Error' do you know please how can to fix it?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub https://github.com/apache/incubator-superset/issues/10032#issuecomment-671060665, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABKB7PXMO6BULH5CJOZU2CTR72ZBBANCNFSM4N2ULOUA.

[omejiasq]

Thank you cgivre, now i get this issue:
sudo python setup.py install
[sudo] password for edgarubuntustudio:
Traceback (most recent call last):
File "setup.py", line 25, in
with open(path.join(this_directory, 'README.md'), encoding='utf-8') as f:
TypeError: 'encoding' is an invalid keyword argument for this function

How can i to fix it?

[cgivre]

What version of python are you using?
I apologize for all these errors and thank you for working with me to get these fixed.

On Aug 9, 2020, at 10:52 AM, omejiasq [e-mail] wrote:

Thank you cgivre, now i get this issue:
sudo python setup.py install
[sudo] password for edgarubuntustudio:
Traceback (most recent call last):
File "setup.py", line 25, in
with open(path.join(this_directory, 'README.md'), encoding='utf-8') as f:
TypeError: 'encoding' is an invalid keyword argument for this function

How can i to fix it?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub https://github.com/apache/incubator-superset/issues/10032#issuecomment-671061482, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABKB7PSDGRM4ISASH5QSQ23R72Z4XANCNFSM4N2ULOUA.

[omejiasq]

What version of python are you using? I apologize for all these errors and thank you for working with me to get these fixed.
Thank you to you,
I have version 3.6.9 of python. do I need to change my python version?

[omejiasq]

Cgivre, do i need to downgrade my python version? can you tell me please to which python version ?

[omejiasq]

ok.. this is a bug on the version on Pypi. I will fix this today. In the meantime, you should be able to do the following: 1. source venv/bin/activate 2. Clone or d/l the Drill dialect from github 3. Navigate to the sqlalchemy-drill directory 4. python setup.py install Just make sure you are in the correct virtual environment when you run the last step and it should work. -- C
…
On Aug 9, 2020, at 10:45 AM, omejiasq @.*> wrote: @omejiasq https://github.com/omejiasq You have to start the virtual environment first: source venv/bin/activate Then, only after you've activated the virtual environment, install the sqlalchemy-drill driver. This is now available on Pypi and can be installed with: pip install sqlalchemy-drill Hello cgvre thank you. I did what you told me, but when i run the command pip install sqlalchemy-drill, i caught some issues. I show this log: Building wheels for collected packages: sqlalchemy-drill Running setup.py bdist_wheel for sqlalchemy-drill ... error Complete output from command /home/edgarubuntustudio/venv/bin/python3 -u -c "import setuptools, tokenize;file='/tmp/pip-build-dezp45fe/sqlalchemy-drill/setup.py';f=getattr(tokenize, 'open', open)(file);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, file, 'exec'))" bdist_wheel -d /tmp/tmpc57728k8pip-wheel- --python-tag cp36: /usr/lib/python3.6/distutils/dist.py:261: UserWarning: Unknown distribution option: 'long_description_content_type' warnings.warn(msg) usage: -c [global_opts] cmd1 [cmd1_opts] [cmd2 [cmd2_opts] ...] or: -c --help [cmd1 cmd2 ...] or: -c --help-commands or: -c cmd --help error: invalid command 'bdist_wheel' Failed building wheel for sqlalchemy-drill Running setup.py clean for sqlalchemy-drill Failed to build sqlalchemy-drill Installing collected packages: sqlalchemy-drill Running setup.py install for sqlalchemy-drill ... done Successfully installed sqlalchemy-drill-0.1.dev0 After of this i restart superset and set this SQLAlchemy URI:drill+sadrill://localhost:8047/dfs?use_ssl=False and when i click over test Connection button i get this error:AttributeError: module 'sqlalchemy_drill.drilldbapi' has no attribute 'Error' do you know please how can to fix it? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#10032 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABKB7PXMO6BULH5CJOZU2CTR72ZBBANCNFSM4N2ULOUA.

Hello Cgivre, do you have anything to this issue please?

Thank you.

[cgivre]

@omejiasq
I just pushed an update with a few other bug fixes. I tested this on my local machine and was able to install the new version from pip without issues. Please let me know if you have any problems.

[omejiasq]

Hello cgivre,
I uninstalled sqlalchemy-drill of this way: pip uninstall sqlalchemy-drill, after i installed again with this command:pip install sqlalchemy-drill of this way:pip install sqlalchemy-drill (in this case I did not see any errors in the installation.).
But i get the same error: AttributeError: module 'sqlalchemy_drill.drilldbapi' has no attribute 'Error' . I send you a complete log of superset:

---

2020-08-16 13:31:18,186:DEBUG:urllib3.connectionpool:http://localhost:8047 "POST /query.json HTTP/1.1" 200 7674
2020-08-16 13:31:18,189:DEBUG:root:Mapping column name of Drill type VARCHAR to dtype object
2020-08-16 13:31:18,192:DEBUG:root:Mapping column isDirectory of Drill type BIT to dtype boolean
2020-08-16 13:31:18,196:ERROR:root:module 'sqlalchemy_drill.drilldbapi' has no attribute 'Error'
Traceback (most recent call last):
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1236, in _execute_context
cursor, statement, parameters, context
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 536, in do_execute
cursor.execute(statement, parameters)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy_drill/drilldbapi/_drilldbapi.py", line 69, in func_wrapper
return func(self, args, kwargs)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy_drill/drilldbapi/_drilldbapi.py", line 186, in execute
df[col_name] = df[col_name].astype(col_dtype)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/pandas/util/_decorators.py", line 178, in wrapper
return func(args, *kwargs)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/pandas/core/generic.py", line 5001, in astype
*kwargs)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/pandas/core/internals.py", line 3714, in astype
return self.apply('astype', dtype=dtype, *kwargs)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/pandas/core/internals.py", line 3581, in apply
applied = getattr(b, f)(kwargs)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/pandas/core/internals.py", line 575, in astype
*kwargs)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/pandas/core/internals.py", line 636, in _astype
dtype = np.dtype(dtype)
TypeError: data type "boolean" not understood

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/superset/views/core.py", line 1781, in testconn
return json_success(json.dumps(engine.table_names(), indent=4))
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2209, in table_names
return self.dialect.get_table_names(conn, schema)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy_drill/base.py", line 283, in get_table_names
curs = connection.execute("SHOW FILES FROM " + quoted_schema)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 974, in execute
return self._execute_text(object_, multiparams, params)
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1147, in _execute_text
parameters,
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1240, in _execute_context
e, statement, parameters, cursor, context
File "/home/edgarubuntustudio/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1347, in _handle_dbapi_exception
isinstance(e, self.dialect.dbapi.Error)
AttributeError: module 'sqlalchemy_drill.drilldbapi' has no attribute 'Error'

^C[2020-08-16 13:36:31 -0500] [2692] [INFO] Handling signal: int

My server has the following characteristics:

1. Ubuntu 18.04.5
2. Superset installed. I start superset of this way:
   . venv/bin/activate
   superset runserver
3. apache drill 1.16.0 version installed and running. I can see all the databases configured even with mongodb databases.
4. I have installed python 3.6.9 in my virtual environment where I run superset and sqlalchemy-drill.

What can I do now? Or could we connect at some point and you will enter my server to check everything please?

[cgivre]

@omejiasq
I apologize for this. There was a PR for the Drill dialect which attempts to infer data types, but in the process, breaks a lot of things.

So two things:

1. I would strongly recommend updating Drill to the latest version if you can.
2. I think the bug is here:

https://github.com/JohnOmernik/sqlalchemy-drill/blob/dedb76d0be5157d32c505abf1b523364582400f8/sqlalchemy_drill/drilldbapi/_drilldbapi.py#L19

Can you try this:

1. Do a git clone and d/l the library from the github repo
2. Change that line to:

'BIT':  'bool',

1. Reinstall the driver from the one you just modified and see if that works.

What I think is happening here is that Drill is returning JSON with all the values in quotes. The dialect is attempting to map the Drill data types to Pandas data types, but is failing because boolean is not a recognized data type.

[omejiasq]

Thank you very much friend. I was able to correct this bug by following your steps.

Excuse me, do you have any document or linkt where i can to read information to change the look and feel?

[cgivre]

@omejiasq
Thanks for helping me find (and fix) a few difficult bugs! As for changing the look and feel, that I don't think I can help you with but I'd ask on the Superset slack channel.

[iercan]

@cgivre I'm getting similar error but it is not related with BIT. It comes from VARCHAR as you can see below. I'm using python 3.8 and latest versions of sqlalchemy-drill and drill itself. Do you have any idea how to solve it?

superset_1 | DEBUG:root:Mapping column TABLE_NAME of Drill type VARCHAR to dtype string
superset_1 | ERROR:root:module 'sqlalchemy_drill.drilldbapi' has no attribute 'Error'
superset_1 | Traceback (most recent call last):
superset_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
superset_1 | self.dialect.do_execute(
superset_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
superset_1 | cursor.execute(statement, parameters)
superset_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy_drill/drilldbapi/_drilldbapi.py", line 65, in func_wrapper
superset_1 | return func(self, args, *kwargs)
superset_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy_drill/drilldbapi/_drilldbapi.py", line 164, in execute
superset_1 | elif str(df[col_name].iloc[0]).startswith("[") and str(df[col_name].iloc[0]).endswith("]"):
superset_1 | File "/usr/local/lib/python3.8/site-packages/pandas/core/indexing.py", line 879, in __getitem__
superset_1 | return self._getitem_axis(maybe_callable, axis=axis)
superset_1 | File "/usr/local/lib/python3.8/site-packages/pandas/core/indexing.py", line 1496, in _getitem_axis
superset_1 | self._validate_integer(key, axis)
superset_1 | File "/usr/local/lib/python3.8/site-packages/pandas/core/indexing.py", line 1437, in _validate_integer
superset_1 | raise IndexError("single positional indexer is out-of-bounds")
superset_1 | IndexError: single positional indexer is out-of-bounds

[cgivre]

@iercan
Can you open a new issue with the full stack trace? There are a few other bugs I'd like to quash as well.
Thanks!

[iercan]

@cgivre I created new issue #12155