Data Manipulation (DML)
DML allows you to update and query data stored in MapD.
See Using Geospatial Objects: Geospatial Functions for details on geospatial functions.
INSERT
INSERT INTO <table> VALUES (value, ...);
Use this statement for singlerow ad hoc inserts. (When inserting many rows, use the more efficient COPY command.)
CREATE TABLE foo (a INT, b FLOAT, c TEXT, d TIMESTAMP);
INSERT INTO foo VALUES (NULL, 3.1415, 'xyz', '20150511 211720`);
SELECT
[ WITH <alias> AS <query>,... ]
SELECT [ALLDISTINCT] <expr> [AS [<alias>]], ...
FROM <table> [ <alias> ], ...
[WHERE <expr>]
[GROUP BY <expr>, ...]
[HAVING <expr>]
[ORDER BY <expr> [ ASC  DESC ] , ...] [ NULLS FIRST  NULLS LAST ]
[LIMIT {<number>ALL} [OFFSET <number> [ROWS]]]
[ANY  ALL (subquery) ;
Usage Notes
ORDER BY
 Sort order defaults to ascending (ASC).
 Sorts null values after nonnull values by default in an ascending sort, before nonnull values in a descending sort. For any query, you can use
NULLS FIRST
to sort null values to the top of the results orNULLS LAST
to sort null values to the bottom of the results.  Allows you to use a positional reference to choose the sort column. For example, the command
SELECT colA,colB FROM table1 ORDER BY 2
sorts the results on colB because it is in position 2.
UPDATE
[ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression  DEFAULT }  ( column_name [, ...] ) = [ ROW ] ( { expression  DEFAULT } [, ...] )  ( column_name [, ...] ) = ( subSELECT ) } [, ...] [ FROM from_list ] [ WHERE condition  WHERE CURRENT OF cursor_name ] [ RETURNING *  output_expression [ [ AS ] output_name ] [, ...] ]
UPDATE
changes the values of the specified columns in all rows that satisfy the condition. For more information, see UPDATE.
DELETE
[ WITH [ RECURSIVE ] with_query [, ...] ] DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] [ USING using_list ] [ WHERE condition  WHERE CURRENT OF cursor_name ] [ RETURNING *  output_expression [ [ AS ] output_name ] [, ...] ]
DELETE
deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table. For more information, see DELETE.
EXPLAIN
Shows generated Intermediate Representation (IR) code, identifying whether it is executed on GPU or CPU. This is primarily used internally by MapD to monitor behavior.
EXPLAIN <STMT>;
For example, when you use the EXPLAIN command on a basic statement, the utility returns 90 lines of IR code that is not meant to be human readable. At the top of the listing, though, a heading indicates whether it is IR for the CPU
or IR for the GPU
, which can be useful to know in some situations.
EXPLAIN CALCITE
Returns a Relational Algebra tree describing the highlevel plan to execute the statement.
EXPLAIN CALCITE <STMT>;
The table below lists the relational algebra classes used to describe the execution plan for a SQL statement.
Method  Description 

