如何使用Java为S3对象创建自定义文件名

Question

我使用Spring Boot, Snowflake和AWS S3。

我有查询两个表并获得结果的SQL查询。结果我必须写S3像CSV文件，并获得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 alln" +
"          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;
}

数据由输入的userIdI查询。一切都很好，但我产生的每一次文件的名称相同。如果我不删除S3中的现有文件，我就无法上传新的文件。

我应该能够为不同的userId上传不同的文件。

我该怎么做?

如何从S3中创建的文件中给出不同的名称?

我在文档https://docs.aws.amazon.com/AmazonS3/latest/userguide/object-keys.html中看到过这个，但是我不知道在代码中应用的最好方法。

是否有办法我可以添加userId作为前缀文件名?

Answer 1

您可以在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 -> {});