Support Questions

Find answers, ask questions, and share your expertise

Beeline CLI fails compiling order by while Hue UI runs the query successfully

avatar
Rising Star

Hi,

If I run below query in Hue UI, it runs successful, but in beeline CLI it fails saying missing 'EOF'

use DB_xxx;
SET hive.execution.engine=tez;
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled= true;
SET hive.cbo.enable=true;
SET hive.stats.fetch.partition.stats=true;
SET hive.auto.convert.join=true;
SET hive.auto.convert.join.noconditionaltask.size=25000000;
SET hive.compute.query.using.stats=true;
SET hive.stats.fetch.column.stats =true;
insert into table db_xgl77sar.orc_claiminfo
select 
CONCAT(CLAIMNUMBER,'-',EXP_NUMBER) AS EXP_ID,
Claimant_Age,
Case 
when Claimant_Age >=0 and Claimant_Age < 3 then  'Infants'
when Claimant_Age >=3 and Claimant_Age < 8 then   'Preschoolers'
when Claimant_Age >=8 and Claimant_Age < 15 then   'General Schoolchildren'
when Claimant_Age >=15 and Claimant_Age < 25 then  'Teens'
when Claimant_Age >=25 and Claimant_Age < 40 then   'Young People'
when Claimant_Age >=40 and Claimant_Age < 60 then   'Mature Age People'
when Claimant_Age >=60 and Claimant_Age < 80 then   'Older People'
when Claimant_Age >=80 then  'Long-lived'
else  'N/A' END AS Claimant_Age_Group,
DATEDIFF(to_date(EXP_ORIGINAL_CLOSE_DATE),to_date(CLAIM_REPORT_DATE)) AS Rpt2Clsd,
case 
when TOTAL_LOSS_ID is  null then '0'
else '1'
end AS TOTAL_LOSS,
Claimnumber, 
INCIDENT, 
EXP_NUMBER,
Claim_Report_Date,   
to_date(Date_of_Loss) As Date_of_Loss,   
Claim_Close_Date,
EXP_ORIGINAL_CLOSE_DATE,   
EXP_CLOSEDATE,   
EXP_REOPENDATE,  
EXP_REOPENREASON, 
Description,   
Insured_Lability, 
ACCIDENT_TYPE,   
FAULT_RATING,   
LOSS_CAUSE,   
LOSS_TYPE,   
LINE_OF_BUSINESS,   
JurisdictionState, 
LOSS_STATE,
POLICY_STATE,
SIU_STATUS,
CLAIMANT_NAME,
TO_DATE(CLAIMANT_DOB) as CLAIMANT_DOB,
CLAIMANT_GENDER,
CLAIMANT_MARITAL_STATUS,
CLAIMANT_OCCUPATION,
EXP_STATUS,   
EXP_TYPE, 
EXP_Coverage,
COMPENSABILITYCOMMENTS,
HitandRun,
TOTAL_LOSS_ID,
VEHICLE_STYLE,
Ambulance_Used,
Death_Date,
DisabledDueToAccident,
General_Injury_Type,
Impairment,
InjuredCheck_AAA,
Detailed_Body_Part,
Detailed_Injury_Type,
Pre_Exist_Cond,
case 
when LOSS_STATE=POLICY_STATE then '1'
else '0'
end PSEQLS,
Date_of_Loss AS DOL_DAY,
case 
when EXP_REOPENDATE is  null then '0'
else '1'
end AS Reopened,
case 
when Death_Date is null then '0'
else '1' 
end AS DEATH,
from_unixtime(unix_timestamp()) AS RUN_DATE
from (SELECT C.Claimnumber,
  C.IncidentReport                AS INCIDENT,
  E.claimorder                    AS EXP_NUMBER,
  to_date(C.createtime)           AS Claim_Report_Date,
  to_date(C.lossdate)             AS Date_of_Loss,
  to_date(C.closedate)            AS Claim_Close_Date,
  to_date(E.INITIALCLOSEDATE_AAA) AS EXP_ORIGINAL_CLOSE_DATE,
  to_date(E.CLOSEDATE)            AS EXP_CLOSEDATE,
  to_date(E.REOPENDATE)           AS EXP_REOPENDATE,
  E.ReopenedReason                AS EXP_REOPENREASON,
  C.Description,
  C.fault                                                                          AS Insured_Lability,
  C.LOSSCAUSE                                                                      AS ACCIDENT_TYPE,
  C.FAULTRATING                                                                    AS FAULT_RATING,
  C.LOSSCAUSE                                                                      AS LOSS_CAUSE,
  C.LOSSTYPE                                                                       AS LOSS_TYPE,
  C.LOBCODE                                                                        AS LINE_OF_BUSINESS,
  E.JURISDICTIONSTATE                                                              AS JurisdictionState,
  ADR.STATE                                                                        AS LOSS_STATE,
  P.POLICYSTATE_AAA                                                                AS POLICY_STATE,
  C.SIUSTATUS                                                                      AS SIU_STATUS,
  CONCAT(CT.FIRSTNAME, ' ', CT.LASTNAME )                                          AS CLAIMANT_NAME,
  to_date(CT.DateOfBirth)                                                           AS CLAIMANT_DOB,
  DATEDIFF (to_date(FROM_UNIXTIME( UNIX_TIMESTAMP())),to_date(CT.DateOfBirth))/365 AS Claimant_Age,
  CT.Gender                                                                        AS CLAIMANT_GENDER,
  CT.MaritalStatus                                                                 AS CLAIMANT_MARITAL_STATUS,
  CT.OCCUPATION                                                                    AS CLAIMANT_OCCUPATION,
  E.STATE                                                                          AS EXP_STATUS,
  E.EXPOSURETYPE                                                                   AS EXP_TYPE,
  E.COVERAGESUBTYPE                                                                AS EXP_Coverage,
  BP.COMPENSABILITYCOMMENTS,
  I.HitandRun,
  I.ID                    AS TOTAL_LOSS_ID,
  V.STYLE                 AS VEHICLE_STYLE,
  I.AmbulanceUsed         AS Ambulance_Used,
  I.DeathDate_AAA         AS Death_Date,
  I.DisabledDueToAccident AS DisabledDueToAccident,
  GeneralInjuryType       AS General_Injury_Type,
  I.Impairment,
  I.InjuredCheck_AAA,
  BP.DetailedBodyPart  AS Detailed_Body_Part,
  BP.InjuryType_AAA    AS Detailed_Injury_Type,
  BP.Pre_ExistCond_AAA AS Pre_Exist_Cond 
FROM
(SELECT id,Claimnumber,IncidentReport,createtime,lossdate,closedate,Description,fault,LOSSCAUSE,FAULTRATING,LOSSTYPE,LOBCODE,SIUSTATUS,LOSSLOCATIONID,POLICYID FROM CC_Claim WHERE RETIRED = 0) C
INNER JOIN (SELECT claimorder,INITIALCLOSEDATE_AAA,CLOSEDATE,REOPENDATE,ReopenedReason,JURISDICTIONSTATE,STATE,EXPOSURETYPE,COVERAGESUBTYPE,CLAIMID,INCIDENTID,ClaimantDenormID FROM CC_Exposure WHERE RETIRED = 0) E
ON C.ID = E.CLAIMID
LEFT JOIN CC_Incident I
ON E.INCIDENTID = I.ID
LEFT JOIN cc_bodypart BP
ON I.ID = BP.INCIDENTID
LEFT JOIN cc_contact CT
ON E.ClaimantDenormID = CT.ID
LEFT JOIN CC_ADDRESS ADR
ON C.LOSSLOCATIONID = ADR.ID
LEFT JOIN CC_VEHICLE V
ON I.VEHICLEID = V.ID
LEFT JOIN (SELECT id,POLICYSTATE_AAA FROM CC_POLICY WHERE RETIRED = 0 ) P
ON C.POLICYID = P.ID
WHERE C.claimnumber LIKE '1000-%'
AND C.incidentreport    <> 1
AND C.lobcode            = '10001'
AND C.losscause NOT     IN ('10010', '10049')
AND upper(C.Description) not like'%TEST%'
ORDER BY C.CLAIMNUMBER ASC
) clminfo;

