SQL Developer Manual

The following guide provides step-by-step instructions to get started interacting with Kinetica using SQL. This guide demonstrates some of the most common and useful SQL commands, but a detailed look into the full SQL support is available on the SQL Support page.

Prerequisites

The ODBC connector needs to be enabled for Kinetica to accept ODBC/JDBC connections (including Kinetica SQL (KiSQL)). For a description of how to configure various clients for issuing SQL commands, see Connecting.

Data File

The tutorial script makes reference to a data file with a default file path. This path should be updated to point to a valid path on the Kinetica Head Node host where the file will be located:

INSERT INTO taxi_trip_data
SELECT *
FROM FILE."/tmp/data/taxi_trip_data.csv"

Creating a Type and Table

In SQL, a type schema and table are created simultaneously. Tables can be created as replicated using the REPLICATED specification. The OR REPLACE specification can be added to ensure the table is replaced if it already exists in the database. Adding a schema name before the table name in the CREATE TABLE query, e.g., CREATE TABLE my_schema.mytable will create the table under the given schema; if the schema does not exist (as is the case in the tutorial), it will be created automatically. For this tutorial, three tables will be created:

• A base table containing a sample of NYC taxi cab ride data
• A lookup table containing sample cab vendor information
• A 1-to-1 relation table containing payment information for a subset of the cab-ride transactions

-- Vendor Table
-- ************

CREATE OR REPLACE REPLICATED TABLE taxi_info.vendor
(
  vendor_id VARCHAR(4) NOT NULL,
  vendor_name VARCHAR(32) NOT NULL,
  phone VARCHAR(10),
  email VARCHAR(32),
  hq_street VARCHAR(32) NOT NULL,
  hq_city VARCHAR(8) NOT NULL,
  hq_state VARCHAR(2) NOT NULL,
  hq_zip INT NOT NULL,
  num_emps INT NOT NULL,
  num_cabs INT NOT NULL,
  PRIMARY KEY (vendor_id)
)

-- Payment Table
-- *************

CREATE OR REPLACE TABLE taxi_info.payment
(
  payment_id LONG NOT NULL,
  payment_type VARCHAR(16),
  credit_type VARCHAR(16),
  payment_timestamp TYPE_TIMESTAMP,
  fare_amount DECIMAL(7,2),
  surcharge DECIMAL(7,2),
  mta_tax DECIMAL(5,2),
  tip_amount DECIMAL(7,2),
  tolls_amount DECIMAL(7,2),
  total_amount DECIMAL(7,2),
  PRIMARY KEY (payment_id)
)

-- Taxi Table
-- **********

CREATE OR REPLACE TABLE taxi_info.taxi_trip_data
(
  transaction_id LONG NOT NULL,
  payment_id LONG(SHARD_KEY) NOT NULL,
  vendor_id VARCHAR(4) NOT NULL,
  pickup_datetime TYPE_TIMESTAMP,
  dropoff_datetime TYPE_TIMESTAMP,
  passenger_count TINYINT,
  trip_distance REAL,
  pickup_longitude REAL,
  pickup_latitude REAL,
  dropoff_longitude REAL,
  dropoff_latitude REAL,
  PRIMARY KEY (transaction_id, payment_id)
)

Inserting Data

The INSERT INTO ... VALUES clause is used to insert records into a table. At a minimum, all of the NOT NULL columns in the table need to be listed in the INSERT INTO clause. Each record in the VALUES clause must match the number and order of values to insert with the specified column names:

