| (window.webpackJsonp=window.webpackJsonp||[]).push([[57],{o4NC:function(e,t,n){"use strict";n.r(t),n.d(t,"_frontmatter",(function(){return i})),n.d(t,"default",(function(){return p}));var a=n("k1TG"),s=n("8o2o"),r=(n("q1tI"),n("7ljp")),o=n("hhGP"),i=(n("qKvR"),{});void 0!==i&&i&&i===Object(i)&&Object.isExtensible(i)&&!i.hasOwnProperty("__filemeta")&&Object.defineProperty(i,"__filemeta",{configurable:!0,value:{name:"_frontmatter",filename:"src/pages/docs/installation/sql_templating.mdx"}});var c={_frontmatter:i},l=o.a;function p(e){var t=e.components,n=Object(s.a)(e,["components"]);return Object(r.b)(l,Object(a.a)({},c,n,{components:t,mdxType:"MDXLayout"}),Object(r.b)("h2",{id:"sql-templating"},"SQL Templating"),Object(r.b)("h3",{id:"jinja-templates"},"Jinja Templates"),Object(r.b)("p",null,"SQL Lab supports ",Object(r.b)("a",Object(a.a)({parentName:"p"},{href:"https://jinja.palletsprojects.com/en/2.11.x/"}),"Jinja templating")," in queries. You'll\nneed to to overload the default Jinja context in your environment by defining the\nJINJA_CONTEXT_ADDONS in your superset configuration (",Object(r.b)("inlineCode",{parentName:"p"},"superset_config.py"),"). Objects referenced in\nthis dictionary are made available for users to use in their SQL code."),Object(r.b)("pre",null,Object(r.b)("code",Object(a.a)({parentName:"pre"},{className:"language-python"}),"JINJA_CONTEXT_ADDONS = {\n 'my_crazy_macro': lambda x: x*2,\n}\n")),Object(r.b)("p",null,"Besides default Jinja templating, SQL lab also supports self-defined template processor by setting\nthe ",Object(r.b)("inlineCode",{parentName:"p"},"CUSTOM_TEMPLATE_PROCESSORS")," in your superset configuration. The values in this dictionary\noverwrite the default Jinja template processors of the specified database engine. The example below\nconfigures a custom presto template processor which implements its own logic of processing macro\ntemplate with regex parsing. It uses the ",Object(r.b)("inlineCode",{parentName:"p"},"$")," style macro instead of ",Object(r.b)("inlineCode",{parentName:"p"},"{{ }}")," style in Jinja\ntemplating."),Object(r.b)("p",null,"By configuring it with ",Object(r.b)("inlineCode",{parentName:"p"},"CUSTOM_TEMPLATE_PROCESSORS"),", a SQL template on a presto database is\nprocessed by the custom one rather than the default one."),Object(r.b)("pre",null,Object(r.b)("code",Object(a.a)({parentName:"pre"},{className:"language-python"}),'def DATE(\n ts: datetime, day_offset: SupportsInt = 0, hour_offset: SupportsInt = 0\n) -> str:\n """Current day as a string."""\n day_offset, hour_offset = int(day_offset), int(hour_offset)\n offset_day = (ts + timedelta(days=day_offset, hours=hour_offset)).date()\n return str(offset_day)\n\nclass CustomPrestoTemplateProcessor(PrestoTemplateProcessor):\n """A custom presto template processor."""\n\n engine = "presto"\n\n def process_template(self, sql: str, **kwargs) -> str:\n """Processes a sql template with $ style macro using regex."""\n # Add custom macros functions.\n macros = {\n "DATE": partial(DATE, datetime.utcnow())\n } # type: Dict[str, Any]\n # Update with macros defined in context and kwargs.\n macros.update(self.context)\n macros.update(kwargs)\n\n def replacer(match):\n """Expand $ style macros with corresponding function calls."""\n macro_name, args_str = match.groups()\n args = [a.strip() for a in args_str.split(",")]\n if args == [""]:\n args = []\n f = macros[macro_name[1:]]\n return f(*args)\n\n macro_names = ["$" + name for name in macros.keys()]\n pattern = r"(%s)\\s*\\(([^()]*)\\)" % "|".join(map(re.escape, macro_names))\n return re.sub(pattern, replacer, sql)\n\nCUSTOM_TEMPLATE_PROCESSORS = {\n CustomPrestoTemplateProcessor.engine: CustomPrestoTemplateProcessor\n}\n')),Object(r.b)("p",null,"SQL Lab also includes a live query validation feature with pluggable backends. You can configure\nwhich validation implementation is used with which database engine by adding a block like the\nfollowing to your configuration file:"),Object(r.b)("pre",null,Object(r.b)("code",Object(a.a)({parentName:"pre"},{className:"language-python"}),"FEATURE_FLAGS = {\n 'SQL_VALIDATORS_BY_ENGINE': {\n 'presto': 'PrestoDBSQLValidator',\n }\n}\n")),Object(r.b)("p",null,"The available validators and names can be found in\n",Object(r.b)("a",Object(a.a)({parentName:"p"},{href:"https://github.com/apache/superset/tree/master/superset/sql_validators"}),"sql_validators"),"."))}void 0!==p&&p&&p===Object(p)&&Object.isExtensible(p)&&!p.hasOwnProperty("__filemeta")&&Object.defineProperty(p,"__filemeta",{configurable:!0,value:{name:"MDXContent",filename:"src/pages/docs/installation/sql_templating.mdx"}}),p.isMDXComponent=!0}}]); |
