Skip to Content

Export Time Series Data

Requires Customer/Partner License
Export time series data and derived data to a file format GZIP, so that the data can be used as is without any need for de-serialization logic.
You will learn
  • How to initiate time series data export
  • How to download the .csv file
  • How to import data into a table using SAP HANA Database Explorer
  • How to execute SQL query on the time series data
banurekhabalajiBanurekha BalajiSeptember 7, 2022
Created by
February 21, 2021


  • Your tenant is onboarded in or after SAP IoT 2010b release.
  • You created an application and a device model.
  • You have onboarded (virtual) devices and have enabled them to send data with timestamps over a period of several days or weeks.
    • For testing purpose, you can ingest data for a week so that you have a considerable amount of data for further analysis.

For data analytics or machine learning scenarios, there is a need to extract time series data for a longer duration spanning across multiple months or years. You can export time series data and derived data to a file format GZIP, so that the data can be used as is without any need for de-serialization logic.

  • Step 1

    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:

      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 your tenant namespace is iot-training-f954-d. The package name must be prefixed with tenant namespace and the hyphen is replaced with dot. identifier of the package
      silo_statusName 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:

      • 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
      InitiatedThe request is placed successfully.
      SubmittedThe request for data export is initiated and the method is retrieving the data and preparing for the export process.
      FailedThe request for data export failed due to various reasons. The reasons are listed in the response payload.
      ExceptionThe system retried to initiate the data export but failed.
      Ready for DownloadThe request for data export succeeded and the data is available in a file format for download.
      ExpiredThe 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:'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,

    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.

    Select the correct size of the data that you can download in a single request:

  • Step 2
    1. Create a database in your SAP HANA trial account following Step 1-3 in the tutorial Choose an SAP HANA Database.

      After creating the database, you are now ready to import the data in the *.csv file into SAP HANA Database.

    2. In SAP HANA Database Explorer, right click the database name and select Import Data from the context menu.

      Import data

      The Import Data wizard appears.

    3. Choose Import Data in the IMPORT TYPE tab.

      Choose import data
    4. Click the Step 2 button.

    5. Select the source of data from the Import Data From dropdown list.

      Choose import data source
      • In this tutorial, the source data is available in the local system. Hence, the next step is to navigate to the folder where the *.csv file is available.
      Choose file to import data
    6. Click the Step 3 button.

    7. You can import the data into a new table or an existing table. In this tutorial, the data is imported into a new table.

      • Select the Create a new table option in the IMPORT TARGET tab.
      • Select DBADMIN for the Schema and enter a table name.
      Choose schema and table name to import data
    8. Click the Step 4 button.

    9. In the TABLE MAPPING tab, you configure the mapping between the column names in the *.csv file and the database.

      • Use the arrow keys to include or exclude the columns in the database.
      • Define the data type and data length for the columns that will be included in the database table. The following image illustrates the column names included in the database and the data type chosen.
      map column and table properties
    10. Click the Step 5 button.

    11. Choose the appropriate error handling option in the ERROR HANDLING tab.

      map column and table properties
    12. Click the Review button to preview the import.

      map column and table properties
    13. Click the Import Into Database button. The data is imported into the specified table and the import status is displayed.

      map column and table properties
    14. You can now access the table and the data that is imported into the table.

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

      open sql

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

      Example 1: Select all rows from the table SILO.

      Select * from SILO;
      Select all rows

      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
      Select list of things

      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
      select maximum temperature

      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
      select minimum timestamp value
    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.

      table name

      The data stored in the table is displayed.

      graphical representation
    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.
      analysis tab

      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.

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

      SUM("fill_level") AS "SUM_fill_level",
      MIN("temperature") AS "MIN_temperature",
      MAX("temperature") AS "MAX_temperature"

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

  • Step 4

    To explore more about downloading the exported data in different ways, you can go to the SAP blog Downloading files from the Coldstore-Downloader app of SAP Internet of Things (SAP IoT).

Back to top