superset-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From maximebeauche...@apache.org
Subject [incubator-superset] branch master updated: Add time grain blacklist and addons to config.py (#5380)
Date Tue, 31 Jul 2018 06:44:33 GMT
This is an automated email from the ASF dual-hosted git repository.

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


The following commit(s) were added to refs/heads/master by this push:
     new c1e6c68  Add time grain blacklist and addons to config.py (#5380)
c1e6c68 is described below

commit c1e6c68a3ed31d3875b6d9dfac1484d8897d6561
Author: Ville Brofeldt <33317356+villebro@users.noreply.github.com>
AuthorDate: Tue Jul 31 09:44:30 2018 +0300

    Add time grain blacklist and addons to config.py (#5380)
    
    * Add interim grains
    
    * Refactor and add blacklist
    
    * Change PT30M to PT0.5H
    
    * Linting
    
    * Linting
    
    * Add time grain addons to config.py and refactor engine spec logic
    
    * Remove redundant import and clean up config.py
    
    * Fix bad rebase
    
    * Implement changes proposed by @betodealmeida
    
    * Revert removal of name from Grain
    
    * Linting
---
 superset/config.py            |  24 ++
 superset/db_engine_specs.py   | 554 ++++++++++++++++++------------------------
 superset/models/core.py       |   2 +-
 tests/db_engine_specs_test.py |  29 +++
 4 files changed, 292 insertions(+), 317 deletions(-)

diff --git a/superset/config.py b/superset/config.py
index 991febf..6c3c526 100644
--- a/superset/config.py
+++ b/superset/config.py
@@ -201,6 +201,30 @@ CSV_EXPORT = {
 }
 
 # ---------------------------------------------------
+# Time grain configurations
+# ---------------------------------------------------
+# List of time grains to disable in the application (see list of builtin
+# time grains in superset/db_engine_specs.builtin_time_grains).
+# For example: to disable 1 second time grain:
+# TIME_GRAIN_BLACKLIST = ['PT1S']
+TIME_GRAIN_BLACKLIST = []
+
+# Additional time grains to be supported using similar definitions as in
+# superset/db_engine_specs.builtin_time_grains.
+# For example: To add a new 2 second time grain:
+# TIME_GRAIN_ADDONS = {'PT2S': '2 second'}
+TIME_GRAIN_ADDONS = {}
+
+# Implementation of additional time grains per engine.
+# For example: To implement 2 second time grain on clickhouse engine:
+# TIME_GRAIN_ADDON_FUNCTIONS = {
+#     'clickhouse': {
+#         'PT2S': 'toDateTime(intDiv(toUInt32(toDateTime({col})), 2)*2)'
+#     }
+# }
+TIME_GRAIN_ADDON_FUNCTIONS = {}
+
+# ---------------------------------------------------
 # List of viz_types not allowed in your environment
 # For example: Blacklist pivot table and treemap:
 #  VIZ_TYPE_BLACKLIST = ['pivot_table', 'treemap']
diff --git a/superset/db_engine_specs.py b/superset/db_engine_specs.py
index ebc8802..ee14017 100644
--- a/superset/db_engine_specs.py
+++ b/superset/db_engine_specs.py
@@ -52,6 +52,36 @@ hive_poll_interval = conf.get('HIVE_POLL_INTERVAL')
 
 Grain = namedtuple('Grain', 'name label function duration')
 
+builtin_time_grains = {
+    None: 'Time Column',
+    'PT1S': 'second',
+    'PT1M': 'minute',
+    'PT5M': '5 minute',
+    'PT10M': '10 minute',
+    'PT15M': '15 minute',
+    'PT0.5H': 'half hour',
+    'PT1H': 'hour',
+    'P1D': 'day',
+    'P1W': 'week',
+    'P1M': 'month',
+    'P0.25Y': 'quarter',
+    'P1Y': 'year',
+    '1969-12-28T00:00:00Z/P1W': 'week_start_sunday',
+    '1969-12-29T00:00:00Z/P1W': 'week_start_monday',
+    'P1W/1970-01-03T00:00:00Z': 'week_ending_saturday',
+    'P1W/1970-01-04T00:00:00Z': 'week_ending_sunday',
+}
+
+
+def _create_time_grains_tuple(time_grains, time_grain_functions, blacklist):
+    ret_list = []
+    blacklist = blacklist if blacklist else []
+    for duration, func in time_grain_functions.items():
+        if duration not in blacklist:
+            name = time_grains.get(duration)
+            ret_list.append(Grain(name, _(name), func, duration))
+    return tuple(ret_list)
+
 
 class LimitMethod(object):
     """Enum the ways that limits can be applied"""
@@ -65,13 +95,23 @@ class BaseEngineSpec(object):
     """Abstract class for database engine specific configurations"""
 
     engine = 'base'  # str as defined in sqlalchemy.engine.engine
-    time_grains = tuple()
+    time_grain_functions = {}
     time_groupby_inline = False
     limit_method = LimitMethod.FORCE_LIMIT
     time_secondary_columns = False
     inner_joins = True
 
     @classmethod
+    def get_time_grains(cls):
+        blacklist = config.get('TIME_GRAIN_BLACKLIST', [])
+        grains = builtin_time_grains.copy()
+        grains.update(config.get('TIME_GRAIN_ADDONS', {}))
+        grain_functions = cls.time_grain_functions.copy()
+        grain_addon_functions = config.get('TIME_GRAIN_ADDON_FUNCTIONS', {})
+        grain_functions.update(grain_addon_functions.get(cls.engine, {}))
+        return _create_time_grains_tuple(grains, grain_functions, blacklist)
+
+    @classmethod
     def fetch_data(cls, cursor, limit):
         if cls.limit_method == LimitMethod.FETCH_MANY:
             return cursor.fetchmany(limit)
@@ -331,25 +371,17 @@ class PostgresBaseEngineSpec(BaseEngineSpec):
 
     engine = ''
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'),
-              "DATE_TRUNC('second', {col}) AT TIME ZONE 'UTC'", 'PT1S'),
-        Grain('minute', _('minute'),
-              "DATE_TRUNC('minute', {col}) AT TIME ZONE 'UTC'", 'PT1M'),
-        Grain('hour', _('hour'),
-              "DATE_TRUNC('hour', {col}) AT TIME ZONE 'UTC'", 'PT1H'),
-        Grain('day', _('day'),
-              "DATE_TRUNC('day', {col}) AT TIME ZONE 'UTC'", 'P1D'),
-        Grain('week', _('week'),
-              "DATE_TRUNC('week', {col}) AT TIME ZONE 'UTC'", 'P1W'),
-        Grain('month', _('month'),
-              "DATE_TRUNC('month', {col}) AT TIME ZONE 'UTC'", 'P1M'),
-        Grain('quarter', _('quarter'),
-              "DATE_TRUNC('quarter', {col}) AT TIME ZONE 'UTC'", 'P0.25Y'),
-        Grain('year', _('year'),
-              "DATE_TRUNC('year', {col}) AT TIME ZONE 'UTC'", 'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': "DATE_TRUNC('second', {col}) AT TIME ZONE 'UTC'",
+        'PT1M': "DATE_TRUNC('minute', {col}) AT TIME ZONE 'UTC'",
+        'PT1H': "DATE_TRUNC('hour', {col}) AT TIME ZONE 'UTC'",
+        'P1D': "DATE_TRUNC('day', {col}) AT TIME ZONE 'UTC'",
+        'P1W': "DATE_TRUNC('week', {col}) AT TIME ZONE 'UTC'",
+        'P1M': "DATE_TRUNC('month', {col}) AT TIME ZONE 'UTC'",
+        'P0.25Y': "DATE_TRUNC('quarter', {col}) AT TIME ZONE 'UTC'",
+        'P1Y': "DATE_TRUNC('year', {col}) AT TIME ZONE 'UTC'",
+    }
 
     @classmethod
     def fetch_data(cls, cursor, limit):
@@ -381,17 +413,25 @@ class PostgresEngineSpec(PostgresBaseEngineSpec):
 
 class SnowflakeEngineSpec(PostgresBaseEngineSpec):
     engine = 'snowflake'
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'), "DATE_TRUNC('SECOND', {col})", 'PT1S'),
-        Grain('minute', _('minute'), "DATE_TRUNC('MINUTE', {col})", 'PT1M'),
-        Grain('hour', _('hour'), "DATE_TRUNC('HOUR', {col})", 'PT1H'),
-        Grain('day', _('day'), "DATE_TRUNC('DAY', {col})", 'P1D'),
-        Grain('week', _('week'), "DATE_TRUNC('WEEK', {col})", 'P1W'),
-        Grain('month', _('month'), "DATE_TRUNC('MONTH', {col})", 'P1M'),
-        Grain('quarter', _('quarter'), "DATE_TRUNC('QUARTER', {col})", 'P0.25Y'),
-        Grain('year', _('year'), "DATE_TRUNC('YEAR', {col})", 'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': "DATE_TRUNC('SECOND', {col})",
+        'PT1M': "DATE_TRUNC('MINUTE', {col})",
+        'PT5M': "DATEADD(MINUTE, FLOOR(DATE_PART(MINUTE, {col}) / 5) * 5, \
+                DATE_TRUNC('HOUR', {col}))",
+        'PT10M': "DATEADD(MINUTE, FLOOR(DATE_PART(MINUTE, {col}) / 10) * 10, \
+                 DATE_TRUNC('HOUR', {col}))",
+        'PT15M': "DATEADD(MINUTE, FLOOR(DATE_PART(MINUTE, {col}) / 15) * 15, \
+                 DATE_TRUNC('HOUR', {col}))",
+        'PT0.5H': "DATEADD(MINUTE, FLOOR(DATE_PART(MINUTE, {col}) / 30) * 30, \
+                  DATE_TRUNC('HOUR', {col}))",
+        'PT1H': "DATE_TRUNC('HOUR', {col})",
+        'P1D': "DATE_TRUNC('DAY', {col})",
+        'P1W': "DATE_TRUNC('WEEK', {col})",
+        'P1M': "DATE_TRUNC('MONTH', {col})",
+        'P0.25Y': "DATE_TRUNC('QUARTER', {col})",
+        'P1Y': "DATE_TRUNC('YEAR', {col})",
+    }
 
 
 class VerticaEngineSpec(PostgresBaseEngineSpec):
@@ -406,16 +446,17 @@ class OracleEngineSpec(PostgresBaseEngineSpec):
     engine = 'oracle'
     limit_method = LimitMethod.WRAP_SQL
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('minute', _('minute'), "TRUNC(TO_DATE({col}), 'MI')", 'PT1M'),
-        Grain('hour', _('hour'), "TRUNC(TO_DATE({col}), 'HH')", 'PT1H'),
-        Grain('day', _('day'), "TRUNC(TO_DATE({col}), 'DDD')", 'P1D'),
-        Grain('week', _('week'), "TRUNC(TO_DATE({col}), 'WW')", 'P1W'),
-        Grain('month', _('month'), "TRUNC(TO_DATE({col}), 'MONTH')", 'P1M'),
-        Grain('quarter', _('quarter'), "TRUNC(TO_DATE({col}), 'Q')", 'P0.25Y'),
-        Grain('year', _('year'), "TRUNC(TO_DATE({col}), 'YEAR')", 'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': 'CAST({col} as DATE)',
+        'PT1M': "TRUNC(TO_DATE({col}), 'MI')",
+        'PT1H': "TRUNC(TO_DATE({col}), 'HH')",
+        'P1D': "TRUNC(TO_DATE({col}), 'DDD')",
+        'P1W': "TRUNC(TO_DATE({col}), 'WW')",
+        'P1M': "TRUNC(TO_DATE({col}), 'MONTH')",
+        'P0.25Y': "TRUNC(TO_DATE({col}), 'Q')",
+        'P1Y': "TRUNC(TO_DATE({col}), 'YEAR')",
+    }
 
     @classmethod
     def convert_dttm(cls, target_type, dttm):
@@ -427,46 +468,30 @@ class OracleEngineSpec(PostgresBaseEngineSpec):
 class Db2EngineSpec(BaseEngineSpec):
     engine = 'ibm_db_sa'
     limit_method = LimitMethod.WRAP_SQL
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'),
-              'CAST({col} as TIMESTAMP)'
-              ' - MICROSECOND({col}) MICROSECONDS',
-              'PT1S'),
-        Grain('minute', _('minute'),
-              'CAST({col} as TIMESTAMP)'
-              ' - SECOND({col}) SECONDS'
-              ' - MICROSECOND({col}) MICROSECONDS',
-              'PT1M'),
-        Grain('hour', _('hour'),
-              'CAST({col} as TIMESTAMP)'
-              ' - MINUTE({col}) MINUTES'
-              ' - SECOND({col}) SECONDS'
-              ' - MICROSECOND({col}) MICROSECONDS ',
-              'PT1H'),
-        Grain('day', _('day'),
-              'CAST({col} as TIMESTAMP)'
-              ' - HOUR({col}) HOURS'
-              ' - MINUTE({col}) MINUTES'
-              ' - SECOND({col}) SECONDS'
-              ' - MICROSECOND({col}) MICROSECONDS ',
-              'P1D'),
-        Grain('week', _('week'),
-              '{col} - (DAYOFWEEK({col})) DAYS',
-              'P1W'),
-        Grain('month', _('month'),
-              '{col} - (DAY({col})-1) DAYS',
-              'P1M'),
-        Grain('quarter', _('quarter'),
-              '{col} - (DAY({col})-1) DAYS'
-              ' - (MONTH({col})-1) MONTHS'
-              ' + ((QUARTER({col})-1) * 3) MONTHS',
-              'P0.25Y'),
-        Grain('year', _('year'),
-              '{col} - (DAY({col})-1) DAYS'
-              ' - (MONTH({col})-1) MONTHS',
-              'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': 'CAST({col} as TIMESTAMP)'
+                ' - MICROSECOND({col}) MICROSECONDS',
+        'PT1M': 'CAST({col} as TIMESTAMP)'
+                ' - SECOND({col}) SECONDS'
+                ' - MICROSECOND({col}) MICROSECONDS',
+        'PT1H': 'CAST({col} as TIMESTAMP)'
+                ' - MINUTE({col}) MINUTES'
+                ' - SECOND({col}) SECONDS'
+                ' - MICROSECOND({col}) MICROSECONDS ',
+        'P1D': 'CAST({col} as TIMESTAMP)'
+               ' - HOUR({col}) HOURS'
+               ' - MINUTE({col}) MINUTES'
+               ' - SECOND({col}) SECONDS'
+               ' - MICROSECOND({col}) MICROSECONDS',
+        'P1W': '{col} - (DAYOFWEEK({col})) DAYS',
+        'P1M': '{col} - (DAY({col})-1) DAYS',
+        'P0.25Y': '{col} - (DAY({col})-1) DAYS'
+                  ' - (MONTH({col})-1) MONTHS'
+                  ' + ((QUARTER({col})-1) * 3) MONTHS',
+        'P1Y': '{col} - (DAY({col})-1) DAYS'
+               ' - (MONTH({col})-1) MONTHS',
+    }
 
     @classmethod
     def epoch_to_dttm(cls):
@@ -479,28 +504,17 @@ class Db2EngineSpec(BaseEngineSpec):
 
 class SqliteEngineSpec(BaseEngineSpec):
     engine = 'sqlite'
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('hour', _('hour'),
-              "DATETIME(STRFTIME('%Y-%m-%dT%H:00:00', {col}))",
-              'PT1H'),
-        Grain('day', _('day'), 'DATE({col})', 'P1D'),
-        Grain('week', _('week'),
-              "DATE({col}, -strftime('%W', {col}) || ' days')",
-              'P1W'),
-        Grain('month', _('month'),
-              "DATE({col}, -strftime('%d', {col}) || ' days', '+1 day')",
-              'P1M'),
-        Grain('year', _('year'),
-              "DATETIME(STRFTIME('%Y-01-01T00:00:00', {col}))",
-              'P1Y'),
-        Grain('week_ending_saturday', _('week_ending_saturday'),
-              "DATE({col}, 'weekday 6')",
-              'P1W/1970-01-03T00:00:00Z'),
-        Grain('week_start_sunday', _('week_start_sunday'),
-              "DATE({col}, 'weekday 0', '-7 days')",
-              '1969-12-28T00:00:00Z/P1W'),
-    )
+
+    time_grain_functions = {
+        None: '{col}',
+        'PT1H': "DATETIME(STRFTIME('%Y-%m-%dT%H:00:00', {col}))",
+        'P1D': 'DATE({col})',
+        'P1W': "DATE({col}, -strftime('%W', {col}) || ' days')",
+        'P1M': "DATE({col}, -strftime('%d', {col}) || ' days', '+1 day')",
+        'P1Y': "DATETIME(STRFTIME('%Y-01-01T00:00:00', {col}))",
+        'P1W/1970-01-03T00:00:00Z': "DATE({col}, 'weekday 6')",
+        '1969-12-28T00:00:00Z/P1W': "DATE({col}, 'weekday 0', '-7 days')",
+    }
 
     @classmethod
     def epoch_to_dttm(cls):
