我想使用 lambda 调用执行这个查询?"/>
我有一个查询,我想使用 lambda 调用执行这个查询?
我有一个任务,我必须从 mysql 中获取详细信息并将数据记录到 xlsx 文件中,但问题是我在 mysql 表中有超过 140 列,而且平均一个月有超过 150000 条记录。我无法下载一个月的所有数据。我正在使用节点 js 和 aws 进行 lambda 调用。
SELECT filterTable.jobSequenceNumber, sntl_invoice_details_import_Ref.invoiceSlNo AS invoiceSequenceNumber, sntl_lineItem_import_Ref.itemSlNo AS itemSequenceNumber, sntl_lineItem_import_Ref.itemSlNo AS "Item Sno", sntl_lineItem_import_Ref.partCode AS "Part Code", sntl_lineItem_import_Ref.description AS "Description", sntl_lineItem_import_Ref.concatDescription AS "Concat Description", sntl_lineItem_import_Ref.hsn AS "HSN", sntl_lineItem_import_Ref.qty AS "Qty", sntl_lineItem_import_Ref.uom AS "UOM", sntl_lineItem_import_Ref.cusQty AS "Cus Qty", sntl_lineItem_import_Ref.cusUom AS "Cus UOM", sntl_lineItem_import_Ref.unitPrice AS "Unit Price", sntl_lineItem_import_Ref.amount AS "Amount", sntl_lineItem_import_Ref.specificQty1 AS "Specific Qty 1", sntl_lineItem_import_Ref.uom1 AS "UOM1", sntl_lineItem_import_Ref.uom2 AS "UOM2", sntl_lineItem_import_Ref.generalDescription AS "Generic Description", sntl_lineItem_import_Ref.specificQty2 AS "Specific Qty 2", sntl_lineItem_import_Ref.manufacturerName AS "Manufacturer Name", sntl_lineItem_import_Ref.manufacturerType AS "Manufacturer Type", sntl_lineItem_import_Ref.manufacturerCode AS "Manufacturer Code", sntl_lineItem_import_Ref.manufacturerAddress1 AS "Manufacturer Address1", sntl_lineItem_import_Ref.manufacturerAddress2 AS "Manufacturer Address2", sntl_lineItem_import_Ref.manufacturerCity AS "Manufacturer City", sntl_lineItem_import_Ref.manufacturerSubDivision AS "Manufacturer Sub Division", sntl_lineItem_import_Ref.manufacturerPin AS "Manufacturer Pin", sntl_lineItem_import_Ref.manufacturerCountry AS "Manufacturer Country", sntl_lineItem_import_Ref.model AS "Model", sntl_lineItem_import_Ref.endUse AS "End Use", sntl_lineItem_import_Ref.brand AS "Brand", sntl_lineItem_import_Ref.countryOfOrigin AS "Country Of Origin", sntl_lineItem_import_Ref.sourceCountry AS "Source Country", sntl_lineItem_import_Ref.transitCountry AS "Transit Country", sntl_lineItem_import_Ref.bcdNtfnNo AS "BCD Notification No.", sntl_lineItem_import_Ref.bcdNtfnSlNo AS "BCD Notification Sr.No.", sntl_lineItem_import_Ref.customsAdditionalDutyNtfnNo AS "Customs Additional Duty Notification No.", sntl_lineItem_import_Ref.customsAdditionalDutyNtfnSlNo AS "Customs Additional Duty Notification Sr.No.", sntl_lineItem_import_Ref.chCessNtfnNo AS "Health Cess Notification No.", sntl_lineItem_import_Ref.chCessNtfnSlNo AS "Health Cess Notification Sr.No.", sntl_lineItem_import_Ref.caidcNtfnNo AS "CAIDC Notification No.", sntl_lineItem_import_Ref.caidcNtfnSlNo AS "CAIDC Notification Sr.No.", sntl_lineItem_import_Ref.swsNtfnNo AS "SWS Notification No.", sntl_lineItem_import_Ref.swsNtfnSlNo AS "SWS Notification Sr.No.", sntl_lineItem_import_Ref.cusEduCessNtfnNo AS "Cus.Edu. Cess Notification No.", sntl_lineItem_import_Ref.cusEduCessNtfnSlNo AS "Cus.Edu. Cess Notification Sr.No.", sntl_lineItem_import_Ref.nccdNtfnNo AS "NCCD Notification No.", sntl_lineItem_import_Ref.nccdNtfnSlNo AS "NCCD Notification Sr.No.", sntl_lineItem_import_Ref.saptaNtfnNo AS "SAPTA Notification No.", sntl_lineItem_import_Ref.saptaNtfnSlNo AS "SAPTA Notification Sr.No." FROM ((SELECT filterJobRefNo, row_number() over() as jobSequenceNumber FROM (SELECT DISTINCT sntl_job_details_import_filter.id as filterJobRefNo FROM (SELECT * FROM sntl_job_details_import WHERE isActiveJob="1" AND tenant_id="8e95996f-4de1-45b7-9e5d-d327483f239a" AND DATE(`jobCreationDate`) BETWEEN '2023-03-20' AND '2023-03-21' order by create_Date_Time desc) sntl_job_details_import_filter ) table1) filterTable INNER JOIN (SELECT * FROM sntl_invoice_details_import WHERE isActiveInvoice=1) sntl_invoice_details_import_Ref ON sntl_invoice_details_import_Ref.jobRefNo=filterTable.filterJobRefNo INNER JOIN (SELECT * FROM sntl_lineItem_import WHERE activeLineItem=1) sntl_lineItem_import_Ref ON sntl_lineItem_import_Ref.jobRefNo=filterTable.filterJobRefNo AND sntl_lineItem_import_Ref.invoiceRefNo=sntl_invoice_details_import_Ref.invoiceCreationDate) ORDER BY jobSequenceNumber, invoiceSequenceNumber, itemSequenceNumber;
const fs = require("fs");
const xlsx = require("xlsx");
module.exports.createReportFileS3 = async (report,sqlRes) => {
try {
let responseOutput = []
if(report.template?.sheetType==="multiple"){
sqlRes.forEach(item => {
if( Array.isArray(item)){
responseOutput = [...responseOutput, item]
}
})
console.log(responseOutput)
fs.writeFileSync("responseOutput.json",JSON.stringify(responseOutput));
const newWB = xlsx.utils.book_new();
for(const [index, column] of responseOutput.entries()){
const newWS = xlsx.utils.json_to_sheet(column);
xlsx.utils.book_append_sheet(newWB, newWS, `${report.tableLabelName[index]}`);
}
xlsx.writeFile(newWB, `/tmp/${report.template.id}.xlsx`); //construct xlsx file with fetched data in local
}else{
console.log('report..',report);
let responseOutput = []
sqlRes.forEach(item => {
if( Array.isArray(item)){
responseOutput = [...item]
}
})
const newWB = xlsx.utils.book_new();
const newWS = xlsx.utils.json_to_sheet(responseOutput);
xlsx.utils.book_append_sheet(newWB, newWS, "USERS_LIST");
xlsx.writeFile(newWB, `/tmp/${report.template.id}.xlsx`); //construct xlsx file with fetched data in local
}
const reportCreationDate = report.reportCreationDate//Date.now()
const readStream = fs.createReadStream(`/tmp/${report.template.id}.xlsx`) // a ReadStream
let params = {
Bucket: config.S3_BUCKET.REPORTS_BUCKET_NAME, //S3 bucket name
Key: `sntl_reports/${report.template.id}/${report.template.templateName}${reportCreationDate}.xlsx`, //file storage path and name
Body: readStream,
ACL:"public-read", //acess control list
ContentType:'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
};
console.log(params)
const command = new PutObjectCommand(params)
const data = await client.send(command);
console.log("data",data);
fs.unlink(`/tmp/${report.template.id}.xlsx`, (err) => { //delete xlsx file in local
if (err){
throw err;
console.log("error delete file");
}
});
return data
} catch (error) {
console.error("error in upload xlsx file in s3 bucket", error, report, sqlRes);
}
}
当我尝试在 lambda 中传递查询时,它给出了超时异常,即使我将 AWS lambda 时间增加到最大限制。
回答如下:更多推荐
我有一个查询,我想使用 lambda 调用执行这个查询?
发布评论