Java Developer Manual¶

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.

Prerequisites¶

Data File¶

The tutorial java file makes reference to a data file in the current directory. This path can be updated to point to a valid path on the host where the file will be located, or the script can be run with the data file in the current directory.

Scanner scanner = new Scanner(new File("taxi_trip_data.csv"));

API Download¶

Maven Download¶

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>[7.0.0.0,7.1.0.0-SNAPSHOT)</gpudb-api.version>
</properties>

<repositories>
	<repository>
		<id>gpudb-releases</id>
		<url>http://files.kinetica.com/nexus/content/repositories/releases/</url>
	</repository>
</repositories>

<dependencies>
	<dependency>
		<groupId>com.gpudb</groupId>
		<artifactId>gpudb-api</artifactId>
		<version>${gpudb-api.version}</version>
		<type>jar</type>
	</dependency>
</dependencies>

Important

The pom.xml file used for the tutorial can be found below

Manual Download¶

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.

File Hierarchy¶

The tutorial was setup like the below diagram:

java_tutorial/
 ├── docsite-tutorial-2.0-jar-with-dependencies.jar
 ├── java_tutorial.out
 ├── pom.xml
 └── src/
      └── main/
           └── java/
                └── Tutorial.java

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 = new GPUdb("http://localhost:9191");

Creating a Type¶

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 Vendor 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 = { "char4", "primary_key" })
    public String vendor_id;
    @RecordObject.Column(order = 1, properties = { "char64" })
    public String vendor_name;
    @RecordObject.Column(order = 2, properties = { "char16", "nullable" })
    public String phone;
    @RecordObject.Column(order = 3, properties = { "char64", "nullable" })
    public String email;
    @RecordObject.Column(order = 4, properties = { "char64" })
    public String hq_street;
    @RecordObject.Column(order = 5, properties = { "char8", "dict" })
    public String hq_city;
    @RecordObject.Column(order = 6, properties = { "char2", "dict" })
    public String hq_state;
    @RecordObject.Column(order = 7)
    public Integer hq_zip;
    @RecordObject.Column(order = 8)
    public Integer num_emps;
    @RecordObject.Column(order = 9)
    public Integer num_cabs;

    public Vendor() {}

    /* Create a constructor for the class that will take parameters so that
     * Bulk Inserting is easier */
    public Vendor(
            String vendor_id, String vendor_name, String phone,
            String email, String hq_street, String hq_city, String hq_state,
            Integer hq_zip, Integer num_emps, Integer num_cabs
    )
    {
        this.vendor_id = vendor_id;
        this.vendor_name = vendor_name;
        this.phone = phone;
        this.email = email;
        this.hq_street = hq_street;
        this.hq_city = hq_city;
        this.hq_state = hq_state;
        this.hq_zip = hq_zip;
        this.num_emps = num_emps;
        this.num_cabs = num_cabs;
    }
}

public static class Payment extends RecordObject
{

    @RecordObject.Column(order = 0, properties = { "primary_key" })
    public long payment_id;
    @RecordObject.Column(order = 1, properties = { "char16", "nullable" })
    public String payment_type;
    @RecordObject.Column(order = 2, properties = { "char16", "nullable" })
    public String credit_type;
    @RecordObject.Column(order = 3, properties = { "timestamp",
            "nullable" })
    public Long payment_timestamp;
    @RecordObject.Column(order = 4, properties = { "nullable" })
    public double fare_amount;
    @RecordObject.Column(order = 5, properties = { "nullable" })
    public double surcharge;
    @RecordObject.Column(order = 6, properties = { "nullable" })
    public double mta_tax;
    @RecordObject.Column(order = 7, properties = { "nullable" })
    public double tip_amount;
    @RecordObject.Column(order = 8, properties = { "nullable" })
    public double tolls_amount;
    @RecordObject.Column(order = 9, properties = { "nullable" })
    public double total_amount;

    public Payment() {}

    public Payment(
            long payment_id, String payment_type, String credit_type,
            Long payment_timestamp, double fare_amount, double surcharge,
            double mta_tax, double tip_amount, double tolls_amount,
            double total_amount
    )
    {
        this.payment_id = payment_id;
        this.payment_type = payment_type;
        this.credit_type = credit_type;
        this.payment_timestamp = payment_timestamp;
        this.fare_amount = fare_amount;
        this.surcharge = surcharge;
        this.mta_tax = mta_tax;
        this.tip_amount = tip_amount;
        this.tolls_amount = tolls_amount;
        this.total_amount = total_amount;
    }
}

