Using Build-In Aggregate Functions

Binh_1 · January 2015

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?

Binh_1 · January 2015

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;

Binh_1 · January 2015

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_Pal · January 2015

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...

Binh_1 · January 2015

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?

Binh_1 · January 2015

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_Pal · January 2015

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.

Bhawana · January 2015

Binh_1 · January 2015

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_Pal · January 2015

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

Binh_1 · January 2015

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_Pal · January 2015

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)

Binh_1 · January 2015

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?

Binh_1 · January 2015

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_Pal · January 2015

Glad to hear that your problem is resolved!!!

Bhawana · January 2015

Hi Binh,

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

Regards

Bhawana

Using Build-In Aggregate Functions

Comments

Leave a Comment