The Databases for PostgreSQL data component

The IBM® Cloud Databases for PostgreSQL data component provides intermediate length storage of your IoT data.

Overview

Databases for PostgreSQL is a database for your recent historical IoT data.

Databases for PostgreSQL also makes up the data lake, which is used as input for the analytics service. Analytics Service writes function output to the data lake.

Data flow

Platform Service collects and retains IoT data for direct access and processing and also for historical storage and analytics with Platform Service.

The following steps constitute the data flow:

1. Your IoT devices send data to the Platform Service, which acts as a message broker and real-time handler of IoT data.
2. Device type data is immediately written to the Databases for PostgreSQL table (iot_devicetype).
   To store IoT data from your connected devices your environment must meet the IBM Maximo Asset Monitor, messaging, and Databases for PostgreSQL requirements.
3. Analytics Service: Derived function data is written to function specific tables.
4. IoT device data and the Analytics Service derived function data is not automatically purged but must be managed manually.

Access information

Access information and credentials for the Databases for PostgreSQL user interface and REST API can be found on the user interface. For detailed steps, see the Get Databases for PostgreSQL credentials section.

Requirements

In addition, to store IoT data from your connected devices in Databases for PostgreSQL, your environment must meet the following requirements:

Maximo Asset Monitor requirements

The following requirements apply to messaging and to your Maximo Asset Monitor instance.

• Device payloads must be well-formed JSON or HTML.
• A device-type logical interface must exist for the device type.
  The logical interface is required to create the Databases for PostgreSQL required tables.
  For more information, see Connecting devices.
  The following limitations apply to the logical interfaces:
  • Only device-type logical interfaces are supported. Thing-type interfaces are not supported.
  • Array JSON type data is not supported as logical interface property data.
  • Nested JSON objects are not supported as logical interface property data.
• Each logical interface must include a complete physical interface mapping.
  If a property is missing from the payload and is not explicitly mapped, the logical interface might not be triggered, and no data is written to the Platform Service.
  You can avoid this issue by explicitly mapping empty properties to default payload values.
  1. In the Platform Service dashboard, go-to to Devices > Device Types.
  2. Select the device type for which you want to update the logical interface mapping.
  3. Click the Edit icon in the device type Interface tab.
  4. Click the Edit icon for the logical interface.
  5. Click Next to get to State Model.
  6. Edit the property that might require null value support.
  7. In the Mapping section, enable Advanced Editor.
  8. Edit the mapping:
     $exists($event.property) ? $event.property : {payload_value}
     Where:
     • .property is the name of the property that you are modifying.
     • $exists checks if the property exists in the payload.
     • {payload_value} is the default value that you want to send if the property is missing from the actual payload, for example: "String", -9999 null
  9. Save your changes and deploy the logical interface.
• To support null values for your properties, configure null as a supported data type for the property.
  The null support configuration must be done by using the HTTP API, which is protected with HTTP basic authentication and require an API key and authentication token. For more information about API keys and tokens, see API key connection.
  1. Deactivate the logical interface configuration by using the patch /logicalinterfaces/{logicalInterfaceId} command.
  2. Get the current schema content by using the get /draft/schemas/{schemaId}/content command.
  3. Update the schema content to support NULL values.
     Save the response from step 2 to a JSON file and edit the properties that need to support null values, for example, change "property": "string" to "property": ["string", "null"]
  4. Update the schema by using the put /draft/schemas/{schemaId} command.
  5. Reactivate and deploy the logical interface changes by using the patch /draft/logicalinterfaces/{logicalInterfaceId} command.
• Additional properties feature
  When you configure your logical interfaces, you can use the additional properties flag to permit additional properties in the state JSON schema.
  To permit schema updates to be correctly processed, the additional properties feature must be set to off.

Databases for PostgreSQL requirements

The following requirements apply to your Databases for PostgreSQL instance.

• Table names
  Do not rename system-created tables in Databases for PostgreSQL. All changes to tables must be done by updating the corresponding logical interface.
• Reserved column names
  Make sure the message properties that are included in the message payloads do not duplicate Maximo Asset Monitor proprietary Databases for PostgreSQL column names. As part of data ingestion process, a number of columns are added to identify the records from an IoT perspective. For more information, see Schemas and tables.
  The following column names are reserved and must not be included as property names in the payloads:
  • devicetype
  • deviceid
  • logicalinterface_id
  • eventtype
  • format
  • rcv_timestamp_utc
  • updated_utc
