A demo to consume MySQL database in Datasphere with Generic JDBC connection

[Cyane]

This Blog I would like to give an introduction of Datasphere "Generic JDBC Connection" to load remote tables from MySQL Database.

Preparation:
        3522872 - How to enable MySQL DB connection with SAP Datasphere - SAP for Me
        Verify if the DB you are attempting to connect to is supported by the CamelJdbcAdapter (DPAgent).
        Refer to SAP HANA Smart Data Integration Product Availability Matrix (PAM) 

Step 1. (MySQL Side) Download MySQL Database and MySQL JDBC Driver.

1. MySQL Database: MySQL :: Download MySQL Installer
   (1) Set password for user root
   (2) Prepare test data
   
2. MySQL JDBC: MySQL :: Download Connector/J
   (1) Download JDBC Driver: Select Operating System -> Platform Independence
   (2) Place the JDBC Driver "mysql-connector-j-<jdbc version>.jar" file in the <DPAgent_root>/camel/lib directory
   

          Reference: Connecting to MySQL with the Camel JDBC Adapter | SAP Help Portal
   
   

Step 2. (Datasphere Side) Create Generic JDBC Connection in Datasphere

1. Choose Generic JDBC type
   
2. Configure Connection Properities
   (1) JDBC Driver Class: com.mysql.cj.jdbc.Driver (for my MySQL version)
   (2) JDBC URL: jdbc:mysql://<hostname>:<port>/<database_name>?sessionVariables=sql_mode=ANSI_QUOTES
           <hostname>: for localhost is 127.0.0.1
           <port>: default is 3306
           eg. my JDBC URL is: jdbc:mysql://127.0.0.1:3306/cyane_MySQL?sessionVariables=sql_mode=ANSI_QUOTES
   Reference: 3076151 - Preview on Mysql or MariaBD data not working in Datasphere through CamelJDBCAdapter - SAP ...
   (3) User Name: MySQL username (default is root)
   (4) Password: MySQL password you set in Step 1.
   (5) Data Provisioning Agent: Choose an available DPAgent

Step 3. (Datasphere Side) Validate Connection and Import Remote Table

1. Validate this Generic JDBC Connection
   Attention: Data Flows and Replication Flows are not supported, only Remote Tables are enabled.
   
   
2.  Data Builder -> Import Remote Table -> Choose Connection -> Choose Table -> Import and Deploy