Multi-Head Egress

Server-Side

Kinetica in AWS automatically configures the database for multi-head egress.

Client-Side

With the exception of the Python background multi-head process, the multi-head egress object of each API requires a list of worker nodes to use to distribute the retrieval requests, with one entry in the list for each node/process. This list can be auto-populated simply by using a GPUdb connection object, which can retrieve the list of available cluster nodes from the database itself. Below is a code snippet showing an automatically populated worker list and subsequent creation of a multi-head egress object with it:

• Java
• Python

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import com.gpudb.GPUdb;
import com.gpudb.WorkerList;
import com.gpudb.GenericRecord;
import com.gpudb.RecordRetriever;

GPUdb db = new GPUdb("https://<aws.fqdn>/<aws.cluster.name>/gpudb-0");
WorkerList workers = new WorkerList(db);

//Multi-head Egress Object Instantiation
RecordRetriever<GenericRecord> recordRetriever =
    new RecordRetriever<>(db, tableName, recordType, workers);

1
2
3
4
5
6
7
8

from gpudb import GPUdb
from gpudb_multihead_io import GPUdbWorkerList, RecordRetriever

db = GPUdb(host=["https://<aws.fqdn>/<aws.cluster.name>/gpudb-0"])
workers = GPUdbWorkerList(db)

# Multi-head Egress Object Instantiation
bulk_retriever = RecordRetriever( db, table_name, record_type, workers )

Syntax

The following simple query syntax is supported for key lookups:

1
2
3
4

SELECT * /* KI_HINT_KEY_LOOKUP */
FROM [<schema name>.]<table_name>
WHERE <key_column_1> = <key_value_1> [... AND <key_column_N> = <key_value_N>]
[AND <indexed_column_1> [=] <indexed_value_1> [... AND <indexed_column_N> = <indexed_value_N>]]

Examples

To look up a particular record in a sharded table with an explicit shard key on column id:

1
2
3

SELECT *  /* KI_HINT_KEY_LOOKUP */
FROM product_sk
WHERE id = 1;

The sharded example above can have an equality or inequality expression added to it to further filter the results, as long as the additional column, stock, has an attribute index.

1
2
3

SELECT * /* KI_HINT_KEY_LOOKUP */
FROM product_sk
WHERE id = 2 AND stock > 0;

To look up records matching a given category in a replicated table with attribute indexes on columns id & category:

1
2
3

SELECT *  /* KI_HINT_KEY_LOOKUP */
FROM product_replicated
WHERE id = 2 AND category = 'Furniture';

Setup

First, connect to the database; here, the host IP address is passed in as a parameter to the Python API call:

1

h_db = gpudb.GPUdb(host = ['http://' + args.host + ':9191'], username = args.username, password = args.password)

Next, prepare the target table used by the single-column multi-head key lookup examples by copying the stocks table to a new table:

1
2
3
4
5
6
7

SQL_CREATE_TABLE = """
    CREATE TABLE example.stocks_multihead AS
    SELECT *
    FROM demo.stocks
"""

h_db.execute_sql(SQL_CREATE_TABLE, 0, 0)

Then add an index on the sharded column, Symbol, which must be the target of any multi-head lookup on the stocks table:

1

h_db.alter_table("example.stocks_multihead", "create_index", "Symbol")

To prepare the target table used by the multi-column multi-head key lookup examples, copy, reshard, & index the stocks table; the new shard key will be on both the Industry & Symbol columns:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SQL_CREATE_TABLE = """
    CREATE TABLE example.stocks_multihead_multicolumn AS
    SELECT *, KI_SHARD_KEY(Industry, Symbol) 
    FROM demo.stocks
"""

h_db.execute_sql(SQL_CREATE_TABLE, 0, 0)

# Create a column index on each of the sharded columns
h_db.alter_table("example.stocks_multihead_multicolumn", "create_index", "Industry")
h_db.alter_table("example.stocks_multihead_multicolumn", "create_index", "Symbol")

Key Lookup

Grab a handle to the stocks_multihead table, passing the use_multihead_io option to enable background multi-head key lookups:

1

stocks_table = gpudb.GPUdbTable(None, name = "example.stocks_multihead", db = h_db, use_multihead_io = True)

Then perform the multi-head key lookup, searching the shard column for the value SPGI:

1

response = stocks_table.get_records_by_key(["SPGI"])

Lastly, output the results:

1
2
3
4
5
6
7
8
9

for record in response["data"]:
    
    print("{:10s} {:8.4f} {:8.4f} {:8.4f} {:8.4f}".format(
            datetime.fromtimestamp(record["Date"]/1000.0).strftime("%Y-%m-%d"),
            record["Open"],
            record["Low"],
            record["High"],
            record["Close"]
    ))

Key Lookup with Expression

Since any column involved in a multi-head key lookup must be indexed, first add an index to the Date column, which will be used in the example filter:

1

h_db.alter_table("example.stocks_multihead", "create_index", "Date")

Then perform the multi-head key lookup with an extra filtering expression, searching the shard column for the value SPGI and filtering out records prior to 2017:

1

response = stocks_table.get_records_by_key(["SPGI"], "Date >= '2017-01-01'")

The results can be output in the same manner shown under Key Lookup.

Multi-Column Key Lookup

Grab a handle to the stocks_multihead_multicolumn table, passing the use_multihead_io option to enable background multi-head key lookups:

1

stocks_table = gpudb.GPUdbTable(None, name = "example.stocks_multihead_multicolumn", db = h_db, use_multihead_io = True)

Then perform the multi-head key lookup, searching the sharded Industry & Symbol columns for the values Financial Exchanges & Data & SPGI, respectively:

1
2
3

response = stocks_table.get_records_by_key(
        {"Industry": "Financial Exchanges & Data", "Symbol": "SPGI"}
)

The results can be output in the same manner shown under Key Lookup.

Multi-Column Key Lookup with Expression

Since any column involved in a multi-head key lookup must be indexed, first add an index to the Date column, which will be used in the example filter:

1

h_db.alter_table("example.stocks_multihead_multicolumn", "create_index", "Date")

Then perform the multi-head key lookup with an extra filtering expression, searching the sharded Industry & Symbol columns for the values Financial Exchanges & Data & SPGI, respectively, and filtering out records prior to 2017:

1
2
3
4

response = stocks_table.get_records_by_key(
        {"Industry": "Financial Exchanges & Data", "Symbol": "SPGI"},
        "Date >= '2017-01-01'"
)

The results can be output in the same manner shown under Key Lookup.

Result

The multi-head key lookup should consistently return faster than the same query without using multi-head. The results of a sample test run bear this out: