romamur opened a new issue #18834:
URL: https://github.com/apache/superset/issues/18834
I have clickhouse field DateTime64 in my table. When i use this field in
superset chart in both "time column" and in "group by" section - this gives me
clickhouse syntax error.
SQL query (generated by superset):
`SELECT toMonday(toDateTime(created_date)) AS created_date
FROM default.test_table
GROUP BY toMonday(toDateTime(created_date))
LIMIT 10000`
As you can see group by sql query missing expression `AS created_date`.
I temporary fix this by edit 2 files:
1. _superset/db_engine_specs/clickhouse.py_ **added AS {col}**
_class ClickHouseEngineSpec(BaseEngineSpec):_
`_time_grain_expressions = {
None: "{col}",
"PT1M": "toStartOfMinute(toDateTime({col})) AS {col}",
"PT5M": "toDateTime(intDiv(toUInt32(toDateTime({col})), 300)*300) AS
{col}",
"PT10M": "toDateTime(intDiv(toUInt32(toDateTime({col})), 600)*600)
AS {col}",
"PT15M": "toDateTime(intDiv(toUInt32(toDateTime({col})), 900)*900)
AS {col}",
"PT0.5H": "toDateTime(intDiv(toUInt32(toDateTime({col})),
1800)*1800) AS {col}",
"PT1H": "toStartOfHour(toDateTime({col})) AS {col}",
"P1D": "toStartOfDay(toDateTime({col})) AS {col}",
"P1W": "toMonday(toDateTime({col})) AS {col}",
"P1M": "toStartOfMonth(toDateTime({col})) AS {col}",
"P0.25Y": "toStartOfQuarter(toDateTime({col})) AS {col}",
"P1Y": "toStartOfYear(toDateTime({col})) AS {col}",
}`
2. _superset/db_engine_specs/base.py **added regex replace (in all sql
query) "double AS expression" to "single AS expression"**_
_class BaseEngineSpec:_
`@classmethod
def execute(cls, cursor: Any, query: str, **kwargs: Any) -> None:
"""
Execute a SQL query
:param cursor: Cursor instance
:param query: Query to execute
:param kwargs: kwargs to be passed to cursor.execute()
:return:
"""
if not cls.allows_sql_comments:
query = sql_parse.strip_comments_from_sql(query)
if cls.arraysize:
cursor.arraysize = cls.arraysize
try:
if re.search(r"AS\s+\w+\s+AS\s+\w+", query):
query = re.sub("AS\s+\w+", '', query, 1)
cursor.execute(query)
except Exception as ex:
raise cls.get_dbapi_mapped_exception(ex)`
Now i get correct SQL query (generated by superset with my fix):
`SELECT toMonday(toDateTime(created_date)) AS created_date
FROM default.test_table
GROUP BY toMonday(toDateTime(created_date)) AS created_date
LIMIT 10000`
I'ts not good fix, but worked for me. Sorry for my english
### Environment
Superset 1.3.0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
