如何使用Java為S3物件創建自定義檔案名

我正在使用 Spring Boot、Snowflake 和 AWS S3。

我有查詢兩個表并獲取結果的 SQL 查詢。結果我必須像CSV檔案一樣寫入S3并獲取URL以供下載。

我通過創建臨時表并在將資料復制到 S3 后將其洗掉來做到這一點。

這是我的代碼：

@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);
    }

寫入 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;
    }

生成 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;
}

資料由userId我發送的查詢。一切正常，但我每次都生成同名檔案。如果我不洗掉 S3 中的現有檔案，我將無法上傳新檔案。

我應該能夠為不同的 userId 上傳不同的檔案。

我怎樣才能做到這一點？

如何為 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?

uj5u.com熱心網友回復：

您可以在 COPY 查詢中添加帶有 userId 的自定義物件鍵：

   @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 -> {});

標籤：java amazon-web-services spring-boot amazon-s3 snowflake-cloud-data-platform

上一篇：“s3:PutObject*”和“s3:PutObject”有什么區別？

下一篇：基于條件階段變數字串的Terraform資源