| "use strict";(self.webpackChunkdoris_website=self.webpackChunkdoris_website||[]).push([[66587],{15680:(e,a,t)=>{t.d(a,{xA:()=>p,yg:()=>y});var n=t(296540);function r(e,a,t){return a in e?Object.defineProperty(e,a,{value:t,enumerable:!0,configurable:!0,writable:!0}):e[a]=t,e}function i(e,a){var t=Object.keys(e);if(Object.getOwnPropertySymbols){var n=Object.getOwnPropertySymbols(e);a&&(n=n.filter((function(a){return Object.getOwnPropertyDescriptor(e,a).enumerable}))),t.push.apply(t,n)}return t}function o(e){for(var a=1;a<arguments.length;a++){var t=null!=arguments[a]?arguments[a]:{};a%2?i(Object(t),!0).forEach((function(a){r(e,a,t[a])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(t)):i(Object(t)).forEach((function(a){Object.defineProperty(e,a,Object.getOwnPropertyDescriptor(t,a))}))}return e}function l(e,a){if(null==e)return{};var t,n,r=function(e,a){if(null==e)return{};var t,n,r={},i=Object.keys(e);for(n=0;n<i.length;n++)t=i[n],a.indexOf(t)>=0||(r[t]=e[t]);return r}(e,a);if(Object.getOwnPropertySymbols){var i=Object.getOwnPropertySymbols(e);for(n=0;n<i.length;n++)t=i[n],a.indexOf(t)>=0||Object.prototype.propertyIsEnumerable.call(e,t)&&(r[t]=e[t])}return r}var s=n.createContext({}),d=function(e){var a=n.useContext(s),t=a;return e&&(t="function"==typeof e?e(a):o(o({},a),e)),t},p=function(e){var a=d(e.components);return n.createElement(s.Provider,{value:a},e.children)},c="mdxType",m={inlineCode:"code",wrapper:function(e){var a=e.children;return n.createElement(n.Fragment,{},a)}},g=n.forwardRef((function(e,a){var t=e.components,r=e.mdxType,i=e.originalType,s=e.parentName,p=l(e,["components","mdxType","originalType","parentName"]),c=d(t),g=r,y=c["".concat(s,".").concat(g)]||c[g]||m[g]||i;return t?n.createElement(y,o(o({ref:a},p),{},{components:t})):n.createElement(y,o({ref:a},p))}));function y(e,a){var t=arguments,r=a&&a.mdxType;if("string"==typeof e||r){var i=t.length,o=new Array(i);o[0]=g;var l={};for(var s in a)hasOwnProperty.call(a,s)&&(l[s]=a[s]);l.originalType=e,l[c]="string"==typeof e?e:r,o[1]=l;for(var d=2;d<i;d++)o[d]=t[d];return n.createElement.apply(null,o)}return n.createElement.apply(null,t)}g.displayName="MDXCreateElement"},345503:(e,a,t)=>{t.r(a),t.d(a,{assets:()=>s,contentTitle:()=>o,default:()=>m,frontMatter:()=>i,metadata:()=>l,toc:()=>d});var n=t(58168),r=(t(296540),t(15680));const i={title:"JDBC Catalog",language:"en"},o=void 0,l={unversionedId:"lakehouse/database/jdbc",id:"lakehouse/database/jdbc",title:"JDBC Catalog",description:"\x3c!--",source:"@site/docs/lakehouse/database/jdbc.md",sourceDirName:"lakehouse/database",slug:"/lakehouse/database/jdbc",permalink:"/docs/dev/lakehouse/database/jdbc",draft:!1,tags:[],version:"current",frontMatter:{title:"JDBC Catalog",language:"en"},sidebar:"docs",previous:{title:"Hive",permalink:"/docs/dev/lakehouse/datalake-building/hive-build"},next:{title:"MySQL",permalink:"/docs/dev/lakehouse/database/mysql"}},s={},d=[{value:"Supported databases",id:"supported-databases",level:2},{value:"Configuration",id:"configuration",level:2},{value:"Basic properties",id:"basic-properties",level:3},{value:"Optional properties",id:"optional-properties",level:3},{value:"Connection pool properties",id:"connection-pool-properties",level:3},{value:"Property Notes",id:"property-notes",level:2},{value:"Driver package path and security",id:"driver-package-path-and-security",level:3},{value:"Lowercase name synchronization",id:"lowercase-name-synchronization",level:3},{value:"Specify synchronization database",id:"specify-synchronization-database",level:3},{value:"Connection pool configuration",id:"connection-pool-configuration",level:3},{value:"Insert transaction",id:"insert-transaction",level:3},{value:"Example",id:"example",level:2},{value:"Statement transparent transmission",id:"statement-transparent-transmission",level:2},{value:"Transparent transmission of DDL and DML",id:"transparent-transmission-of-ddl-and-dml",level:3},{value:"Transparent query",id:"transparent-query",level:3},{value:"Principles and Limitations",id:"principles-and-limitations",level:3}],p={toc:d},c="wrapper";function m(e){let{components:a,...t}=e;return(0,r.yg)(c,(0,n.A)({},p,t,{components:a,mdxType:"MDXLayout"}),(0,r.yg)("p",null,"Doris JDBC Catalog supports connecting to different databases that support the JDBC protocol through the standard JDBC interface. This document introduces the general configuration and usage of JDBC Catalog."),(0,r.yg)("h2",{id:"supported-databases"},"Supported databases"),(0,r.yg)("p",null,"Doris JDBC Catalog supports connecting to the following databases:"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Database"),(0,r.yg)("th",{parentName:"tr",align:null},"Description"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("a",{parentName:"td",href:"/docs/dev/lakehouse/database/mysql"},"MySQL")),(0,r.yg)("td",{parentName:"tr",align:null})),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("a",{parentName:"td",href:"/docs/dev/lakehouse/database/postgresql"},"PostgreSQL")),(0,r.yg)("td",{parentName:"tr",align:null})),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("a",{parentName:"td",href:"/docs/dev/lakehouse/database/oracle"},"Oracle")),(0,r.yg)("td",{parentName:"tr",align:null})),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("a",{parentName:"td",href:"/docs/dev/lakehouse/database/sqlserver"},"SQL Server")),(0,r.yg)("td",{parentName:"tr",align:null})),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("a",{parentName:"td",href:"/docs/dev/lakehouse/database/ibm-db2"},"IBM Db2")),(0,r.yg)("td",{parentName:"tr",align:null})),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("a",{parentName:"td",href:"/docs/dev/lakehouse/database/clickhouse"},"ClickHouse")),(0,r.yg)("td",{parentName:"tr",align:null})),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("a",{parentName:"td",href:"/docs/dev/lakehouse/database/sap-hana"},"SAP HANA")),(0,r.yg)("td",{parentName:"tr",align:null})),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("a",{parentName:"td",href:"/docs/dev/lakehouse/database/oceanbase"},"OceanBase")),(0,r.yg)("td",{parentName:"tr",align:null})))),(0,r.yg)("h2",{id:"configuration"},"Configuration"),(0,r.yg)("h3",{id:"basic-properties"},"Basic properties"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Parameters"),(0,r.yg)("th",{parentName:"tr",align:null},"Description"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"type")),(0,r.yg)("td",{parentName:"tr",align:null},"Fixed to ",(0,r.yg)("inlineCode",{parentName:"td"},"jdbc"))),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"user")),(0,r.yg)("td",{parentName:"tr",align:null},"Data source user name")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"password")),(0,r.yg)("td",{parentName:"tr",align:null},"Data source password")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"jdbc_url")),(0,r.yg)("td",{parentName:"tr",align:null},"Data source connection URL")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"driver_url")),(0,r.yg)("td",{parentName:"tr",align:null},"Path to the data source JDBC driver")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"driver_class")),(0,r.yg)("td",{parentName:"tr",align:null},"The class name of the data source JDBC driver")))),(0,r.yg)("h3",{id:"optional-properties"},"Optional properties"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Parameters"),(0,r.yg)("th",{parentName:"tr",align:null},"Default value"),(0,r.yg)("th",{parentName:"tr",align:null},"Description"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"lower_case_meta_names")),(0,r.yg)("td",{parentName:"tr",align:null},'"false"'),(0,r.yg)("td",{parentName:"tr",align:null},"Whether to synchronize the library name, table name and column name of the external data source in lowercase")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"meta_names_mapping")),(0,r.yg)("td",{parentName:"tr",align:null},'""'),(0,r.yg)("td",{parentName:"tr",align:null},"When the external data source has the same name but different case, such as DORIS and doris, Doris reports an error when querying the Catalog due to ambiguity. In this case, the ",(0,r.yg)("inlineCode",{parentName:"td"},"meta_names_mapping")," parameter needs to be configured to resolve the conflict.")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"only_specified_database")),(0,r.yg)("td",{parentName:"tr",align:null},'"false"'),(0,r.yg)("td",{parentName:"tr",align:null},"Whether to synchronize only the Database of the data source specified in the JDBC URL (Database here is the Database level mapped to Doris)")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"include_database_list")),(0,r.yg)("td",{parentName:"tr",align:null},'""'),(0,r.yg)("td",{parentName:"tr",align:null},"When ",(0,r.yg)("inlineCode",{parentName:"td"},"only_specified_database=true"),", specify to synchronize multiple Databases, separated by ','. Database names are case-sensitive.")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"exclude_database_list")),(0,r.yg)("td",{parentName:"tr",align:null},'""'),(0,r.yg)("td",{parentName:"tr",align:null},"When ",(0,r.yg)("inlineCode",{parentName:"td"},"only_specified_database=true"),", specify multiple Databases that do not need to be synchronized, separated by ','. Database names are case-sensitive.")))),(0,r.yg)("h3",{id:"connection-pool-properties"},"Connection pool properties"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Parameter"),(0,r.yg)("th",{parentName:"tr",align:null},"Default value"),(0,r.yg)("th",{parentName:"tr",align:null},"Description"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"connection_pool_min_size")),(0,r.yg)("td",{parentName:"tr",align:null},"1"),(0,r.yg)("td",{parentName:"tr",align:null},"Defines the minimum number of connections in the connection pool, which is used to initialize the connection pool and ensure that at least this number of connections are active when the keep-alive mechanism is enabled.")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"connection_pool_max_size")),(0,r.yg)("td",{parentName:"tr",align:null},"10"),(0,r.yg)("td",{parentName:"tr",align:null},"Defines the maximum number of connections in the connection pool. Each FE or BE node corresponding to each Catalog can hold up to this number of connections.")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"connection_pool_max_wait_time")),(0,r.yg)("td",{parentName:"tr",align:null},"5000"),(0,r.yg)("td",{parentName:"tr",align:null},"Defines the maximum number of milliseconds the client will wait for a connection if there is no available connection in the connection pool.")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"connection_pool_max_life_time")),(0,r.yg)("td",{parentName:"tr",align:null},"1800000"),(0,r.yg)("td",{parentName:"tr",align:null},"Set the maximum time (in milliseconds) that a connection remains active in the connection pool. Timed out connections will be recycled. At the same time, half of this value will serve as the minimum eviction idle time of the connection pool, and connections that reach this time will become eviction candidates.")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},(0,r.yg)("inlineCode",{parentName:"td"},"connection_pool_keep_alive")),(0,r.yg)("td",{parentName:"tr",align:null},"false"),(0,r.yg)("td",{parentName:"tr",align:null},"Only valid on BE nodes, used to decide whether to keep connections that have reached the minimum eviction idle time but have not reached the maximum lifetime active. Turned off by default to reduce unnecessary resource usage.")))),(0,r.yg)("h2",{id:"property-notes"},"Property Notes"),(0,r.yg)("h3",{id:"driver-package-path-and-security"},"Driver package path and security"),(0,r.yg)("p",null,(0,r.yg)("inlineCode",{parentName:"p"},"driver_url")," can be specified in the following three ways:"),(0,r.yg)("ol",null,(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("p",{parentName:"li"},"File name. Such as ",(0,r.yg)("inlineCode",{parentName:"p"},"mysql-connector-j-8.3.0.jar"),". The Jar package needs to be pre-stored in ",(0,r.yg)("inlineCode",{parentName:"p"},"jdbc_drivers/")," under the FE and BE deployment directories.\nUnder contents. The system will automatically search in this directory. The location of this directory can also be modified by the ",(0,r.yg)("inlineCode",{parentName:"p"},"jdbc_drivers_dir")," configuration in fe.conf and be.conf.")),(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("p",{parentName:"li"},"Local absolute path. Such as ",(0,r.yg)("inlineCode",{parentName:"p"},"file:///path/to/mysql-connector-j-8.3.0.jar"),". Jar packages need to be stored in the paths specified by all FE/BE nodes in advance.")),(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("p",{parentName:"li"},"HTTP address. For example: ",(0,r.yg)("a",{parentName:"p",href:"http://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.3.0/mysql-connector-j-8.3.0.jar"},"http://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.3.0/mysql-connector-j-8.3.0.jar")," The system will download the Driver file from this Http address. Only HTTP services without authentication are supported."))),(0,r.yg)("p",null,(0,r.yg)("strong",{parentName:"p"},"Driver package security")),(0,r.yg)("p",null,"In order to prevent the use of a Driver Jar package with an unallowed path when creating the Catalog, Doris will perform path management and checksum checking on the Jar package."),(0,r.yg)("ol",null,(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("p",{parentName:"li"},"For the above method 1, the ",(0,r.yg)("inlineCode",{parentName:"p"},"jdbc_drivers_dir")," configured by the Doris default user and all Jar packages in its directory are safe and will not be path checked.")),(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("p",{parentName:"li"},"For the above methods 2 and 3, Doris will check the source of the Jar package. The checking rules are as follows:"),(0,r.yg)("ul",{parentName:"li"},(0,r.yg)("li",{parentName:"ul"},"Control the allowed driver package paths through the FE configuration item ",(0,r.yg)("inlineCode",{parentName:"li"},"jdbc_driver_secure_path"),". This configuration item can configure multiple paths, separated by semicolons. When this option is configured, Doris\nIt will check whether the partial prefix of the path of driver_url in Catalog properties is in ",(0,r.yg)("inlineCode",{parentName:"li"},"jdbc_driver_secure_path"),". If it is not in it, the creation will be refused.\nCatalog."),(0,r.yg)("li",{parentName:"ul"},"This parameter defaults to ",(0,r.yg)("inlineCode",{parentName:"li"},"*"),", which means Jar packages of all paths are allowed."),(0,r.yg)("li",{parentName:"ul"},"If the configuration ",(0,r.yg)("inlineCode",{parentName:"li"},"jdbc_driver_secure_path")," is empty, it also means that Jar packages of all paths are allowed.")),(0,r.yg)("admonition",{parentName:"li",title:"remarks",type:"info"},(0,r.yg)("p",{parentName:"admonition"},"For example, configure ",(0,r.yg)("inlineCode",{parentName:"p"},'jdbc_driver_secure_path = "file:///path/to/jdbc_drivers;http://path/to/jdbc_drivers"'),":"),(0,r.yg)("p",{parentName:"admonition"},"Then only driver package paths starting with ",(0,r.yg)("inlineCode",{parentName:"p"},"file:///path/to/jdbc_drivers")," or ",(0,r.yg)("inlineCode",{parentName:"p"},"http://path/to/jdbc_drivers")," are allowed."))),(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("p",{parentName:"li"},"When creating a Catalog, you can specify the checksum of the driver package through the ",(0,r.yg)("inlineCode",{parentName:"p"},"checksum")," parameter. Doris will verify the driver package after loading the driver package. If the verification fails, the creation will be rejected.\nCatalog."))),(0,r.yg)("admonition",{title:"remarks",type:"info"},(0,r.yg)("p",{parentName:"admonition"},"The above verification will only be performed when the catalog is created, and the already created catalog will not be verified again.")),(0,r.yg)("h3",{id:"lowercase-name-synchronization"},"Lowercase name synchronization"),(0,r.yg)("p",null,"When ",(0,r.yg)("inlineCode",{parentName:"p"},"lower_case_meta_names")," is set to ",(0,r.yg)("inlineCode",{parentName:"p"},"true"),", Doris maintains the mapping of lowercase names to actual names in the remote system, enabling queries to use lowercase to query non-lowercase databases, tables and columns of external data sources."),(0,r.yg)("p",null,"Since FE has the ",(0,r.yg)("inlineCode",{parentName:"p"},"lower_case_table_names")," parameter, it will affect the table name case rules during query, so the rules are as follows"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},(0,r.yg)("p",{parentName:"li"},(0,r.yg)("strong",{parentName:"p"},"lower_case_meta_names = true")),(0,r.yg)("p",{parentName:"li"},"Library table column names will be converted to lowercase.")),(0,r.yg)("li",{parentName:"ul"},(0,r.yg)("p",{parentName:"li"},(0,r.yg)("strong",{parentName:"p"},"lower_case_meta_names = false")),(0,r.yg)("p",{parentName:"li"},"When the ",(0,r.yg)("inlineCode",{parentName:"p"},"lower_case_table_names")," parameter of FE is ",(0,r.yg)("inlineCode",{parentName:"p"},"0")," or ",(0,r.yg)("inlineCode",{parentName:"p"},"2"),", the library names, table names and column names will not be converted."),(0,r.yg)("p",{parentName:"li"},"When the ",(0,r.yg)("inlineCode",{parentName:"p"},"lower_case_table_names")," parameter of FE is ",(0,r.yg)("inlineCode",{parentName:"p"},"1"),", table names will be converted to lowercase, and library names and column names will not be converted."))),(0,r.yg)("p",null,"If the parameter configuration when creating the Catalog matches the lowercase conversion rule in the above rules, Doris will convert the corresponding name to lowercase and store it in Doris. You need to use\nDoris displays the lowercase name to query."),(0,r.yg)("p",null,"If the external data source has the same name but different case, such as DORIS and doris, Doris will query the Catalog due to ambiguity.\nAn error is reported. At this time, the ",(0,r.yg)("inlineCode",{parentName:"p"},"meta_names_mapping")," parameter needs to be configured to resolve the conflict."),(0,r.yg)("p",null,"The ",(0,r.yg)("inlineCode",{parentName:"p"},"meta_names_mapping")," parameter accepts a Json format string with the following format:"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-json"},'{\n "databases": [\n {\n "remoteDatabase": "DORIS",\n "mapping": "doris_1"\n },\n {\n "remoteDatabase": "doris",\n "mapping": "doris_2"\n }\n ],\n "tables": [\n {\n "remoteDatabase": "DORIS",\n "remoteTable": "DORIS",\n "mapping": "doris_1"\n },\n {\n "remoteDatabase": "DORIS",\n "remoteTable": "doris",\n "mapping": "doris_2"\n }\n ],\n "columns": [\n {\n "remoteDatabase": "DORIS",\n "remoteTable": "DORIS",\n "remoteColumn": "DORIS",\n "mapping": "doris_1"\n },\n {\n "remoteDatabase": "DORIS",\n "remoteTable": "DORIS",\n "remoteColumn": "doris",\n "mapping": "doris_2"\n }\n ]\n}\n')),(0,r.yg)("p",null,"When filling this configuration into the statement that creates the Catalog, there are double quotes in Json, so you need to escape the double quotes or directly use single quotes to wrap the Json string when filling in."),(0,r.yg)("h3",{id:"specify-synchronization-database"},"Specify synchronization database"),(0,r.yg)("p",null,(0,r.yg)("inlineCode",{parentName:"p"},"only_specified_database"),":\nWhether to synchronize only the Database of the data source specified in the JDBC URL. The default value is ",(0,r.yg)("inlineCode",{parentName:"p"},"false"),", which means synchronizing all Databases in the JDBC URL."),(0,r.yg)("p",null,(0,r.yg)("inlineCode",{parentName:"p"},"include_database_list"),":\nOnly effective when ",(0,r.yg)("inlineCode",{parentName:"p"},"only_specified_database=true"),", specify the Schema of PostgreSQL that needs to be synchronized, separated by ','. Schema names are case-sensitive."),(0,r.yg)("p",null,(0,r.yg)("inlineCode",{parentName:"p"},"exclude_database_list"),":\nOnly effective when ",(0,r.yg)("inlineCode",{parentName:"p"},"only_specified_database=true"),", specify the Schema of PostgreSQL that does not need to be synchronized, separated by ','. Schema names are case-sensitive."),(0,r.yg)("admonition",{title:"remarks",type:"info"},(0,r.yg)("ul",{parentName:"admonition"},(0,r.yg)("li",{parentName:"ul"},"The Database mentioned in the above three parameters refers to the Database level in Doris, not the Database level of external data sources. For specific mapping relationships, please refer to each data source document."),(0,r.yg)("li",{parentName:"ul"},"When ",(0,r.yg)("inlineCode",{parentName:"li"},"include_database_list")," and ",(0,r.yg)("inlineCode",{parentName:"li"},"exclude_database_list")," have overlapping database configurations, ",(0,r.yg)("inlineCode",{parentName:"li"},"exclude_database_list")," will take effect first."))),(0,r.yg)("h3",{id:"connection-pool-configuration"},"Connection pool configuration"),(0,r.yg)("p",null,"In Doris, each FE and BE node maintains a connection pool, which avoids frequently opening and closing separate data source connections. Each connection in the connection pool can be used to establish a connection with the data source and execute queries. When the task is completed, these connections are returned to the pool for reuse, which not only improves performance, but also reduces the overhead of establishing connections and helps prevent the data source's connection limit from being reached."),(0,r.yg)("p",null,"The connection pool size can be adjusted to better suit your workload. Typically, the minimum number of connections in a connection pool should be set to 1 to ensure that at least one connection is active when the keepalive mechanism is enabled. The maximum number of connections in the connection pool should be set to a reasonable value to avoid too many connections occupying resources."),(0,r.yg)("p",null,"At the same time, in order to avoid accumulating too many unused connection pool caches on BE, you can specify the time interval for clearing the cache by setting the ",(0,r.yg)("inlineCode",{parentName:"p"},"jdbc_connection_pool_cache_clear_time_sec")," parameter of BE. The default value is 28800 seconds (8 hours). After this interval, BE will forcefully clear all connection pool caches that have not been used for more than this time."),(0,r.yg)("h3",{id:"insert-transaction"},"Insert transaction"),(0,r.yg)("p",null,"Doris' data is written to the JDBC Catalog in a batch manner. If the import is interrupted midway, the previously written data may need to be rolled back. Therefore, JDBC Catalog supports transactions when data is written. Transaction support needs to be set by setting session variable: ",(0,r.yg)("inlineCode",{parentName:"p"},"enable_odbc_transcation"),"."),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},"set enable_odbc_transcation = true;\n")),(0,r.yg)("p",null,"Transactions ensure the atomicity of JDBC Catalog data writing, but will reduce the performance of data writing to a certain extent. You can consider turning on this function as appropriate."),(0,r.yg)("h2",{id:"example"},"Example"),(0,r.yg)("p",null,"Here, MySQL is used as an example to show how to create a MySQL Catalog and query the data in it."),(0,r.yg)("p",null,"Create a Catalog named ",(0,r.yg)("inlineCode",{parentName:"p"},"mysql"),":"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},'CREATE CATALOG mysql PROPERTIES (\n "type"="jdbc",\n "user"="root",\n "password"="secret",\n "jdbc_url" = "jdbc:mysql://example.net:3306",\n "driver_url" = "mysql-connector-j-8.3.0.jar",\n "driver_class" = "com.mysql.cj.jdbc.Driver"\n)\n')),(0,r.yg)("p",null,"View all databases in this catalog by running SHOW DATABASES:"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},"SHOW DATABASES FROM mysql;\n")),(0,r.yg)("p",null,"If you have a MySQL database named test, you can view the tables in that database by running SHOW TABLES:"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},"SHOW TABLES FROM mysql.test;\n")),(0,r.yg)("p",null,"Finally, you can access the table in the MySQL database:"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},"SELECT * FROM mysql.test.table;\n")),(0,r.yg)("h2",{id:"statement-transparent-transmission"},"Statement transparent transmission"),(0,r.yg)("p",null,"Doris supports direct execution of DDL, DML statements and query statements of JDBC data sources through transparent transmission."),(0,r.yg)("h3",{id:"transparent-transmission-of-ddl-and-dml"},"Transparent transmission of DDL and DML"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre"},'CALL EXECUTE_STMT("catalog_name", "raw_stmt_string");\n')),(0,r.yg)("p",null,"The ",(0,r.yg)("inlineCode",{parentName:"p"},"EXECUTE_STMT()")," procedure takes two parameters:"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"Catalog Name: Currently only JDBC type Catalog is supported."),(0,r.yg)("li",{parentName:"ul"},"Execution statements: Currently only DDL and DML statements are supported, and the syntax corresponding to the data source needs to be used directly.")),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre"},'CALL EXECUTE_STMT("jdbc_catalog", "insert into db1.tbl1 values(1,2), (3, 4)");\n\nCALL EXECUTE_STMT("jdbc_catalog", "delete from db1.tbl1 where k1 = 2");\n\nCALL EXECUTE_STMT("jdbc_catalog", "create table dbl1.tbl2 (k1 int)");\n')),(0,r.yg)("h3",{id:"transparent-query"},"Transparent query"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},'query(\n "catalog" = "catalog_name",\n "query" = "select * from db_name.table_name where condition"\n );\n')),(0,r.yg)("p",null,"The ",(0,r.yg)("inlineCode",{parentName:"p"},"query")," table function takes two parameters:"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},(0,r.yg)("inlineCode",{parentName:"li"},"catalog"),": Catalog name, which needs to be filled in according to the name of the Catalog."),(0,r.yg)("li",{parentName:"ul"},(0,r.yg)("inlineCode",{parentName:"li"},"query"),": The query statement that needs to be executed, and the syntax corresponding to the data source needs to be used directly.")),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},'select * from query("catalog" = "jdbc_catalog", "query" = "select * from db_name.table_name where condition");\n')),(0,r.yg)("h3",{id:"principles-and-limitations"},"Principles and Limitations"),(0,r.yg)("p",null,"Through the ",(0,r.yg)("inlineCode",{parentName:"p"},"CALL EXECUTE_STMT()")," command, Doris will directly send the SQL statement written by the user to the JDBC data source corresponding to the Catalog for execution. Therefore, this operation has the following limitations:"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"The SQL statement must be the syntax corresponding to the data source. Doris will not perform syntax and semantic checks."),(0,r.yg)("li",{parentName:"ul"},"It is recommended that the table name referenced in the SQL statement be a fully qualified name, that is, in the format of ",(0,r.yg)("inlineCode",{parentName:"li"},"db.tbl"),". If db is not specified, the db name specified in the JDBC URL of the JDBC Catalog is used."),(0,r.yg)("li",{parentName:"ul"},"SQL statements cannot reference library tables other than JDBC data sources, nor can they reference Doris library tables. However, you can reference library tables in the JDBC data source but not synchronized to the Doris JDBC Catalog."),(0,r.yg)("li",{parentName:"ul"},"When executing a DML statement, the number of rows inserted, updated, or deleted cannot be obtained, but only whether the command was successfully executed."),(0,r.yg)("li",{parentName:"ul"},"Only users with LOAD permissions on the Catalog can execute the ",(0,r.yg)("inlineCode",{parentName:"li"},"CALL EXECUTE_STMT()")," command."),(0,r.yg)("li",{parentName:"ul"},"Only users with SELECT permissions on Catalog can execute the ",(0,r.yg)("inlineCode",{parentName:"li"},"query()")," table function."),(0,r.yg)("li",{parentName:"ul"},"The supported data types of the data read by the ",(0,r.yg)("inlineCode",{parentName:"li"},"query")," table function are consistent with the data types supported by the queried catalog type.")))}m.isMDXComponent=!0}}]); |