export to Parquet : Error 3457

RKverticaRKvertica Registered User

A customer is receiving Error 3457: Function ParquetExportFinalize(int) does not exist, or permission is denied for ParquetExportFinalize(int). They are on 8.1.1-6 . Any thoughts? They are using dbadmin. Does this function exist on 8.1.1-6?

thanks.

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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:

    [[email protected] ~]$ 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:

    [[email protected] ~]$ vsql -f /opt/vertica/packages/ParquetExport/ddl/uninstall.sql
    DROP LIBRARY
    

    Then install it:

    [[email protected] ~]$ 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
    
  • RKverticaRKvertica Registered User

    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.

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    Hi,

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

    For example, say I uninstall it:

    [[email protected] ~]$ vsql -f /opt/vertica/packages/ParquetExport/ddl/uninstall.sql
    DROP LIBRARY
    

    I'll get the same error:

    [[email protected] ~]$ 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:

    [[email protected] ~]$ rm -fr /home/dbadmin/test_parq
    
    [[email protected] ~]$ 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)
    
  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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
    
  • xinvincible21xinvincible21 Registered User

    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;

  • victoryhl7victoryhl7 Registered 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)

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited August 16

    @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:

    [[email protected] ~]$ admintools -t install_package -d test_db -p password --package ParquetExport --force-reinstall
    Installing package ParquetExport...
    ...Success!
    
  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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)
    
  • victoryhl7victoryhl7 Registered User

    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
    
  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited August 17

    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?

  • victoryhl7victoryhl7 Registered User

    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.

  • victoryhl7victoryhl7 Registered User

    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.

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
  • victoryhl7victoryhl7 Registered User

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

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    AWESOME!

Leave a Comment

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