Snowflake: COPY Command to upload files into S3 with only Header and with expected Header Names

0
votes

I am trying to resolve 2 issues

Issue 1. The Copy command created HEADER with all columns as CAPS, but I need to generate header with what Application is needed

For example

Expected Header : 

AcctID,qute_c,AcctNumber,AcctName,MRR

Issue 2: The process uploads the file into s3 from the Snowflake query. When there are rows it uploads the file which works fine but when there are no rows returned, no file is uploaded which is working as expected

But still, I want to upload the empty file with HEADER only when there are no rows returned

   COPY INTO '@stage/path/test.csv'
    FROM (SELECT A.AcctID,B.qute_c,A.AcctNumber,A.AcctName,B.MRR FROM TABLE A , TABLE B  WHERE A.AcctID=B.AcctID AND A.C_DATE = CURRENT_DATE())
    MAX_FILE_SIZE = 5368706371
    FILE_FORMAT = (FORMAT_NAME = 'CSV_TEXTQUOTE_NOZIP')
    OVERWRITE=TRUE
    SINGLE=TRUE
    HEADER = TRUE;
2
snowflake-cloud-data-platformsnowflake-schema
Please refer to the usage notes for COPY INTO <location> docs.snowflake.com/en/sql-reference/sql/…... "If the source table contains 0 rows, then the COPY operation does not unload a data file." – Greg Pavlik
Yes I know that , we need to send empty file coz the upstream application needs file all the time – Kar
One way would be to have a separate COPY INTO statement with a separate file format in the case where there are no rows. You can have a file format that specifies not to print the header, but send the "header" as the values of the columns in a single row. You'd define the table as all varchar columns and have a single row with the names of the columns. When you need to send an empty resultset file, you would do a COPY INTO the stage from that table. It's a workaround at best, but it will work. – Greg Pavlik

2 Answers

1
votes

For question 1: SELECT AcctID AS "Account Id" , AcctNumber AS "Account Number" ....

1
votes

@Diamantis , Yes I did the same way by including the UNION query which resolved me both the issues 

COPY INTO '@stage/path/test.csv'
    FROM (
SELECT 'AcctID' as AcctID, 'qute_c' as qute_c ,'AcctNumber' as AcctNumber ,'AcctName' as AcctName,'MRR' as MRR
Union
SELECT A.AcctID,B.qute_c,A.AcctNumber,A.AcctName,B.MRR FROM TABLE A , TABLE B  WHERE A.AcctID=B.AcctID AND A.C_DATE = CURRENT_DATE())
    MAX_FILE_SIZE = 5368706371
    FILE_FORMAT = (FORMAT_NAME = 'CSV_TEXTQUOTE_NOZIP')
    OVERWRITE=TRUE
    SINGLE=TRUE
    HEADER = FALSE;