多个云sql表导出为csv

Question

是否有一种方法可以通过从云SQL发出特定查询来将多个SQL表导出为csv。

下面是我目前拥有的代码。当我背靠背调用多个表的exportTables时，我看到一个409错误。这可能是因为云sql实例正忙于导出，并且不允许后续的导出请求。

我怎样才能让它发挥作用？这里的理想解决方案是什么。

private void exportTables(String table_name, String query)
throws IOException, InterruptedException {
HttpClient httpclient = new HttpClient();
PostMethod httppost =
new PostMethod(
"https://www.googleapis.com/sql/v1beta4/projects/"
+ "abc"
+ "/instances/"
+ "zxy"
+ "/export");
String destination_bucket =
String.join(
"/",
"gs://" + "test",
table_name,
DateTimeUtil.getCurrentDate() + ".csv");
GoogleCredentials credentials =
GoogleCredentials.getApplicationDefault().createScoped(SQLAdminScopes.all());
AccessToken access_token = credentials.refreshAccessToken();
access_token.getTokenValue();
httppost.addRequestHeader("Content-Type", "application/json");
httppost.addRequestHeader("Authorization", "Bearer " + access_token.getTokenValue());
String request =
"{"
+ "  "exportContext": {"
+ "    "fileType": "CSV","
+ "    "uri":""
+ destination_bucket
+ "","
+ "    "databases": [""
+ "xyz"
+ ""],"
+ "    "csvExportOptions": {"
+ "      "selectQuery": ""
+ query
+ """
+ "    }n"
+ "  }"
+ "}";
httppost.setRequestEntity(new StringRequestEntity(request, "application/json", "UTF-8"));
httpclient.executeMethod(httppost);
if (httppost.getStatusCode() > 200) {
String response = new String(httppost.getResponseBody(), StandardCharsets.UTF_8);
if (httppost.getStatusCode() != 409) {
throw new RuntimeException(
"Exception occurred while exporting the table: " + table_name + " Error " + response);
} else {
throw new IOException("SQL instance seems to be busy at the moment. Please retry");
}
}
httppost.releaseConnection();
logger.info("Finished exporting table {} to {}", table_name, destination_bucket);

}

Answer 1

我没有直接在Cloud SQL上解决问题的建议，但由于有了一个新工具，可以按顺序执行导出的解决方案：工作流

• 在JSON中定义所需的数据格式，以定义ONE导出
• 然后为工作流提供一系列配置
• 在该工作流程中，
  • 在配置数组上进行循环
  • 对Cloud SQL执行API调用，以在每个配置上生成导出
  • 获取API调用的答案，您有jobId
  • 睡一会儿
  • 检查导出是否已结束(使用jobId(。
    • 如果没有，请睡眠并再次检查
    • 如果是，则循环(从而开始下一次导出(

它是无服务器的，免费层使这个用例免费。