-- prerequisite: install Vertica R package from your usual source -- prerequisite: install jsonlite and kpeaks packages into Vertica R UDx as shown at https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ExtendingVertica/R/RPackages.htm -- see kpeaks documentation at https://cran.r-project.org/web/packages/kpeaks/kpeaks.pdf -- publication at https://www.researchgate.net/publication/331258718_kpeaks_An_R_Package_for_Quick_Selection_of_K_for_Cluster_Analysis -- kpeaks SQL Test DROP TABLE IF EXISTS kpeaks_iris; DROP FUNCTION KPeaks_User(); DROP LIBRARY kpeaks_rLib cascade; -- Create table and load data CREATE TABLE kpeaks_iris (sl FLOAT, sw FLOAT, pl FLOAT, pw FLOAT, spec VARCHAR(32)); COPY kpeaks_iris FROM STDIN; 5.1|3.5|1.4|0.2|Iris-setosa 4.9|3.0|1.4|0.2|Iris-setosa 4.7|3.2|1.3|0.2|Iris-setosa 4.6|3.1|1.5|0.2|Iris-setosa 5.0|3.6|1.4|0.2|Iris-setosa 5.4|3.9|1.7|0.4|Iris-setosa 4.6|3.4|1.4|0.3|Iris-setosa 5.0|3.4|1.5|0.2|Iris-setosa 4.4|2.9|1.4|0.2|Iris-setosa 4.9|3.1|1.5|0.1|Iris-setosa 5.4|3.7|1.5|0.2|Iris-setosa 4.8|3.4|1.6|0.2|Iris-setosa 4.8|3.0|1.4|0.1|Iris-setosa 4.3|3.0|1.1|0.1|Iris-setosa 5.8|4.0|1.2|0.2|Iris-setosa 5.7|4.4|1.5|0.4|Iris-setosa 5.4|3.9|1.3|0.4|Iris-setosa 5.1|3.5|1.4|0.3|Iris-setosa 5.7|3.8|1.7|0.3|Iris-setosa 5.1|3.8|1.5|0.3|Iris-setosa 5.4|3.4|1.7|0.2|Iris-setosa 5.1|3.7|1.5|0.4|Iris-setosa 4.6|3.6|1.0|0.2|Iris-setosa 5.1|3.3|1.7|0.5|Iris-setosa 4.8|3.4|1.9|0.2|Iris-setosa 5.0|3.0|1.6|0.2|Iris-setosa 5.0|3.4|1.6|0.4|Iris-setosa 5.2|3.5|1.5|0.2|Iris-setosa 5.2|3.4|1.4|0.2|Iris-setosa 4.7|3.2|1.6|0.2|Iris-setosa 4.8|3.1|1.6|0.2|Iris-setosa 5.4|3.4|1.5|0.4|Iris-setosa 5.2|4.1|1.5|0.1|Iris-setosa 5.5|4.2|1.4|0.2|Iris-setosa 4.9|3.1|1.5|0.1|Iris-setosa 5.0|3.2|1.2|0.2|Iris-setosa 5.5|3.5|1.3|0.2|Iris-setosa 4.9|3.1|1.5|0.1|Iris-setosa 4.4|3.0|1.3|0.2|Iris-setosa 5.1|3.4|1.5|0.2|Iris-setosa 5.0|3.5|1.3|0.3|Iris-setosa 4.5|2.3|1.3|0.3|Iris-setosa 4.4|3.2|1.3|0.2|Iris-setosa 5.0|3.5|1.6|0.6|Iris-setosa 5.1|3.8|1.9|0.4|Iris-setosa 4.8|3.0|1.4|0.3|Iris-setosa 5.1|3.8|1.6|0.2|Iris-setosa 4.6|3.2|1.4|0.2|Iris-setosa 5.3|3.7|1.5|0.2|Iris-setosa 5.0|3.3|1.4|0.2|Iris-setosa 7.0|3.2|4.7|1.4|Iris-versicolor 6.4|3.2|4.5|1.5|Iris-versicolor 6.9|3.1|4.9|1.5|Iris-versicolor 5.5|2.3|4.0|1.3|Iris-versicolor 6.5|2.8|4.6|1.5|Iris-versicolor 5.7|2.8|4.5|1.3|Iris-versicolor 6.3|3.3|4.7|1.6|Iris-versicolor 4.9|2.4|3.3|1.0|Iris-versicolor 6.6|2.9|4.6|1.3|Iris-versicolor 5.2|2.7|3.9|1.4|Iris-versicolor 5.0|2.0|3.5|1.0|Iris-versicolor 5.9|3.0|4.2|1.5|Iris-versicolor 6.0|2.2|4.0|1.0|Iris-versicolor 6.1|2.9|4.7|1.4|Iris-versicolor 5.6|2.9|3.6|1.3|Iris-versicolor 6.7|3.1|4.4|1.4|Iris-versicolor 5.6|3.0|4.5|1.5|Iris-versicolor 5.8|2.7|4.1|1.0|Iris-versicolor 6.2|2.2|4.5|1.5|Iris-versicolor 5.6|2.5|3.9|1.1|Iris-versicolor 5.9|3.2|4.8|1.8|Iris-versicolor 6.1|2.8|4.0|1.3|Iris-versicolor 6.3|2.5|4.9|1.5|Iris-versicolor 6.1|2.8|4.7|1.2|Iris-versicolor 6.4|2.9|4.3|1.3|Iris-versicolor 6.6|3.0|4.4|1.4|Iris-versicolor 6.8|2.8|4.8|1.4|Iris-versicolor 6.7|3.0|5.0|1.7|Iris-versicolor 6.0|2.9|4.5|1.5|Iris-versicolor 5.7|2.6|3.5|1.0|Iris-versicolor 5.5|2.4|3.8|1.1|Iris-versicolor 5.5|2.4|3.7|1.0|Iris-versicolor 5.8|2.7|3.9|1.2|Iris-versicolor 6.0|2.7|5.1|1.6|Iris-versicolor 5.4|3.0|4.5|1.5|Iris-versicolor 6.0|3.4|4.5|1.6|Iris-versicolor 6.7|3.1|4.7|1.5|Iris-versicolor 6.3|2.3|4.4|1.3|Iris-versicolor 5.6|3.0|4.1|1.3|Iris-versicolor 5.5|2.5|4.0|1.3|Iris-versicolor 5.5|2.6|4.4|1.2|Iris-versicolor 6.1|3.0|4.6|1.4|Iris-versicolor 5.8|2.6|4.0|1.2|Iris-versicolor 5.0|2.3|3.3|1.0|Iris-versicolor 5.6|2.7|4.2|1.3|Iris-versicolor 5.7|3.0|4.2|1.2|Iris-versicolor 5.7|2.9|4.2|1.3|Iris-versicolor 6.2|2.9|4.3|1.3|Iris-versicolor 5.1|2.5|3.0|1.1|Iris-versicolor 5.7|2.8|4.1|1.3|Iris-versicolor 6.3|3.3|6.0|2.5|Iris-virginica 5.8|2.7|5.1|1.9|Iris-virginica 7.1|3.0|5.9|2.1|Iris-virginica 6.3|2.9|5.6|1.8|Iris-virginica 6.5|3.0|5.8|2.2|Iris-virginica 7.6|3.0|6.6|2.1|Iris-virginica 4.9|2.5|4.5|1.7|Iris-virginica 7.3|2.9|6.3|1.8|Iris-virginica 6.7|2.5|5.8|1.8|Iris-virginica 7.2|3.6|6.1|2.5|Iris-virginica 6.5|3.2|5.1|2.0|Iris-virginica 6.4|2.7|5.3|1.9|Iris-virginica 6.8|3.0|5.5|2.1|Iris-virginica 5.7|2.5|5.0|2.0|Iris-virginica 5.8|2.8|5.1|2.4|Iris-virginica 6.4|3.2|5.3|2.3|Iris-virginica 6.5|3.0|5.5|1.8|Iris-virginica 7.7|3.8|6.7|2.2|Iris-virginica 7.7|2.6|6.9|2.3|Iris-virginica 6.0|2.2|5.0|1.5|Iris-virginica 6.9|3.2|5.7|2.3|Iris-virginica 5.6|2.8|4.9|2.0|Iris-virginica 7.7|2.8|6.7|2.0|Iris-virginica 6.3|2.7|4.9|1.8|Iris-virginica 6.7|3.3|5.7|2.1|Iris-virginica 7.2|3.2|6.0|1.8|Iris-virginica 6.2|2.8|4.8|1.8|Iris-virginica 6.1|3.0|4.9|1.8|Iris-virginica 6.4|2.8|5.6|2.1|Iris-virginica 7.2|3.0|5.8|1.6|Iris-virginica 7.4|2.8|6.1|1.9|Iris-virginica 7.9|3.8|6.4|2.0|Iris-virginica 6.4|2.8|5.6|2.2|Iris-virginica 6.3|2.8|5.1|1.5|Iris-virginica 6.1|2.6|5.6|1.4|Iris-virginica 7.7|3.0|6.1|2.3|Iris-virginica 6.3|3.4|5.6|2.4|Iris-virginica 6.4|3.1|5.5|1.8|Iris-virginica 6.0|3.0|4.8|1.8|Iris-virginica 6.9|3.1|5.4|2.1|Iris-virginica 6.7|3.1|5.6|2.4|Iris-virginica 6.9|3.1|5.1|2.3|Iris-virginica 5.8|2.7|5.1|1.9|Iris-virginica 6.8|3.2|5.9|2.3|Iris-virginica 6.7|3.3|5.7|2.5|Iris-virginica 6.7|3.0|5.2|2.3|Iris-virginica 6.3|2.5|5.0|1.9|Iris-virginica 6.5|3.0|5.2|2.0|Iris-virginica 6.2|3.4|5.4|2.3|Iris-virginica 5.9|3.0|5.1|1.8|Iris-virginica \. -- Create the library and the transform function \set libfile '\''`pwd`'/kpeaks.R\'' CREATE LIBRARY kpeaks_rlib AS :libfile LANGUAGE 'R'; CREATE FUNCTION KPeaks_User AS LANGUAGE 'R' name 'KPeaks_UserFactory' LIBRARY kpeaks_rLib; -- Use the function SELECT * FROM ( SELECT KPeaks_User(sl,sw,pl,pw) FROM kpeaks_iris ) kp1 LIMIT 1;