Created 01-12-2016 06:20 PM
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
Created 01-12-2016 07:09 PM
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.
Created 01-12-2016 06:34 PM
There is very high probablity that it's syntax issue.
Created 01-12-2016 07:20 PM
If Syntax issue, Then how it is working in Hue??
Created 01-12-2016 07:23 PM
Some kind of formatting while posting the query in beeline. Try with hive
hive -f /home/my/hive-script.sql
Created 01-12-2016 07:09 PM
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.