Article

Unlock your MariaDB/MySQL data with Red Hat JBoss Data Virtualization

 

December 7, 2016
Developer Tools
Madou Coulibaly
Data Consumers

Welcome back to a new episode of the series: "Unlock your [….] data with Red Hat JBoss Data Virtualization." Through this blog series, we will look at how to connect Red Hat JBoss Data Virtualization (JDV) to different and heterogenous data sources.

JDV is a lean, virtual data integration solution that unlocks trapped data and delivers it as easily consumable, unified, and actionable information. It makes data spread across physically diverse systems — such as multiple databases, XML files, and Hadoop systems — appear as a set of tables in a local database. By providing following functionality, JDV enables agile data use:

  1. Connect: Access data from multiple, heterogeneous data sources.
  2. Compose: Easily combine and transform data into reusable, business-friendly virtual data models and views.
  3. Consume: Make unified data easily consumable through open standards interfaces.

It hides complexities, like the true locations of data or the mechanisms required to access or merge it. Data becomes easier for developers and users to work with.

This post will guide you step-by-step how to connect JDV to a MariaDB/MySQL database using Teiid Designer. We will connect to a MariaDB 10.1 server using MySQL Connector/J 5.1, a JDBC driver for communicating with MariaDB/MySQL servers. Indeed, you can follow this same tutorial with a MySQL instance.

Prerequisites

Install & Configure the MySQL JDBC Driver on JDV

  1. Create a JBoss module directory for the MySQL JDBC driver
    $ mkdir -p $JDV_HOME/modules/system/layers/dv/com/mysql/main
  2. Copy the MySQL JDBC jar file into the new JBoss module directory created
    $ cp $DRIVER_HOME/mysql-connector-java-5.1.40-bin.jar $JDV_HOME/modules/system/layers/dv/com/mysql/main
  3. Create a module.xml file with content seen below, in the new JBoss modules directory created
    <?xml version="1.0"?>
<module xmlns="urn:jboss:module:1.1" name="com.mysql">
    <resources>
        <resource-root path="mysql-connector-java-5.1.40-bin.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>
  4. Start your local JDV 6.3 environment
    $ $JDV_HOME/bin/standalone.sh
  5. Add MySQL JDBC driver
    $ $JDV_HOME/bin/jboss-cli.sh --connect
[standalone@localhost:9999 /] /subsystem=datasources/jdbc-driver=mysql:add(driver-module-name=com.mysql, driver-name=mysql, driver-xa-datasource-class-name=com.mysql.jdbc.jdbc2.optional.MysqlXADataSource)
  6. Add MySQL Datasource and enable it
    [standalone@localhost:9999 /] data-source add --name=UnlockData_MySQL_DS --jndi-name=java:/UnlockData_MySQL_DS --connection-url=jdbc:mysql://localhost:3306/unlockdata --driver-name=mysql --user-name=mysql_dev --password=mysql_dev
[standalone@localhost:9999 /] data-source enable --name=UnlockData_MySQL_DS

What about automating configuration ? (Optional)

A question you might ask: Can we automate the above configuration steps?
The answer is yes. we can, with Ansible, by Red Hat.

Ansible is a radically simple IT automation engine that automates cloud provisioning, configuration management, application deployment, intra-service orchestration, and many other IT needs. It uses no agents and no additional custom security infrastructure, so it’s easy to deploy – and most importantly, it uses a very simple language (YAML, in the form of Ansible Playbooks) that allow you to describe your automation jobs in a way that approaches plain English.

For your convenience most of the steps are automated in an ansible playbook called mariadb_mysql on github and to run you only need to run one command and you should see similar output as shown below:

$ cd unlock-your-data/mariadb_mysql
$ ansible-playbook local.yml
PLAY [Configure local JBoss Data Virtualization with MySQL JDBC driver] ********

TASK [setup] *******************************************************************
ok: [localhost]

TASK [Create JBoss module directory for the MySQL JDBC driver] *****************
changed: [localhost]

TASK [Download and Unarchive the MySQL JDBC zip file] **************************
changed: [localhost]

TASK [Copy the jar to the main directory] **************************************
changed: [localhost]

