Error in Hive : Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: One or more arguments are expected

Question

I am trying to translate some PL/SQL script in hive, and i faced an error with one HiveQL script.

The error is this one :

    FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: One or more arguments are expected.

I think that the error is coming from this part of script :

SELECT
        mag.co_magasin,
        dem.id_produit                                  as id_produit_orig,
        pnvente.dt_debut_commercial                     as dt_debut_commercial,
        COALESCE(pnvente.id_produit,dem.id_produit)     as id_produit,
        min(
          CASE WHEN dem.co_validation IS NULL THEN 0 ELSE 1 END
        )                                               as flg_demarque_valide,
        sum(CASE WHEN dem.co_validation IS NULL THEN 0 ELSE cast(dem.mt_revient_ope AS INT) END)
                                                        as me_dem_con_prx_cs,
        0                                               as me_dem_inc_prx_cs,
        0                                               as me_dem_prov_stk_cs,
        sum(CASE WHEN dem.co_validation IS NULL THEN 0 ELSE cast(dem.qt_demarque AS INT) END)
                                                        as qt_dem_con,
        0                                               as qt_dem_inc,
        0                                               as qt_dem_prov_stk,
        RANK() OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY pnvente.dt_debut_commercial DESC, COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang
      from default.calendrier cal
      INNER JOIN default.demarque_mag_jour dem
      ON  CASE WHEN dem.co_societe = 1 THEN 1 ELSE 2 END = '${hiveconf:in_co_societe}'
      AND dem.dt_jour    = cal.dt_jour
      LEFT OUTER JOIN default.produit_norm pn
      ON  pn.co_societe = dem.co_societe
      AND pn.id_produit = dem.id_produit
      LEFT OUTER JOIN default.produit_norm pnvente
      ON  pnvente.co_societe = pn.co_societe
      AND pnvente.co_produit_rfu = pn.co_produit_lip
      AND pnvente.co_type_motif='05'
      INNER JOIN default.kpi_magasin mag
      ON  mag.co_societe = '${hiveconf:in_co_societe}'
      AND mag.id_magasin = dem.id_magasin
      WHERE cal.dt_jour = '${hiveconf:in_dt_jour}'
      AND NOT (dem.co_validation IS NULL AND cal.dt_jour > from_unixtime(unix_timestamp()-3*60*60*24, 'ddmmyyyy'))
      -- JYP 4.4
      AND dem.co_operation_magasin IN ('13','14','32')
      GROUP BY
        mag.co_magasin,
        dem.id_produit,
        pnvente.dt_debut_commercial,
        COALESCE(pnvente.id_produit,dem.id_produit)

But i can't find any solution on the web.

Thanks for your help :-)

Answer 1

I have run into the same error. rank() is case sensitive in hive and the error message give nothing away. Try changing RANK() to rank().