@@ -540,36 +554,29 @@ class SqliteEngineSpec(BaseEngineSpec):
 
 class MySQLEngineSpec(BaseEngineSpec):
     engine = 'mysql'
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'), 'DATE_ADD(DATE({col}), '
+
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': 'DATE_ADD(DATE({col}), '
               'INTERVAL (HOUR({col})*60*60 + MINUTE({col})*60'
               ' + SECOND({col})) SECOND)',
-              'PT1S'),
-        Grain('minute', _('minute'), 'DATE_ADD(DATE({col}), '
+        'PT1M': 'DATE_ADD(DATE({col}), '
               'INTERVAL (HOUR({col})*60 + MINUTE({col})) MINUTE)',
-              'PT1M'),
-        Grain('hour', _('hour'), 'DATE_ADD(DATE({col}), '
+        'PT1H': 'DATE_ADD(DATE({col}), '
               'INTERVAL HOUR({col}) HOUR)',
-              'PT1H'),
-        Grain('day', _('day'), 'DATE({col})', 'P1D'),
-        Grain('week', _('week'), 'DATE(DATE_SUB({col}, '
+        'P1D': 'DATE({col})',
+        'P1W': 'DATE(DATE_SUB({col}, '
               'INTERVAL DAYOFWEEK({col}) - 1 DAY))',
-              'P1W'),
-        Grain('month', _('month'), 'DATE(DATE_SUB({col}, '
+        'P1M': 'DATE(DATE_SUB({col}, '
               'INTERVAL DAYOFMONTH({col}) - 1 DAY))',
-              'P1M'),
-        Grain('quarter', _('quarter'), 'MAKEDATE(YEAR({col}), 1) '
+        'P0.25Y': 'MAKEDATE(YEAR({col}), 1) '
               '+ INTERVAL QUARTER({col}) QUARTER - INTERVAL 1 QUARTER',
-              'P0.25Y'),
-        Grain('year', _('year'), 'DATE(DATE_SUB({col}, '
+        'P1Y': 'DATE(DATE_SUB({col}, '
               'INTERVAL DAYOFYEAR({col}) - 1 DAY))',
-              'P1Y'),
-        Grain('week_start_monday', _('week_start_monday'),
-              'DATE(DATE_SUB({col}, '
+        '1969-12-29T00:00:00Z/P1W': 'DATE(DATE_SUB({col}, '
               'INTERVAL DAYOFWEEK(DATE_SUB({col}, INTERVAL 1 DAY)) - 1 DAY))',
-              'P1W'),
-    )
+    }
+
     type_code_map = {}  # loaded from get_datatype only if needed
 
     @classmethod
