EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

  5. Supported Apache Hive Functions

Supported Apache Hive Functions

Community Admin

• Strategy

This article summarizes the Apache Hive functions that are supported by functions in MicroStrategy.

MicroStrategy pushes the calculations for the following list of built-in basic functions down to Impala and Hive using the associated SQL patterns.
 

Basic Function 

MicroStrategy pushes the calculations for the following list of built-in basic functions down to Hive using the associated SQL patterns.

MicroStrategy Function Name

Hive 0.13.x and Hive 0.14.x

StdevPFunction

stddev_pop(#0#< #*#>)   

BetweenOperator

#0 BETWEEN #1 and #2   

StdevFunction

STDDEV_SAMP(#0)  

VarFunction

VAR_SAMP(#0#< #*#>) 

GeoMeanFunction

EXP(AVG(LN(#0))) 

GreatestFunction

MAX(#0) 

LeastFunction

MIN(#0)  

FirstFunction

MIN(#0)

LastFunction

MAX(#0)

IFOperator

(Case when #0 then #1 else #2 end)

VarPFunction

VAR_POP(#0#< #*#>) 

 

Date and Time Function (100.001.028)

MicroStrategy supports the following date and time functions.

MicroStrategy Function Name

Hive 0.13.x and Hive 0.14.x

DaysBetweenFunction

DATEDIFF(#1,#0)

CurrentDateFunction

CURRENT_DATE

DayOfMonthFunction

TO_NUMBER(TO_CHAR(#0, 'DD'))

WeekFunction

TO_NUMBER(TO_CHAR(#0, 'WW') ,'99')

MonthFunction

MONTH(#0)

YearFunction

YEAR(#0)

CurrentDateTimeFunction

FROM_UNIXTIME(UNIX_TIMESTAMP())

CurrentTimeFunction

CURRENT_TIME

HourFunction

TO_NUMBER(TO_CHAR(#0, 'HH24'))

MinuteFunction

TO_NUMBER(TO_CHAR(#0, 'MI')) 

SecondFunction

TO_NUMBER(TO_CHAR(#0, 'SS'))

AddDaysFunction

(#0 + #1)

DateFunction

TO_DATE(#0,yyyy-MM-dd HH:mm:ss)

 
 

OLAP Function

MicroStrategy pushes the calculations for the following list of built-in OLAP functions down to Hive using the associated SQL patterns.

MicroStrategy Function Name

Hive 0.13.x and Hive 0.14.x

FirstInRangeFunction

(FIRST_VALUE(#0) WITHIN GROUP(ORDER BY #1 ASC)

LastInRangeFunction

LAST_VALUE(#0) WITHIN GROUP(ORDER BY #1 ASC)

LagFunction

lag(#0#<, #*#>) over ([#P] [#O])

LeadFunction

lead(#0#<, #*#>) over ([#P] [#O])

OLAPRankFunction

rank() over ([#P] [#O])

 

OLAP Functions 

OLAP functions are a powerful family of functions for business intelligence and data warehousing applications. These functions are collectively called “ordered analytical functions” in Hive, and they provide significant analytical capabilities for many business analysis queries.
 
MicroStrategy’s OLAP functions are calculated via SQL in Hive rather than in the MicroStrategy analytical engine. There is no VLDB setting associated with this option.


SQL Syntax for Ordered Analytic Functions:
Image
Moving average example:


select a11.DAY_DATE DAY_DATE,


a12.REGION_ID REGION_ID,


max(a13.REGION_NAME) REGION_NAME,


sum(a11.TOT_DOLLAR_SALES) WJXBFS1,


avg(sum(a11.TOT_DOLLAR_SALES)) over(partition by a12.REGION_ID order by

sum(a11.TOT_DOLLAR_SALES) asc rows 2 preceding) WJXBFS1


from DAY_CTR_SLS a11 j


oin LU_CALL_CTR a12


on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)


join LU_REGION a13 on (a12.REGION_ID = a13.REGION_ID)


group by a11.DAY_DATE, a12.REGION_ID



 
Ranking: The RANK function is closely related to other ordered analytical functions. Key distinctions of the RANK function are that it requires an order by clause and does not support a window clause. The MicroStrategy Engine is aware that Hive supports the Rank function, so the ranking will be done by the database rather than the MicroStrategy Analytical Engine.


select a13.REGION_ID REGION_ID, max(a14.REGION_NAME) REGION_NAME,


a11.EMP_ID EMP_ID,


max(a12.EMP_LAST_NAME) EMP_LAST_NAME,


max(a12.EMP_FIRST_NAME) EMP_FIRST_NAME,


sum(a11.TOT_DOLLAR_SALES) WJXBFS1,


rank () over( order by sum(a11.TOT_DOLLAR_SALES) desc nulls last) WJXBFS1


from ITEM_EMP_SLS a11 join LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)


join LU_CALL_CTR a13 on (a12.CALL_CTR_ID = a13.CALL_CTR_ID) join LU_REGION a14


on (a13.REGION_ID = a14.REGION_ID)


group by a13.REGION_ID, a11.EMP_ID



 

Math Function

MicroStrategy pushes the calculations for the following list of built-in Math functions down to Hive using the associated SQL patterns.
 

MicroStrategy Function Name

Hive 0.13.x and Hive 0.14.x

Round2Function

ROUND(#0, #1)

RankFunction

#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<,#*#> order by #0)

AbsFunction

ABS(#0)  

AcosFunction

ACOS(#0)   

AcoshFunction

LN(#0+SQRT(#0-1)*SQRT(#0+1))

AsinFunction

ASIN(#0)

AtanFunction

ATAN(#0)

AtanhFunction

((LN(1+#0)-LN(1- #0))/2)

CeilingFunction

CEIL(#0)

CosFunction

(EXP(#0)+EXP(-#0))/2

CoshFunction

(EXP(#0)+EXP(-#0))/2 

DegreesFunction

DEGREES(#0)

ExpFunction

EXP(#0)

FloorFunction

FLOOR(#0)

LnFunction

LN(#0)

LogFunction

LOG(#1, #0)

Log10Function

LOG10(#0)

PowerFunction

POWER(#0, #1)

QuotientFunction

TRUNC((#0)/(CASE WHEN (#1)=0 THEN NULL ELSE (#1) END), 0)

RadiansFunction

RADIANS(#0)

RoundFunction

ROUND(#0)

SinFunction

SIN(#0)

SinhFunction

((EXP(#0)-EXP(#0*(-1)))/2)

SqrtFunction

SQRT(#0)

TanFunction

TAN(#0)

TanhFunction

(-1+(2/(1+EXP(-2*#0))))

TruncFunction

TRUNC(#0)

AsinhFunction

LN(#0+SQRT(POW(#0, 2)+1))

ModFunction

SIGN(#0)*PMOD(ABS(#0), #1) 




 

Statistical Functions

MicroStrategy pushes the calculations for the following list of built-in statistical functions down to Hive using the associated SQL patterns.
 

MicroStrategy Function Name

Hive 0.13.x and Hive 0.14.x

StandardizeFunction

case when (#2 > 0) then (#0 - #1)/(#2) else NULL end

CovarianceFunction

covar_pop(#0, #1)

 

String Functions (100.001.0xx )

MicroStrategy pushes the calculations for the following list of built-in string functions down to Hive using the associated SQL patterns.
 

MicroStrategy Function Name

Hive 0.13.x and Hive 0.14.x

ConcatFunction

(#0#< || #*#>)

LengthFunction

LENGTH(#0) 

LowerFunction

LOWER(#0)

LTrimFunction

LTRIM(#0)

PositionFunction

INSTR(#1, #0)

RTrimFunction

RTRIM(#0)

SubStrFunction

SUBSTRING(#0, #1, #2)

InitCapFunction

(UPPER(SUBSTR(#0, 1, 1)) + LOWER(SUBSTR(#0, 2, LENGTH(#0) - 1)))

TrimFunction

TRIM(#0)

RightStrFunction

SUBSTR(#0, (LENGTH(#0) - #1 + 1))

LeftStrFunction

SUBSTR(#0, 1, #1)

UpperFunction

UPPER(#0)

ConcatBlankFunction

(#0#< || ' ' || #*#>)

 
 
Comment

0 comments

Details

Knowledge Article

Published:

January 31, 2024

Last Updated:

January 21, 2026