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 [<schema name>.]<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>]
[LIMIT [<offset>, ]<num rows>]

Note

• The * can be used to specify all columns in the column expression list.
• Columns can be double-quoted to induce case-sensitivity and to use reserved words as column names; e.g., "PERCENT".
• TOP <n> returns the first n records (up to 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.
• LIMIT applies paging to the result set, starting at the 0-based offset (if specified) and returning num rows records.

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

Tableless Query

A query without a FROM clause can be used to return a single row of data containing a constant or constant expression.

For example, to select the value of pi using the arccosine function:

SELECT acos(0) * 2

Aggregation

The GROUP BY clause can be used to segment data into groups and apply aggregate functions over the values within each group. Aggregation functions applied to data without a GROUP BY clause will be applied over the entire result set.

Note

GROUP BY can operate on columns, column expressions, or the position of a member of the SELECT clause (where 1 is the first element), but does not work on column aliases.

For example, to find the average cab fare from the taxi data set:

SELECT AVG(total_amount)
FROM nyctaxi

To find the minimum, maximum, & average trip distances, as well as the average passenger count for each vendor per year from the taxi data set (weeding out data with errant trip distances):

SELECT
    vendor_id,
    YEAR(pickup_datetime) AS Year,
    MAX(trip_distance) max_trip,
    MIN(trip_distance) min_trip,
    AVG(trip_distance) avg_trip,
    AVG(passenger_count) avg_passenger_count
FROM nyctaxi
WHERE
    trip_distance > 0 AND
    trip_distance < 100
GROUP BY vendor_id, 2
ORDER BY vendor_id, Year

Grouping

The GROUP BY clause can also be used to apply the following grouping functions over the values within each group:

• ROLLUP
• CUBE
• GROUPING SETS

With each of these, the GROUPING() aggregate function can be used to distinguish null values in the data from null values generated by the grouping operation.

For instance, the following SELECT will turn all null values in the Sector column into an <UNKNOWN SECTOR> group and the null value generated by the grouping operation into an <ALL SECTORS> group:

SELECT
    CASE
        WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
        ELSE NVL(Sector, '<UNKNOWN SECTOR>')
    END AS Sector,

ROLLUP

The ROLLUP(expr list) function calculates n + 1 aggregates for n number of columns in expr list.

For example, the following query will aggregate the average opening stock price for these groups:

• Each market sector & stock symbol pair
• Each market sector
• All sectors and symbols

SELECT
    CASE
        WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
        ELSE NVL(Sector, '<UNKNOWN SECTOR>')
    END AS Sector,
    CASE
        WHEN (GROUPING(Symbol) = 1) THEN '<ALL SYMBOLS>'
        ELSE NVL(Symbol, '<UNKNOWN SYMBOL>')
    END AS Symbol,
    AVG(Open) AS AvgOpen
FROM Stocks
GROUP BY ROLLUP(Sector, Symbol)
ORDER BY Sector, Symbol

CUBE

The CUBE(expr list) function calculates 2ⁿ aggregates for n number of columns in expr list.

For example, the following query will aggregate the average opening stock price for these groups:

• Each market sector & stock symbol pair
• Each market sector
• Each stock symbol
• All sectors and symbols

SELECT
    CASE
        WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
        ELSE NVL(Sector, '<UNKNOWN SECTOR>')
    END AS Sector,
    CASE
        WHEN (GROUPING(Symbol) = 1) THEN '<ALL SYMBOLS>'
        ELSE NVL(Symbol, '<UNKNOWN SYMBOL>')
    END AS Symbol,
    AVG(Open) AS AvgOpen
FROM Stocks
GROUP BY CUBE(Sector, Symbol)
ORDER BY Sector, Symbol

GROUPING SETS

The GROUPING SETS(expr list) function calculates aggregates for each group of columns in expr list.

For example, the following query will aggregate the average opening stock price for these groups:

• Each market sector
• Each stock symbol
• All sectors and symbols

SELECT
    CASE
        WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
        ELSE NVL(Sector, '<UNKNOWN SECTOR>')
    END AS Sector,
    CASE
        WHEN (GROUPING(Symbol) = 1) THEN '<ALL SYMBOLS>'
        ELSE NVL(Symbol, '<UNKNOWN SYMBOL>')
    END AS Symbol,
    AVG(Open) AS AvgOpen
FROM Stocks
GROUP BY GROUPING SETS((Sector), (Symbol), ())
ORDER BY Sector, Symbol

Window

Window functions are available through the use of the OVER clause, which can partition rows into frames. Different types of functions can be used to aggregate data over a sliding window.

The basic form for a window is:

SELECT
    <window function> OVER (
        PARTITION BY <column expression list>
        [ORDER BY <ordering expression list>]
        [
            ROWS
            <
                <UNBOUNDED PRECEDING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING> |
                BETWEEN <UNBOUNDED PRECEDING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING>
                    AND <UNBOUNDED FOLLOWING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING>
            >
        ]
    ) [AS <alias>]

If no ROWS clause is specified, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With this frame type, CURRENT ROW includes all peer rows (rows with the same ordering values). Thus, when the first of a set of peer rows is encountered, all associated peer rows are included in the frame (not just the first one). In contrast, a frame type of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW will only include the first of a set of peer rows when encountered--the other peers will not be included in the frame.

If a ROWS clause is specified without a BETWEEN, the clause is applied to the frame start; the frame end will still be the default of CURRENT ROW.

For example, to calculate the rolling sum of total amounts collected by each taxi vendor over the course of a given day:

SELECT
    vendor_id,
    pickup_datetime,
    total_amount,
    SUM(total_amount) OVER
        (
            PARTITION BY vendor_id
            ORDER BY pickup_datetime
        ) AS growing_sum
FROM nyctaxi
WHERE pickup_datetime >= '2009-12-01' AND pickup_datetime < '2009-12-02'
ORDER BY
    vendor_id,
    pickup_datetime

To rank, by vendor, the total amounts collected from two-passenger trips on a given day:

SELECT
    vendor_id,
    pickup_datetime,
    dropoff_datetime,
    total_amount,
    RANK() OVER (PARTITION BY vendor_id ORDER BY total_amount) AS ranked_total,
    PERCENT_RANK() OVER (PARTITION BY vendor_id ORDER BY total_amount) AS percent_ranked_total
FROM nyctaxi
WHERE
	passenger_count = 2 AND
	pickup_datetime BETWEEN '2015-05-11' AND '2015-05-12'
ORDER BY
    vendor_id,
    total_amount

To compare each trip's total amount to the lowest, highest, & average total amount for 4-passenger trips for each vendor over the course of a given day:

SELECT
    vendor_id,
    pickup_datetime,
    total_amount as current_trip_amount,
    FIRST_VALUE(total_amount) OVER
        (PARTITION BY vendor_id ORDER BY total_amount) AS lowest_amount,
    AVG(total_amount) OVER
        (PARTITION BY vendor_id ORDER BY total_amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS average_amount,
    FIRST_VALUE(total_amount) OVER
        (PARTITION BY vendor_id ORDER BY total_amount DESC) AS highest_amount
FROM nyctaxi
WHERE
    passenger_count = 4 AND
    pickup_datetime >= '2009-12-01' AND pickup_datetime < '2009-12-02'
ORDER BY
    vendor_id,
    pickup_datetime,
    current_trip_amount

To compare each vendor's average total amount to their average total amount within the interquartile range:

SELECT
	vendor_id,
	AVG(total_amount) average_total_amount,
	AVG(IF(quartile IN (2,3), total_amount, null)) average_interq_range_total_amount
FROM
(
	SELECT
		vendor_id,
		total_amount,
		NTILE(4) OVER (PARTITION BY vendor_id ORDER BY total_amount) quartile
	FROM
		nyctaxi
)
GROUP BY
	vendor_id

PIVOT

The PIVOT clause can be used to pivot columns, "rotating" column values into row values, creating wider and shorter denormalized tables from longer, more normalized tables.

The basic form for a pivot is:

<SELECT statement>
PIVOT
(
    <aggregate expression [AS <alias>][, <aggregate expression list>]>
        FOR <column> IN (<column list>)
)

For example, given a source table customer, which lists each phone number for a customer as a separate record in the table, a pivot operation can be performed like so, creating a single record per customer with the home, work, & cell phone numbers as separate columns:

SELECT *
FROM (
  SELECT
    name,
    phone_type,
    phone_number
  FROM
    customer
) AS pvt
PIVOT (
  MAX(phone_number) AS Phone
  FOR phone_type IN (Home, Work, Cell)
);

UNPIVOT

The UNPIVOT clause can be used to unpivot columns, "rotating" row values into column values, creating longer, more normalized tables from shorter, more denormalized tables.

The basic form for an unpivot is:

<SELECT statement>
UNPIVOT
(
  <value_column> FOR <var_column> IN (<column list>)
)

For example, given a source table customer, which lists the home, work, & cell phone numbers for each customer in the table, an unpivot operation can be performed like so, creating separate home, work, & cell phone records for each customer:

SELECT *
FROM (
  SELECT
    name,
    Home_Phone,
    Work_Phone,
    Cell_Phone
  FROM
    customer
) as pvted
UNPIVOT (
  phone_number FOR phone_type in (Home_Phone, Work_Phone, Cell_Phone)
);

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 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. Use the ALL keyword to keep duplicate records that exist in both sets; omit it to remove duplicate records and form a single list of records that exist in both sets. See Limitations for limitations.

<SELECT statement>
INTERSECT [ALL]
<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. Use the ALL keyword to keep duplicate records that exist in the first set but not in the second; omit it to remove duplicate records and form a single list of records that exist in the first set but not the second. See Limitations for limitations:

<SELECT statement>
EXCEPT [ALL]
<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

Iteration

Kinetica supports iteration over each record within a data set for the purpose of creating a result set with 0 to N result records per record in the original set.

This iteration can be variable, based on some value within each record, or fixed, based on a given constant value.

The iteration is performed by joining against the virtual ITER table, as follows:

SELECT *
FROM table, ITER
WHERE ITER.i < <column expression>

The <column expression> can be replaced by a constant for fixed iteration.

For example, to extract all of the individual letters from a column of words, with one record per letter extracted (using variable iteration):

SELECT id, word, i, SUBSTR(word, i + 1, 1) AS letter
FROM dictionary
JOIN ITER ON i < LENGTH(word)
ORDER BY id, i;

To duplicate the set of words five times (using fixed iteration):

SELECT *
FROM dictionary, ITER
WHERE i < 5
ORDER BY id, i;

For more detail, examples, and limitations, see Iteration.

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-sensitive manner.

Conditional Functions

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 expr - any true/false condition. Note that when an integer column is used directly, this function will interpret non-zero values as true and zero values as false) value_if_true - any numeric value value_if_false - any numeric value

Conversion Functions

Function	Description
CAST(expr AS [SQL_]<conv_type>) or CONVERT(expr, SQL_<conv_type>)	Converts expr into conv_type data type 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 CHAR DATE DECIMAL LONGVARCHAR TIME DOUBLE VARCHAR TIMESTAMP FLOAT WVARCHAR TYPE_DATE INTEGER   TYPE_TIME NUMERIC   TYPE_TIMESTAMP REAL     SMALLINT     TINYINT
CHAR256(charN)	Converts the given charN to char256 type
CHAR128(charN)	Converts the given charN to char128 type
CHAR64(charN)	Converts the given charN to char64 type
CHAR32(charN)	Converts the given charN to char32 type
CHAR16(charN)	Converts the given charN to char16 type
CHAR8(charN)	Converts the given charN to char8 type
CHAR4(charN)	Converts the given charN to char4 type
CHAR2(charN)	Converts the given charN to char2 type
CHAR1(charN)	Converts the given charN to char1 type
CHAR(int)	Returns the character associated with the ASCII code in int

Date/Time Functions

Function	Description
CURRENT_DATE()	Returns the date as YYYY-MM-DD
CURRENT_DATETIME()	Returns the date & time as YYYY-MM-DD HH24:MI:SS.mmm
CURRENT_TIME()	Returns the time as HH24:MI:SS.mmm
CURRENT_TIMESTAMP()	Returns the date & time as the number of milliseconds since the epoch
DATE(expr)	Returns date in the format YYYY-MM-DD from expr
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
DATETIME(expr)	Returns expr (as a string) as a datetime (YYYY-MM-DD HH:MM:SS.mmm)
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)
DAY_OF_WEEK(expr)	Synonymous with DAYOFWEEK(expr)
DAYOFYEAR(expr)	Extracts the day of the year from expr [1 - 366]
DAY_OF_YEAR(expr)	Synonymous with DAYOFYEAR(expr)
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
LAST_DAY(date)	Returns the last day of the month provided in date. The given date can be of date or datetime type.
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 ]
MSEC(expr)	Extracts the millsecond of the second from expr [0 - 999]
NEXT_DAY(date, expr)	Returns the next day (as provided by expr) after the given date, e.g., NEXT_DAY('2000-10-10', 'Friday') would return 2000-10-13 because 2000-10-10 is a Wednesday. The given date can be of date or datetime type.
NOW()	Alias for CURRENT_DATETIME()
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]
SEC(expr)	Alias for SECOND(expr)
TIME(expr)	Returns the time (HH:MM:SS) from the expr
TIMESTAMP(expr)	Returns the timestamp from the expr (as string)
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 as the SQL_TSI_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 SQL_TSI_MONTH to Mar 31st results in Apr 30th, and the TIMESTAMPDIFF in SQL_TSI_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.

