You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by yo...@apache.org on 2022/09/16 04:02:34 UTC

[superset] branch master updated: fix: Time Offset in SQLite and refine logic in Date Type conversion (#21378)

This is an automated email from the ASF dual-hosted git repository.

yongjiezhao pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git


The following commit(s) were added to refs/heads/master by this push:
     new 2dfcba04b0 fix: Time Offset in SQLite and refine logic in Date Type conversion (#21378)
2dfcba04b0 is described below

commit 2dfcba04b0e191cc46405a5514f99bf61831f48d
Author: Yongjie Zhao <yo...@apache.org>
AuthorDate: Fri Sep 16 12:02:22 2022 +0800

    fix: Time Offset in SQLite and refine logic in Date Type conversion (#21378)
---
 superset/common/query_context_processor.py     |  64 +++++++++++---
 superset/utils/core.py                         |  97 +++++++++++++++-------
 superset/viz.py                                |  13 ++-
 tests/integration_tests/conftest.py            |  10 +++
 tests/integration_tests/query_context_tests.py | 110 ++++++++++++++++++++++++-
 tests/integration_tests/utils_tests.py         |  14 +++-
 6 files changed, 259 insertions(+), 49 deletions(-)

diff --git a/superset/common/query_context_processor.py b/superset/common/query_context_processor.py
index ae3202981b..98aaecebd9 100644
--- a/superset/common/query_context_processor.py
+++ b/superset/common/query_context_processor.py
@@ -49,8 +49,10 @@ from superset.utils import csv
 from superset.utils.cache import generate_cache_key, set_and_log_cache
 from superset.utils.core import (
     DatasourceType,
+    DateColumn,
     DTTM_ALIAS,
     error_msg_from_exception,
+    get_base_axis_labels,
     get_column_names_from_columns,
     get_column_names_from_metrics,
     get_metric_names,
@@ -238,18 +240,57 @@ class QueryContextProcessor:
         return result
 
     def normalize_df(self, df: pd.DataFrame, query_object: QueryObject) -> pd.DataFrame:
-        datasource = self._qc_datasource
-        timestamp_format = None
-        if datasource.type == "table":
-            dttm_col = datasource.get_column(query_object.granularity)
-            if dttm_col:
-                timestamp_format = dttm_col.python_date_format
+        # todo: should support "python_date_format" and "get_column" in each datasource
+        def _get_timestamp_format(
+            source: BaseDatasource, column: Optional[str]
+        ) -> Optional[str]:
+            column_obj = source.get_column(column)
+            if (
+                column_obj
+                # only sqla column was supported
+                and hasattr(column_obj, "python_date_format")
+                and (formatter := column_obj.python_date_format)
+            ):
+                return str(formatter)
+
+            return None
 
+        datasource = self._qc_datasource
+        labels = tuple(
+            label
+            for label in [
+                *get_base_axis_labels(query_object.columns),
+                query_object.granularity,
+            ]
+            if datasource
+            # Query datasource didn't support `get_column`
+            and hasattr(datasource, "get_column")
+            and (col := datasource.get_column(label))
+            and col.is_dttm
+        )
+        dttm_cols = [
+            DateColumn(
+                timestamp_format=_get_timestamp_format(datasource, label),
+                offset=datasource.offset,
+                time_shift=query_object.time_shift,
+                col_label=label,
+            )
+            for label in labels
+            if label
+        ]
+        if DTTM_ALIAS in df:
+            dttm_cols.append(
+                DateColumn.get_legacy_time_column(
+                    timestamp_format=_get_timestamp_format(
+                        datasource, query_object.granularity
+                    ),
+                    offset=datasource.offset,
+                    time_shift=query_object.time_shift,
+                )
+            )
         normalize_dttm_col(
             df=df,
-            timestamp_format=timestamp_format,
-            offset=datasource.offset,
-            time_shift=query_object.time_shift,
+            dttm_cols=tuple(dttm_cols),
         )
 
         if self.enforce_numerical_metrics:
@@ -344,10 +385,7 @@ class QueryContextProcessor:
                 offset_metrics_df = offset_metrics_df.rename(columns=metrics_mapping)
 
                 # 3. set time offset for index
-                # TODO: add x-axis to QueryObject, potentially as an array for
-                #  multi-dimensional charts
-                granularity = query_object.granularity
-                index = granularity if granularity in df.columns else DTTM_ALIAS
+                index = (get_base_axis_labels(query_object.columns) or [DTTM_ALIAS])[0]
                 if not dataframe_utils.is_datetime_series(offset_metrics_df.get(index)):
                     raise QueryObjectValidationError(
                         _(
diff --git a/superset/utils/core.py b/superset/utils/core.py
index 4131ede785..2786cfa27f 100644
--- a/superset/utils/core.py
+++ b/superset/utils/core.py
@@ -16,6 +16,8 @@
 # under the License.
 """Utility functions used across Superset"""
 # pylint: disable=too-many-lines
+from __future__ import annotations
+
 import _thread
 import collections
 import decimal
@@ -34,6 +36,7 @@ import traceback
 import uuid
 import zlib
 from contextlib import contextmanager
+from dataclasses import dataclass
 from datetime import date, datetime, time, timedelta
 from distutils.util import strtobool
 from email.mime.application import MIMEApplication
@@ -1271,15 +1274,13 @@ def is_adhoc_column(column: Column) -> TypeGuard[AdhocColumn]:
     return isinstance(column, dict)
 
 
-def get_base_axis_column(columns: Optional[List[Column]]) -> Optional[AdhocColumn]:
-    if columns is None:
-        return None
+def get_base_axis_labels(columns: Optional[List[Column]]) -> Tuple[str, ...]:
     axis_cols = [
         col
-        for col in columns
+        for col in columns or []
         if is_adhoc_column(col) and col.get("columnType") == "BASE_AXIS"
     ]
-    return axis_cols[0] if axis_cols else None
+    return tuple(get_column_name(col) for col in axis_cols)
 
 
 def get_column_name(
@@ -1301,9 +1302,12 @@ def get_column_name(
         expr = column.get("sqlExpression")
         if expr:
             return expr
-        raise ValueError("Missing label")
-    verbose_map = verbose_map or {}
-    return verbose_map.get(column, column)
+
+    if isinstance(column, str):
+        verbose_map = verbose_map or {}
+        return verbose_map.get(column, column)
+
+    raise ValueError("Missing label")
 
 
 def get_metric_name(
@@ -1845,33 +1849,64 @@ def remove_duplicates(
     return result
 
 
+@dataclass
+class DateColumn:
+    col_label: str
+    timestamp_format: Optional[str] = None
+    offset: Optional[int] = None
+    time_shift: Optional[timedelta] = None
+
+    def __hash__(self) -> int:
+        return hash(self.col_label)
+
+    def __eq__(self, other: object) -> bool:
+        return isinstance(other, DateColumn) and hash(self) == hash(other)
+
+    @classmethod
+    def get_legacy_time_column(
+        cls,
+        timestamp_format: Optional[str],
+        offset: Optional[int],
+        time_shift: Optional[timedelta],
+    ) -> DateColumn:
+        return cls(
+            timestamp_format=timestamp_format,
+            offset=offset,
+            time_shift=time_shift,
+            col_label=DTTM_ALIAS,
+        )
+
+
 def normalize_dttm_col(
     df: pd.DataFrame,
-    timestamp_format: Optional[str],
-    offset: int,
-    time_shift: Optional[timedelta],
+    dttm_cols: Tuple[DateColumn, ...] = tuple(),
 ) -> None:
-    if DTTM_ALIAS not in df.columns:
-        return
-    if timestamp_format in ("epoch_s", "epoch_ms"):
-        dttm_col = df[DTTM_ALIAS]
-        if is_numeric_dtype(dttm_col):
-            # Column is formatted as a numeric value
-            unit = timestamp_format.replace("epoch_", "")
-            df[DTTM_ALIAS] = pd.to_datetime(
-                dttm_col, utc=False, unit=unit, origin="unix", errors="coerce"
-            )
+    for _col in dttm_cols:
+        if _col.col_label not in df.columns:
+            continue
+
+        if _col.timestamp_format in ("epoch_s", "epoch_ms"):
+            dttm_series = df[_col.col_label]
+            if is_numeric_dtype(dttm_series):
+                # Column is formatted as a numeric value
+                unit = _col.timestamp_format.replace("epoch_", "")
+                df[_col.col_label] = pd.to_datetime(
+                    dttm_series, utc=False, unit=unit, origin="unix", errors="coerce"
+                )
+            else:
+                # Column has already been formatted as a timestamp.
+                df[_col.col_label] = dttm_series.apply(pd.Timestamp)
         else:
-            # Column has already been formatted as a timestamp.
-            df[DTTM_ALIAS] = dttm_col.apply(pd.Timestamp)
-    else:
-        df[DTTM_ALIAS] = pd.to_datetime(
-            df[DTTM_ALIAS], utc=False, format=timestamp_format, errors="coerce"
-        )
-    if offset:
-        df[DTTM_ALIAS] += timedelta(hours=offset)
-    if time_shift is not None:
-        df[DTTM_ALIAS] += time_shift
+            df[_col.col_label] = pd.to_datetime(
+                df[_col.col_label],
+                utc=False,
+                format=_col.timestamp_format,
+                errors="coerce",
+            )
+        if _col.offset:
+            df[_col.col_label] += timedelta(hours=_col.offset)
+        if _col.time_shift is not None:
+            df[_col.col_label] += _col.time_shift
 
 
 def parse_boolean_string(bool_str: Optional[str]) -> bool:
diff --git a/superset/viz.py b/superset/viz.py
index 42de86497f..43e71b533c 100644
--- a/superset/viz.py
+++ b/superset/viz.py
@@ -80,6 +80,7 @@ from superset.utils import core as utils, csv
 from superset.utils.cache import set_and_log_cache
 from superset.utils.core import (
     apply_max_row_limit,
+    DateColumn,
     DTTM_ALIAS,
     ExtraFiltersReasonType,
     get_column_name,
@@ -301,9 +302,15 @@ class BaseViz:  # pylint: disable=too-many-public-methods
         if not df.empty:
             utils.normalize_dttm_col(
                 df=df,
-                timestamp_format=timestamp_format,
-                offset=self.datasource.offset,
-                time_shift=self.time_shift,
+                dttm_cols=tuple(
+                    [
+                        DateColumn.get_legacy_time_column(
+                            timestamp_format=timestamp_format,
+                            offset=self.datasource.offset,
+                            time_shift=self.time_shift,
+                        )
+                    ]
+                ),
             )
 
             if self.enforce_numerical_metrics:
diff --git a/tests/integration_tests/conftest.py b/tests/integration_tests/conftest.py
index 606bfe4377..c117422adc 100644
--- a/tests/integration_tests/conftest.py
+++ b/tests/integration_tests/conftest.py
@@ -402,3 +402,13 @@ only_postgresql = pytest.mark.skipif(
     "postgresql" not in os.environ.get("SUPERSET__SQLALCHEMY_DATABASE_URI", ""),
     reason="Only run test case in Postgresql",
 )
+
+only_sqlite = pytest.mark.skipif(
+    "sqlite" not in os.environ.get("SUPERSET__SQLALCHEMY_DATABASE_URI", ""),
+    reason="Only run test case in SQLite",
+)
+
+only_mysql = pytest.mark.skipif(
+    "mysql" not in os.environ.get("SUPERSET__SQLALCHEMY_DATABASE_URI", ""),
+    reason="Only run test case in MySQL",
+)
diff --git a/tests/integration_tests/query_context_tests.py b/tests/integration_tests/query_context_tests.py
index cb2b24e9cc..2306b0a1e2 100644
--- a/tests/integration_tests/query_context_tests.py
+++ b/tests/integration_tests/query_context_tests.py
@@ -18,6 +18,8 @@ import re
 import time
 from typing import Any, Dict
 
+import numpy as np
+import pandas as pd
 import pytest
 from pandas import DateOffset
 
@@ -39,7 +41,7 @@ from superset.utils.core import (
 )
 from superset.utils.pandas_postprocessing.utils import FLAT_COLUMN_SEPARATOR
 from tests.integration_tests.base_tests import SupersetTestCase
-from tests.integration_tests.conftest import only_postgresql
+from tests.integration_tests.conftest import only_postgresql, only_sqlite
 from tests.integration_tests.fixtures.birth_names_dashboard import (
     load_birth_names_dashboard_with_slices,
     load_birth_names_data,
@@ -910,3 +912,109 @@ def test_non_date_adhoc_column(app_context, physical_dataset):
     df = qc.get_df_payload(query_object)["df"]
     assert df["ADHOC COLUMN"][0] == 0
     assert df["ADHOC COLUMN"][1] == 10
+
+
+@only_sqlite
+def test_time_grain_and_time_offset_with_base_axis(app_context, physical_dataset):
+    column_on_axis: AdhocColumn = {
+        "label": "col6",
+        "sqlExpression": "col6",
+        "columnType": "BASE_AXIS",
+        "timeGrain": "P3M",
+    }
+    qc = QueryContextFactory().create(
+        datasource={
+            "type": physical_dataset.type,
+            "id": physical_dataset.id,
+        },
+        queries=[
+            {
+                "columns": [column_on_axis],
+                "metrics": [
+                    {
+                        "label": "SUM(col1)",
+                        "expressionType": "SQL",
+                        "sqlExpression": "SUM(col1)",
+                    }
+                ],
+                "time_offsets": ["3 month ago"],
+                "granularity": "col6",
+                "time_range": "2002-01 : 2003-01",
+            }
+        ],
+        result_type=ChartDataResultType.FULL,
+        force=True,
+    )
+    query_object = qc.queries[0]
+    df = qc.get_df_payload(query_object)["df"]
+    # todo: MySQL returns integer and float column as object type
+    """
+        col6  SUM(col1)  SUM(col1)__3 month ago
+0 2002-01-01          3                     NaN
+1 2002-04-01         12                     3.0
+2 2002-07-01         21                    12.0
+3 2002-10-01          9                    21.0
+    """
+    assert df.equals(
+        pd.DataFrame(
+            data={
+                "col6": pd.to_datetime(
+                    ["2002-01-01", "2002-04-01", "2002-07-01", "2002-10-01"]
+                ),
+                "SUM(col1)": [3, 12, 21, 9],
+                "SUM(col1)__3 month ago": [np.nan, 3, 12, 21],
+            }
+        )
+    )
+
+
+@only_sqlite
+def test_time_grain_and_time_offset_on_legacy_query(app_context, physical_dataset):
+    qc = QueryContextFactory().create(
+        datasource={
+            "type": physical_dataset.type,
+            "id": physical_dataset.id,
+        },
+        queries=[
+            {
+                "columns": [],
+                "extras": {
+                    "time_grain_sqla": "P3M",
+                },
+                "metrics": [
+                    {
+                        "label": "SUM(col1)",
+                        "expressionType": "SQL",
+                        "sqlExpression": "SUM(col1)",
+                    }
+                ],
+                "time_offsets": ["3 month ago"],
+                "granularity": "col6",
+                "time_range": "2002-01 : 2003-01",
+                "is_timeseries": True,
+            }
+        ],
+        result_type=ChartDataResultType.FULL,
+        force=True,
+    )
+    query_object = qc.queries[0]
+    df = qc.get_df_payload(query_object)["df"]
+    # todo: MySQL returns integer and float column as object type
+    """
+  __timestamp  SUM(col1)  SUM(col1)__3 month ago
+0  2002-01-01          3                     NaN
+1  2002-04-01         12                     3.0
+2  2002-07-01         21                    12.0
+3  2002-10-01          9                    21.0
+    """
+    assert df.equals(
+        pd.DataFrame(
+            data={
+                "__timestamp": pd.to_datetime(
+                    ["2002-01-01", "2002-04-01", "2002-07-01", "2002-10-01"]
+                ),
+                "SUM(col1)": [3, 12, 21, 9],
+                "SUM(col1)__3 month ago": [np.nan, 3, 12, 21],
+            }
+        )
+    )
diff --git a/tests/integration_tests/utils_tests.py b/tests/integration_tests/utils_tests.py
index da1567cab4..7df9cd82f9 100644
--- a/tests/integration_tests/utils_tests.py
+++ b/tests/integration_tests/utils_tests.py
@@ -70,6 +70,7 @@ from superset.utils.core import (
     validate_json,
     zlib_compress,
     zlib_decompress,
+    DateColumn,
 )
 from superset.utils.database import get_or_create_db
 from superset.utils import schema
@@ -1062,7 +1063,18 @@ class TestUtils(SupersetTestCase):
             time_shift: Optional[timedelta],
         ) -> pd.DataFrame:
             df = df.copy()
-            normalize_dttm_col(df, timestamp_format, offset, time_shift)
+            normalize_dttm_col(
+                df,
+                tuple(
+                    [
+                        DateColumn.get_legacy_time_column(
+                            timestamp_format=timestamp_format,
+                            offset=offset,
+                            time_shift=time_shift,
+                        )
+                    ]
+                ),
+            )
             return df
 
         ts = pd.Timestamp(2021, 2, 15, 19, 0, 0, 0)