LogicalAggregate 
Relational operator that eliminates duplicates and computes totals 
LogicalCalc 
Relational expression that computes project expressions and also filters 
LogicalChi 
Relational operator that converts a stream to a relation 
LogicalCorrelate 
Relational operator that performs nestedloop joins 
LogicalDelta 
Relational operator that converts a relation to a stream 
LogicalExchange 
Relational expression that imposes a particular distribution on its input without otherwise changing its content 
LogicalFilter 
Relational expression that iterates over its input and returns elements for which a condition evaluates to true 
LogicalIntersect 
Relational expression that returns the intersection of the rows of its inputs 
LogicalJoin 
Relational expression that combines two relational expressions according to some condition 
LogicalMatch 
Relational expression that represents a MATCH_RECOGNIZE node 
LogicalMinus 
Relational expression that returns the rows of its first input minus any matching rows from its other inputs. Corresponds to the SQL EXCEPT operator 
LogicalProject 
Relational expression that computes a set of ‘select expressions’ from its input relational expression 
LogicalSort 
Relational expression that imposes a particular sort order on its input without otherwise changing its content 
LogicalTableFunctionScan 
Relational expression that calls a tablevalued function 
LogicalTableModify 
Relational expression that modifies a table. It is similar to TableScan, but represents a request to modify a table rather than read from it 
LogicalTableScan 
Reads all the rows from a RelOptTable 
LogicalUnion 
Relational expression that returns the union of the rows of its inputs, optionally eliminating duplicates 
LogicalValues 
Relational expression whose value is a sequence of zero or more literal row values 
LogicalWindow 
Relational expression representing a set of window aggregates 
For example, a SELECT statement is described as a table scan and projection.
mapdql> explain calcite (select * from movies);
Explanation
LogicalProject(movieId=[$0], title=[$1], genres=[$2])
LogicalTableScan(table=[[CATALOG, mapd, MOVIES]])
If you add a sort order, the table projection is folded under a LogicalSort procedure.
mapdql> explain calcite (select * from movies order by title);
Explanation
LogicalSort(sort0=[$1], dir0=[ASC])
LogicalProject(movieId=[$0], title=[$1], genres=[$2])
LogicalTableScan(table=[[CATALOG, mapd, MOVIES]])
When the SQL statement is simple, the EXPLAIN CALCITE version is actually less “human readable.” EXPLAIN CALCITE is more useful when you work with more complex SQL statements, like the one that follows. This query performs a scan on the BOOK table before scanning the BOOK_ORDER table.
mapdql> explain calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
FROM book b, book_customer bc, book_order bo, shipper s
WHERE bo.cust_id = bc.cust_id AND b.book_id = bo.book_id AND bo.shipper_id = s.shipper_id
AND s.name = 'UPS';
Explanation
LogicalProject(firstname=[$5], lastname=[$6], title=[$2], orderdate=[$11], name=[$14])
LogicalFilter(condition=[AND(=($9, $4), =($0, $8), =($10, $13), =($14, 'UPS'))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, mapd, BOOK]])
LogicalTableScan(table=[[CATALOG, mapd, BOOK_CUSTOMER]])
LogicalTableScan(table=[[CATALOG, mapd, BOOK_ORDER]])
LogicalTableScan(table=[[CATALOG, mapd, SHIPPER]])
Revising the original SQL command results in a more natural selection order and a more performant query.
mapdql> explain calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
FROM book_order bo, book_customer bc, book b, shipper s
WHERE bo.cust_id = bc.cust_id AND bo.book_id = b.book_id AND bo.shipper_id = s.shipper_id
AND s.name = 'UPS';
Explanation
LogicalProject(firstname=[$10], lastname=[$11], title=[$7], orderdate=[$3], name=[$14])
LogicalFilter(condition=[AND(=($1, $9), =($5, $0), =($2, $13), =($14, 'UPS'))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, mapd, BOOK_ORDER]])
LogicalTableScan(table=[[CATALOG, mapd, BOOK_CUSTOMER]])
LogicalTableScan(table=[[CATALOG, mapd, BOOK]])
LogicalTableScan(table=[[CATALOG, mapd, SHIPPER]])
Table Expression and Join Support
<table> , <table> WHERE <column> = <column>
<table> [ LEFT ] JOIN <table> ON <column> = <column>
Usage Notes
 If a join column name or alias is not unique, it must be prefixed by its table name.
 The data type of join columns must be SMALLINT, INTEGER, BIGINT, DATE, or TEXT/VARCHAR ENCODING DICT.
 Data types of join columns must match exactly. For example, a SMALLINT column cannot be joined to a BIGINT column.
 For all but the first table list in the fromlist, the data values in the join column must be unique. In data warehouse terms, list the “fact” table first, followed by any number of “dimension” tables.
Logical Operator Support
Operator  Description 

AND 
Logical AND 
NOT 
Negates value 
OR 
Logical OR 
Comparison Operator Support
Operator  Description 

= 
Equals 
<> 
Not equals 
> 
Greater than 
>= 
Greater than or equal to 
< 
Less than 
<= 
Less than or equal to 
BETWEEN x AND y 
Is a value within a range 
NOT BETWEEN x AND y 
Is a value not within a range 
IS NULL 
Is a value null 
IS NOT NULL 
Is a value not null 
NULLIF(x, y) 
Compare expressions x and
y. If different, return
x. If they are the same,
return null . For example,
if a dataset uses ‘NA’ for
null values, you can use
this statement to return
null using SELECT
NULLIF(field_name,'NA') . 
Mathematical Function Support
Function  Description 

