tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Tencent Cloud TCHouse-D
    Documentation
    Download PDF

    JDBC Catalog

    Last updated: 2024-06-27 11:16:03
    Download PDF
      Note:
      This feature is applicable to TCHouse-D 1.2 and later versions.
      JDBC Catalog connects to other data sources through the standard JDBC protocol. After the connection is made, Doris will automatically synchronize the Metadata of the Database and Table under the data source for quick access to these external data.

      Creating Catalog

      MySQL

      CREATE CATALOG jdbc_mysql PROPERTIES (    
      "type"="jdbc",    
      "user"="root",    
      "password"="123456",    
      "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",    
      "driver_url" = "mysql-connector-java-5.1.47.jar",    
      "driver_class" = "com.mysql.jdbc.Driver");

      PostgreSQL

      CREATE CATALOG jdbc_postgresql PROPERTIES (    
      "type"="jdbc",    
      "user"="root",    
      "password"="123456",    
      "jdbc_url" = "jdbc:postgresql://127.0.0.1:5449/demo",    
      "driver_url" = "postgresql-42.5.1.jar",    
      "driver_class" = "org.postgresql.Driver");
      When mapping PostgreSQL, one Database in Doris corresponds to one Schema under a specified Catalog in PostgreSQL (such as the schemas under "demo" in the jdbc_url parameter in the example). And a Table in a Doris Database corresponds to Tables under a Schema in PostgreSQL. The mapping relationship is as follows:
      Doris
      PostgreSQL
      Catalog
      Database
      Database
      Schema
      Table
      Table
      Doris uses SQL statements to get all the schemas that the PG user can access and maps them as Doris databases.
      select nspname from pg_namespace where has_schema_privilege('<UserName>', nspname, 'USAGE');

      Oracle

      CREATE CATALOG jdbc_oracle PROPERTIES (    
      "type"="jdbc",    
      "user"="root",    
      "password"="123456",    
      "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",    
      "driver_url" = "ojdbc6.jar",    
      "driver_class" = "oracle.jdbc.driver.OracleDriver");
      When mapping Oracle, one Database in Doris corresponds to one User in Oracle. A Table in the Doris Database corresponds to a Table to which this User has permission to access in Oracle. The mapping relationship is as follows:
      Doris
      Oracle
      Catalog
      Database
      Database
      User
      Table
      Table

      Clickhouse

      CREATE CATALOG jdbc_clickhouse PROPERTIES (    
      "type"="jdbc",    
      "user"="root",    
      "password"="123456",    
      "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",    
      "driver_url" = "clickhouse-jdbc-0.3.2-patch11-all.jar",    
      "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver");

      SQLServer

      CREATE CATALOG sqlserver_catalog PROPERTIES (    
      "type"="jdbc",    
      "user"="SA",    
      "password"="Doris123456",    
      "jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",    "driver_url" = "mssql-jdbc-11.2.3.jre8.jar",    
      "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver");
      When mapping SQLServer, one Database in Doris corresponds to one Schema under a specified Database (such as "doris_test" in the jdbc_url parameter in the example) in SQLServer. A Table in the Doris Database corresponds to Tables under a Schema in SQLServer. The mapping relationship is as follows:
      Doris
      SQLServer
      Catalog
      Database
      Database
      Schema
      Table
      Table

      Doris

      JDBC Catalog also supports connecting to another Doris database:
      CREATE CATALOG doris_catalog PROPERTIES (    
      "type"="jdbc",   
      "user"="root",    
      "password"="123456",    
      "jdbc_url" = "jdbc:mysql://127.0.0.1:9030?useSSL=false",    
      "driver_url" = "mysql-connector-java-5.1.47.jar",    
      "driver_class" = "com.mysql.jdbc.Driver");
      Currently, JDBC Catalog only supports connection to a Doris database with the JDBC jar package of version 5.x. If the 8.x JDBC jar package is used, column type mismatch issues may occur.

      Parameter Description

      Parameter
      Required
      Default Value
      Description
      User
      Yes
      -
      Username of the corresponding database
      Password
      Yes
      -
      Password for the corresponding database
      jdbc_url
      Yes
      -
      JDBC connection string
      driver_url
      Yes
      -
      JDBC Driver Jar package name*
      driver_class
      Yes
      -
      JDBC Driver Class name
      only_specified_database
      No
      "false"
      Specify whether to only synchronize the specified database
      lower_case_table_names
      No
      "false"
      Whether to synchronize the table name of the JDBC external data source in lowercase
      include_database_list
      No
      ""
      When only_specified_database=true, specify to synchronize multiple databases, separated by ','. The db name is case-sensitive.
      exclude_database_list
      No
      ""
      When only_specified_database=true, specify multiple databases that don't need to be synchronized, separated by ','. The db name is case-sensitive.

      Driver Package Path

      Driver_urlcan be specified in the following three ways:
      1. File name. For example, mysql-connector-java-5.1.47.jar. The Jar package must be stored in the jdbc_drivers/ directory of the FE and BE deployment directory in advance. The system will automatically find the package in this directory. The location of this directory can also be modified by thejdbc_drivers_dir configuration in fe.conf and be.conf.
      2. Local absolute path. Like file:///path/to/mysql-connector-java-5.1.47.jar. The Jar package must be stored in the path specified by all the FE/BE nodes in advance.
      3. Http address. For example:https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-5.1.47.jar. The system will download the driver file from this http address. Only unauthenticated http services are supported.

      Specifying the Synchronized Database

      only_specified_database: When connecting to jdbc, you can specify which database/schema to link to, such as: database can be specified in jdbc_url of MySQL, and currentSchema can be specified in jdbc_url of pg.
      include_database_list: when only_specified_database=true, specify the databases to be synchronized, separated by ','. The default is '', that is, to synchronize all the databases without filtering. The db name is case-sensitive.
      exclude_database_list: When only_specified_database=true, specify multiple databases that don't need to be synchronized, separated by ','. The default is '', that is, to sync all the databases without filtering. The db name is case-sensitive.
      When the databases configurations ofinclude_database_list and exclude_database_list overlap,exclude_database_listwill take priority to take effect first.
      If you connect to the Oracle database using this parameter, you must use the jar package later than the ojdbc8.jar versions.

      Data Query

      select * from mysql_catalog.mysql_database.mysql_table where t1 > 1000 and t2 ='term';
      Since there may be situations where the internal keywords of the database are used as field names, in order to query correctly under this situation, escape characters will be automatically added to field names and table names in SQL statements according to the standards of each database. For example MYSQL(``),PostgreSQL(""), SQLServer([]), and ORACLE(""), therefore, this may cause case sensitivity of field names. You can view the query statements issued to each databases after escaping through explain sql.

      Column Type Mapping

      MySQL

      MYSQL Type
      Doris Type
      Comment
      BOOLEAN
      BOOLEAN
      -
      TINYINT
      TINYINT
      -
      SMALLINT
      SMALLINT
      -
      MEDIUMINT
      INT
      -
      INT
      INT
      -
      BIGINT
      BIGINT
      -
      UNSIGNED TINYINT
      SMALLINT
      Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
      UNSIGNED MEDIUMINT
      INT
      Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
      UNSIGNED INT
      BIGINT
      Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
      UNSIGNED BIGINT
      LARGEINT
      -
      FLOAT
      FLOAT
      -
      DOUBLE
      DOUBLE
      -
      DECIMAL
      DECIMAL
      -
      DATE
      DATE
      -
      TIMESTAMP
      DATETIME
      -
      DATETIME
      DATETIME
      -
      YEAR
      SMALLINT
      -
      TIME
      STRING
      -
      CHAR
      CHAR
      -
      VARCHAR
      VARCHAR
      -
      TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,TINY blob , blob ,MEDIUM blob ,LONG blob ,TINYSTRING,STRING,MEDIUMSTRING,LONGSTRING,BINARY,VARBINARY,JSON,SET,BIT
      STRING
      -
      Other
      UNSUPPORTED
      -

      PostgreSQL

      POSTGRESQL Type
      Doris Type
      Comment
      boolean
      BOOLEAN
      -
      smallint/int2
      SMALLINT
      -
      integer/int4
      INT
      -
      bigint/int8
      BIGINT
      -
      decimal/numeric
      DECIMAL
      -
      real/float4
      FLOAT
      -
      double precision
      DOUBLE
      -
      smallserial
      SMALLINT
      -
      serial
      INT
      -
      bigserial
      BIGINT
      -
      char
      CHAR
      -
      varchar/text
      STRING
      -
      timestamp
      DATETIME
      -
      date
      DATE
      -
      time
      STRING
      -
      interval
      STRING
      -
      point/line/lseg/box/path/polygon/circle
      STRING
      -
      cidr/inet/macaddr
      STRING
      -
      bit/bit(n)/bit varying(n)
      STRING
      The BIT type is mapped to the STRING type of Doris, and the read data is true/false instead of 1/0.
      uuid/JSONB
      STRING
      -
      Other
      UNSUPPORTED
      -

      Oracle

      ORACLE Type
      Doris Type
      Comment
      number(p) / number(p,0)
      TINYINT/SMALLINT/INT/BIGINT/LARGEINT
      Doris selects the corresponding type based on the size of p: p < 3 -> TINYINT; p < 5 -> SMALLINT; p < 10 -> INT; p < 19 -> BIGINT; p > 19 -> LARGEINT
      number(p,s), [ if(s>0 && p>s) ]
      DECIMAL(p,s)
      -
      number(p,s), [ if(s>0 && p < s) ]
      DECIMAL(s,s)
      -
      number(p,s), [ if(s<0) ]
      TINYINT/SMALLINT/INT/BIGINT/LARGEINT
      When s<0, Doris sets p as p+|s| and maps the same as number(p) / number(p,0).
      number
      -
      Doris currently does not support Oracle type with unspecified p and s.
      decimal
      DECIMAL
      -
      float/real
      DOUBLE
      -
      DATE
      DATETIME
      -
      TIMESTAMP
      DATETIME
      -
      CHAR/NCHAR
      STRING
      -
      VARCHAR2/NVARCHAR2
      STRING
      -
      LONG/ RAW/ LONG RAW/ INTERVAL
      STRING
      -
      Other
      UNSUPPORTED
      -

      SQLServer

      SQLServer Type
      Doris Type
      Comment
      bit
      BOOLEAN
      -
      tinyint
      SMALLINT
      The tinyint of SQLServer is unsigned, so it is mapped to SMALLINT of Doris.
      smallint
      SMALLINT
      -
      int
      INT
      -
      bigint
      BIGINT
      -
      real
      FLOAT
      -
      float
      DOUBLE
      -
      money
      DECIMAL(19,4)
      -
      smallmoney
      DECIMAL(10,4)
      -
      decimal/numeric
      DECIMAL
      -
      date
      DATE
      -
      datetime/datetime2/smalldatetime
      DATETIMEV2
      -
      char/varchar/text/nchar/nvarchar/ntext
      STRING
      -
      binary/varbinary
      STRING
      -
      time/datetimeoffset
      STRING
      -
      Other
      UNSUPPORTED
      -

      Clickhouse

      ClickHouse Type
      Doris Type
      Comment
      Bool
      BOOLEAN
      -
      String
      STRING
      -
      Date/Date32
      DATEV2
      The Jdbc Catalog uses the DATEV2 type to connect to ClickHouse by default.
      DateTime/DateTime64
      DATETIMEV2
      The Jdbc Catalog uses the DATETIMEV2 type to connect to ClickHouse by default.
      Float32
      FLOAT
      -
      Float64
      DOUBLE
      -
      Int8
      TINYINT
      -
      Int16/UInt8
      SMALLINT
      Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
      Int32/UInt16
      INT
      Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
      Int64/Uint32
      BIGINT
      Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
      Int128/UInt64
      LARGEINT
      Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
      Int256/UInt128/UInt256
      STRING
      Doris does not have data type of this magnitude, it is processed with STRING.
      DECIMAL
      DECIMAL/DECIMALV3/STRING
      The type is decided based on the (precision, scale) of Doris DECIMAL field and the enable_decimal_conversion switch.
      Enum/IPv4/IPv6/UUID
      STRING
      When displaying, IPv4 and IPv6 will display an extra "/" at the very beginning of the data. You need to handle it using the split_part function.
      Array
      ARRAY
      The adaptation logic of Array internal types refers to the above types. Nested types are not supported.
      Other
      UNSUPPORTED
      -
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy