Copy with Delayed_Creation Failed

ERROR: ERROR 6774: Failed to create default key projections for table "ODS"."analyst": Permission denied for schema ODS
HINT: Ensure that tables involved in the query and their key projections exist (DDL interference)
It is my understanding that the initial load of a table will create and load the superprojection. The following created the projection but it did no load it. Permissions are correct.
COPY ods.analyst (CLAIM,DIAG,QLF_CODE,CDE_POA) FROM LOCAL \'/dss/load/ODS_analyst_20170106.TXT\' DELIMITER \'|\' NO ESCAPE TRAILING NULLCOLS EXCEPTIONS \'/dss/error/ODS_analyst_20170106_exceptions.txt\' REJECTED DATA \'/rejected/ODS_analyst_20170106_rejected_data.txt\' ABORT ON ERROR DIRECT"']

Comments

  • SruthiASruthiA Vertica Employee Administrator

    To me it looks like you are facing a known issue with regards to grants which has been fixed recently. Please upgrade to 10.1.0-2 and try running the test.

  • The upgrade to 10.1.0-2 is complete and the error still occurs.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    @Michael_Pacocha - Can you post the DDL for the table ods.analyst?

  • It doesn't match the copy columns in the above statement. I edited that one. The real copy command is the same as above with these columns listed:
    CREATE TABLE ODS.T_ANALYST
    (
    ID_CLERK char(8) NOT NULL,
    NAM_FIRST varchar(50) NOT NULL,
    NAM_LAST varchar(50) NOT NULL,
    NUM_PHONE varchar(10) NOT NULL,
    NAM_MID_INIT char(4) NOT NULL,
    NAME_USER varchar(32) NOT NULL,
    NAM_DOMAIN varchar(67) NOT NULL,
    CDE_NOTIFICATION_TYPE char(1) NOT NULL,
    ADR_EMAIL varchar(256) NOT NULL,
    CDE_STATUS char(1) NOT NULL,
    NUM_PHONE_EXT char(4) NOT NULL,
    CONSTRAINT I_ANALYST_NAME_USER UNIQUE (NAME_USER, NAM_DOMAIN) ENABLED,
    CONSTRAINT I_ANALYST PRIMARY KEY (ID_CLERK) ENABLED
    );

  • I have opened SD02862074 for this. When I run the copy from vSQL it works. We are running this from a Python script. When the projection exists the script works. When we depend on the delayed_creation we get the above message.

  • 2021-03-16 11:31:03.261 Init Session:0x7f05aafed700-a0000000022370 [Txn] Begin Txn: a0000000022370 'TRUNCATE TABLE ods.t_auth_change; COPY ods.t_auth_change (SAK_AUTH,ID_AUTH,CDE_AUTH_TYPE,ID_STATE,DSC_AUTH,DTE_RECEIVED) FROM LOCAL '/dsms/acc/data/biar/dss/load/DSSJW100_MS_HP_ODS_t_auth_change_20170106.TXT' DELIMITER '|' NO ESCAPE TRAILING NULLCOLS EXCEPTIONS '/dsms/acc/data/biar/dss/error/DSSJW100_MS_HP_ODS_t_auth_change_20170106_exceptions.txt' REJECTED DATA '/dsms/acc/data/biar/dss/rejected/DSSJW100_MS_HP_ODS_t_auth_change_20170106_rejected_data.txt' ABORT ON ERROR DIRECT'
    2021-03-16 11:31:03.261 Init Session:0x7f05aafed700-a0000000022370 [Command] [AutoProj] creating default projection query string: SELECT ID_AUTH FROM ODS.T_AUTH_CHANGE
    2021-03-16 11:31:03.261 Init Session:0x7f05aafed700-a0000000022370 [Command] [AutoProj] creating lazy projection for constraint I_AUTH_CHANGE_2: CREATE PROJECTION ODS.T_AUTH_CHANGE /+ _oidrefs(_16,_45035996273844554,_45035996273845104) */ ( ID_AUTH) AS SELECT ID_AUTH FROM ODS.T_AUTH_CHANGE ORDER BY ID_AUTH SEGMENTED BY hash( ID_AUTH) ALL NODES KSAFE;
    2021-03-16 11:31:03.263 Init Session:0x7f057086d700-a0000000022367 [EE] DataTargetProxy allocates 2 receive buffers (buffer size = 1056768)
    2021-03-16 11:31:03.265 EEThread:0x7f051126f700-a0000000022367 [EE] DataTargetProxy: Starting to receive on 2 WriteFile queues
    2021-03-16 11:31:03.266 EEThread:0x7f05cd2c9700-a0000000022367 [EE] Finalizing ROS container [0] with EE is [still running]
    2021-03-16 11:31:03.267 InternalStmt:0x7f0565c61700 [Session] InternalStatement subsession v_msedwa1_node0003-42939:0xdc51 inherited parent session v_msedwa1_node0003-42939:0xdc50
    2021-03-16 11:31:03.268 InternalStmt:0x7f0565c61700-a0000000022371 [Txn] Begin Txn: a0000000022371 'CREATE PROJECTION ODS.T_AUTH_CHANGE /
    + _oidrefs(_16,_45035996273844554,_45035996273845104) */ ( ID_AUTH) AS SELECT ID_AUTH FROM ODS.T_AUTH_CHANGE ORDER BY ID_AUTH SEGMENTED BY hash( ID_AUTH) ALL NODES KSAFE;'
    2021-03-16 11:31:03.271 InternalStmt:0x7f0565c61700-a0000000022371 [Txn] Rollback Txn: a0000000022371 'CREATE PROJECTION ODS.T_AUTH_CHANGE /*+ _oidrefs(_16,_45035996273844554,_45035996273845104) */ ( ID_AUTH) AS SELECT ID_AUTH FROM ODS.T_AUTH_CHANGE ORDER BY ID_AUTH SEGMENTED BY hash( ID_AUTH) ALL NODES KSAFE;'
    2021-03-16 11:31:03.272 InternalStmt:0x7f0565c61700 [Designer] Internal statement exec error: Permission denied for schema ODS
    2021-03-16 11:31:03.272 Init Session:0x7f057086d700-a0000000022367 [LocalPlanner] (a0000000022367) Plan type: TM_DIRECTLOAD, Plan subtype: INSERT - Created new StorageContainer 45035996274407245 (Grouped: No)
    2021-03-16 11:31:03.274 Init Session:0x7f05aafed700-a0000000022370 @v_msedwa1_node0003: 42V15/6774: Failed to create default key projections for table "ODS"."T_AUTH_CHANGE": Permission denied for schema ODS

  • SruthiASruthiA Vertica Employee Administrator
    edited March 2021

    @Michael_Pacocha I have used vertica-python and tried to run the test as a different user and it is working for me

    python /home/dbadmin/test.py
    ('Rows loaded:', [[1]])

    cat /home/dbadmin/test.py
    import vertica_python

    conn_info = {'host': '127.0.0.1',
    'port': 5433,
    'user': 'test_part',
    'password': '',
    'database': 'test',
    'connection_load_balance': True}

    with vertica_python.connect(**conn_info) as conn:
    cur = conn.cursor()
    cur.execute("SELECT NODE_NAME FROM V_MONITOR.CURRENT_SESSION")
    cur.execute("TRUNCATE TABLE ods.t_analyst")
    cur.execute("COPY ods.t_analyst(ID_CLERK,NAM_FIRST,NAM_LAST,NUM_PHONE,NAM_MID_INIT,NAME_USER,NAM_DOMAIN ,CDE_NOTIFICATION_TYPE,ADR_EMAIL,CDE_STATUS,NUM_PHONE_EXT) from local"
    " '/home/dbadmin/a.txt' delimiter ','"
    " rejected data as table t_rej;")
    print("Rows loaded:",cur.fetchall())

    dbadmin=> select * from ods.T_ANALYST ;
    ID_CLERK | NAM_FIRST | NAM_LAST | NUM_PHONE | NAM_MID_INIT | NAME_USER | NAM_DOMAIN | CDE_NOTIFICATION_TYPE | ADR_EMAIL | CDE_STATUS | NUM_PHONE_EXT
    ----------+-----------+----------+------------+--------------+-----------+------------+-----------------------+------------------+------------+---------------
    'sdr' | 'nam' | 'LAST' | '90344355' | 'sa' | 'dfdg' | 'dfddtr' | ' | 'sreredetrtyrhy' | ' | '454
    (1 row)

    dbadmin=>

  • SruthiASruthiA Vertica Employee Administrator

    @Michael_Pacocha : In my case, projection creation type is DELAYED CREATION when I run python program shared above. Are you using vertica python or a different method?

    dbadmin=> select projection_schema,projection_name,create_type from projections where projection_schema ilike '%ods%';
    projection_schema | projection_name | create_type
    -------------------+---------------------+------------------
    ods | T_ANALYST_ukcol6_b0 | DELAYED CREATION
    ods | T_ANALYST_b0 | DELAYED CREATION
    (2 rows)

    dbadmin=>

  • Nimmi_guptaNimmi_gupta - Select Field - Employee

    @Michael_Pacocha this is resolved now after giving the right permission to schema.

  • Thank you Nimmi for your assistance. Apparently, the permission requirements change around version 9.3.1. I still haven't found documentation about it but prior to that this worked without the additional permission.

  • Nimmi_guptaNimmi_gupta - Select Field - Employee

    @Michael_Pacocha what was the previous vertica version?
    As per 9.2 doc, you need to use below command to grant on schema
    GRANT CREATE, USAGE ON SCHEMA online_sales TO Joe;

Leave a Comment

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