Executing long-running queries using the SqlBackground API

Luminesce has a SqlBackground API you can use to execute a long-running Luminesce SQL query and monitor its progress. You can use this information to handle errors and timeouts.

To use the SqlBackground API, it’s important to perform all of the following steps:

Step 1: Start your query

  1. Obtain an API access token.

  2. Call the the StartQuery API for your LUSID domain, passing in your API token and:

    • A queryName you can view in logs and when using Sys.Logs.HcQueryStart.

    • A timeoutSeconds value specifying the maximum time the query can run for.

    • A keepForSeconds value specifying how long the result is stored for.

    • Any scalarParameters as key-value pairs to use in the execution.

    • Your Luminesce SQL query as the request body.

For example, to start a query that upserts a large quantity of transactions to multiple portfolios:

curl -X PUT "https://<your-domain>.lusid.com/honeycomb/api/SqlBackground"
  -H "Authorization: Bearer <your-API-access-token>"
  -d "
@txns = values
('US-Equities', 'TXN-123', 'Buy', 100, 250, 25000, 'AMGN', 'LUID_00003DFS', 750),
('Global-Portfolio', 'TXN-124', 'StockIn', 100, 200, 20000, 'AAPL', 'LUID_00003DG1', 600),
('Global-Portfolio', 'TXN-125', 'Sell', 100, 800, 80000, 'INTC', 'LUID_00003DFJ', 2400);

@table_of_data = select 'Finbourne-Examples' as PortfolioScope, column1 as PortfolioCode, column2 as TxnId, column3 as Type,
#2024-09-18# as TransactionDate, #2024-09-19# as SettlementDate, column4 as Units, column5 as TradePrice, column6 as TotalConsideration,
'GBP' as SettlementCurrency, column7 as Ticker, column8 as ClientInternal, column9 as BrokerCommission
from @txns;

select * from Lusid.Portfolio.Txn.Writer where ToWrite = @table_of_data;"

Part of a successful response is as follows, meaning the query was successfully parsed. Note the executionId which you must use to monitor the query going forward:

 {
  "executionId": "f2c1fb86-cb4e-4d4c-a555-3da4cef751e0",
  "progress": {
    "relation": "Progress",
    "href": "/api/SqlBackground/f2c1fb86-cb4e-4d4c-a555-3da4cef751e0",
    "description": "Gets the current state and user-presentable progress information",
    "method": "GET"
  },
  "cancel": {
    "relation": "Cancel",
    "href": "/api/SqlBackground/f2c1fb86-cb4e-4d4c-a555-3da4cef751e0",
    "description": "Cancels (if running) or clears the data from (if completed) a previously started query",
    "method": "DELETE"
  },
  "fetchJson": {
    "relation": "FetchJson",
    "href": "/api/SqlBackground/f2c1fb86-cb4e-4d4c-a555-3da4cef751e0/json",
    "description": "Gets the resulting data (or error information on failure) in Json format",
    "method": "GET"
  },
  ...
}

Step 2: Check query execution progress

After you successfully start the query execution, you can call the GetProgressOf API, passing in the executionId to the URL, to check on its progress.

We strongly recommend you use information from the response for monitoring and handling the execution of your long-running queries. The GetProgressOf API response is invaluable for investigating issues if your queries do not execute as expected.

For example, to check on the progress of the query sent in step 1:

 curl -X GET "https://<your-domain>.lusid.com/honeycomb/api/SqlBackground/f2c1fb86-cb4e-4d4c-a555-3da4cef751e0"
  -H "Authorization: Bearer <your-API-access-token>"

