Version:

Nulls

Using nulls begins with the nullability of type fields at type schema creation time. After supplying a column name and primitive type in a type schema, you can specify the NULLABLE column property. Null values and nullability are also supported for SQL via the Kinetica ODBC/JDBC connector.

Setting Nullability

Setting nullability is possible using three different methods: via GAdmin, via the native API (using the /create/type endpoint), and via SQL. The process for specifying nullability varies between the different API languages and SQL; the process for each language is outlined below.

SQL

When creating a table in SQL, columns are nullable by default, but can be set as nullable explicitly with the modifier NULL. Non-nullable columns need to be defined explicitly with NOT NULL.

In the following example, all columns except for id are nullable:

CREATE OR REPLACE TABLE employee
(
    id INTEGER NOT NULL,
    dept_id INTEGER,
    manager_id INTEGER,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    phone_number VARCHAR(16) NULL,
    sal DECIMAL NULL,
    hire_date DATE NULL,
    PRIMARY KEY(id)
)

REST

Note

The REST API does not have the ability to use built-in convenience classes, so not only do you need to specify nullability at the column property level but also at the type definition level as a union between the type of the column and null.

{
  "type_definition":{
    "type":"record",
    "name":"example_null_type_rest",
    "fields":[
      {"name":"nullable_column", "type":["int", "null"]},
      {"name":"non_nullable_column", "type":"int"}
    ],
    "label":"example_null_type_rest",
    "properties":{
      "nullable_column":["nullable"]
    }
  }
}

C++

#include "gpudb/GPUdb.hpp"

#include <boost/algorithm/string/classification.hpp>
#include <boost/algorithm/string/split.hpp>
#include <boost/lexical_cast.hpp>
#include <boost/optional/optional_io.hpp>

