JDBC Engine

This article mainly introduces the installation, use and configuration of the JDBC engine plugin in Linkis.

If you want to use JDBC engine on your server, you need to prepare JDBC connection information, such as MySQL database connection address, username and password, etc.

  1. mysql -uroot -P 3306 -h 127.0.0.1 -p 123456

The output of the following information means that the JDBC connection information is available

  1. mysql: [Warning] Using a password on the command line interface can be insecure.
  2. Welcome to the MySQL monitor.  Commands end with ; or \g.
  3. Your MySQL connection id is 9
  4. Server version: 5.7.39 MySQL Community Server (GPL)
  6. Copyright (c) 2000, 2022, Oracle and/or its affiliates.
  8. Oracle is a registered trademark of Oracle Corporation and/or its
  9. affiliates. Other names may be trademarks of their respective
  10. owners.
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  14. mysql>

Method 1: Download the engine plug-in package directly

Linkis engine plugin download

Method 2: Compile the engine plug-in separately (requires a maven environment)

  1. # compile
  2. cd ${linkis_code_dir}/linkis-engineconn-plugins/jdbc/
  3. mvn clean install
  4. # The compiled engine plug-in package is located in the following directory
  5. ${linkis_code_dir}/linkis-engineconn-plugins/jdbc/target/out/

EngineConnPlugin engine plugin installation

Upload the engine plug-in package in 2.1 to the engine directory of the server

  1. ${LINKIS_HOME}/lib/linkis-engineconn-plugins

The directory structure after uploading is as follows

  1. linkis-engineconn-plugins/
  2. ├── jdbc
  3. │   ├── dist
  4. │ │ └── 4
  5. │   │       ├── conf
  6. │ │ └── lib
  7. │   └── plugin
  8. │ └── 4

Refresh the engine by restarting the linkis-cg-linkismanager service

  1. cd ${LINKIS_HOME}/sbin
  2. sh linkis-daemon.sh restart cg-linkismanager

You can check whether the last_update_time of the linkis_engine_conn_plugin_bml_resources table in the database is the time to trigger the refresh.

  1. #Login to the `linkis` database
  2. select * from linkis_cg_engine_conn_plugin_bml_resources;
  1. sh ./bin/linkis-cli -engineType jdbc-4 \
  2. -codeType jdbc -code "show tables" \
  3. -submitUser hadoop -proxyUser hadoop \
  4. -runtimeMap wds.linkis.jdbc.connect.url=jdbc:mysql://127.0.0.1:3306/linkis_db \
  5. -runtimeMap wds.linkis.jdbc.driver=com.mysql.jdbc.Driver \
  6. -runtimeMap wds.linkis.jdbc.username=test \
  7. -runtimeMap wds.linkis.jdbc.password=123456

More Linkis-Cli command parameter reference: Linkis-Cli usage

Linkis provides SDK of Java and Scala to submit tasks to Linkis server. For details, please refer to JAVA SDK Manual. For the JDBC task, you only need to modify EngineConnType and CodeType parameters in Demo:

  1. Map<String, Object> labels = new HashMap<String, Object>();
  2. labels.put(LabelKeyConstant.ENGINE_TYPE_KEY, "jdbc-4"); // required engineType Label
  3. labels.put(LabelKeyConstant.USER_CREATOR_TYPE_KEY, "hadoop-IDE");// required execute user and creator
  4. labels.put(LabelKeyConstant.CODE_TYPE_KEY, "jdbc"); // required codeType

Starting from Linkis 1.2.0, it provides support for multiple data sources in the JDBC engine. First, we can manage different data sources in the console. Address: Log in to the management console—>Data source management—>Add data source

JDBC Engine - 图1

Figure 3-3 Data source management

JDBC Engine - 图2

Figure 3-4 Data source connection test

After the data source is added, you can use the multi-data source switching function of the JDBC engine. There are two ways:

  1. Specify the data source name parameter through the interface parameter Example parameters:
  1. {
  2.   "executionContent": {
  3.     "code": "show databases",
  4.     "runType": "jdbc"
  5.   },
  6.   "params": {
  7.     "variable": {},
  8.     "configuration": {
  9.       "startup": {},
  10.       "runtime": {
  11.         "wds.linkis.engine.runtime.datasource": "test_mysql"
  12.       }
  13.     }
  14.   },
  15.   "source": {
  16.     "scriptPath": ""
  17.   },
  18.   "labels": {
  19.     "engineType": "jdbc-4",
  20.     "userCreator": "hadoop-IDE"
  21.   }
  22. }