Timestamp/Date Conversion Functions

Function	Description
DATE_TO_EPOCH_SECS(year, month, day, hours, minutes, seconds)	Converts the full date to seconds since the epoch. Negative values are accepted (e.g., DATE_TO_EPOCH_SECS(2017,06,-15,09,22,15) would return 1494926535, which resolves to Tuesday, May 16, 2017 9:22:15 AM)
DATE_TO_EPOCH_MSECS(year, month, day, hours, minutes, seconds, milliseconds)	Converts the full date to milliseconds since the epoch. Negative values are accepted
WEEK_TO_EPOCH_SECS(year, week_number)	Converts the year and week number to seconds since the epoch. Negative values are accepted (e.g., WEEK_TO_EPOCH_SECS(2017,-32) would return 1463270400, which resolves to Sunday, May 15, 2016 12:00:00 AM). Each new week begins Sunday at midnight
WEEK_TO_EPOCH_MSECS(year, week_number)	Converts the year and week number to seconds since the epoch. Negative values are accepted
MSECS_SINCE_EPOCH(timestamp)	Converts the timestamp to millseconds since the epoch
TIMESTAMP_FROM_DATE_TIME(date, time)	converts the date and time (as strings) to timestamp format, e.g., TIMESTAMP_FROM_DATE_TIME('2017-06-15', '10:37:30') would return 1497523050000, which resolves to Thursday, June 15, 2017 10:37:30 AM.

Encoding Functions

Function	Description
HASH(expr[, seed])	Returns an 8-byte hash (long type) of the given value expr. An optional seed can be provided.
SHA256(expr)	Returns the hex digits of the SHA-256 hash of the given value expr as a char64 string.

Geospatial Functions

Tip

• Use ST_ISVALID to determine if a geometry object is valid. The functions below work best with valid geometry objects.
• Use the REMOVE_NULLABLE function to remove any nullable column types that could result from calculating a derived column (e.g., as in Projections) using one of the functions below.

Enhanced Performance Functions

The functions below all compare x and y coordinates to geometry objects (or vice versa), thus increasing their performance in queries. Each of these functions have a geometry-to-geometry version listed in the next section.

Function	Description
STXY_CONTAINS(geom, x, y)	Returns 1 (true) if geom contains the x and y coordinate, e.g. lies in the interior of geom. The coordinate cannot be on the boundary and also be contained because geom does not contain its boundary
STXY_CONTAINSPROPERLY(geom, x, y)	Returns 1 (true) if the x and y coordinate intersects the interior of geom but not the boundary (or exterior) because geom does not contain its boundary but does contain itself
STXY_COVEREDBY(x, y, geom)	Returns 1 (true) if the x and y coordinate is covered by geom
STXY_COVERS(geom, x, y)	Returns 1 (true) if geom covers the x and y coordinate
STXY_DISJOINT(x, y, geom)	Returns 1 (true) if the given x and y coordinate and the geometry geom do not spatially intersect.
STXY_DISTANCE(x, y, geom[, solution])	Calculates the minimum distance between the given x and y coordinate and geom using the specified solution type. Solution types available: 0 (default) - Euclidean; returns 2-D Euclidean distance 1 - Haversine; returns minimum sphere distance in meters 2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance Note: If the x and y coordinate and geom intersect (verify using ST_INTERSECTS), the distance will always be 0.
STXY_DWITHIN(x, y, geom, distance[, solution])	Returns 1 (true) if the x and y coordinate is within the specified distance from geom using the specified solution type. Solution types available: 0 (default) - Euclidean; uses degrees to calculate distance 1 - Sphere; uses meters to calculate distance
STXY_ENVDWITHIN(x, y, geom, distance[, solution])	Returns 1 (true) if the x and y coordinate is within the specified distance from the bounding box of geom using the specified solution type. Solution types available: 0 (default) - Euclidean; uses degrees to calculate distance 1 - Sphere; uses meters to calculate distance
STXY_ENVINTERSECTS(x, y, geom)	Returns 1 (true) if the bounding box of the given geometry geom intersects the x and y coordinate.
STXY_INTERSECTION(x, y, geom)	Returns the shared portion between the x and y coordinate and the given geometry geom, i.e. the point itself.
STXY_INTERSECTS(x, y, geom)	Returns 1 (true) if the x and y coordinate and geom intersect in 2-D.
STXY_TOUCHES(x, y, geom)	Returns 1 (true) if the x and y coordinate and geometry geom have at least one point in common but their interiors do not intersect. If geom is a GEOMETRYCOLLECTION, a 0 is returned regardless if the point and geometry touch
STXY_WITHIN(x, y, geom)	Returns 1 (true) if the x and y coordinate is completely inside the geom geometry i.e., not on the boundary

Geometry Functions

