Skip to content
Advertisement

How to write FILE_FORMAT in Snowflake to Java code?

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement