Skip to main content

Self-service External Database Integration

1. Overview

1.1. Function Description

Domestic databases are showing rapid development momentum. With the support of national policies and the promotion of technological innovation, domestic databases have made significant progress in performance, security, and reliability, gradually winning the favor of enterprises and government departments. Therefore, Guandata has launched a self-service external database integration method, supporting the integration of various databases outside the scope of BI standard supported databases, including cloud vendors and domestic databases, to quickly support pre-sales customers' new database POC scenarios and service customers' domestic database integration scenarios. This improves the flexibility of data integration and shortens the waiting period for database integration.

1.2. Prerequisites

Before configuring external database integration, please collect the following information:

  • Driver Jar package and available Driver Class;

  • The dialect standard of the database to be integrated, whether the table list acquisition method is the same as the dialect database;

  • IP address and port number of the server where the database is located;

  • Name of the database;

  • Username and password of the database;

  • Must support connection via JDBC method.

2. Usage Guide

Operation Steps

  1. Enter the Administrator Settings > Resource Management > Driver and Connector Management page;

  2. Click the Driver Management > Add Driver button to start creating and configuring the driver;

  3. After the driver configuration is completed, click the Database Connector Management > New Database Connector button to start creating and configuring the database connector;

  4. After the driver and connection are established, enter the Data Preparation > Data Account > New Data Account page to create a data account;

  5. Go to the "Data Preparation" page for database integration, which can be integrated according to the standard database method.

2.1. New Configuration Driver

Function entry: Administrator Settings > Resource Management > Driver and Connector Management.

Step 1: Click the Driver Management > Add Driver button, edit the driver name, and click "Save" to complete the new driver creation.

1.png

Step 2: Click on the newly created driver, upload the driver Jar package and available Driver Class, and click "Apply" to complete the driver configuration.

2.png

2.2. New Configuration Database Connection

Click Database Connector Management > New Database Connector to start creating a new data connector, as shown in the figure below:

3.png

  • Parameter 1: Display ICON, set a custom data connection picture ICON for display in the data account and database dataset creation sections.

  • Parameter 2: Connector name, defined according to user habits, special characters are not supported.

  • Parameter 3: Default dialect support, currently open supported dialects include MySQL, Oracle, POSTGRES, and the verified supported databases are as follows:

DialectVerified Supported Databases
MySQLOceanBase(MySQL)、Azure DataBricks、云器LakeHouse、ByteHouse
Oracle人大金仓KES、Oceanbase(Oracle)、Aloudata Air Engine
POSTGRESGaussDB(DWS)、Aws Athena、CnosDB
  • Parameter 4: Get table list SQL, different databases may have inconsistencies with dialect databases in table list acquisition logic. This function supports users to customize the acquisition logic according to the corresponding database, specify the SQL for table list acquisition and the index of the table name column in the returned result set. Verified databases are referenced as follows:

Database

Driver Download Address

Table List Acquisition SQL

1

人大金仓 KES

官网kingbaseES下载中心

注：在官网 kingbaseES下载中心中选择 kingbaseES,根据数据库部署对应机器架构选择下载相应 JDBC 驱动

{"sql": "SHOW TABLES from ||database||", "tableNameIdx": 1}

2

Azure DataBircks

官方下载地址

{"sql": "SHOW TABLES", "tableNameIdx": 2} 会默认查询名为default schema下的表

3

{"sql": "SHOW TABLES IN ||schema||", "tableNamfeIdx": 2},需要在创建账号时候有额外表单项schema，查询指定 schema 下表

4

云器LakeHouse

官方下载地址

若 JDBC URL 中指定了schema(例如：schema=public)，则语句为:{"sql":"SHOW TABLES", "tableNameIdx": 2}

5

如果没有指定，则为：{"sql": "SHOW TABLES IN ||schema||", "tableNameIdx": 2}，并在创建账号时候有额外表单项schema

6

OceanBase(MySQL)

官方下载地址

注：根据 CPU 架构选择下载

{"sql": "SHOW TABLES", "tableNameIdx": 1}

7

OceanBase(Oracle)

官方下载地址

注：根据 CPU 架构选择下载

{"sql": "SHOW TABLES from ||database||", "tableNameIdx": 1}

8

Aloudata Air Engine

联系官方支持获取

{"sql": "SHOW TABLES IN ||schema||", "tableNameIdx": 1}

9

CnosDB

联系官方支持获取

{"sql": "SHOW TABLES", "tableNameIdx": 2, "withSchemaPrepend": true, "schemaOrDatabaseNameIdx": 1}

10

GaussDB-DWS

联系官方支持获取

{"sql": "SHOW TABLES from ||database||", "tableNameIdx": 1}

11

Aws-Athena

官方下载地址

{"sql": "select \"table_name\" from \"information_schema\".\"tables\" where (lower(cast(\"table_catalog\" as varchar)) = ||catalog|| and lower(cast(\"table_schema\" as varchar)) = ||database||) order by \"TABLE_NAME\"", "tableNameIdx": 1}

12

ByteHouse云数仓

官方下载地址

注：官方驱动 github 仓库有直接下载链接

{"sql": "SHOW TABLES from ||database||", "tableNameIdx": 1}

注意：ByteHouse 企业版不能使用云数仓接入，可以直接使用ClickHouse 连接接入。

Note: If ||schema|| parameter is specified in the table list acquisition SQL, the schema option will be dynamically displayed when creating the data account, and users will input it.

4.png|450

  • Parameter 5: Username password display switch, for example, in Azure DataBricks authentication method, username/password is included in the URL, which does not support traditional parameter passing method, and can be controlled whether to display through the switch.

5.png|450

2.3. Create Data Account

After the driver and connection are established, on the Data Preparation > Data Account > New Data Account page, you can select the corresponding connection and driver mapping relationship to complete the data account creation.

For custom accounts, considering the parameter differences under non-JDBC URL methods, currently only JDBC URL connection method is supported.

6.png

2.4. Integrate External Database

Go to the "Data Preparation" page for database integration, which can be integrated according to the standard database method. For details, please refer to [Standard Database Connection Guide](1-Standard Database Connection Guide.md).