test_sqlalchemy_athena failing in my local env

[sdia-zz]

Hello everyone,

i am currently working on the get_columnsdialect method. I have a decent implementation (sdia-zz@da61f6c) I was about to submit as PR. But I am stuck because I can get the tests working in my local dev, even without my changes.

Are you aware of any issues on test_sqlalchemy_athena?
Is it fine if I just submit my PR without tests ?

============================================================================================================ test session starts =============================================================================================================
platform darwin -- Python 2.7.13, pytest-3.2.2, py-1.4.34, pluggy-0.4.0
rootdir: /private/tmp/PyAthenaJDBC, inifile: setup.cfg
collected 65 items                                                                                                                                                                                                                            

tests/test_cursor.py ...............................
tests/test_formatter.py .....................
tests/test_sqlalchemy_athena.py ....F.F.....
tests/test_util.py .

================================================================================================================== FAILURES ==================================================================================================================
______________________________________________________________________________________________ TestSQLAlchemyAthena.test_reflect_no_such_table _______________________________________________________________________________________________

self = <tests.test_sqlalchemy_athena.TestSQLAlchemyAthena testMethod=test_reflect_no_such_table>, args = (), kwargs = {}
engine = Engine(awsathena+jdbc://athena.us-west-2.amazonaws.com:443/test_pyathena_jdbc_fzfz7mqn8g?s3_staging_dir=s3://clm-tmp/sdia/), conn = <sqlalchemy.engine.base.Connection object at 0x1224cb3d0>

    @functools.wraps(fn)
    def wrapped_fn(self, *args, **kwargs):
        engine = self.create_engine()
        try:
            with contextlib.closing(engine.connect()) as conn:
>               fn(self, engine, conn, *args, **kwargs)

tests/util.py:36: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
tests/test_sqlalchemy_athena.py:53: in test_reflect_no_such_table
    lambda: Table('this_does_not_exist', MetaData(bind=engine), autoload=True))
tests/test_sqlalchemy_athena.py:53: in <lambda>
    lambda: Table('this_does_not_exist', MetaData(bind=engine), autoload=True))
/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py:439: in __new__
    metadata._remove_table(name, schema)
/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py:66: in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py:434: in __new__
    table._init(name, metadata, *args, **kw)
/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py:514: in _init
    include_columns, _extend_on=_extend_on)
/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py:540: in _autoload
    _extend_on=_extend_on
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:2045: in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:1534: in run_callable
    return callable_(self, *args, **kwargs)
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py:372: in reflecttable
    table, include_columns, exclude_columns, **opts)
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:598: in reflecttable
    table_name, schema, **table.dialect_kwargs):
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:369: in get_columns
    **kw)
<string>:2: in get_columns
    ???
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py:54: in cache
    ret = fn(self, con, *args, **kw)
pyathenajdbc/sqlalchemy_athena.py:127: in get_columns
    res = connection.execute(query).fetchall()
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:939: in execute
    return self._execute_text(object, multiparams, params)
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:1097: in _execute_text
    statement, parameters
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:1189: in _execute_context
    context)
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:1402: in _handle_dbapi_exception
    exc_info
/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py:203: in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py:1182: in _execute_context
    context)
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py:470: in do_execute
    cursor.execute(statement, parameters)
pyathenajdbc/util.py:34: in _wrapper
    return wrapped(*args, **kwargs)
pyathenajdbc/util.py:24: in _wrapper
    return wrapped(*args, **kwargs)
pyathenajdbc/cursor.py:194: in execute
    raise_from(DatabaseError(*e.args), e)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

exc = DatabaseError(<jpype._jclass.java.sql.SQLException object at 0x1228987d0>,), cause = SQLExceptionPyRaisable(<jpype._jclass.java.sql.SQLException object at 0x1228987d0>,)

    def raise_from(exc, cause):
        """
            Equivalent to:
    
                raise EXCEPTION from CAUSE
    
            on Python 3. (See PEP 3134).
            """
        # Is either arg an exception class (e.g. IndexError) rather than
        # instance (e.g. IndexError('my message here')? If so, pass the
        # name of the class undisturbed through to "raise ... from ...".
        if isinstance(exc, type) and issubclass(exc, Exception):
            e = exc()
            # exc = exc.__name__
            # execstr = "e = " + _repr_strip(exc) + "()"
            # myglobals, mylocals = _get_caller_globals_and_locals()
            # exec(execstr, myglobals, mylocals)
        else:
            e = exc
        e.__suppress_context__ = False
        if isinstance(cause, type) and issubclass(cause, Exception):
            e.__cause__ = cause()
            e.__suppress_context__ = True
        elif cause is None:
            e.__cause__ = None
            e.__suppress_context__ = True
        elif isinstance(cause, BaseException):
            e.__cause__ = cause
            e.__suppress_context__ = True
        else:
            raise TypeError("exception causes must derive from BaseException")
        e.__context__ = sys.exc_info()[1]