@@ -621,41 +628,23 @@ class MySQLEngineSpec(BaseEngineSpec):
 class PrestoEngineSpec(BaseEngineSpec):
     engine = 'presto'
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'),
-              "date_trunc('second', CAST({col} AS TIMESTAMP))",
-              'PT1S'),
-        Grain('minute', _('minute'),
-              "date_trunc('minute', CAST({col} AS TIMESTAMP))",
-              'PT1M'),
-        Grain('hour', _('hour'),
-              "date_trunc('hour', CAST({col} AS TIMESTAMP))",
-              'PT1H'),
-        Grain('day', _('day'),
-              "date_trunc('day', CAST({col} AS TIMESTAMP))",
-              'P1D'),
-        Grain('week', _('week'),
-              "date_trunc('week', CAST({col} AS TIMESTAMP))",
-              'P1W'),
-        Grain('month', _('month'),
-              "date_trunc('month', CAST({col} AS TIMESTAMP))",
-              'P1M'),
-        Grain('quarter', _('quarter'),
-              "date_trunc('quarter', CAST({col} AS TIMESTAMP))",
-              'P0.25Y'),
-        Grain('week_ending_saturday', _('week_ending_saturday'),
-              "date_add('day', 5, date_trunc('week', date_add('day', 1, "
-              'CAST({col} AS TIMESTAMP))))',
-              'P1W/1970-01-03T00:00:00Z'),
-        Grain('week_start_sunday', _('week_start_sunday'),
-              "date_add('day', -1, date_trunc('week', "
-              "date_add('day', 1, CAST({col} AS TIMESTAMP))))",
-              '1969-12-28T00:00:00Z/P1W'),
-        Grain('year', _('year'),
-              "date_trunc('year', CAST({col} AS TIMESTAMP))",
-              'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': "date_trunc('second', CAST({col} AS TIMESTAMP))",
+        'PT1M': "date_trunc('minute', CAST({col} AS TIMESTAMP))",
+        'PT1H': "date_trunc('hour', CAST({col} AS TIMESTAMP))",
+        'P1D': "date_trunc('day', CAST({col} AS TIMESTAMP))",
+        'P1W': "date_trunc('week', CAST({col} AS TIMESTAMP))",
+        'P1M': "date_trunc('month', CAST({col} AS TIMESTAMP))",
+        'P0.25Y': "date_trunc('quarter', CAST({col} AS TIMESTAMP))",
+        'P1Y': "date_trunc('year', CAST({col} AS TIMESTAMP))",
+        'P1W/1970-01-03T00:00:00Z':
+            "date_add('day', 5, date_trunc('week', date_add('day', 1, \
+            CAST({col} AS TIMESTAMP))))",
+        '1969-12-28T00:00:00Z/P1W':
+            "date_add('day', -1, date_trunc('week', \
+            date_add('day', 1, CAST({col} AS TIMESTAMP))))",
+    }
 
     @classmethod
     def adjust_database_uri(cls, uri, selected_schema=None):
