I am trying to execute COPY INTO statement in Java code like this:
copy into s3://snowflake from "TEST"."PUBLIC"."USER_TABLE_TEMP" storage_integration = s3_int file_format = CSV_TEST;
And it works fine.
Is there any way to add this file_format
in Java code, so there is no need to set it up in Snowflake?
For example, SQL code of file_format
that I have set in Snowflake is
ALTER FILE FORMAT "TEST"."PUBLIC".CSV_TEST SET COMPRESSION = 'NONE' FIELD_DELIMITER = ',' RECORD_DELIMITER = 'n' SKIP_HEADER = 0 FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE' TRIM_SPACE = TRUE ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = '134' DATE_FORMAT = 'AUTO' TIMESTAMP_FORMAT = 'AUTO' NULL_IF = ('\N');
Is there any way to write this as Java code?
UPDATE
Here is the code where I am using copy into statement:
String q = "COPY INTO s3://snowflake/"+ userId +" from "EPICEROS"."PUBLIC"."USER_TABLE_TEMP" storage_integration = s3_int file_format = CSV_TEST OVERWRITE=TRUE;"; jdbcTemplatePerBrand.get(brand).query(q, s -> {});
So how can I apply like file_format created on execution of query?
Advertisement
Answer
This is the solution that I found for my question.
To be able to write file_format
from code and not create one in Snowflake I did like this:
copy into s3://snowflake from "TEST"."PUBLIC"."USER_TABLE_TEMP" storage_integration = s3_int OVERWRITE = TRUE file_format = (type = csv compression = 'none' file_extension ='csv' FIELD_OPTIONALLY_ENCLOSED_BY = '"' NULL_IF = () single = true max_file_size = 4900000000;
I also added OVERWRITE = TRUE
which means that if my file exists alredy in S3, overwrite it with new one.
single = true
and max_file_size = 4900000000
means that I am allowing to export files big to 5 GB. If I haven’t added these two, my one big file would be separated in few smaller .csv
files, which I did not want.