Spark dataframe column in jooq select query

user_9234341 0
Viewed 134

I am reading from csv file using rdd and get only one column from dataframe and convert it to array using scala toArray.

After that, I am using this array in a sql to check whether one field value in this array or not.

I am using Postgresql and jooq 3.11, but how much I try, I can not render the sql as it needs to be.

in below code is Array[String]. However, it is not generating the sql. I got these errors while I was experimenting on this issue with inline() and DSL.array, and in() versions of the same question:

Cannot interpret argument of type class org.jooq.impl.QuantifiedSelectImpl as a Field Type class org.jooq.impl.Array is not supported in dialect DEFAULT operator does not exist: character varying = character varying[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

val ast = select(columns:_*).from(tableName).where(field("y").equal(inline(y))).and(field("x").equal(any(x))).asTable("t")

How can I use Array[String] to compare if my column value in this array.

jooq apache-spark postgresql scala
1 Answers

Using source code generation to avoid this problem in general

It is generally recommended to use the jOOQ code generator because it does the following work for you, instead of you having to think of this all the time.

Work around this problem in your specific case

In many cases, jOOQ needs to know the correct data type of a Field<T> expression to bind variables correctly, especially when it comes to PostgreSQL arrays. Type inference for these array types is not as strong as one might hope in PostgreSQL. So to make sure everything works as expected, you'll have to write things like:

field("y", SQLDataType.VARCHAR)
field("x", SQLDataType.VARCHAR)

If you don't supply a data type, jOOQ will simply infer SQLDataType.OBJECT, which corresponds to Types.OTHER in JDBC, or java.lang.Object. So it doesn't know how to bind an array, and a few type inference problems in Java (and Scala) might even lead to the wrong overload being chosen, e.g. among the Field.equal() overloads. In your case, Field.equal(T) was chosen but you wanted Field.equal(QuantifiedSelect)

user_5217990
Related