Magpie offers the MySQL data source, which provides users the ability to connect to MySQL databases through a standard JDBC Connector. The MySQL data source type improves compatibility between Magpie and MySQL over the generic JDBC connector in Magpie.
MySQL sources are configured very similarly to standard JDBC data sources in Magpie. Please view Data Source JSON specification to see more details. The configuration is set during the initial creation of the source or through an ALTER
statement using a JSON specification to set the values.
An example of such a command is shown below.
create data source { "name": "my_mysql_source", "sourceType": "mysql", "host": "mydb.mycompany.com", "port": 3306, "database": "analytics", "user": "my_user_name", "password": "**********" }
Once a MySQL source has been created, the tables within it can be used as the underlying storage for tables within Magpie. The commands present in the "Accessing Data from JDBC Sources" page are broadly applicable to MySQL data sources as well. MySQL databases are treated similarly to schemas in other database engines in order to enable cross-database joins.
Security
To access this type of data source, you may need to adjust the security configuration of your cloud environment. In particular, you need to generate database user credentials for the Magpie cluster, along with appropriate permissions to access relevant data. Please reach out to a member of the Silectis team with any questions or support requests.
Note, database data sources should generally be created using a user with read-only permissions to the target database. This can prevent unwanted updates to the target database resulting from actions within Magpie. If you do need to write data back to the database, it may be useful to define a separate data source to be used for writing, with stricter permissions.