TASK [Remove MySQL JDBC unarchived directory] **********************************
changed: [localhost]

TASK [Copy module.xml into JBoss modules directory] ****************************
changed: [localhost]

TASK [Execute Management CLI file(s)] ******************************************
changed: [localhost] => (item=add_datasource.cli)
changed: [localhost] => (item=add_driver.cli)

PLAY RECAP *********************************************************************
localhost                  : ok=7    changed=6    unreachable=0    failed=0

Note: See https://github.com/cvanball/unlock-your-data/tree/master/mariadb_mysql for more information.

Start Your Development Environment

    1. Start your local JDV 6.3 environment
      $ $JDV_HOME/bin/standalone.sh
    2. Start your local JBDS environment

      Start JBDS 9.1.0 and open the Teiid Designer Perspective as shown below
      capture-decran-2016-11-30-a-10-04-30

      Note: Use the following menu options "Window" > "Perspective" > "Open Perspective" > "Other..." > "Teiid Designer" to set JBDS in Teiid Designer perspective

Create Your Teiid Project

    1. Create Teiid Model Project called "MySQLSample"

      Create a new Teiid Model project using right-click "New" > "Teiid Model Project" in the Model Explorer window as shown below.
      capture-decran-2016-11-30-a-10-06-17

    2. Import Metadata using JDBC importer

      We are now going to use the MySQL JDBC driver directly and import metadata directly using the JDBC importer. Right-click the project "MySQLSample" and select Import and select "JDBC Database >> Source Model" as shown above and click "Next >".
      capture-decran-2016-11-30-a-10-13-54

Create Your Connection Profile

  1. Create the connection profile

    On the first page of the wizard, click "New..." to create a new Connection Profile. Before we can proceed we need to setup a new connection profile to be able to connect to the MariaDB server using the JDBC jar previously downloaded.

    Select "MySQL" for the Connection Profile Type and name the Connection Profile “UnlockData_MySQL_DS”. Click "Next >".

  2. Add and configure the JDBC driver

    Click the “Add Driver Definition” button.

    In the "Name/Type" tab, select the right driver template.

    In the "JAR List" tab, click "Clear All" and click "Add JAR/Zip..." to add the JDBC driver jar file, $DRIVER_HOME/mysql-connector-java-bin.jar.

    Click "Ok" and now we are ready to connect to the MariaDB server by providing the correct connection details.
    The MySQL JDBC URL is a string with the following syntax:

    jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]] [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

    where

    [host1][:port1][,[host2][:port2]]...

    is a server instance or a comma separated list of server instances to connect to. The default port for MariaDB/MySQL server is 3306.

    /[database]

    is the name of the database.

    [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

    is a semicolon separated list of name=value pairs of properties (e.g., user=foo;password=bar).

    Here we will use the following connection details:

    Username = mysql_dev
Password = mysql_dev
Database = unlockdata
URL = jdbc:mysql://localhost:3306/unlockdata

    Check “Save password” box and Click "Test Connection" to validate if the connection to the server can ping successfully.

    Since the connection can ping successfully we are ready to select tables from the MariaDB/MySQL server and create a source model out of it. Click "OK" then "Finish".

Create Your Source Model

  1. Import the metadata from the database

    Click "Next >" twice to select database objects.

    Select all tables in the 'unlockdata' database and click "Next >"

    Specify the target folder for the source models (here "MySQLServer/DataSourceLayer"). Make sure that the JNDI name corresponds to the one we created in the JDV environment (Hint: UnlockData_MySQL_DS) and that Auto-create Data Source is not selected. Click “Finish” to create the source models.
    capture-decran-2016-11-30-a-11-59-09

  2. Preview the data through JDV

    Select any model and click the running man icon to preview the data as depicted below.

Conclusion

In this post we've shown the configuration steps needed to perform in order to unlock your MariaDB/MySQL data using MySQL Connector/J JDBC driver with Red Hat JBoss Data Virtualization.

Now we are ready to federate this data with other data-sources from physically distinct systems into such as other SQL databases, XML/Excel files, NoSQL databases, enterprise applications and web-services etc.

For more information about MariaDB, MySQL and Red Hat JBoss Data Virtualization please refer to the following websites:

Last updated: November 9, 2023