How to write FILE_FORMAT in Snowflake to Java code?

Question

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?

Simeon Pilgrim · Accepted Answer · 2021-11-07 20:14:47Z

2

You are wanting an EXTERNAL STAGE

Which you would create like:

CREATE STAGE awesome_stange_name
    URL = 's3://snowflake'
    FILE_FORMAT = test.public.csv_test

and then you can copy to it:

COPY INTO @awesome_stange_name
  FROM test.public.user_table_temp;

This means if the user doing the copy has permission to use the stage, then they can, without need to have access to the security tokens needed to working with that secure location.

answered Nov 7, 2021 at 20:14

Simeon Pilgrim

26.7k3 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user9347049 Over a year ago

But with this I still need to set FILE_FORMAT = test.public.csv_test? So, I should still set csv_test inside Snowflake?

Simeon Pilgrim Over a year ago

again the "file format" is like the "stage" it is a thing you need to refer to, but you don't need to spell the whole thing out every time, thus it still add a large amount of value, imho

Greg Pavlik · Accepted Answer · 2021-11-08 13:54:11Z

0

Is there any way to write this as Java code?

In Snowflake, creating and altering file formats is done through SQL. You can simply execute a SQL statement through a JDBC connection in Java.

Just change your alter to a create if the file format is not already created:

CREATE FILE FORMAT "TEST"."PUBLIC".CSV_TEST 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');

Assign that to a String variable like sql and just run it like any other statement using JDBC:

ResultSet rs = stmt.executeQuery(sql);

You can then have a line rs.next(); and read from the first ordinal column or the column name status (in lowercase) to get the success/failure message.

answered Nov 8, 2021 at 13:54

Greg Pavlik

11.3k2 gold badges14 silver badges31 bronze badges

1 Comment

user9347049 Over a year ago

Hi, thank you for your answer. I think this is exactly what I am looking for. I updated my question with query that I am using. Can you please give example of how would u use this ResultSet in my query? Thank you!

user9347049 · Accepted Answer · 2021-11-11 12:23:10Z

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.

Collectives™ on Stack Overflow

How to write FILE_FORMAT in Snowflake to Java code?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related