CODEWITHSUNDEEP
hivehiveql
Tee Jay
Tee Jay

Reputation: 113

Hive SQL is giving extraneous input ',' expecting ')' error

My Hive SQL code is as follows:

  FROM (
      SELECT *, CONCAT_WS('__', ) AS no_hash_join_key
        FROM xyz.e_6445
       WHERE p_m_id = '{MARKET_ID}'
         AND p_date = '{RUN_DATE_YYYY-MM-DD}'
  ) dataforge

I am getting the following error:

 extraneous input ',' expecting ')'

I am failing to understand what is causing the error. I tried changing it to the following:

SELECT *, CONCAT_WS('__', ' ' )

This removes the given error but results in a different error :

 INSERT_SQL_COLUMN_MISMATCH

What am I doing wrong? Any help would be appreciated.

Upvotes: 2

Views: 24736

Answers (3)

jeppoo1
jeppoo1

Reputation: 698

My problem was with a CAST function in the Hive SQL:

/* this doesn't work: */
CAST((my_date) AS DATE)

/* this one works: */
(CAST(my_date AS DATE))

Upvotes: 0

pr.lwd
pr.lwd

Reputation: 140

You have a blank parameter in concat_ws. Get rid of the comma (or pass another parameter)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I don't have Hive on-hand, but some databases require aliases when using * with other columns. In addition, you need a second argument in CONCAT_WS().

Does this work?

SELECT e.*, CONCAT_WS('__', NULL) AS no_hash_join_key
FROM xyz.e_6445 e
WHERE e.p_m_id = '{MARKET_ID}' AND e.p_date = '{RUN_DATE_YYYY-MM-DD}'

Upvotes: 0

Related Questions