CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sqlgoogle-bigquery
Anoop Deshpande
Anoop Deshpande

Reputation: 582

How to use IN clause with Spark BigQuery Connector (Scala)

I'm unable to use In clause with Spark-BigQuery connector (Scala). I'm getting below exception:

{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "location" : "q",
    "locationType" : "parameter",
    "message" : "No matching signature for operator IN UNNEST for argument types: NUMERIC, ARRAY<FLOAT64> at [1:100]",
    "reason" : "invalidQuery"
  } ],
  "message" : "No matching signature for operator IN UNNEST for argument types: NUMERIC, ARRAY<FLOAT64> at [1:100]",
  "status" : "INVALID_ARGUMENT"
}

Below is my code snippet,

session.read
    .format("com.google.cloud.spark.bigquery")
    .load("my_dataset.department_table")
    .select("DEPT_ORG_ID", "DEPT_NAME")
    .where("DEPT_ORG_ID In (511324, 511322)")

DEPT_ORG_ID is of type numeric.

Spark version: 2.3.1

Upvotes: 1

Views: 269

Answers (1)

Anoop Deshpande
Anoop Deshpande

Reputation: 582

I figured out the solution. In clause should be passed in option() as follows,

session.read
    .option("filter","DEPT_ORG_ID In (511324, 511322)")
    .format("com.google.cloud.spark.bigquery")
    .load("my_dataset.department_table")
    .select("DEPT_ORG_ID", "DEPT_NAME")

Upvotes: 1

Related Questions