>       raise e
E       DatabaseError: (pyathenajdbc.error.DatabaseError) java.sql.SQLException: FAILED: SemanticException [Error 10001]: Table not found test_pyathena_jdbc_fzfz7mqn8g.this_does_not_exist [SQL: u'SHOW CREATE TABLE test_pyathena_jdbc_fzfz7mqn8g.this_does_not_exist']

/usr/local/lib/python2.7/site-packages/future/utils/__init__.py:454: DatabaseError
__________________________________________________________________________________________________ TestSQLAlchemyAthena.test_reflect_select __________________________________________________________________________________________________

self = <tests.test_sqlalchemy_athena.TestSQLAlchemyAthena testMethod=test_reflect_select>, args = (), kwargs = {}
engine = Engine(awsathena+jdbc://athena.us-west-2.amazonaws.com:443/test_pyathena_jdbc_fzfz7mqn8g?s3_staging_dir=s3://clm-tmp/sdia/), conn = <sqlalchemy.engine.base.Connection object at 0x1228a7050>

    @functools.wraps(fn)
    def wrapped_fn(self, *args, **kwargs):
        engine = self.create_engine()
        try:
            with contextlib.closing(engine.connect()) as conn:
>               fn(self, engine, conn, *args, **kwargs)

tests/util.py:36: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
tests/test_sqlalchemy_athena.py:152: in test_reflect_select
    self.assertIsInstance(one_row_complex.c.col_int.type, INTEGER)
E   AssertionError: NullType() is not an instance of <class 'sqlalchemy.sql.sqltypes.INTEGER'>
------------------------------------------------------------------------------------------------------------ Captured stdout call ------------------------------------------------------------------------------------------------------------
BOOLEAN
TINYINT
SMALLINT
INT
BIGINT
FLOAT
DOUBLE
STRING
TIMESTAMP
DATE
BINARY
ARRAY<INT>
MAP<INT,INT>
STRUCT<A:INT,B:INT>
DECIMAL(10,1)
=================================================================================================== 2 failed, 63 passed in 180.05 seconds ====================================================================================================

[laughingman7743]

Hi @sdia

I tried rebuilding with TravisCI earlier, but all the master branch tests were successful.
Is not there a problem in the place you modified? I do not know why you do not use the information schema.

Certainly the current implementation is not good, so I'm planning to change it to a method implemented by PyAthena(https://github.com/laughingman7743/PyAthena/blob/master/pyathena/sqlalchemy_athena.py#L127) with #48 in the near future.

[sdia-zz]

Hi @laughingman7743,

thanks for the reply. Ok it seems like the tests issue is environment related I will investigate more on my side.

Regarding information_schema, the systems table columns does not exist in my Athena console... I did the simple test to login my AWS Athena console and run SELECT * FROM information_schema.columns and it failed.

Maybe we have different version of Athena, or maybe you are using some presto stuff???

I would love to add a unit test for that, but again I am struggling with getting the existing tests work.

[laughingman7743]

Hi @sdia,

All tests are actually done by uploading test data to S3, creating databases and tables at Athena. It does not use Presto. The information schema is not displayed on the Athena web console, but it exists.

If you throw the following query on the Athena web console, column information of all tables should be displayed.

SELECT * FROM information_schema.columns

If you specify a schema that does not exist in the where clause, an error will occur. This seems to be the specification of Presto.

SELECT * FROM information_schema.columns where table_schema = 'aaa'

Your query has the following error(s):

com.facebook.presto.hive.DataCatalogException: Namespace aaa not found. Please check your query.
This query ran against the "sampledb" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: d69b52ba-7661-4452-92fd-efbe37d906f5.

I am not an AWS Support, so I do not know the details of your issues, but it may be better to hear it on AWS Support or the AWS Forums (https://forums.aws.amazon.com/forum.jspa?forumID=242).

[laughingman7743]

Related issue: apache/superset#3559