Part of a successful response is as follows:

     {
  "hasData": true,
  "rowCount": 3,
  "status": "RanToCompletion",
  "state": "Serialized",
  "progress": "11:57:21.929 >> Progress / QueryOrchestrator >> @txns [1] -> calculating ...\n11:57:21.957 >> Progress / QueryOrchestrator >> @txns -> 3 rows in [0.6738ms + 27ms + 0.0143ms]\n11:57:21.957 >> Progress / QueryOrchestrator >> @table_of_data [2] -> calculating ...\n11:57:21.981 >> Progress / QueryOrchestrator >> @table_of_data -> 3 rows in [0.6243ms + 23ms + 0.0137ms]\n11:57:22.002 >> Progress / QueryOrchestrator >> Lusid.Portfolio.Txn.Writer [PortfolioScope, PortfolioCode, DisplayName, Txn...]\n11:57:23.144 >> Warning / ToWrite >> The following columns are understood and passed in: PortfolioScope, PortfolioCode, TxnId, Type, TransactionDate, SettlementDate, Units, TradePrice, TotalConsideration, SettlementCurrency, Ticker, ClientInternal\nThe following columns are understood yet not passed in: TradePriceType, TradeCurrency, ExchangeRate, CounterpartyId, Source, BondInterest, TradeToPortfolioRate, OrderScope, OrderCode, AllocationScope, AllocationCode, WriteAction, LusidInstrumentId, Isin, Sedol, Cusip, Figi, CompositeFigi, ShareClassFigi, Wertpapier, RIC, QuotePermId, EdiKey\nThe following columns are passed in yet are not understood (so skipped): BrokerCommission\n11:57:25.444 >> Progress / Lusid.Portfolio.Txn.Writer >> 1 transactions written so far\n11:57:26.059 >> Progress / QueryOrchestrator >> Lusid.Portfolio.Txn.Writer -> 3 rows in [21ms + 4.052s + 3.4746ms]\n11:57:26.089 >> Progress / QueryOrchestrator >> \n@txns = values\n('US-Equities', 'TXN-123', 'Buy', 100, 250, 25000, 'AMGN', 'LUID_00003DFS', 750),\n('Global-Portfolio', 'TXN-124', 'StockIn', 100, 200, 20000, 'AAPL', 'LUID_00003DG1', 600),\n('Global-Portfolio', 'TXN-125', 'Sell', 100, 800, 80000, 'INTC', 'LUID_00003DFJ', 2400);\n\n@table_of_data = select 'Finbourne-Examples' as PortfolioScope, column1 as PortfolioCode, column2 as TxnId, column3 as Type,\n#2024-09-18# as TransactionDate, #2024-09-19# as SettlementDate, column4 as Units, column5 as TradePrice, column6 as TotalConsideration,\n'GBP' as SettlementCurrency, column7 as Ticker, column8 as ClientInternal, column9 as BrokerCommission\nfrom @txns;\n\nselect * from Lusid.Portfolio.Txn.Writer where ToWrite = @table_of_data;\n\nRows         : 3\nData Volume  : 798 B\n\nPrep         : 208.3708 ms\nProviders    : 4131.1449 ms\nMerge/Sql    : 0.3232 ms\nFillTable    : 27.5036 ms\n(total)      : 4367.3425 ms\n\nSession Id   : c649a7d7-3b05-4d76-862b-738573387a5e\nExecution Id : 8b61dae9-e29e-4793-b052-cacd86624690\nExt. Ex. Id  : 0HN6PD7QFTS9M:00000007\nScalar Param : null\nClient       : <your-domain>\nClient Id    : 0oa7draltjzKvjCz92p7\nUser Id      : 00uji4twb4jDcHGjN2p7\nUser Type    : Personal\nApi Version  : 1.16.625.0\n\nDependency & Execution details:\n\n.                          |                                                     4.334s|\n@anonymous_0 => @txns      |  |                                                        | \n@txns => @table_of_data    |  |                                                        | -> 3 row(s)\n@table_of_data             |  ||                                                       | -> 3 row(s)\nLusid.Portfolio.Txn.Writer |  ||>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>| -> 3 row(s)\nQuery Coordinator          |**|--------------------------------------------------------| -> 3 row(s)\n\n",
  "feedback": [
    {
      "when": "2024-09-20T11:57:21.9296540Z",
      "sessionId": "c649a7d7-3b05-4d76-862b-738573387a5e",
      "executionId": "8b61dae9-e29e-4793-b052-cacd86624690",
      "level": "Progress",
      "sender": "QueryOrchestrator",
      "messageTemplate": "{Variable} [{Id}] -> calculating ...",
      "propertyValues": [
        "@txns",
        1
      ],
      "message": "@txns [1] -> calculating ..."
    },
   ...
  ],
  "query": "@txns = values\n('US-Equities', 'TXN-123', 'Buy', 100, 250, 25000, 'AMGN', 'LUID_00003DFS', 750),\n('Global-Portfolio', 'TXN-124', 'StockIn', 100, 200, 20000, 'AAPL', 'LUID_00003DG1', 600),\n('Global-Portfolio', 'TXN-125', 'Sell', 100, 800, 80000, 'INTC', 'LUID_00003DFJ', 2400);\n\n@table_of_data = select 'Finbourne-Examples' as PortfolioScope, column1 as PortfolioCode, column2 as TxnId, column3 as Type,\n#2024-09-18# as TransactionDate, #2024-09-19# as SettlementDate, column4 as Units, column5 as TradePrice, column6 as TotalConsideration,\n'GBP' as SettlementCurrency, column7 as Ticker, column8 as ClientInternal, column9 as BrokerCommission\nfrom @txns;\n\nselect * from Lusid.Portfolio.Txn.Writer where ToWrite = @table_of_data;",
  "columnsAvailable": [
    {
      "isPrimaryKey": false,
      "isMain": false,
      "isRequiredByProvider": false,
      "name": "PortfolioScope",
      "type": "Text",
      "displayName": "Portfolio Scope",
      "conditionUsage": "None"
    },
    ...
  ]
}

