"IN" clause with Apache Hive HQL

Is there a best practice for utilizing the array that's derived from a multi-select component on a "IN" clause with Apache Hive HQL? I'm utilizing the following query, but I'm getting an Unknown type array.node error when trying to use the bracket notation:

However, it does work when I turn the same exact discreet values explicitly into an array:

Could this be an issue of SQL Prepared Statements?

1 Like

Hi @Douglas_Urriola,

Does one of the two MS SQL options work here? :thinking:

Disabling prepared statements may work, but we generally suggest finding a solution with prepared statements enabled if at all possible :crossed_fingers:

I don't have a Apache Hive HQL resource to test with, so this exact syntax may not work, but it may lead you in the right direction. For my Databricks resource, I can use where id in (select explode(array("1","2"))) but then need the below syntax when referencing the array of values dynamically:

The latter query works, while the former does not.

Yeah, unfortunately the sequence of functions you're using for your Databricks table isn't working for my Hive table:

I've tried a multitude of potential solutions and have to found a solution.

:disappointed: sorry to hear! I see one customer was able to use something like this:

WHERE regexp_like( {{JSON.stringify(multiselect.value)}}, columnName)

Happy to take a look with you in office hours if you'd like!

Yeah, unfortunate that function seems exclusive to Oracle-based databases. Yes, I'll try to join tomorrow's office hours meet. Thanks!

Hey @Douglas_Urriola - It does look like an issue with the array type specifically, was able to work around it with a local Hive instance by casting the multi-select array to a string and then back in the DB with something like

where array_contains(split({{ multiselect1.value.join() }}, ','), COLUMN)


Hi Justin,
This is working like a charm.
Thanks so much to all of you guys for your help!!!