Incubator-superset: Error querying data from prestodb

[OElesin]

Kindly help. I get this error while querying my data after connecting to prestodb

{u'errorLocation': {u'columnNumber': 25, u'lineNumber': 3}, u'failureInfo': {u'suppressed': [], u'message': u"line 3:25: '>=' cannot be applied to timestamp, varchar(26)", u'errorLocation': {u'columnNumber': 25, u'lineNumber': 3}, u'type': u'com.facebook.presto.sql.analyzer.SemanticException', u'stack': [u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.getOperator(ExpressionAnalyzer.java:989)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitComparisonExpression(ExpressionAnalyzer.java:429)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitComparisonExpression(ExpressionAnalyzer.java:221)', u'com.facebook.presto.sql.tree.ComparisonExpression.accept(ComparisonExpression.java:133)', u'com.facebook.presto.sql.tree.StackableAstVisitor.process(StackableAstVisitor.java:28)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:240)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.coerceType(ExpressionAnalyzer.java:1012)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitLogicalBinaryExpression(ExpressionAnalyzer.java:412)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitLogicalBinaryExpression(ExpressionAnalyzer.java:221)', u'com.facebook.presto.sql.tree.LogicalBinaryExpression.accept(LogicalBinaryExpression.java:85)', u'com.facebook.presto.sql.tree.StackableAstVisitor.process(StackableAstVisitor.java:28)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:240)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer.analyze(ExpressionAnalyzer.java:213)', u'com.facebook.presto.sql.analyzer.ExpressionAnalyzer.analyzeExpression(ExpressionAnalyzer.java:1195)', u'com.facebook.presto.sql.analyzer.StatementAnalyzer.analyzeExpression(StatementAnalyzer.java:2019)', u'com.facebook.presto.sql.analyzer.StatementAnalyzer.analyzeWhere(StatementAnalyzer.java:1851)', u'com.facebook.presto.sql.analyzer.StatementAnalyzer.lambda$visitQuerySpecification$12(StatementAnalyzer.java:1165)', u'java.util.Optional.ifPresent(Optional.java:159)', u'com.facebook.presto.sql.analyzer.StatementAnalyzer.visitQuerySpecification(StatementAnalyzer.java:1165)', u'com.facebook.presto.sql.analyzer.StatementAnalyzer.visitQuerySpecification(StatementAnalyzer.java:229)', u'com.facebook.presto.sql.tree.QuerySpecification.accept(QuerySpecification.java:125)', u'com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:22)', u'com.facebook.presto.sql.analyzer.StatementAnalyzer.visitQuery(StatementAnalyzer.java:929)', u'com.facebook.presto.sql.analyzer.StatementAnalyzer.visitQuery(StatementAnalyzer.java:229)', u'com.facebook.presto.sql.tree.Query.accept(Query.java:103)', u'com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:22)', u'com.facebook.presto.sql.analyzer.Analyzer.analyze(Analyzer.java:60)', u'com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:273)', u'com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:259)', u'com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:223)', u'com.facebook.presto.execution.QueuedExecution.lambda$start$1(QueuedExecution.java:62)', u'java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)', u'java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)', u'java.lang.Thread.run(Thread.java:745)']}, u'errorType': u'USER_ERROR', u'errorName': u'SYNTAX_ERROR', u'errorCode': 1, u'message': u"line 3:25: '>=' cannot be applied to timestamp, varchar(26)"}

Thanks in advance

[rhp4515]

Can you paste a few rows of your db and the query generated by caravel? Looks like time/date column in your db is not getting interpreted as timestamps

[OElesin]

Few rows from my table
id | applicationdate
-----+-------------------------
98 | 2016-03-02 12:03:07.000
99 | 2016-03-02 21:15:08.000
100 | 2016-03-03 01:08:45.000
101 | 2016-03-03 11:50:31.000
102 | 2016-03-03 13:03:36.000

[georgeke]

What type is that date column in your presto database? Can you make sure that it's a datetime/timestamp type?

[volkmar]

The exception is due to Presto misinterpreting the date as a varchar. In your field definition of the table, add timestamp '{}' to database expression. It will change the SQL query from fieldname <= '2016-07-20 07:00:00' to fieldname <= timestamp '2016-07-20 07:00:00'.

See https://prestodb.io/docs/current/functions/datetime.html. Requires a change in pyhive for sqlalchemy which currently doesn't handle time conversions correctly.

[OElesin]

@georgeke, the type is timestamp. @volkmar, Worked like a charm. Thanks a lot

[volkmar]

As this is a work-around, you may want to keep the ticket open and fix it for real in caravel/pyhive.

[xrmx]

@OElesin which version of pyhive are you using? Can you reproduce it with 0.2.1?

[timfeirg]

I'm using superset 0.18.4, pyhive 0.3, still seeing this @xrmx

[timfeirg]

correct me if I'm wrong, while you can apply this workaround to your own hand write sql in sqllab, the query generated by superset visualizations are not editable by users.

this means if you tried to generate a chart using one of superset's visualization class which deals with timestamp, presto cannot correctly execute the query, and the chart will not draw.

because when rendering these type of charts, superset will wrap your query with something like select dt as '__timestamp', ... where 'dt' > '2016-06-09 17:23:06', because dt here is a timestamp type, and presto does not support directly comparing these types.

our team haven't got much experience with superset, or presto, may I ask how you guys make this work? I mean, visualization using presto sql, using timestamp types. @xrmx @mistercrunch

[timfeirg]

and I think this should be a prestodb issue, not pyhive or superset, things broke because presto doesn't implicitly convert varchar to timestamp when comparing with a timestamp. @volkmar

see https://stackoverflow.com/questions/38037713/presto-static-date-and-timestamp-in-where-clause

any plans to add some kind of compatibility fix into superset? PR welcome?

[mistercrunch]

Superset should be casting as defined here:
https://github.com/airbnb/superset/blob/master/superset/db_engine_specs.py#L407

Can you paste the SQL generated? Also what's the exact type of the dttm column?

The SQL should read something like:

WHERE your_date_column = from_iso8601_timestamp('2017-01-01T00:00:00')

[timfeirg]

the generated sql:

SELECT "tag_name" AS "tag_name",
       count(*) AS "count"
FROM
  (select create_time,
          tag_name
   from rhllor_ods.feedback_tag
   left join rhllor_ods.feedback on rhllor_ods.feedback.feedback_id=rhllor_ods.feedback_tag.feedback_id) AS "expr_qry"
WHERE "create_time" >= '1917-06-13 07:59:13'
  AND "create_time" <= '2017-06-13 07:59:13'
GROUP BY "tag_name"
ORDER BY "count" DESC
LIMIT 25

and this is the related column type:

feedback_id BIGINT
create_time TIMESTAMP
tag_name tag_name VARCHAR

@mistercrunch

[nod]

Is there any other feedback on this? We're seeing hte same issues. We got around it by casting our original ts to a varchar but it feels "dirty".

[mistercrunch]

Notice: this issue has been closed because it has been inactive for 300 days. Feel free to comment and request for this issue to be reopened.