Data Profiler - Data360_Analyze - Latest

Logical Type detection

You can add your own logical types - also known as semantic types - to those detected by default by the Data Profiler node. To do this, provide a JSON specification in a file that you upload to the server, and use the path to that file as the value of the LogicalTypeDefinitions property on the node.

For example, to detect possible UK National Insurance number values in a data field, you could use the following JSON:

        [
	{
		"semanticType": "UK.NI",
		"pluginType": "regex",
		"validLocales": [ 
		{ 
			"localeTag":  "*", 
			"matchEntries" : [{ "regExpReturned": "\\s*[a-zA-Z]{2}(\\s*\\d\\s*){6}[a-zA-Z]\\s*" } ] 
		}],
		"threshold": 98,
		"baseType": "STRING"
	}
]
      

When you supply input data to the Data Profiler node, types can be identified by using the JSON when the following criteria are met:

• If a string field contains values that are matched by the regular expression defined in the regExpReturned property in the JSON,
• AND, if the percentage of values for a field that are identified as the specified type is equal to or greater than the value specified by threshold. The value is given as a percentage, so in this example, a minimum of 98% of the values must be detected as a match.
• When a match is found, the output analysis identifies the Type Qualifier for the field as the value given by the semanticType property - in this case UK.NI - and the value of the Validation column for the field is the regular expression defined in the regExp property in the JSON.
• For more information, see https://github.com/tsegall/fta

Properties

Sample Size

Optionally specify the size of the sample set of the input data to analyze. The default is to process the entire set.

Empty String Is Null

Optionally specify whether strings that are either zero length or all whitespace are interpreted as NULL. If false, they are treated as distinct string values. The default value is True.

AmbiguousDateResolutionMode

Choose from:

• Day First - In case of ambiguity, assume that the day appears before the month.
• Month First - In case of ambiguity, assume that the month appears before the day.
• Auto - In case of ambiguity, use the information from the locale - based on the Locale property - to determine the correct order.

As an example, consider there is an input string field, with values "1/2/2009" and "1/3/2009".

If AmbiguousDateResolutionMode is set to Day First, then the determined format will be d/M/yyyy and the values will be parsed as the first of February and March in 2009.

If AmbiguousDateResolutionMode is set to Month First, then the determined format will be M/d/yyyy and the values will be parsed as the second and third day of January in 2009.

If AmbiguousDateResolutionMode is set to Auto, then in a US English (en_US) locale, the determined format will be M/d/yyyy and the values will be parsed as the second and third days of January in 2009.

If AmbiguousDateResolutionMode is set to Auto, then in a British English (en_GB) locale, the determined format will be d/M/yyyy and the values will be parsed as the first of February and March in 2009.

The default value is Auto.

Enable Default Logical Types

Enable the default set of Logical Types, which is based on the locale The default value is True.

Length Qualifier

Indicate whether to qualify the size of the detected Regular Expression. For example, if set to True the qualifier might be expresssed as \d{3,6}, indicating a minimum length of three digits and a maximum of 6, or if set to False, it could result be expressed as \d+, indicating one or more digits.

The default value is True.

Detect Window

Set the size of the Detect Window to collect before attempting to determine the type. This value is the number of samples taken in order to determine the type. The default value is 20.

Detection Threshold

Set the percentage for the Detection Threshold - typically between 60 and 100. If set to 100, then all values must be of the appropriate type. The default value is 95.

Maximum Cardinality

Set the maximum cardinality that will be tracked. This value represents the number of discrete values for a field that will be tracked in order to analyze a field. The default value is 12000.

Maximum Outlier Cardinality

Set the maximum outlier cardinality that will be tracked. This value represents the number of discrete values that are outliers, and which will be tracked in order to analyze a field. The default value is100.

JSONDetail Level

Control the level of detail related to valid, invalid, and shape sets provided in the JSON output.

• 0 - just counts.
• 1 - first 100 rows.
• 2 - all rows.

The default value is 1.

Locale

Optionally specify the Locale to be used for analyzing the input stream. Locale must be specified in the format <language code>[_<country code>[_<variant code>]]

For example, to set the locale to English, the Locale property should be set to en.

For US English, the Locale property should be set to en_US.

For Austrian German, the Locale property should be set to de_AT.

The default value is the default Locale under which the server is running.

LogicalTypeDefinitions

Optionally provide the path to a JSON specification for additional Logical Types to be detected. For details, see Logical Type detection.

Bulk Mode

If set to True then the input is assumed to be a bulk description of the data to be processed.

In this mode four fields are expected:

• Key (unicode)
• Name (unicode)
• Value (unicode)
• Count (long)

For example, the first row could be Northwind.Customers.Gender,Gender,Female,10341

The default value is False.

Key Field

Optionally select a Field from the input to be used as a Pass-through field. This provides similar functionality to the Key input in Bulk mode.

Inputs and outputs

Inputs: in1.

Outputs: analysis, detail.