search

ibmdb / python-ibmdb

Automatically exported from code.google.com/p/ibm-db
Apache License 2.0
305 stars 191 forks source link

Apache superset - Error retrive table information (SqlAlchemy) #320

Closed mariomarf closed 6 years ago

mariomarf commented 6 years ago

Hello, when i try to get table information i receive "Error occurred while fetching table metadata". In log: "

2018-05-23 22:43:22,945:INFO:werkzeug:192.168.158.1 - - [23/May/2018 22:43:22] "GET /superset/table/2/ttis11_ms_od_gio/TISA%20%20%20%20/ HTTP/1.1" 500 - Traceback (most recent call last): File "/usr/lib/python2.7/site-packages/flask/app.py", line 1997, in call return self.wsgi_app(environ, start_response) File "/usr/lib/python2.7/site-packages/flask/app.py", line 1985, in wsgi_app response = self.handle_exception(e) File "/usr/lib/python2.7/site-packages/flask/app.py", line 1540, in handle_exception reraise(exc_type, exc_value, tb) File "/usr/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app response = self.full_dispatch_request() File "/usr/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/usr/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/usr/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request return self.view_functionsrule.endpoint File "/usr/lib/python2.7/site-packages/flask_appbuilder/security/decorators.py", line 26, in wraps return f(self, *args, *kwargs) File "/usr/lib/python2.7/site-packages/superset/models/core.py", line 905, in wrapper value = f(args, **kwargs) File "/usr/lib/python2.7/site-packages/superset/views/core.py", line 2253, in table cols=columns, latest_partition=False), File "/usr/lib/python2.7/site-packages/superset/models/core.py", line 722, in select_star indent=indent, latest_partition=latest_partition, cols=cols) File "/usr/lib/python2.7/site-packages/superset/db_engine_specs.py", line 256, in select_star sql = my_db.compile_sqla_query(qry) File "/usr/lib/python2.7/site-packages/superset/models/core.py", line 713, in compile_sqla_query compiled = qry.compile(eng, compile_kwargs={'literal_binds': True}) File "", line 1, in

File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 442, in compile return self._compiler(dialect, bind=bind, kw) File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 448, in _compiler return dialect.statement_compiler(dialect, self, kw) File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 453, in init Compiled.init(self, dialect, statement, kwargs) File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 219, in init self.string = self.process(self.statement, compile_kwargs) File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 245, in process return obj._compiler_dispatch(self, kwargs) File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch return meth(self, kw) File "/usr/lib/python2.7/site-packages/ibm_db_sa-0.3.2-py2.7.egg/ibm_db_sa/base.py", line 351, in visit_select sql_ori = compiler.SQLCompiler.visit_select(self, select, kwargs) File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1791, in visit_select text += self.get_select_precolumns(select, kwargs) TypeError: get_select_precolumns() got an unexpected keyword argument 'literal_binds'

"

Thanks, best regards Mario

enriquemt commented 6 years ago

It is happening the same to me using the last ibm_db_sa driver with superset.

abhi7436 commented 6 years ago

Hi @enriquemt , Can you please share steps to reproduce and also share your pip list.

Thanks, Abhinav

enriquemt commented 6 years ago

I have configured in Superset a connection to use ibm_db_sa with my database settings, anytime I am trying to load information I got the error. This is my pip list:

Package Version

