Add Median Function to MySQL

This article describes how to add median UDF to MySQL to enable median aggregation for Datasets and Metrics. Whether MySQL is deployed in a container or on a remote database server, the median UDF package must be manually added to the MySQL database.

PREREQUISITES:

  • Direct access to the MySQL server with version 8.0.20 and above (UDFs cannot be added to MySQL-as-a-service, such as Amazon RDS)
  • Connect to MySQL container/instance and download the installation files:
    • MI Simple Installation:
      1. Enter MySQL container: docker exec -it mi_mysql_1 bash
      2. Download compiled artifact: wget https://metricinsights-downloads.s3.amazonaws.com/udf_infusion.tar.gz
      3. Download md5sum file: wget https://metricinsights-downloads.s3.amazonaws.com/udf_infusion.tar.gz.md
    • Other Installations:
      1. Log onto the database server
      2. Download compiled artifact
      3. Download md5sum file
  • All the commands listed in this article are executed from the database container or database host

1. Check Artifact File

Run the following shell script:

$ md5sum --check udf_infusion.tar.gz.md5
udf_infusion.tar.gz: OK

2. Determine MySQL Plugin Directory

Run the following next commands, replacing ${MYSQL_ROOT_PASSWORD} with your MySQL root user password:

$ mysql -uroot -p${MYSQL_ROOT_PASSWORD} 
mysql> SHOW VARIABLES WHERE Variable_Name = "plugin_dir";
+---------------+------------------------+ 
| Variable_name | Value                  | 
+---------------+------------------------+ 
| plugin_dir    | /usr/lib/mysql/plugin/ | 
+---------------+------------------------+ 
1 row in set (0.00 sec)

3. Install Tar File

$ tar -zxf udf_infusion.tar.gz  -C /usr/lib/mysql/plugin/ ./udf_infusion.so ./udf_infusion.la 
$ ls -l /usr/lib/mysql/plugin/udf* 
-rwxr-xr-x 1 root root    953 Nov  1  2020 /usr/lib/mysql/plugin/udf_infusion.la 
-rwxr-xr-x 1 root root 230720 Nov  1  2020 /usr/lib/mysql/plugin/udf_infusion.so 

$ tar -zxf udf_infusion.tar.gz  -C ./ ./load.sql 
$ mysql -uroot -p${MYSQL_ROOT_PASSWORD} < load.sql

4. Test the Added Function

Check whether the function has been added successfully to your MySQL database by calling it as follows:

$ mysql -uroot -p${MYSQL_ROOT_PASSWORD} 
mysql> use mysql;
mysql> SELECT median(User) from user; 
+--------------+ 
| median(User) | 
+--------------+ 
|            0 | 
+--------------+ 
1 row in set, 6 warnings (0.00 sec)