Null Functions - Kinetica Docs

Some of the following null functions require parameters to be of convertible data types. Note that limited-width (charN) & unlimited-width (non-charN) string types are not convertible.

COALESCE(expr_a, ..., expr_N)

Returns the value of the first expression that is not null starting with expr_a and ending with expr_N. If all are null, then null is returned. All expressions should be of the same or convertible data type.

IFNULL(expr_a, expr_b)

Returns expr_a if it is not null; otherwise, returns expr_b. Both should be of the same or convertible data type. See Short-Circuiting for error-checking details.

IS_NULL(expr)

Returns 1 if expr is null; otherwise, returns 0

ISNULL(expr)

Alias for IS_NULL(expr)

NULLIF(expr_a, expr_b)

Returns null if expr_a equals expr_b; otherwise, returns the value of expr_a; both expressions should be of the same or convertible data type.

NVL(expr_a, expr_b)

Alias for IFNULL

NVL2(expr, value_if_not_null, value_if_null)

Evaluates expr: if not null, returns value_if_not_null; if null, returns value_if_null. Both value_if_not_null & value_if_null should be of the same data type as expr or implicitly convertible; see Short-Circuiting for error-checking details.

REMOVE_NULLABLE(expr)

Alias for ZEROIFNULL

ZEROIFNULL(expr)

Replaces null values with appropriate values based on the column type (e.g., 0 if numeric column, an empty string if charN column, etc.). Also removes the nullable column property if used to calculate a derived column.

Record Distribution Functions

Record distribution functions provide a means to locate any database record within the memory processing hierarchy; and through aggregating the results, determine the distribution of records across the cluster.

KI_CHUNK()

Returns the index of the chunk within the rank/TOM containing this record. This value may change as data is added or deleted from a table.

KI_CHUNK_ID()

Returns the ID of the chunk within the rank/TOM containing this record. This value should remain the same even after system restarts.

KI_CHUNK_MAX(col)

Returns the maximum value for the specified column in its chunk. If the data has changed, this value may be higher than any actual values.

KI_CHUNK_MIN(col)

Returns the minimum value for the specified column in its chunk. If the data has changed, this value may be lower than any actual values.

KI_CHUNK_OFFSET()

Returns the index of the record within the chunk.

KI_PARTITION()

Returns the index of the partition within the rank/TOM containing this record, for partitioned tables.

KI_RANK()

Returns the index of the rank containing this record.

KI_TOM()

Returns the index of the TOM within the rank containing this record.

For example, the following query can be used to determine the distribution of records of the example.stocks table across the nodes of the cluster.

SELECT
	KI_RANK() AS "Rank",
	COUNT(*) AS "TotalRecords"
FROM example.stocks
GROUP BY 1
ORDER BY 1

SELECT
	KI_RANK() AS "Rank",
	KI_PARTITION() AS "Partition",
	COUNT(*) AS "TotalRecords"
FROM example.stocks
GROUP BY 1, 2
ORDER BY 1, 2

SELECT
	KI_RANK() AS "Rank",
	KI_TOM() AS "TOM",
	COUNT(*) AS "TotalRecords"
FROM example.stocks
GROUP BY 1, 2
ORDER BY 1, 2

SELECT
	KI_RANK() AS "Rank",
	KI_CHUNK() AS "Chunk",
	COUNT(*) AS "TotalRecords"
FROM example.stocks
GROUP BY 1, 2
ORDER BY 1, 2

For partitioned tables, KI_PARTITION() can be added to the query to also the distribution across partitions, and for clusters with more than 1 TOM per rank (1 TOM/rank is the default), KI_TOM() can be added. To see chunk-level distribution, use KI_CHUNK().

​Record Distribution Functions

Record Distribution Functions