-- Use explicit column name syntax when the ordering of values for the set of
-- records doesn't match the natural ordering of all of the columns defined
-- within the Vendor table; here, num_emps and num_cabs are reversed
INSERT INTO vendor (vendor_id, vendor_name, phone, email, hq_street, hq_city, hq_state, hq_zip, num_cabs, num_emps)
VALUES
  ('VTS','Vine Taxi Service','9998880001','admin@vtstaxi.com','26 Summit St.','Flushing','NY',11354,450,400),
  ('YCAB','Yes Cab','7895444321',NULL,'97 Edgemont St.','Brooklyn','NY',11223,445,425),
  ('NYC','New York City Cabs',NULL,'support@nyc-taxis.com','9669 East Bayport St.','Bronx','NY',10453,505,500),
  ('DDS','Dependable Driver Service',NULL,NULL,'8554 North Homestead St.','Bronx','NY',10472,200,124),
  ('CMT','Crazy Manhattan Taxi','9778896500','admin@crazymanhattantaxi.com','950 4th Road Suite 78','Brooklyn','NY',11210,500,468),
  ('TNY','Taxi New York',NULL,NULL,'725 Squaw Creek St.','Bronx','NY',10458,315,305),
  ('NYMT','New York Metro Taxi',NULL,NULL,'4 East Jennings St.','Brooklyn','NY',11228,166,150),
  ('5BTC','Five Boroughs Taxi Co.','4566541278','mgmt@5btc.com','9128 Lantern Street','Brooklyn','NY',11229,193,175)

If each record in the VALUES clause specifies values for all columns in their natural table ordering (the order in which they appeared in the original CREATE TABLE statement) no column names need to be specified, like so:

-- Use shorthand syntax, where each record of values matches the natural
-- ordering of all of the columns defined within the Payment table
INSERT INTO payment
VALUES
  (136,'Cash',NULL,'2015-04-11 01:42:01',4,0.5,0.5,1,0,6.3),
  (148,'Cash',NULL,'2015-04-27 08:49:41',9.5,0,0.5,1,0,11.3),
  (114,'Cash',NULL,'2015-04-05 18:47:53',5.5,0,0.5,1.89,0,8.19),
  (180,'Cash',NULL,'2015-04-13 22:57:03',6.5,0.5,0.5,1,0,8.8),
  (109,'Cash',NULL,'2015-04-13 18:08:33',22.5,0.5,0.5,4.75,0,28.55),
  (132,'Cash',NULL,'2015-04-19 19:46:19',6.5,0.5,0.5,1.55,0,9.35),
  (134,'Cash',NULL,'2015-04-19 19:44:28',33.5,0.5,0.5,0,0,34.8),
  (176,'Cash',NULL,'2015-04-07 10:52:42',9,0.5,0.5,2.06,0,12.36),
  (100,'Cash',NULL,NULL,9,0,0.5,2.9,0,12.7),
  (193,'Cash',NULL,NULL,3.5,1,0.5,1.59,0,6.89),
  (140,'Credit','Visa',NULL,28,0,0.5,0,0,28.8),
  (161,'Credit','Visa',NULL,7,0,0.5,0,0,7.8),
  (199,'Credit','Visa',NULL,6,1,0.5,1,0,8.5),
  (159,'Credit','Visa','2015-04-10 14:01:27',7,0,0.5,0,0,7.8),
  (156,'Credit','MasterCard','2015-04-10 13:32:33',12.5,0.5,0.5,0,0,13.8),
  (198,'Credit','MasterCard','2015-04-19 19:43:56',9,0,0.5,0,0,9.8),
  (107,'Credit','MasterCard','2015-04-11 01:56:17',5,0.5,0.5,0,0,6.3),
  (166,'Credit','American Express','2015-04-12 03:18:43',17.5,0,0.5,0,0,18.3),
  (187,'Credit','American Express','2015-04-10 12:49:41',14,0,0.5,0,0,14.8),
  (125,'Credit','Discover','2015-04-24 10:01:13',8.5,0.5,0.5,0,0,9.8),
  (119,NULL,NULL,'2015-04-30 22:04:31',9.5,0,0.5,0,0,10.3),
  (150,NULL,NULL,'2015-04-30 22:20:47',7.5,0,0.5,0,0,8.3),
  (170,'No Charge',NULL,'2015-04-30 22:05:02',28.6,0,0.5,0,0,28.6),
  (123,'No Charge',NULL,'2015-04-27 12:10:49',20,0.5,0.5,0,0,21.3),
  (181,NULL,NULL,'2015-04-27 11:51:01',6.5,0.5,0.5,0,0,7.8),
  (189,'No Charge',NULL,NULL,6.5,0,0.5,0,0,7)

