Using Build-In Aggregate Functions

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;

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.

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

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.

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.

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