alembic 1.0.0 amqp 2.3.2 asn1crypto 0.24.0 Babel 2.6.0 backports-abc 0.5 Beaker 1.10.0 beautifulsoup4 4.6.0 billiard 3.5.0.4 bleach 2.1.3 boto3 1.4.7 botocore 1.7.48 cchardet 1.1.3 celery 4.2.0 certifi 2018.4.16 cffi 1.11.5 chardet 3.0.4 click 6.7 colorama 0.3.9 contextlib2 0.5.5 cryptography 2.2.2 decorator 4.3.0 docutils 0.14 enum34 1.1.6 et-xmlfile 1.0.1 Flask 0.12.4 Flask-AppBuilder 1.10.0 Flask-Babel 0.11.1 Flask-Caching 1.4.0 Flask-Compress 1.4.0 Flask-Login 0.2.11 Flask-Migrate 2.2.1 Flask-OpenID 1.2.5 Flask-Script 2.0.6 Flask-SQLAlchemy 2.1 Flask-Testing 0.7.1 Flask-WTF 0.14.2 flower 0.9.2 FormEncode 1.3.1 funcsigs 1.0.2 functools32 3.2.3.post2 future 0.16.0 futures 3.2.0 geographiclib 1.49 geopy 1.15.0 grafana-api 0.2.4 gunicorn 19.9.0 hive 0.1.3.dev0 html5lib 1.0.1 humanize 0.5.1 ibm-db 2.0.8 idna 2.7 ijson 2.3 impala 0.2 influxdb 5.2.0 ipaddress 1.0.22 isodate 0.6.0 itsdangerous 0.24 jdcal 1.4 Jinja2 2.10 jmespath 0.9.3 jsonlines 1.2.0 jsonschema 2.6.0 kombu 4.2.1 linear-tsv 1.1.0 Mako 1.0.7 Markdown 2.6.11 MarkupSafe 1.0 mysqlclient 1.3.13 nose 1.3.7 numpy 1.14.5 openpyxl 2.4.11 pandas 0.23.3 parsedatetime 2.4 Paste 2.0.3 PasteDeploy 1.5.2 PasteScript 2.0.2 pathlib2 2.3.2 pip 10.0.1 pkg-resources 0.0.0 polyline 1.3.2 psycopg2 2.7.5 psycopg2-binary 2.7.5 pycparser 2.18 pydruid 0.4.5 Pygments 2.2.0 PyHive 0.6.0 Pylons 1.0.3 python-dateutil 2.7.3 python-editor 1.0.3 python-geohash 0.8.5 python-openid 2.2.5 pytz 2018.5 PyYAML 3.13 repoze.lru 0.7 requests 2.19.1 rfc3986 1.1.0 Routes 2.4.1 s3transfer 0.1.13 sasl 0.2.1 scandir 1.7 setuptools 40.0.0 simplejson 3.16.0 singledispatch 3.4.0.3 six 1.11.0 SQLAlchemy 1.2.10 SQLAlchemy-Utils 0.33.3 sqlparse 0.2.4 superset 0.26.3 tableschema 1.1.0 tabulator 1.14.0 Tempita 0.5.2 thrift 0.11.0 thrift-sasl 0.3.0 tornado 5.1 unicodecsv 0.14.1 Unidecode 1.0.22 urllib3 1.23 vine 1.1.4 waitress 1.1.0 webencodings 0.5.1 WebError 0.13.1 WebHelpers 1.3 WebOb 1.8.2 WebTest 2.0.30 Werkzeug 0.14.1 wheel 0.31.1 WTForms 2.2.1 xlrd 1.1.0

abhi7436 commented 6 years ago

Hi, I am not able to see ibm_db_sa configured in your pip list can you please check if it got properly installed or not and also share your stack trace as well to have a better look.

Thanks, Abhinav

enriquemt commented 6 years ago

Dear Abhinav, I did not user pip to install it, I build it manually and then copy the ibm_db_sa folder in order to be used by superset, the test connection to the DB works fine and I am able to see tables and columns from it.

image

ajgil commented 6 years ago

Hi there! I had installed using this Ibm doc

Used Ubuntu and sles15 python2.7 and python3.6 and got same error on selects.

news?

Updated

It's possible due to haven't license? ibm groups

abhi7436 commented 6 years ago