CSV data ingest is possible using an INSERT INTO ... SELECT ... statement, but a table must be created first for the data to be inserted into. SELECT * must be used to select all columns, and the input file must match the number and ordering of the columns in the target table.

-- Insert records from a CSV File into the Taxi table
INSERT INTO taxi_trip_data
SELECT *
FROM FILE."/tmp/data/taxi_trip_data.csv"

Retrieving Data

A SELECT ... FROM statement will retrieve the records from the given table. You can reduce the amount of records returned by using a SELECT TOP n clause. This will select the first n records returned by the query. Use * to select all columns in the table.

-- Retrieve no more than 10 records from the Payment table
SELECT TOP 10 *
FROM payment
ORDER BY payment_id

-- Retrieve all records from the Vendor table
SELECT *
FROM vendor
ORDER BY vendor_id

Updating and Removing Records

The UPDATE ... SET statement is used to update matching key values for all records in a table. A WHERE clause can be used to specify the records to update.

-- Update the e-mail of, and add two employees and one cab to, the DDS vendor
UPDATE vendor
SET
  email = 'management@ddstaxico.com',
  num_emps = num_emps + 2,
  num_cabs = num_cabs + 1
WHERE vendor_id = 'DDS'

The DELETE FROM ... statement is used to delete records from a table. A WHERE clause can be used to specify the records to delete.

-- Delete payment 189
DELETE
FROM payment
WHERE payment_id = 189

Alter Table

Some properties can be altered or added after table creation, including indexes, dictionary encoding, and compression. Use the ALTER TABLE ... statement to specify the table and property you want to alter.

Indexes

ADD INDEX ... will add a column index.

ALTER TABLE payment
ADD INDEX (fare_amount)

ALTER TABLE taxi_trip_data
ADD INDEX (vendor_id)

Compression

SET COLUMN ... COMPRESSION ... adds the specified compression type to the desired column.

ALTER TABLE vendor
SET COLUMN vendor_name COMPRESSION snappy

ALTER TABLE vendor
SET COLUMN email COMPRESSION snappy

Important

Column compression is applied at a fixed interval, so be sure to verify later that the compression has been added.

Dictionary Encoding

Use ALTER COLUMN ... to add column properties like dictionary encoding.

-- Add the dictionary encoding column property to the taxi table vendor ID column
ALTER TABLE taxi_trip_data
ALTER COLUMN vendor_id VARCHAR(4, DICT) NOT NULL

Important

When adding a new property, all existing column properties must be listed along with any new property; those not listed will be removed

Filters & Aggregates

A simple filter can be performed using a SELECT ... WHERE statement.

-- Select all payments with a fare amount greater than 8
SELECT payment_id, fare_amount
FROM payment
WHERE fare_amount > 8 
ORDER BY payment_id

Note

This query makes use of the column index created in the preceding Indexes section.

BETWEEN can be used to filter values within a range, inclusively.

-- Select trips with passenger counts between 6 and 10
SELECT pickup_datetime, dropoff_datetime, trip_distance, passenger_count
FROM taxi_trip_data
WHERE passenger_count BETWEEN 6 AND 10
ORDER BY pickup_datetime

The CASE ... WHEN statement is a logical function that allows you to categorize records based on given filters.

-- Select the top 30 records where the pickup is between April 20th and
-- April 26th, then order them from longest trip to shortest trip.  A trip
-- description will also be returned, designating any trip of over six miles as
-- a "long trip", between three and six miles as a "medium trip", and three or
-- shorter as a "short trip".
SELECT TOP 30
  vendor_id,
  pickup_datetime,
  dropoff_datetime,
  passenger_count,
  CASE
    WHEN trip_distance > 6 THEN 'long trip'
    WHEN trip_distance > 3 THEN 'medium trip'
    ELSE 'short trip'
  END AS trip_description
FROM taxi_trip_data
WHERE pickup_datetime BETWEEN '2015-04-20 00:00:00.000' AND '2015-04-27 00:00:00.000'
ORDER BY trip_distance DESC

Aggregate functions are just a small offering of the available SQL functions. Aggregate functions are paired with a GROUP BY clause that groups the returned records by values in a given column. The HAVING clause filters records after they've been aggregated.