What could be the possible issue??

Thanks

Mamta

1 ACCEPTED SOLUTION

avatar
Contributor

Digging through your SQL for some syntactical things that might possibly throw off beeline differently than the Hive UI. No promises, but a couple of things to check.

Always use "AS" for column aliases. You're missing an "AS" here:

case
when LOSS_STATE=POLICY_STATE then '1'
else '0'
end PSEQLS,

If you're selecting from more than one table, always use the table alias in your select. You're missing one here:

  GeneralInjuryType       AS General_Injury_Type,

Let us know what you find. You might also try running your query through the hive CLI, too. Or running beeline in verbose mode.

View solution in original post

4 REPLIES 4

avatar
Master Mentor
@Mamta Chawla

There is very high probablity that it's syntax issue.

avatar
Rising Star

If Syntax issue, Then how it is working in Hue??

avatar
Master Mentor
@Mamta Chawla

Some kind of formatting while posting the query in beeline. Try with hive

hive -f /home/my/hive-script.sql

avatar
Contributor

Digging through your SQL for some syntactical things that might possibly throw off beeline differently than the Hive UI. No promises, but a couple of things to check.

Always use "AS" for column aliases. You're missing an "AS" here:

case
when LOSS_STATE=POLICY_STATE then '1'
else '0'
end PSEQLS,

If you're selecting from more than one table, always use the table alias in your select. You're missing one here:

  GeneralInjuryType       AS General_Injury_Type,

Let us know what you find. You might also try running your query through the hive CLI, too. Or running beeline in verbose mode.