Parameter: wds.linkis.engine.runtime.datasource is a configuration with a fixed name, do not modify the name definition at will

  1. Use the Scripts code of DSS to submit the entry drop-down to filter the data sources that need to be submitted, as shown in the figure below: JDBC Engine - 图3 Currently dss-1.1.0 does not support drop-down selection of data source name, PR is under development, you can wait for the subsequent release or pay attention to related PR: (https://github.com/WeBankFinTech/DataSphereStudio/issues/940)

Function description of multiple data sources:

1) In the previous version, the JDBC engine’s support for data sources was not perfect, especially when used with Scripts, the JDBC script type can only bind a set of JDBC engine parameters of the console. When we need to switch multiple data sources, we can only modify the connection parameters of the JDBC engine, which is troublesome.

2) To cooperate with data source management, we introduce the multi-data source switching function of JDBC engine, which can realize that only setting the data source name can submit jobs to different JDBC services, and ordinary users do not need to It maintains the connection information of the data source, avoids the complicated configuration, and also meets the security requirements of the data source connection password and other configurations.

3) The data sources set in the multi-data source management can be loaded by the JDBC engine only after they have been released and have not expired, otherwise different types of exception prompts will be fed back to the user.

4) The loading priority of JDBC engine parameters is: task submission parameters > data source selection parameters > console JDBC engine parameters

ConfigurationDefaultRequiredDescription
wds.linkis.jdbc.connect.urljdbc:mysql://127.0.0.1:10000yesjdbc connection address
wds.linkis.jdbc.drivernoyesjdbc connection driver
wds.linkis.jdbc.usernamenoyesdatabase connection username
wds.linknis.jdbc.passwordnoyesdatabase link password
wds.linkis.jdbc.connect.max10NoThe maximum number of jdbc engine connections
wds.linkis.jdbc.versionjdbc4nojdbc version

If the default parameters are not satisfied, there are the following ways to configure some basic parameters

jdbc

Note: After modifying the configuration under the IDE tag, you need to specify -creator IDE to take effect (other tags are similar), such as:

  1. sh ./bin/linkis-cli -creator IDE \
  2. -engineType jdbc-4 -codeType jdbc \
  3. -code "show tables"  \
  4. -submitUser hadoop -proxyUser hadoop

Submit the task interface, configure it through the parameter params.configuration.runtime

  1. Example of http request parameters
  2. {
  3.     "executionContent": {"code": "show databases;", "runType":  "jdbc"},
  4.     "params": {
  5.                     "variable": {},
  6.                     "configuration": {
  7.                             "runtime": {
  8.                                     "wds.linkis.jdbc.connect.url":"jdbc:mysql://127.0.0.1:3306/test",  
  9.                                     "wds.linkis.jdbc.driver":"com.mysql.jdbc.Driver",
  10.                                     "wds.linkis.jdbc.username":"test",
  11.                                     "wds.linkis.jdbc.password":"test23"
  12.                                 }
  13.                             }
  14.                     },
  15.     "labels": {
  16.         "engineType": "jdbc-4",
  17.         "userCreator": "hadoop-IDE"
  18.     }
  19. }

Linkis is managed through engine tags, and the data table information involved is as follows.

  1. linkis_ps_configuration_config_key: key and default values ​​of configuration parameters inserted into the engine
  2. linkis_cg_manager_label: Insert engine label such as: jdbc-4
  3. linkis_ps_configuration_category: The directory association relationship of the insertion engine
  4. linkis_ps_configuration_config_value: The configuration that the insertion engine needs to display
  5. linkis_ps_configuration_key_engine_relation: The relationship between the configuration item and the engine

The initial data related to the engine in the table is as follows

  1. -- set variable
  2. SET @JDBC_LABEL="jdbc-4";
  3. SET @JDBC_ALL=CONCAT('*-*,',@JDBC_LABEL);
  4. SET @JDBC_IDE=CONCAT('*-IDE,',@JDBC_LABEL);
  6. -- engine label
  7. insert into `linkis_cg_manager_label` (`label_key`, `label_value`, `label_feature`, `label_value_size`, `update_time`, `create_time`) VALUES ('combined_userCreator_engineType', @JDBC_ALL, 'OPTIONAL', 2, now(), now());
  8. insert into `linkis_cg_manager_label` (`label_key`, `label_value`, `label_feature`, `label_value_size`, `update_time`, `create_time`) VALUES ('combined_userCreator_engineType', @JDBC_IDE, 'OPTIONAL', 2, now(), now());
  10. select @label_id := id from linkis_cg_manager_label where `label_value` = @JDBC_IDE;
  11. insert into linkis_ps_configuration_category (`label_id`, `level`) VALUES (@label_id, 2);
  13. -- configuration key
  14. insert into `linkis_ps_configuration_config_key` (`key`, `description`, `name`, `default_value`, `validate_type`, `validate_range`, `is_hidden`, `is_advanced`, `level`, `treeName`, `engine_conn_type`) VALUES ('wds.linkis.jdbc.connect.url', 'For example: jdbc:mysql://127.0.0.1:10000', 'jdbc connection address', 'jdbc:mysql://127.0.0.1:10000', 'Regex', '^\\s*jdbc:\\w+://([^:]+)(:\\d+)(/[^\\?]+)?(\\?\\S* )?$', '0', '0', '1', 'Datasource configuration', 'jdbc');
  15. insert into `linkis_ps_configuration_config_key` (`key`, `description`, `name`, `default_value`, `validate_type`, `validate_range`, `is_hidden`, `is_advanced`, `level`, `treeName`, `engine_conn_type`) VALUES ('wds.linkis.jdbc.driver', 'For example: com.mysql.jdbc.Driver', 'jdbc connection driver', '', 'None', '', '0', '0', '1 ', 'User Configuration', 'jdbc');
  16. insert into `linkis_ps_configuration_config_key` (`key`, `description`, `name`, `default_value`, `validate_type`, `validate_range`, `is_hidden`, `is_advanced`, `level`, `treeName`, `engine_conn_type`) VALUES ('wds.linkis.jdbc.version', 'Value range: jdbc3,jdbc4', 'jdbc version','jdbc4', 'OFT', '[\"jdbc3\",\"jdbc4\"]' , '0', '0', '1', 'userconfig', 'jdbc');
  17. insert into `linkis_ps_configuration_config_key` (`key`, `description`, `name`, `default_value`, `validate_type`, `validate_range`, `is_hidden`, `is_advanced`, `level`, `treeName`, `engine_conn_type`) VALUES ('wds.linkis.jdbc.username', 'username', 'Database connection username', '', 'None', '', '0', '0', '1', 'User configuration', 'jdbc');
  18. insert into `linkis_ps_configuration_config_key` (`key`, `description`, `name`, `default_value`, `validate_type`, `validate_range`, `is_hidden`, `is_advanced`, `level`, `treeName`, `engine_conn_type`) VALUES ('wds.linkis.jdbc.password', 'password', 'Database connection password', '', 'None', '', '0', '0', '1', 'User configuration', ' jdbc');
  19. insert into `linkis_ps_configuration_config_key` (`key`, `description`, `name`, `default_value`, `validate_type`, `validate_range`, `is_hidden`, `is_advanced`, `level`, `treeName`, `engine_conn_type`) VALUES ('wds.linkis.jdbc.connect.max', 'range: 1-20, unit: piece', 'jdbc engine maximum number of connections', '10', 'NumInterval', '[1,20]', '0', '0', '1', 'Datasource configuration', 'jdbc');
  21. -- key engine relation
  22. insert into `linkis_ps_configuration_key_engine_relation` (`config_key_id`, `engine_type_label_id`)
  23. (select config.id as `config_key_id`, label.id AS `engine_type_label_id` FROM linkis_ps_configuration_config_key config
  24. INNER JOIN linkis_cg_manager_label label ON config.engine_conn_type = 'jdbc' and label_value = @JDBC_ALL);
  26. insert into `linkis_ps_configuration_config_value` (`config_key_id`, `config_value`, `config_label_id`)
  27. (select `relation`.`config_key_id` AS `config_key_id`, '' AS `config_value`, `relation`.`engine_type_label_id` AS `config_label_id` FROM linkis_ps_configuration_key_engine_relation relation
  28. INNER JOIN linkis_cg_manager_label label ON relation.engine_type_label_id = label.id AND label.label_value = @JDBC_ALL);