Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - PL/SQL to HiveQL translation


Copy link to this message
-
Re: PL/SQL to HiveQL translation
Jérôme Verdier 2013-07-30, 07:20
Hi,

Thanks for this link, it was very helpful :-)

I have another question.

I have some HiveQL script wich are stored into .hql file.

What is the best way to execute these scripts with a Java/JDBC program ?

Thanks.
2013/7/29 Brendan Heussler <[EMAIL PROTECTED]>

> 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 <
> [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
>>
>>> 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 <[EMAIL PROTECTED]>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 <
>>>> [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
>>>>>
>>>>>>  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,
*Jérôme VERDIER*
06.72.19.17.31
[EMAIL PROTECTED]