LiveView Web cannot export data to CSV while query has a limit error
Article ID: KB0077028
Updated On:
| Products | Versions |
|---|---|
| TIBCO LiveView Web | - |
Description
LiveView Web permits exporting the data shown in a data-grid card to a local CSV file.
It appears the export action is not allowed:
1. when using the LIMIT keyword in the query
2. when not using LIMIT but when data exceeds the more than 1000 rows (the default limit)
How can we avoid the query error so that data may be exported from a card?
It appears the export action is not allowed:
1. when using the LIMIT keyword in the query
2. when not using LIMIT but when data exceeds the more than 1000 rows (the default limit)
How can we avoid the query error so that data may be exported from a card?
Issue/Introduction
Cards that have an error status cannot be used to export data
Resolution
In LiveView Web, you cannot export data from a card that has an error (red (!) status).
This happens when there are too many rows for the current row-limit of the query. The default limit is 1000 rows, after which the query is closed. This limit protects the server from open-ended queries which can consume too many server resources with continuous updates.
To avoid reaching the default query limit for number of rows maintained in the view continuously, in the query expression of the card, use the query predicate modifiers " ORDER BY { column} LIMIT { count}". As long as the limit value specified in this expression is less than the default, then there will never be an error from hitting the limit. Make sure you pick an "ORDER BY" column which is either part of the primary key or is part of a secondary index.
For example:
The different uses of the "LIMIT" keyword are described in the product documentation here:
TIBCO Live Datamart Documentation > LiveView References > LiveQL Reference
Section: " LiveQL Syntax Reference"
Subsection: " Query Modifiers"
which states:
"LIMIT has a special meaning in ORDER BY queries. In ORDER BY queries, the limit defines the maximum size of the result set. The result set will grow to size LIMIT and then the correctly ordered rows will be added/removed or updated from the result set regardless of how many total rows meet the predicate.
For non ORDER BY queries, LIMIT acts as a mechanism for clients to optionally use to protect themselves from unexpectedly large result sets. If the result set grows to LIMIT size, the query is closed and the client gets an exception."
To avoid limit errors in the card, always use "ORDER BY" along with a limit count. It is the query being closed on an error that prevents exporting the data to a CSV file.
When using the above query example, the card has no error when at the limit because it is maintained within the limit by the ORDER BY and LIMIT expressions together:
This happens when there are too many rows for the current row-limit of the query. The default limit is 1000 rows, after which the query is closed. This limit protects the server from open-ended queries which can consume too many server resources with continuous updates.
To avoid reaching the default query limit for number of rows maintained in the view continuously, in the query expression of the card, use the query predicate modifiers " ORDER BY { column} LIMIT { count}". As long as the limit value specified in this expression is less than the default, then there will never be an error from hitting the limit. Make sure you pick an "ORDER BY" column which is either part of the primary key or is part of a secondary index.
For example:
The different uses of the "LIMIT" keyword are described in the product documentation here:
TIBCO Live Datamart Documentation > LiveView References > LiveQL Reference
Section: " LiveQL Syntax Reference"
Subsection: " Query Modifiers"
which states:
"LIMIT has a special meaning in ORDER BY queries. In ORDER BY queries, the limit defines the maximum size of the result set. The result set will grow to size LIMIT and then the correctly ordered rows will be added/removed or updated from the result set regardless of how many total rows meet the predicate.
For non ORDER BY queries, LIMIT acts as a mechanism for clients to optionally use to protect themselves from unexpectedly large result sets. If the result set grows to LIMIT size, the query is closed and the client gets an exception."
To avoid limit errors in the card, always use "ORDER BY" along with a limit count. It is the query being closed on an error that prevents exporting the data to a CSV file.
When using the above query example, the card has no error when at the limit because it is maintained within the limit by the ORDER BY and LIMIT expressions together:
Feedback
Yes
No
Powered by
