SQL基础：Superset实现动态SQL查询功能

regex = re.compile(
r”\{\{.*(”
r”current_user_id\(.*\)|”
r”current_username\(.*\)|”
r”current_userroles\(.*\)|”
r”isadmin\(.*\)|”
r”cache_key_wrapper\(.*\)|”
r”url_param\(.*\)”
r”).*\}\}”
)

def current_user_id(self, add_to_cache_keys: bool = True) -> Optional[int]:
“””
Return the user ID of the user who is currently logged in.

:param add_to_cache_keys: Whether the value should be included in the cache key
:returns: The user ID
“””

if hasattr(g, “user”) and g.user:
if add_to_cache_keys:
self.cache_key_wrapper(g.user.get_id())
return g.user.get_id()
return None

def current_username(self, add_to_cache_keys: bool = True) -> Optional[str]:
“””
Return the username of the user who is currently logged in.

:param add_to_cache_keys: Whether the value should be included in the cache key
:returns: The username
“””

if g.user and hasattr(g.user, “username”):
if add_to_cache_keys:
self.cache_key_wrapper(g.user.username)
return g.user.username
return None
def current_userroles(self, add_to_cache_keys: bool = True) -> Optional[str]:
“””
Return the roles of the user who is currently logged in.

:param add_to_cache_keys: Whether the value should be included in the cache key
:returns: The userroles
“””

if g.user and hasattr(g.user, “roles”):
if add_to_cache_keys:
user_roles = “/”.join([role.name.lower() for role in list(g.user.roles)])
self.cache_key_wrapper(user_roles)
print(user_roles)
return user_roles
“””admin in user_roles”””
return None

def isadmin(self, add_to_cache_keys: bool = True) -> Optional[str]:
“””
Return the roles of the user who is currently logged in.

:param add_to_cache_keys: Whether the value should be included in the cache key
:returns: The userroles
“””

if g.user and hasattr(g.user, “roles”):
if add_to_cache_keys:
user_roles = [role.name.lower() for role in list(g.user.roles)]
return “admin” in user_roles
return None

class JinjaTemplateProcessor(BaseTemplateProcessor):
def set_context(self, **kwargs: Any) -> None:
super().set_context(**kwargs)
extra_cache = ExtraCache(self._extra_cache_keys)
self._context.update(
{
“url_param”: partial(safe_proxy, extra_cache.url_param),
“current_user_id”: partial(safe_proxy, extra_cache.current_user_id),
“current_username”: partial(safe_proxy, extra_cache.current_username),
“current_userroles”: partial(safe_proxy, extra_cache.current_userroles),
“isadmin”: partial(safe_proxy, extra_cache.isadmin),
“cache_key_wrapper”: partial(safe_proxy, extra_cache.cache_key_wrapper),
“filter_values”: partial(safe_proxy, filter_values),
}
)

ALLOWED_TYPES = (
NONE_TYPE,
“bool”,
“str”,
“unicode”,
“int”,
“long”,
“float”,
“list”,
“dict”,
“tuple”,
“set”,
)

select DATE,risktype,sum(num) as num from
(SELECT date , customerid,product,risktype ,count(*) as num
from v_superset_forecast_risk group by date , customerid,product,risktype ) a
join
(select distinct customer_code,product from v_superset_access
where name='{{ current_username() }}’ )access
on a.customerid=access.customer_code
and a.product=access.product
and DATE_FORMAT(date,’%Y-%m’)> DATE_FORMAT(date_sub(STR_TO_DATE(concat( {{ “‘” + “‘, ‘”.join(filter_values(‘yearmonthend’)) + “‘” }},’-01′), ‘%Y-%m-%d’), interval 12 month),’%Y-%m’)
and DATE_FORMAT(date,’%Y-%m’)<={{ “‘” + “‘, ‘”.join(filter_values(‘yearmonthend’)) + “‘” }}
group by DATE,risktype