You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2018/07/31 06:44:32 UTC
[GitHub] mistercrunch closed pull request #5380: Add time grain blacklist
and addons to config.py
mistercrunch closed pull request #5380: Add time grain blacklist and addons to config.py
URL: https://github.com/apache/incubator-superset/pull/5380
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
diff --git a/superset/config.py b/superset/config.py
index 991febfcba..6c3c526ef9 100644
--- a/superset/config.py
+++ b/superset/config.py
@@ -200,6 +200,30 @@
'encoding': 'utf-8',
}
+# ---------------------------------------------------
+# 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:
diff --git a/superset/db_engine_specs.py b/superset/db_engine_specs.py
index ebc8802668..ee14017736 100644
--- a/superset/db_engine_specs.py
+++ b/superset/db_engine_specs.py
@@ -52,6 +52,36 @@
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,12 +95,22 @@ 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:
@@ -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 @@ def get_table_names(cls, schema, inspector):
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 @@ def convert_dttm(cls, target_type, dttm):
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 @@ def convert_dttm(cls, target_type, dttm):
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 @@ def get_table_names(cls, schema, inspector):
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 @@ def extract_error_message(cls, e):
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 @@ def convert_dttm(cls, target_type, dttm):
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 60af53d0b2..007049827b 100644
--- a/superset/models/core.py
+++ b/superset/models/core.py
@@ -832,7 +832,7 @@ def grains(self):
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 1b340b4dcd..81709dc67b 100644
--- a/tests/db_engine_specs_test.py
+++ b/tests/db_engine_specs_test.py
@@ -4,8 +4,11 @@
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 @@ def test_limit_with_non_token_limit(self):
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)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org