The state and status fields in the response can be particularly useful:

State

The state field in the response tells you the current state of the Luminesce SQL query. The following list provides more information on each state:

  • New: The query is not yet fully initialised.

  • Running: Currently executing the query.

  • Errored: Error when executing query.

  • Cancelled: Cancelled when executing the query.

  • Executed: Query successfully executed.

  • ExecutedNoSerializationRequired: Query successfully executed and no serialization was required.

  • Serialized: Query executed and written to cloud storage cache.

  • SerializationFailed: Query executed but failed to be written to cloud storage cache.

  • AttemptingToDeserialize: Query executed, successfully cached and now being read from cloud storage cache.

  • Loaded: Query executed, successfully cached and now being loaded.

  • Cleared: Query passed its expiry limit and has been deleted.

  • Disposed: Query no longer available from storage.

  • OwnerTerminated: AWS node was forced to terminate the query execution. Another pod may pick this up.

Status

The status field in the response tells you to some extent if the query has succeeded or failed, though note the state field (above) is more informative. You could choose to handle WaitingForActivation, Faulted and Cancelled in a loop to achieve the desired behaviour.

The following list provides more information on each status:

  • Created: Query execution is in progress.

  • WaitingToRun: Query execution is in progress.

  • Running: Query execution is in progress.

  • WaitingForChildrenToComplete: Query execution is in progress.

  • WaitingForActivation: Query execution is in progress.

  • Canceled: User cancelled the query execution.

  • Faulted: Something went wrong; further information can be retrieved from the response JSON. See step 3 to retrieve an end failure message.

  • RanToCompletion: Query has completed successfully. A table of data is available for downloading in a variety of formats. See step 3.

Unexpected timeouts at or beyond 30 minutes

Note that a query may error with a Timeout if the query either:

  • Runs for longer than 30 minutes (due to potential Luminesce query engine interruption).

  • Has any single provider call that takes longer than 30 minutes (due to potential provider interruption).

