hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dyuti a <hadoop.hiv...@gmail.com>
Subject Re: Need urgent help in hive query
Date Tue, 02 Jul 2013 13:38:01 GMT
sure will check to that Robin


-Thanks


On Sat, Jun 29, 2013 at 1:11 AM, Robin Morris <rdm@baynote.com> wrote:

>  It probably won't make a difference to performance — the subqueries will
> be executed in separate map-reduce jobs, and the output put on disk anyway.
>
>
>  First, get it to work; second, optimize.
>
>  Robin
>
>   From: dyuti a <hadoop.hive04@gmail.com>
> Reply-To: "user@hive.apache.org" <user@hive.apache.org>
> Date: Friday, June 28, 2013 12:05 PM
> To: "user@hive.apache.org" <user@hive.apache.org>
> Subject: Re: Need urgent help in hive query
>
>   Hi Robin,
> Thanks for your reply. Hope this will help us but still looking into
> performance wise too....if no other option then have to go by this.
>
> Dti
>
>
> On Sat, Jun 29, 2013 at 12:03 AM, Robin Morris <rdm@baynote.com> wrote:
>
>>  Split up the query – put results of the subquery into a table, run the
>> final query on that, then drop the temporary table.
>>
>>  Robin
>>
>>   From: dyuti a <hadoop.hive04@gmail.com>
>> Reply-To: "user@hive.apache.org" <user@hive.apache.org>
>> Date: Friday, June 28, 2013 10:54 AM
>> To: "user@hive.apache.org" <user@hive.apache.org>, Michael Malak <
>> michaelmalak@yahoo.com>
>> Subject: Re: Fwd: Need urgent help in hive query
>>
>>   Hi Michael,
>> Thanks for your help, is there any other possible options apart from this.
>>
>>
>> On Fri, Jun 28, 2013 at 10:33 PM, Michael Malak <michaelmalak@yahoo.com>wrote:
>>
>>>  Just copy and paste the whole long expressions to their second
>>> occurrences.
>>>
>>>   ------------------------------
>>> *From:* dyuti a <hadoop.hive04@gmail.com>
>>> *To:* user@hive.apache.org
>>> *Sent:* Friday, June 28, 2013 10:58 AM
>>> *Subject:* Fwd: Need urgent help in hive query
>>>
>>>   Hi Experts,
>>>  I'm trying with the below SQL query in Hive, which does not support
>>> column alias access in subsequent columns as shown below in the query. Is
>>> there any other way to rewrite the same without using alias? any of your
>>> help are really appreciated.
>>>
>>> INSERT INTO CAUD
>>>    (
>>>      pst_cc
>>>   pst_no
>>>   pst_ber
>>>   pst_tkn
>>>   pst_dtm
>>>   pst_nbr
>>>   pst_cde
>>>   pst_dte
>>> )
>>> SELECT der.cc
>>>   der.no
>>>   der.ber
>>>   der.tkn
>>>   der.dtm
>>>   der.nbr
>>>   der.cde
>>>   der.dte
>>>
>>> FROM (SELECT udp.cc
>>>    udp.no
>>>    udp.ber
>>>    udp.tkn
>>>    ,CASE WHEN udp.SYSTEM_DTE>16000000 AND udp.SYSTEM_DTE<100000000 THEN
>>> udp.SYSTEM_DTE
>>>             WHEN udp.DTE_OUT>16000000 AND udp.DTE_OUT<100000000 THEN
>>> udp.DTE_OUT
>>>             WHEN udp.DTE_IN>16000000 AND udp.DTE_IN<100000000 THEN
>>> udp.DTE_IN
>>>             ELSE '1231'
>>>             END  AS DTE_OUT
>>>    ,CASE WHEN udp.TME_OUT <> 0 THEN udp.TME_OUT
>>>             WHEN udp.TME_IN <> 0 THEN udp.TME_IN
>>>             ELSE 0
>>>             END AS TME_OUT
>>>             ,TRIM(CAST(TME_OUT  AS CHAR(6))) AS TME_OUT1
>>>     ,CAST(CAST(SUBSTR(TRIM(DTE_OUT),1,8)  AS CHAR(8)) AS DATE FORMAT
>>> 'yyyymmdd')  AS DTE_OUT_O
>>>     ,CASE WHEN TME_OUT>99999 THEN CAST(TME_OUT1 AS CHAR(6))
>>>             WHEN TME_OUT>9999 AND TME_OUT<=99999 THEN
>>> CAST('0'||TME_OUT1  AS CHAR(6))
>>>             WHEN TME_OUT>999 AND TME_OUT<=9999 THEN CAST('00'||TME_OUT1
>>> AS CHAR(6))
>>>             WHEN TME_OUT>99 AND TME_OUT<=999 THEN CAST('000'||TME_OUT1
>>> AS CHAR(6))
>>>             WHEN TME_OUT>9 AND TME_OUT<=99 THEN CAST('0000'||TME_OUT1 AS
>>> CHAR(6))
>>>             WHEN TME_OUT>0 AND TME_OUT<=9 THEN CAST('00000'||TME_OUT1 AS
>>> CHAR(6))
>>>             WHEN TME_OUT=0 THEN '000000'
>>>             END AS TME_OUT2
>>>
>>> ,SUBSTR(TRIM(TME_OUT2),1,2)||':'||SUBSTR(TRIM(TME_OUT2),3,2)||':'||SUBSTR(TRIM(TME_OUT2),5,2)
>>> AS
>>>             TME_OUT_O
>>>             , CAST( DTE_OUT_O||' '||TME_OUT_O AS TIMESTAMP FORMAT
>>> 'YYYYMMDD:HH: MI:SS')     AS DTM
>>>             ,udp.nbr  AS  nbr
>>>
>>>    FROM   STS_GNCAUDP   udp
>>>
>>>             INNER JOIN LOAD_LOG LZ_LL  ON udp.LOG_KEY=LZ_LL.LOG_KEY
>>>             INNER JOIN ESA_LOAD_LOG ESA_LL  ON
>>> LZ_LL.ESA_LOAD_LOG_KEY=ESA_LL.LOG_KEY
>>>             AND ESA_LL.PBLSH_IND='$PBLSH_IND'
>>>             AND ESA_LL.LOAD_END_DTM ='$HIGH_DATE_TIME'
>>>             AND ESA_LL.SOR_CD= '$CLM_SOR_CD'
>>>             AND ESA_LL.SUBJ_AREA_NM= '$SUBJ_AREA_NM'
>>>             AND ESA_LL.WORK_FLOW_NM= '$WORK_FLOW_NM'
>>>             QUALIFY ROW_NUMBER()  OVER  (PARTITION BY udp.cc,udp.pst_no,
>>>             udp.cde,udp.nbr,udp.dte,udp.LOG_KEY
>>>             ORDER BY DTM DESC)=1) AS der
>>> ;
>>>
>>>
>>>
>>> Thanks in advance!
>>> Dti
>>>
>>>
>>>
>>>
>>>
>>
>

Mime
View raw message