Using Build-In Aggregate Functions

Hi,

I'm trying to use the APPROXIMATE_COUNT_DISTINCT function.  When I execute this function with a superuser, it works fine.  However, if I use a different user, I get this error reported from the Vertica driver.

ERROR: Function ApproxCountDistinct(int) does not exist, or permission is denied for ApproxCountDistinct(int)

When I query the v_catalog.user_functions table, I see the function does exists, with the following info:

procedure_type = User Defined Aggregate
function_argument_type = Integer
function_definition = Class 'ApproxCountDistinctFactory' in Library 'dbadmin.ApproximateLib'
schema_name = dbadmin

As per the Vertica documentation, I even granted EXECUTE permission on this aggregate function to that user.  I even granted the USAGE permission to the schema.

From what I have witnessed, it seems like I need to somehow add an entry to the user_functions table for this same function, but have the schema be different (because I would like to run the function against data in a separate schema).

So it looks like I have to add this function using CREATE AGGREGATE FUNCTION, but this requires loading a library that contains the function.  Since this is a built-in function, I don't know where it is located.  So I can't run the required CREATE LIBRARY statement.

Basically, I have something like this:
CREATE OR REPLACE AGGREGATE FUNCTION db.schema.ApproxCountDistinct AS LANGUAGE 'C++' NAME 'ApproxCountDistinctFactory' LIBRARY 'dbadmin.ApproximateLib' (the name and library value I copied from the existing entry in user_functions table).

Does anyone know how I can use this built in function in the schema that I want?  Am I going down the wrong path as stated above?  What am I doing wrong?