Function	Description
DIST(x1, y1, x2, y2)	Computes the Euclidean distance (in degrees), i.e. SQRT( (x1-x2)*(x1-x2) + (y1-y2)*(y1-y2) ).
GEODIST(lon1, lat1, lon2, lat2)	Computes the geographic great-circle distance (in meters) between two lat/lon points.
GEOHASH_DECODE_LATITUDE(geohash)	Decodes a given geohash and returns the latitude value for the given hash string. Supports a maximum geohash character length of 16.
GEOHASH_DECODE_LONGITUDE(geohash)	Decodes a given geohash and returns the longitude value for the given hash string. Supports a maximum geohash character length of 16.
GEOHASH_ENCODE(lat, lon, precision)	Encodes a given coordinate pair and returns a hash string with a given precision.
ST_ADDPOINT(linestring, point, position)	Adds a the given point geometry to the given linestring geometry at the specified position, which is a 0-based index.
ST_ALMOSTEQUALS(geom1, geom2, decimal)	Returns 1 (true) if given geometries, geom1 and geom2, are almost spatially equal within the given amount of decimal scale. Note that geometries will still be considered equal if the decimal scale for the geometries is within a half order of magnitude of each other, e.g, if decimal is set to 2, then POINT(63.4 123.45) and POINT(63.4 123.454) are equal, but POINT(63.4 123.45) and POINT(63.4 123.459) are not equal. The geometry types must match to be considered equal.
ST_AREA(geom[, solution])	Returns the area of the given geometry geom if it is a POLYGON or MULTIPOLYGON using the specified solution type. Returns 0 if the input geometry type is (MULTI)POINT or (MULTI)LINESTRING. Solution types available: 0 (default) - 2D Euclidean area 1 - curved surface area on a sphere in meters
ST_AZIMUTH(geom1, geom2)	Returns the azimuth in radians defined by the segment between two POINTs, geom1 and geom2. Returns a null if the input geometry type is MULTIPOINT, (MULTI)LINESTRING, or (MULTI)POLYGON.
ST_BOUNDARY(geom)	Returns the closure of the combinatorial boundary of a given geometry geom. Returns an empty geometry if geom is an empty geometry. Returns a null if geom is a GEOMETRYCOLLECTION
ST_BOUNDINGDIAGONAL(geom)	Returns the diagonal of the given geometry's (geom) bounding box.
ST_BUFFER(geom, radius[, style[, solution]])	Returns a geometry that represents all points whose distance from the given geometry geom is less than or equal to the given distance radius. The radius units can be specified by the solution type (default is in degrees) and the radius is created in the provided style. The style options are specified as a list of blank-separated key-value pairs, e.g., 'quad_segs=8 endcap=round'. If an empty style list ('') is provided, the default settings will be used. The style parameter must be specified to provide a solution type. Available style options: quad_segs -- the number of segments used to approximate a quarter circle (default is 8) endcap -- the endcap style of the buffer (default is round); options are round, flat (or butt), and square join -- the join style of the buffer (default is round); options are round, mitre (or miter), and bevel mitre_limit -- the mitre ratio limit expressed as a floating point number (miter_limit is also acceptable) Available solution types: 0 (default) - 2D Euclidean radius distance in degrees 1 - curved surface radius distance on a sphere in meters Tip To create a 5-meter buffer around geom using the default styles: ST_BUFFER(geom, 5, '', 1). To create a 5-foot (converting feet to meters) buffer around geom using the following styles: ST_BUFFER(geom, 5*0.3048,'quad_segs=4 endcap=flat', 1)
ST_CENTROID(geom)	Calculates the center of the given geometry geom as a POINT. For (MULTI)POINTs, the center is calculated as the average of the input coordinates. For (MULTI)LINESTRINGs, the center is calculated as the weighted length of each given LINESTRING. For (MULTI)POLYGONs, the center is calculated as the weighted area of each given POLYGON. If geom is an empty geometry, an empty GEOMETRYCOLLECTION is returned
ST_CLIP(geom1, geom2)	Returns the geometry shared between given geometries geom1 and geom2
ST_CLOSESTPOINT(geom1, geom2[, solution])	Calculates the 2-D POINT in geom1 that is closest to geom2 using the specified solution type. If geom1 or geom2 is empty, a null is returned. Solution types available: 0 (default) - Euclidean; calculates the closest point using 2-D Euclidean distance 1 - Haversine; calculates the closest point using sphere distance in meters
ST_COLLECT(geom1, geom2)	Returns a MULTI* or GEOMETRYCOLLECTION comprising geom1 and geom2. If geom1 and geom2 are the same, singular geometry type, a MULTI* is returned, e.g., if geom1 and geom2 are both POINTs (empty or no), a MULTIPOINT is returned. If geom1 and geom2 are neither the same type nor singular geometries, a GEOMETRYCOLLECTION is returned.
ST_COLLECTIONEXTRACT(collection, type)	Returns only the specified type from the given geometry collection. Type is a number that maps to the following: 1 = POINT 2 = LINESTRING 3 = POLYGON
ST_COLLECTIONHOMOGENIZE(collection)	Returns the simplest form of the given collection, e.g., a collection with a single POINT will be returned as POINT(x y), and a collection with multiple individual points will be returned as a MULTIPOINT.
ST_CONCAVEHULL(geom, target_percent[, allow_holes])	Returns a potentially concave geometry that encloses all geometries found in the given geom set. Use target_percent (values between 0 and 1) to determine the percent of area of a convex hull the concave hull will attempt to fill; 1 will return the same geometry as an ST_CONVEXHULL operation. Set allow_holes to 1 (true) to allow holes in the resulting geometry; default value is 0 (false). Note that allow_holes is independent of the area of target_percent.
ST_CONTAINS(geom1, geom2)	Returns 1 (true) if no points of geom2 lie in the exterior of geom1 and at least one point of geom2 lies in the interior of geom1. Note that geom1 does not contain its boundary but does contain itself.
ST_CONTAINSPROPERLY(geom1, geom2)	Returns 1 (true) if geom2 intersects the interior of geom1 but not the boundary (or exterior). Note that geom1 does not contain its boundary but does contain itself.
ST_CONVEXHULL(geom)	Returns the minimum convex geometry that encloses all geometries in the given geom set.
ST_COORDDIM(geom)	Returns the coordinate dimension of the given geom, e.g., a geometry with x, y, and z coordinates would return 3.
ST_COVEREDBY(geom1, geom2)	Returns 1 (true) if no point in geom1 is outside geom2.
ST_COVERS(geom1, geom2)	Returns 1 (true) if no point in geom2 is outside geom1.
ST_CROSSES(geom1, geom2)	Returns 1 (true) if the given geometries, geom1 and geom2, spatially cross, meaning some but not all interior points in common. If geom1 and/or geom2 are a GEOMETRYCOLLECTION, a 0 is returned regardless if the two geometries cross
ST_DIFFERENCE(geom1, geom2)	Returns a geometry that represents the part of geom1 that does not intersect with geom2.
ST_DIMENSION(geom)	Returns the dimension of the given geometry geom, which is less than or equal to the coordinate dimension. If geom is a single geometry, a 0 is for POINT, a 1 is for LINESTRING, and a 2 is for POLYGON. If geom is a collection, it will return the largest dimension from the collection. If geom is empty, 0 is returned.
ST_DISJOINT(geom1, geom2)	Returns 1 (true) if the given geometries, geom1 and geom2, do not spatially intersect.
ST_DISTANCE(geom1, geom2[, solution])	Calculates the minimum distance between the given geometries, geom1 and geom2, using the specified solution type. Solution types available: 0 (default) - Euclidean; returns 2-D Euclidean distance 1 - Haversine; returns minimum sphere distance in meters 2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance Note: If geom1 and geom2 intersect (verify using ST_INTERSECTS), the distance will always be 0.
ST_DISTANCEPOINTS(x1, y1, x2, y2[, solution])	Calculates the minimum distance between the given points, x1, y1 and x2, y2, using the specified solution type. Solution types available: 0 (default) - Euclidean; returns 2-D Euclidean distance 1 - Haversine; returns minimum sphere distance in meters 2 - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance
ST_DFULLYWITHIN(geom1, geom2, distance[, solution])	Returns 1 (true) if the maximum distance between geometries geom1 and geom2 is less than or equal to the specified distance of each other using the specified solution type. If geom1 or geom2 is null, 0 (false) is returned. Solution types available: 0 (default) - Euclidean; uses degrees to calculate distance 1 - Sphere; uses meters to calculate distance 2 - Spheroid; uses meters to calculate distance, more accurate than sphere but slower performance
ST_DWITHIN(geom1, geom2, distance[, solution])	Returns 1 (true) if the minimum distance between geometries geom1 and geom2 is within the specified distance of each other using the specified solution type. Solution types available: 0 (default) - Euclidean; uses degrees to calculate distance 1 - Sphere; uses meters to calculate distance
ST_ELLIPSE(centerx, centery, height, width)	Returns an ellipse using the following values: centerx -- the x coordinate or longitude used to center the ellipse centery -- the y coordinate or latitude used to center the ellipse height -- the height of the ellipse (in degrees) width -- the width of the ellipse (in degrees)
ST_ENDPOINT(geom)	Returns the last point of the given geom as a POINT if it's a LINESTRING. If geom is not a a LINESTRING, null is returned.
ST_ENVDWITHIN(geom1, geom2, distance[, solution])	Returns 1 (true) if geom1 is within the specified distance of the bounding box of geom2 using the specified solution type. Solution types available: 0 (default) - Euclidean; uses degrees to calculate distance 1 - Sphere; uses meters to calculate distance
ST_ENVELOPE(geom)	Returns the bounding box of a given geometry geom.
ST_ENVINTERSECTS(geom1, geom2)	Returns 1 (true) if the bounding box of the given geometries, geom1 and geom2, intersect.
ST_EQUALS(geom1, geom2)	Returns 1 (true) if the given geometries, geom1 and geom2, are spatially equal. Note that order does not matter.
ST_EQUALSEXACT(geom1, geom2, tolerance)	Returns 1 (true) if the given geometries, geom1 and geom2, are almost spatially equal within some given tolerance. If the values within the given geometries are within the tolerance value of each other, they're considered equal, e.g., if tolerance is 2, POINT(1 1) and POINT(1 3) are considered equal, but POINT(1 1) and POINT(1 3.1) are not. Note that the geometry types have to match for them to be considered equal.
ST_ERASE(geom1, geom2)	Returns the result of erasing a portion of geom1 equal to the size of geom2.
ST_EXPAND(geom, units)	Returns the bounding box expanded in all directions by the given units of the given geom. The expansion can also be defined for separate directions by providing separate parameters for each direction, e.g., ST_EXPAND(geom, unitsx, unitsy, unitsz, unitsm).
ST_EXPANDBYRATE(geom, rate)	Returns the bounding box expanded by a given rate (a ratio of width and height) for the given geometry geom. The rate must be between 0 and 1.
ST_EXTERIORRING(geom)	Returns a LINESTRING representing the exterior ring of the given POLYGON geom
ST_GENERATEPOINTS(geom, num)	Creates a MULTIPOINT containing a number num of randomly generated points within the boundary of geom.
ST_GEOHASH(geom, precision)	Returns a hash string representation of the given geometry geom with specified precision (the length of the geohash string). The longer the precision, the more precise the hash is. By default, precision is set to 20. Returns null if geom is an empty geometry. Note The value returned will not be a geohash of the exact geometry but a geohash of the centroid of the given geometry
ST_GEOMETRYN(geom, index)	Returns the index geometry back from the given geom geometry. The index starts from 1 to the number of geometry in geom.
ST_GEOMETRYTYPE(geom)	Returns the type of geometry from the given geom.
ST_GEOMETRYTYPEID(geom)	Returns the type ID of from geom. Type and ID mappings: POINT = 0 LINESTRING = 1 LINEARRING = 2 POLYGON = 3 MULTIPOINT = 4 MULTILINESTRING = 5 MULTIPOLYGON = 6 GEOMETRYCOLLECTION = 7
ST_GEOMFROMGEOHASH(geohash, precision)	Returns a POLYGON boundary box using the given geohash with a precision set by the integer precision. If precision is specified, the function will use as many characters in the hash equal to precision to create the geometry. If no precision is specified, the full length of the geohash is used.
ST_GEOMFROMTEXT(wkt)	Returns a geometry from the given Well-Known text representation wkt. Note that this function is only compatible with constants
ST_INTERIORRINGN(geom, n)	Returns the n-th interior LINESTRING ring of the POLYGON geom. If geom is not a POLYGON or the given n is out of range, a null is returned. The index begins at 1
ST_INTERSECTION(geom1, geom2)	Returns the shared portion between given geometries geom1 and geom2
ST_INTERSECTS(geom1, geom2)	Returns 1 (true) if the given geometries, geom1 and geom2, intersect in 2-D
ST_ISCLOSED(geom)	Returns 1 (true) if the given geometry's (geom) start and end points coincide
ST_ISCOLLECTION(geom)	Returns 1 (true) if geom is a collection, e.g., GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, etc.
ST_ISEMPTY(geom)	Returns 1 (true) if geom is empty
ST_ISRING(geom)	Returns 1 (true) if LINESTRING geom is both closed (per ST_ISCLOSED) and "simple" (per ST_ISSIMPLE). Returns 0 if geom is not a LINESTRING
ST_ISSIMPLE(geom)	Returns 1 (true) if geom has no anomalous geometric points, e.g., self-intersection or self-tangency
ST_ISVALID(geom)	Returns 1 (true) if geom (typically a [MULTI]POLYGON) is well formed. A POLYGON is valid if its rings do not cross and its boundary intersects only at POINTs (not along a line). The POLYGON must also not have dangling LINESTRINGs. A MULTIPOLYGON is valid if all of its elements are also valid and the interior rings of those elements do not intersect. Each element's boundaries may touch but only at POINTs (not along a line)
ST_LENGTH(geom[, solution])	Returns the length of the geometry if it is a LINESTRING or MULTILINESTRING. Returns 0 if another type of geometry, e.g., POINT, MULTIPOINT, etc. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies. Solution types available: 0 (default) - 2D Euclidean length 1 - length on a sphere in meters 2 - length on a spheroid in meters
ST_LINEFROMMULTIPOINT(geom)	Creates a LINESTRING from geom if it is a MULTIPOINT. Returns null if geom is not a MULTIPOINT
ST_LINEINTERPOLATEPOINT(geom, fraction)	Returns a POINT that represents the specified fraction of the LINESTRING geom. If geom is either empty or not a LINESTRING, null is returned
ST_LINEMERGE(geom)	Returns a LINESTRING or MULTILINESTRING from a given geom. If geom is a MULTILINESTRING comprising LINESTRINGs with shared endpoints, a contiguous LINESTRING is returned. If geom is a LINESTRING or a MULTILINESTRING comprising LINESTRINGS without shared endpoints, geom is returned If geom is an empty (MULTI)LINESTRING or a (MULTI)POINT or (MULTI)POLYGON, an empty GEOMETRYCOLLECTION is returned.
ST_LINESUBSTRING(geom, start_fraction, end_fraction)	Returns the fraction of a given geom LINESTRING where start_fraction and end_fraction are between 0 and 1. For example, given LINESTRING(1 1, 2 2, 3 3) a start_fraction of 0 and an end_fraction of 0.25 would yield the first quarter of the given LINESTRING, or LINESTRING(1 1, 1.5 1.5). Returns null if start_fraction is greater than end_fraction. Returns null if input geometry is (MULTI)POINT, MULTILINESTRING, or (MULTI)POLYGON. Returns null if start_fraction and/or end_fraction are less than 0 or more than 1.
ST_LONGESTLINE(geom1, geom2[, solution])	Returns the LINESTRING that represents the longest line of points between the two geometries. If multiple longest lines are found, only the first line found is returned. If geom1 or geom2 is empty, null is returned. Solution types available: 0 (default) - Euclidean; uses degrees to calculate the longest line 1 - Sphere; uses meters to calculate the longest line 2 - Spheroid; uses meters to calculate the longest line, more accurate than sphere but slower performance
ST_MAKEENVELOPE(xmin, ymin, xmax, ymax)	Creates a rectangular POLYGON from the given min and max parameters
ST_MAKELINE(geom[, geom2])	Creates a LINESTRING from geom if it is a MULTIPOINT. If geom is a POINT, there must be at least one other POINT to construct a LINESTRING. If geom is a LINESTRING, it must have at least two points. Returns null if geom is not a POINT, MULTIPOINT, or LINESTRING Note This function can be rather costly in terms of performance
ST_MAKEPOINT(x, y)	Creates a POINT at the given coordinate Note This function can be rather costly in terms of performance
ST_MAKEPOLYGON(geom)	Creates a POLYGON from geom. Inputs must be closed LINESTRINGs Note This function can be rather costly in terms of performance
ST_MAKETRIANGLE2D(x1, y1, x2, y2, x3, y3)	Creates a closed 2-D POLYGON with three vertices
ST_MAKETRIANGLE3D(x1, y1, z1, x2, y2, z2, x3, y3, z3)	Creates a closed 3-D POLYGON with three vertices
ST_MAXDISTANCE(geom1, geom2[, solution])	Returns the maximum distance between the given geom1 and geom2 geometries using the specifed solution type. If geom1 or geom2 is empty, null is returned. Solution types available: 0 (default) - returns maximum 2-D Euclidean distance 1 - Sphere; returns maximum distance in meters 2 - Spheroid; returns maximum distance in meters, more accurate than sphere but slower performance
ST_MAXX(geom)	Returns the maximum x coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.
ST_MAXY(geom)	Returns the maximum y coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.
ST_MAXZ(geom)	Returns the maximum z coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.
ST_MINX(geom)	Returns the minimum x coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.
ST_MINY(geom)	Returns the minimum y coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.
ST_MINZ(geom)	Returns the minimum z coordinate of a bounding box for the given geom geometry. This function works for 2-D and 3-D geometries.
ST_MULTI(geom)	Returns geom as a MULTI- geometry, e.g., a POINT would return a MULTIPOINT.
ST_MULTIPLERINGBUFFERS(geom, distance, outside)	Creates multiple buffers at specified distance around the given geom geometry. Multiple distances are specified as comma-separated values in an array, e.g., [10,20,30]. Valid values for outside are: FULL -- indicates that buffers will overlap or cover the given geom geometry. This is the default. OUTSIDE_ONLY -- indicates that buffers will be rings around the given geom geometry.
ST_NEAR(geom1, geom2)	Returns the portion of geom2 that is closest to geom1. If geom2 is a singular geometry object (e.g., POINT, LINESTRING, POLYGON), geom2 will be returned. If geom2 a multi-geometry, e.g., MULTIPOINT, MULTILINESTRING, etc., the nearest singular geometry in geom2 will be returned.
ST_NORMALIZE(geom)	Returns geom in its normalized (canonical) form, which may rearrange the points in lexicographical order.
ST_NPOINTS(geom)	Returns the number of points (vertices) in geom.
ST_NUMGEOMETRIES(geom)	If geom is a collection or MULTI- geometry, returns the number of geometries. If geom is a single geometry, returns 1.
ST_NUMINTERIORRINGS(geom)	Returns the number of interior rings if geom is a POLYGON. Returns null if geom is anything else.
ST_NUMPOINTS(geom)	Returns the number of points in the geom LINESTRING. Returns null if geom is not a LINESTRING.
ST_OVERLAPS(geom1, geom2)	Returns 1 (true) if given geometries geom1 and geom2 share space. If geom1 and/or geom2 are a GEOMETRYCOLLECTION, a 0 is returned regardless if the two geometries overlap
ST_PARTITION(geom, threshold)	Returns a MULTIPOLYGON representing the given geom partitioned into a number of POLYGONs with a maximum number of vertices equal to the given threshold. Minimum value for threshold is 10; default value is 10000. If geom is not a POLYGON or MULTIPOLYGON, geom is returned. If the number of vertices in geom is less than the threshold, geom is returned.
ST_POINT(x, y)	Returns a POINT with the given x and y coordinates.
ST_POINTFROMGEOHASH(geohash, precision)	Returns a POINT using the given geohash with a precision set by the integer precision. If precision is specified, the function will use as many characters in the hash equal to precision to create the geometry. If no precision is specified, the full length of the geohash is used. Note The POINT returned represents the center of the bounding box of the geohash
ST_POINTN(geom, n)	Returns the n-th point in LINESTRING geom. Negative values are valid, but note that they are counted backwards from the end of geom. A null is returned if geom is not a LINESTRING.
ST_POINTS(geom)	Returns a MULTIPOINT containing all of the coordinates of geom.
ST_REMOVEPOINT(geom, offset)	Remove a point from LINESTRING geom using offset to skip over POINTs in the LINESTRING. The offset is 0-based.
ST_REMOVEREPEATEDPOINTS(geom, tolerance)	Removes points from geom if the point's vertices are greater than or equal to the tolerance of the previous point in the geometry's list. If geom is not a MULTIPOINT, MULTILINESTRING, or a MULTIPOLYGON, no points will be removed.
ST_REVERSE(geom)	Return the geometry with its coordinate order reversed.
ST_SCALE(geom, x, y)	Scales geom by multiplying its respective vertices by the given x and y values. This function also supports scaling geom using another geometry object, e.g., ST_SCALE('POINT(3 4)', 'POINT(5 6)') would return POINT(15 24). If specifying x and y for scale, note that the default value is 0, e.g., ST_SCALE('POINT(1 3)', 4) would return POINT(4 0).
ST_SEGMENTIZE(geom, max_segment_length[, solution])	Returns the given geom but segmentized n number of times depending on how the max_segment_length distance (in units based on the solution type) divides up the original geometry. The new geom is guaranteed to have segments that are smaller than the given max_segment_length. Note that POINTs are not able to be segmentized. Collection geometries (GEOMETRYCOLLECTION, MULTILINESTRING, MULTIPOINT, etc.) can be segmentized, but only the individual parts will be segmentized, not the collection as a whole. Solution types available: 0 - Euclidean; uses degrees to calculate distance 1 (default) - Sphere; uses meters to calculate distance
ST_SETPOINT(geom1, position, geom2)	Replace a point of LINESTRING geom1 with POINT geom2 at position (base 0). Negative values are valid, but note that they are counted backwards from the end of geom.
ST_SHAREDPATH(geom1, geom2)	Returns a collection containing paths shared by geom1 and geom2.
ST_SHORTESTLINE(geom1, geom2)	Returns the 2-D LINESTRING that represents the shortest line of points between the two geometries. If multiple shortest lines are found, only the first line found is returned. If geom1 or geom2 is empty, null is returned
ST_SNAP(geom1, geom2, tolerance)	Snaps geom1 to geom2 within the given tolerance. If the tolerance causes geom1 to not snap, the geometries will be returned unchanged.
ST_SPLIT(geom1, geom2)	Returns a collection of geometries resulting from the split between geom1 and geom2 geometries.
ST_STARTPOINT(geom)	Returns the first point of LINESTRING geom as a POINT. Returns null if geom is not a LINESTRING.
ST_SYMDIFFERENCE(geom1, geom2)	Returns a geometry that represents the portions of geom1 and geom2 geometries that do not intersect.
ST_TOUCHES(geom1, geom2)	Returns 1 (true) if the given geometries, geom1 and geom2, have at least one point in common but their interiors do not intersect. If geom1 and/or geom2 are a GEOMETRYCOLLECTION, a 0 is returned regardless if the two geometries touch
ST_TRANSLATE(geom, deltax, deltay[, deltaz])	Translate geom by given offsets deltax and deltay. A z-coordinate offset can be applied using deltaz.
ST_UNION(geom1, geom2)	Returns a geometry that represents the point set union of the two given geometries, geom1 and geom2.
ST_UNIONCOLLECTION(geom)	Returns a geometry that represents the point set union of a single given geometry geom.
ST_UPDATE(geom1, geom2)	Returns a geometry that is geom1 geometry updated by geom2 geometry
ST_VORONOIPOLYGONS(geom, tolerance)	Returns a GEOMETRYCOLLECTION containing Voronoi polygons (regions consisting of points closer to a vertex in geom than any other vertices in geom) calculated from the vertices in geom and the given tolerance. The tolerance determines the distance at which points will be considered the same. An empty GEOMETRYCOLLECTION is returned if geom is an empty geometry, a single POINT, or a LINESTRING or POLYGON composed of equivalent vertices (e.g., POLYGON((0 0, 0 0, 0 0, 0 0)), LINESTRING(0 0, 0 0)).
ST_WITHIN(geom1, geom2)	Returns 1 (true) if the geom1 geometry is inside the geom2 geometry. Note that as long as at least one point is inside of geom2, geom1 is considered within geom2 even if the rest of the geom1 lies along the boundary of geom2
ST_WKTTOWKB(geom)	Returns the binary form (WKB) of a geom (WKT) Note This function can only be used in queries against a single table.
ST_X(geom)	Returns the X coordinate of the POINT geom; if the coordinate is not available, null is returned. geom must be a POINT.
ST_Y(geom)	Returns the Y coordinate of the POINT geom; if the coordinate is not available, null is returned. geom must be a POINT.

