The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
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"']
0
Comments
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.
@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
@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=>
@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=>
@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.
@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;