Issue with 'COPY' statement with SQL MAGIC inside a formatted string

[pn-alex]

Hello,

We encounter an unexpected behavior when using the 'COPY' statement inside a formatted string.

Database:

For our example, we use a csv file structured as follows:

id,produit
1,A
2,B
3,C

Code :

schema = '***'
table = 'test'
file = 'test.csv'

sql = f"""COPY {schema}.{table} 
FROM LOCAL '{file}' 
PARSER public.fcsvparser(type='rfc4180', header=True, reject_on_materialized_type_error=True) 
REJECTMAX 1 REJECTED DATA 'Files/z-err.txt' EXCEPTIONS 'Files/z-err_log.txt';"""

%sql -c :sql

This code generates this error:

PermissionDenied: Severity: ERROR, Message: Permission denied for storage location [Files/z-err_log.txt], Sqlstate: 42501, Routine: report_no_priv, File: /data/jenkins/workspace/RE-ReleaseBuilds/RE-Knuckleboom_2/server/vertica/Commands/GrantRevoke.cpp, Line: 525, Error Code: 4368, SQL: "COPY ***.test FROM STDIN PARSER public.fcsvparser(type='rfc4180', header=True, reject_on_materialized_type_error=True) REJECTMAX 1 REJECTED DATA 'Files/z-err.txt' EXCEPTIONS 'Files/z-err_log.txt'"

Temporary Solution:

For this code to work, it would appear that a line break is required at the beginning of the formatted string containing the SQL command :

sql = f"""
COPY {schema}.{table} 
FROM LOCAL '{file}' 
PARSER public.fcsvparser(type='rfc4180', header=True, reject_on_materialized_type_error=True) 
REJECTMAX 1 REJECTED DATA 'Files/z-err.txt' EXCEPTIONS 'Files/z-err_log.txt';"""

Output:

Would it be possible for you to look into this issue to fix it or make the error message more explicit to avoid similar errors in the future?

Thank you for your attention to this matter !

Best regards,
Alexandre Peter

[oualib]

Thanks for reporting this issue, I'll do some tests and come back to you.

[oualib]

I reproduced your example, and it is working for me.

It seems the error is server side. Besides, it seems like a Permission issue and not a code issue.

I recommend you to check your parameters with the server team.

[pn-alex]

Hello,

Thanks for your answer. But we've realized that there's a noticeable difference in the queries received by the database when there's a line break or not:

• Without a line break :
  COPY ***.TEST_CSV FROM STDIN PARSER public.fcsvparser(type='rfc4180', header=True, reject_on_materialized_type_error=True) REJECTMAX 10 REJECTED DATA '/home/jovyan/z-err.txt' EXCEPTIONS '/home/jovyan/z-err_log.txt'

• With a line break :
  COPY ***.TEST_CSV FROM LOCAL 'test_sqlmagic.csv' PARSER public.fcsvparser(type='rfc4180', header=True, reject_on_materialized_type_error=True) REJECTMAX 10 REJECTED DATA '/home/jovyan/z-err.txt' EXCEPTIONS '/home/jovyan/z-err_log.txt'

When we make a FROM LOCAL request, it writes the rejects to the client, but when we make a FROM STDIN request, it writes them to the cluster, which explains why we don't have the permission.

We think that it's not normal to convert the request to FROM STDIN. Si it would be a code issue.

Thank you for your time !

Best regards,
Alexandre Peter

[oualib]

@pn-alex, Indeed, COPY LOCAL was recently supported by the client, and the transformation was necessary in the past. I am adding an option to the SQL magic to deactivate the auto-replacement.

[oualib]

Now, there is an stdin option that allows you to deactivate the auto parsing and replacing: #1326 , it should close your issue.
Usage: %sql ... -stdin False.