Geospatial Aggregation Functions

Function	Description
ST_AGGREGATE_COLLECT(geom)	Alias for ST_COLLECT_AGGREGATE()
ST_COLLECT_AGGREGATE(geom)	Returns a GEOMETRYCOLLECTION comprising all geometries found in geom. Any MULTI* geometries will be divided into separate singular geometries, e.g., MULTIPOINT((0 0), (1 1)) would be divided into POINT(0 0) and POINT(1 1) in the results; the same is true for elements of a GEOMETRYCOLLECTION found in geom. Any empty geometries in geom are ignored even if they are part of a GEOMETRYCOLLECTION.
ST_DISSOLVE(geom)	Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Line geometries (LINESTRING, LINEARRING, and MULTILINESTRING) are ignored when calculating the resulting geometry.
ST_LINESTRINGFROMORDEREDPOINTS(x, y, t)	Returns a LINESTRING that represents a "track" of the given points (x, y) ordered by the given sort column t (e.g., a timestamp or sequence number). If any of the values in the specified columns are null, the null "point" will be left out of the resulting LINESTRING. If there's only one non-null "point" in the source table, a POINT is returned. If there are no non-null "points" in the source table, a null is returned

Math Functions

Function	Description
ABS(expr)	Calculates the absolute value of expr
ACOS(expr)	Returns the inverse cosine (arccosine) of expr as a double
ACOSF(expr)	Returns the inverse cosine (arccosine) of expr as a float
ACOSH(expr)	Returns the inverse hyperbolic cosine of expr as a double
ACOSHF(expr)	Returns the inverse hyperbolic cosine of expr as a float
ASIN(expr)	Returns the inverse sine (arcsine) of expr as a double
ASINF(expr)	Returns the inverse sine (arcsine) of expr as a float
ASINH(expr)	Returns the inverse hyperbolic sine of expr as a double
ASINHF(expr)	Returns the inverse hyperbolic sine of expr as a float
ATAN(expr)	Returns the inverse tangent (arctangent) of expr as a double
ATANF(expr)	Returns the inverse tangent (arctangent) of expr as a float
ATANH(expr)	Returns the inverse hyperbolic tangent of expr as a double
ATANHF(expr)	Returns the inverse hyperbolic tangent of expr as a float
ATAN2(x, y)	Returns the inverse tangent (arctangent) using two arguments as a double
ATAN2F(x, y)	Returns the inverse tangent (arctangent) using two arguments as a float
ATN2(x, y)	Alias for ATAN2
ATN2F(x, y)	Alias for ATAN2F
CBRT(expr)	Returns the cube root of expr as a double
CBRTF(expr)	Returns the cube root of expr as a float
CEIL(expr)	Alias for CEILING
CEILING(expr)	Rounds expr up to the next highest integer
COS(expr)	Returns the cosine of expr as a double
COSF(expr)	Returns the cosine of expr as a float
COSH(expr)	Returns the hyperbolic cosine of expr as a double
COSHF(expr)	Returns the hyperbolic cosine of expr as a float
COT(expr)	Returns the cotangent of expr as a double
COTF(expr)	Returns the cotangent of expr as a float
DEGREES(expr)	Returns the conversion of expr (in radians) to degrees as a double
DEGREESF(expr)	Returns the conversion of expr (in radians) to degrees as a float
DIVZ(a, b, c)	Returns the quotient a / b unless b == 0, in which case it returns c
EXP(expr)	Returns e to the power of expr as a double
EXPF(expr)	Returns e to the power of expr as a float
FLOOR(expr)	Rounds expr down to the next lowest integer
GREATER(expr_a, expr_b)	Returns whichever of expr_a and expr_b has the larger value, based on typed comparison
HYPOT(x, y)	Returns the hypotenuse of x and y as a double
HYPOTF(x, y)	Returns the hypotenuse of x and y as a float
ISNAN(expr)	Returns 1 (true) if expr is not a number by IEEE standard; otherwise, returns 0 (false)
IS_NAN(expr)	Alias for ISNAN
ISINFINITY(expr)	Returns 1 (true) if expr is infinity by IEEE standard; otherwise, returns 0 (false)
IS_INFINITY(expr)	Alias for ISINFINITY
LDEXP(x, exp)	Returns the value of x * 2exp as a double
LDEXPF(x, exp)	Returns the value of x * 2exp as a float
LESSER(expr_a, expr_b)	Returns whichever of expr_a and expr_b has the smaller value, based on typed comparison
LN(expr)	Returns the natural logarithm of expr as a double
LNF(expr)	Returns the natural logarithm of expr as a float
LOG(expr)	Alias for LN
LOGF(expr)	Alias for LNF
LOG10(expr)	Returns the base-10 logarithm of expr as a double
LOG10F(expr)	Returns the base-10 logarithm of expr as a float
MAX_CONSECUTIVE_BITS(expr)	Calculates the length of the longest series of consecutive 1 bits in the integer expr
MOD(dividend, divisor)	Calculates the remainder after integer division of dividend by divisor
PI()	Returns the value of pi
POW(base, exponent)	Alias for POWER
POWF(base, exponent)	Alias for POWERF
POWER(base, exponent)	Returns base raised to the power of exponent as a double
POWERF(base, exponent)	Returns base raised to the power of exponent as a float
RADIANS(expr)	Returns the conversion of expr (in degrees) to radians as a double
RADIANSF(expr)	Returns the conversion of expr (in degrees) to radians as a float
RAND()	Returns a random floating-point value.
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)	Returns the sine of expr as a double
SINF(expr)	Returns the sine of expr as a float
SINH(expr)	Returns the hyperbolic sine of expr as a double
SINHF(expr)	Returns the hyperbolic sine of expr as a float
SQRT(expr)	Returns the square root of expr as a double
SQRTF(expr)	Returns the square root of expr as a float
TAN(expr)	Returns the tangent of expr as a double
TANF(expr)	Returns the tangent of expr as a float
TANH(expr)	Returns the hyperbolic tangent of expr as a double
TANHF(expr)	Returns the hyperbolic tangent of expr as a float
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.

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
IS_NULL(expr)	Synonymous with ISNULL(expr)
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.
REMOVE_NULLABLE(expr)	Alias for ZEROIFNULL
ZEROIFNULL(expr)	Replaces null values with appropriate values based on the column type (e.g., 0 if numeric column, an empty string if charN column, etc.). Also removes the nullable column property if used to calculate a derived column.