public static class TaxiTripData extends RecordObject
{

    @RecordObject.Column(order = 0, properties = { "primary_key" })
    public long transaction_id;
    @RecordObject.Column(order = 1, properties = { "primary_key", "shard_key"})
    public long payment_id;
    @RecordObject.Column(order = 2, properties = { "char4" })
    public String vendor_id;
    @RecordObject.Column(order = 3, properties = { "timestamp" })
    public long pickup_datetime;
    @RecordObject.Column(order = 4, properties = { "timestamp" })
    public long dropoff_datetime;
    @RecordObject.Column(order = 5, properties = { "int8" })
    public int passenger_count;
    @RecordObject.Column(order = 6)
    public float trip_distance;
    @RecordObject.Column(order = 7)
    public float pickup_longitude;
    @RecordObject.Column(order = 8)
    public float pickup_latitude;
    @RecordObject.Column(order = 9)
    public float dropoff_longitude;
    @RecordObject.Column(order = 10)
    public float dropoff_latitude;

    public TaxiTripData() {}
}

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 types need to be created:

String vendorTypeId = RecordObject.createType(Vendor.class, gpudb);
String paymentTypeId = RecordObject.createType(Payment.class, gpudb);
String taxiTypeId = RecordObject.createType(TaxiTripData.class, gpudb);

Creating a Table¶

The returned object from the createType() call contains a unique type identifier allocated by the system. This identifier can then be used in the request to create a new table. The examples below outline creating a table with either an options map (Vendor) or options object (Payment):

// Create the Vendor table using an options map
Map<String, String> optionCollectionReplicated = GPUdb.options(
        CreateTableRequest.Options.COLLECTION_NAME, COLLECTION,
        CreateTableRequest.Options.IS_REPLICATED, "true"
);
gpudb.createTable(
        TABLE_VENDOR,
        vendorTypeId,
        optionCollectionReplicated
);

// Create the Payment table using the options object
gpudb.createTable(
        TABLE_PAYMENT,
        paymentTypeId,
        GPUdb.options(
                CreateTableRequest.Options.COLLECTION_NAME, COLLECTION
        )
);

gpudb.createTable(
        TABLE_TAXI,
        taxiTypeId,
        GPUdb.options(
                CreateTableRequest.Options.COLLECTION_NAME, COLLECTION
        )
);

Inserting Data¶

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 Multi-Head Ingest. For this tutorial, only the native Java API call insertRecords() will be shown.

Key/Value Record¶

// Create a record object and assign values to properties
Payment paymentDatum = new Payment();
paymentDatum.payment_id = 189;
paymentDatum.payment_type = "No Charge";
paymentDatum.credit_type = null;
paymentDatum.payment_timestamp = null;
paymentDatum.fare_amount = 6.5;
paymentDatum.surcharge = 0;
paymentDatum.mta_tax = 0.6;
paymentDatum.tip_amount = 0;
paymentDatum.tolls_amount = 0;
paymentDatum.total_amount = 7.1;

// Insert the record into the table
int numInserted = gpudb.insertRecords(
        TABLE_PAYMENT,
        Collections.singletonList(paymentDatum),
        null
).getCountInserted();
System.out.println(
        "Number of records inserted into the Payment table:  " + numInserted
);

In-Line Record¶

/* Create a list of in-line records. The order of the values must match
 * the column order in the type */
List<Vendor> vendorRecords = new ArrayList<>();
vendorRecords.add(
        new Vendor(
                "VTS","Vine Taxi Service","9998880001",
                "admin@vtstaxi.com","26 Summit St.","Flushing","NY",
                11354,450,400
        ));
vendorRecords.add(
        new Vendor(
                "YCAB","Yes Cab","7895444321",null,"97 Edgemont St.",
                "Brooklyn","NY",11223,445,425
        ));
vendorRecords.add(
        new Vendor(
                "NYC","New York City Cabs",null,"support@nyc-taxis.com",
                "9669 East Bayport St.","Bronx","NY",10453,505,500
        ));
