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+ville...@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)