ABS(x) 
Returns the absolute value of x 
CEIL(x) 
Returns the smallest integer not less than the argument 
DEGREES(x) 
Converts radians to degrees 
EXP(x) 
Returns the value of e to the power of x 
FLOOR(x) 
Returns the largest integer not greater than the argument 
LN(x) 
Returns the natural logarithm of x 
LOG(x) 
Returns the natural logarithm of x 
LOG10(x) 
Returns the base10 logarithm of the specified float expression x 
MOD(x, y) 
Returns the remainder of x divided by y 
PI() 
Returns the value of pi 
POWER(x,
y) 
Returns the value of x raised to the power of y 
RADIANS(x) 
Converts degrees to radians 
ROUND(x ) 
Rounds x to the nearest integer value, but does not change the data type. For example, the double value 4.1 rounds to the double value 4. 
ROUND_TO_DIGIT
(x, y) 
Rounds x to y decimal places 
SIGN(x) 
Returns the sign of x as 1, 0, 1 if x is negative, zero, or positive 
SQRT(x) 
Returns the square root of x. 
TRUNCATE(x,
y) 
Truncates x to y decimal places 
Trigonometric Function Support
Function  Description 

ACOS(x) 
Returns the arc cosine of x 
ASIN(x) 
Returns the arc sine of x 
ATAN(x) 
Returns the arc tangent of x 
ATAN2(x, y) 
Returns the arc tangent of x and y 
COS(x) 
Returns the cosine of x 
COT(x) 
Returns the cotangent of x 
SIN(x) 
Returns the sine of x 
TAN(x) 
Returns the tangent of x 
Geometric Function Support
Function  Description 

DISTANCE_IN_METERS(fromLon, fromLat,
toLon, toLat) 
Calculates distance in meters between two WGS84 positions. 
CONV_4326_900913_X(x) 
Converts WGS84 latitude to WGS84 Web Mercator x coordinate. 
CONV_4326_900913_Y(y) 
Converts WGS84 longitude to WGS84 Web Mercator y coordinate. 
String Function Support
Function  Description 

CHAR_LENGTH(str) 
Returns the number of characters in a string 
LENGTH(str) 
Returns the length of a string in bytes 
PatternMatching Support
Name  Example  Description 

str LIKE pattern 
'ab' LIKE 'ab' 
Returns true if the string matches the pattern 
str NOT LIKE pattern 
'ab' NOT LIKE 'cd' 
Returns true if the string does not match the pattern 
str ILIKE pattern 
'AB' ILIKE 'ab' 
Caseinsensitive LIKE 
str REGEXP POSIX pattern 
'^[az]+r$' 
Lowercase string ending with r 
REGEXP_LIKE ( str , POSIX
pattern ) 
'^[hc]at' 
cat or hat 
Usage Notes
The following wildcard characters are supported by LIKE
and ILIKE
:
%
matches any number of characters, including zero characters._
matches exactly one character.
Date/Time Function Support
Function  Description 

