The following guide provides step-by-step instructions to get started writing Java applications using Kinetica. This guide demonstrates only a small set of the available API. A detailed description of the complete interface is available under Java API Reference.
We suggest using Maven as the build tool for your Java project. To use the Kinetica Java API, you must add our Nexus repository and the Kinetica Java API dependency that matches that of the targeted Kinetica database, as shown below:
<properties>
<gpudb-api.version>6.0.0</gpudb-api.version>
<repository>
<id>gpudb-releases</id>
<url>http://files.kinetica.com/nexus/content/repositories/releases/</url>
</repository>
<dependency>
<groupId>com.gpudb</groupId>
<artifactId>gpudb-api</artifactId>
<version>${gpudb-api.version}</version>
<type>jar</type>
</dependency>
The source code for the Java API is also available for download from the GitHub repository kineticadb/kinetica-api-java. Follow the instructions in the included README file to build the API library.
To connect to the database, instantiate an object of the GPUdb class, providing the connection URL, including host & port of the database server:
gpudb = new GPUdb("http://localhost:9191");
Before any data can be loaded into the system, a Type needs to be defined in the system. The type is a class, extended from RecordObject, using annotations to describe which class instance variables are fields (i.e. columns), what type they are, and any special handling they should receive. Each field consists of a name and a data type:
public static class WeatherRecord extends RecordObject
{
/* Create column(s), establish its ordering, give it property sub-type(s), give it a
column type, and give it a name.
*/
@RecordObject.Column(order = 0, properties = { "char16" })
public String city;
@RecordObject.Column(order = 1, properties = { "char32" })
public String state_province;
@RecordObject.Column(order = 2, properties = { "char16" })
public String country;
@RecordObject.Column(order = 3)
public double x;
@RecordObject.Column(order = 4)
public double y;
@RecordObject.Column(order = 5)
public double avg_temp;
@RecordObject.Column(order = 6, properties = { "char8" })
public String time_zone;
public WeatherRecord() {}
// Create a constructor for the class that will take parameters so that Bulk Inserting is easier
public WeatherRecord(String city, String stateProvince, String country, double x, double y, double avgTemp, String timeZone)
{
this.city = city;
this.state_province = stateProvince;
this.country = country;
this.x = x;
this.y = y;
this.avg_temp = avgTemp;
this.time_zone = timeZone;
}
}
Note: Although a constructor is not required, if the class does have any constructors, it must have a constructor with no parameters. Other constructors can be added, as necessary
Next, the type needs to be created:
/* Create the type in the database and save the type ID, needed to create
a table in the next step; see WeatherRecord class above for type
definition
*/
String typeId = RecordObject.createType(WeatherRecord.class, gpudb);
The returned object from the 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.createTable(TABLE_WEATHER, typeId, null);
Once the table is created, data can be inserted into it. There is a convenience class called BulkInserter, which facilitates inserting records into a table in batches, documented under Ingestion. For this tutorial, only the native Java API call insertRecords() will be shown.
// Create a record object and assign values to properties
WeatherRecord weatherRecord = new WeatherRecord();
weatherRecord.city = "Washington, D.C.";
weatherRecord.state_province = "--";
weatherRecord.country = "USA";
weatherRecord.x = -77.016389d;
weatherRecord.y = 38.904722d;
weatherRecord.avg_temp = 58.5;
weatherRecord.time_zone = "UTC-5";
// Insert the record within a list into the table
int numInserted = gpudb.insertRecords(TABLE_WEATHER, Arrays.asList(weatherRecord), null).getCountInserted();
System.out.println("Number of single records inserted: " + numInserted);
// Add weather records to a list
List<WeatherRecord> weatherRecords = new ArrayList<WeatherRecord>();
weatherRecords.add(new WeatherRecord("Paris", "TX", "USA", -95.547778d, 33.6625d, 64.6, "UTC-6"));
weatherRecords.add(new WeatherRecord("Memphis", "TN", "USA", -89.971111d, 35.1175d, 63, "UTC-6"));
weatherRecords.add(new WeatherRecord("Sydney", "Nova Scotia", "Canada", -60.19551d, 46.13631d, 44.5, "UTC-4"));
weatherRecords.add(new WeatherRecord("La Paz", "Baja California Sur", "Mexico", -110.310833d, 24.142222d, 77, "UTC-7"));
weatherRecords.add(new WeatherRecord("St. Petersburg", "FL", "USA", -82.64d, 27.773056d, 74.5, "UTC-5"));
weatherRecords.add(new WeatherRecord("Oslo", "--", "Norway", 10.75d, 59.95d, 45.5, "UTC+1"));
weatherRecords.add(new WeatherRecord("Paris", "--", "France", 2.3508d, 48.8567d, 56.5, "UTC+1"));
weatherRecords.add(new WeatherRecord("Memphis", "--", "Egypt", 31.250833d, 29.844722d, 73, "UTC+2"));
weatherRecords.add(new WeatherRecord("St. Petersburg", "--", "Russia", 30.3d, 59.95d, 43.5, "UTC+3"));
weatherRecords.add(new WeatherRecord("Lagos", "Lagos", "Nigeria", 3.384082d, 6.455027d, 83, "UTC+1"));
weatherRecords.add(new WeatherRecord("La Paz", "Pedro Domingo Murillo", "Bolivia", -68.15d, -16.5d, 44, "UTC-4"));
weatherRecords.add(new WeatherRecord("Sao Paulo", "Sao Paulo", "Brazil", -46.633333d, -23.55d, 69.5, "UTC-3"));
weatherRecords.add(new WeatherRecord("Santiago", "Santiago Province", "Chile", -70.666667d, -33.45d, 62, "UTC-4"));
weatherRecords.add(new WeatherRecord("Buenos Aires", "--", "Argentina", -58.381667d, -34.603333d, 65, "UTC-3"));
weatherRecords.add(new WeatherRecord("Manaus", "Amazonas", "Brazil", -60.016667d, -3.1d, 83.5, "UTC-4"));
weatherRecords.add(new WeatherRecord("Sydney", "New South Wales", "Australia", 151.209444d, -33.865d, 63.5, "UTC+10"));
weatherRecords.add(new WeatherRecord("Auckland", "--", "New Zealand", 174.74d, -36.840556d, 60.5, "UTC+12"));
weatherRecords.add(new WeatherRecord("Jakarta", "--", "Indonesia", 106.816667d, -6.2d, 83, "UTC+7"));
weatherRecords.add(new WeatherRecord("Hobart", "--", "Tasmania", 147.325d, -42.880556d, 56, "UTC+10"));
weatherRecords.add(new WeatherRecord("Perth", "Western Australia", "Australia", 115.858889d, -31.952222d, 68, "UTC+8"));
// Insert the records into the table
numInserted = gpudb.insertRecords(TABLE_WEATHER, weatherRecords, null).getCountInserted();
System.out.println("Number of batch records inserted: " + numInserted);
Once the table is populated with data, the data can be retrieved from the system by a call to getRecords(tableName, offset, limit, options), using in-line parameter-passing.
// Retrieve no more than 10 records from weather_table using in-line
// request parameters
Map<String,String> options = GPUdb.options(GetRecordsRequest.Options.SORT_BY, COL_CITY);
GetRecordsResponse<WeatherRecord> grResp = gpudb.getRecords(TABLE_WEATHER, 0, 10, options);
System.out.printf("City State/Province Country Latitude Longitude Avg. Temp Time Zone%n");
System.out.printf("==================== ========================= =============== ========== =========== ========= =========%n");
for (WeatherRecord w : grResp.getData())
System.out.printf
(
"%-20s %-25s %-15s %10.6f %11.6f %9.1f %s%n",
w.city, w.state_province, w.country, w.y, w.x, w.avg_temp, w.time_zone
);
One can also invoke getRecords(request) using the GetRecordsRequest request class. This object contains all the parameters for the method call, and can be reused in successive calls, avoiding re-specifying query parameters.
// Retrieve no more than 25 of the remaining records from weather table
// using a request object
GetRecordsRequest grReq = new GetRecordsRequest();
grReq.setTableName(TABLE_WEATHER);
grReq.setOffset(10);
grReq.setLimit(25);
grReq.setOptions(options);
grResp = gpudb.getRecords(grReq);
for (WeatherRecord w : grResp.getData())
System.out.printf
(
"%-20s %-25s %-15s %10.6f %11.6f %9.1f %s%n",
w.city, w.state_province, w.country, w.y, w.x, w.avg_temp, w.time_zone
);
System.out.println("\nNumber of records in new table: " + grResp.getTotalNumberOfRecords());
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.
Also, note that all query related methods have the above two versions--with the request object and with the parameters passed directly to the method.
Filters are an easy way to filter down a larger table into more concise views.
/* Filter records where column x is less than 0, i.e, cities in the
* western hemisphere, and store the filter in a view
*/
FilterResponse fResp = gpudb.filter(TABLE_WEATHER, VIEW_WEATHER_W, "x < 0", null);
System.out.println("Number of records in the western hemisphere: " + fResp.getCount());
/* Filter records where column x is less than 0 and column y is greater
* than 0, i.e., cities in the northwestern semi-hemisphere, and store
* the filter in a view
*/
fResp = gpudb.filter(TABLE_WEATHER, VIEW_WEATHER_NW, "x < 0 and y > 0", null);
System.out.println("Number of records in the northwestern semi-hemisphere: " + fResp.getCount());
/* Filter records using the same expressions as Example 2, but using
* query chaining this time
*/
// Clear the view, as it will be recreated with chained query
gpudb.clearTable(VIEW_WEATHER_NW, null, null);
fResp = gpudb.filter(VIEW_WEATHER_W, VIEW_WEATHER_NW, "y > 0", null);
System.out.println("Number of records in the northwestern semi-hemisphere (with query chaining): " + fResp.getCount());
/* Filter by list where country name is USA, Brazil, or Australia */
Map<String, List<String>> columnValuesMap = new HashMap<>();
columnValuesMap.put(COL_COUNTRY, Arrays.asList("USA", "Brazil", "Australia"));
FilterByListResponse fblResp = gpudb.filterByList(TABLE_WEATHER, VIEW_WEATHER_COUNTRY, columnValuesMap, null);
System.out.println("Number of records where country name is USA, Brazil, or Australia: " + fblResp.getCount());
/* Filter by range cities that are east of GMT (the Prime Meridian) */
FilterByRangeResponse fbrResp = gpudb.filterByRange(TABLE_WEATHER, VIEW_WEATHER_E, COL_LONG, 0, 180, null);
System.out.println("Number of records that are east of the Prime Meridian (x > 0): " + fbrResp.getCount());
Kinetica supports various aggregate and group-by queries, which group and aggregate your data to return counts and useful statistics.
/* Aggregate count, min, mean, and max on the average temperature */
Map<String,Double> stats = gpudb.aggregateStatistics(TABLE_WEATHER, COL_AVG_TEMP, "count,min,max,mean", null).getStats();
System.out.println("Statistics of values in the average temperature column:");
System.out.printf
(
"\tCount: %.0f%n\tMin: %4.2f%n\tMean: %4.2f%n\tMax: %4.2f%n%n",
stats.get("count"), stats.get("min"), stats.get("mean"), stats.get("max")
);
/* Find unique city names */
List<Record> weatherLocs = gpudb.aggregateUnique(TABLE_WEATHER, COL_CITY, 0, 25, null).getData();
System.out.println("Unique city names:");
for (Record weatherLoc : weatherLocs)
System.out.println("\t* " + weatherLoc.get(COL_CITY));
/* Find number of weather locations per country in the northwestern
* semi-hemisphere; use binary decoding
*/
List<Record> countries = gpudb.aggregateGroupBy(VIEW_WEATHER_NW, Arrays.asList(COL_COUNTRY), 0, 25, null).getData();
System.out.println("Weather locations per country in the northwest semi-hemisphere:");
for (Record country : countries)
System.out.printf("\t%-10s%2d%n", country.get(COL_COUNTRY) + ":", country.get("count"));
/* Filter table to southeastern semi-hemisphere records, group by
* country, and aggregate min, max, and mean on the average temperature;
* use JSON decoding
*/
gpudb.filter(TABLE_WEATHER, VIEW_WEATHER_SE, "x > 0 and y < 0", null);
List<String> agbCols = Arrays.asList(COL_COUNTRY, "min(" + COL_AVG_TEMP + ")", "max(" + COL_AVG_TEMP + ")", "mean(" + COL_AVG_TEMP + ")");
countries = gpudb.aggregateGroupBy(VIEW_WEATHER_SE, agbCols, 0, 25, null).getData();
System.out.println("SE Semi-Hemi Country Min Mean Max ");
System.out.println("==================== ===== ===== =====");
for (Record c : countries)
System.out.printf
(
"%-20s %5.2f %5.2f %5.2f%n",
c.get(agbCols.get(0)), c.get(agbCols.get(1)), c.get(agbCols.get(3)), c.get(agbCols.get(2))
);
/* Filter for southern hemisphere cities and create a histogram for the
* average temperature of those cities (divided into every 10 degrees,
* e.g., 40s, 50s, 60s, etc.)
*/
gpudb.filter(TABLE_WEATHER, VIEW_WEATHER_HIST, "y < 0", null);
double start = 40;
double end = 90;
double interval = 10;
List<Double> histoGroups = gpudb.aggregateHistogram(VIEW_WEATHER_HIST, COL_AVG_TEMP, start, end, interval, null).getCounts();
System.out.println("Number of southern hemisphere cities with average temps in the given ranges:");
for (int hgNum = 0; hgNum < histoGroups.size(); hgNum++)
System.out.printf("\t%.0fs: %2.0f%n", start + hgNum * interval, histoGroups.get(hgNum));
You can delete records from a table using filter expressions. This method allows you to specify multiple filter expressions--note that each expression is used to delete records independently from the others (i.e., a record only needs to meet any one expression's criteria to be deleted from the table).
/* Filter for cities that are either south of latitude -50 or west of
* longitude -50 to determine how many records will be deleted; delete
* the records, then confirm the deletion by refiltering
*/
String deleteExpression = "x < -50 or y < -50";
fResp = gpudb.filter(TABLE_WEATHER, null, deleteExpression, null);
System.out.println("Number of records that meet deletion criteria before deleting: " + fResp.getCount());
gpudb.deleteRecords(TABLE_WEATHER, Arrays.asList(deleteExpression), null);
fResp = gpudb.filter(TABLE_WEATHER, null, deleteExpression, null);
System.out.println("Number of records that meet deletion criteria after deleting: " + fResp.getCount());
Included below is a complete sample program containing all the above queries:
import com.gpudb.GPUdb;
import com.gpudb.GPUdbException;
import com.gpudb.Record;
import com.gpudb.RecordObject;
import com.gpudb.Type;
import com.gpudb.protocol.*;
import java.util.*;
/*
* Java API Programming Tutorial
*
* Covered here: importing GPUdb, instantiating Kinetica, creating a type,
* creating a table, inserting records, retrieving records, filtering records,
* aggregating/grouping records, and deleting records.
*/
public class Tutorial
{
public static class WeatherRecord extends RecordObject
{
/* Create column(s), establish its ordering, give it property sub-type(s), give it a
column type, and give it a name.
*/
@RecordObject.Column(order = 0, properties = { "char16" })
public String city;
@RecordObject.Column(order = 1, properties = { "char32" })
public String state_province;
@RecordObject.Column(order = 2, properties = { "char16" })
public String country;
@RecordObject.Column(order = 3)
public double x;
@RecordObject.Column(order = 4)
public double y;
@RecordObject.Column(order = 5)
public double avg_temp;
@RecordObject.Column(order = 6, properties = { "char8" })
public String time_zone;
public WeatherRecord() {}
// Create a constructor for the class that will take parameters so that Bulk Inserting is easier
public WeatherRecord(String city, String stateProvince, String country, double x, double y, double avgTemp, String timeZone)
{
this.city = city;
this.state_province = stateProvince;
this.country = country;
this.x = x;
this.y = y;
this.avg_temp = avgTemp;
this.time_zone = timeZone;
}
}
public static void main(String[] args) throws GPUdbException
{
System.out.println("TUTORIAL OUTPUT");
System.out.println("===============\n");
// Columns and table names used in queries below
final String COL_COUNTRY = "country";
final String COL_CITY = "city";
final String COL_LONG = "x";
final String COL_AVG_TEMP = "avg_temp";
final String TABLE_WEATHER = "weather";
final String VIEW_WEATHER_W = "weather_west";
final String VIEW_WEATHER_NW = "weather_northwest";
final String VIEW_WEATHER_COUNTRY = "weather_country";
final String VIEW_WEATHER_E = "weather_east";
final String VIEW_WEATHER_SE = "weather_southeast";
final String VIEW_WEATHER_HIST = "weather_histogram";
// Establish connection with a locally-running instance of Kinetica
GPUdb gpudb = new GPUdb("http://localhost:9191");
System.out.println();
System.out.println("CREATING A TYPE & TABLE");
System.out.println("-----------------------");
System.out.println();
/* Create the type in the database and save the type ID, needed to create
a table in the next step; see WeatherRecord class above for type
definition
*/
String typeId = RecordObject.createType(WeatherRecord.class, gpudb);
// Create the table from the type
if (gpudb.hasTable(TABLE_WEATHER, null).getTableExists())
gpudb.clearTable(TABLE_WEATHER, null, null);
gpudb.createTable(TABLE_WEATHER, typeId, null);
System.out.println("Table created: OK");
System.out.println("\n");
System.out.println("INSERTING DATA");
System.out.println("--------------");
System.out.println();
// Single record example
// Create a record object and assign values to properties
WeatherRecord weatherRecord = new WeatherRecord();
weatherRecord.city = "Washington, D.C.";
weatherRecord.state_province = "--";
weatherRecord.country = "USA";
weatherRecord.x = -77.016389d;
weatherRecord.y = 38.904722d;
weatherRecord.avg_temp = 58.5;
weatherRecord.time_zone = "UTC-5";
// Insert the record within a list into the table
int numInserted = gpudb.insertRecords(TABLE_WEATHER, Arrays.asList(weatherRecord), null).getCountInserted();
System.out.println("Number of single records inserted: " + numInserted);
// Multiple records example
// Add weather records to a list
List<WeatherRecord> weatherRecords = new ArrayList<WeatherRecord>();
weatherRecords.add(new WeatherRecord("Paris", "TX", "USA", -95.547778d, 33.6625d, 64.6, "UTC-6"));
weatherRecords.add(new WeatherRecord("Memphis", "TN", "USA", -89.971111d, 35.1175d, 63, "UTC-6"));
weatherRecords.add(new WeatherRecord("Sydney", "Nova Scotia", "Canada", -60.19551d, 46.13631d, 44.5, "UTC-4"));
weatherRecords.add(new WeatherRecord("La Paz", "Baja California Sur", "Mexico", -110.310833d, 24.142222d, 77, "UTC-7"));
weatherRecords.add(new WeatherRecord("St. Petersburg", "FL", "USA", -82.64d, 27.773056d, 74.5, "UTC-5"));
weatherRecords.add(new WeatherRecord("Oslo", "--", "Norway", 10.75d, 59.95d, 45.5, "UTC+1"));
weatherRecords.add(new WeatherRecord("Paris", "--", "France", 2.3508d, 48.8567d, 56.5, "UTC+1"));
weatherRecords.add(new WeatherRecord("Memphis", "--", "Egypt", 31.250833d, 29.844722d, 73, "UTC+2"));
weatherRecords.add(new WeatherRecord("St. Petersburg", "--", "Russia", 30.3d, 59.95d, 43.5, "UTC+3"));
weatherRecords.add(new WeatherRecord("Lagos", "Lagos", "Nigeria", 3.384082d, 6.455027d, 83, "UTC+1"));
weatherRecords.add(new WeatherRecord("La Paz", "Pedro Domingo Murillo", "Bolivia", -68.15d, -16.5d, 44, "UTC-4"));
weatherRecords.add(new WeatherRecord("Sao Paulo", "Sao Paulo", "Brazil", -46.633333d, -23.55d, 69.5, "UTC-3"));
weatherRecords.add(new WeatherRecord("Santiago", "Santiago Province", "Chile", -70.666667d, -33.45d, 62, "UTC-4"));
weatherRecords.add(new WeatherRecord("Buenos Aires", "--", "Argentina", -58.381667d, -34.603333d, 65, "UTC-3"));
weatherRecords.add(new WeatherRecord("Manaus", "Amazonas", "Brazil", -60.016667d, -3.1d, 83.5, "UTC-4"));
weatherRecords.add(new WeatherRecord("Sydney", "New South Wales", "Australia", 151.209444d, -33.865d, 63.5, "UTC+10"));
weatherRecords.add(new WeatherRecord("Auckland", "--", "New Zealand", 174.74d, -36.840556d, 60.5, "UTC+12"));
weatherRecords.add(new WeatherRecord("Jakarta", "--", "Indonesia", 106.816667d, -6.2d, 83, "UTC+7"));
weatherRecords.add(new WeatherRecord("Hobart", "--", "Tasmania", 147.325d, -42.880556d, 56, "UTC+10"));
weatherRecords.add(new WeatherRecord("Perth", "Western Australia", "Australia", 115.858889d, -31.952222d, 68, "UTC+8"));
// Insert the records into the table
numInserted = gpudb.insertRecords(TABLE_WEATHER, weatherRecords, null).getCountInserted();
System.out.println("Number of batch records inserted: " + numInserted);
System.out.println("\n");
System.out.println("RETRIEVING DATA");
System.out.println("---------------");
System.out.println();
// Retrieve no more than 10 records from weather_table using in-line
// request parameters
Map<String,String> options = GPUdb.options(GetRecordsRequest.Options.SORT_BY, COL_CITY);
GetRecordsResponse<WeatherRecord> grResp = gpudb.getRecords(TABLE_WEATHER, 0, 10, options);
System.out.printf("City State/Province Country Latitude Longitude Avg. Temp Time Zone%n");
System.out.printf("==================== ========================= =============== ========== =========== ========= =========%n");
for (WeatherRecord w : grResp.getData())
System.out.printf
(
"%-20s %-25s %-15s %10.6f %11.6f %9.1f %s%n",
w.city, w.state_province, w.country, w.y, w.x, w.avg_temp, w.time_zone
);
// Retrieve no more than 25 of the remaining records from weather table
// using a request object
GetRecordsRequest grReq = new GetRecordsRequest();
grReq.setTableName(TABLE_WEATHER);
grReq.setOffset(10);
grReq.setLimit(25);
grReq.setOptions(options);
grResp = gpudb.getRecords(grReq);
for (WeatherRecord w : grResp.getData())
System.out.printf
(
"%-20s %-25s %-15s %10.6f %11.6f %9.1f %s%n",
w.city, w.state_province, w.country, w.y, w.x, w.avg_temp, w.time_zone
);
System.out.println("\nNumber of records in new table: " + grResp.getTotalNumberOfRecords());
System.out.println("\n");
System.out.println("FILTERING");
System.out.println("---------");
System.out.println();
// Filter Example 1
/* Filter records where column x is less than 0, i.e, cities in the
* western hemisphere, and store the filter in a view
*/
FilterResponse fResp = gpudb.filter(TABLE_WEATHER, VIEW_WEATHER_W, "x < 0", null);
System.out.println("Number of records in the western hemisphere: " + fResp.getCount());
// Filter Example 2
/* Filter records where column x is less than 0 and column y is greater
* than 0, i.e., cities in the northwestern semi-hemisphere, and store
* the filter in a view
*/
fResp = gpudb.filter(TABLE_WEATHER, VIEW_WEATHER_NW, "x < 0 and y > 0", null);
System.out.println("Number of records in the northwestern semi-hemisphere: " + fResp.getCount());
// Filter Example 3
/* Filter records using the same expressions as Example 2, but using
* query chaining this time
*/
// Clear the view, as it will be recreated with chained query
gpudb.clearTable(VIEW_WEATHER_NW, null, null);
fResp = gpudb.filter(VIEW_WEATHER_W, VIEW_WEATHER_NW, "y > 0", null);
System.out.println("Number of records in the northwestern semi-hemisphere (with query chaining): " + fResp.getCount());
// Filter Example 4
/* Filter by list where country name is USA, Brazil, or Australia */
Map<String, List<String>> columnValuesMap = new HashMap<>();
columnValuesMap.put(COL_COUNTRY, Arrays.asList("USA", "Brazil", "Australia"));
FilterByListResponse fblResp = gpudb.filterByList(TABLE_WEATHER, VIEW_WEATHER_COUNTRY, columnValuesMap, null);
System.out.println("Number of records where country name is USA, Brazil, or Australia: " + fblResp.getCount());
// Filter Example 5
/* Filter by range cities that are east of GMT (the Prime Meridian) */
FilterByRangeResponse fbrResp = gpudb.filterByRange(TABLE_WEATHER, VIEW_WEATHER_E, COL_LONG, 0, 180, null);
System.out.println("Number of records that are east of the Prime Meridian (x > 0): " + fbrResp.getCount());
System.out.println("\n");
System.out.println("AGGREGATING, GROUPING, and HISTOGRAMS");
System.out.println("-------------------------------------");
System.out.println();
// Aggregate Example 1
/* Aggregate count, min, mean, and max on the average temperature */
Map<String,Double> stats = gpudb.aggregateStatistics(TABLE_WEATHER, COL_AVG_TEMP, "count,min,max,mean", null).getStats();
System.out.println("Statistics of values in the average temperature column:");
System.out.printf
(
"\tCount: %.0f%n\tMin: %4.2f%n\tMean: %4.2f%n\tMax: %4.2f%n%n",
stats.get("count"), stats.get("min"), stats.get("mean"), stats.get("max")
);
// Aggregate Example 2
/* Find unique city names */
List<Record> weatherLocs = gpudb.aggregateUnique(TABLE_WEATHER, COL_CITY, 0, 25, null).getData();
System.out.println("Unique city names:");
for (Record weatherLoc : weatherLocs)
System.out.println("\t* " + weatherLoc.get(COL_CITY));
System.out.println();
// Aggregate Example 3
/* Find number of weather locations per country in the northwestern
* semi-hemisphere; use binary decoding
*/
List<Record> countries = gpudb.aggregateGroupBy(VIEW_WEATHER_NW, Arrays.asList(COL_COUNTRY), 0, 25, null).getData();
System.out.println("Weather locations per country in the northwest semi-hemisphere:");
for (Record country : countries)
System.out.printf("\t%-10s%2d%n", country.get(COL_COUNTRY) + ":", country.get("count"));
System.out.println();
// Aggregate Example 4
/* Filter table to southeastern semi-hemisphere records, group by
* country, and aggregate min, max, and mean on the average temperature;
* use JSON decoding
*/
gpudb.filter(TABLE_WEATHER, VIEW_WEATHER_SE, "x > 0 and y < 0", null);
List<String> agbCols = Arrays.asList(COL_COUNTRY, "min(" + COL_AVG_TEMP + ")", "max(" + COL_AVG_TEMP + ")", "mean(" + COL_AVG_TEMP + ")");
countries = gpudb.aggregateGroupBy(VIEW_WEATHER_SE, agbCols, 0, 25, null).getData();
System.out.println("SE Semi-Hemi Country Min Mean Max ");
System.out.println("==================== ===== ===== =====");
for (Record c : countries)
System.out.printf
(
"%-20s %5.2f %5.2f %5.2f%n",
c.get(agbCols.get(0)), c.get(agbCols.get(1)), c.get(agbCols.get(3)), c.get(agbCols.get(2))
);
System.out.println();
// Aggregate Example 5
/* Filter for southern hemisphere cities and create a histogram for the
* average temperature of those cities (divided into every 10 degrees,
* e.g., 40s, 50s, 60s, etc.)
*/
gpudb.filter(TABLE_WEATHER, VIEW_WEATHER_HIST, "y < 0", null);
double start = 40;
double end = 90;
double interval = 10;
List<Double> histoGroups = gpudb.aggregateHistogram(VIEW_WEATHER_HIST, COL_AVG_TEMP, start, end, interval, null).getCounts();
System.out.println("Number of southern hemisphere cities with average temps in the given ranges:");
for (int hgNum = 0; hgNum < histoGroups.size(); hgNum++)
System.out.printf("\t%.0fs: %2.0f%n", start + hgNum * interval, histoGroups.get(hgNum));
System.out.println("\n");
System.out.println("DELETING DATA");
System.out.println("-------------");
System.out.println();
/* Filter for cities that are either south of latitude -50 or west of
* longitude -50 to determine how many records will be deleted; delete
* the records, then confirm the deletion by refiltering
*/
String deleteExpression = "x < -50 or y < -50";
fResp = gpudb.filter(TABLE_WEATHER, null, deleteExpression, null);
System.out.println("Number of records that meet deletion criteria before deleting: " + fResp.getCount());
gpudb.deleteRecords(TABLE_WEATHER, Arrays.asList(deleteExpression), null);
fResp = gpudb.filter(TABLE_WEATHER, null, deleteExpression, null);
System.out.println("Number of records that meet deletion criteria after deleting: " + fResp.getCount());
} // end main
} // end class Tutorial
After running the above sample script, the following is output:
TUTORIAL OUTPUT
===============
CREATING A TYPE & TABLE
-----------------------
Table created: OK
INSERTING DATA
--------------
Number of single records inserted: 1
Number of batch records inserted: 20
RETRIEVING DATA
---------------
City State/Province Country Latitude Longitude Avg. Temp Time Zone
==================== ========================= =============== ========== =========== ========= =========
Auckland -- New Zealand -36.840556 174.740000 60.5 UTC+12
Buenos Aires -- Argentina -34.603333 -58.381667 65.0 UTC-3
Hobart -- Tasmania -42.880556 147.325000 56.0 UTC+10
Jakarta -- Indonesia -6.200000 106.816667 83.0 UTC+7
La Paz Baja California Sur Mexico 24.142222 -110.310833 77.0 UTC-7
La Paz Pedro Domingo Murillo Bolivia -16.500000 -68.150000 44.0 UTC-4
Lagos Lagos Nigeria 6.455027 3.384082 83.0 UTC+1
Manaus Amazonas Brazil -3.100000 -60.016667 83.5 UTC-4
Memphis TN USA 35.117500 -89.971111 63.0 UTC-6
Memphis -- Egypt 29.844722 31.250833 73.0 UTC+2
Oslo -- Norway 59.950000 10.750000 45.5 UTC+1
Paris TX USA 33.662500 -95.547778 64.6 UTC-6
Paris -- France 48.856700 2.350800 56.5 UTC+1
Perth Western Australia Australia -31.952222 115.858889 68.0 UTC+8
Santiago Santiago Province Chile -33.450000 -70.666667 62.0 UTC-4
Sao Paulo Sao Paulo Brazil -23.550000 -46.633333 69.5 UTC-3
St. Petersburg FL USA 27.773056 -82.640000 74.5 UTC-5
St. Petersburg -- Russia 59.950000 30.300000 43.5 UTC+3
Sydney Nova Scotia Canada 46.136310 -60.195510 44.5 UTC-4
Sydney New South Wales Australia -33.865000 151.209444 63.5 UTC+10
Washington, D.C. -- USA 38.904722 -77.016389 58.5 UTC-5
Number of records in new table: 21
FILTERING
---------
Number of records in the western hemisphere: 11
Number of records in the northwestern semi-hemisphere: 6
Number of records in the northwestern semi-hemisphere (with query chaining): 6
Number of records where country name is USA, Brazil, or Australia: 8
Number of records that are east of the Prime Meridian (x > 0): 10
AGGREGATING, GROUPING, and HISTOGRAMS
-------------------------------------
Statistics of values in the average temperature column:
Count: 21
Min: 43.50
Mean: 63.74
Max: 83.50
Unique city names:
* Auckland
* Buenos Aires
* Hobart
* Jakarta
* La Paz
* Lagos
* Manaus
* Memphis
* Oslo
* Paris
* Perth
* Santiago
* Sao Paulo
* St. Petersburg
* Sydney
* Washington, D.C.
Weather locations per country in the northwest semi-hemisphere:
Canada: 1
Mexico: 1
USA: 4
SE Semi-Hemi Country Min Mean Max
==================== ===== ===== =====
Tasmania 56.00 56.00 56.00
New Zealand 60.50 60.50 60.50
Australia 63.50 65.75 68.00
Indonesia 83.00 83.00 83.00
Number of southern hemisphere cities with average temps in the given ranges:
40s: 1
50s: 1
60s: 6
70s: 0
80s: 2
DELETING DATA
-------------
Number of records that meet deletion criteria before deleting: 10
Number of records that meet deletion criteria after deleting: 0