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.

Topics

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

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 Limitations and Cautions 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 Limitations 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 Limitations 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`

Conditional 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 ¶

Creates a new table in the configured ParentSet collection. If OR REPLACE is specified, any existing table with the same name will be dropped before creating this one. If REPLICATED is specified, the table will be distributed within the database as a replicated 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 TABLE ... AS ¶

Creates a new table from the given query in the configured ParentSet collection. If OR REPLACE is specified, any existing table with the same name will be dropped before creating this one. If TEMP is specified, the table will be read-only and will be removed the next time the database is restarted; otherwise, the table will be read/write and will persist through database restarts.

The general format is:

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

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

CREATE TEMP TABLE new_table AS
(
    SELECT *
    FROM old_table
)

Note

Primary & foreign keys are not transferred to the new table, though shard keys will be. See Limitations for other restrictions.

To create a permanent read/write table, replacing one of the same name, if it exists:

CREATE OR REPLACE 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>

TRUNCATE TABLE ¶

Deletes all the records from a table:

TRUNCATE TABLE <table name>

DROP TABLE ¶

Removes a table from the database:

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"

Scalar Column Functions
- Null Functions (except ZEROIFNULL )
- Conditional Functions (equality-based)
Aggregate Column Functions
- COUNT
- COUNT DISTINCT
WHERE (predicate condition must be equality-based)
JOIN (join condition must be equality-based)

Inapplicable Operations ¶

Subqueries requiring a temporary view to be created
GROUP BY
UNION
INTERSECT
EXCEPT
CREATE TABLE...AS

Store-Only Columns ¶

Columns marked store-only are only stored on disk, not in memory. Because they are not available for processing, only data extraction operations can be applied to them.

Inapplicable Operations ¶

Scalar or Aggregate Column Functions
Subqueries requiring a temporary view to be created
WHERE
GROUP BY
JOIN
UNION
INTERSECT
EXCEPT
CREATE TABLE...AS

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

Table Of Contents

SQL Support¶