DATE_TRUNC('date_part',
timestamp) 
Truncates the timestamp to the specified 'date_part'. 
EXTRACT('date_part' FROM
timestamp) 
Returns the specified 'date_part' from timestamp. 
INTERVAL count 'date_part' 
Adds or Subtracts count 'date_part' units from a timestamp. Examples: DATE ‘2008131’ + INTERVAL ‘1’ YEAR DATE ‘20080301’  INTERVAL ‘1’ DAY 
NOW() 
Returns the current timestamp. 
TIMESTAMPADD('date_part',
count, timestamp 
date) 
Adds an interval of count 'date_part' to timestamp or date and returns signed 'date_part' units in the provided timestamp or date form. 
TIMESTAMPDIFF('date_part',
timestamp1, timestamp2) 
Subtracts timestamp1 from timestamp2 and returns the result in signed 'date_part' units. 
DATEDIFF('date_part',
timestamp, timestamp) 
returns the difference between two timestamps, calculated to the lowest level of the 'date_part' you specify. For example, if you set the 'date_part' as day, only the year, month, and day are used to calculate the result. Other fields, such as hour and minute, are ignored. 
DATEADD('date_part',
interval, timestamp) 
returns a date after a specified time/date interval has been added. 
DATEPART(interval,
timestamp) 
Returns a specified part of a given date as an integer value. 
Usage Notes
The 'date_part'
argument must be enclosed in single quotes. For example, in the following query, the 'DAY'
argument is in single quotes:
SELECT DATEDIFF('DAY', NOW(),plane_issue_date) from flights_2008_7M;
The interval
argument does not require quotes, but the numeric value associated with the interval must be enclosed in single quotes. For example, if setting the interval to one day, the number 1 must be in single quotes:
SELECT INTERVAL '1' DAY FROM flights_2008_7M LIMIT 1;
Supported 'date_part' types:
DATE_TRUNC [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND,
MILLENNIUM, CENTURY, DECADE, WEEK, QUARTERDAY]
EXTRACT [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND,
DOW, ISODOW, DOY, EPOCH, QUARTERDAY, WEEK]
DATEDIFF [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND,
MILLENNIUM, CENTURY, DECADE, WEEK, QUARTERDAY]
Supported interval types:
DATEADD [YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR,
MINUTE, SECOND, MILLISECOND]
DATEPART [YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR,
MINUTE, SECOND, MILLISECOND]
Accepted Date, Time, and Timestamp Formats
Datatype  Formats  Examples 

DATE  YYYYMMDD  20131031 
DATE  MM/DD/YYYY  10/31/2013 
DATE  DDMONYY  31Oct13 
DATE  DD/Mon/YYYY  31/Oct/2013 
TIME  HH:MM  23:49 
TIME  HHMMSS  234901 
TIME  HH:MM:SS  23:49:01 
TIMESTAMP  DATE TIME  31Oct13 23:49:01 
TIMESTAMP  DATETTIME  31Oct13T23:49:01 
TIMESTAMP  DATE:TIME  11/31/2013:234901 
TIMESTAMP  DATE TIME ZONE  31Oct13 11:30:25 0800 
TIMESTAMP  DATE HH.MM.SS PM  31Oct13 11.30.25pm 
TIMESTAMP  DATE HH:MM:SS PM  31Oct13 11:30:25pm 
TIMESTAMP  1383262225 
Usage Notes
 For twodigit years, years 6999 are assumed to be previous century (for example, 1969), and 068 are assumed to be current century (for example, 2016).
 For fourdigit years, negative years (BC) are not supported.
 Hours are expressed in 24hour format.
 When time components are separated by colons, you can write them as one or two digits.
 Months are case insensitive. You can spell them out or abbreviate to three characters.
 For timestamps, decimal seconds are ignored. Time zone offsets are written as +/HHMM.
 For timestamps, a numeric string is converted to +/ seconds since January 1, 1970.
 On output, dates are formatted as YYYYMMDD. Times are formatted as HH:MM:SS.
Aggregate Function Support
Function  Description 

AVG(x) 
Returns the average value of x 
COUNT() 
Returns the count of the number of rows returned 
COUNT(DISTINCT x) 
Returns the count of distinct values of x 
APPROX_COUNT_DISTINCT(x, e) 
Returns the approximate count of distinct values of x with defined expected error rate e 
MAX(x) 
Returns the maximum value of x 
MIN(x) 
Returns the minimum value of x 
SUM(x) 
Returns the sum of the values of x 
LAST_SAMPLE(x) 
Returns one sample value from aggregated column x. For example, the following query returns population grouped by city:
Select city, LAST_SAMPLE(state), sum (population) from census_table group by city 
Usage Notes
COUNT(DISTINCT x)
, especially when used in conjunction with GROUP BY, can require a very large amount of memory to keep track of all distinct values in large tables with large cardinalities. To avoid this large overhead, use APPROX_COUNT_DISTINCT.APPROX_COUNT_DISTINCT(x, e)
gives an approximate count of the value x, based on an expected error rate defined in e. The error rate is an integer value from 1 to 100. The lower the value of e, the higher the precision, and the higher the memory cost. Select a value for e based on the level of precision required. On large tables with large cardinalities, consider usingAPPROX_COUNT_DISTINCT
when possible to preserve memory.Set the default error rate using the hllprecisionbits configuration paramenter.
Statistical Function Support
Both doubleprecision (standard) and singleprecision floating point functions are provided. Singleprecision functions run faster on GPUs but might cause overflow errors.
Doubleprecision FP Function  Singleprecision FP Function  Description 

