Error loading tables from Hive2/SparkSQL

[piffall]

While adding a dataset from Apache Spark SQL / Hive form incorrectly loads tables on Add dataset form. It affects to tables and views. The number of tables + number of views, the number of default on Add dataset select box (see screenshot).

In contrast, querying database using SQL Lab is working perfectly (see screenshot).

Expected results

Add a dataset to Superset.

Actual results

Cannot add the dataset to Superset. No errors or warnings in logs.

Screenshots

How to reproduce the bug

1. Add new database
2. Select Apache Spark SQL
3. Enter the SQLAlchemy URI: hive://x.x.x.x:10000/default (tested withoud db name also)
4. Click on Connect
5. Go to Datasets and add new dataset
6. Select the new connection
7. Select the Schema
8. Table select will be populated with default table names.

Environment

Running the latest version of docker-compose-non-dev.yaml

[danielewood]

This should be resolved with the changes I upstreamed to the impyla connector.

try impala:// or ascend://

Both use the impyla driver, the difference is the ascend connection string uses postgres date formats.

ascend://{username}:{password}@{hostname}:{port}/{database}?auth_mechanism=PLAIN;use_ssl=true

[piffall]

Thanks @danielewood , that worked for me!

[PayneZx]

This should be resolved with the changes I upstreamed to the impyla connector.

try impala:// or ascend://

Both use the impyla driver, the difference is the ascend connection string uses postgres date formats.

ascend://{username}:{password}@{hostname}:{port}/{database}?auth_mechanism=PLAIN;use_ssl=true

I also used this method, but the Databases edit function cannot be used。

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[penghuo]

here is a quick fix in docker image.

# 1. edit docker images
docker run -u root -it apache/superset:latest-dev /bin/bash

# 2. edit phhive in shell
vim /usr/local/lib/python3.8/site-packages/pyhive/sqlalchemy_hive.py

change https://github.com/dropbox/PyHive/blob/master/pyhive/sqlalchemy_hive.py#L364
    def get_table_names(self, connection, schema=None, **kw):
        query = 'SHOW TABLES'
        if schema:
            query += ' IN ' + self.identifier_preparer.quote_identifier(schema)
        return [row[1] for row in connection.execute(query)]
        
change https://github.com/dropbox/PyHive/blob/master/pyhive/sqlalchemy_hive.py#L314
    if col_name == '# Partitioning':        


# 3. in another console, commit the change
docker ps
CONTAINER ID   IMAGE         COMMAND       CREATED              STATUS                          PORTS      NAMES
374249e4ac34   superset   "/bin/bash"   About a minute ago   Up About a minute (unhealthy)   8088/tcp   brave_jang

docker commit 374249e4ac34 superset-test

# 4. replace superset-image in docker-compose-non-dev.yml as superset-test 

[jbguerraz]

related #10940
Probably blocked by dropbox/PyHive#247

[rusackas]

This issue seems to have largely gone stale. It sounds like there were a couple of pending solutions on the thread. Can anyone confirm or deny whether this issue should remain open?

[pedrosalgadowork]

Same here, this is particularly important regarding catalog metadata fetch, currently, we can't use physical references to the tables, only virtual SQL queries, and metadata exploration using the UI is blocked.

[bkyryliuk]

Pyhive is not maintained. If anyone is interested in polishing dropbox/PyHive#247 and doing a couple rounds of testing / adding integration test for superset or pyhive - I am happy to review & release a new version