• Column data types
  The column data types are automatically created based on the data types that are assigned to the logical interface properties. To update the data type, you must modify the logical interface.
  If you change the data type for a property, the existing Databases for PostgreSQL table is renamed for backup purposes, and a new table is created.

Default settings for Databases for PostgreSQL

The following default configuration settings are used:

• The data storage capacity is defined by your plan. For more information, see the Monitoring usage topic.
• Analytics Service: The Analytics Service database tables are user-managed. See "optimizing" information.

Schemas and tables

In Databases for PostgreSQL, IoT device data is stored as tables. Maximo Asset Monitor automatically creates all schemas and tables to manage all data insertion.

General Platform Service tables

All user-accessible IoT data is stored in tables according to device type and uses the following naming convention: iot_devicetype.

The following tables are available:

Tip: All tables use the Cloudant NoSQL DB '_ID' parameter as the primary key.

The following identifiers are used in the previous table:

• devicetype is the device type for which data is stored.
• timestamp is the time stamp of the schema update that triggered the table branching. Format: YYYYMMDDHHmmSSsss

The iot_devicetype tables are used by and controlled by Platform Service and must not be the target for any SQL operations except QUERY.

The following Platform Service logical interface driven schema upgrades can be automatically merged in Databases for PostgreSQL:

• Updating the logical interface name.
• Adding a property or renaming an existing property in the logical interface.
  This action adds a database table column with the new property name.

Analytics Service tables

All user-accessible Analytics Service data is stored in tables according to device type and uses the following naming convention: dm_entity_grainid.

The following tables are available:

The following identifiers are used in the previous table:

• device is the device type for which derived metric data is stored. For entities that are based on IoT devices the device part of the table name corresponds to the device type (DEVICETYPE).
• GRAINID is the granularity set at which the function data was calculated.

The Analytics Service tables are used by and controlled by IBM Maximo Asset Monitor and must not be the target for any SQL operations except QUERY.

Data access

Use an external database tool or the Databases for PostgreSQL API to extract and process the IoT data that you need. For more information about service API access, see REST APIs.

Tip: with the Analytics Service, you can access PostgreSQL data directly from the data lake with the Monitor dashboard, and use it as input for analytics functions.

Step 1: Get Databases for PostgreSQL credentials

In the Maximo Asset Monitor user interface, click View Details for the Databases for PostgreSQL service to get the following parameters. These parameters are required to connect to and retrieve IoT data from the database:

Databases for PostgreSQL Credentials

Username

An alphanumeric string.

Example: ibmcloudunique_identifier

Password

An alphanumeric string.

Example: 15645 ... bb201

Database name

Default: ibmclouddb

Default schema: public

Host

A URI that identifies your Databases for PostgreSQL host.

Example: unique_identifier.databases.appdomain.cloud.

Port

A port number to access your Databases for PostgreSQLL instance.

Example: 50000

Optional: Certificate

Base64 encoded version of the certificate used to enabled SSL connection.

Example: MIIDD ... Q3oaUA==

For more information about Databases for PostgreSQL authorization and how to provide credentials in API calls, see Databases for PostgreSQL documentation.

Step 2: Find your IoT device data

Use an external database tool to connect to Databases for PostgreSQL and access your data. You can also access the data by using the API.

Databases for PostgreSQL recommends the PGAdmin tool. For more information, see the Administration Tools section of the Databases for PostgreSQL documentation.

To explore your IoT data, complete the following steps:

1. In your database tool, connect to your Databases for PostgreSQL database.
2. Explore the Databases for PostgreSQL tables and data.
   • Your device data is in the iot_devicetype tables.
   • For the Analytics Service, you can find the derived metrics data in the dm_entity+grain tables.

The iot_devicetype tables are used by and controlled by IBM Maximo Asset Monitor and must not be the target for any SQL operations except QUERY.

Step 3: Retrieve IoT device data

If your plan includes the Analytics Service add-on, the Monitor tab lets you derive key performance indicators (KPIs) from device data by using the built-in analytics functions. Line-of-business users can easily enrich and interact with the raw metrics data that comes from IoT entities by using the built-in configurable analytics functions. Data scientist developers can extend and customize the standard functions by using a set of APIs.

Related topics

For more information, see the following Databases for PostgreSQL documentation topics:

• Databases for PostgreSQL documentation
• Overview of Analytics Service