@@ -1219,39 +1208,21 @@ class MssqlEngineSpec(BaseEngineSpec):
     epoch_to_dttm = "dateadd(S, {col}, '1970-01-01')"
     limit_method = LimitMethod.WRAP_SQL
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'), 'DATEADD(second, '
-              "DATEDIFF(second, '2000-01-01', {col}), '2000-01-01')",
-              'PT1S'),
-        Grain('minute', _('minute'), 'DATEADD(minute, '
-              'DATEDIFF(minute, 0, {col}), 0)',
-              'PT1M'),
-        Grain('5 minute', _('5 minute'), 'DATEADD(minute, '
-              'DATEDIFF(minute, 0, {col}) / 5 * 5, 0)',
-              'PT5M'),
-        Grain('half hour', _('half hour'), 'DATEADD(minute, '
-              'DATEDIFF(minute, 0, {col}) / 30 * 30, 0)',
-              'PT0.5H'),
-        Grain('hour', _('hour'), 'DATEADD(hour, '
-              'DATEDIFF(hour, 0, {col}), 0)',
-              'PT1H'),
-        Grain('day', _('day'), 'DATEADD(day, '
-              'DATEDIFF(day, 0, {col}), 0)',
-              'P1D'),
-        Grain('week', _('week'), 'DATEADD(week, '
-              'DATEDIFF(week, 0, {col}), 0)',
-              'P1W'),
-        Grain('month', _('month'), 'DATEADD(month, '
-              'DATEDIFF(month, 0, {col}), 0)',
-              'P1M'),
-        Grain('quarter', _('quarter'), 'DATEADD(quarter, '
-              'DATEDIFF(quarter, 0, {col}), 0)',
-              'P0.25Y'),
-        Grain('year', _('year'), 'DATEADD(year, '
-              'DATEDIFF(year, 0, {col}), 0)',
-              'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': "DATEADD(second, DATEDIFF(second, '2000-01-01', {col}), '2000-01-01')",
+        'PT1M': 'DATEADD(minute, DATEDIFF(minute, 0, {col}), 0)',
+        'PT5M': 'DATEADD(minute, DATEDIFF(minute, 0, {col}) / 5 * 5, 0)',
+        'PT10M': 'DATEADD(minute, DATEDIFF(minute, 0, {col}) / 10 * 10, 0)',
+        'PT15M': 'DATEADD(minute, DATEDIFF(minute, 0, {col}) / 15 * 15, 0)',
+        'PT0.5H': 'DATEADD(minute, DATEDIFF(minute, 0, {col}) / 30 * 30, 0)',
+        'PT1H': 'DATEADD(hour, DATEDIFF(hour, 0, {col}), 0)',
+        'P1D': 'DATEADD(day, DATEDIFF(day, 0, {col}), 0)',
+        'P1W': 'DATEADD(week, DATEDIFF(week, 0, {col}), 0)',
+        'P1M': 'DATEADD(month, DATEDIFF(month, 0, {col}), 0)',
+        'P0.25Y': 'DATEADD(quarter, DATEDIFF(quarter, 0, {col}), 0)',
+        'P1Y': 'DATEADD(year, DATEDIFF(year, 0, {col}), 0)',
+    }
 
     @classmethod
     def convert_dttm(cls, target_type, dttm):
