How to write FILE_FORMAT in Snowflake to Java code?-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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.