My goal is to retrieve data from a Trino database. Upon sending my initial query to the database, I receive a NextURI. Subsequently, in a while loop, I check the NextURI to obtain portions of the data until the Trino connection completes sending the entire dataset.
The problem arises when I receive no data and am stuck in an infinite loop of receiving NextURIs.
The code snippet below outlines this process, mentioning that it commences with acquiring an authentication token for Trino.
app.get("/getOpenskyData", async (req: Request, res: Response) => {
var startTime = new Date().getTime();
console.log("Server | getHistoricalADSBData | Called");
try {
const startTimeEPOCHMS = Number(req.query.startTimeEPOCHMS);
const endTimeEPOCHMS = Number(req.query.endTimeEPOCHMS);
interface TokenPayload {
access_token: string;
exp: number;
}
// Get the token from OpenSky Network
const result = await axios.post(
"https://auth.opensky-network.org/auth/realms/opensky-network/protocol/openid-connect/token",
new URLSearchParams({
client_id: "trino-client",
grant_type: "password",
username: process.env.OPENSKY_USERNAME + "",
password: process.env.OPENSKY_PASSWORD + ""
}).toString(),
{
headers: {
'Content-Type': 'application/x-www-form-urlencoded'
}
}
);
const payload = result.data;
const decoded: TokenPayload = jwtDecode(payload.access_token);
// Ensure the token has an exp property
if (!decoded.exp) {
throw new Error("Token does not have an expiration time");
}
console.log("Server | getHistoricalADSBData | Got Token");
const queryString = `SELECT * FROM minio.osky.state_vectors_data4 WHERE time BETWEEN ${startTimeEPOCHMS / 1000} AND ${endTimeEPOCHMS / 1000}`;
// Execute the query on Trino
const response = await axios.post(
'https://trino.opensky-network.org/v1/statement',
queryString,
{
headers: {
Authorization: `Bearer ${payload.access_token}`,
'Content-Type': 'text/plain',
'X-Trino-Buffer': 'true',
},
}
);
if (response && response.status === 200) {
const body = response.data;
const results: any[] = [];
let nextUri = body.nextUri; // Initialize nextUri
// Loop to handle pagination
while (nextUri) {
const fetchResponse = await axios.get(nextUri, {
headers: {
Authorization: `Bearer ${payload.access_token}`,
},
});
if (fetchResponse && fetchResponse.status === 200) {
const fetchBody = fetchResponse.data;
if (fetchBody.data && fetchBody.data.length > 0) {
results.push(...fetchBody.columns); // Push data to results array
}
console.log(fetchBody.columns)
nextUri = fetchBody.nextUri; // Update nextUri
console.log(fetchBody.nextUri)
} else {
throw new Error('Unokay response whilst fetching data from OpenSky');
}
}
console.log("Server | getHistoricalADSBData | Results:", results);
res.status(200).json(results);
} else {
throw new Error('Unokay response whilst querying OpenSky');
}
console.log("Server | getHistoricalADSBData | Took " + (new Date().getTime() - startTime) + " ms");
} catch (error) {
console.error('Server | getHistoricalADSBData | Error of: ', (error as unknown as Error).message);
return res.status(500).json({ message: 'Internal server error while fetching historical ADSB.' });
}
});
Upon printing the fetchResponse.columns field, the output shows an array missing crucial data, only containing a single ICAO24 value. The expected output should consist of hundreds of thousands of data pieces, given that it should retrieve global ADSB data for one second.
[
{
name: 'time',
type: 'integer',
typeSignature: { rawType: 'integer', arguments: [] }
},
{
name: 'icao24',
type: 'varchar',
typeSignature: { rawType: 'varchar', arguments: [Array] }
},
...
]
The NextURI appears as:
https://trino.opensky-network.org/v1/statement/executing/20240619_210740_88003_jtdt5/y2e8bb6c86212fff8d50ab161a4300917292bc401/278
After 278 NextURIs (equivalent to 278 seconds of processing), I am still unable to retrieve any data.
I have searched but found no relevant documentation on how Trino formats data when sending it or how to parse this data in JavaScript.
The query should not take 278 seconds to complete or return no data.
How can I correctly process Trino data in Typescript?
Edit: Here is the JSON.stringify of the response:
Fetched data: {
...
<details of the fetched data>
...
}
After running for 20 minutes, no output rows are retrieved.