Overview
Using a bad atquery
to export/dump data from the Sensage database to a CSV file can result in a timeout or processes being killed.
(This article assumes you are running an ad-hoc (SQL) query report using the atquery
instead of using the Analyzer's reporting features.)
Solution
The user is most likely using a sub-optimal atquery
that is exporting a lot of unnecessary data and not allowing the query to execute within a reasonable time frame.
Running atquery --help
can help you understand the full usage of the atquery and its options.
To optimize the atquery
:
- Don't forget to specify the format you want to save the report in. If it's a CSV file, use
--format="csv"
- Add the
--verbose=1
option to avoid showing unnecessary data. - Specify a time range to limit data. For example, if you need to export data from a single day, you should use a
DURING
clause to limit the query to the desired time range. This could look like this:SELECT * FROM example_table DURING _time('Jan 31 00:00:00 2020'),_time('Jan 31 23:59:59 2020')
. See page 373 of the Reporting Guide in the Information section below to find the usage of the_time()
function. - Do NOT use a
WHERE
clause on a specific field to filter data by time as it is going to go over the entire table to export even a single day and that will take forever. Replace theWHERE
clause with aDURING
clause to narrow down the time range as explained above. - Use the
SELECT
statement to select only the data fields that are needed.
Prior knowledge of SQL and Sensage SQL syntax is needed here. For helpful documentation see the Information section.
Following these recommendations should let the query execute without timing out.
Information
For help on reporting, Sensage SQL syntax, and other user guides, refer to the following:
- Sensage AP 5.x: Reporting Guide
- Sensage AP 6.x: Product Documentation
Comments
0 comments
Please sign in to leave a comment.