Versions Compared

Old Version 13

changes.mady.by.user Charles Bishop

Saved on

compared with

New Version 14

changes.mady.by.user Charles Bishop

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Note

SQL worksheets are available as of Datameer 7.2

...

SQL worksheets give you the ability to write SQL queries directly in a workbook. Use SQL worksheets to combine processes that might take several steps using the traditional interface.

Using SQL Worksheets

Open a SQL worksheet

Open a workbook, select Sheets from the menu bar and select Add SQL Sheet.

A new worksheet is added to your workbook with a multiline SQL editor instead of a formula editor.

...

The Datameer SQL editor interface emulates structured query language. The following SQL features are available for use within a Datameer SQL worksheet:

  • Base SQL 99 operators
  • (=, ⇔, ==, <>, <, >, >=, <=, etc)
  • Logical operators
    • AND
    • OR
    • NOT
    • NOT NULL
    • BETWEEN
  • Timestamp

    • String

    LIKE , SIMILAR , COALESCE , NVL
    TitleSQL command
    SQL selection
    • SELECT <column> FROM
    SQL sub-selection
    • SELECT <column> FROM ( SELECT a + b AS <column> from t1) t2
    • CASE
    Sorting & Aggegration
    • SELECT
    • GROUP BY
    • ORDER BY
    • COUNT
    • COUNT DISTINCT
    • SUM
    • MIN
    • MAX
    • AVG
    Filters
    • WHERE
    • HAVING
    • LIMIT
    Joins
    • INNER JOIN
    • (LEFT, RIGHT, FULL) OUTER JOIN
    • LEFT SEMI JOIN
    • UNION (ALL)
    • INTERSECT
    • EXCEPT
    Relationship operators
    Logic functions
    • AND
    • OR
    Comparison functions

    • value1 = value2

    • value1 <> value2

    • value1 > value2

    • value1 >= value2

    • value1 < value2

    • value1 <= value2

    • value IS NULL

    • value IS NOT NULL

    • value1 IS DISTINCT FROM value2

    • value1 IS NOT DISTINCT FROM value2

    • value1 BETWEEN value2 AND value3

    • value1 NOT BETWEEN value2 AND value3

    • string1 LIKE string2 [ ESCAPE string3 ]

    • string1 NOT LIKE string2 [ ESCAPE string3 ]

    • string1 SIMILAR TO string2 [ ESCAPE string3 ]

    • string1 NOT SIMILAR TO string2 [ ESCAPE string3 ]

    • value IN (value [, value]*)

    • value NOT IN (value [, value]*)

    • value IN (sub-query)

    • value NOT IN (sub-query)

    • EXISTS (sub-query)

    • NOT EXISTS (sub-query)

    SQL logic

    • boolean1 OR boolean2

    • boolean1 AND boolean2

    • NOT boolean

    • boolean IS FALSE

    • boolean IS TRUE

    • boolean IS NOT TRUE

    • boolean IS UNKNOWN

    • boolean IS NOT UNKNOWN

    Numeric functions

    • + numeric

    • - numeric

    • numeric1 + numeric2

    • numeric1 - numeric2

    • numeric1 * numeric2

    • numeric1 / numeric2

    • POWER(numeric1, numeric2)

    • ABS(numeric)

    • SQRT(numeric)

    • LN(numeric)

    • LOG10(numeric)

    • EXP(numeric)

    • RAND([seed])

    • ACOS(numeric)

    • ASIN(numeric)

    • ATAN(numeric)

    • ATAN2(numeric, numeric)

    • COS(numeric)

    • COT(numeric)

    • DEGREES(numeric)

    • PI

    • RADIANS(numeric)

    • SIN(numeric)

    • TAN(numeric)

    String functions

    • string || string

    • CHAR_LENGTH(string)

    • CHARACTER_LENGTH(string)

    • UPPER(string)

    • LOWER(string)

    • POSITION(string1 IN string2)

    • REPLACE (string_expression, string_pattern, string_replacement)

    • TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)

    • SUBSTRING(string FROM integer)

    • SUBSTRING(string FROM integer FOR integer)

    Date/Time/Timestamp functions

    • LOCALTIME

    • LOCALTIME(precision)

    • LOCALTIMESTAMP

    • LOCALTIMESTAMP(precision)

    • CURRENT_DATE

    • EXTRACT(timeUnit FROM datetime)

    Case functions
    • CASE value
    • CASE
    • NULLIF(value, value)
    • COALESCE(value, value [, value ]*)
    Aggregation functions
    • COUNT( [ ALL | DISTINCT ] value [, value ]*)
    • COUNT(*)
    • APPROX_COUNT_DISTINCT(value [, value ]*)
    • AVG( [ ALL | DISTINCT ] numeric)
    • SUM( [ ALL | DISTINCT ] numeric)
    • MAX( [ ALL | DISTINCT ] value)
    • MIN( [ ALL | DISTINCT ] value)
    • STDDEV_POP( [ ALL | DISTINCT ] numeric)
    • STDDEV_SAMP( [ ALL | DISTINCT ] numeric)
    • VAR_POP( [ ALL | DISTINCT ] value)
    • VAR_SAMP( [ ALL | DISTINCT ] numeric)
    Set clauses

    • UNION

    • UNION ALL

    • GROUP BY

    • ORDER BY

    • HAVING

    • INTERSECT

    • EXCEPT

    • LIMIT

    • CAST

    Join clauses
    • INNER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN
    • INNER JOIN (sub-query)
    • LEFT OUTER JOIN (sub-query)
    • RIGHT OUTER JOIN (sub-query)
    • FULL OUTER JOIN (sub-query)


    Data types mapping

    DatameerSQL
    datetimestamp
    integerbig integers
    big decimaldecimal
    big intigerdecimal
    floatsdoubles
    strings varchars
    booleanboolean

    Anchor
    reserved
    reserved

    ...

    Using  reserved SQL

    ...

    keywords 

    There are reserved  reserved keywords that SQL uses for  for defining, manipulating, and accessing databases. 

    ...

    To reference a column in an SQL sheet that has the same name as a reserved SQL keyword, the reference column name must be escaped using backticks (`) around the word. 

    Example

    ...

    SQL sheet callResponse
    SELECT salesInfo.DateError
    SELECT salesInfo.`Date`Success

    ...

    • Create Table, Delete Table, and Modify Table structure / DDL aren't currently supported. 
    • Window functions aren't currently supported.
    • Using Hive UDFs /UDAs in the SQL editor isn't currently supported.
    • Using all the Datameer functions in the SQL editor isn't currently supported.
    • Datameer's s LIST data type isn isn't supported for SQL worksheets.

    ...