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)

1. Connect to MySQL Container/Instance and Download the Installation Files

  1. 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.md5
    • Orchestration:
      1. Log onto the database server or access the web container >>mysql
      2. Download compiled artifact
      3. Download md5sum file
  2. Check the artifact file by running the following shell script:
$ md5sum --check udf_infusion.tar.gz.md5
udf_infusion.tar.gz: OK
  1. If MySQL database is deployed in a container:
    • Exit the MySQL container and access the web container:
$ exit
$ mi-console

2. Determine MySQL Plugin Directory

If MySQL is deployed in a container:

  • Run the following next commands, replacing ${MYSQL_ROOT_PASSWORD} with your MySQL root user password: $ mysql -uroot -p${MYSQL_ROOT_PASSWORD}

If MySQL is deployed in a standalone server, ignore the $ mysql -uroot -p${MYSQL_ROOT_PASSWORD} command.

$ 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)

mysql> exit
Click to copy

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
Click to copy

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)
Click to copy