How to Connect Your PI AF with MySQL and MariaDB Using Linked Tables

How to Connect Your PI AF with MySQL and MariaDB Using Linked Tables

Integrating and connecting databases is a critical aspect of effective data management and utilization. In the era of big data, connecting different databases seamlessly ensures that data from various sources can be leveraged without friction. In particular, connecting MySQL or MariaDB databases to PI Asset Framework (AF) can be crucial for organizations using PI System as they aim to consolidate and analyze operational data.

In this post, we explore how you can achieve this connection, focusing on overcoming challenges related to driver compatibility and installation.

The Challenge

Consider a scenario where you are tasked with connecting a MySQL or MariaDB database to your PI AF system. Despite successfully enabling remote connections and having been able to connect to the database using Python's pyodbc package from a 64-bit computer, integrating it with PI AF can be challenging due to driver compatibility issues.

Initially, attempts to connect using various available drivers prove futile. This situation is not uncommon, as PI AF (like many systems) requires specific configuration to work seamlessly with external databases.

The Solution: Using Vendor-Specific ODBC Drivers

The breakthrough comes with the realization that both MySQL and MariaDB offer their own vendor-specific ODBC drivers, which are not only compatible but necessary for establishing a connection to PI AF via linked tables.

Here's a step-by-step guide to setting up the connection:

1. Download the Correct ODBC Driver:

   • For MySQL, you can download the Connector/ODBC driver from the MySQL official website.
   • For MariaDB, visit the MariaDB download page to get the appropriate driver.

2. Install the Driver on the AF Server:

   • Follow the specific installation instructions provided by the driver manufacturer. These may differ significantly from the installation process for other database drivers, such as OSIsoft's standard drivers.

3. Configure the Linked Table in PI AF:

   • Once the driver is installed, configure your PI AF to recognize the ODBC driver and set up the linked table. This may require specifying the driver in the connection string and providing the necessary database credentials.

4. Test the Connection:

   • Verify the connection by attempting to access a table or view data through your configured linked table in PI AF. Troubleshoot any errors by ensuring your configuration settings align with the database's connection requirements.

Benefits of a Successful Connection

By linking MySQL or MariaDB tables to your PI AF, you enable a more seamless integration of data flows into your operations. This connection allows users to leverage existing databases without the need for complex and error-prone data exports or transformations, enhancing the speed and reliability of data-driven decision-making.

Conclusion

Connecting MySQL or MariaDB to PI AF through linked tables demonstrates the importance of using correct vendor-specific drivers and following detailed installation instructions. By ensuring the right driver compatibility, organizations can unlock the full potential of their operational data, enabling more efficient and informed decision-making processes.

By thoughtfully integrating these databases within your data infrastructure, you set the stage for enhanced data accessibility and analytics capabilities, driving enterprise success.