C++ Developer Manual¶
The following guide provides step-by-step instructions to get started writing C++ applications using Kinetica. This guide demonstrates only a small set of the available API. A detailed description of the complete interface is available under C++ API Reference.
API Download¶
The source code for the C++ API is available for download from the GitHub repository kineticadb/kinetica-api-cpp. Follow the instructions in the included README file to build the API library.
Connecting to the Database¶
To connect to the database, instantiate an object of the GPUdb class, providing the connection URL, including host & port of the database server:
gpudb::GPUdb h_db("http://127.0.0.1:9191");
Loading Data¶
Before any data can be loaded into the system, a Type needs to be defined in the system. The type definition is a JSON string describing the fields (i.e. columns) of the type along with a name for the type. Each field consists of a name and a data type:
const std::string COL_1 = "col1";
const std::string COL_2 = "col2";
const std::string COL_GROUP_ID = "group_id";
std::vector<gpudb::Type::Column> columns;
columns.push_back(gpudb::Type::Column(COL_1, avro::AVRO_DOUBLE));
columns.push_back(gpudb::Type::Column(COL_2, avro::AVRO_STRING));
columns.push_back(gpudb::Type::Column(COL_GROUP_ID,avro::AVRO_STRING));
gpudb::Type myType("my_type_1", columns);
std::string type_id_1 = myType.create(h_db);
The returned object from the Type.create() call contains a unique type
identifier allocated by the system. This identifier can then be used in the
request to create a new table as follows:
gpudb::CreateTableResponse response = h_db.createTable(my_table, type_id_1, options);
Once the table is created, data can be inserted as follows:
std::map<std::string, std::string> options;
std::vector<gpudb::GenericRecord> record_list;
for (int i = start ; i < end ; i++)
{
gpudb::GenericRecord datum(myTypeSchema);
datum.setAsDouble(COL_1,i + col1_const);
datum.setAsString(COL_2,ostr.str());
record_list.push_back(datum);
}
options["return_record_ids"] = "true"; // optionally request record_ids
gpudb::InsertRecordsResponse response = h_db.insertRecords(my_table, record_list, options);
Retrieving Data¶
Once the table is populated with data, the data can be retrieved from the system
by a call to getRecords() as shown below:
gpudb::GetRecordsResponse<gpudb::GenericRecord> response = h_db.getRecords<gpudb::GenericRecord>(myType, my_table, 0, 100, options);
for(unsigned int i = 0 ; i < response.data.size() ; ++i)
{
const gpudb::GenericRecord& record = response.data[i];
double d = record.getAsDouble(COL_1);
}
For large tables, the data can be easily be retrieved in smaller blocks by using
the offset and limit parameters. The returned response also contains
the schema (or data type) of the results.
Running Queries¶
To filter a subset of the records, use the filter() method with an
expression as follows:
std::string my_view("my_view_1");
my_filterExpr="col1 <= 9 and group_id=\"Group 1\"";
gpudb::FilterResponse filter_resp = h_db.filter(my_table, my_view, my_filterExpr, options);
To filter all the records matching a known list of values:
std::string my_view2("my_view_2");
std::map<std::string, std::vector<std::string> > my_filterList;
my_filterList["col1"].push_back("1.1");
my_filterList["col1"].push_back("2.1");
my_filterList["col1"].push_back("5.1");
gpudb::FilterByListResponse filterByList_resp = h_db.filterByList(my_view, my_view2, my_filterList, options);
To retrieve a list of all the unique values for a column or a set of columns or expression:
gpudb::AggregateUniqueResponse aggrUniq_resp = h_db.aggregateUnique(my_table, COL_GROUP_ID, 0, 100, options);
std::cout << "Unique values in group_id column " << aggrUniq_resp.data.size() << std::endl;
std::cout << "Schema " << aggrUniq_resp.responseSchemaStr << std::endl;
for (size_t i = 0 ; i < aggrUniq_resp.data.size(); ++i )
{
std::cout << '"' << record.value<std::string>(0) << "\", ";
}
Kinetica supports various group-by queries. To group by one or more columns
and return the count of the unique values, use the aggregateGroupBy() method
as shown below. The next query shows how to get the count of records, as well
as the sum and average of the values of column col1, within each group:
std::vector<std::string> col_names;
col_names.push_back("group_id");
gpudb::AggregateGroupByResponse aggrGB_resp = h_db.aggregateGroupBy(my_table, col_names, 0, 100, options);
col_names.push_back("count(*)");
col_names.push_back("sum(col1)");
col_names.push_back("avg(col1)");
aggrGB_resp = h_db.aggregateGroupBy(my_table, col_names, 0, 100, options);
Kinetica supports grouping numerical data into a histogram. The input range is divided into equal sized bins and the count of objects in each bin are returned:
double range_start = 0.0;
double range_end = 11;
double interval = 1;
gpudb::AggregateHistogramResponse aggrHist_resp = h_db.aggregateHistogram(my_table, "col1", range_start, range_end, interval, options);
Complete Sample¶
Included below is a complete sample program containing all the above queries:
#include <iostream>
#include <GPUdb.hpp>
const std::string COL_1 = "col1";
const std::string COL_2 = "col2";
const std::string COL_GROUP_ID = "group_id";
const std::string STR_GROUP_1_CONST = "Group 1";
const std::string STR_GROUP_2_CONST = "Group 2";
const std::string my_table("my_table_1");
// helper functions prototypes
void print_GetRecordsResponse(const gpudb::GetRecordsResponse<gpudb::GenericRecord>&);
void insertRecordsLocal(gpudb::GPUdb& h_db, gpudb::Type myTypeSchema, int start, int end, double col1_const, std::string col2_const, std::string group_id_const, bool display_record_ids = false);
int main(int argc, char *argv[])
{
std::map<std::string, std::string> options;
gpudb::GPUdb h_db("http://127.0.0.1:9191");
std::vector<gpudb::Type::Column> columns;
columns.push_back(gpudb::Type::Column(COL_1, gpudb::Type::Column::ColumnType::DOUBLE));
columns.push_back(gpudb::Type::Column(COL_2, gpudb::Type::Column::ColumnType::STRING));
columns.push_back(gpudb::Type::Column(COL_GROUP_ID, gpudb::Type::Column::ColumnType::STRING));
gpudb::Type myType("my_type_1", columns);
std::string type_id_1 = myType.create(h_db);
std::cout << "GPUdb generated type id for the new type - " << type_id_1 << std::endl;
try
{
gpudb::CreateTableResponse response = h_db.createTable(my_table, type_id_1, options);
}
catch (const std::exception& ex)
{
std::cout << "Caught Exception: " << ex.what() << std::endl;
return 10;
}
try
{
// Insert some records into the table
insertRecordsLocal(h_db, myType, 1, 10, 0.1, "string ", STR_GROUP_1_CONST, true);
// Call the helper function to Retrieve records from the table
gpudb::GetRecordsResponse<gpudb::GenericRecord> response = h_db.getRecords<gpudb::GenericRecord>(myType, my_table, 0, 100, options);
print_GetRecordsResponse(response);
// Filter the records by an expression into a view
std::string my_view("my_view_1");
std::string my_filterExpr("col1=1.1");
gpudb::FilterResponse filter_resp = h_db.filter(my_table, my_view, my_filterExpr, options);
std::cout << "Number of records returned by filter expresion: " << filter_resp.count << std::endl;
// Retrieve records from the view
response = h_db.getRecords<gpudb::GenericRecord>(myType, my_view, 0, 100, options);
print_GetRecordsResponse(response);
// Drop the view (same function as dropping a table)
gpudb::ClearTableResponse clrTbl_Resp = h_db.clearTable(my_view, "", options);
// Apply a filter condition with two columns
my_filterExpr = "col1 <= 9 and group_id=\"Group 1\"";
filter_resp = h_db.filter(my_table, my_view, my_filterExpr, options);
std::cout << "Number of records returned by second filter expresion: " << filter_resp.count << std::endl;
response = h_db.getRecords<gpudb::GenericRecord>(myType, my_view, 0, 100, options);
print_GetRecordsResponse(response);
// Filter by a list of values. Note also query chaining - query run on another view.
std::string my_view2("my_view_2");
std::map<std::string, std::vector<std::string> > my_filterList;
my_filterList["col1"].push_back("1.1");
my_filterList["col1"].push_back("2.1");
my_filterList["col1"].push_back("5.1");
gpudb::FilterByListResponse filterByList_resp = h_db.filterByList(my_view, my_view2, my_filterList, options);
std::cout << "Number of records returned by filter by list expresion " << filterByList_resp.count << std::endl;
response = h_db.getRecords<gpudb::GenericRecord>(myType, my_view2, 0, 100, options);
print_GetRecordsResponse(response);
// Filter by a range
std::string my_view3("my_view_3");
gpudb::FilterByRangeResponse filterByRange_resp = h_db.filterByRange(my_view, my_view3, COL_1, 1, 5, options);
std::cout << "Number of records returned by filter by range expresion " << filterByRange_resp.count << std::endl;
response = h_db.getRecords<gpudb::GenericRecord>(myType, my_view3, 0, 100, options);
print_GetRecordsResponse(response);
// Insert New Records
insertRecordsLocal(h_db, myType, 1, 8, 10.1, "string ", STR_GROUP_2_CONST);
// Retrieve unique values for a column
gpudb::AggregateUniqueResponse aggrUniq_resp = h_db.aggregateUnique(my_table, COL_GROUP_ID, 0, 100, options);
std::cout << "Unique values in group_id column " << aggrUniq_resp.data.size() << std::endl;
std::cout << "Schema " << aggrUniq_resp.responseSchemaStr << std::endl;
for (size_t i = 0; i < aggrUniq_resp.data.size(); ++i)
{
const gpudb::DynamicTableRecord& record = aggrUniq_resp.data[i];
// already know the returned column and its type ; just print it.
assert(record.getFieldCount() == 1);
std::cout << '"' << record.value<std::string>(0) << "\", ";
}
// "Group by" query
std::cout << "Calling aggregateGroupBy on a single column. ";
std::vector<std::string> col_names;
col_names.push_back(COL_2);
gpudb::AggregateGroupByResponse aggrGB_resp = h_db.aggregateGroupBy(my_table, col_names, 0, 100, options);
std::cout << "Number of unique values in col2 " << aggrGB_resp.data.size() << std::endl;
std::cout << "Schema " << aggrGB_resp.responseSchemaStr << std::endl;
for (size_t i = 0; i < aggrGB_resp.data.size(); ++i)
{
const gpudb::DynamicTableRecord& record = aggrGB_resp.data[i];
assert(record.getFieldCount() == 2);
std::cout << '"' << record.value<std::string>(0) << '"';
std::cout << ':' << record.getAsDouble(1) << std::endl;
}
std::cout << "Calling aggregateGroupBy to retrieve stats on a column. ";
col_names.clear();
col_names.push_back(COL_GROUP_ID);
col_names.push_back("count(*)");
col_names.push_back("sum(col1)");
col_names.push_back("avg(col1)");
aggrGB_resp = h_db.aggregateGroupBy(my_table, col_names, 0, 100, options);
std::cout << "Number of unique values in group_id column " << aggrGB_resp.data.size() << std::endl;
// std::cout << "Schema " << aggrGB_resp.responseSchemaStr << std::endl;
// Parse the group by response.
const gpudb::GenericRecord& rec1 = aggrGB_resp.data[0];
for (size_t j = 0; j < rec1.getType().getColumnCount(); j++)
{
std::cout << rec1.getType().getColumn(j).getName() << '\t';
}
std::cout << std::endl;
// print the values from the response
for (size_t i = 0; i < aggrGB_resp.data.size(); ++i)
{
const gpudb::DynamicTableRecord& record = aggrGB_resp.data[i];
assert(record.getFieldCount() == col_names.size());
for (size_t j = 0; j < record.getType().getColumnCount(); j++)
{
switch (record.getType().getColumn(j).getType())
{
case gpudb::Type::Column::STRING:
std::cout << '"' << record.value<std::string>(j) << "\"\t";
break;
case gpudb::Type::Column::DOUBLE:
std::cout << record.getAsDouble(j) << '\t';
break;
case gpudb::Type::Column::FLOAT:
std::cout << record.getAsFloat(j) << '\t';
break;
case gpudb::Type::Column::INT:
std::cout << record.getAsInt(j) << '\t';
break;
case gpudb::Type::Column::LONG:
std::cout << record.getAsLong(j) << '\t';
break;
default:
std::cout << "!Unhandled Type!" << '\t';
}
}
std::cout << std::endl;
}
// expressions in group by
std::cout << "Calling aggregateGroupBy for an expression. ";
col_names.clear();
col_names.push_back(COL_GROUP_ID);
col_names.push_back("sum(col1*10)");
aggrGB_resp = h_db.aggregateGroupBy(my_table, col_names, 0, 100, options);
std::cout << "Unique values in group_id column " << aggrGB_resp.data.size() << std::endl;
std::cout << "Schema " << aggrGB_resp.responseSchemaStr << std::endl;
const gpudb::DynamicTableRecord& rec2 = aggrGB_resp.data[0];
for (size_t j = 0; j < rec2.getType().getColumnCount(); j++)
{
std::cout << rec2.getType().getColumn(j).getName() << '\t';
}
std::cout << std::endl;
for (size_t i = 0; i < aggrGB_resp.data.size(); ++i)
{
const gpudb::DynamicTableRecord& record = aggrGB_resp.data[i];
assert(record.getFieldCount() == col_names.size());
for (size_t j = 0; j < record.getType().getColumnCount(); j++)
{
switch (record.getType().getColumn(j).getType())
{
case gpudb::Type::Column::STRING:
std::cout << '"' << record.value<std::string>(j) << "\"\t";
break;
case gpudb::Type::Column::DOUBLE:
std::cout << record.getAsDouble(j) << '\t';
break;
case gpudb::Type::Column::FLOAT:
std::cout << record.getAsFloat(j) << '\t';
break;
case gpudb::Type::Column::INT:
std::cout << record.getAsInt(j) << '\t';
break;
case gpudb::Type::Column::LONG:
std::cout << record.getAsLong(j) << '\t';
break;
default:
std::cout << "!Unhandled Type!" << '\t';
}
}
std::cout << std::endl;
}
// Insert few more records
insertRecordsLocal(h_db, myType, 4, 10, 0.6, "string 2", STR_GROUP_1_CONST);
// Aggregate Histogram
std::cout << "Calling aggregateHistogram. ";
double range_start = 0.0;
double range_end = 11;
double interval = 1;
gpudb::AggregateHistogramResponse aggrHist_resp = h_db.aggregateHistogram(my_table, COL_1, range_start, range_end, interval, options);
std::cout << "Num Histogram bins: " << aggrHist_resp.counts.size()
<< " Start: " << aggrHist_resp.start
<< " end: " << aggrHist_resp.end << std::endl;
std::cout << "Count per bin - ";
std::copy(aggrHist_resp.counts.begin(), aggrHist_resp.counts.end(), std::ostream_iterator<int>(std::cout, ", "));
std::cout << std::endl;
}
catch (const std::exception& ex)
{
std::cout << "Caught Exception: " << ex.what() << std::endl;
return 1;
}
return 0;
}
// Helper functions
// Insert new records to the table
void insertRecordsLocal(gpudb::GPUdb& h_db, gpudb::Type myTypeSchema, int start, int end, double col1_const, std::string col2_const, std::string group_id_const, bool display_record_ids)
{
std::map<std::string, std::string> options;
std::vector<gpudb::GenericRecord> record_list;
for (int i = start; i < end; i++)
{
gpudb::GenericRecord datum(myTypeSchema);
datum.setAsDouble(COL_1,i + col1_const);
std::ostringstream ostr;
ostr << col2_const << i;
datum.setAsString(COL_2,ostr.str());
datum.setAsString(COL_GROUP_ID,group_id_const);
record_list.push_back(datum);
}
options["return_record_ids"] = "true"; // optionally request record_ids
gpudb::InsertRecordsResponse response = h_db.insertRecords(my_table, record_list, options);
options.clear();
if (display_record_ids)
{
std::cout << "Record Ids for " << response.countInserted << " new records - [";
std::copy(response.recordIds.begin(), response.recordIds.end() - 1, std::ostream_iterator<std::string>(std::cout, "', '"));
std::cout << "'" << *(response.recordIds.end() - 1) << "']" << std::endl;
}
else
{
std::cout << response.countInserted << " new records inserted." << std::endl;
}
}
// Print the records from the table
void print_GetRecordsResponse(const gpudb::GetRecordsResponse<gpudb::GenericRecord>& response)
{
// std::cout << "response schema - " << response.typeSchema << std::endl;
for (unsigned int i = 0; i < response.data.size(); ++i)
{
const gpudb::GenericRecord& record = response.data[i];
std::cout << "\"" << COL_1 << "\":" << record.getAsDouble(COL_1)
<< ", \"" << COL_2 << "\":\"" << record.getAsString(COL_2) << std::flush
<< "\", \"" << COL_GROUP_ID << "\":\"" << record.getAsString(COL_GROUP_ID) << "\"\n";
}
}
Output from above sample program
GPUdb generated type id for the new type - 460479593343997452
Record Ids for 9 new records - [0010000010e00000_0000000000000000', '0010000010e00000_0000000000000001', '0010000010e00000_0000000000000002', '0010000010e00000_0000000000000003', '0010000010e00000_0000000000000004', '0010000010e00000_0000000000000005', '0010000010e00000_0000000000000006', '0010000010e00000_0000000000000007', ''0010000010e00000_0000000000000008']
"col1":1.1, "col2":"string 1", "group_id":"Group 1"
"col1":2.1, "col2":"string 2", "group_id":"Group 1"
"col1":3.1, "col2":"string 3", "group_id":"Group 1"
"col1":4.1, "col2":"string 4", "group_id":"Group 1"
"col1":5.1, "col2":"string 5", "group_id":"Group 1"
"col1":6.1, "col2":"string 6", "group_id":"Group 1"
"col1":7.1, "col2":"string 7", "group_id":"Group 1"
"col1":8.1, "col2":"string 8", "group_id":"Group 1"
"col1":9.1, "col2":"string 9", "group_id":"Group 1"
Number of records returned by filter expresion: 1
"col1":1.1, "col2":"string 1", "group_id":"Group 1"
Number of records returned by second filter expresion: 8
"col1":1.1, "col2":"string 1", "group_id":"Group 1"
"col1":2.1, "col2":"string 2", "group_id":"Group 1"
"col1":3.1, "col2":"string 3", "group_id":"Group 1"
"col1":4.1, "col2":"string 4", "group_id":"Group 1"
"col1":5.1, "col2":"string 5", "group_id":"Group 1"
"col1":6.1, "col2":"string 6", "group_id":"Group 1"
"col1":7.1, "col2":"string 7", "group_id":"Group 1"
"col1":8.1, "col2":"string 8", "group_id":"Group 1"
Number of records returned by filter by list expresion 3
"col1":1.1, "col2":"string 1", "group_id":"Group 1"
"col1":2.1, "col2":"string 2", "group_id":"Group 1"
"col1":5.1, "col2":"string 5", "group_id":"Group 1"
Number of records returned by filter by range expresion 4
"col1":1.1, "col2":"string 1", "group_id":"Group 1"
"col1":2.1, "col2":"string 2", "group_id":"Group 1"
"col1":3.1, "col2":"string 3", "group_id":"Group 1"
"col1":4.1, "col2":"string 4", "group_id":"Group 1"
7 new records inserted.
Unique values in group_id column 2
"Group 1", "Group 2", Calling aggregateGroupBy on a single column. Number of unique values in col2 9
"string 1":2
"string 2":2
"string 3":2
"string 4":2
"string 5":2
"string 6":2
"string 7":2
"string 8":1
"string 9":1
Calling aggregateGroupBy to retrieve stats on a column. Number of unique values in group_id column 2
group_id count(*) sum(col1) avg(col1)
"Group 1" 9 45.9 0
"Group 2" 7 98.7 0
Calling aggregateGroupBy for an expression. Unique values in group_id column 2
group_id sum(col1*10)
"Group 1" 459
"Group 2" 987
6 new records inserted.
Calling aggregateHistogram. Num Histogram bins: 11 Start: 0 end: 11
Count per bin - 0, 1, 1, 1, 2, 2, 2, 2, 2, 2, 0,