CORR_MATRIX() error

mgallant

Hi,
I'm trying to use CORR_MATRIX (simplified example, I realize CORR would work for just 2 columns) as follows:
select CORR_MATRIX("hour_7", "drops") OVER () from (
select * from (select apply_one_hot_encoder(* using parameters model_name='test_encoder', drop_first='false') from test) as Q1
) as Q2;

but getting this error:
ERROR 3457: Function CORR_MATRIX(int, int) does not exist, or permission is denied for CORR_MATRIX(int, int)
I'm running
vsql version 09.02.0007, built for Linux64
From JB Favre's vertica docker container
https://hub.docker.com/r/jbfavre/vertica/
The docs say CORR_MATRIX is a 9.2.x feature, but I can't find details more granular than that.
Any ideas?
Thanks,
Michael

Vertica_Curtis

9.2.1
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/NewFeatures/9.2/9.2.1/MachineLearning.htm?tocpath=Vertica 9.2.x New Features and Changes|New and Changed in Vertica 9.2.1|_____7

mgallant

The complete use case is for many columns (hour_0, hour_1,...,hour23). That was just a simplified example.
I just noticed there were 2 replies...so it's a 9.2.1 feature. Understood, and thanks!

Vertica_Curtis

But as you indicated, just use CORR(), since you're just analyzing two columns.

BETEPOK

I have this error too:
Vertica 9.3
variant1. SELECT corr_matrix(prescr_amount,total_pat_flow,target_pat_flow) OVER() FROM Baikal_Progressa_table;
ERROR: Function corr_matrix(float, float, float) does not exist, or permission is denied for corr_matrix(float, float, float)

variant2.
SELECT corr_matrix("prescr_amount","total_pat_flow","target_pat_flow") OVER() FROM Baikal_Progressa_table;
ERROR: Function corr_matrix(float, float, float) does not exist, or permission is denied for corr_matrix(float, float, float)

variant3.
SELECT corr_matrix('prescr_amount', 'total_pat_flow', 'target_pat_flow') OVER() FROM Baikal_Progressa_table;
ERROR: Function corr_matrix(unknown, unknown, unknown) does not exist, or permission is denied for corr_matrix(unknown, unknown, unknown)

Where my mistake??

Vertica_Curtis

Your first option should run.
SELECT CORR_MATRIX(Sepal_Length, Sepal_Width, Petal_Length, Petal_Width) OVER() FROM iris;
Perhaps it's a problem with the function. You might need to reinstall the packages associated with that function (probably the machine learning package). https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/InstallationGuide/Upgrade/PostUpgrade/UpgradingandReinstallingPackages.htm?zoom_highlight=uninstall package

BETEPOK

Thanks!
What is the name of the machine learning package? Where can I see it?

Jim_Knicely

All of the Vertica packages are located under /opt/vertica/packages.
You can reinstall the MachineLearning package like this:

[dbadmin@localhost packages]$ admintools -t list_packages
             Package Description
                 AWS Amazon Web Services Package
        ComplexTypes Functions for Complex Types
     MachineLearning In-dataBase Machine Learning package.
       ParquetExport Functions that support Parquet Export
      SparkConnector Spark connector for reading from and writing into Vertica
       TFIntegration Using Tensorflow models in Vertica
          VFunctions Vertica functions
         approximate Probabilistic/Approximate Aggregation Functions
           flextable Flexible Tables Data Load and Query
                hcat Java/HCatalog data load and query
               kafka Kafka streaming load and export
           logsearch Common Advanced text search functions
               place Geospatial Data Query
            txtindex Common string-manipulation functions
       voltagesecure Voltage SecureData functions

[dbadmin@localhost packages]$ admintools -t install_package -d  verticademos -P MachineLearning --force-reinstall
Installing package MachineLearning...
...Success!

BETEPOK

Thanks. I'll tryT

BETEPOK

It works!!