Version:

SQL Support

Kinetica has broad support for the SQL-92 standard through its ODBC connector interface. For details on installation, configuration, & use, see the The ODBC/JDBC Connector section.

Query

The basic form of the supported SELECT statement is:

SELECT [DISTINCT | TOP <n>] <column expression list>
FROM <table name>
    [<join type> JOIN <join table name> ON <join expression>],...
[WHERE <filtering expression list>]
[GROUP BY <grouping expression list>]
[HAVING <group filtering expression list>]
[ORDER BY <ordering expression list>]

Note

The * can be used to specify all columns in the column expression list.

TOP <n> returns up to n=20000 records by default, but is configurable.

See Joins for more detail & limitations on joins. Supported join type keywords are:

  • INNER
  • CROSS
  • LEFT
  • RIGHT
  • FULL OUTER

The grouping expression list may contain column names, expressions, or column positions (e.g., GROUP BY 2 to aggregate on the 2nd column in the SELECT list).

Ordering is NULLS FIRST. Changing this behavior is not currently supported.

For example:

SELECT
    e.first_name || ' ' || e.last_name as "Employee_Name",
    m.first_name || ' ' || m.last_name as "Manager_Name"
FROM
    emp e
LEFT JOIN
    emp m ON e.manager_id = m.id
WHERE
    e.dept_id = 1
ORDER BY
    e.hire_date

Set Operations

UNION

The UNION set operator creates a single list of records from the results of two SELECT statements. Use the ALL keyword to keep all records from both sets; omit it to remove duplicate records and form a single list of records unique between the two sets. See /create/union for limitations.

<SELECT statement>
UNION [ALL]
<SELECT statement>

INTERSECT

The INTERSECT set operator creates a single list of records that exist in both of the result sets from two SELECT statements. See /create/union for limitations.

<SELECT statement>
INTERSECT
<SELECT statement>

EXCEPT

The EXCEPT set operator performs set subtraction, creating a single list of records that exist in the first SELECT statement's result set, but not in the second SELECT statement's result set. See /create/union for limitations:

<SELECT statement>
EXCEPT
<SELECT statement>

WITH (Common Table Expressions)

The WITH set operation, also known as a Common Table Expression (CTE) creates a set of data that can be aliased and used one or more times in subsequent operations. The aliased set can be used within the SELECT, FROM, or WHERE clauses of a subsequent query; a subsequent CTE within the same WITH operation; or an INSERT, UPDATE, or DELETE statement.

Recursive WITH operations are not supported--the aliased set cannot refer to itself. The alias must be unique within the WITH statement--no other column or column alias can be similarly named, for example. Also, when used in a FROM clause and given a table alias, the table alias must be preceded with AS.

Each CTE definition within a WITH statement is structured as follows:

<cte name> [(column list)] AS (<SELECT statement>)

Each WITH statement can contain one or more CTE definitions, followed by a SELECT, INSERT, UPDATE, or DELETE statement, as shown here:

WITH <cte definition>,...
<SELECT | INSERT | UPDATE | DELETE statement>

For example:

WITH
    dept2_emp_sal_by_mgr (manager_id, sal) AS
    (
        SELECT manager_id, sal
        FROM emp
        WHERE dept_id = 2
    )
SELECT
    manager_id dept2_mgr_id,
    MAX(sal) dept2_highest_emp_sal_per_mgr,
    COUNT(*) as dept2_total_emp_per_mgr
FROM dept2_emp_sal_by_mgr
GROUP BY manager_id
WITH
    dept2_emp AS
    (
        SELECT first_name, last_name, manager_id
        FROM emp
        WHERE dept_id = 2
    ),
    dept2_mgr AS
    (
        SELECT first_name, last_name, id
        FROM emp
        WHERE dept_id = 2
    )
INSERT INTO dept2_emp_mgr_roster (emp_first_name, emp_last_name, mgr_first_name, mgr_last_name)
SELECT d2emp.first_name, d2emp.last_name, d2mgr.first_name, d2mgr.last_name
FROM
    dept2_emp as d2emp
    JOIN dept2_mgr as d2mgr ON d2emp.manager_id = d2mgr.id

Expressions

An expression can consist of a literal constant, a column name, or a function applied to a constant or column name. A compound expression is an operation or function applied to one or more expressions.

The following are the supported expression operators:

  • + addition
  • - subtraction
  • * multiplication
  • / division
  • () grouping
  • || string concatenation