vendorRecords.add(
        new Vendor(
                "DDS","Dependable Driver Service",null,null,
                "8554 North Homestead St.","Bronx","NY",10472,200,124
        ));
vendorRecords.add(
        new Vendor(
                "CMT","Crazy Manhattan Taxi","9778896500",
                "admin@crazymanhattantaxi.com","950 4th Road Suite 78",
                "Brooklyn","NY",11210,500,468
        ));
vendorRecords.add(
        new Vendor(
                "TNY","Taxi New York",null,null,"725 Squaw Creek St.",
                "Bronx","NY",10458,315,305
        ));
vendorRecords.add(
        new Vendor(
                "NYMT","New York Metro Taxi",null,null,
                "4 East Jennings St.","Brooklyn","NY",11228,166,150
        ));
vendorRecords.add(
        new Vendor(
                "5BTC","Five Boroughs Taxi Co.","4566541278",
                "mgmt@5btc.com","9128 Lantern Street","Brooklyn","NY",
                11229,193,175
        ));

// Insert the records into the Vendor table
numInserted = gpudb.insertRecords(
        TABLE_VENDOR,
        vendorRecords,
        null
).getCountInserted();
System.out.println(
        "Number of records inserted into the Vendor table:  " + numInserted
);

Important

Additional records are inserted at this point, which can be found in the full Tutorial.java file below

CSV¶

This example requires the util and io libraries but allows for importing a large amount of records with ease. After setting up a Scanner and File instance, you can loop over all values in a .csv file, append the values to lists of a list, then insert the list.

try {
    Scanner scanner = new Scanner(new File("taxi_trip_data.csv"));

    List<TaxiTripData> taxiRecords = new ArrayList<>();

    scanner.nextLine();

    while (scanner.hasNextLine()) {
        String[] record = scanner.nextLine().split(",", -1);
        TaxiTripData taxiRecord = new TaxiTripData();

        taxiRecord.transaction_id = Long.parseLong(record[0]);
        taxiRecord.payment_id = Long.parseLong(record[1]);
        taxiRecord.vendor_id = record[2];
        taxiRecord.pickup_datetime = Long.parseLong(record[3]);
        taxiRecord.dropoff_datetime = Long.parseLong(record[4]);
        taxiRecord.passenger_count = Integer.parseInt(record[5]);
        taxiRecord.trip_distance = Float.parseFloat(record[6]);
        taxiRecord.pickup_longitude = Float.parseFloat(record[7]);
        taxiRecord.pickup_latitude = Float.parseFloat(record[8]);
        taxiRecord.dropoff_longitude = Float.parseFloat(record[9]);
        taxiRecord.dropoff_latitude = Float.parseFloat(record[10]);
        taxiRecords.add(taxiRecord);
    }
    numInserted = gpudb.insertRecords(
            TABLE_TAXI,
            taxiRecords,
            null
    ).getCountInserted();
    System.out.println(
            "Number of records inserted into the Taxi table:  " + numInserted
    );
} catch (FileNotFoundException e) {
    e.printStackTrace();
}

Retrieving Data¶

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 payments using in-line request parameters
GetRecordsResponse<Payment> getPaymentRecordsResp = gpudb.getRecords(
        TABLE_PAYMENT,
        0,
        10,
        GPUdb.options(GetRecordsRequest.Options.SORT_BY,"payment_id")
);
System.out.println(
        "Payment ID Payment Type Credit Type Payment Timestamp " +
        "Fare Amount Surcharge MTA Tax Tip Amount Tolls Amount Total Amount"
);
System.out.println(
        "========== ============ =========== ================= " +
        "=========== ========= ======= ========== ============ ============"
);
for (Payment p : getPaymentRecordsResp.getData())
    System.out.printf(
            "%10d %-12s %-11s %17d %11.2f %9.2f %7.2f %10.2f %12.2f %12.2f %n",
            p.payment_id, p.payment_type, p.credit_type,
            p.payment_timestamp, p.fare_amount, p.surcharge, p.mta_tax,
            p.tip_amount, p.tolls_amount, p.total_amount
    );

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 all records from the Vendor table using a request object
GetRecordsRequest vendorReq = new GetRecordsRequest();
vendorReq.setTableName(TABLE_VENDOR);
vendorReq.setOffset(0);
vendorReq.setLimit(GPUdb.END_OF_SET);
vendorReq.setOptions(
        GPUdb.options(GetRecordsRequest.Options.SORT_BY, "vendor_id")
);
GetRecordsResponse<Vendor> vendorResp = gpudb.getRecords(vendorReq);
System.out.println(
        "Vendor ID Vendor Name                Phone       Email                         " +
        "HQ Street                HQ City  HQ State HQ Zip " +
        "# Employees # Cabs"
);
System.out.println(
        "========= ========================== =========== ============================= " +
        "======================== ======== ======== ====== " +
        "=========== ======");