-- Select the longest, shortest, and average trip distance & passenger count for
-- each vendor whose average passenger count is higher than 1.4
SELECT
  vendor_id,
  MAX(trip_distance) max_trip,
  MIN(trip_distance) min_trip,
  ROUND(AVG(trip_distance),2) avg_trip,
  INT(AVG(passenger_count)) avg_passenger_count
FROM taxi_trip_data
GROUP BY vendor_id
HAVING AVG(passenger_count) > 1.4
ORDER BY vendor_id

Subqueries

Subqueries allow for nested queries within the clauses of a SQL statement. The below example is a simple SELECT ... WHERE statement that includes subqueries in each of its three clauses.

-- Show how tips compare between cash and credit card payments: retrieve unique
-- paid-by-cash fare & tip combinations and calculate the relationship between
-- the cash tip percentage and the average credit tip percentage, across all
-- cash fares that were at least as much as the lowest credit card fare.  The
-- tip factor will be the size of the cash tip in terms of the average credit
-- tip; e.g., "10" indicates the cash tip percentage is 10 times higher than the	
-- average credit tip
SELECT
  fare_amount,
  tip_amount,
  DECIMAL
  (
    (tip_amount / fare_amount) * 100 /
    (
      SELECT AVG(tip_amount / fare_amount) * 100 as avg_credit_tip_pct
      FROM payment
      WHERE payment_type = 'Credit'
    )
  ) as tip_factor_cash_vs_credit_pct
FROM
(
  SELECT DISTINCT
    fare_amount,
    tip_amount
  FROM payment
  WHERE payment_type = 'Cash'
) cash_fare_tip
WHERE fare_amount >=
(
  SELECT MIN(fare_amount)
  FROM payment
  WHERE payment_type = 'Credit'
)

Common Table Expression / With

The WITH statement can be used to give a subquery an alias for use in a larger query. You can reuse the aliased query as if it were another result set, but only in the query immediately following the WITH statement. The "parameters" to the WITH statement are the aliases that will be given to the result columns returned by the SELECT contained within the WITH; the number of "parameters" and columns in the SELECT clause should match.

-- Retrieve the set of cash payments that fall within the timestamp range of
-- recorded credit payments
WITH credit_pay_ts_min_max (min_pay_ts, max_pay_ts) AS
(
  SELECT
    MIN(payment_timestamp) AS min_pay_ts,
    MAX(payment_timestamp) AS max_pay_ts
  FROM payment
  WHERE payment_type = 'Credit'
)
SELECT
  payment_id,
  payment_timestamp,
  total_amount
FROM payment
WHERE
  payment_type = 'Cash' AND
  payment_timestamp BETWEEN
    (SELECT min_pay_ts FROM credit_pay_ts_min_max) AND
    (SELECT max_pay_ts FROM credit_pay_ts_min_max)
ORDER BY payment_timestamp

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.

-- Join Example 1 (Inner Join)
-- Retrieve payment information for rides having more than three passengers
SELECT
  t.payment_id,
  payment_type,
  total_amount,
  passenger_count,
  vendor_id,
  trip_distance
FROM taxi_trip_data t
INNER JOIN payment p ON t.payment_id = p.payment_id
WHERE
  passenger_count > 3
ORDER BY payment_id

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. Kinetica, by default, will output how the given join will be processed internally to the system log, under /opt/gpudb/core/logs.

-- Join Example 2 (Left Join)
-- 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.  Also, output
-- the join execution plan to the ODBC logs.
SELECT
  transaction_id,
  pickup_datetime,
  trip_distance,
  t.vendor_id,
  vendor_name
FROM taxi_trip_data t
LEFT JOIN vendor v ON t.vendor_id = v.vendor_id
WHERE payment_id != 0
ORDER BY transaction_id

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.

