How to create custom file names for S3 objects using Java-CodePudding

I am using Spring Boot, Snowflake and AWS S3.

I have SQL query that queries two tables and gets the result. That result I have to write to S3 like CSV file and get URL for download in return.

I am doing that by creating temporary table and deleting it after data is copied to S3.

Here is code I have:

@Override
    public void getUserTest(String userId) {

        String q = "CREATE TEMPORARY TABLE \"TEST\".\"PUBLIC\".\"USER_TABLE_TEMP\" AS SELECT \"ID\", \"FIRST_NAME\", \"LAST_NAME\" from \"TEST\".\"PUBLIC\".\"USER_TABLE\"\n"  
                "          where \"ID\" = ?\n"  
                "          union all\n"  
                "          select \"ID\",\"ACCOUNT_NAME\", \"ACCOUNT_NUMBER\" from \"TEST\".\"PUBLIC\".\"ACCOUNT_DATA\"\n"  
                "          where \"ID\" = ?";
        jdbcTemplate.query(q, s -> {}, userId, userId);
    }

Method that writes to S3.

   @Override
    public URL writeToS3() {

        String q = "copy into s3://snowflake171 from \"TEST\".\"PUBLIC\".\"USER_TABLE_TEMP\" storage_integration = s3_int file_format = CSV_TEST;\n";
        jdbcTemplate.query(q, s -> {});

        URL url = generateURL();

        String dropTable = "drop table if exists \"TEST\".\"PUBLIC\".\"USER_TABLE_TEMP\"";
        jdbcTemplate.query(dropTable, s -> {});
        return url;
    }

Method that generated URL:

public URL generateURL() {

    try {
        BasicAWSCredentials awsCreds = new BasicAWSCredentials(accessKey, secretKey);
        final AmazonS3 s3Client = AmazonS3ClientBuilder.standard().withCredentials(new
                AWSStaticCredentialsProvider(awsCreds)).withRegion(clientRegion).build();

        // Set the presigned URL to expire after 2h.
        java.util.Date expiration = new java.util.Date();
        long expTimeMillis = Instant.now().toEpochMilli();
        expTimeMillis  = 1000 * 60 * 120;
        expiration.setTime(expTimeMillis);

        // Generate the presigned URL.
        System.out.println("Generating pre-signed URL.");
        GeneratePresignedUrlRequest generatePresignedUrlRequest =
                new GeneratePresignedUrlRequest(bucket, objectKey)
                        .withMethod(HttpMethod.GET)
                        .withExpiration(expiration);
        URL url = s3Client.generatePresignedUrl(generatePresignedUrlRequest);
        System.out.println("Pre-Signed URL: "   url.toString());
        return url;

    } catch (AmazonServiceException e) {
        // The call was transmitted successfully, but Amazon S3 couldn't process
        // it, so it returned an error response.
        e.printStackTrace();
    } catch (SdkClientException e) {
        // Amazon S3 couldn't be contacted for a response, or the client
        // couldn't parse the response from Amazon S3.
        e.printStackTrace();
    }
    return null;
}

Data is queried by userId I sent in. Everything works fine, but I am generating every time file with same name. And if I dont delete existing file in S3 I am not able to upload new one.

I should be able to upload different files for different userId.

How can I do this?

How can I give different names from files created in S3?

I have seen this in docs https://docs.aws.amazon.com/AmazonS3/latest/userguide/object-keys.html but I don't know the best way to apply in code.

Is there a way I can add userId as prefix to filename?

CodePudding user response：

You can add a custom object key with userId in your COPY query :

   @Override
    public URL writeToS3(userId) {

        String q = "copy into s3://snowflake171/"   userId   " from \"TEST\".\"PUBLIC\".\"USER_TABLE_TEMP\" storage_integration = s3_int file_format = CSV_TEST;\n";
        jdbcTemplate.query(q, s -> {});