Hi @enriquemt , From logs I can see control is not coming to ibm_db_sa code. if it comes then error log will contain something like below. ile "/usr/lib/python2.7/site-packages/ibm_db_sa-0.3.2-py2.7.egg/ibm_db_sa/base.py", line 351, in visit_select sql_ori = compiler.SQLCompiler.visit_select(self, select, **kwargs)

Please install ibm_db_sa using pip to the latest version (0.3.3)

@ajgil , can you please share your sample script, because I tried to execute sample program mentioned in your given link but not seeing the issue and share your pip list as well.

Thanks, Abhinav

enriquemt commented 6 years ago

@abhi7436 I am still having the same problem, the output now has changed and now the error log contains the info you posted.

image

ajgil commented 6 years ago

@abhi7436, @enriquemt from sles15 and python3.6 add Stack trace. Ibm_db, ibm_dbi and ibm_db_sa drivers are well installed and on Superset SQLEditor map db, schema and tables. When starting select against tables that got error.

Drivers

sudo pip install ibm_db
sudo pip install ibm_db_sa

Python2.7 Test conn

Python 2.7.13 (default, Jan 11 2017, 10:56:06) [GCC] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import ibm_db
>>> import ibm_db_dbi
>>> import sqlalchemy
>>> import ibm_db_sa
>>> from sqlalchemy import *
>>> db2 = sqlalchemy.create_engine('ibm_db_sa://XXX:XXX@XXXX.XXX.org:50001/XXX')
>>>

Stack trace

2018-07-26 09:10:33,639:INFO:root:Database.get_sqla_engine(). Masked URL: ibm_db_sa://xxxxus1:XXXXXXXXXX@xxxdbint.xxx.net:50001/XXXXX
2018-07-26 09:10:44,298:INFO:werkzeug:10.168.24.69 - - [26/Jul/2018 09:10:44] "GET /superset/schemas/2/ HTTP/1.1" 200 -
2018-07-26 09:10:44,372:INFO:root:Database.get_sqla_engine(). Masked URL: ibm_db_sa://xxxxus1:XXXXXXXXXX@xxxdbint.xxx.net:50001/XXXXX
2018-07-26 09:10:54,485:INFO:root:Database.get_sqla_engine(). Masked URL: ibm_db_sa://xxxxus1:XXXXXXXXXX@xxxdbint.xxx.net:50001/XXXXX
2018-07-26 09:11:04,559:INFO:werkzeug:10.168.24.69 - - [26/Jul/2018 09:11:04] "GET /superset/schemas/2/ HTTP/1.1" 200 -
2018-07-26 09:11:14,674:INFO:werkzeug:10.168.24.69 - - [26/Jul/2018 09:11:14] "GET /superset/tables/2/axxxxg/XXXX%20/undefined/ HTTP/1.1" 200 -
2018-07-26 09:11:18,015:DEBUG:root:[stats_logger] (incr) table
2018-07-26 09:11:18,020:INFO:root:Database.get_sqla_engine(). Masked URL: ibm_db_sa://xxxxus1:XXXXXXXXXX@xxxdbint.xxx.net:50001/XXXXX
2018-07-26 09:11:23,048:DEBUG:root:[stats_logger] (incr) extra_table_metadata
2018-07-26 09:11:28,152:INFO:werkzeug:10.168.24.69 - - [26/Jul/2018 09:11:28] "GET /superset/extra_table_metadata/2/axxxxg/XXXX%20/HTTP/1.1" 200 -
2018-07-26 09:11:58,842:INFO:root:Database.get_sqla_engine(). Masked URL: ibm_db_sa:/xxxxus1:XXXXXXXXXX@xxxdbint.xxx.net:50001/XXXXX
2018-07-26 09:11:58,844:INFO:root:Database.get_sqla_engine(). Masked URL: ibm_db_sa://xxxxus1:XXXXXXXXXX@xxxdbint.xxx.net:50001/XXXXX
2018-07-26 09:11:59,084:INFO:werkzeug:10.168.24.69 - - [26/Jul/2018 09:11:59] "GET /superset/table/2/axxxxg/XXXX%20/ HTTP/1.1" 500 -
Traceback (most recent call last):
  File "/usr/lib64/python3.6/site-packages/flask/app.py", line 1997, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/lib64/python3.6/site-packages/flask/app.py", line 1985, in wsgi_app
    response = self.handle_exception(e)
  File "/usr/lib64/python3.6/site-packages/flask/app.py", line 1540, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/lib64/python3.6/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/usr/lib64/python3.6/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/lib64/python3.6/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/lib64/python3.6/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/lib64/python3.6/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/usr/lib64/python3.6/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/lib64/python3.6/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/lib64/python3.6/site-packages/flask_appbuilder/security/decorators.py", line 26, in wraps
    return f(self, *args, **kwargs)
  File "/usr/lib/python3.6/site-packages/superset/models/core.py", line 903, in wrapper
    value = f(*args, **kwargs)
  File "/usr/lib/python3.6/site-packages/superset/views/core.py", line 2259, in table
    cols=columns, latest_partition=False),
  File "/usr/lib/python3.6/site-packages/superset/models/core.py", line 727, in select_star
    indent=indent, latest_partition=latest_partition, cols=cols)
  File "/usr/lib/python3.6/site-packages/superset/db_engine_specs.py", line 310, in select_star
    sql = my_db.compile_sqla_query(qry)
  File "/usr/lib/python3.6/site-packages/superset/models/core.py", line 716, in compile_sqla_query
    compiled = qry.compile(eng, compile_kwargs={'literal_binds': True})
  File "<string>", line 1, in <lambda>

  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 442, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 448, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 453, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 219, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 245, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/lib/python3.6/site-packages/ibm_db_sa/base.py", line 354, in visit_select
    sql_ori = compiler.SQLCompiler.visit_select(self, select, **kwargs)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 1791, in visit_select
    text += self.get_select_precolumns(select, **kwargs)
TypeError: get_select_precolumns() got an unexpected keyword argument 'literal_binds'
abhi7436 commented 6 years ago

Hi, I have made some code changes but having difficulty testing it. I have pasted below the code changes can you please try it out. It should fix the issue. in base.py file please replace def get_select_precolumns(self, select): and def limit_clause(self, select): with below lines.

def get_select_precolumns(self, select, **kwargs): def limit_clause(self, select, **kwargs):

Let me know if you still face the same issue.

Thanks, Abhinav

ajgil commented 6 years ago

Hi there, @abhi7436 your fix found!

2018-07-27 13:30:05,604:INFO:root:Database.get_sqla_engine(). Masked URL: ibm_db_sa://XXXXXXXXXX:XXXXXXXXXX@XXXXXXXXXX.XXXXXXXXXX.net:50001/XXXXXXXXXX
2018-07-27 13:30:05,624:INFO:root:SELECT "XXXXXXXXXX" AS "XXXXXXXXXX", sum(XXXXXXXXXX) AS "XXXXXXXXXX"
FROM (SELECT XXXXXXXXXX,
XXXXXXXXXX,     
FROM "XXXXXXXXXX".XXXXXXXXXX FETCH FIRST 100 ROWS ONLY) AS expr_qry
WHERE "XXXXXXXXXX" >= '1918-07-27-00.00.00' AND "XXXXXXXXXX" <= '2018-07-27-13.30.05' GROUP BY "XXXXXXXXXX" ORDER BY "XXXXXXXXXX" DESC FETCH FIRST 50000 ROWS ONLY
2018-07-27 13:30:05,664:INFO:root:Database.get_sqla_engine(). Masked URL: ibm_db_sa://XXXXXXXXXX:XXXXXXXXXX@XXXXXXXXXX.XXXXXXXXXX.net:50001/XXXXXXXXXX
SabaKauser commented 6 years ago

closing.