Here's my Hive Query:
INSERT OVERWRITE TABLE csp_filtered_data_table_b
SELECT CSP_CPCODE, CSP_ACSKEYWORDS, CSP_TARIFF_CODE, CSP_ERROR_CODE,
CONCAT(CSP_ARRAY_DATE[2], '-',
(
CASE CSP_ARRAY_DATE[1]
WHEN 'Jan' THEN '01'
WHEN 'Feb' THEN '02'
WHEN 'Mar' THEN '03'
WHEN 'Apr' THEN '04'
WHEN 'May' THEN '05'
WHEN 'Jun' THEN '06'
WHEN 'Jul' THEN '07'
WHEN 'Aug' THEN '08'
WHEN 'Sep' THEN '09'
WHEN 'Oct' THEN '10'
WHEN 'Nov' THEN '11'
ELSE '12'
END
), '-',
CSP_ARRAY_DATE[0]),
SUBSTR(CSP_TRANSACTION_TIME_DATE, 13,23),
CSP_MOBILE_NUMBER,
WEEKOFYEAR(
CONCAT(CSP_ARRAY_DATE[2], '-',
(
CASE CSP_ARRAY_DATE[1]
WHEN 'Jan' THEN '01'
WHEN 'Feb' THEN '02'
WHEN 'Mar' THEN '03'
WHEN 'Apr' THEN '04'
WHEN 'May' THEN '05'
WHEN 'Jun' THEN '06'
WHEN 'Jul' THEN '07'
WHEN 'Aug' THEN '08'
WHEN 'Sep' THEN '09'
WHEN 'Oct' THEN '10'
WHEN 'Nov' THEN '11'
ELSE '12'
END
), '-',
CSP_ARRAY_DATE[0])
)
FROM csp_formatted_date;
Now, I'm supposed to get the results grouped by the CSP_ERROR_CODE.
So I added the following:
GROUP BY CSP_CPCODE, CSP_ACSKEYWORDS, CSP_TARIFF_CODE, CSP_MOBILE_NUMBER, CSP_ERROR_CODE;
Though I need only group it by CSP_ERROR_CODE, it always returns an error (seen below) which can be fixed by putting in all the other fields. I have no idea what this means for table organization, but after doing just that in this case, it is giving me the following error:
FAILED: SemanticException [Error 10025]: Line 3:7 Expression not in GROUP BY key '2'
I usually encounter that error when I failed to put in the column name 'key', but where did key '2' come from, and how do I fix this?