This may occasionally happen even if the PRAGMA TimeoutSec = N; and/or the timeoutSeconds parameter is set to more than 1800.

This is because cloud infrastructure for excessively long running operations cannot be guaranteed. Luminesce implements many strategies to ensure runtimes of up to 30 minutes are as likely to complete as possible. For runtimes of 30 minutes or more, cloud interruption events and continuous deployment operations are allowed to interrupt such queries.

Runtimes of over 8 hours are not supported.

Step 3: Retrieve your results

Once your GetProgressOf response has a status of RanToCompletion, you can call the FetchQueryResult<format> API to return the query results as a table of data in your preferred format. You can also use this API to retrieve an end failure message if your response has a status of Faulted.

Currently, APIs are available to retrieve results in the following formats:

For example, to return the results of the query sent in step 1 as a table of data in a CSV format, you can call the FetchQueryResultCsv API:

 curl -X GET "https://<your-domain>.lusid.com/honeycomb/api/SqlBackground/f2c1fb86-cb4e-4d4c-a555-3da4cef751e0/csv?download=true"
  -H "Accept: application/json, text/plain, text/json"
  -H "Authorization: Bearer <your-API-access-token>"

429 errors

It is common to receive a 429 error response, so it is important to handle these errors. A 429 error indicates the query is still running or that data is being serialized. You can resend your FetchQueryResult API request until you receive a non-429 response.

If your GetProgressOf API response (in step 2) contained a status of RanToCompletion, a successful response is as follows:

PortfolioScope,PortfolioCode,DisplayName,TxnId,Type,TransactionDate,SettlementDate,Units,TradePrice,TradePriceType,TotalConsideration,TradeCurrency,ExchangeRate,SettlementCurrency,CounterpartyId,Source,BondInterest,TradeToPortfolioRate,OrderScope,OrderCode,AllocationScope,AllocationCode,InstrumentScope,ResolvedLusidInstrumentId,LusidInstrumentId,Isin,Sedol,Cusip,Ticker,ClientInternal,Figi,CompositeFigi,ShareClassFigi,Wertpapier,RIC,QuotePermId,EdiKey,WriteAction,WriteAsAt,WriteErrorCode,WriteError,WriteErrorDetail
Finbourne-Examples,US-Equities,US Equity Portfolio,TXN-123,Buy,2024-09-18,2024-09-19,100,250,Price,25000,,0,GBP,,,,,,,,,default,LUID_00003DFS,,,,,AMGN,LUID_00003DFS,,,,,,,,Upsert,2024-04-10 16:20:17.382,0,,
Finbourne-Examples,Global-Portfolio,Global Portfolio,TXN-124,StockIn,2024-09-18,2024-09-19,100,200,Price,20000,,0,GBP,,,,,,,,,default,LUID_00003DG1,,,,,AAPL,LUID_00003DG1,,,,,,,,Upsert,2024-09-20 11:57:25.548,0,,
Finbourne-Examples,Global-Portfolio,Global Portfolio,TXN-125,Sell,2024-09-18,2024-09-19,100,800,Price,80000,,0,GBP,,,,,,,,,default,LUID_00003DFJ,,,,,INTC,LUID_00003DFJ,,,,,,,,Upsert,2024-09-20 11:57:25.548,0,,

If your GetProgressOf API response (in step 2) contained a status of Faulted, a successful response is formatted as a JSON regardless of the chosen format and contains details on the error.

Cancelling a query

You can cancel a query at any time using the CancelQuery API. When you send a cancellation request:

  • If the query is still running, the query execution is cancelled.

  • If the query has finished running, you can no longer retrieve query data.

For example, to cancel the query from step 1:

curl -X DELETE "https://<your-domain>.lusid.com/honeycomb/api/SqlBackground/f2c1fb86-cb4e-4d4c-a555-3da4cef751e0"
  -H "accept: text/plain"
  -H "Authorization: Bearer <your-API-access-token>"