Copy with Delayed_Creation Failed

Michael_Pacocha

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"']

SruthiA

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.

Michael_Pacocha

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

Jim_Knicely

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

Michael_Pacocha

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
);

Michael_Pacocha

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.

Michael_Pacocha

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

SruthiA

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

SruthiA

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

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

Michael_Pacocha

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_gupta

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