export to Parquet : Error 3457

The "Export to Parquet" feature was introduced in Vertica 8.1.1.

See:
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/NewFeatures/8.1/8.1.1/Hadoop.htm

You can check if the Parquet Export package is installed like this:

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

If you need to re-install it, you can first uninstall it:

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

Then install it:

[dbadmin@vertica01 ~]$ vsql -f /opt/vertica/packages/ParquetExport/ddl/install.sql
              version
------------------------------------
 Vertica Analytic Database v9.0.1-0
(1 row)

CREATE LIBRARY
CREATE TRANSFORM FUNCTION
CREATE TRANSFORM FUNCTION
CREATE TRANSFORM FUNCTION

The parquetexport package is installed. And they are still getting the error.Is this a priviliges issue? If so, what privilege does the user need to execute this? They are able to load parquet data (copy) into vertica just fine.

Hi,

I have a feeling it's not really installed, or the wrong version is installed.

For example, say I uninstall it:

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

I'll get the same error:

[dbadmin@s18384357 ~]$ 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=> export to parquet (directory='/home/dbadmin/test_parq') as select * from test;
ERROR 3457:  Function ParquetExportFinalize(int) does not exist, or permission is denied for ParquetExportFinalize(int)
HINT:  No function matches the given name and argument types. You may need to add explicit type casts

Until I reinstall:

[dbadmin@s18384357 ~]$ rm -fr /home/dbadmin/test_parq

[dbadmin@s18384357 ~]$ 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=> export to parquet (directory='/home/dbadmin/test_parq2') as select * from test;
 Rows Exported
---------------
             1
(1 row)

I wonder if a previous database upgrade failed?

Check the USER_LIBRARIES system table to make sure everything looks good.

Example:

dbadmin=> \x
Expanded display is on.
dbadmin=> select * from user_libraries where lib_name = 'ParquetExportLib';
-[ RECORD 1 ]-----+---------------------------------------------------------------------
schema_name       | public
lib_name          | ParquetExportLib
lib_oid           | 45035996287409142
author            | HPE Vertica
owner_id          | 45035996273704962
lib_file_name     | ParquetExportLib_02e43502fb456e1da3dd4e169c72c9d300a0000000d11bf6.so
md5_sum           | 0fafa4cc79f4ffa5a9b277c79bc46b7b
sdk_version       | 9.0.1
revision          | releases/VER_9_0_RELEASE_BUILD_1_2_20180122
lib_build_tag     | releases/VER_9_0_RELEASE_BUILD_1_2_20180122
lib_version       | 9.0.1
lib_sdk_version   | 9.0.1
source_url        | http://www.vertica.com/
description       | Parquet export package
licenses_required |
signature         |
dependencies      |
is_valid          | t
sal_storage_id    | 02e43502fb456e1da3dd4e169c72c9d300a0000000d11bf6

GRANT ALL ON LIBRARY public.ParquetExportLib to user;
GRANT ALL ON TRANSFORM FUNCTION public.ParquetExport(int) TO user;
GRANT ALL ON TRANSFORM FUNCTION public.ParquetExportMulti(int) TO user;
GRANT ALL ON TRANSFORM FUNCTION public.ParquetExportFinalize(int) TO user;

I have executed the grant statements, but still receive the error:

[Vertica]VJDBC ERROR: Function ParquetExportFinalize(int) does not exist, or permission is denied for ParquetExportFinalize(int)

@victoryhl7 - Can you describe the function?

Example:

dbadmin=> \df ParquetExportFinalize*
                            List of functions
    procedure_name     | procedure_return_type | procedure_argument_types
-----------------------+-----------------------+--------------------------
 ParquetExportFinalize | Any                   | Any
(1 row)

If not, try reinstalling the ParquetExport package:

Example:

[dbadmin@s18384357 ~]$ admintools -t install_package -d test_db -p password --package ParquetExport --force-reinstall
Installing package ParquetExport...
...Success!

Make sure you see the privileges in the GRANTS system table:

dbadmin=> SELECT privileges_description, object_schema, object_name, object_type, grantee FROM grants WHERE object_name = 'ParquetExportFinalize';
 privileges_description | object_schema |      object_name      | object_type | grantee
