Cannot created temp table in redshift DB

[Vishnuprasadverma]

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

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

Superset version=0.19.1

Expected results=create temp table should work

Actual results=create temp table gives "The query returned no data" with no error on console. But table is not created in db.

Steps to reproduce

Use redshift db
create temp table temp_table(col1 int);
no table created in DB

[xrmx]

Stacktrace please. What do you mean with "create temp table" ?

[Vishnuprasadverma]

When i fire "create temp table" query in sqllab it does not work for redshift DB.
exact query
create temp table hello(col1 int);
output:
The query returned no data

stacktrace:

[2017-09-25 19:00:18,088: WARNING/Worker-19] /usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py:596: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  'storage.' % (dialect.name, dialect.driver))

stacktrace

2017-09-25 19:00:16,130:INFO:root:[stats_logger] (incr) results
2017-09-25 19:00:16,133:INFO:root:Parsing with sqlparse statement create temp table hello(col1 int);
2017-09-25 19:00:16,142:INFO:werkzeug:115.249.48.25 - - [25/Sep/2017 19:00:16] "GET /superset/results/a5cfeedf-03d5-4c0c-b505-8dce26c04341/ HTTP/1.1" 200 -
2017-09-25 19:00:18,058:INFO:root:[stats_logger] (incr) sql_json
2017-09-25 19:00:18,060:INFO:root:Parsing with sqlparse statement create temp table hello(col1 int);
2017-09-25 19:00:18,071:INFO:root:Triggering query_id: 76
2017-09-25 19:00:18,071:INFO:root:Running query on a Celery worker
2017-09-25 19:00:18,083:INFO:werkzeug:115.249.48.25 - - [25/Sep/2017 19:00:18] "POST /superset/sql_json/ HTTP/1.1" 202 -

[everglory99]

I also observed this issue. After looking deeper into the problem, I realized that the problem is not that the temp table can't be created in SQL lab and actually the message The query returned no data is normal. However the real problem is that the created temp table can't be used in the successive queries. For example if I run the following query blocks in SQL lab

-- query block 1
-- dummy table is just a copy of customer table
create temp table dummy
as
(
select user_id
from customer
)
;

-- query block 2
select * from dummy 

I will get the expected result. However if I first run query block 1 and then query block 2, I will receive error message indicating table dummy does't exist. From Redshift temp table documentation:

...creates a temporary table that is visible only within the current session. The table is automatically dropped at the end of the session in which it is created

I am starting to wonder how SQL lab handles "session". Does each SQL lab run re-connect to the database (start a new session) or it uses the same connection throughout the time when user is still logged in? For most of the use cases, I think it is more convenient to enable SQL lab user to first create a temp table and then use it afterwards. Is it possible to achieve this? Thanks very much for the help!

[everglory99]

Does anyone have a similar problem like this when using Redshift? Any help/pointer will be greatly appreciated, thanks!

[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.

[sarithapodali]

@everglory99 I face the same issue with temp tables using snowflake.

[willbarrett]

Hi All. The current architecture for Superset creates a new connection to analytical databases for each query executed. Thus temp tables can only be used when they are created during the same query execution as the subsequent operation that references them. I hope this helps you work around the issue. We're currently investigating connection pooling for analytical databases, which would be one step closer to providing persistent sessions.

[villebro]

Most databases support some type of cross-session temporary table: in SQL Server ##tmp_tbl vs #tmp_tbl, in Snowflake transient table vs temporary table etc. I suggest using those for now.