The following guide provides step by step instructions to get started writing C++ applications using GPUdb. This guide demonstrates only a small set of the available API. A detailed description of the complete interface is available under C++ API Reference.
The source code for the C++ API is available for download from GitHub repository GPUdb/gpudb-api-cpp. Follow the instructions in the included README file to build the API library.
To connect to the GPUdb create an object of the GPUdb class providing the ipaddress and port number of the GPUdb server
gpudb::GPUdb h_db("http://127.0.0.1:9191");
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 create_type 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.asDouble(COL_1) = i;
datum.asString(COL_2) = "string";
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);
Once the table is populated with data, the data can be retrieved from the system by a call to get_records as shown below
gpudb::GetRecordsResponse<gpudb::GenericRecord> response = h_db.getRecords<gpudb::GenericRecord>(myType.getSchema(), 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.asDouble(COL_1);
}
The results are returned in the response along with the schema of the results. For large tables, the data can be easily be retrieved in smaller blocks by using the offset and limit parameters.
To filter and retrieve only a subset of the records, use the filter function 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 retrieve 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) << "\", ";
}
GPUdb supports various group by queries. To group by one or more columns and return the count of the unique values, use the aggregate_group_by function as shown below. The next query shows how to get the count, sum and mean of the values
std::vector<std::string> col_names;
col_names.push_back(COL_GROUP_ID);
col_names.push_back("count(*)");
col_names.push_back("sum(col1)");
col_names.push_back("avg(col1)");
gpudb::AggregateGroupByResponse 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;
GPUdb 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, COL_1, range_start, range_end, interval, options);
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, avro::ValidSchema 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");
// drops all existing tables from the system.
// h_db.clearTable("", "", options);
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);
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.getSchema(), 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.getSchema(), 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.getSchema(), 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.getSchema(), 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.getSchema(), 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.getSchema(), my_view3,0,100,options);
print_GetRecordsResponse(response);
// Insert New Records
insertRecordsLocal(h_db, myType.getSchema(), 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.value<double>(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::DynamicTableRecord& rec1 = aggrGB_resp.data[0];
for (size_t j = 0 ; j < rec1.getFieldCount() ; j++)
{
std::cout << rec1.getExpression(j) << '\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.getFieldCount() ; j++)
{
switch (record.getFieldType(j))
{
case avro::AVRO_STRING:
std::cout << '"' << record.value<std::string>(j) << "\"\t";
break;
case avro::AVRO_DOUBLE:
std::cout << record.value<double>(j) << '\t';
break;
case avro::AVRO_FLOAT:
std::cout << record.value<float>(j) << '\t';
break;
case avro::AVRO_INT:
std::cout << record.value<float>(j) << '\t';
break;
case avro::AVRO_LONG:
std::cout << record.value<float>(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.getFieldCount() ; j++)
{
std::cout << rec2.getExpression(j) << '\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.getFieldCount() ; j++)
{
switch (record.getFieldType(j))
{
case avro::AVRO_STRING:
std::cout << '"' << record.value<std::string>(j) << "\"\t";
break;
case avro::AVRO_DOUBLE:
std::cout << record.value<double>(j) << '\t';
break;
case avro::AVRO_FLOAT:
std::cout << record.value<float>(j) << '\t';
break;
case avro::AVRO_INT:
std::cout << record.value<float>(j) << '\t';
break;
case avro::AVRO_LONG:
std::cout << record.value<float>(j) << '\t';
break;
default:
std::cout << "!Unhandled Type!" << '\t';
}
}
std::cout << std::endl;
}
// Insert few more records
insertRecordsLocal(h_db, myType.getSchema(), 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, avro::ValidSchema 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.asDouble(COL_1) = i + col1_const;
std::ostringstream ostr;
ostr << col2_const << i;
datum.asString(COL_2) = ostr.str();
datum.asString(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.asDouble(COL_1)
<< ", \"" << COL_2 << "\":\"" << record.asString(COL_2) << std::flush
<< "\", \"" << COL_GROUP_ID << "\":\"" << record.asString(COL_GROUP_ID) << "\"\n";
}
}
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,