@@ -1261,38 +1232,21 @@ class MssqlEngineSpec(BaseEngineSpec):
 class AthenaEngineSpec(BaseEngineSpec):
     engine = 'awsathena'
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'),
-              "date_trunc('second', CAST({col} AS TIMESTAMP))",
-              'PT1S'),
-        Grain('minute', _('minute'),
-              "date_trunc('minute', CAST({col} AS TIMESTAMP))",
-              'PT1M'),
-        Grain('hour', _('hour'),
-              "date_trunc('hour', CAST({col} AS TIMESTAMP))",
-              'PT1H'),
-        Grain('day', _('day'),
-              "date_trunc('day', CAST({col} AS TIMESTAMP))",
-              'P1D'),
-        Grain('week', _('week'),
-              "date_trunc('week', CAST({col} AS TIMESTAMP))",
-              'P1W'),
-        Grain('month', _('month'),
-              "date_trunc('month', CAST({col} AS TIMESTAMP))",
-              'P1M'),
-        Grain('quarter', _('quarter'),
-              "date_trunc('quarter', CAST({col} AS TIMESTAMP))",
-              'P0.25Y'),
-        Grain('week_ending_saturday', _('week_ending_saturday'),
-              "date_add('day', 5, date_trunc('week', date_add('day', 1, "
-              'CAST({col} AS TIMESTAMP))))',
-              'P1W/1970-01-03T00:00:00Z'),
-        Grain('week_start_sunday', _('week_start_sunday'),
-              "date_add('day', -1, date_trunc('week', "
-              "date_add('day', 1, CAST({col} AS TIMESTAMP))))",
-              '1969-12-28T00:00:00Z/P1W'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': "date_trunc('second', CAST({col} AS TIMESTAMP))",
+        'PT1M': "date_trunc('minute', CAST({col} AS TIMESTAMP))",
+        'PT1H': "date_trunc('hour', CAST({col} AS TIMESTAMP))",
+        'P1D': "date_trunc('day', CAST({col} AS TIMESTAMP))",
+        'P1W': "date_trunc('week', CAST({col} AS TIMESTAMP))",
+        'P1M': "date_trunc('month', CAST({col} AS TIMESTAMP))",
+        'P0.25Y': "date_trunc('quarter', CAST({col} AS TIMESTAMP))",
+        'P1Y': "date_trunc('year', CAST({col} AS TIMESTAMP))",
+        'P1W/1970-01-03T00:00:00Z': "date_add('day', 5, date_trunc('week', \
+                                    date_add('day', 1, CAST({col} AS TIMESTAMP))))",
+        '1969-12-28T00:00:00Z/P1W': "date_add('day', -1, date_trunc('week', \
+                                    date_add('day', 1, CAST({col} AS TIMESTAMP))))",
+    }
 
     @classmethod
     def convert_dttm(cls, target_type, dttm):
