Ingesting empty or null data
You can import empty or null values into the Oracle Unity data model using the Stream API.
In this topic:
Using the Stream API to ingest null data
When using the Stream API, Oracle Unity ingests null values from streaming ingest payloads where the incoming data is expressed as null. Let's look at an example where we ingest the data record where the "MiddleName" is expressed as null .
Request:
POST /api-stream/v1/<tenantAccessKey>/data/entities
Request body:
{
"customers": [{
"SourceCustomerID": "100",
"FirstName": "John",
"LastName": "Doe",
"MiddleName": null,
"Email": "john.doe@example.com",
"OKtoEmail": "N",
}]
}
In the resulting data record, the record's "MiddleName" in Oracle Unity will be null.
Example: The diagram below illustrates how this works.
Because null was specified in the ingest payload, the resulting value is null. Refer to Restrictions on empty or null data to learn which data types and attributes this behavior does not apply to.
Using the Stream API to ingest empty data
Next let's look at what happens when you use the Stream API to ingest an empty value, ie. "MiddleName": "".
Request:
POST /api-stream/v1/<tenantAccessKey>/data/entities
Request body:
{
"customers": [{
"SourceCustomerID": "100",
"FirstName": "John",
"LastName": "Doe",
"MiddleName": "",
"Email": "john.doe@example.com",
"OKtoEmail": "N",
}]
}
In the resulting data record, the record's "MiddleName" in Oracle Unity will change from "M" to an empty value after ingestion.
Example: The diagram below illustrates how this works.
It's important to note that empty values are not treated the same as null values. When you want to set an attribute to null, you must explicitly set the value to null during ingestion.
Refer to Restrictions on empty or null data to learn which data types and attributes this behavior does not apply to.
Using ingest jobs to ingest empty or null data
To learn more about how to prepare your source files for Unity ingest jobs, refer to the Oracle Unity Help Center.
Restrictions on empty or null data
The following attributes and data types cannot be made null:
-
Attributes: ID, TenantID, SourceID, CreatedBy, ModifiedBy, CreatedTS, ModifiedTS, RowCreatedTS, RowModifiedTS, and the sourceAttribute. The sourceAttribute is an attribute that helps Oracle Unity identify the attribute's source data object. For example, the sourceAttribute for the "Customer" data object is "SourceCustomerID", and the sourceAttribute for the "Order" data object is "SourceOrderID". The sourceAttribute is applicable to data warehouse data objects.
-
Data types: Boolean, Map, and Array.
Other features to manage empty or null values
These are other features in Oracle Unity to help you better manage empty or null data.
Static Transformations
The Static Transformation enables you to find a word or entry and replace it with another word or entry. You can use this to find null values and replace them with a static value.
Example: You might want to use Static Transformations to replace null values with a string like "not applicable" or "N/A".
How to Use Static Transformations
The following code sample demonstrates how to find null values using a Static Transformation:
"transforms": [
...
[
{
"table": "dataObject",
"column": "attribute"
},
{
"transform": "STATIC",
"arguments": {
"passThruUnmatched" : "true",
"sourceTable.sourceTableColumn.": "Replace_string1",
"sourceTable.sourceTableColumn.Find_string2": "Replace_string2",
"sourceTable.sourceTableColumn.Find_string3": "Replace_string3",
...
}
}
],
...
]
Static Transformation Parameters
| Parameter | Data Type | Description |
|---|---|---|
passThruUnmatched
|
Boolean |
Determines how to treat values that aren't captured by the matching syntax.
|
Note the following:
-
The line
"sourceTable.sourceTableColumn.": "Replace_string1",finds null values -
The lines
"sourceTable.sourceTableColumn.Find_string2"and string3 do not find null values because the "Find_String" portion of the match syntax is omitted from the match syntax -
The Static Transformation cannot insert null values
Null-Value Concatenation Transformations
When designing ingest jobs, the concatenation transformation concatenates two or more data records together. The includeNullAs parameter provides additional control to handle null values, because this parameter replaces null values with a string. This field is optional.
Syntax
[
{
"table": "dataObject",
"column": "attribute"
},
{
"transform": "CONCAT",
"arguments": {
"delimiter": "|",
"order": "sourceDataObject.attribute1,sourceDataObject.attribute2,sourceDataObject.attribute3",
"includeNullAs": "<a_string>"
}
}
]
Examples
Let's walkthrough an example where the concatenate transformation is used to concatenate multiple address values into a single continuous string named "FullAddress".
The data contains the following attributes and corresponding values:
-
Address Line 1 = "123 Redwood Shores"
-
Address Line 2 = null
-
City = "Redwood City"
-
State = "California"
-
Zip Code = "94065"
-
Country = "United States"
-
Delimiter = |
Example 1 - Concatenate without includeNullAs specified:
Concatenation transformation:
[
{
"table": "Address",
"column": "FullAddress"
},
{
"transform": "CONCAT",
"arguments": {
"delimiter": "|",
"order": "Address.AddressLine1,Address.AddressLine2,Address.City,Address.State,Address.ZipCode,Address.Country"
}
}
]
Result:
123 Redwood Shores|Redwood City|California|94065|United States
The result omits the null value (Address Line 2).
Example 2 - Concatenate using the includeNullAs parameter:
Concatenation transformation:
[
{
"table": "Address",
"column": "FullAddress"
},
{
"transform": "CONCAT",
"arguments": {
"delimiter": "|",
"order": "Address.AddressLine1,Address.AddressLine2,Address.City,Address.State,Address.ZipCode,Address.Country",
"includeNullAs": "N/A"
}
}
]
Result:
123 Redwood Shores|N/A|Redwood City|California|94065|United States
The result replaces the null value (Address Line 2) with "N/A".