int main() {

  // Establish connection with a locally-running instance of Kinetica
  gpudb::GPUdb h_db("http://127.0.0.1:9191");

  /* Create the 'columns' vector, and add two columns with types and properties
     to the vector */
  std::vector<gpudb::Type::Column> columns;
  columns.push_back(gpudb::Type::Column("nullable_column", gpudb::Type::Column::ColumnType::DOUBLE, {gpudb::ColumnProperty::NULLABLE}));
  columns.push_back(gpudb::Type::Column("non_nullable_column", gpudb::Type::Column::ColumnType::DOUBLE));

  // Establish a type ('nullType') using a label and the  'columns' vector
  gpudb::Type nullType("null_cpp_type", columns);

C#

using System;
using System.Collections.Generic;

using Avro;
using kinetica;

namespace CreateNullType
{
    /// <summary>
    /// Creating a new type with a nullable column
    /// </summary>
    class create_null_type
    {
        // Create basic columns with a general type
        private class null_type
        {
            public int nullable_column { get; set; }
            public int non_nullable_column { get; set; }
        }

        static void Main()
        {
            // Establish connection with a locally-running instance of Kinetica
            Kinetica h_db = new Kinetica("http://127.0.0.1:9191");

            // Create a map of properties lists ('column_properties')
            IDictionary<string, IList<string>> column_properties = new Dictionary<string, IList<string>>();

            /* Create a properties list ('null_col_props') for the column with
               the 'NULLABLE' property added, then map the properties list to
               the 'nullable_column' in the 'column_properties' map */
            List<string> null_col_props = new List<string>();
            null_col_props.Add(ColumnProperty.NULLABLE);
            column_properties.Add("nullable_column", null_col_props);

            /* Set variable 'null_type' to the 'null_type' class with the
               additional properties found in 'column_properties', then
               create the type and print out the type ID */
            KineticaType null_type = KineticaType.fromClass(typeof(null_type), column_properties);
            string type_id = null_type.create(h_db);
            Console.WriteLine("GPUdb generated type id for the new type - " + type_id);
        }

Java

import com.gpudb.*;

public class CreateNullType
{

    /* Create columns, establish their ordering, provide column properties,
       provide the column type, then provide a column name. */

    public static class NullJavaType extends RecordObject
    {
        @RecordObject.Column(order = 0, properties = {ColumnProperty.NULLABLE})
        public Integer nullable_column;

        @RecordObject.Column(order = 1)
        public Integer non_nullable_column;
    }

    public static void main(String[] args) throws GPUdbException {
        // Establish connection with a locally-running instance of Kinetica
        GPUdb gpudb = new GPUdb("http://localhost:9191");

        // Create a type from the NullJavaType class and print out the type ID
        String typeId = RecordObject.createType(NullJavaType.class, gpudb);
        System.out.println("GPUdb generated type id for the new type - " + typeId);
    }
}

JavaScript

'use strict';

/* nulls.html -- opened using a browser to call the create_null_type.js script

<!DOCTYPE html>
<html>
<head>
</head>

<body>
<script language="javascript" src="../javascript/GPUdb.js"> </script>
<script language="javascript" src="create_null_type.js"> </script>
</body>

</html>

*/

main();


function main()
{
	// create a callback function to receive responses from Kinetica
	var build_callback = function(success, error) {
		return function(err, response) {
			if (err === null) {
				if (success !== undefined) {
					success(response);
				}
			} else {
				if (error !== undefined) {
					error(err);
				} else {
					console.log(err);
				}
			}
		};
	}

	// Establish connection with a locally-running instance of Kinetica
	var h_db = new GPUdb( "http://localhost:9191" );

	// Establish columns, general types, and properties
	var null_type = new GPUdb.Type("null_js_type",
			new GPUdb.Type.Column("nullable_column", "int", "nullable"),
			new GPUdb.Type.Column("non_nullable_column", "int")
	);

	// Create a type and print out the type ID
	null_type.create(h_db, build_callback(function(response) {
			var type_id = response;

Node.js

var GPUdb = require("../nodejs/GPUdb.js");

// Establish connection with a locally-running instance of Kinetica
var h_db = new GPUdb("http://localhost:9191");

// create a callback function to receive responses from Kinetica
var build_callback = function(success, error) {
	return function(err, response) {
		if (err === null) {
			if (success !== undefined) {
				success(response);
			}
		} else {
			if (error !== undefined) {
				error(err);
			} else {
				console.log(err);
			}
		}
	};
}


var create_type = function() {
		// Establish columns, general types, and properties
		var null_type = new GPUdb.Type("null_node_type",
				new GPUdb.Type.Column("nullable_column", "int", "nullable"),
				new GPUdb.Type.Column("non_nullable_column", "int")
		);

		// Create a type and print out the type ID
		null_type.create(h_db, build_callback(function(response) {
				type_id = response;
				process.stdout.write("GPUdb generated type id for the new type - " + type_id)

Python

Creating a Table with Nullable Columns

To create a table with a nullable column:

import collections
import json
import gpudb
from gpudb import GPUdbRecordColumn as GRC
from gpudb import GPUdbColumnProperty as GCP
# Establish connection with a locally-running instance of Kinetica
h_db = gpudb.GPUdb(encoding = "BINARY", host = args.host, port = "9191")
# Create a column list
columns = [
    [ "nullable_column", GRC._ColumnType.DOUBLE, GCP.NULLABLE ],
    [ "non_nullable_column", GRC._ColumnType.DOUBLE ]
]

# Create a simple table using the column list
gpudb.GPUdbTable(
    columns,
    name = "table_with_nullable_column",
    db = h_db
)

Creating a Type with Nullable Fields

To create a type, from which multiple tables can be instantiated, either the GPUdbRecordType object or a JSON string can be used.

GPUdbRecordType
# Establish connection with a locally-running instance of Kinetica
h_db = gpudb.GPUdb(encoding = "BINARY", host = args.host, port = "9191")
# Creating a type using the GPUdbRecordType Object
columns = []
columns.append(gpudb.GPUdbRecordColumn("nullable_column", GRC._ColumnType.DOUBLE, [GCP.NULLABLE]))
columns.append(gpudb.GPUdbRecordColumn("non_nullable_column", GRC._ColumnType.DOUBLE))

null_type_1 = gpudb.GPUdbRecordType(columns, label = "null_py_type_object")
null_type_1.create_type(h_db)
type_id = null_type_1.type_id
print "GPUdb generated type id for the new type (using the GPUdbRecordType Object) - {}".format(type_id)
JSON
# Establish connection with a locally-running instance of Kinetica
h_db = gpudb.GPUdb(encoding = "BINARY", host = args.host, port = "9191")
# Creating a type using a JSON string
null_type_2 ='''{
                        "type": "record",
                        "name": "null_py_type_json",
                        "fields": [
                            {"name": "nullable_column", "type": ["double", "null"]},
                            {"name": "non_nullable_column", "type": "double"}
                        ]
                }'''

response = h_db.create_type(type_definition = null_type_2, label = "null_py_type_json", properties = {"nullable_column": ["nullable"]})
print "GPUdb generated type id for the new type (using a JSON string) -  {}".format(response["type_id"])

Note

When using a JSON string to create a type, one needs to specify nullability at the column property level but also at the type definition level as a union between the type of the column and null.

Using Nulls

Null values can be used in expressions much like any other value. Null functions can also be used on column names in expressions to test for and evaluate null column values. The native API null functions can be found here; the SQL null functions can be found here.

Examples

Given table employees, you can query to see which employees have not input their phone number into the employee record database using the SQL statement

SELECT *
FROM employee
WHERE phone_number IS null

You can also query for all employees and phone numbers, replacing missing numbers with a note:

SELECT
    first_name,
    last_name,
    NVL2(phone_number, phone_number, '<not given>') AS phone_number
FROM employee

Given roll-up view budget, you can create a projection to track if a department's budget has changed since last year using the NULLIF() function. For example, in Python:

budget_changes = budget.create_projection(
    projection_name="budget_changes",
    column_names=[
        "dept_id",
        "budget_prev",
        "NULLIF(budget_new, budget_prev) as budget_changed"
    ]
)

Aggregates and Nulls

There are two points of note when attempting to perform aggregation/grouping operations on a table or view that contains null values.

  • Null values are ignored in every aggregation calculation except for certain instances of COUNT() usage, e.g., counting the amount of records in a table or view (COUNT(*)) will include records with null values even if all the values in a record are null; however, counting the values in a column (COUNT(column_name)) will ignore null values
  • Aggregating against an empty table results in 0 records for the count and null values for the other calculations (e.g., SUM, MIN, VAR_POP)

Example

Given the following table survey_response:

+-------------------+--------------+-------------+
| email             | first_name   | last_name   |
+-------------------+--------------+-------------+
| jdoe@yahoo.com    | John         | <null>      |
| mrsmith@aol.com   | <null>       | <null>      |
| <null>            | <null>       | <null>      |
+-------------------+--------------+-------------+

If queried like so:

SELECT
    COUNT(*) AS count_survey_responses,
    COUNT(email) AS count_email,
    COUNT(first_name) AS count_fn,
    COUNT(last_name) AS count_ln
FROM survey_response

The following response is returned:

+--------------------------+---------------+------------+------------+
|   count_survey_responses |   count_email |   count_fn |   count_ln |
+--------------------------+---------------+------------+------------+
|                        3 |             2 |          1 |          0 |
+--------------------------+---------------+------------+------------+