String Functions

Important

These functions will only work with fixed-width string fields (char1 - char256).

Function	Description
ASCII(expr)	Returns the ASCII code for the first character in expr
CHAR(expr)	The character represented by the standard ASCII code expr in the range [ 0 - 127 ]
CONCAT(expr_a, expr_b)	Performs a string concatenation of expr_a & expr_b; use nested CONCAT calls to concatenate more than two strings Note The resulting field size of any CONCAT will be a charN field big enough to hold the concatenated fields, e.g., concatenating a char32 column and a char64 column will result in a char128 column. Columns of type char256 cannot be used with CONCAT.
CONCAT_TRUNCATE(expr_a, expr_b)	Returns the concatenation of expr_a and expr_b, truncated at the maximum size of expr_a. For columns, this size is explicit; for string constants, this will be the smallest charN type that can hold the expr_a string. Examples: CONCAT_TRUNCATE('ABC123','!') -> ABC123! CONCAT_TRUNCATE('ABC123','DEFG') -> ABC123DE (char8 is the minimum size required to hold the ABC123 value, so the result is truncated at 8 characters) CONCAT_TRUNCATE('ABCD1234','DEFG') -> ABCD1234 (char8 is the minimum size required to hold the ABCD1234 value, so no additional characters can be concatenated)
CONTAINS(match_expr, ref_expr)	Returns 1 if ref_expr contains match_expr by string-literal comparison; otherwise, returns 0
DIFFERENCE(expr_a, expr_b)	Returns a value between 0 and 4 that represents the difference between the sounds of expr_a and expr_b based on the SOUNDEX() value of the strings--a value of 4 is the best possible sound match
EDIT_DISTANCE(expr_a, expr_b)	Returns the Levenshtein edit distance between expr_a and expr_b; the lower the the value, the more similar the two strings are
ENDS_WITH(match_expr, ref_expr)	Returns 1 if ref_expr ends with match_expr by string-literal comparison; otherwise, returns 0
INITCAP(expr)	Returns expr with the first letter of each word in uppercase
IS_IPV4(expr)	Returns 1 if expr is an IPV4 address; returns 0 otherwise
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
LPAD(base_expr, length, pad_expr)	Left pads the given base_expr string with the pad_expr string to the given length of characters. If base_expr is longer than length, the return value is shortened to length characters. If length is larger than 256, it will be truncated to 256. Examples: LPAD('test', 9, 'pad') -> padpatest LPAD('test', 3, 'pad') -> tes
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
REVERSE(expr)	Returns expr with the order of characters reversed. Example: REVERSE('Because') -> esuaceB
RIGHT(expr, num_chars)	Returns the rightmost num_chars characters from expr
RPAD(base_expr, length, pad_expr)	Right pads the given base_expr string with the pad_expr string to the given length of characters. If base_expr is longer than length, the return value is shortened to length characters. If length is larger than 256, it will be truncated to 256. Examples: RPAD('test', 9, 'pad') -> testpadpa RPAD('test', 3, 'pad') -> tes
RTRIM(expr)	Removes whitespace from the right side of expr
SOUNDEX(expr)	Returns a soundex value from expr. Only the first word in the string will be considered in the calculation. Note: This is the algorithm used by most programming languages
SPACE(n)	Returns a string consisting of n space characters. The value of n can only be within the range of 0-256.
SPLIT(expr, delim, group_num)	Splits expr into groups delimited by the delim character and returns the group_num split group. If group_num is positive, groups will be counted from the beginning of expr; if negative, groups will be counted from the end of expr going backwards. Two consecutive delimiters will result in an empty string being added to the list of selectable groups. If no instances of delim exist in expr, the entire string is available at group 1 (and -1). Group 0 returns nothing. Examples: SPLIT('apple', 'p', 1) -> a SPLIT('apple', 'p', 2) -> <empty string> SPLIT('apple', 'p', -1) -> le
STARTS_WITH(match_expr, ref_expr)	Returns 1 if ref_expr starts with match_expr by string-literal comparison; otherwise, returns 0
STRCMP(expr_a, expr_b)	Returns 0 if expr_a and expr_b are the same, -1 if expr_a comes before expr_b in a lexigraphical sort, and 1 if expr_b comes before expr_a
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

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

Aggregation Functions

Function	Description
ATTR(expr)	If MIN(expr) = MAX(expr), returns expr; otherwise *
ARG_MIN(agg_expr, ret_expr)	The value of ret_expr where agg_expr is the minimum value (e.g. ARG_MIN(cost, product_id) returns the product ID of the lowest cost product)
ARG_MAX(agg_expr, ret_expr)	The value of ret_expr where agg_expr is the maximum value (e.g. ARG_MAX(cost, product_id) returns the product ID of the highest cost product)
AVG(expr)	Calculates the average value of expr
CORR(expr1, expr2)	Calculates the correlation coefficient of expr1 and expr2
CORRELATION(expr1, expr2)	Alias for CORR
CORRCOEF(expr1, expr2)	Alias for CORR
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
COV(expr1, expr2)	Alias for COVAR_POP
COVAR(expr1, expr2)	Alias for COVAR_POP
COVARIANCE(expr1, expr2)	Alias for COVAR_POP
COVAR_POP(expr1, expr2)	Calculates the population covariance of expr1 and expr2
COVAR_SAMP(expr1, expr2)	Calculates the sample covariance of expr1 and expr2
GROUPING(expr)	Used primarily with Rollup, Cube, and Grouping Sets, to distinguish the source of null values in an aggregated result set, returns whether expr is part of the aggregation set used to calculate the values in a given result set row. Returns 0 if expr is part of the row's aggregation set, 1 if expr is not (meaning that aggregation took place across all expr values). For example, in a ROLLUP(A) operation, there will be two potential rows with null in the result set for column A. One row will contain null values of A aggregated together, and the other will contain null, but be an aggregation over the entire table, irrespective of A values. In this case, GROUPING(A) will return 0 for the null values of A aggregated together (as well as all other grouped A values) and 1 for the row resulting from aggregating across all A values.
KURT(expr)	Alias for KURTOSIS_POP
KURTOSIS(expr)	Alias for KURTOSIS_POP
KURTOSIS_POP(expr)	Calculate the population kurtosis of expr
KURTOSIS_SAMP(expr)	Calculate the sample kurtosis of expr
KURT_POP(expr)	Alias for KURTOSIS_POP
KURT_SAMP(expr)	Alias for KURTOSIS_SAMP
MAX(expr)	Finds the maximum value of expr
MEAN(expr)	Alias for AVG
MIN(expr)	Finds the minimum value of expr
SKEW(expr)	Alias for SKEWNESS_POP
SKEWNESS(expr)	Alias for SKEWNESS_POP
SKEWNESS_POP(expr)	Calculate the population skew of expr
SKEWNESS_SAMP(expr)	Calculate the sample skew of expr
SKEW_POP(expr)	Alias for SKEWNESS_POP
SKEW_SAMP(expr)	Alias for SKEWNESS_SAMP
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
SUM(expr)	Sums all 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

Grouping Functions

Function	Description
ROLLUP(expr)	Calculates n + 1 aggregates for n number of columns in expr
CUBE(expr)	Calculates 2n aggregates for n number of columns in expr
GROUPING SETS(expr)	Calculates aggregates for any given aggregates in expr, including ROLLUP() and CUBE()

Distribution Functions

Distribution functions are column expressions that affect the sharded/replicated nature of the result set of a given query. It may be necessary to force a result set to be distributed in a certain way for a subsequent operation on that result set to be successful.

Function	Description
KI_REPLICATE()	Force a scalar result set to be replicated (query with no GROUP BY)
KI_REPLICATE_GROUP_BY(0)	Force an aggregated result set to be replicated (query with GROUP BY)
KI_MATCH_COLUMN(0)	Aligns the column count of queries that are part of a UNION, INTERSECT or EXCEPT with a query whose column list has been amended with either KI_REPLICATE_GROUP_BY or KI_SHARD_KEY
KI_SHARD_KEY(<column list>)	Force the result set to be sharded on the given columns. This will override any implicitly-derived or explicitly-defined replication status the table would have had. Note The column(s) listed in column list must also appear in the SELECT list; KI_SHARD_KEY merely identifies which of the selected columns should be used as the shard key.

Sharding Example

For example, a query for all employees and their total employees managed, including employees who don't manage anyone, could employ a UNION like this:

SELECT manager_id, COUNT(*)
FROM employee
GROUP BY manager_id
UNION
SELECT id, 0
FROM employee
WHERE id NOT IN
    (
        SELECT manager_id
        FROM employee
        WHERE manager_id IS NOT NULL
    );