@@ -1316,36 +1270,21 @@ class ClickHouseEngineSpec(BaseEngineSpec):
 
     time_secondary_columns = True
     time_groupby_inline = True
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('minute', _('minute'),
-              'toStartOfMinute(toDateTime({col}))',
-              'PT1M'),
-        Grain('5 minute', _('5 minute'),
-              'toDateTime(intDiv(toUInt32(toDateTime({col})), 300)*300)',
-              'PT5M'),
-        Grain('10 minute', _('10 minute'),
-              'toDateTime(intDiv(toUInt32(toDateTime({col})), 600)*600)',
-              'PT10M'),
-        Grain('hour', _('hour'),
-              'toStartOfHour(toDateTime({col}))',
-              'PT1H'),
-        Grain('day', _('day'),
-              'toStartOfDay(toDateTime({col}))',
-              'P1D'),
-        Grain('week', _('week'),
-              'toMonday(toDateTime({col}))',
-              'P1W'),
-        Grain('month', _('month'),
-              'toStartOfMonth(toDateTime({col}))',
-              'P1M'),
-        Grain('quarter', _('quarter'),
-              'toStartOfQuarter(toDateTime({col}))',
-              'P0.25Y'),
-        Grain('year', _('year'),
-              'toStartOfYear(toDateTime({col}))',
-              'P1Y'),
-    )
+
+    time_grain_functions = {
+        None: '{col}',
+        'PT1M': 'toStartOfMinute(toDateTime({col}))',
+        'PT5M': 'toDateTime(intDiv(toUInt32(toDateTime({col})), 300)*300)',
+        'PT10M': 'toDateTime(intDiv(toUInt32(toDateTime({col})), 600)*600)',
+        'PT15M': 'toDateTime(intDiv(toUInt32(toDateTime({col})), 900)*900)',
+        'PT0.5H': 'toDateTime(intDiv(toUInt32(toDateTime({col})), 1800)*1800)',
+        'PT1H': 'toStartOfHour(toDateTime({col}))',
+        'P1D': 'toStartOfDay(toDateTime({col}))',
+        'P1W': 'toMonday(toDateTime({col}))',
+        'P1M': 'toStartOfMonth(toDateTime({col}))',
+        'P0.25Y': 'toStartOfQuarter(toDateTime({col}))',
+        'P1Y': 'toStartOfYear(toDateTime({col}))',
+    }
 
     @classmethod
     def convert_dttm(cls, target_type, dttm):
@@ -1364,18 +1303,17 @@ class BQEngineSpec(BaseEngineSpec):
     As contributed by @mxmzdlv on issue #945"""
     engine = 'bigquery'
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'), 'TIMESTAMP_TRUNC({col}, SECOND)', 'PT1S'),
-        Grain('minute', _('minute'), 'TIMESTAMP_TRUNC({col}, MINUTE)', 'PT1M'),
-        Grain('hour', _('hour'), 'TIMESTAMP_TRUNC({col}, HOUR)', 'PT1H'),
-        Grain('day', _('day'), 'TIMESTAMP_TRUNC({col}, DAY)', 'P1D'),
-        Grain('week', _('week'), 'TIMESTAMP_TRUNC({col}, WEEK)', 'P1W'),
-        Grain('month', _('month'), 'TIMESTAMP_TRUNC({col}, MONTH)', 'P1M'),
-        Grain('quarter', _('quarter'),
-              'TIMESTAMP_TRUNC({col}, QUARTER)', 'P0.25Y'),
-        Grain('year', _('year'), 'TIMESTAMP_TRUNC({col}, YEAR)', 'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': 'TIMESTAMP_TRUNC({col}, SECOND)',
+        'PT1M': 'TIMESTAMP_TRUNC({col}, MINUTE)',
+        'PT1H': 'TIMESTAMP_TRUNC({col}, HOUR)',
+        'P1D': 'TIMESTAMP_TRUNC({col}, DAY)',
+        'P1W': 'TIMESTAMP_TRUNC({col}, WEEK)',
+        'P1M': 'TIMESTAMP_TRUNC({col}, MONTH)',
+        'P0.25Y': 'TIMESTAMP_TRUNC({col}, QUARTER)',
+        'P1Y': 'TIMESTAMP_TRUNC({col}, YEAR)',
+    }
 
     @classmethod
     def convert_dttm(cls, target_type, dttm):
@@ -1397,16 +1335,16 @@ class ImpalaEngineSpec(BaseEngineSpec):
 
     engine = 'impala'
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('minute', _('minute'), "TRUNC({col}, 'MI')", 'PT1M'),
-        Grain('hour', _('hour'), "TRUNC({col}, 'HH')", 'PT1H'),
-        Grain('day', _('day'), "TRUNC({col}, 'DD')", 'P1D'),
-        Grain('week', _('week'), "TRUNC({col}, 'WW')", 'P1W'),
-        Grain('month', _('month'), "TRUNC({col}, 'MONTH')", 'P1M'),
-        Grain('quarter', _('quarter'), "TRUNC({col}, 'Q')", 'P0.25Y'),
-        Grain('year', _('year'), "TRUNC({col}, 'YYYY')", 'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1M': "TRUNC({col}, 'MI')",
+        'PT1H': "TRUNC({col}, 'HH')",
+        'P1D': "TRUNC({col}, 'DD')",
+        'P1W': "TRUNC({col}, 'WW')",
+        'P1M': "TRUNC({col}, 'MONTH')",
+        'P0.25Y': "TRUNC({col}, 'Q')",
+        'P1Y': "TRUNC({col}, 'YYYY')",
+    }
 
     @classmethod
     def epoch_to_dttm(cls):
@@ -1431,17 +1369,17 @@ class DruidEngineSpec(BaseEngineSpec):
     engine = 'druid'
     inner_joins = False
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'), 'FLOOR({col} TO SECOND)', 'PT1S'),
-        Grain('minute', _('minute'), 'FLOOR({col} TO MINUTE)', 'PT1M'),
-        Grain('hour', _('hour'), 'FLOOR({col} TO HOUR)', 'PT1H'),
-        Grain('day', _('day'), 'FLOOR({col} TO DAY)', 'P1D'),
-        Grain('week', _('week'), 'FLOOR({col} TO WEEK)', 'P1W'),
-        Grain('month', _('month'), 'FLOOR({col} TO MONTH)', 'P1M'),
-        Grain('quarter', _('quarter'), 'FLOOR({col} TO QUARTER)', 'P3M'),
-        Grain('year', _('year'), 'FLOOR({col} TO YEAR)', 'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': 'FLOOR({col} TO SECOND)',
+        'PT1M': 'FLOOR({col} TO MINUTE)',
+        'PT1H': 'FLOOR({col} TO HOUR)',
+        'P1D': 'FLOOR({col} TO DAY)',
+        'P1W': 'FLOOR({col} TO WEEK)',
+        'P1M': 'FLOOR({col} TO MONTH)',
+        'P0.25Y': 'FLOOR({col} TO QUARTER)',
+        'P1Y': 'FLOOR({col} TO YEAR)',
+    }
 
 
 class KylinEngineSpec(BaseEngineSpec):