for (Vendor v : vendorResp.getData())
    System.out.printf(
            "%-9s %-26s %-11s %-29s " +
            "%-24s %-8s %-8s %-6d " +
            "%11d %6d%n",
            v.vendor_id, v.vendor_name, v.phone, v.email, v.hq_street,
            v.hq_city, v.hq_state, v.hq_zip, v.num_emps, v.num_cabs
    );

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.

Updating and Removing Records¶

Use updateRecords() to update matching key values for all records in a table.

// Update the e-mail, number of employees, and number of cabs of the DDS vendor
List<Map<String, String>> newValsList = new ArrayList<>();
Map<String,String> newVals = new HashMap<>();
newVals.put("email", "'management@ddstaxico.com'");
newVals.put("num_emps", "num_emps + 2");
newVals.put("num_cabs", "num_cabs + 1");
newValsList.add(newVals);
gpudb.updateRecords(
        TABLE_VENDOR,
        Collections.singletonList("vendor_id = 'DDS'"),
        newValsList,
        null,
        GPUdb.options(
                UpdateRecordsRequest.Options.USE_EXPRESSIONS_IN_NEW_VALUES_MAPS,
                UpdateRecordsRequest.Options.TRUE
        )
);

Use deleteRecords() to delete records from a table. A list can be used to specify which records delete based on matching expressions.

String delExpr = "payment_id = 189";
System.out.println("Deleting record where " + delExpr);
gpudb.deleteRecords(
        TABLE_PAYMENT,
        Collections.singletonList(delExpr),
        null
);

Alter Table¶

Some properties can be altered or added after table creation, including indexes, dictionary encoding, and compression.

Indexes¶

Using the alterTable method, you can create indexes on columns using the create_index action paired with a column name.

/* Add column indexes on:
 *   - payment table, fare_amount (for query-chaining filter example)
 *   - taxi table, passenger_count (for filter-by-range example) */
gpudb.alterTable(
        TABLE_PAYMENT,
        "create_index",
        "fare_amount",
        null
);

gpudb.alterTable(
        TABLE_TAXI,
        "create_index",
        "passenger_count",
        null
);

Compression¶

Applying column compression works similarly: using the alter_table method but with a set_column_compression action paired with a column name and compression type option.

/* Apply the snappy compression algorithm to the pickup and dropoff
 * datetime columns */
Map<String,String> snappyComp = GPUdb.options(
        AlterTableRequest.Options.COMPRESSION_TYPE, "snappy"
);
gpudb.alterTable(
        TABLE_TAXI,
        "set_column_compression",
        "pickup_datetime",
        snappyComp
);

gpudb.alterTable(
        TABLE_TAXI,
        "set_column_compression",
        "dropoff_datetime",
        snappyComp
);

Important

Column compression is applied at a fixed interval, so be sure to verify later that the compression has been added. Column usage should decrease by roughly 23% (~1989 bytes)

Dictionary Encoding¶

Applying dictionary encoding via alter_table involves adding a new property to a column.

// Apply dictionary encoding to the payment type column
AlterTableResponse dictEncResp = gpudb.alterTable(
        TABLE_TAXI,
        AlterTableRequest.Action.CHANGE_COLUMN,
        columnName,
        GPUdb.options(
                AlterTableRequest.Options.COLUMN_PROPERTIES,
                "char4,dict"
        )
);

Important

To add a new property, all existing column properties must be listed along with any new property

Filters¶

Filters are an easy way to reduce larger table into more concise views using expressions.

Filter Example 1¶

// Selects all payments with no corresponding payment type
Map<String, String> optionCollection = GPUdb.options(
        "collection_name", COLLECTION
);
long f1Count = gpudb.filter(
        TABLE_PAYMENT,
        VIEW_EXAMPLE1,
        "IS_NULL(payment_type)",
        optionCollection
).getCount();
System.out.println("Number of null payments:  " + f1Count);