-- Full outer joins require both tables to be replicated. Set
-- merges like Union Distinct, Intersect, and Except need to use replicated
-- tables to ensure the correct results. Create a replicated copy of the Taxi
-- table and copy the records from the non-replicated table to the replicated
-- one
CREATE OR REPLACE REPLICATED TABLE taxi_info.taxi_trip_data_replicated (
  transaction_id LONG NOT NULL,
  payment_id LONG NOT NULL,
  vendor_id VARCHAR(4) NOT NULL,
  pickup_datetime TYPE_TIMESTAMP,
  dropoff_datetime TYPE_TIMESTAMP,
  passenger_count DECIMAL(2),
  trip_distance FLOAT,
  pickup_longitude FLOAT,
  pickup_latitude FLOAT,
  dropoff_longitude FLOAT,
  dropoff_latitude FLOAT
)

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.

-- Join Example 3 (Full Outer Join)
-- 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; also output the join plan
SELECT
  v.vendor_id vend_table_vendors,
  t.vendor_id taxi_table_vendors,
  COUNT(*) as total_records
FROM taxi_trip_data_replicated t
FULL OUTER JOIN vendor v ON v.vendor_id = t.vendor_id
WHERE
  v.vendor_id IS NULL OR
  t.vendor_id IS NULL
GROUP BY
  v.vendor_id,
  t.vendor_id

Create Table As

You can also create a table directly from a query using CREATE TABLE...AS. These can also be created as temporary tables by applying the TEMP specification. Much like regular table creation, you can also specify OR REPLACE to replace the table if it already exists.

-- Create a memory-only table containing all payments by credit card
CREATE OR REPLACE TEMP TABLE credit_payment AS (
  SELECT *
  FROM payment
  WHERE payment_type = 'Credit'
)

-- Create a persisted table with cab ride transactions greater than 5 miles
-- whose trip started during lunch hours
CREATE OR REPLACE TABLE lunch_time_rides AS (
  SELECT
    HOUR(pickup_datetime) hour_of_day,
    vendor_id,
    passenger_count,
    trip_distance
  FROM taxi_trip_data
  WHERE
    HOUR(pickup_datetime) BETWEEN '11' AND '14' AND
    trip_distance > 5
)

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.

-- Union Example 1 (Union All)
-- 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
SELECT
  '2015-04-01 - 2015-04-15' pickup_window_range,
  INT(AVG(passenger_count)) avg_pass_count,
  ROUND(AVG(trip_distance),2) avg_trip,
  MIN(trip_distance) min_trip,
  MAX(trip_distance) max_trip
FROM taxi_trip_data
WHERE pickup_datetime BETWEEN '2015-04-01' AND '2015-04-15 23:59:59.999'
UNION ALL
SELECT
  '2015-04-16 - 2015-04-23',
  INT(AVG(passenger_count)),
  ROUND(AVG(trip_distance),2),
  MIN(trip_distance),
  MAX(trip_distance)
FROM taxi_trip_data
WHERE pickup_datetime BETWEEN '2015-04-16' AND '2015-04-23 23:59:59.999'

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

-- Union Example 2 (Intersect)
-- Retrieve locations (as lat/lon pairs) that were both pick-up and drop-off
-- points
SELECT
  pickup_latitude AS latitude,
  pickup_longitude AS longitude
FROM taxi_trip_data_replicated
WHERE
  pickup_latitude <> 0 AND
  pickup_longitude <> 0
INTERSECT
SELECT
  dropoff_latitude,
  dropoff_longitude
FROM taxi_trip_data_replicated
ORDER BY latitude, longitude

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.

-- Union Example 3 (Except)
-- 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
SELECT vendor_id
FROM taxi_trip_data_replicated
WHERE HOUR(pickup_datetime) BETWEEN 0 AND 11
EXCEPT
SELECT vendor_id
FROM taxi_trip_data_replicated
WHERE HOUR(pickup_datetime) BETWEEN 12 AND 23

Truncate

Use TRUNCATE to remove all records from a table without deleting the table.

TRUNCATE TABLE credit_payment

Complete Sample

Included below is a complete example containing all the above queries, the data files, and output.

• SQL Script
• SQL Data File (taxi trip data)
• SQL Output

The script can be run via KiSQL:

$ /opt/gpudb/kitools/kisql/kisql -host localhost -echoSql true < sql_tutorial.kisql

Note

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