Skip to main content
Skip table of contents

SQL in general

The general syntax of the HIVE 2.


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

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


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.


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:

LetterDate or Time ComponentExamples
GEra designatorAD
yYear1996; 96
YWeek year2009; 09
MMonth in yearJuly; Jul; 07
wWeek in year27
WWeek in month2
DDay in year189
dDay in month10
FDay of week in month2
EDay name in weekTuesday; Tue
uDay number of week (1 = Monday, ..., 7 = Sunday)1
aAm/pm markerPM
HHour in day (0-23)0
kHour in day (1-24)24
KHour in am/pm (0-11)0
hHour in am/pm (1-12)12
mMinute in hour30
sSecond in minute55
zTime zonePacific Standard Time; PST; GMT-08:00
ZTime zone-0800
XTime zone-08; -0800; -08:00
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.