Function APPROXIMATE_MEDIAN(int) does not exist, or permission is denied for APPROXIMATE_MEDIAN(int)

According to the docs, APPROXIMATE_MEDIAN and APPROXIMATE_PERCENTILE were added in Vertica 8.1. However, in Vertica 9.3, this yields the error
ERROR 3457: Function APPROXIMATE_MEDIAN(int) does not exist, or permission is denied for APPROXIMATE_MEDIAN(int)

It fails for integers and floats, both as a regular db user as well as when signed in as dbadmin. Does this method need to be compiled or added before it can be used?

Best Answer

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited October 2020 Answer ✓

    You can also use admintools to reinstall the any package:


    [dbadmin@localhost ~]$ 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 ~]$ admintools -t install_package -d verticademos -P approximate --force-reinstall
    Installing package approximate...



  • Options
    edited October 2020

    I found this script in the directory, and running the following command as dbadmin successfully installed it.

    \i /opt/vertica/packages/approximate/ddl/install.sql

    Out of curiosity, where might I find the documentation for these? I didn't see a link from the main page https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/APPROXIMATE_PERCENTILE.htm

  • Options
    HibikiHibiki Vertica Employee Employee

    Approximate package is installed when creating the database. Did you see any error when installing that package? You can see the messages for this installation in /opt/vertica/log/adminTools.log file. For example:

    2020-09-28 10:48:18.511 admintools/4125:0x7f268d1cb700 [at_command.to_subprocess_invocation] <INFO> Command: [/opt/vertica/oss/python3/bin/python3 -m vertica.engine.api.at_runner --no-prompt --module vertica.engine.api.vsql_script.module]; input: [#{"vsql_path": "/opt/vertica/bin/vsql", "user": "dbadmin", "port": 5433, "db_name": "trial", "tmp_dir": "/tmp", "options": ["TUPLES_ONLY", "UNALIGNED"], "cwd": "/opt/vertica/packages/approximate", "__rollback__": false, "__protocol__": "1.0"}]
    2020-09-28 10:48:19.137 admintools/4125:0x7f2698759740 [adminExec._install_default_packages] <INFO> Success: package approximate installed

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file