Filter Example 2¶

// Using query chaining, filter null payment type records with a fare amount greater than 8
long f2Count = gpudb.filter(
        VIEW_EXAMPLE1,
        VIEW_EXAMPLE2,
        "fare_amount > 8",
        optionCollection
).getCount();
System.out.println(
        "Number of null payments with a fare amount greater than $8.00 " +
                "(with query chaining): " + f2Count
);

Filter Example 3¶

// Filter by list where vendor ID is either NYC or YCAB
Map<String, List<String>> columnValuesMap = new HashMap<>();
columnValuesMap.put("vendor_id", Arrays.asList("NYC", "YCAB"));
long f3Count = gpudb.filterByList(
        TABLE_TAXI,
        VIEW_EXAMPLE3,
        columnValuesMap,
        null
).getCount();
System.out.println(
        "Number of records where vendor_id is either NYC or YCAB:  " + f3Count
);

Filter Example 4¶

// Filter by range trip with passenger count between 1 and 3
long f4Count = gpudb.filterByRange(
        TABLE_TAXI,
        VIEW_EXAMPLE4,
        "passenger_count",
        1,
        3,
        null
).getCount();
System.out.println(
        "Number of trips with passenger_count between 1 and 3:  " + f4Count
);

Aggregates¶

Kinetica supports various aggregate and group-by queries, which group and aggregate your data to return counts and useful statistics.

Aggregate Example 1¶

// Aggregate count, min, mean, and max on the trip distance
Map<String,Double> a1Resp = gpudb.aggregateStatistics(
        TABLE_TAXI,
        "trip_distance",
        AggregateStatisticsRequest.Stats.COUNT + "," +
        AggregateStatisticsRequest.Stats.MIN + "," +
        AggregateStatisticsRequest.Stats.MAX + "," +
        AggregateStatisticsRequest.Stats.MEAN,
        null
).getStats();
System.out.println("Statistics of values in the trip_distance column:");
System.out.printf(
        "\tCount: %.0f%n\tMin:  %4.2f%n\tMean: %4.2f%n\tMax:  %4.2f%n%n",
        a1Resp.get(AggregateStatisticsRequest.Stats.COUNT),
        a1Resp.get(AggregateStatisticsRequest.Stats.MIN),
        a1Resp.get(AggregateStatisticsRequest.Stats.MEAN),
        a1Resp.get(AggregateStatisticsRequest.Stats.MAX)
);

Aggregate Example 2¶

// Find unique taxi vendor IDs
List<Record> a2Resp = gpudb.aggregateUnique(
        TABLE_TAXI,
        "vendor_id",
        0,
        GPUdb.END_OF_SET,
        null
).getData();
System.out.println("Unique vendor IDs in the taxi trip table:");
for (Record vendor : a2Resp)
    System.out.println("\t* " + vendor.get("vendor_id"));

Aggregate Example 3¶

// Find number of trips per vendor
List <String> colNames = Arrays.asList("vendor_id", "count(vendor_id)");
List<Record> a3Resp = gpudb.aggregateGroupBy(
        TABLE_TAXI,
        colNames,
        0,
        GPUdb.END_OF_SET,
        GPUdb.options(
                AggregateGroupByRequest.Options.SORT_BY,
                AggregateGroupByRequest.Options.KEY
        )
).getData();
System.out.println("Trips per vendor:");
for (Record vendor : a3Resp)
    System.out.printf(
            "\t%-5s  %3d%n",
            vendor.get("vendor_id") + ":",
            vendor.get("count(vendor_id)")
    );

Aggregate Example 4¶

// Create a histogram for the different groups of passenger counts
float start = 1;
float end = 6;
float interval = 1;
List<Double> a4Resp = gpudb.aggregateHistogram(
        TABLE_TAXI,
        "passenger_count",
        start,
        end,
        interval,
        null
).getCounts();

System.out.println("Passenger count groups by size:");
System.out.println("Passengers Total Trips");
System.out.println("========== ===========");
List<String> countGroups = Arrays.asList("1", "2", "3", "4", ">5");
for (int hgNum = 0; hgNum < a4Resp.size(); hgNum++)
    System.out.printf(
            "%10s %11.0f%n",
            countGroups.get(hgNum),
            a4Resp.get(hgNum)
    );