------------------------+---------------+-----------------------+-------------+---------
 EXECUTE*               | public        | ParquetExportFinalize | PROCEDURE   | dbadmin
 EXECUTE                | public        | ParquetExportFinalize | PROCEDURE   | public
(2 rows)

I could export to parquet using dbadmin, but not another user. I still face the same error after reinstalling the ParquetExport package.

VERTICADB=> \df ParquetExportFinalize*
                            List of functions
    procedure_name     | procedure_return_type | procedure_argument_types
-----------------------+-----------------------+--------------------------
 ParquetExportFinalize | Any                   | Any
(1 row)


    VERTICADB=> SELECT privileges_description, object_schema, object_name, object_type, grantee FROM grants WHERE object_name like 'Parquet%' order by grantee;
                               privileges_description                            | object_schema |      object_name      | object_type |  grantee
    -----------------------------------------------------------------------------+---------------+-----------------------+-------------+-----------
     EXECUTE                                                                     | public        | ParquetExportFinalize | PROCEDURE   | user
     USAGE                                                                       | public        | ParquetExportLib      | LIBRARY     | user
     EXECUTE                                                                     | public        | ParquetExportMulti    | PROCEDURE   | user
     EXECUTE                                                                     | public        | ParquetExport         | PROCEDURE   | user
     EXECUTE*                                                                    | public        | ParquetExportMulti    | PROCEDURE   | dbadmin
     EXECUTE*                                                                    | public        | ParquetExport         | PROCEDURE   | dbadmin
     EXECUTE*                                                                    | public        | ParquetExportFinalize | PROCEDURE   | dbadmin
     INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, USAGE*, CREATE*, TRUNCATE* | public        | ParquetExportLib      | LIBRARY     | dbadmin

Looks like you are missing the grants for PUBLIC (your result set from GRANTS does not show "public" as a grantee).

This is what see:

dbadmin=> SELECT privileges_description, object_schema, object_name, object_type, grantee FROM grants WHERE object_name like 'Parquet%' order by grantee;
 privileges_description | object_schema |      object_name      | object_type | grantee
------------------------+---------------+-----------------------+-------------+---------
 EXECUTE*               | public        | ParquetExport         | PROCEDURE   | dbadmin
 EXECUTE*               | public        | ParquetExportFinalize | PROCEDURE   | dbadmin
 EXECUTE*               | public        | ParquetExportMulti    | PROCEDURE   | dbadmin
 EXECUTE                | public        | ParquetExportMulti    | PROCEDURE   | public
 EXECUTE                | public        | ParquetExport         | PROCEDURE   | public
 EXECUTE                | public        | ParquetExportFinalize | PROCEDURE   | public
(6 rows)

Run these commands as DBADMIN:

GRANT EXECUTE ON TRANSFORM FUNCTION public.ParquetExportFinalize() TO PUBLIC;

GRANT EXECUTE ON TRANSFORM FUNCTION public.ParquetExportMulti() TO PUBLIC;

GRANT EXECUTE ON TRANSFORM FUNCTION public.ParquetExport() TO PUBLIC;

Then try re-running the query against the GRANTS table. Do you see "public" as a grantee?

I have executed the following:

`GRANT EXECUTE ON TRANSFORM FUNCTION public.ParquetExportFinalize() TO PUBLIC;

GRANT EXECUTE ON TRANSFORM FUNCTION public.ParquetExportMulti() TO PUBLIC;

GRANT EXECUTE ON TRANSFORM FUNCTION public.ParquetExport() TO PUBLIC;`

I am able to see "public" as grantee, but I am still getting the same error.

I manage to export to parquet now. If I run the export script in "public" schema, then it works. My user has a default search_path that points to another schema.

@victoryhl7 -

Nice!

Here's a tip that you might find useful:

https://forum.vertica.com/discussion/239667/when-modifying-a-search-path-don-t-forget-to-include-public

Thanks for the tips. After adding "public" to the user's search_path, the export command can be run in other schema as well.

AWESOME!

This was a very helpful thread to me nearly two years later. Thank you team!