Timeout During Export of Sensage Data to CSV Using atquery

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 the WHERE clause with a DURING 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:

 

 

Comments

0 comments

Please sign in to leave a comment.