Incubator-superset: Charts built off Snowflake only support uppercase metric labels

[elicutler]

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

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

Superset version

0.31.1

Expected results

Chart should render without error when metric is given a non-uppercase label.

Actual results

Chart fails to render when metric is given a non-uppercase label.
Stacktrace is as follows:

Traceback (most recent call last):
  File "/home/superset/superset/views/base.py", line 114, in wraps
    return f(self, *args, **kwargs)
  File "/home/superset/superset/views/core.py", line 1229, in explore_json
    samples=samples,
  File "/home/superset/superset/views/core.py", line 1160, in generate_json
    payload = viz_obj.get_payload()
  File "/home/superset/superset/viz.py", line 378, in get_payload
    payload['data'] = self.get_data(df)
  File "/home/superset/superset/viz.py", line 1231, in get_data
    df = self.process_data(df)
  File "/home/superset/superset/viz.py", line 1151, in process_data
    values=self.metric_labels)
  File "/usr/local/lib/python3.6/site-packages/pandas/core/frame.py", line 5303, in pivot_table
    margins_name=margins_name)
  File "/usr/local/lib/python3.6/site-packages/pandas/core/reshape/pivot.py", line 61, in pivot_table
    raise KeyError(i)
KeyError: 'my label'

Steps to reproduce

1. Create a chart (I've reproduced this error with bar chart and line chart).
2. Add a metric to the chart under Query > Metrics in the chart UI.
3. Click on the created metric; this will open a new box, and at the top is a place to write a custom label for the metric. Provide the metric a non-uppercase label, e.g. my label.
4. Run the query.

[yuyuhan0306]

I'm also faced with the same issue. I got the error message like this {"error": "'__timestamp'"} I think this problem is related to #4994 (Force lowercase column names for Snowflake and Oracle)

[yuguang]

Snowflake returns all column names as upper case. If you run queries in the Snowflake web portal or on a console, they all return upper case columns.

[rumbin]

Imho Snowflake should be able to deal with any column name, as long as it is quoted.
Thus I can imagine that forcing quotes on the column names may do the trick here.

[yuguang]

@rumbin Did you try running a query with quoted columns in Snowflake? From what I remember, Snowflake does not keep the casing of names in quotes

[rumbin]

@yuguang I have never worked with Snowflake. However, my suggestion is based on the docs:

https://docs.snowflake.net/manuals/sql-reference/identifiers-syntax.html

[...]
To support case-sensitive identifiers, as well as allowing identifiers to start with special characters and use extended characters (including blank spaces), identifiers can be enclosed in double quotes.
[...]

[yuyuhan0306]

@rumbin I tried to rename columns by lowering the column names and putting double quotes " to them. (For example, the date column is renamed to "the_date") However, I still got the error message "error": "000904 (42000): 018bdaf9-03cf-51b6-0000-3a6d000435b6: SQL compilation error: error line 1 at position 25\ninvalid identifier 'THE_DATE'" It seems that Snowflake still defaults columns to uppercase.

[yuguang]

@yuyuhan0306 Try adding {"connect_args": {"session_parameters": {"QUOTED_IDENTIFIERS_IGNORE_CASE": false}}} to the engine_params of the Snowflake configuration. After that, you should be able to lowercase column names by running SELECT AS statements. I tested this out by running SELECT hospital as "hospital" FROM emory.public.raw_er, which returned a lowercase hospital column.

[yuyuhan0306]

@yuyuhan0306 Try adding {"connect_args": {"session_parameters": {"QUOTED_IDENTIFIERS_IGNORE_CASE": false}}} to the engine_params of the Snowflake configuration. After that, you should be able to lowercase column names by running SELECT AS statements. I tested this out by running SELECT hospital as "hospital" FROM emory.public.raw_er, which returned a lowercase hospital column.

I added the parameter to the engine_params of the Snowflake configuration, which worked well for a table view. However, when I try to graph a time series - line chart, I still got the {"error": "'__timestamp'"}.

[yuguang]

@yuyuhan0306 Which branch are you on? If you are not on master, you will need this pull request: https://github.com/apache/incubator-superset/pull/7147

[yuyuhan0306]

@yuguang In order to get the changes from the #7147 PR to work with my system, I had to use a newer version of Superset that hasn't officially been released yet. When I tried to upgrade the metadata database to the newer version, it failed.

[stale[bot]]

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.