Jerome, There is a really good page on the wiki: https://cwiki.apache.org/Hive/hiveserver2-clients.html
I use the HiveServer2 JDBC driver. Maybe there are other ways? Brendan On Mon, Jul 29, 2013 at 5:47 AM, Jérôme Verdier <verdier.jerom...@gmail.com>wrote: > Hi, > > Thanks everyone for your help. > > Has anyone have a good tutorial to run Hive queries and scripts with Java > (over Eclipse). I have some Java Development basis but i'm pretty new > using Hive with Java/Eclipse. > > Thanks. > > > 2013/7/25 j.barrett Strausser <j.barrett.straus...@gmail.com> > >> The advice I have always seen for your case is to transform the subquery >> in the WHERE clause into a LEFT OUTER JOIN. >> >> >> >> >> On Thu, Jul 25, 2013 at 11:04 AM, Edson Ramiro <erlfi...@gmail.com>wrote: >> >>> AFAIK, >>> >>> Hive supports subqueries only in the FROM clause. >>> >>> Maybe you have to split you query into more queries... >>> >>> >>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries >>> >>> >>> >>> >>> Edson Ramiro >>> >>> >>> On Thu, Jul 25, 2013 at 9:31 AM, Jérôme Verdier < >>> verdier.jerom...@gmail.com> wrote: >>> >>>> Hi Bennie, >>>> >>>> I was trying some solutions to pass through my problem, and a problem >>>> occurs >>>> >>>> here is the error : >>>> >>>> FAILED: ParseException line 26:14 cannot recognize input near 'SELECT' >>>> 'cal' '.' in expression specification >>>> >>>> Is AND...BETWEEN ( SELECT..... is possible in Hive? >>>> >>>> >>>> 2013/7/25 Bennie Schut <bsc...@ebuddy.com> >>>> >>>>> Hi Jerome, >>>>> >>>>> Yes it looks like you could stop using GET_SEMAINE and directly >>>>> joining "calendrier_hebdo" with "calendrier" for example. For >>>>> "FCALC_IDJOUR" you will have to make a udf so I hope you have some java >>>>> skills :) >>>>> The "calendrier" tables suggests you have star schema with a calendar >>>>> table. If on oracle you partitioned on a date and use a subquery to get >>>>> the >>>>> dates you want from the fact table you can expect this to be a problem in >>>>> hive. Partition pruning works during planning it will not know which >>>>> partitioned to prune and thus run on all the data in the fact table and >>>>> filter after it's done making partitioning useless. There are ways of >>>>> working around this, it seems most people decide to use a "deterministic" >>>>> udf which produces the dates and this causes the udfs to be run during >>>>> planning and partition pruning magically works again. >>>>> Hope this helps. >>>>> >>>>> Bennie. >>>>> >>>>> Op 25-7-2013 09:50, Jérôme Verdier schreef: >>>>> >>>>> Hi, >>>>> >>>>> I need some help to translate a PL/SQL script in HiveQL. >>>>> >>>>> Problem : my PL/SQL script is calling two functions. >>>>> >>>>> you can see the script below : >>>>> >>>>> SELECT >>>>> in_co_societe as co_societe, >>>>> 'SEMAINE' as co_type_periode, >>>>> a.type_entite as type_entite, >>>>> a.code_entite as code_entite, >>>>> a.type_rgrp_produits as type_rgrp_produits, >>>>> a.co_rgrp_produits as co_rgrp_produits, >>>>> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC, >>>>> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC, >>>>> SUM(a.NB_CLIENTS) as NB_CLIENTS, >>>>> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP, >>>>> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as >>>>> MT_OBJ_CA_NET_TTC_COMP, >>>>> SUM(a.NB_CLIENTS_COMP) as NB_CLIENTS_COMP >>>>> from >>>>> kpi.thm_ca_rgrp_produits_jour/*@o_bi.match.eu*/ a >>>>> WHERE >>>>> a.co_societe = in_co_societe >>>>> AND a.dt_jour between >>>>> ( >>>>> SELECT >>>>> cal.dt_jour_deb >>>>> FROM ods.calendrier_hebdo cal >>>>> WHERE cal.co_societe = in_co_societe >>>>> AND cal.co_an_semaine = ods.package_date.get_semaine( >>>>> ods.package_date.fcalc_idjour( >>>>> CASE >>>>> WHEN TO_CHAR(D_Dernier_Jour,'YYYY') = >>>>> TO_CHAR(D_Dernier_Jour-364,'YYYY') THEN >>>>> NEXT_DAY(D_Dernier_Jour-364,1)-7 >>>>> ELSE >>>>> D_Dernier_Jour-364 >>>>> END >>>>> ) >>>>> ) >>>>> ) >>>>> AND D_Dernier_Jour-364 >>>>> -- On ne calcule rien si la semaine est compl¿¿te >>>>> AND ( >>>>> TO_CHAR(D_Dernier_Jour,'DDMM') <> '3112' >>>>> AND TO_CHAR(D_Dernier_Jour,'D') <> '7' >>>>> ) >>>>> GROUP BY >>>>> a.type_entite, >>>>> a.code_entite, >>>>> a.type_rgrp_produits, >>>>> a.co_rgrp_produits; >>>>> >>>>> The function ods.package_date.get_semaine is : >>>>> >>>>> FUNCTION GET_SEMAINE >>>>> (ID_DEB IN NUMBER) >>>>> RETURN NUMBER >>>>> IS >>>>> SEMAINE NUMBER(10); >>>>> BEGIN >>>>> SELECT CO_AN_SEMAINE >>>>> INTO SEMAINE >>>>> FROM CALENDRIER >>>>> WHERE ID_JOUR = ID_DEB; >>>>> >>>>> RETURN (SEMAINE); >>>>> EXCEPTION >>>>> WHEN NO_DATA_FOUND THEN >>>>> RETURN (0); >>>>> WHEN OTHERS THEN >>>>> RETURN (0); >>>>> END; >>>>> >>>>> The function ods.package_date.fcalc_idjour is below : >>>>> >>>>> FUNCTION FCALC_IDJOUR >>>>> (DATE_REFERENCE IN DATE) >>>>> RETURN NUMBER >>>>> IS >>>>> NM_ANNEE NUMBER := >>>>> TO_NUMBER(TO_CHAR(DATE_REFERENCE,'YYYY')); >>>>> NM_MOIS NUMBER := >>>>> TO_NUMBER(SUBSTR(TO_CHAR(DATE_REFERENCE,'YYYYMM'),5,2)); >>>>> NM_JOUR NUMBER := >>>>> TO_NUMBER(SUBSTR(TO_CHAR(DATE_REFERENCE,'YYYYMMDD'),7,2)); >>>>> IDJOUR_CALCULE NUMBER := 0; >>>>> BEGIN >>>>> IF NM_ANNEE < 1998 >>>>> OR DATE_REFERENCE IS NULL THEN >>>>> IDJOUR_CALCULE := 0; >>>>> ELSE >>>>> IDJOUR_CALCULE := ((NM_ANNEE - 1998) * 600) + ((NM_MOIS - 01) * >>>>> 50) + NM_JOUR; >>>>> END IF; >>>>> >>>>> RETURN IDJOUR_CALCULE; >>>>> DBMS_OUTPUT.PUT_LINE(IDJOUR_CALCULE); >>>>> END FCALC_IDJOUR; >>>>> >>>>> Is it possible to translate this in one HiveQL script ? >>>>> >>>>> >>>>> >>>> >>>> >>>> -- >>>> *Jérôme VERDIER* >>>> 06.72.19.17.31 >>>> verdier.jerom...@gmail.com >>>> >>>> >>> >> >> >> -- >> >> >> https://github.com/bearrito >> @deepbearrito >> > > > > -- > *Jérôme VERDIER* > 06.72.19.17.31 > verdier.jerom...@gmail.com > >