CORRELATION(x, y) 
CORRELATION_FLOAT(x, y) 
Alias of CORR. Returns the coefficient of correlation of a set of number pairs. 
CORR(x, y) 
CORR_FLOAT(x, y) 
Returns the coefficient of correlation of a set of number pairs. 
COVAR_POP(x, y) 
COVAR_POP_FLOAT(x, y) 
Returns the population covariance of a set of number pairs. 
COVAR_SAMP(x, y) 
COVAR_SAMP_FLOAT(x, y) 
Returns the sample covariance of a set of number pairs. 
STDDEV(x) 
STDDEV_FLOAT(x) 
Alias of STDDEV_SAMP. Returns sample standard deviation of the value. 
STDDEV_POP(x) 
STDDEV_POP_FLOAT(x) 
Returns the population standard the standard deviation of the value. 
STDDEV_SAMP(x) 
STDDEV_SAMP_FLOAT(x) 
Returns the sample standard deviation of the value. 
VARIANCE(x) 
VARIANCE_FLOAT(x) 
Alias of VAR_SAMP. Returns the sample variance of the value. 
VAR_POP(x) 
VAR_POP_FLOAT(x) 
Returns the population variance sample variance of the value. 
VAR_SAMP(x) 
VAR_SAMP_FLOAT(x) 
Returns the sample variance of the value. 
Conditional Expression Support
Expression  Description 

CASE WHEN condition THEN result
ELSE default

Case operator 
COALESCE(val1, val2, ..) 
Returns the first nonnull value in the list 
Subquery Expression Support
Expression  Description 

expr IN (subquery or list of
values) 
Evaluates whether expr equals any value of the IN list. 
expr NOT IN (subquery or list
of values) 
Evaluates whether expr does not equal any value of the IN list. 
Usage Notes
 You can use a subquery anywhere an expression can be used, subject to any runtime constraints of that expression. For example, a subquery in a CASE statement must return exactly one row, but a subquery can return multiple values to an IN expression.
 You can use a subquery anywhere a table is allowed (for example, FROM subquery), using aliases to name any reference to the table and columns returned by the subquery.
Type Cast Support
Expression  Example  Description 

CAST(expr AS
type ) 
CAST(1.25 AS FLOAT) 
Converts an expression to another data type 
Array Support
Expression  Description 

SELECT <ArrayCol>[n] ... 
Query array elements n of column ArrayCol . 
UNNEST(<ArrayCol>) ... 
Expand the array ArrayCol to a set of rows. 
SELECT <some_column>
FROM <your_table>
WHERE <test> = ANY <array_column>

ANY compares a scalar value with a single set of values (in the text array <array_column>), and returns TRUE when the result contains at least one item. ANY must be preceded by a comparison operator. 
SELECT <some_column>
FROM <your_table>
WHERE <test> = ALL <array_column>

ALL compares a scalar value with a single set of values (in the text array <array_column>), and returns TRUE when the result specified is TRUE for all items in the array. ALL must be preceded by a comparison operator. 
ARRAYINDEX(row_index) 
Returns a value from a specific location in an array. 
Note: As with many SQLbased services, MapD array indexes are 1based.
LIKELY/UNLIKELY
Expression  Description 

LIKELY(X) 
Provides a hint to the query planner that argument X is a Boolean value that is usually true. The planner can prioritize filters on the value X earlier in the execution cycle and return results more efficiently. 
UNLIKELY(X) 
Provides a hint to the query planner that argument X is a Boolean value that is usually not true. The planner can prioritize filters on the value X later in the execution cycle and return results more efficiently. 
Usage Notes
SQL normally assumes that terms in the WHERE clause that cannot be used by indices are usually true. If this assumption is incorrect, it could lead to a suboptimal query plan. Use the LIKELY(X) and UNLIKELY(X) SQL functions to provide hints to the query planner about clause terms that are probably not true, which helps the query planner to select the best possible plan.