JavaScript Developer Manual¶
The following guide provides step-by-step instructions to get started writing JavaScript applications using Kinetica. This guide demonstrates only a small set of the available API. A detailed description of the complete interface is available under JavaScript API Reference.
API Download¶
The source code for the JavaScript API is available for download from the GitHub repository kineticadb/kinetica-api-javascript. Follow the instructions in the included README file to use 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:
var gpudb = new GPUdb( "http://localhost: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:
var my_type = {
"type": "record",
"name": "my_type_1",
"fields": [
{"name":"col1","type":"double"},
{"name":"col2","type":"string"},
{"name":"group_id","type":"string"}
]
};
var create_type_rsp = gpudb.create_type( JSON.stringify( my_type ), "my_type" );
var type_id = create_type_rsp.type_id;
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:
var create_table_rsp = gpudb.create_table( table_name, type_id );
Once the table is created, data can be inserted as follows:
// Generate records to be inserted
var records = [];
for (var i = 0; i < 10; i++)
{
var record = {
col1 : (i + 0.1),
col2 : ("string " + i),
group_id : "Group 1"
};
records.push( record );
}
// This option will return IDs per record with which we can refer
// to particular records later as needed
var insert_options = { "return_record_ids" : "true" }
var insert_records_rsp = gpudb.insert_records( table_name, records,
insert_options );
Retrieving Data¶
Once the table is populated with data, the data can be retrieved from the system
by a call to get_records() as shown below:
var get_records_rsp = gpudb.get_records( table_name );
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:
var view_name_1 = "view_1";
var expression = (col1 + " = 1.1");
var filter_rsp = gpudb.filter( table_name, view_name_1, expression );
To filter all the records matching a known list of values:
var view_name_2 = "view_2";
var column_values_map = {
col1 : [ "1.1", "2.1", "5.1" ]
};
var filter_by_list_rsp = gpudb.filter_by_list( table_name, view_name_2, column_values_map );
To retrieve a list of all the unique values for a column or a set of columns or expression:
var unique_rsp = gpudb.aggregate_unique( table_name, group_id, 0 )
Kinetica 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()
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:
var column_names = [ "group_id" ];
var group_by_rsp = gpudb.aggregate_group_by( table_name, column_names, 0 )
var column_names = [ "group_id", "count(*)", "sum(col1)", "avg(col1)" ];
group_by_rsp = gpudb.aggregate_group_by( table_name, column_names, 0 )
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:
var start = 1.1;
var end = 2;
var interval = 1;
var histogram_rsp = gpudb.aggregate_histogram( table_name, "col1", start, end, interval )
Complete Sample¶
Included below is a complete sample program containing all the above queries:
console.log( "Establishing a connection with the database..." );
var gpudb = new GPUdb( "http://localhost:9191" );
var show_table_rsp = gpudb.show_table( "", {} );
console.log( JSON.stringify( show_table_rsp ) );
// Declare the data type for the table
var my_type = {
"type": "record",
"name": "my_type_1",
"fields": [
{"name":"col1","type":"double"},
{"name":"col2","type":"string"},
{"name":"group_id","type":"string"}
]
};
// Convenient variables for referring to the columns
var col1 = "col1";
var col2 = "col2";
var group_id = "group_id";
// Register the data type and get the type's ID
var create_type_rsp = gpudb.create_type( JSON.stringify( my_type ), "my_type" );
var type_id = create_type_rsp.type_id;
// Create a table
var table_name = "my_table";
var create_table_rsp = gpudb.create_table( table_name, type_id );
// Generate records to be inserted
var records = [];
for (var i = 0; i < 10; i++)
{
var record = {
col1 : (i + 0.1),
col2 : ("string " + i),
group_id : "Group 1"
};
records.push( record );
}
// This option will return IDs per record with which we can refer
// to particular records later as needed
var insert_options = { "return_record_ids" : "true" }
var insert_records_rsp = gpudb.insert_records( table_name, records,
insert_options );
console.log( "Record IDs for newly inserted records: "
+ insert_records_rsp["record_ids"] );
// Fetch the records from the table
var get_records_rsp = gpudb.get_records( table_name );
console.log( "Retrieved records: " );
console.log( JSON.stringify( get_records_rsp["data"] ) );
// Perform a filter operation on the table
var view_name_1 = "view_1";
var expression = (col1 + " = 1.1");
var filter_rsp = gpudb.filter( table_name, view_name_1, expression );
console.log( "Number of filtered records: " + filter_rsp["count"] );
// Fetch the records from the view (like reading from a regular table)
var get_records_rsp = gpudb.get_records( view_name_1 );
console.log( "Filtered records: " );
console.log( JSON.stringify( get_records_rsp["data"] ) );
// Drop the view
gpudb.clear_table( view_name_1 );
// Perform a filter operation on the table on two columns
expression = (col1 + " <= 9 and " + group_id + '= "Group 1"');
filter_rsp = gpudb.filter( table_name, view_name_1, expression );
console.log( "Number of records filtered by the second expression: "
+ filter_rsp["count"] );
// Fetch the records from the view
get_records_rsp = gpudb.get_records( view_name_1 );
console.log( "Second set of filtered records: " );
console.log( JSON.stringify( get_records_rsp["data"] ) );
// Perform a filter by list operation
var view_name_2 = "view_2";
var column_values_map = {
col1 : [ "1.1", "2.1", "5.1" ]
};
var filter_by_list_rsp = gpudb.filter_by_list( table_name, view_name_2,
column_values_map );
console.log( "Number of records filtered by list: "
+ filter_by_list_rsp["count"] );
// Fetch the records from the second view
get_records_rsp = gpudb.get_records( view_name_2 );
console.log( "Records filtered by a list: " );
console.log( JSON.stringify( get_records_rsp["data"] ) );
// Perform a filter by range operation
var view_name_3 = "view_3";
var lower_bound = 1;
var upper_bound = 5;
var filter_by_range_rsp = gpudb.filter_by_range( table_name,
view_name_3,
col1,
lower_bound,
upper_bound );
console.log( "Number of records filtered by range: "
+ filter_by_range_rsp["count"] );
// Fetch the records from the second view
get_records_rsp = gpudb.get_records( view_name_3 );
console.log( "Records filtered by a range: " );
console.log( JSON.stringify( get_records_rsp["data"] ) );
// Perform an aggregate operation (statistics: sum, mean, count)
var statistics = "sum,mean,count"
var stats_rsp = gpudb.aggregate_statistics( table_name, col1, statistics )
console.log( "Statistics of values in 'col1': " + stats_rsp['stats'] );
// Insert some more records
console.log( "Inserting more records into the table..." );
var records = [];
for (var i = 1; i < 8; i++)
{
var record = {
col1 : (i + 10.1),
col2 : ("string " + i),
group_id : "Group 2" // unique from the first group of records
};
records.push( record );
}
gpudb.insert_records( table_name, records );
// Find all unique values of a given column
var unique_rsp = gpudb.aggregate_unique( table_name, group_id, 0 )
console.log( "Unique of values in 'group_id': " );
console.log( JSON.stringify( unique_rsp['data'] ) );
// Aggregate values of a given column by grouping by its values
var column_names = [ col2 ];
var group_by_rsp = gpudb.aggregate_group_by( table_name, column_names, 0 )
console.log( "Group by results: " );
console.log( JSON.stringify( group_by_rsp['data'] ) );
// Second group by
var column_names = [ group_id, "count(*)", "sum(col1)", "avg(col1)" ];
group_by_rsp = gpudb.aggregate_group_by( table_name, column_names, 0 )
console.log( "Second group by results: " );
console.log( JSON.stringify( group_by_rsp['data'] ) );
// Third group by
var column_names = [ group_id, "sum(col1*10)" ];
group_by_rsp = gpudb.aggregate_group_by( table_name, column_names, 0 )
console.log( "Third group by results: " );
console.log( JSON.stringify( group_by_rsp['data'] ) );
// Insert some more records
console.log( "Inserting more records into the table..." );
var records = [];
for (var i = 4; i < 10; i++)
{
var record = {
col1 : (i + 0.6),
col2 : ("string 2" + i),
group_id : "Group 1"
};
records.push( record );
}
gpudb.insert_records( table_name, records );
// Perform a histogram calculation
var start = 1.1;
var end = 2;
var interval = 1;
var histogram_rsp = gpudb.aggregate_histogram( table_name, col1,
start, end,
interval )
console.log( "Histogram results: " );
console.log( JSON.stringify( histogram_rsp ) );
// Drop the original table (will automatically drop all views of it)
gpudb.clear_table( table_name );
// Check that no view of that table is available anymore.
// Using a callback function to check the error status of the query.
gpudb.show_table( view_name_3, "",
function( err, data ) // callback function
{
if (err !== null)
{
console.log( "View '" + view_name_3 + "' not available as expected." );
}
} );
Output from above sample program
Establishing a connection with the database...
example.js:17 {"table_name":"","table_names":[],"is_collection":[],"is_view":[],"type_ids":[],"type_schemas":[],"type_labels":[],"properties":[],"ttls":[],"sizes":[],"full_sizes":[],"total_size":-1,"total_full_size":-1}
example.js:59 Record IDs for newly inserted records: 0020000002700000_0000000000000000,0020000002700000_0000000000000001,0020000002700000_0000000000000002,0020000002700000_0000000000000003,0020000002700000_0000000000000004,0020000002700000_0000000000000005,0020000002700000_0000000000000006,0020000002700000_0000000000000007,0020000002700000_0000000000000008,0020000002700000_0000000000000009
example.js:64 Retrieved records:
example.js:65 [{"col1":0.1,"col2":"string 0","group_id":"Group 1"},{"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"}]
example.js:71 Number of filtered records: 1
example.js:75 Filtered records:
example.js:76 [{"col1":1.1,"col2":"string 1","group_id":"Group 1"}]
example.js:84 Number of records filtered by the second expression: 9
example.js:89 Second set of filtered records:
example.js:90 [{"col1":0.1,"col2":"string 0","group_id":"Group 1"},{"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"}]
example.js:99 Number of records filtered by list: 3
example.js:104 Records filtered by a list:
example.js:105 [{"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"}]
example.js:116 Number of records filtered by range: 4
example.js:121 Records filtered by a range:
example.js:122 [{"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"}]
example.js:128 Statistics of values in 'col1': [object Object]
example.js:133 Inserting more records into the table...
example.js:149 Unique of values in 'group_id':
example.js:150 {"column_1":["Group 1","Group 2"],"column_headers":["group_id"]}
example.js:155 Group by results:
example.js:156 {"column_1":["string 0","string 1","string 2","string 3","string 4","string 5","string 6","string 7","string 8","string 9"],"column_2":[1,2,2,2,2,2,2,2,1,1],"column_headers":["col2","count(*)"]}
example.js:161 Second group by results:
example.js:162 {"column_1":["Group 1","Group 2"],"column_2":[10,7],"column_3":[46,98.69999999999999],"column_4":[4.6,14.1],"column_headers":["group_id","count(*)","sum(col1)","avg(col1)"]}
example.js:167 Third group by results:
example.js:168 {"column_1":["Group 1","Group 2"],"column_2":[460,987],"column_headers":["group_id","sum(col1*10)"]}
example.js:172 Inserting more records into the table...
example.js:193 Histogram results:
example.js:194 {"counts":[1],"start":1.1,"end":2}
example.js:206 View 'view_3' not available as expected.