How to write a valid custom SQL metric in Apache Superset? I need to write a metric like: SUM(col_name)/2700000.0 but I am getting error

Question

I want to use custom sql metric in Apache Superset Big Number visualization type. I want to use metric like: SUM(col_name)/2700000.0, but it doesn't work.

I tried SUM(col_name) and it works. I have tried also SUM(col_name)/COUNT(col_name) and this also works.

Metric I am trying to use: SUM(col_name)/2700000.0

I expect the metric to be working, but instead I am getting error:

ORA-00904: "col_name": invalid identifier

When I am clicking on hamburger icon and view query I can see:

SELECT SUM(col_name)/2700000.0
FROM
  (SELECT SUM(col_name)/2700000.0 AS "SUM(col_name)/2700000.0"
   FROM mytable
   ORDER BY SUM(col_name)/2700000.0 DESC)
WHERE ROWNUM <= 50000;

Answer 1

I assume this is on an Oracle engine. There seems to be a bug in either Superset or the Oracle dialect in SQLAlchemy that's causing the outer query to be rendered without quotes. By changing the alias of the metric to all caps without special characters, e.g. MY_SUM, the query should complete successfully.

Edit: This bug has been fixed in SQLAlchemy 1.3.5 and necessary changes have been made to Superset master branch as of 19 June 2019.

Answer 2

It seems that when using custom SQL metric you have to name the metric (e.g. "ABC"). Then the metric got an alias "ABC" and query works fine:

SELECT "ABC"
FROM
  (SELECT SUM(col_name)/2700000 AS "ABC"
   FROM mytable
   ORDER BY SUM(col_name)/2700000 DESC)
WHERE ROWNUM <= 50000;