Export Time Series Data

To learn how to call an API using Postman, see Call SAP IoT APIs with Postman.

For more information about the time series data export APIs, see Export Time Series Data in SAP Help Portal.

1. In Postman, choose the POST method.

2. Enter the following request URL to initiate time series data export: https://coldstore-export-sap.cfapps.eu10.hana.ondemand.com/v1/InitiateDataExport/iot.training.f954.d.com.sap.silo9960:silo_status?timerange=2020-12-01-2020-12-07.

   The method after successfully initiating the data export, will display a request ID in the response header.

   • The data to be exported must be less than or equal to 7GB. If the data is beyond 7GB, you must export the data in multiple requests by splitting the date range.
   • The date range should be greater than 48 hours and less than 365 days.

   For better understanding of the property set type name in the request URL, refer the below table:

   Text	Description
   iot.training.f954.d	Assume your tenant namespace is iot-training-f954-d. The package name must be prefixed with tenant namespace and the hyphen is replaced with dot.
   com.sap.silo9960	Unique identifier of the package
   silo_status	Name of the property set type

   For more information about the naming convention of property set type, see Create a Property Set Type.

3. In Postman, choose the GET method.

4. Enter the following request URL to know the status of the initiated data export: https://coldstore-export-sap.cfapps.eu10.hana.ondemand.com/v1/DataExportStatus?requestId=0B003FE2F4DE43FC810E041CD4FD5D14.

   • If the method returns the status with the following message, you can proceed with the download of data:

   
   {
       "RequestId": "0B003FE2F4DE43FC810E041CD4FD5D14",
       "Status": "The file is available for download."
   }
   

   • The possible status messages that you could see are as follows:

   Status	Description
   Initiated	The request is placed successfully.
   Submitted	The request for data export is initiated and the method is retrieving the data and preparing for the export process.
   Failed	The request for data export failed due to various reasons. The reasons are listed in the response payload.
   Exception	The system retried to initiate the data export but failed.
   Ready for Download	The request for data export succeeded and the data is available in a file format for download.
   Expired	The data that is ready for download is available only for seven days, beyond which the exported data is not available for download. You should re-initiate the request for data export.

5. In Postman, choose the GET method.

6. Enter the following request URL to download the data: https://coldstore-downloader-sap.cfapps.eu10.hana.ondemand.com/v1/DownloadData('0B003FE2F4DE43FC810E041CD4FD5D14').

   Click the Send and Download button instead of the Send button.

   The method prompts you to choose a folder path to save the *.zip file. By default, the name of the zip file is same as the time range provided in the initiate data export request. For example, 2020-12-01-2020-12-07.zip.

7. Use the WinZip application to extract the content of the .zip files.
   The extracted file will be a *.gz file.

8. Use the 7-zip application to extract the contents of the .gz file.

   The extracted files are .csv files. Each .csv file corresponds to the respective epoch days for which the data for the requested property set type is available.

1. In SAP HANA Database Explorer, you can run SQL queries on data using SQL Console. Open the SQL Console using the icon.

   

   Following are the example SQL queries and the result data fetched:

   Example 1: Select all rows from the table SILO.

   Select * from SILO;
   

   

   Example 2: Select list of things and temperature from the table SILO where the temperature value is greater than 40

   Select THING_ID, "temperature" from SILO where "temperature" > 40
   

   

   Example 3: Select list of things from the table SILO with maximum temperature value for every thing ID.

   Select THING_ID,  Max("temperature") from SILO group by THING_ID
   

   

   Example 4: Select list of things from the table SILO along with their minimum timestamp value.

   Select THING_ID, Min (ADD_SECONDS (TO_TIMESTAMP ('1970-01-01 00:00:00'), "_TIME" / 1000.0)) from SILO group by THING_ID
   

   

2. In the left pane, expand GreenhouseDB, then Catalog and then choose Tables.

   • If the schema is not selected, you may have to select the DBADMIN schema.

   • The table you created appears in the left pane.

3. Click the table name in the left pane. The column details of the selected table appears in the right pane.

4. Click the Open Data button.

   

   The data stored in the table is displayed.

   

5. In SAP HANA Database Explorer, you can use the Analysis tab to display the data in graphical representation. Click the Analysis tab.

6. In the Analysis tab, drag and drop one or more fields into the Label Axis frame and Value Axis frame.

   For each field, you can change the function to be executed, such as minimum temperature value and sum of fill levels. Use the arrow-like button before the field name to change the function.

   

7. Click the SQL icon to view the corresponding SQL query which looks as follows:

   SELECT TOP 1000 DISTINCT  
   "THING_ID",
   SUM("fill_level") AS "SUM_fill_level",
   MIN("temperature") AS "MIN_temperature",
   MAX("temperature") AS "MAX_temperature"
   FROM "DBADMIN"."SILO"
   GROUP BY "THING_ID"
   ORDER BY "THING_ID" ASC
   

   In the SQL Query window, you have an option to copy the query to the clipboard.