1

I am new to HiveQL and is IN and NOT IN supported in it? Especially when using Qubole?

Here is my query:

SELECT DISTINCT vId FROM table1 
WHERE d.columnOne = "123"
AND NOT d.columnTwo
AND timestamp between 1523550000000 AND 1523930000000 
AND NOT h.columnThree regexp "000.000.000.00|111.111.111.11|222.22.222.22" 
AND vId in (select vId from table2 where colOne regexp "000|111|222")

my error is

"Unsupported language features in query: "

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
noobeerp
  • 417
  • 2
  • 6
  • 11
  • 1
    I'm thinking more that the `AND NOT` is the unsupported part. That doesn't look like valid SQL but I am certainly not a hive expert. `IN` and `NOT IN` seem to be [supported](https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.4/bk_data-access/content/hive-013-feature-subqueries-in-where-clauses.html) – Jacob H May 08 '18 at 20:55
  • 1
    "AND NOT d.columnTwo" doesn't seem right to me. Also, are you sure regexp are supported? try a simpler query with just `in` and see what happens. – ATC May 08 '18 at 20:56
  • AND vId in (select vId from table2 where colOne regexp "000|111|222") is where the query is breaking. The rest work fine! – noobeerp May 08 '18 at 21:02
  • can you show us the full query so we can understand where those table aliases come from? – Vamsi Prabhala May 08 '18 at 22:23
  • you should provide the table definitions for table1 and table 2 to understand better – hlagos May 09 '18 at 03:01

0 Answers0