Joins¶

Joins allow you to link multiple tables together, along their relations, retrieving associated information from any or all of them. Tables can only be joined if they're sharded similarly or replicated.

An inner join returns only records that have matching values in both tables.

/* Retrieve cab ride transactions and the full name of the associated
 * vendor for rides having more than three passengers between April 1st
 * & 16th, 2015 */
gpudb.createJoinTable(
        JOIN_TABLE_INNER,
        Arrays.asList(TABLE_TAXI + " as t", TABLE_PAYMENT + " as p"),
        Arrays.asList(
                "t.payment_id", "payment_type", "total_amount",
                "passenger_count", "vendor_id", "trip_distance"
        ),
        Arrays.asList(
                "t.payment_id = p.payment_id", "passenger_count > 3"
        ),
        optionCollection
);

A left join returns all of the records an inner join does, but additionally, for each record in the table on the left side of the join that has no match along the relation to a record in the table on the right side of the join, a corresponding record will be returned with "left-side" columns populated with the "left-side" record data and the "right-side" columns populated with nulls. Note the usage of left join in the given expression.

/* Retrieve cab ride transactions and the full name of the associated
 * vendor (if available--blank if vendor name is unknown) for
 * transactions with associated payment data, sorting by increasing
 * values of transaction ID. */
gpudb.createJoinTable(
        JOIN_TABLE_LEFT,
        Arrays.asList(TABLE_TAXI + " as t", TABLE_VENDOR + " as v"),
        Arrays.asList(
                "transaction_id", "pickup_datetime", "trip_distance",
                "t.vendor_id", "vendor_name"
        ),
        Arrays.asList(
                "left join t, v on (t.vendor_id = v.vendor_id)",
                "payment_id <> 0"
        ),
        optionCollection
);

Note

Full outer joins require both tables to be replicated or joined on their shard keys. Set merges that perform deduplication of records, like Union Distinct, Intersect, and Except also need to use replicated tables to ensure the correct results, so a replicated version of the taxi (taxi_trip_data_replicated) table is created at this point in the tutorial using /merge/records. You could also use /append/records, but the taxi_trip_data_replicated would need to be created before appending records to it.

Map<String,String> colMap = new HashMap<>();
colMap.put("transaction_id", "transaction_id");
colMap.put("payment_id", "payment_id");
colMap.put("vendor_id", "vendor_id");
colMap.put("pickup_datetime", "pickup_datetime");
colMap.put("dropoff_datetime", "dropoff_datetime");
colMap.put("passenger_count", "passenger_count");
colMap.put("trip_distance", "trip_distance");
colMap.put("pickup_longitude", "pickup_longitude");
colMap.put("pickup_latitude", "pickup_latitude");
colMap.put("dropoff_longitude", "dropoff_longitude");
colMap.put("dropoff_latitude", "dropoff_latitude");
gpudb.mergeRecords(
        TABLE_TAXI_REPLICATED,
        Collections.singletonList(TABLE_TAXI),
        Collections.singletonList(colMap),
        optionCollectionReplicated
);

A full outer join returns all of the records a left join does, but additionally, for each record in the table on the right side of the join that has no match along the relation to a record in the table on the left side of the join, a corresponding record will be returned with "right-side" columns populated with the "right-side" record data and the "left-side" columns populated with nulls.

/* Retrieve the vendor IDs of known vendors with no recorded cab ride
 * transactions, as well as the vendor ID and number of transactions
 * for unknown vendors with recorded cab ride transactions */
gpudb.createJoinTable(
        JOIN_TABLE_OUTER,
        Arrays.asList(
                TABLE_TAXI_REPLICATED + " as t", TABLE_VENDOR + " as v"
        ),
        Arrays.asList(
                "t.vendor_id as vendor_id", "v.vendor_id as vendor_id_1"
        ),
        Collections.singletonList(
                "full_outer join t,v on ((v.vendor_id = t.vendor_id))"
        ),
        optionCollection
);

Projections¶

You can create projections using the createProjection method.

