"use strict";(self.webpackChunkdocs_v_2=self.webpackChunkdocs_v_2||[]).push([[1444],{44599:(e,t,n)=>{n.r(t),n.d(t,{assets:()=>l,contentTitle:()=>o,default:()=>u,frontMatter:()=>i,metadata:()=>s,toc:()=>d});var a=n(83117),r=(n(67294),n(3905));const i={title:"Using Database Connection UI",hide_title:!0,sidebar_position:3,version:1},o=void 0,s={unversionedId:"databases/db-connection-ui",id:"databases/db-connection-ui",title:"Using Database Connection UI",description:"Here is the documentation on how to leverage the new DB Connection UI. This will provide admins the ability to enhance the UX for users who want to connect to new databases.",source:"@site/docs/databases/db-connection-ui.mdx",sourceDirName:"databases",slug:"/databases/db-connection-ui",permalink:"/docs/databases/db-connection-ui",draft:!1,editUrl:"https://github.com/apache/superset/tree/master/docs/docs/databases/db-connection-ui.mdx",tags:[],version:"current",sidebarPosition:3,frontMatter:{title:"Using Database Connection UI",hide_title:!0,sidebar_position:3,version:1},sidebar:"tutorialSidebar",previous:{title:"Adding New Drivers in Docker",permalink:"/docs/databases/docker-add-drivers"},next:{title:"Amazon Athena",permalink:"/docs/databases/athena"}},l={},d=[{value:"How to setup up preferred database options and images",id:"how-to-setup-up-preferred-database-options-and-images",level:3},{value:"Setting images",id:"setting-images",level:3},{value:"How to add new database engines to available endpoint",id:"how-to-add-new-database-engines-to-available-endpoint",level:3}],p={toc:d},c="wrapper";function u(e){let{components:t,...n}=e;return(0,r.kt)(c,(0,a.Z)({},p,n,{components:t,mdxType:"MDXLayout"}),(0,r.kt)("p",null,"Here is the documentation on how to leverage the new DB Connection UI. This will provide admins the ability to enhance the UX for users who want to connect to new databases."),(0,r.kt)("p",null,(0,r.kt)("img",{parentName:"p",src:"https://user-images.githubusercontent.com/27827808/125499607-94e300aa-1c0f-4c60-b199-3f9de41060a3.gif",alt:"db-conn-docs"})),(0,r.kt)("p",null,"There are now 3 steps when connecting to a database in the new UI:"),(0,r.kt)("p",null,"Step 1: First the admin must inform superset what engine they want to connect to. This page is powered by the ",(0,r.kt)("inlineCode",{parentName:"p"},"/available")," endpoint which pulls on the engines currently installed in your environment, so that only supported databases are shown."),(0,r.kt)("p",null,"Step 2: Next, the admin is prompted to enter database specific parameters. Depending on whether there is a dynamic form available for that specific engine, the admin will either see the new custom form or the legacy SQLAlchemy form. We currently have built dynamic forms for (Redshift, MySQL, Postgres, and BigQuery). The new form prompts the user for the parameters needed to connect (for example, username, password, host, port, etc.) and provides immediate feedback on errors."),(0,r.kt)("p",null,"Step 3: Finally, once the admin has connected to their DB using the dynamic form they have the opportunity to update any optional advanced settings."),(0,r.kt)("p",null,"We hope this feature will help eliminate a huge bottleneck for users to get into the application and start crafting datasets."),(0,r.kt)("h3",{id:"how-to-setup-up-preferred-database-options-and-images"},"How to setup up preferred database options and images"),(0,r.kt)("p",null,"We added a new configuration option where the admin can define their preferred databases, in order:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-python"},'# A list of preferred databases, in order. These databases will be\n# displayed prominently in the "Add Database" dialog. You should\n# use the "engine_name" attribute of the corresponding DB engine spec\n# in `superset/db_engine_specs/`.\nPREFERRED_DATABASES: list[str] = [\n    "PostgreSQL",\n    "Presto",\n    "MySQL",\n    "SQLite",\n]\n')),(0,r.kt)("p",null,"For copyright reasons the logos for each database are not distributed with Superset."),(0,r.kt)("h3",{id:"setting-images"},"Setting images"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},"To set the images of your preferred database, admins must create a mapping in the ",(0,r.kt)("inlineCode",{parentName:"li"},"superset_text.yml")," file with engine and location of the image. The image can be host locally inside your static/file directory or online (e.g. S3)")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-python"},'DB_IMAGES:\n  postgresql: "path/to/image/postgres.jpg"\n  bigquery: "path/to/s3bucket/bigquery.jpg"\n  snowflake: "path/to/image/snowflake.jpg"\n')),(0,r.kt)("h3",{id:"how-to-add-new-database-engines-to-available-endpoint"},"How to add new database engines to available endpoint"),(0,r.kt)("p",null,"Currently the new modal supports the following databases:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},"Postgres"),(0,r.kt)("li",{parentName:"ul"},"Redshift"),(0,r.kt)("li",{parentName:"ul"},"MySQL"),(0,r.kt)("li",{parentName:"ul"},"BigQuery")),(0,r.kt)("p",null,"When the user selects a database not in this list they will see the old dialog asking for the SQLAlchemy URI. New databases can be added gradually to the new flow. In order to support the rich configuration a DB engine spec needs to have the following attributes:"),(0,r.kt)("ol",null,(0,r.kt)("li",{parentName:"ol"},(0,r.kt)("inlineCode",{parentName:"li"},"parameters_schema"),": a Marshmallow schema defining the parameters needed to configure the database. For Postgres this includes username, password, host, port, etc. (",(0,r.kt)("a",{parentName:"li",href:"https://github.com/apache/superset/blob/accee507c0819cd0d7bcfb5a3e1199bc81eeebf2/superset/db_engine_specs/base.py#L1309-L1320"},"see"),")."),(0,r.kt)("li",{parentName:"ol"},(0,r.kt)("inlineCode",{parentName:"li"},"default_driver"),': the name of the recommended driver for the DB engine spec. Many SQLAlchemy dialects support multiple drivers, but usually one is the official recommendation. For Postgres we use "psycopg2".'),(0,r.kt)("li",{parentName:"ol"},(0,r.kt)("inlineCode",{parentName:"li"},"sqlalchemy_uri_placeholder"),": a string that helps the user in case they want to type the URI directly."),(0,r.kt)("li",{parentName:"ol"},(0,r.kt)("inlineCode",{parentName:"li"},"encryption_parameters"),": parameters used to build the URI when the user opts for an encrypted connection. For Postgres this is ",(0,r.kt)("inlineCode",{parentName:"li"},'{"sslmode": "require"}'),".")),(0,r.kt)("p",null,"In addition, the DB engine spec must implement these class methods:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},(0,r.kt)("inlineCode",{parentName:"li"},"build_sqlalchemy_uri(cls, parameters, encrypted_extra)"),": this method receives the distinct parameters and builds the URI from them."),(0,r.kt)("li",{parentName:"ul"},(0,r.kt)("inlineCode",{parentName:"li"},"get_parameters_from_uri(cls, uri, encrypted_extra)"),": this method does the opposite, extracting the parameters from a given URI."),(0,r.kt)("li",{parentName:"ul"},(0,r.kt)("inlineCode",{parentName:"li"},"validate_parameters(cls, parameters)"),": this method is used for ",(0,r.kt)("inlineCode",{parentName:"li"},"onBlur")," validation of the form. It should return a list of ",(0,r.kt)("inlineCode",{parentName:"li"},"SupersetError")," indicating which parameters are missing, and which parameters are definitely incorrect (",(0,r.kt)("a",{parentName:"li",href:"https://github.com/apache/superset/blob/accee507c0819cd0d7bcfb5a3e1199bc81eeebf2/superset/db_engine_specs/base.py#L1404"},"example"),").")),(0,r.kt)("p",null,"For databases like MySQL and Postgres that use the standard format of ",(0,r.kt)("inlineCode",{parentName:"p"},"engine+driver://user:password@host:port/dbname")," all you need to do is add the ",(0,r.kt)("inlineCode",{parentName:"p"},"BasicParametersMixin")," to the DB engine spec, and then define the parameters 2-4 (",(0,r.kt)("inlineCode",{parentName:"p"},"parameters_schema")," is already present in the mixin)."),(0,r.kt)("p",null,"For other databases you need to implement these methods yourself. The BigQuery DB engine spec is a good example of how to do that."))}u.isMDXComponent=!0},3905:(e,t,n)=>{n.d(t,{Zo:()=>p,kt:()=>m});var a=n(67294);function r(e,t,n){return t in e?Object.defineProperty(e,t,{value:n,enumerable:!0,configurable:!0,writable:!0}):e[t]=n,e}function i(e,t){var n=Object.keys(e);if(Object.getOwnPropertySymbols){var a=Object.getOwnPropertySymbols(e);t&&(a=a.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),n.push.apply(n,a)}return n}function o(e){for(var t=1;t<arguments.length;t++){var n=null!=arguments[t]?arguments[t]:{};t%2?i(Object(n),!0).forEach((function(t){r(e,t,n[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(n)):i(Object(n)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(n,t))}))}return e}function s(e,t){if(null==e)return{};var n,a,r=function(e,t){if(null==e)return{};var n,a,r={},i=Object.keys(e);for(a=0;a<i.length;a++)n=i[a],t.indexOf(n)>=0||(r[n]=e[n]);return r}(e,t);if(Object.getOwnPropertySymbols){var i=Object.getOwnPropertySymbols(e);for(a=0;a<i.length;a++)n=i[a],t.indexOf(n)>=0||Object.prototype.propertyIsEnumerable.call(e,n)&&(r[n]=e[n])}return r}var l=a.createContext({}),d=function(e){var t=a.useContext(l),n=t;return e&&(n="function"==typeof e?e(t):o(o({},t),e)),n},p=function(e){var t=d(e.components);return a.createElement(l.Provider,{value:t},e.children)},c="mdxType",u={inlineCode:"code",wrapper:function(e){var t=e.children;return a.createElement(a.Fragment,{},t)}},h=a.forwardRef((function(e,t){var n=e.components,r=e.mdxType,i=e.originalType,l=e.parentName,p=s(e,["components","mdxType","originalType","parentName"]),c=d(n),h=r,m=c["".concat(l,".").concat(h)]||c[h]||u[h]||i;return n?a.createElement(m,o(o({ref:t},p),{},{components:n})):a.createElement(m,o({ref:t},p))}));function m(e,t){var n=arguments,r=t&&t.mdxType;if("string"==typeof e||r){var i=n.length,o=new Array(i);o[0]=h;var s={};for(var l in t)hasOwnProperty.call(t,l)&&(s[l]=t[l]);s.originalType=e,s[c]="string"==typeof e?e:r,o[1]=s;for(var d=2;d<i;d++)o[d]=n[d];return a.createElement.apply(null,o)}return a.createElement.apply(null,n)}h.displayName="MDXCreateElement"}}]);