Comments

  • I found the library.  Now I ran this command in vsql, but the library is not created.

    \set u_libfile '\'/opt/vertica/packages/approximate/lib/libvertica_approximate.so\'';\echo Loading the Approximate library: :u_libfile
    CREATE OR REPLACE LIBRARY myDB.mySchema.ApproximateLib' AS :u_libfile;
  • I found the problem.  I had a syntax error.  The final statement which works is:

    CREATE OR REPLACE LIBRARY db.schema.ApproximateLib AS '/opt/vertica/packages/approximate/lib/libvertica_approximate.so';CREATE OR REPLACE AGGREGATE FUNCTION db.schema.ApproxCountDistinct AS LANGUAGE 'C++' NAME 'ApproxCountDistinctFactory' LIBRARY db.schema.ApproximateLib;
  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    I checked this function and it's schema is 'public'. Not sure how is you schema set to dbadmin.

    dbadmin=> select * from user_functions limit 1;
     schema_name |    function_name    |     procedure_type     | function_return_type | function_argument_type |                          function_definition                          | volatility | is_strict | is_fenced | comment
    -------------+---------------------+------------------------+----------------------+------------------------+-----------------------------------------------------------------------+------------+-----------+-----------+---------
     public      | ApproxCountDistinct | User Defined Aggregate | Integer              | Integer                | Class 'ApproxCountDistinctFactory' in Library 'public.ApproximateLib' |            | f         | f         |
    (1 row)


    You may follow the link to drop and recreate this function in public schema and also describes how you can grant other users to execute if it is created in other schema.

    http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEAGGREG...


  • Hi Prasanta,

    I'm not sure why the schema is not set to public.  It must have been done by the db admin.  But in any case, the fact that it is not public is not the question here.  Perhaps if the schema is public, then I can use this function in any schema then, is that correct?

    In any case, I created the library and the aggregate function from the library and associated with my schema.  I even granted EXECUTE permission for the function to the user and USAGE permission to the user for the SCHEMA which the function is associated with and I still can't run this function.  I get the same error as previously stated.  Can you please help?
  • UPDATE:  I found another server where the schema is 'public' for this function and I'm able to run the function fine.

    So I guess my next question is:  how do I change the schema for this function to 'public'?  I would hate to re-create the library and function.  I'm hoping for an easy way to accomplish this.
  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    Some examples are there in the link to alter the schema of the function
    => ALTER FUNCTION zeronull(x INT) SET SCHEMA macros; ALTER FUNCTION
    http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/ALTERFUNCTIO...

    You may create the same function in public schema as below:

    CREATE OR REPLACE LIBRARY ApproximateLib AS '/opt/vertica/packages/approximate/lib/libvertica_approximate.so';
    CREATE OR REPLACE AGGREGATE FUNCTION ApproxCountDistinct AS LANGUAGE 'C++' NAME 'ApproxCountDistinctFactory' LIBRARY ApproximateLib;

    Then you can drop the previous one once this is created or you can leave that one in that specific schema.

  • Hi,

    Running "select * from vs_procedures where procedure_name ilike '%appr%';" shows that ApproxCountDistinct procedure is under the public schema. 

    dbadmin=> select * from vs_procedures where procedure_name ilike '%appr%'; 

    proc_oid | procedure_name | procedure_return_type | procedure_argument_types | schema_name | definition | volatility | is_strict | user_defined | is_fenced | procedure_type | language 


    58546795159730038 | ApproxCountDistinct | Integer | Integer | public | Class 'ApproxCountDistinctFactory' in Library 'public.ApproximateLib' | | f | t | f | User Defined Aggregate | C++ 
    58546795159730044 | ApproxCountDistinctSynopsis | Varbinary | Integer | public | Class 'ApproxCountDistinctSynopsisFactory' in Library 'public.ApproximateLib' | | f | t | f | User Defined Aggregate | C++ 
    58546795159730050 | ApproxCountDistinctOfSynopsis | Integer | Varbinary | public | Class 'ApproxCountDistinctOfSynopsisFactory' in Library 'public.ApproximateLib' | | f | t | f | User Defined Aggregate | C++ 
    58546795159730056 | ApproxCountDistinct5PCT | Integer | Integer | public | Class 'ApproxCountDistinct5PCTFactory' in Library 'public.ApproximateLib' | | f | t | f | User Defined Aggregate | C++ 
    58546795159730062 | ApproxCountDistinctLong | Integer | Integer | public | Class 'ApproxCountDistinctLongFactory' in Library 'public.ApproximateLib' | | f | t | f | User Defined Aggregate | C++ 
    58546795159730068 | ApproxCountDistinctLongSynopsis | Long Varbinary | Integer | public | Class 'ApproxCountDistinctLongSynopsisFactory' in Library 'public.ApproximateLib' | | f | t | f | User Defined Aggregate | C++ 
    58546795159730074 | ApproxCountDistinctOfLongSynopsis | Integer | Long Varbinary | public | Class 'ApproxCountDistinctOfLongSynopsisFactory' in Library 'public.ApproximateLib' | | f | t | f | User Defined Aggregate | C++ 
    (7 rows) 


    That is why it's not able to find the function ApproxCountDistinct under myschema. This can be verified by setting: 

    "set search_path=public" 

    This time it passes without any errors: 

    [dbadmin@hdhavale4 Downloads]$ java -cp vertica-jdbc-7.0.1-0.jar:. ExecuteVerticaQuery_1 "jdbc:vertica://localhost:5433/hd_vertica7_db" dbadmin password 
    "select count(distinct table_id) from system_tables" returned: 129 
    "select approximate_count_distinct(table_id) from system_tables" returned: 129 

    ## At this point "set search_path=public" is run



    "select count(distinct table_id) from system_tables" returned: 129 
    "select approximate_count_distinct(table_id) from system_tables" returned: 129 



    This time, approximate_count_distinct returns successfully.



    I hope this helps.


    Regards

    Bhawana






  • Thanks all.

    I am now able to create the library and function on the 'public' schema and verified that it is working now. 

    I still have 1 more question though.  I dropped the library that is created on the dbadmin schema and wanted to do a clean install of this library and associated functions on the 'public' schema.  I see that there is a  '/opt/vertica/packages/approximate/ddl/install.sql'.  When I run this via the Linux shell via the following, the library is still created with the dbadmin schema. 

    /opt/vertica/bin/vsql -X -At -U user-w pasword -f "/opt/vertica/packages/approximate/ddl/install.sql"

    How do I run this script but force it to use the 'public' schema instead w/o modifying the script to include the public schema in the library and functions.  I went to the following URL, but couldn't find anything related to it:  http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/ConnectingToHPVertica/vsql/CommandLineOpti....


  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    Checked if it is installed:

    [dbadmin@ppal6 ~]$ vsql -f /opt/vertica/packages/approximate/ddl/isinstalled.sql
     ?column?
    ----------
     t
    (1 row)

    2. unstalled

    [dbadmin@ppal6 ~]$ vsql -f /opt/vertica/packages/approximate/ddl/uninstall.sql
    DROP LIBRARY

    3. Checked the user dbadmin

    dbadmin=> select * from users;
          user_id      | user_name | is_super_user | profile_name | is_locked | lock_time | resource_pool | memory_cap_kb | temp_space_cap_kb | run_time_cap |              all_roles               |            default_roles             | search_path
    -------------------+-----------+---------------+--------------+-----------+-----------+---------------+---------------+-------------------+--------------+--------------------------------------+--------------------------------------+-------------
     45035996273704962 | dbadmin   | t             | default      | f         |           | general       | unlimited     | unlimited         | unlimited    | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser* |
    (1 row)

    dbadmin=> \q

    4. Recreated the function

    [dbadmin@ppal6 ~]$ vsql -f /opt/vertica/packages/approximate/ddl/install.sql
                  version              
    ------------------------------------
     Vertica Analytic Database v7.1.0-1
    (1 row)

    Loading the Approximate library: '/opt/vertica/packages/approximate/lib/libvertica_approximate.so';
    CREATE LIBRARY
    CREATE AGGREGATE FUNCTION
    GRANT PRIVILEGE
    CREATE AGGREGATE FUNCTION
    GRANT PRIVILEGE
    CREATE AGGREGATE FUNCTION
    GRANT PRIVILEGE
    CREATE AGGREGATE FUNCTION
    GRANT PRIVILEGE
    CREATE AGGREGATE FUNCTION
    GRANT PRIVILEGE
    CREATE AGGREGATE FUNCTION
    GRANT PRIVILEGE
    CREATE AGGREGATE FUNCTION
    GRANT PRIVILEGE
    [dbadmin@ppal6 ~]$
    [dbadmin@ppal6 ~]$
    [dbadmin@ppal6 ~]$
    [dbadmin@ppal6 ~]$ vsql
    Welcome to vsql, the Vertica Analytic Database interactive terminal.

    Type:  \h or \? for help with vsql commands
           \g or terminate with semicolon to execute query
           \q to quit

    dbadmin=> select * from v_catalog.user_functions limit 1;
     schema_name |    function_name    |     procedure_type     | function_return_type | function_argument_type |                          function_definition                          | volatility | is_strict | is_fenced | comment
    -------------+---------------------+------------------------+----------------------+------------------------+-----------------------------------------------------------------------+------------+-----------+-----------+---------
     public      | ApproxCountDistinct | User Defined Aggregate | Integer              | Integer                | Class 'ApproxCountDistinctFactory' in Library 'public.ApproximateLib' |            | f         | f         |
    (1 row)


    let me know if you have followed the same steps as I did, it created in the public schema
    OR
    you can paste the steps how you create the function



  • 1. I ran "DROP LIBRARY myDB.public.ApproximateLib CASCADE;
    2. I ran  vsql -f /opt/vertica/packages/approximate/ddl/isinstalled.sql and got "f" which means false.
    3. I ran vsql -f /opt/vertica/packages/approximate/ddl/install.sql and got the same output as you, which says it created the library and and aggregate functions and granted privileges.
    4. I ran select * from user_libraries; and now I'm seeing the schema_name = dbadmin and lib_name = ApproximateLib
    5. SELECT * FROM AGG.v_catalog.user_functions; also shows the functions from that library having a schema_name of "dbadmin"

    I want the schema_name to public.
  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    Which version you are running?

    select version();

    Check the current schema of the user when you login

    dbadmin=> SELECT CURRENT_SCHEMA();
     current_schema
    ----------------
     public
    (1 row)




  • I am running version 7.0.0.0-0. 
    Current schema is dbadmin.  Looks like that is the problem.  How do I change the current schema for that user?
  • I found my answer.  I can run the "SET SEARCH_PATH" to change it to use 'public' just for the session, then run the install script, which will create the library and functions using the 'public' schema.

    Thanks all for your help.
  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    Glad to hear that your problem is resolved!!!
  • Hi Binh,

    That is what I have suggested in my reply above. We are glad that it got resolved.

    Regards

    Bhawana

Leave a Comment

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