SELECT

The SELECT command returns a set of records from one or more tables.

1
query:
2
  |   WITH withItem [ , withItem ]* query
3
  |   {
4
          select
5
      }
6
      [ ORDER BY orderItem [, orderItem ]* ]
7
      [ LIMIT [ start, ] { count | ALL } ]
8
      [ OFFSET start { ROW | ROWS } ]
9
10
withItem:
11
      name
12
      [ '(' column [, column ]* ')' ]
13
      AS '(' query ')'
14
15
orderItem:
16
      expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
17
18
select:
19
      SELECT [ DISTINCT ] [/*+ hints */]
20
          { * | projectItem [, projectItem ]* }    
21
      FROM tableExpression
22
      [ WHERE booleanExpression ]
23
      [ GROUP BY { groupItem [, groupItem ]* } ]
24
      [ HAVING booleanExpression ]
25
      [ WINDOW window_name AS ( window_definition ) [, ...] ]
26
27
projectItem:
28
      expression [ [ AS ] columnAlias ]
29
  |   tableAlias . *
30
31
tableExpression:
32
      tableReference [, tableReference ]*
33
  |   tableExpression [ ( LEFT ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
34
35
joinCondition:
36
      ON booleanExpression
37
  |   USING '(' column [, column ]* ')'
38
39
tableReference:
40
      tablePrimary
41
      [ [ AS ] alias ]
42
43
tablePrimary:
44
      [ catalogName . ] tableName
45
  |   '(' query ')'
46
47
groupItem:
48
      expression
49
  |   '(' expression [, expression ]* ')'

For more information, see SELECT.

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.

Query Hints

HEAVY.AI provides various query hints for controlling the behavior of the query execution engine.

Syntax

1
SELECT /*+ hint */ FROM ...;

By default, a hint is applied to the query step in which it is defined. If you have multiple SELECT clauses and define a query hint in one of those clauses, the hint is applied only to the specific query step; the rest of the query steps are unaffected. For example, applying the /* cpu_mode */ hint affects only the SELECT clause in which it exists.

You can define a hint to apply to all query steps by prepending g_ to the query hint. For example, if you define /*+ g_cpu_mode */, CPU execution is applied to all query steps.

HEAVY.AI supports the following query hints.

Cross-Database Queries

In Release 6.4 and higher, you can run SELECT queries across tables in different databases on the same HEAVY.AI cluster without having to first connect to those databases. This enables more efficient storage and memory utilization by eliminating the need for table duplication across databases, and simplifies access to shared data and tables.

To execute queries against another database, you must have ACCESS privilege on that database, as well as SELECT privilege.

Example

Execute a join query involving a table in the current database and another table in the my_other_db database:

1
SELECT name, saleamt, saledate FROM my_other_db.customers AS c, sales AS s 
2
  WHERE c.id = s.customerid;