// Create a projection containing all payments by credit card
gpudb.createProjection(
        TABLE_PAYMENT,
        PROJECTION_EXAMPLE1,
        Arrays.asList(
                "payment_id", "payment_type", "credit_type",
                "payment_timestamp", "fare_amount", "surcharge",
                "mta_tax", "tip_amount", "tolls_amount", "total_amount"
        ),
        GPUdb.options(
                CreateProjectionRequest.Options.COLLECTION_NAME,
                COLLECTION,
                CreateProjectionRequest.Options.EXPRESSION,
                "payment_type = 'Credit'"
        )
);

To persist a projection:

/* Create a persisted table with cab ride transactions greater than 5
 * miles whose trip started during lunch hours */
gpudb.createProjection(
        TABLE_TAXI,
        PROJECTION_EXAMPLE2,
        Arrays.asList(
                "hour(pickup_datetime) as hour_of_day", "vendor_id",
                "passenger_count", "trip_distance"
        ),
        GPUdb.options(
                CreateProjectionRequest.Options.EXPRESSION,
                "(hour(pickup_datetime) >= 11) AND " +
                "(hour(pickup_datetime) <= 14) AND " +
                "(trip_distance > 5)",
                CreateProjectionRequest.Options.PERSIST,
                CreateProjectionRequest.Options.TRUE,
                CreateProjectionRequest.Options.COLLECTION_NAME,
                COLLECTION
        )
);

Union, Intersect, and Except¶

Union can be used to combine homogeneous data sets into one larger data set. Union & Union Distinct will both combine data sets but only retain the records that are unique across the chosen columns, removing all duplicates. Union All will combine data sets, retaining all records from the source data sets.

/* Calculate the average number of passengers, as well as the shortest,
 * average, and longest trips for all trips in each of the two time
 * periods--from April 1st through the 15th, 2015 and from April 16th
 * through the 23rd, 2015--and return those two sets of statistics in a
 * single result set. */
gpudb.aggregateGroupBy(
        TABLE_TAXI,
        Arrays.asList(
                "avg(passenger_count) as avg_pass_count",
                "avg(trip_distance) as avg_trip_dist",
                "min(trip_distance) as min_trip_dist",
                "max(trip_distance) as max_trip_dist"
        ),
        0,
        GPUdb.END_OF_SET,
        GPUdb.options(
                AggregateGroupByRequest.Options.EXPRESSION,
                "((pickup_datetime >= '2015-04-01') AND " +
                "(pickup_datetime <= '2015-04-15 23:59:59.999'))",
                AggregateGroupByRequest.Options.RESULT_TABLE,
                AGG_GRPBY_UNION_ALL_SRC1,
                AggregateGroupByRequest.Options.COLLECTION_NAME,
                COLLECTION
        )
);
gpudb.aggregateGroupBy(
        TABLE_TAXI,
        Arrays.asList(
                "avg(passenger_count) as avg_pass_count",
                "avg(trip_distance) as avg_trip_dist",
                "min(trip_distance) as min_trip_dist",
                "max(trip_distance) as max_trip_dist"
        ),
        0,
        GPUdb.END_OF_SET,
        GPUdb.options(
                AggregateGroupByRequest.Options.EXPRESSION,
                "((pickup_datetime >= '2015-04-16') AND " +
                "(pickup_datetime  <= '2015-04-23 23:59:59.999'))",
                AggregateGroupByRequest.Options.RESULT_TABLE,
                AGG_GRPBY_UNION_ALL_SRC2,
                AggregateGroupByRequest.Options.COLLECTION_NAME,
                COLLECTION
        )
);
gpudb.createUnion(
        UNION_ALL_TABLE,
        Arrays.asList(AGG_GRPBY_UNION_ALL_SRC1, AGG_GRPBY_UNION_ALL_SRC2),
        Arrays.asList(
                Arrays.asList(
                        "'2015-04-01 - 2015-04-15'",
                        "avg_pass_count", "avg_trip_dist",
                        "min_trip_dist", "max_trip_dist"
                ),
                Arrays.asList(
                        "'2015-04-16 - 2015-04-23'",
                        "avg_pass_count", "avg_trip_dist",
                        "min_trip_dist", "max_trip_dist"
                )
        ),
        Arrays.asList(
                "pickup_window_range", "avg_pass_count",
                "avg_trip", "min_trip", "max_trip"
        ),
        GPUdb.options(
                CreateUnionRequest.Options.MODE,
                CreateUnionRequest.Options.UNION_ALL,
                CreateUnionRequest.Options.COLLECTION_NAME,
                COLLECTION
        )
);

