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 single-row 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', '2015-05-11 211720`);

SELECT

[ WITH <alias> AS <query>,... ]
SELECT [ALL|DISTINCT] <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 non-null values by default in an ascending sort, before non-null values in a descending sort. For any query, you can use NULLS FIRST to sort null values to the top of the results or NULLS 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 [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ 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 high-level 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.

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 from-list, 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

Comparison Operator Support

Mathematical Function Support

Trigonometric Function Support

Geometric Function Support

String Function Support

Pattern-Matching Support

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

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, QUARTERDAY]

Supported interval types:

DATEADD     [YEAR, QUARTER, MONTH, DAY, WEEKDAY, HOUR,
             MINUTE, SECOND, MILLISECOND]
DATEPART    [YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, HOUR,
             MINUTE, SECOND, MILLISECOND]

Accepted Date, Time, and Timestamp Formats

Usage Notes

• For two-digit years, years 69-99 are assumed to be previous century (for example, 1969), and 0-68 are assumed to be current century (for example, 2016).
• For four-digit years, negative years (BC) are not supported.
• Hours are expressed in 24-hour 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 YYYY-MM-DD. Times are formatted as HH:MM:SS.

Aggregate Function Support

Select city, 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 using APPROX_COUNT_DISTINCT when possible to preserve memory.

  Set the default error rate using the -hll-precision-bits configuration paramenter.

• Currently, MapD does not support grouping by non-dictionary-encoded strings. However, with the SAMPLE aggregate function, you can select non-dictionary-encoded strings that are presumed to be unique in a group. For example:

  SELECT user_name, SAMPLE(user_description) FROM tweets GROUP BY user_name;

  If the aggregated column (user_description in the example above) is not unique within a group, SAMPLE selects a value that might be nondeterministic because of the parallel nature of MapD query execution.

Conditional Expression Support

Subquery Expression Support

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

Array Support

Note: As with many SQL-based services, MapD array indexes are 1-based.

LIKELY/UNLIKELY

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.