How to connect AWS Athena DBs to n8n

Hi guys! I’m in a project that i need consume some data from tables in AWS Athena and create a workflow on n8n.
Is there any way to make this connection? Any alternative?

I appreciate any and all help!

Hey @gfrts,

Welcome to the community :raised_hands:

I have not used Athena before but assuming it has an API you might be able to use that with the HTTP Request node, Does Athena have anything clever in it like the ability to connect using MySQL clients? That could be another possible option if it is supported.

1 Like

I managed to install the SDK using the command node with the following code: cd /root/.n8n && npm install -g aws-sdk

To do a get or a post, I run a code node with the following code:

`NODE_FUNCTION_ALLOW_EXTERNAL= ‘aws-sdk’
var query = $node.Webhook.json.body.query

const AWS = require(‘aws-sdk’);

AWS.config.update({
accessKeyId: ‘KEY_ID’,
secretAccessKey: ‘SECRET_KEY’,
region: ‘us-east-2’
});

const athena = new AWS.Athena();

const params = {
QueryString: query
};

function waitForQueryCompletion(queryExecutionId) {
return new Promise((resolve, reject) => {
const checkStatus = setInterval(() => {
athena.getQueryExecution({ QueryExecutionId: queryExecutionId }, (err, data) => {
if (err) {
clearInterval(checkStatus);
reject(err);
} else {
const status = data.QueryExecution.Status.State;
if (status === ‘SUCCEEDED’) {
clearInterval(checkStatus);
resolve();
} else if (status === ‘FAILED’ || status === ‘CANCELLED’) {
clearInterval(checkStatus);
reject(new Error(Falha na execução da consulta. Status: ${status}));
}
}
});
}, 100); // Verificar a cada 2 segundos
});
}

async function runQuery() {
const queryExecution = await athena.startQueryExecution(params).promise();
const queryExecutionId = queryExecution.QueryExecutionId;

try {
await waitForQueryCompletion(queryExecutionId);
console.log(‘Execução da consulta concluída.’);

// Obter os resultados da consulta
const getResultParams = {
  QueryExecutionId: queryExecutionId
};

const queryResults = await athena.getQueryResults(getResultParams).promise();

// Preparar o array de objetos de saída
const arrayDeObjetos = queryResults.ResultSet.Rows.map(row => {
  const objeto = {};
  queryResults.ResultSet.ResultSetMetadata.ColumnInfo.forEach((coluna, index) => {
    objeto[coluna.Name] = row.Data[index].VarCharValue;
  });
  return objeto;
});

return arrayDeObjetos; // Retornar o array de objetos

} catch (error) {
console.error(‘Erro:’, error);
return ; // Retornar um array vazio em caso de erro
}
}

const saída = await runQuery();

return saída; // Isso será retornado como a saída do seu código no n8n`