Intersect will combine data sets but only include the records found in both data sets, removing duplicate result records.

/* Retrieve locations (as lat/lon pairs) that were both pick-up and
 * drop-off points */
gpudb.createUnion(
        UNION_INTERSECT_TABLE,
        Arrays.asList(TABLE_TAXI_REPLICATED, TABLE_TAXI_REPLICATED),
        Arrays.asList(
                Arrays.asList("pickup_latitude", "pickup_longitude"),
                Arrays.asList("dropoff_latitude", "dropoff_longitude")
        ),
        Arrays.asList("latitude", "longitude"),
        GPUdb.options(
                CreateUnionRequest.Options.MODE,
                CreateUnionRequest.Options.INTERSECT,
                CreateUnionRequest.Options.COLLECTION_NAME,
                COLLECTION
        )
);

Except will return records that appear in the first data set but not the second data set. Note that the data sets on each side of the Except will have duplicates removed first, and then the set subtraction will be processed.

/* Show vendors that operate before noon, but not after noon: retrieve
 * the unique list of IDs of vendors who provided cab rides between
 * midnight and noon, and remove from that list the IDs of any vendors
 * who provided cab rides between noon and midnight */
gpudb.createProjection(
        TABLE_TAXI_REPLICATED,
        PROJECTION_EXCEPT_SRC1,
        Collections.singletonList("vendor_id"),
        GPUdb.options(
                CreateProjectionRequest.Options.EXPRESSION,
                "((HOUR(pickup_datetime) >= 0) AND (HOUR(pickup_datetime) <= 11))",
                CreateProjectionRequest.Options.COLLECTION_NAME,
                COLLECTION
        )
);
gpudb.createProjection(
        TABLE_TAXI_REPLICATED,
        PROJECTION_EXCEPT_SRC2,
        Collections.singletonList("vendor_id"),
        GPUdb.options(
                CreateProjectionRequest.Options.EXPRESSION,
                "((HOUR(pickup_datetime) >= 12) AND (HOUR(pickup_datetime) <= 23))",
                CreateProjectionRequest.Options.COLLECTION_NAME,
                COLLECTION
        )
);
gpudb.createUnion(
        UNION_EXCEPT_TABLE,
        Arrays.asList(PROJECTION_EXCEPT_SRC1, PROJECTION_EXCEPT_SRC2),
        Arrays.asList(
                Collections.singletonList("vendor_id"),
                Collections.singletonList("vendor_id")
        ),
        Collections.singletonList("vendor_id"),
        GPUdb.options(
                CreateUnionRequest.Options.MODE,
                CreateUnionRequest.Options.EXCEPT,
                CreateUnionRequest.Options.COLLECTION_NAME,
                COLLECTION
        )
);

Deleting Records¶

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).

gpudb.deleteRecords(
        TABLE_PAYMENT,
        Collections.singletonList("payment_type = 'Cash'"),
        null
);

Download & Run¶

Included below is a complete example containing all the above requests, the data file, output, compiled jar, and pom files.

To run the complete sample, ensure the taxi_trip_data.csv is in the correct location, given the direction here, and run the tutorial JAR as an executable jar. For instance, if running from the API project root:

java -jar target/docsite-tutorial-2.0-jar-with-dependencies.jar

Note

As this script creates a schema and several database objects within it, system admin permission is required to run it.

Table Of Contents

Java Developer Manual¶

Prerequisites¶

Data File¶

API Download¶

Maven Download¶

Manual Download¶

File Hierarchy¶

Connecting to the Database¶

Creating a Type¶

Creating a Table¶

Inserting Data¶

Key/Value Record¶

In-Line Record¶

CSV¶

Retrieving Data¶

Updating and Removing Records¶

Alter Table¶

Indexes¶

Compression¶

Dictionary Encoding¶

Filters¶

Filter Example 1¶

Filter Example 2¶

Filter Example 3¶

Filter Example 4¶

Aggregates¶

Aggregate Example 1¶

Aggregate Example 2¶

Aggregate Example 3¶

Aggregate Example 4¶

Joins¶

Projections¶

Union, Intersect, and Except¶

Deleting Records¶

Download & Run¶