Azure Data Explorer is a fully managed, high-performance, big data analytics platform that makes it easy to analyze high volumes of data in near real time. The Azure Data Explorer toolbox gives you an end-to-end solution for data ingestion, query, visualization, and management. Azure Data Explorer is scalable, secure, robust, and enterprise-ready, and is useful for log analytics, time series analytics, IoT, and general-purpose exploratory analytics.
The mysql_request plugin in Azure Data Explorer sends query to a Azure Database for MySQL - Flexible Server network endpoint and returns the first row set in the results. The query may return more than one row set, but only the first row set is made available for the rest of the Kusto query.
This post walks you through the process of connecting to and querying an Azure Database for MySQL flexible server from Azure Data Explorer.
Prerequisites
Before you begin to work through the process outlined in this post, be sure to create an:
Connect to the cluster and database in Azure Data Explorer
To connect to the cluster and database in Azure Data Explorer, perform the following steps:
1. Open a browser, navigate to https://dataexplorer.azure.com, and then select Add Cluster:
2. In the Add Cluster dialog box, specify a connection URI and display name, and then select Add.
After you’ve connected to the cluster, you’ll see the database that’s part of the cluster.
3. Select the database to set the scope.
Enable the mysql_request plugin
The mysql_request plugin is in preview mode and is disabled by default.
- To enable the mysql_request plugin, in the query window, run the following command: .enable plugin mysql_request
Note: To verify which plugins are enabled, run the .show plugins management command.
The mysql_request plugin makes callouts to the MySQL database, so you need to ensure that the cluster's callout policy enables calls of type mysql to the target MySqlDbUri.
- To set the callout policy, run the following command:
.alter cluster policy callout @'[{"CalloutType": "mysql", "CalloutUriRegex": "\\.mysql\\.database\\.azure\\.com", "CanCall": true}]'
Note: The command above includes an alter callout policy command for mysql CalloutType. It's recommended that you restrict the callout policy to specific endpoints, for example: "CalloutUriRegex": "my_endpoint1\\.mysql\\.database\\.azure\\.com",
Connect to the Azure Database for MySQL flexible server
When you connect to the Azure Database for MySQL flexible server, the firewall verifies that Azure connections are allowed. To enable this setting, in the portal, on the Networking tab, select the Allow public access from Azure services and resources within Azure to this server option, and then select Save. For more information, see Connect from Azure.
Important: Be sure to obfuscate any confidential or guarded information from connection strings and queries to ensure that it is omitted from any Kusto tracing. For more information, see Obfuscated string literals.
To connect to the Azure Database for MySQL flexible server, modify the parameters in the following command, and then run the command:
evaluate mysql_request(
'Server=<servername>; Port = 3306;'
'Database=<databasename>;'
h'UID=<username>;'
h'Pwd=<password>;',
'<Query>')
|
Parameter |
Replace with the… |
|
<servername> |
Name of the Azure Database for MySQL flexible server to which you want to connect. |
|
<databasename> |
Name of the database to connect to and query. |
|
<username> |
Name of the user to connect with to query database. |
|
<Password> |
Password to use to connect to database. |
|
<Query> |
Query that you want to run against the database. |
The following example command runs a query to retrieve all table information for a database named mysql, filters the information, and then shows the requested columns.
evaluate mysql_request(
'Server=snmyqlflexidemo1.mysql.database.azure.com; Port = 3306;'
'Database=mysql;'
h'UID=mysqladmin;'
h'Pwd=Password;',
'SELECT * FROM information_schema.tables;')
| where TABLE_SCHEMA == 'bidirrepldemo'
| project TABLE_NAME,TABLE_TYPE,ENGINE
Conclusion
Connecting to different data sources is a requirement when you want to have an end-to-end solution that provides for data ingestion, query, visualization, and management. You can use the mysql_request plugin to connect to the databases hosted on an Azure Database for MySQL flexible server from Azure Data Explorer.
If you have any feedback or questions, please leave a comment below or email our team at AskAzureDBforMySQL@service.microsoft.com. Thank you!