SQL in general ​

The general syntax of the HIVE 2.

SELECT ​

The general syntax of the HIVE 2 SELECT command is the following:

text
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM my_table
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [LIMIT rows]

ORDER BY ​

Indexima adds an additional feature to the ORDER BY with ORDER BY columns NULLS FIRST / LAST.

When NULLS FIRST, all NULL values will be listed on top of the query's result.

When NULLS LAST, all NULL values will be listed at the bottom of the query's result.

The default NULL sorting order for ASC order is NULLS FIRST, while the default NULL sorting order for DESC order is NULLS LAST.

DATE_FORMAT ​

String DATE_FORMAT(DATE data, string format).

Returns a formatted element of the date:

  • DATE_FORMAT(‘2008-12-20 12:13:14’, ‘u’) returns the day number of the week => 6 = Saturday
  • DATE_FORMAT(‘2008-12-20’, ‘y’) returns the year => "2018"
  • DATE_FORMAT(1463313304, ‘m’)“) returns minutes => "28".

Here the DATE is represented by a DOUBLE and corresponds to the date "Jan 17, 1970 11:28:33 PM".

Formats supported by Indexima are listed here:

Letter

Date or Time Component

Examples

G

Era designator

AD

y

Year

1996; 96

Y

Week year

2009; 09

M

Month in year

July; Jul; 07

w

Week in year

27

W

Week in month

2

D

Day in year

189

d

Day in month

10

F

Day of week in month

2

E

Day name in week

Tuesday; Tue

u

Day number of week (1 = Monday, ..., 7 = Sunday)

1

a

Am/pm marker

PM

H

Hour in day (0-23)

0

k

Hour in day (1-24)

24

K

Hour in am/pm (0-11)

0

h

Hour in am/pm (1-12)

12

m

Minute in hour

30

s

Second in minute

55

S

Millisecond

978

z

Time zone

Pacific Standard Time; PST; GMT-08:00

Z

Time zone

-0800

X

Time zone

-08; -0800; -08:00