In this example, the employee table is sharded on id. Since the first part of the UNION aggregates on manager_id, the result will be replicated. The second part of the UNION does no aggregation and includes the shard key in the SELECT list; the result of this will be sharded.

Given that a limitation of UNION operations is that both parts of a UNION have to be distributed the same way, this query will fail, with the following message:

GPUdb Error: either all input tables must be replicated or all input tables
must be non-replicated

In order to work around this limitation, a distribution function can be used.

One option is to shard the first part of the UNION to match the second part:

SELECT
     manager_id,
     COUNT(*),
     KI_SHARD_KEY(manager_id)
FROM employee
GROUP BY manager_id
UNION
SELECT
    id,
    0,
    KI_MATCH_COLUMN(0)
FROM employee
WHERE id NOT IN
    (
        SELECT manager_id
        FROM employee
        WHERE manager_id IS NOT NULL
    );

Here, the distribution function KI_SHARD_KEY is used to make the selected manager_id column the new shard key for the first part of the UNION. Now, the shard key for the first part of the UNION (manager_id) aligns with the shard key for the second part (id), and the query succeeds. Note the use of KI_MATCH_COLUMN, which aligns the selected column lists on each side of the UNION. Without this matching distribution function, the UNION would appear to be merging three columns from the first part of the query into two columns in the second part and would fail.

Note

The manager_id column must exist in the SELECT list in order for the KI_SHARD_KEY function to designate it as the shard key.

Replication Example

SQL support does not currently extend to creating replicated tables from the results of queries using CREATE TABLE ... AS. For instance, the following will fail:

CREATE REPLICATED TABLE employee_replicated AS
SELECT *
FROM employee

...returning this error:

GPUdb Error: REPLICATED option not allowed with AS option in SQL

However, a replicated table can be created using CREATE TABLE ... AS by forcing the result set to already be replicated using the KI_REPLICATE() distribution function. This will succeed in creating a replicated version of the employee table:

CREATE TABLE employee_replicated AS
SELECT *, KI_REPLICATE()
FROM employee

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_DONT_SPLIT_JOINS	Execute joins within a single query using a single-phase scheme; combining the join (tables, conditions, and filters) with the rest of the subquery (column selection, aggregation, etc); prevents optimizations that could be made if multiple subqueries make use of the same underlying join.
KI_HINT_EXPLAIN_JOINS	Output join explain plan to logs.
KI_HINT_GROUP_BY_FORCE_REPLICATED	Make all result tables within a single query replicated; useful when meeting the input table requirements of JOIN, UNION, etc. in a query containing aggregated subqueries which generate differently-sharded result tables.
KI_HINT_GROUP_BY_PK	Create primary keys for all GROUP BY result sets on the grouped-upon columns/expressions within a given query; often used to create a primary key on the result of a CREATE TABLE...AS that ends in a GROUP BY, and can also make materialized views containing grouping operations more performant. NOTE: if any of the grouped-on expressions are nullable, no primary key will be applied.
KI_HINT_INDEX(<column list>)	Create an index on each of the comma-separated columns in the given list; often used with CREATE TABLE...AS to create an index on a persisted result set.
KI_HINT_JOBID_PREFIX(x)	Tag corresponding database job names(s) with x; e.g., KI_HINT_JOBID_PREFIX(tag) will result in job names like ODBC_tag_01234567-89ab-cdef-0123-456789abcdef.
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)	Set the maximum number of allowed joins within the query that are not against primary key columns; helps protect against unintended Cartesian products.
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.
KI_HINT_SPLIT_JOINS	Execute joins within a single query using a two-phase scheme, separating the join (tables, conditions, and filters) from the rest of the subquery (column selection, aggregation, etc); offers a performance gain for any query with repeated requests (subqueries) for the same join conditions, but different sets of selected columns, at the price of marginal overhead.
KI_HINT_UPDATE_ON_EXISTING_PK	Changes the record collision policy for inserting into a table with a primary key to an upsert scheme; any existing table record with a primary key that matches a record being inserted will be replaced by that new record. Without this hint, the record being inserted will be discarded. If the specified table does not have a primary key, then this hint is ignored.

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 / property list>)] [[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	TINYINT	Native type: int8
	SMALLINT	Native type: int16
	INTEGER	Native type: integer
	INT	Alias for INTEGER
	BIGINT	Native type: long
	LONG	Alias for BIGINT
	REAL	Native type: float
	DOUBLE	Native type: double
	FLOAT	Alias for 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
	DATETIME	Native type: datetime
	TIME	Alias for TYPE_TIME
	TIMESTAMP	Alias for TYPE_TIMESTAMP
Binary	BINARY	Native type: bytes
	BYTES	Alias for BINARY
	VARBINARY	Alias for BINARY
	LONGVARBINARY	Alias for BINARY
Geospatial	WKT	Native type: wkt
	GEOMETRY	Alias for WKT
	ST_GEOMETRY	Alias for WKT

Column Properties

Property	Description
DICT	Applies dict data handling to a column, enabling dictionary-encoding of its values; see Dictionary Encoding for details
DISK_OPTIMIZED	Applies disk-optimized data handling to a column
IPV4	Treats the associated string-based column as an IPv4 address
LZ4	Applies LZ4 compression to a column
LZ4HC	Applies LZ4HC compression to a column
PRIMARY_KEY	Treats the associated column as a primary key, or part of a composite primary key if other columns also have this property
SHARD_KEY	Treats the associated column as a shard key, or part of a composite shard key if other columns also have this property
SNAPPY	Applies Snappy compression to a column
STORE_ONLY	Applies store-only data handling to a column
TEXT_SEARCH	Applies text-searchability to a column
INIT_WITH_NOW	For DATE, TIME, DATETIME, and TIMESTAMP column types, enables the database to replace empty strings and invalid timestamp values with NOW()

CREATE SCHEMA

Schemas are logical containers for tables, referred to as collections, natively.

To create one:

CREATE < SCHEMA | COLLECTION > <schema name>

ALTER SCHEMA

Any of the following facets of a schema can be altered:

• Name
• Protection Mode
• TTL

Rename Schema

A schema can be renamed.

ALTER SCHEMA <schema name>
RENAME TO <new schema name>

Set Protection Mode

A schema can be put into a protected mode, which will prevent tables & view within it from expiring. This will override the individual protection modes of this contained entities, though it will not overwrite their modes. When a schema is taken out of protected status, the contained tables & views return to their previous protection mode. A protected entity cannot have its TTL updated.

Setting PROTECTED to TRUE enables protection for a schema's tables & views, and prevents ALTER...SET TTL from being executed against it or its contained entities.

Setting PROTECTED to FALSE disables protection for a schema's tables & views, and allows subsequent ALTER...SET TTL requests to succeed.

ALTER SCHEMA <schema name>
SET PROTECTED < TRUE | FALSE >

Set TTL

A schema can have a time-to-live (TTL) set, which is a convenience for setting all of the tables & views within that schema to a given TTL.

ALTER SCHEMA <schema name>
SET TTL <new ttl>

DROP SCHEMA

When removing a schema from the database, there are two options available, which control how the removal takes place. Normally, an error will be reported if the schema to drop doesn't exist; if IF EXISTS is specified, no error will be reported. Also, an error will be reported if the schema to drop contains any database objects (tables, etc.); if CASCADE is specified, the schema and all objects within it will be removed.

DROP < SCHEMA | COLLECTION > [IF EXISTS] <schema name> [CASCADE]

CREATE TABLE

Creates a new table in the configured ParentSet collection, unless a schema is specified.

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

CREATE [OR REPLACE] [REPLICATED] [TEMP] TABLE [<schema name>.]<table name>
(
    <column name> <column definition>,
    ...
    <column name> <column definition>,
    [PRIMARY KEY (<column list>)],
    [SHARD KEY (<column list>)],
    [FOREIGN KEY (<column list>) REFERENCES <foreign table name>(<foreign column list>) [AS <foreign key name>],...]
)
[[ATTRIBUTE] INDEX (<column>)]
...
[[ATTRIBUTE] INDEX (<column>)]

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. If TEMP is specified, the table will be removed the next time the database is restarted; otherwise, the table will persist through database restarts.

For example, to create a table with various column types and properties:

    CREATE TABLE master.various_types
    (
        i   INTEGER NOT NULL,                         /* non-nullable integer, part of primary key (defined at end)                         */
        bi  BIGINT NOT NULL,                          /* long, part of primary key, shard key, foreign key source (defined at end)          */
        r   REAL,                                     /* native float                                                                       */
        f   FLOAT,                                    /* native double                                                                      */
        d   DOUBLE(STORE_ONLY),                       /* native 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, DICT),                        /* char32 using dictionary-encoding of values                                         */
        p   VARCHAR(256, SNAPPY),                     /* char256 using Snappy compression of values                                         */
        ip  VARCHAR(IPV4),                            /* IP address                                                                         */
        ts  TYPE_TIMESTAMP,                           /* timestamp                                                                          */
        td  TYPE_DATE,                                /* simple date                                                                        */
        tt  TYPE_TIME,                                /* simple time                                                                        */
        dt  DATETIME,                                 /* date/time                                                                          */
        dc1 DECIMAL,                                  /* native long                                                                        */
        dc2 DECIMAL(18,4),                            /* native decimal                                                                     */
        dc3 DECIMAL(6,5),                             /* native float                                                                       */
        dc4 DECIMAL(7, 5, STORE_ONLY),                /* double, not in-memory                                                              */
        n   NUMERIC(5, 3),                            /* native decimal, the next largest native numeric type to hold the number type       */
        wkt WKT,                                      /* geospatial column for WKT string data                                              */
        PRIMARY KEY (i, bi),                          /* primary key columns must be NOT NULL                                               */
        SHARD KEY (bi),                               /* shard key columns must be part of the primary key                                  */
        FOREIGN KEY (bi) REFERENCES lookup(id) AS fk  /* foreign key is often on the shard key                                              */
    )
    INDEX (ip)                                        /* index on IP column                                                                 */
    INDEX (ts)                                        /* index on timestamp column                                                          */

CREATE TABLE ... AS

Creates a new table from the given query in the configured ParentSet collection, unless a schema is specified.

The general format is:

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

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. If TEMP is specified, the table will be removed the next time the database is restarted; otherwise, the table will persist through database restarts. If VRAM is specified, the table will be loaded into GPU memory.

While primary keys & foreign keys are not transferred to the new table, shard keys will be, if the column(s) composing them are part of the SELECT list.

The following can be applied to the SELECT statement to affect the resulting table:

• KI_HINT_GROUP_BY_PK (hint) - creates a primary keys on the columns in the GROUP BY clause if the outermost SELECT statement contains a GROUP BY
• KI_HINT_INDEX (hint) - indexes result columns
• KI_SHARD_KEY (pseudo-function) - shards the result table

For example, to create a replicated temporary table that is a copy of an existing table, failing if a table with the same name as the target table already exists:

    CREATE REPLICATED TEMP TABLE new_temporary_table AS
    (
        SELECT *
        FROM old_table
    )

To create a permanent table with columns a, b, c, & d a new shard key on columns a & b, and an index on column d, replacing a table with the same name as the target table, if it exists:

    CREATE OR REPLACE TABLE new_sharded_table AS
    (
        SELECT a, b, c, d, KI_SHARD_KEY(a, b) /* KI_HINT_INDEX(d) */
        FROM old_table
    )

To copy a table with columns a, b, c, & d, preserving the primary key on a, b, & c, and the foreign key from d; a new table must be created to match the schema of the old one and then records can be copied from the old one to the new one:

    CREATE TABLE new_pk_copy_table
    (
        a INT NOT NULL,
        b INT NOT NULL,
        c VARCHAR(32) NOT NULL,
        d TIMESTAMP,
        PRIMARY KEY (a, b, c),
        FOREIGN KEY (d) REFERENCES old_table_lookup(d)
    )

    INSERT INTO new_pk_copy_table
    SELECT *
    FROM old_table

Note

This create/insert process is necessary, as neither primary keys nor foreign keys can be preserved through hints.

See Limitations for other restrictions.

ALTER TABLE

Any of the following facets of a table can be altered:

• Name
• Schema
• Global Access Mode
• Protection Mode
• TTL
• Columns
• Column Indexes
• Foreign Keys

Rename Table

A table can be renamed.

ALTER TABLE [<schema name>.]<table name>
RENAME TO <new table name>

Move Table

A table can be moved from one schema to another.

The general form of the command is:

ALTER TABLE [<schema name>.]<table name>
< MOVE TO | SET SCHEMA > <new schema name>

For example, to move the sales_2017 table from the olap schema to the archive schema:

    ALTER TABLE olap.sales_2017
    MOVE TO archive

To move the sales_2017 table from the archive schema to the root schema:

    ALTER TABLE archive.sales_2017
    MOVE TO ""

Set Access Mode

A table can have its global accessibility modified for all users in the system, independently from and further restricting any role-based access controls in place. Note that changing the access mode cannot widen access for users not already granted access; it can only narrow access for those who already have access. This setting will also trump administrative access to a table.

ALTER TABLE [<schema name>.]<table name>
SET ACCESS MODE < NO_ACCESS | READ_ONLY | WRITE_ONLY | READ_WRITE >

Set Protection Mode

A table can be altered to not expire, by altering its protection mode. Note that tables don't normally expire, but can be set to expire.

Setting PROTECTED to TRUE enables protection for a table and prevents ALTER TABLE...SET TTL from being executed against it.

Setting PROTECTED to FALSE disables protection for a table and allows subsequent ALTER TABLE...SET TTL requests to succeed.

ALTER TABLE <table name>
SET PROTECTED < TRUE | FALSE >

Set TTL

A table's time-to-live (TTL) can be altered.

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 [<schema name>.]<table name>
ADD <column name> <column definition> [DEFAULT <string/numeric constant | column name>]

Note

Column compression must be applied after a new column is added; see Compress Column for syntax.

Examples

To add, to the employee table, a salary column that is a non-nullable, store-only, 10-digit number field containing 2 decimal places with a default value of 0:

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

To add, to the employee table, a category column that is a nullable, dictionary-encoded, 32-character text field:

    ALTER TABLE employee
    ADD category VARCHAR(32, DICT)

To add, to the employee table, a bio column that is a nullable, text-searchable, disk-optimized, 256-character text field:

    ALTER TABLE employee
    ADD bio VARCHAR(TEXT_SEARCH, DISK_OPTIMIZED)

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 [<schema name>.]<table name>
MODIFY [COLUMN] <column name> <column definition>

ALTER TABLE [<schema name>.]<table name>
ALTER COLUMN <column name> <column definition>

Note

Column compression must be applied after an existing column is modified; see Compress Column for syntax.

Examples

To change, in the employee table, the first_name column to one that is a non-nullable, dictionary-encoded, 50-character text field:

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

Compress Column

A column can have its data compressed in memeory.

The general form to alter a column's compression setting is:

ALTER TABLE [<schema name>.]<table name>
SET COLUMN <column name> COMPRESSION [TO] <compression type>

For example, to use LZ4 compression on a column:

    ALTER TABLE employee
    SET COLUMN first_name COMPRESSION lz4

To use no compression on a column:

    ALTER TABLE employee
    SET COLUMN first_name COMPRESSION none

Drop Column

An existing column can be removed from a table:

ALTER TABLE [<schema name>.]<table name>
DROP COLUMN <column name>

Add Column Index

An index can be added to any column not marked as store-only in order to improve the performance of operations whose expressions contain relational operators against the column.

ALTER TABLE [<schema name>.]<table name>
ADD INDEX (<column name>)

For example, to index the employee table's department ID column:

    ALTER TABLE employee
    ADD INDEX (dept_id)

Drop Column Index

An existing column index can be removed from a table:

ALTER TABLE [<schema name>.]<table name>
DROP INDEX (<column name>)

For example, to drop the index on the employee table's department ID column:

    ALTER TABLE employee
    DROP INDEX (dept_id)

Add Foreign Key

A foreign key can be added to any column or set of columns not marked as store-only in order to improve the performance of join operations between the table being altered and the table referenced in the foreign key.

ALTER TABLE [<schema name>.]<table name>
ADD FOREIGN KEY (<column name>,...) REFERENCES <foreign table name>(<foreign column name>,...) [AS <foreign key name>]

For example, to add a foreign key on the employee table's department ID column, linking it to the department table's department ID column:

    ALTER TABLE employee
    ADD FOREIGN KEY (dept_id) REFERENCES department(id) AS fk_emp_dept

Drop Foreign Key

An existing foreign key can be removed from a table, either by the name (alias) given to it during creation or by its definition:

• By name:

  ALTER TABLE [<schema name>.]<table name>
  DROP FOREIGN KEY <foreign key name>
  

• By definition:

  ALTER TABLE [<schema name>.]<table name>
  DROP FOREIGN KEY (<column name>,...) REFERENCES <foreign table name>(<foreign column name>,...)
  

For example, to drop the foreign key on the employee table's department ID column:

• By name:

    ALTER TABLE employee
    DROP FOREIGN KEY fk_emp_dept

• By definition:

    ALTER TABLE employee
    DROP FOREIGN KEY (dept_id) REFERENCES department(id)

TRUNCATE TABLE

Deletes all the records from a table:

TRUNCATE TABLE [<schema name>.]<table name>

DROP TABLE

When removing a table from the database, there are two options available, which control how the removal takes place. Normally, an error will be reported if the table to drop doesn't exist; if IF EXISTS is specified, no error will be reported.

DROP TABLE [IF EXISTS] [<schema name>.]<table name>

CREATE VIEW

Creates a new view from the given query in the configured ParentSet collection, unless a schema is specified. If OR REPLACE is specified, any existing view with the same name will be dropped before creating this one. If TEMP is specified, the view will be removed the next time the database is restarted; otherwise, the view will be recreated upon database restart. If VRAM is specified, the view will be loaded into GPU memory.

When any of a view's source tables is dropped, the view will also be dropped. NOTE: a CREATE OR REPLACE issues an implicit drop, so replacing an input table will have the same effect of dropping the view.

When MATERIALIZED is not specified, the view created will mirror the interface of a typical view, always reflecting the latest updates in the supporting tables. However, the intermediary results of Kinetica views are cached to improve the performance of queries against them. This means that, unlike typical views, Kinetica views are not lightweight database entities, but rather consume memory and processing time proportional to the size of the source data and complexity of the query.

While primary keys & foreign keys are not transferred to the new view, shard keys will be, if the column(s) composing them are part of the SELECT list. A new shard key can be specified for the created view by using the KI_SHARD_KEY(<column list>) pseudo-function in the SELECT list.

See Limitations for other restrictions.

The general format is:

CREATE [OR REPLACE] [TEMP|VRAM] [MATERIALIZED] VIEW [<schema name>.]<view name>
[
    REFRESH
    <
        OFF |
        ON CHANGE |
        EVERY <number> <SECOND[S] | <MINUTE[S] | HOUR[S] | DAY[S]> [STARTING AT <YYYY-MM-DD [HH:MM[:SS]]>]
    >
]
AS
<SELECT statement>

For example, to create a view that is a copy of an existing table, failing if a table, view, or collection with the same name as the target view already exists:

    CREATE VIEW view_of_table AS
    (
        SELECT *
        FROM table_to_view
    )

CREATE MATERIALIZED VIEW

Specifying MATERIALIZED in a CREATE VIEW statement will make the view a materialized view, able to be automatically refreshed using the specified REFRESH scheme.

• OFF will prevent the view from being automatically refreshed, but will still allow manual refreshes of the data to be requested
• ON CHANGE will cause the view to be updated any time a record is added, modified, or deleted from the subtending tables in the view's query
• EVERY allows specification of an interval in seconds, minutes, hours, or days, with the optional specification of a starting time at which the first refresh interval will run; if no start time is specified, the default will be an interval's worth of time from the point at which the view creation was requested

To create a materialized view with columns a, b, c, & d and a new shard key on columns a & b, that refreshes once per half hour, replacing a view with the same name as the target view, if it exists:

    CREATE OR REPLACE MATERIALIZED VIEW materialized_view_of_table
    REFRESH EVERY .5 HOURS AS
    (
        SELECT a, b, c, d, KI_SHARD_KEY(a, b)
        FROM table_to_view
    )

ALTER VIEW

Any of the following facets of a view can be altered:

• Name
• Schema
• Global Access Mode
• Protection Mode
• Refresh Mode
• TTL

Rename View

A view can be renamed.

ALTER VIEW [<schema name>.]<view name>
RENAME TO <new view name>

Move View

A view can be moved from one schema to another.

The general form of the command is:

ALTER VIEW [<schema name>.]<view name>
< MOVE TO | SET SCHEMA > <new schema name>

For example, to move the sales_2017 view from the olap schema to the archive schema:

    ALTER VIEW olap.sales_2017
    MOVE TO archive

To move the sales_2017 view from the archive schema to the root schema:

    ALTER VIEW archive.sales_2017
    MOVE TO ""

Set Access Mode

A view can have its global accessibility modified for all users in the system, independently from and further restricting any role-based access controls in place. Note that changing the access mode cannot widen access for users not already granted access; it can only narrow access for those who already have access. This setting will also trump administrative access to a view.

ALTER VIEW [<schema name>.]<view name>
SET ACCESS MODE < NO_ACCESS | READ_ONLY | WRITE_ONLY | READ_WRITE >

Set Protection Mode

A view can be altered to not expire, by altering its protection mode. Note that views don't normally expire, but can be set to expire.

Setting PROTECTED to TRUE enables protection for a view and prevents ALTER VIEW...SET TTL from being executed against it.

Setting PROTECTED to FALSE disables protection for a view and allows subsequent ALTER VIEW...SET TTL requests to succeed.

ALTER view <view name>
SET PROTECTED < TRUE | FALSE >

Set Refresh Mode

The refresh mode of a materialized view can be modified:

• OFF will prevent the view from being automatically refreshed, but will still allow manual refreshes of the data to be requested
• ON CHANGE will cause the view to be updated any time a record is added, modified, or deleted from the subtending tables in the view's query
• EVERY allows specification of an interval in seconds, minutes, hours, or days, with the optional specification of a starting time at which the first refresh interval will run; if no start time is specified, the default will be an interval's worth of time from the point at which the view alteration was requested

ALTER MATERIALIZED VIEW [<schema name>.]<view name>
SET REFRESH
<
    OFF |
    ON CHANGE |
    EVERY <number> <SECOND[S] | <MINUTE[S] | HOUR[S] | DAY[S]> [STARTING AT <YYYY-MM-DD [HH:MM[:SS]]>]
>

For example, to alter the current sales table to refresh every 6 hours:

   ALTER MATERIALIZED VIEW sales_current
   SET REFRESH EVERY 6 HOURS

This would alter the view in the same way:

   ALTER MATERIALIZED VIEW sales_current
   SET REFRESH EVERY .25 DAYS

Set TTL

A view's time-to-live (TTL) can be altered.

ALTER VIEW <view name>
SET TTL <new ttl>

REFRESH VIEW

Refreshes the data within a materialized view:

REFRESH [MATERIALIZED] VIEW [<schema name>.]<view name>

DROP VIEW

When removing a view from the database, there are two options available, which control how the removal takes place. Normally, an error will be reported if the view to drop doesn't exist; if IF EXISTS is specified, no error will be reported.

DROP [MATERIALIZED] VIEW [IF EXISTS] [<schema name>.]<view name>

DESCRIBE

Lists the columns and column types & properties for a given table or view:

DESC[RIBE] [<schema name>.]<table/view name>

For example, to describe the example table created in the CREATE TABLE section:

DESC master.various_types

+-----------+--------+------------+------------------------------------+
|   Col_num | Name   | Null?      | Type                               |
+-----------+--------+------------+------------------------------------+
|         0 | bi     | NOT NULL   | BIGINT(primary_key, shard_key)     |
|         1 | i      | NOT NULL   | INTEGER(primary_key)               |
|         2 | r      |            | REAL                               |
|         3 | f      |            | DOUBLE                             |
|         4 | d      |            | DOUBLE(store_only)                 |
|         5 | s      |            | VARCHAR(store_only, text_search)   |
|         6 | c      |            | VARCHAR(32, dict)                  |
|         7 | p      |            | VARCHAR(256, snappy)               |
|         8 | ip     |            | IPV4                               |
|         9 | ts     |            | TIMESTAMP                          |
|        10 | td     |            | DATE                               |
|        11 | tt     |            | TIME                               |
|        12 | dt     |            | DATETIME                           |
|        13 | dc1    |            | BIGINT                             |
|        14 | dc2    |            | DECIMAL                            |
|        15 | dc3    |            | REAL                               |
|        16 | dc4    |            | DOUBLE(store_only)                 |
|        17 | n      |            | DECIMAL                            |
|        18 | wkt    |            | GEOMETRY                           |
+-----------+--------+------------+------------------------------------+

SHOW CREATE TABLE

Outputs the DDL statement required to reconstruct the given table:

SHOW CREATE TABLE [<schema name>.]<table name>

For example, to output the DDL for the example table created in the CREATE TABLE section:

SHOW CREATE TABLE master.various_types

CREATE TABLE "MASTER"."various_types"
(
    "bi" BIGINT(primary_key, shard_key) NOT NULL,
    "i" INTEGER(primary_key) NOT NULL,
    "r" REAL,
    "f" DOUBLE,
    "d" DOUBLE(store_only),
    "s" VARCHAR(store_only, text_search),
    "c" VARCHAR(32, dict),
    "p" VARCHAR(256, snappy),
    "ip" IPV4,
    "ts" TIMESTAMP,
    "td" DATE,
    "tt" TIME,
    "dt" DATETIME,
    "dc1" BIGINT,
    "dc2" DECIMAL,
    "dc3" REAL,
    "dc4" DOUBLE(store_only),
    "n" DECIMAL,
    "wkt" GEOMETRY,
    FOREIGN KEY (bi) REFERENCES lookup(id) AS fk
)
ATTRIBUTE INDEX (ip)
ATTRIBUTE INDEX (ts)

Note

The response to SHOW CREATE TABLE is a single-record result set with the DDL statement as the value in the DDL column.

Data Manipulation (DML)

INSERT

To insert records with literal values, use this format:

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

For example:

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

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

INSERT INTO [<schema name>.]<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.

To upsert records, inserting new records and updating existing ones (as denoted by primary key), use the KI_HINT_UPDATE_ON_EXISTING_PK hint. If the target table has no primary key, this hint will be ignored.

For example:

INSERT INTO target_table /* KI_HINT_UPDATE_ON_EXISTING_PK */
SELECT *
FROM source_table
WHERE last_updated_ts > NOW() - INT(10 * 60 * 1000)

Important

By default, any record being inserted that matches the primary key of an existing record in the target table will be discarded, and the existing record will remain unchanged.

UPDATE

Updates can set columns to specified constant values or expressions. The general format is:

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

For example, to update employee with ID 5 to have a new manager, with ID 3, and to have a 10% salary increase:

UPDATE employee
SET
    sal = sal * 1.10,
    manager_id = 3
WHERE id = 5

DELETE

Deletes records from a table; the general format is:

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

For example, to delete employee with ID 6:

DELETE
FROM employee
WHERE id = 6

Working with Files

The ODBC Server allows files to be read from and written to via SQL. The files are accessed by the ODBC Server process, which means they need to have system file permissions set appropriately for that process owner.

Writing to CSV Files

The ODBC Server can export data to a file, prepending the header information necessary for reading the data back from the file again.

A new file can be created with a data export via the following syntax:

CREATE TABLE FILE."<file name>" AS
SELECT <column list>
FROM <table name>
WHERE <expression list>

For example, to write all records from the emp table to an emp_2017.csv file:

CREATE TABLE FILE."emp_2017.csv" AS
SELECT *
FROM emp

A file can have data appended to it using the following syntax:

INSERT INTO FILE."<file name>"
SELECT <column list>
FROM <table name>
WHERE <expression list>

For example, to append records from the emp table of employees hired in the second half of 2017 to an emp_2017.csv file:

INSERT INTO FILE."emp_2017.csv"
SELECT id, dept_id, manager_id, first_name, last_name, sal, hire_date
FROM emp
WHERE hire_date BETWEEN '2017-07-01' AND '2017-12-31'

Reading from CSV Files

The ODBC Server can query data from a CSV file using a standard SELECT statement.

In order for the ODBC Server to read data from a CSV file, the file must be properly formatted:

• The first row must contain the same column name/type format required by GAdmin for importing data. This is also the same format written during export, described in Writing to CSV Files above.

• The fields must be comma-delimited

• Strings can optionally be enclosed in double-quotes; double-quotes must be used when the data contains 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."

Data can be read from a file with the following syntax:

SELECT <column list>
FROM FILE."<file name>"
WHERE <expression list>

For example, to read employee IDs & names in department 2 from an emp_2017.csv file:

SELECT id, first_name || ' ' || last_name as "Full Name"
FROM FILE."emp_2017.csv"
WHERE dept_id = 2

The SELECT statement can be paired with a CREATE TABLE ... AS to import records into a new table or an INSERT INTO to import records into an existing table.

For example, to import all employee records from an emp_2017.csv file into a new table named emp_2017_archive:

CREATE TABLE emp_2017_archive AS
SELECT *
FROM FILE."emp_2017.csv"

To import department 2 employee records from an emp_2017.csv file into an emp table, whose column types match the file's field types:

INSERT INTO emp (id, dept_id, manager_id, first_name, last_name)
SELECT id, dept_id, manager_id, first_name, last_name
FROM FILE."emp_2017.csv"
WHERE dept_id = 2

Security

Kinetica provides basic table-level role-based access control for users. It also allows global read/write and administrative access to be granted. For details about Kinetica security, see Security Concepts.

CREATE ROLE

A new role can be created as a container for permissions or other roles, though both of those must be granted to the role after its creation.

To create a new role, use this format:

CREATE ROLE <role name>

For example, to create an analyst role:

CREATE ROLE analyst

CREATE USER

Users can be added to the system and assigned table-level & system-level permissions either directly or via roles.

To add a user to the system, use this format:

CREATE USER <user name> [ < [WITH] PASSWORD [=] | IDENTIFIED BY [PASSWORD] > '<user password>' ]

Note

The password needs to be single-quoted and must not contain single quotes.

For example, two of the ways to create a new internal user with the user ID of jdoe and a password of secret are:

CREATE USER jdoe IDENTIFIED BY 'secret'
CREATE USER jdoe WITH PASSWORD 'secret'

To create a user with an existing external LDAP user, the user name should match the LDAP user name and be prepended with the @ symbol; no password is supplied, as the user will be externally authenticated:

CREATE USER @jdoe

DROP ROLE

Dropping a role will remove the associated permissions & roles granted through the role to all users with the role. Users & roles granted the same permissions either directly or via other roles will retain those permissions.

Any role, other than the default roles, can be removed from the system.

To drop an existing role, use this format:

DROP ROLE <role name>

For example, to drop the analyst role:

DROP ROLE analyst

DROP USER

Any user, other than the default users, can be removed from the system. Note that any database objects created by a user will remain when the user is removed.

To remove a user from the system, use this format:

DROP USER <user name>

For example, to drop an internal user jdoe:

DROP USER jdoe

To drop an external LDAP user jdoe:

DROP USER @jdoe

GRANT Role

Roles can be granted directly to users or other roles.

To grant a role to a user or role:

GRANT <role name> TO <user name | role name>

For example, to grant a role allowing access to analyst tables to the analyst role, and then grant that analyst role to user jdoe:

GRANT analyst_table_access TO analyst
GRANT analyst TO jdoe

GRANT SYSTEM Permission

System-level permissions can be granted directly to users or other roles.

To grant a system-level permission:

GRANT SYSTEM < ADMIN | READ | WRITE > TO <user name | role name>

For example, to grant system administrator permission to jdoe and then grant read access to all tables to the auditor role:

GRANT SYSTEM admin TO jdoe
GRANT SYSTEM read TO auditor

GRANT Table Permission

Table-level permissions, which can be applied to schemas, tables, and views, can be granted directly to users or other roles.

To grant a table-level permission:

GRANT < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
    ON [TABLE] [<schema name>.]<table name>
    TO <user name | role name>

Note

The ALL permission corresponds to the native table_admin permission, which gives full read/write access as well as the additional permission to ALTER and DROP the table.

For example, to grant full access on the order table to jdoe and then grant SELECT access on the order_history table to the analyst role:

GRANT ALL PRIVILEGES ON TABLE order TO jdoe
GRANT SELECT ON order_history TO analyst

REVOKE Role

Roles can be revoked from users or other roles.

To revoke a role:

REVOKE <role name> FROM <user name | role name>

For example, to revoke a role allowing access to analyst tables from the analyst role, and then revoke that analyst role from user jdoe:

REVOKE analyst_table_access FROM analyst
REVOKE analyst FROM jdoe

REVOKE SYSTEM Permission

System-level permissions can be revoked from users or other roles.

To revoke a system-level permission:

REVOKE SYSTEM < ADMIN | READ | WRITE > FROM <user name | role name>

For example, to revoke system administrator permission from jdoe and then revoke write access to all tables from the auditor role:

REVOKE SYSTEM admin FROM jdoe
REVOKE SYSTEM write FROM auditor

REVOKE Table Permission

Table-level permissions, which can be applied to schemas, tables, and views, can be revoked from users or other roles.

To revoke a table-level permission:

REVOKE < SELECT | INSERT | UPDATE | DELETE | ALL > [PRIVILEGES]
    ON [TABLE] [<schema name>.]<table name>
    FROM <user name | role name>

Note

The ALL permission corresponds to the native table_admin permission, which gives full read/write access as well as the additional permission to ALTER and DROP the table.

For example, to revoke full access on the order table from jdoe and then revoke DELETE access on the order_history table from the analyst role:

REVOKE ALL PRIVILEGES ON TABLE order FROM jdoe
REVOKE DELETE ON order_history FROM analyst

SHOW SECURITY

To list the permissions & roles for one or more users and/or roles (or all users and roles in the system):

SHOW SECURITY [FOR <user name | role name>,...]

For example, to show the permissions & roles for jdoe:

SHOW SECURITY FOR jdoe

To show all users & roles:

SHOW SECURITY

Limitations

Kinetica has some limitations for any columns marked as store-only and string columns lacking a charN attribute.

Unlimited-Width Strings

String columns with no charN attribute are stored in whole on disk and in hashed form in memory. Because the strings are only available for processing as hashes, only equality-based operations can be applied to them.

Partially Applicable Operations

• 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)
• Windowing Features
  • Null Ordering
  • Collation on ORDER BY
  • Frame Exclusion (EXCLUDE)
  • Frames Organized by RANGE
• Ranking Functions
  • PERCENTILE_DISC
  • PERCENTILE_CONT
• Spatial Queries (e.g., SQL GIS)