@@ -1449,35 +1387,19 @@ class KylinEngineSpec(BaseEngineSpec):
 
     engine = 'kylin'
 
-    time_grains = (
-        Grain('Time Column', _('Time Column'), '{col}', None),
-        Grain('second', _('second'),
-              'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO SECOND) AS TIMESTAMP)',
-              'PT1S'),
-        Grain('minute', _('minute'),
-              'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO MINUTE) AS TIMESTAMP)',
-              'PT1M'),
-        Grain('hour', _('hour'),
-              'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO HOUR) AS TIMESTAMP)',
-              'PT1H'),
-        Grain('day', _('day'),
-              'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO DAY) AS DATE)',
-              'P1D'),
-        Grain('week', _('week'),
-              'CAST(TIMESTAMPADD(WEEK, WEEK(CAST({col} AS DATE)) - 1, \
-              FLOOR(CAST({col} AS TIMESTAMP) TO YEAR)) AS DATE)',
-              'P1W'),
-        Grain('month', _('month'),
-              'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO MONTH) AS DATE)',
-              'P1M'),
-        Grain('quarter', _('quarter'),
-              'CAST(TIMESTAMPADD(QUARTER, QUARTER(CAST({col} AS DATE)) - 1, \
-              FLOOR(CAST({col} AS TIMESTAMP) TO YEAR)) AS DATE)',
-              'P0.25Y'),
-        Grain('year', _('year'),
-              'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO YEAR) AS DATE)',
-              'P1Y'),
-    )
+    time_grain_functions = {
+        None: '{col}',
+        'PT1S': 'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO SECOND) AS TIMESTAMP)',
+        'PT1M': 'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO MINUTE) AS TIMESTAMP)',
+        'PT1H': 'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO HOUR) AS TIMESTAMP)',
+        'P1D': 'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO DAY) AS DATE)',
+        'P1W': 'CAST(TIMESTAMPADD(WEEK, WEEK(CAST({col} AS DATE)) - 1, \
+               FLOOR(CAST({col} AS TIMESTAMP) TO YEAR)) AS DATE)',
+        'P1M': 'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO MONTH) AS DATE)',
+        'P0.25Y': 'CAST(TIMESTAMPADD(QUARTER, QUARTER(CAST({col} AS DATE)) - 1, \
+                  FLOOR(CAST({col} AS TIMESTAMP) TO YEAR)) AS DATE)',
+        'P1Y': 'CAST(FLOOR(CAST({col} AS TIMESTAMP) TO YEAR) AS DATE)',
+    }
 
     @classmethod
     def convert_dttm(cls, target_type, dttm):
diff --git a/superset/models/core.py b/superset/models/core.py
index 60af53d..0070498 100644
--- a/superset/models/core.py
+++ b/superset/models/core.py
@@ -832,7 +832,7 @@ class Database(Model, AuditMixinNullable, ImportMixin):
         each database has slightly different but similar datetime functions,
         this allows a mapping between database engines and actual functions.
         """
-        return self.db_engine_spec.time_grains
+        return self.db_engine_spec.get_time_grains()
 
     def grains_dict(self):
         """Allowing to lookup grain by either label or duration
diff --git a/tests/db_engine_specs_test.py b/tests/db_engine_specs_test.py
index 1b340b4..81709dc 100644
--- a/tests/db_engine_specs_test.py
+++ b/tests/db_engine_specs_test.py
@@ -4,8 +4,11 @@ from __future__ import division
 from __future__ import print_function
 from __future__ import unicode_literals
 
+import inspect
+
 from six import text_type
 
+from superset import db_engine_specs
 from superset.db_engine_specs import (
     BaseEngineSpec, HiveEngineSpec, MssqlEngineSpec,
     MySQLEngineSpec, PrestoEngineSpec,
@@ -264,3 +267,29 @@ class DbEngineSpecsTestCase(SupersetTestCase):
                 SELECT
                     'LIMIT 777' LIMIT 1000""",
         )
+
+    def test_time_grain_blacklist(self):
+        blacklist = ['PT1M']
+        time_grains = {
+            'PT1S': 'second',
+            'PT1M': 'minute',
+        }
+        time_grain_functions = {
+            'PT1S': '{col}',
+            'PT1M': '{col}',
+        }
+        time_grains = db_engine_specs._create_time_grains_tuple(time_grains,
+                                                                time_grain_functions,
+                                                                blacklist)
+        self.assertEqual(1, len(time_grains))
+        self.assertEqual('PT1S', time_grains[0].duration)
+
+    def test_engine_time_grain_validity(self):
+        time_grains = set(db_engine_specs.builtin_time_grains.keys())
+        # loop over all subclasses of BaseEngineSpec
+        for cls_name, cls in inspect.getmembers(db_engine_specs):
+            if inspect.isclass(cls) and issubclass(cls, BaseEngineSpec):
+                # make sure that all defined time grains are supported
+                defined_time_grains = {grain.duration for grain in cls.get_time_grains()}
+                intersection = time_grains.intersection(defined_time_grains)
+                self.assertSetEqual(defined_time_grains, intersection, cls_name)


Mime
View raw message