Note

Use double quotes to specify column names in a case-sensitve manner.

Conversion Functions

Conversion Function Description

CAST(expr AS [SQL_]<conv_type>)

or

CONVERT(expr, SQL_<conv_type>)

Converts expr into conv_type data type. See Conversion Types for the complete set of data types.

Note: CONVERT requires the SQL_ prefix, while it is optional with CAST; however, CAST does require the prefix for the following data types:

  • SQL_TYPE_DATE
  • SQL_TYPE_TIME
  • SQL_TYPE_TIMESTAMP
  • SQL_WVARCHAR

Conversion Types

Numeric String Date/Time
  • BIGINT
  • DECIMAL
  • DOUBLE
  • FLOAT
  • INTEGER
  • NUMERIC
  • REAL
  • SMALLINT
  • TINYINT
  • CHAR
  • LONGVARCHAR
  • VARCHAR
  • WVARCHAR
  • DATE
  • TIME
  • TIMESTAMP
  • TYPE_DATE
  • TYPE_TIME
  • TYPE_TIMESTAMP

Date/Time Functions

Date/Time Function Description
CURRENT_DATE() Returns the current system date
CURRENT_TIME() Returns the current system time
CURRENT_TIMESTAMP() Returns the current system date/time
DATEDIFF(expr_end, expr_begin) Determines the difference between two dates, irrespective of time component, as the number of days when expr_begin is subtracted from expr_end; returns a negative number of days if expr_begin occurs after expr_end
DAY(expr) Alias for DAYOFMONTH
DAYNAME(expr) Extracts the day of the week from expr and converts it to the corresponding day name [Sunday - Saturday ]
DAYOFMONTH(expr) Extracts the day of the month from expr [1 - 31]
DAYOFWEEK(expr) Extracts the day of the week from expr [1 - 7]; (1 = Sunday)
DAYOFYEAR(expr) Extracts the day of the year from expr [1 - 366]
HOUR(expr) Extracts the hour of the day from expr [0 - 23]
<expr> + INTERVAL <amount> <part> <expr> - INTERVAL <amount> <part>

Adds to or subtracts from the date/time expr the integral amount units of type part. This mirrors the behavior of the TIMESTAMPADD function, only with a different format and different date/time part constants. The following date/time constants are supported for part:

Constant Description
YEAR Year is modified by interval amount (not affected by leap year, etc.)
MONTH Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g. Apr 31st -> Apr 30th)
DAY Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
HOUR Hour is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
MINUTE Minute is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
SECOND Second is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
MILLISECOND Millisecond is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
MICROSECOND Microsecond is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
QUARTER Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same way as in MONTH description, but only on final month (i.e., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
WEEK Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
MINUTE(expr) Extracts the minute of the day from expr [0 - 59]
MONTH(expr) Extracts the month of the year from expr [1 - 12]
MONTHNAME(expr) Extracts the month of the year from expr and converts it to the corresponding month name [January - December ]
QUARTER(expr) Extracts the quarter of the year from expr [1 - 4]; (1 = January, February, & March)
SECOND(expr) Extracts the seconds of the minute from expr [0 - 59]
TIMESTAMPADD(ts_part, ts_amount, expr)

Adds the positive or negative integral ts_amount units of type ts_part to expr. The following date/time constants are supported for ts_part:

Constant Description
SQL_TSI_YEAR Year is modified by interval amount (not affected by leap year, etc.)
SQL_TSI_MONTH Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g. Apr 31st -> Apr 30th)
SQL_TSI_DAY Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
SQL_TSI_HOUR Hour is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
SQL_TSI_MINUTE Minute is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
SQL_TSI_SECOND Second is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
SQL_TSI_FRAC_SECOND Nanosecond is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
SQL_TSI_QUARTER Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same way as in MONTH description, but only on final month (i.e., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
SQL_TSI_WEEK Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
TIMESTAMPDIFF(ts_part, expr_begin, expr_end)

Determines the difference between two dates, calculating the result in the units specified; more precisely, how many units of ts_part need to be added to or subtracted from expr_begin to equal expr_end (or get as close as possible to after going past) using the rules specified in TIMESTAMPADD. The ts_part constants are the same as in TIMESTAMPADD.

Note: this is symmetric with TIMESTAMPADD in all cases; e.g., adding 1 MONTH to Mar 31st results in Apr 30th, and the TIMESTAMPDIFF in MONTH units between those two dates is 1.

WEEK(expr) Extracts the week of the year from expr [1 - 53]; each full week starts on Sunday (1 = week containing Jan 1st)
YEAR(expr) Extracts the year from expr; 4-digit year, A.D.

Math Functions

Math Function Description
ABS(expr) Returns the absolute value of expr
ACOS(expr) Calculates the inverse cosine (arccosine) of expr
ASIN(expr) Calculates the inverse sine (arcsine) of expr
ATAN(expr) Calculates the inverse tangent (arctangent) of expr
ATAN2(x, y) Calculates the inverse tangent (arctangent) of the angle at the origin where the opposite end of the hypotenuse is at (x, y)
CEIL(expr) Alias for CEILING
CEILING(expr) Rounds expr up to the next highest integer
COS(expr) Calculates the cosine of expr
COT(expr) Calculates the cotangent of expr
DEGREES(expr) Converts expr (in radians) to degrees
EXP(expr) Raises e to the power of expr
FLOOR(expr) Rounds expr down to the next lowest integer
GREATEST(expr_a, ..., expr_N) Returns whichever of expr_a through expr_N has the largest value, based on typed comparison
LEAST(expr_a, ..., expr_N) Returns whichever of expr_a through expr_N has the smallest value, based on typed comparison
LOG(expr) Calculates the natural logarithm of expr
LOG10(expr) Calculates the base-10 logarithm of expr
MOD(dividend, divisor) Calculates the remainder after integer division of dividend by divisor
PI() Returns the value of pi
POWER(base, exponent) Calculates base raised to the power of exponent
RADIANS(expr) Converts expr (in degrees) to radians
ROUND(expr, scale)

Rounds expr to the nearest decimal number with scale decimal places when scale is a positive number; rounds to the nearest number such that the result has -(scale) zeros to the left of the decimal point when scale is negative; use scale of 0 to round to the nearest integer. Examples:

  • ROUND(12345.678, 2) -> 12345.68
  • ROUND(12345.678, 0) -> 12346
  • ROUND(12345.678, -2) -> 12300
SIGN(expr)

Determines whether a number is positive, negative, or zero; returns one of the following three values:

  • positive: 1
  • zero: 0
  • negative: -1
SIN(expr) Calculates the sine of expr
SQRT(expr) Calculates the square root of expr
TAN(expr) Calculates the tangent of expr
TRUNCATE(expr, scale)

Rounds expr down to the nearest decimal number with scale decimal places, following the same rules as ROUND. Examples:

  • TRUNCATE(12345.678, 2) -> 12345.67
  • TRUNCATE(12345.678, 0) -> 12345
  • TRUNCATE(12345.678, -2) -> 12300

Null Functions

Some of the following null functions require parameters to be of convertible data types. Note that limited-width (charN) & unlimited-width (non-charN) string types are not convertible.

Null Function Description
COALESCE(expr_a, ..., expr_N) Returns the value of the first expression that is not null starting with expr_a and ending with expr_N. If all are null, then null is returned. All expressions should be of the same or convertible data type.
IFNULL(expr_a, expr_b) Returns expr_a if it is not null; otherwise, returns expr_b. Both should be of the same or convertible data type.
ISNULL(expr) Returns 1 if expr is null; otherwise, returns 0
NULLIF(expr_a, expr_b) Returns null if expr_a equals expr_b; otherwise, returns the value of expr_a; both expressions should be of the same or convertible data type.
NVL(expr_a, expr_b) Alias for IFNULL
NVL2(expr, value_if_not_null, value_if_null) Evaluates expr: if not null, returns value_if_not_null; if null, returns value_if_null. Both value_if_not_null & value_if_null should be of the same data type as expr or implicitly convertible.
ZEROIFNULL(expr) Alias for IFNULL(expr, 0); not applicable to strings

String Functions

The following functions only apply to limited-width (charN) strings.

String Function Description
ASCII(expr) Returns the ASCII code for the first character in expr
CHAR(expr) Returns the character associated with the ASCII code in expr
CONCAT(expr_a, expr_b) Performs a string concatenation of expr_a & expr_b
LCASE(expr) Converts expr to lowercase
LEFT(expr, num_chars) Returns the leftmost num_chars characters from expr
LENGTH(expr) Returns the number of characters in expr
LOCATE(match_expr, ref_expr, [start_pos]) Returns the starting position of the first match of match_expr in ref_expr, starting from position 1 or start_pos (if specified)
LOWER(expr) Alias for LCASE
LTRIM(expr) Removes whitespace from the left side of expr
POSITION(match_expr, ref_expr, [start_pos]) Alias for LOCATE
REPLACE(ref_expr, match_expr, repl_expr) Replaces every occurrence of match_expr in ref_expr with repl_expr
RIGHT(expr, num_chars) Returns the rightmost num_chars characters from the expr
RTRIM(expr) Removes whitespace from the right side of expr
SUBSTR(expr, start_pos, num_chars) Alias for SUBSTRING
SUBSTRING(expr, start_pos, num_chars) Returns num_chars characters from the expr, starting at the 1-based start pos
TRIM(expr) Removes whitespace from both sides of expr
UCASE(expr) Converts expr to uppercase
UPPER(expr) Alias for UCASE

Other Scalar Functions

Scalar Function Description
DECODE(expr, match_a, value_a, ..., match_N, value_N) Evaluates expr: returns the first value whose corresponding match is equal to expr
IF(expr, value_if_true, value_if_false) Evaluates expr: if true, returns value_if_true; otherwise, value_if_false

CASE

The case statement acts as a scalar function, but has two more complex forms. Note that for each of these CASE statements, the value expressions must all be of the same or convertible data type.

In the first form, each WHEN is followed by a conditional expression whose corresponding THEN expression will have its value returned, if true. Control will continue through each WHEN until a match is found and the corresponding value returned; if no match is found, the value of the ELSE expression will be returned, or null, if no ELSE clause exists.

CASE
    WHEN <cond_expr_a> THEN <value_expr_a>
    ...
    WHEN <cond_expr_N> THEN <value_expr_N>
    ELSE <value_expr>
END

In the second form, the CASE expression is evaluated. A match of that result will be attempted against each WHEN expression until a match is found and the value of the corresponding THEN expression returned; if no match is found, the value of the ELSE expression will be returned, or null, if no ELSE clause exists.

CASE <expr>
    WHEN <match_expr_a> THEN <value_expr_a>
    ...
    WHEN <match_expr_N> THEN <value_expr_N>
    ELSE <value_expr>
END

Note

This second version below has greater optimization than the first.

Examples:

CASE
    WHEN color = 1 THEN 'Red'
    WHEN color >= 2 THEN 'Green'
    ELSE 'Blue'
END
CASE mod(length(text), 2)
    WHEN 0 THEN 'Even'
    WHEN 1 THEN 'Odd'
    ELSE null
END

Aggregate Functions

Aggregate Function Description
COUNT(*) Returns the number of records in a table
COUNT(expr) Returns the number of non-null data values in expr
COUNT(DISTINCT expr) Returns the number of distinct non-null data values in expr
SUM(expr) Sums all the values of expr
MIN(expr) Finds the minimum value of expr
MAX(expr) Finds the maximum value of expr
AVG(expr) Calculates the average value of expr
MEAN(expr) Alias for AVG
STDDEV(expr) Alias for STDDEV_POP
STDDEV_POP(expr) Calculates the population standard deviation of the values of expr
STDDEV_SAMP(expr) Calculates the sample standard deviation of the values of expr
VAR(expr) Alias for VAR_POP
VAR_POP(expr) Calculates the population variance of the values of expr
VAR_SAMP(expr) Calculates the sample variance of the values of expr

Predicates

Predicate are generally used within a SQL WHERE clause to query records. They compare the values of two or more expressions; whenever a record meets the criteria defined in a predicate clause it will be marked as eligible to be part of the query result set. If it meets all predicate clauses defined within a query, it will be returned in the result set.

A single predicate clause may use a simple predicate operator to compare the values of two expressions or a more complex predicate clause form. A compound predicate clause uses a compound predicate operator to link together multiple predicate clauses to further refine a result set.

Unlimited-width (non-charN) strings can only be used within equality-based predicates, e.g. =, IN, etc.

Predicate Operators

  • = equality
  • != or <> inequality
  • < less than
  • <= less than or equal to
  • > greater than
  • >= greater than or equal to

Predicate Clauses

In the following list of predicate clauses, ref_expr is the reference expression to apply the predicate to; note that EXISTS has no reference expression.

Predicate Clause Description
<expr_a> <pred_op> <expr_b> Matches records where expr_a relates to expr_b according to predicate operator pred_op.
<ref_expr> <pred_op> ALL (<SELECT statement>) Matches records where the reference expression ref_expr relates to all of the results of SELECT statement according to the predicate operator pred_op
<ref_expr> <pred_op> ANY (<SELECT statement>) Matches records where the reference expression ref_expr relates to any of the results of SELECT statement according to the predicate operator pred_op
<ref_expr> [NOT] BETWEEN <begin_expr> AND <end_expr> Matches records where the reference expression ref_expr is (or is NOT) between the values of begin_expr and end_expr
<ref_expr> [NOT] IN (<match_list>) Matches records where the reference expression ref_expr is (or is NOT) in the match_list list of match values. The list can either be a comma-separated list of terms/expressions or the result of a SELECT statement.
<ref_expr> IS [NOT] NULL Matches records where the reference expression ref_expr is (or is NOT) null.
<ref_expr> [NOT] LIKE <match_expr>

Matches records where reference expression ref_expr does (or does NOT) match the string value of match_expr. The match is a string literal one, with the following exceptions:

  • % matches any string of 0 or more characters
  • _ matches any single character
[NOT] EXISTS (<SELECT statement>)

Matches records where SELECT statement returns 1 or more records.

Note: This clause has limited utility, as correlated subqueries, upon whose real value it relies, are unsupported at this time.

Compound Predicate Operators

Predicate Operator Description
<pred_a> AND <pred_b> Matches records where both pred_a & pred_b are true
<pred_a> OR <pred_b> Matches records where either pred_a or pred_b is true
NOT <pred_b> Matches records where pred is false

Non-Correlated Subqueries

These are subqueries that are self-contained, in that they can be executed independently of the surrounding query.

SELECT num0
FROM calcs
WHERE num0 = (SELECT max(num0) FROM calcs)
SELECT num0, (SELECT max(num0) FROM calcs) as max_num0
FROM calcs
SELECT *
FROM table1
WHERE x IN (SELECT y FROM table2)
SELECT *
FROM table1
WHERE EXISTS (SELECT y FROM table2 WHERE y > 5)
SELECT max(num0)
FROM (SELECT num0 FROM calcs) as num0s

Note

SQL-92 requires that subqueries in FROM clauses have aliases, even if a FROM clause only consists of a single subquery; the num0s table alias in the last example serves that function. The as keyword is optional.

Hints

Hint strings (KI_HINT) can be added as comments within queries, and affect just the query in which they appear. They will override the corresponding client & server settings of the same names (when such settings exist). For example:

SELECT /* KI_HINT_KEEP_TEMP_TABLES, KI_HINT_ROWS_PER_FETCH(20000) */ first_name, last_name
FROM customer
Hint Description
KI_HINT_ALLOW_PARTIAL_PASSDOWN This avoids the error: Query not fully handled, though the query should be reported for further investigation & handling. This hint will try to process the query as best as it can, which will probably not have optimal efficiency.
KI_HINT_DONT_COMBINE Don’t combine joins and unions for this query.
KI_HINT_DONT_FILTER_IN_AGGREGATE Use when issuing a filter on an aggregation of a join
KI_HINT_EXPLAIN_JOINS Output join explain plan to logs
KI_HINT_KEEP_CROSSJOINS Use when issuing a cross join that is being handled as an inner join and returning incorrect results
KI_HINT_KEEP_TEMP_TABLES Don’t erase temp tables created by this query
KI_HINT_MATERIALIZE_AFTER_JOIN Use projection to materialize joins
KI_HINT_MAX_QUERY_DIMENSIONS(n) Number of joins within the query that are not against primary key columns
KI_HINT_MAX_ROWS_TO_FETCH(n) Set maximum number of rows for a query to retrieve
KI_HINT_NO_PASSDOWN Don’t use optimizations for this query
KI_HINT_ROWS_PER_FETCH(n) Set number of rows to be requested in each batch (also used for batch size when inserting)
KI_HINT_SIMULATION Don’t issue calls to the database, but output the calls that would be made to the log


Data Definition (DDL)

Kinetica supports the basic notion of SQL tables as containers of one or more columns of data. Tables can be created, altered, and dropped.

A column definition consists of a column type and optional column size, column properties, and nullability. Column properties are used to optimize data storage & speed.

The format of a defined column is column name, followed by column definition. A column definition is column type optionally followed by any column size limit or column properties all enclosed in parentheses, followed by an optional nullability statement:

<column name> <column type> [(<column size / column property>,...)] [[NOT] NULL]

This format applies to any DDL statement requiring the definition of columns, like CREATE TABLE and ALTER TABLE (when adding/modifying a column).

For example, the following are valid defined columns:

id INT(SHARD_KEY)                -- makes the id column an integer that is also the table's shard key
name VARCHAR(50, TEXT_SEARCH)    -- makes the name column a 50-char limited string that is text-searchable
ip VARCHAR(IPV4)                 -- makes the ip column a string in IPv4 format
cost DECIMAL(10, 2, STORE_ONLY)  -- makes the cost column an 8.2 decimal that is not held in memory

Column Types

Category Data Type Description
Number INTEGER Native type: integer
INT Alias for INTEGER
BIGINT Native type: long
LONG Alias for BIGINT
FLOAT Native type: float
REAL Alias for FLOAT
DOUBLE Native type: double
DECIMAL Alias for BIGINT
DECIMAL(P,S)

Native type: varies by P & S

P S Native Type
1, 2 0 int8
3, 4 0 int16
5, 6 0 integer
7- n 0 long
1- n 1-4 decimal
5, 6 5, 6 float
7- n 5- n double
NUMERIC Alias for DECIMAL
String VARCHAR Native type: string; character limit based on configured system property
VARCHAR(N) Native type: char1 - char256 or string, whichever is large enough to hold N characters
STRING Alias for VARCHAR
TEXT Alias for VARCHAR
Date/Time TYPE_DATE Native type: date
TYPE_TIME Native type: time
TYPE_TIMESTAMP Native type: timestamp
DATE Alias for TYPE_DATE
TIME Alias for TYPE_TIME
TIMESTAMP Alias for TYPE_TIMESTAMP

Column Properties

Property Description
DISK_OPTIMIZED Applies disk-optimized data handling to a column
IPV4 Treats the associated string-based column as an IPv4 address
PRIMARY_KEY Treats the associated column as a primary key, or part of a composite primary key if other columns also have this designation
SHARD_KEY Treats the associated column as a shard key, or part of a composite shard key if other columns also have this property
STORE_ONLY Applies store-only data handling to a column
TEXT_SEARCH Applies text-searchability to a column

CREATE TABLE

The basic form of the supported CREATE TABLE statement follows. See here for column definition format.

CREATE [OR REPLACE] [REPLICATED] TABLE <table name>
(
    <column name> <column definition>,
    ...
    <column name> <column definition>,
    [PRIMARY KEY (<column name>,...)]
)

For example:

CREATE REPLICATED TABLE various_types
(
    i   INTEGER NOT NULL,                  -- non-nullable integer
    bi  BIGINT(SHARD_KEY) NOT NULL,        -- long, shard key
    f   FLOAT,                             -- float
    d   DOUBLE(STORE_ONLY),                -- double, not in-memory
    s   VARCHAR(STORE_ONLY, TEXT_SEARCH),  -- string, searchable, not in-memory, only limited in size by system-configured value
    c   VARCHAR(30),                       -- char32
    ip  VARCHAR(IPV4),                     -- IP address
    ts  TYPE_TIMESTAMP,                    -- timestamp
    dc1 DECIMAL,                           -- long
    dc2 DECIMAL(18,4),                     -- decimal
    dc3 DECIMAL(6,5),                      -- float
    dc4 DECIMAL(7, 5, STORE_ONLY),         -- double, not in-memory
    n   NUMERIC(5, 3),                     -- will use the next largest native numeric type to hold the specified number type
    PRIMARY KEY (i, bi)                    -- primary key columns must be NOT NULL
)

CREATE TEMP TABLE ... AS

Creates a new table in the configured ParentSet collection; default collection is MASTER. The general format is:

CREATE [OR REPLACE] TEMP TABLE <table name> AS
(
    <SELECT statement>
)

For example, to create a temporary table, failing if it already exists:

CREATE TEMP TABLE new_table AS
(
    SELECT *
    FROM old_table
)

Note

The table created is temporary, in that it will not exist if the database is restarted. Also, primary & foreign keys are not transferred to the new table.

To create a temporary table, replacing one of the same name, if it exists:

CREATE OR REPLACE TEMP TABLE new_table AS
(
    SELECT *
    FROM old_table
)

ALTER TABLE

Alters a table's name, time-to-live (TTL), or column set. A column can be added or removed, or have its column definition modified.

Note

In the default configuration of Kinetica, tables cannot be renamed or have their TTLs adjusted via ALTER TABLE. By default, the ODBC connector uses the MASTER collection in the database. This collection is protected, meaning that no table-level alterations can be made to tables contained within it. For ALTER TABLE to be able to perform these modifications successfully, a non-protected collection must be used instead. See ODBC Configuration for details about changing the ParentSet parameter, which specifies the collection to use.

Rename Table

A table can be renamed. See note for limitations.

ALTER TABLE <table name>
RENAME TO <new table name>

Set TTL

A table's time-to-live can be altered. See note for limitations.

ALTER TABLE <table name>
SET TTL <new ttl>

Add Column

A column can be added, specifying a column definition.

A new column can have its values initially populated through the use of the DEFAULT keyword. These values can either be a string/numeric constant or the name of an existing column in the table from which values can be copied into the new column. This default value is only in effect for the column creation; the new column will have no default value after that.

ALTER TABLE <table name>
ADD <column name> <column definition> [DEFAULT <string/numeric constant | column name>]

For example:

ALTER TABLE employee
ADD salary NUMERIC(10, 2, STORE_ONLY) NOT NULL DEFAULT 0

Modify Column

A column can have its column definition modified, affecting column type, column size, column properties, and nullability.

If a column is modified to be non-nullable, it will be populated with default values--empty string for string fields and 0 for numeric fields.

Either of the following can be used to modify a column:

ALTER TABLE <table name>
MODIFY [COLUMN] <column name> <column definition>
ALTER TABLE <table name>
ALTER COLUMN <column name> <column definition>

For example:

ALTER TABLE employee
ALTER COLUMN first_name VARCHAR(50, TEXT_SEARCH) NOT NULL

Drop Column

An existing column can be removed from a table:

ALTER TABLE <table name>
DROP COLUMN <column name>

DROP TABLE

Drops a table:

DROP TABLE <table name>


Data Manipulation (DML)

INSERT

To insert one record with literal values, use this format:

INSERT INTO <table name> [(<column list>)]
VALUES (<column value list>)

For example:

INSERT INTO target_table (x, y, point_name)
VALUES (99, 100, 'Vertex A')

To insert multiple records, using another table as the source, use this format:

INSERT INTO <table name> [(<column list>)]
<SELECT statement>

For example:

INSERT INTO target_table
SELECT *
FROM source_table
WHERE x > 100

Note

When specifying a column list, any non-nullable fields not included in the list will be given default values--empty string for strings, and 0 for numerics. The fields in the column list and the fields selected must align.

UPDATE

Updates can only set columns to constant values, not other column values or results of expressions. The general format is:

UPDATE <table name>
SET
  <key 1> = <value 1>,
  ...
  <key n> = <value n>
[WHERE <expression list>]

For example:

UPDATE emp
SET
  sal = 100000,
  mgr = 1234
WHERE empno = 1111

DELETE

Deletes records from a table; the general format is:

DELETE
FROM <table name>
[WHERE <expression list>]

For example:

DELETE
FROM emp
WHERE empno = 1111

Reading From CSV files

  • File must be accessible from machine running ODBC Server

  • First row should contain column names, and optionally types, similar to importing with GAdmin

  • File must have fields separated by commas

  • Strings optionally enclosed in double-quotes

  • Use double-quotes for string data containing commas; use two consecutive double-quotes as an escape code for double-quoted string data containing double-quotes:

    "This string contains a "" quote mark and a "","" double-quoted comma."

INSERT INTO calcs (int0, int1, int2, int3)
SELECT int0, int1, int2, int3
FROM FILE."C:\Tableau\Data\Calcs.csv"


Not Yet Supported SQL

  • Correlated Subqueries (though these can be supported via rewrites as joins)
  • Ranking Functions
    • RANK
    • DENSE_RANK
    • ROW_NUMBER
    • NTILE
  • Partitioning
    • OVER/PARTITIONED BY
  • Spatial Queries (e.g., SQL GIS)
  • View DDL
    • CREATE VIEW
    • CREATE OR REPLACE VIEW
    • DROP VIEW


Limitations

  • Kinetica has some limitations for any columns marked as store-only and string columns lacking a charN attribute. For example, store-only columns may not be used in a filter, and therefore are not allowed in SQL WHERE and GROUP BY clauses.