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.

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 base 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, base 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, base 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

Note

The Python API has the ability to use built-in convenience classes or to use a JSON string to specify nullability. However, if 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.

import collections
import json
import gpudb

def create_null_types():

    # Establish connection with a locally-running instance of Kinetica
    h_db = gpudb.GPUdb(encoding = 'BINARY', host = '127.0.0.1', port = '9191')

    # Creating a type using the GPUdbRecordType Object
    columns = []
    columns.append(gpudb.GPUdbRecordColumn("nullable_column", gpudb.GPUdbRecordColumn._ColumnType.DOUBLE, [gpudb.GPUdbColumnProperty.NULLABLE]))
    columns.append(gpudb.GPUdbRecordColumn("non_nullable_column", gpudb.GPUdbRecordColumn._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)

    # 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"])

# end create_null_types()


if __name__ == '__main__':
    create_null_types()

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 employees
WHERE phone_number IS null

Given roll-up view supervisors, you can query to see the number of employees assigned to each supervisor (if any) using the SQL statement

SELECT firstname, lastname, NVL2(employees, employees, 0)
FROM supervisors

Given roll-up view budgets, 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:

gpudb.create_projection(table_name="budgets", projection_name="changing_budgets", column_names=["dept", "dept_id", "budget_prev", "NULLIF(budget_new, budget_prev) as budget_updated"])

Aggregates and Nulls

There a couple items to note when attempting to use the /aggregate/* endpoints 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