Data misplaced when reading a table that does not have the same field positions as the spark schema

[azaroui]

I am trying to create a dataset from bigquery table. The table has the same fields as case class but not in the same order.
When creating the dataset, we get columns mapped to the wrong fields.

Given this table:

When loading dataset

case class NestedClass(
  int3: Int,
  int1: Int,
  int2: Int)

case class ClassA(str: String, l1: NestedClass)

 val schema = Encoders.product[ClassA].schema

    val ds2 = spark.read
      .schema(schema)
      .option("table", "customers_sale.test_table")
      .format("com.google.cloud.spark.bigquery")
      .load()
      .as[ClassA]

    ds2.map(_.l1).show(false)

NB: notice that NestedClass has the same fields as table but in different order: (int3, int1, int2) instead of (int1, int2, int3)

we got this:

+----+----+----+
|int1|int2|int3|
+----+----+----+
|2   |3   |1   |
+----+----+----+

We expect to get this

+----+----+----+
|int1|int2|int3|
+----+----+----+
|1   |2   |3   |
+----+----+----+

=> The connector does not use fields name to assign values but correlates field position in case class with the same position in table.

[davidrabinowitz]

It seems that the data has been written correctly into the table. What happens when you read the table without specifying the schema? Also notice that the order of the l1 fields you've specified is int3,int1,int2 which correlates to the values. Can you please fix the order and try again?

[azaroui]

It seems that the data has been written correctly into the table. What happens when you read the table without specifying the schema? Also notice that the order of the l1 fields you've specified is int3,int1,int2 which correlates to the values. Can you please fix the order and try again?

I purposely changed the order of the attributes to reproduce the problem i had on our project: the connector does not use the field name to assign the values but their positions.

Without specifying schema we get this:

 Exception in thread "main" org.apache.spark.sql.AnalysisException: Cannot up cast `l1`.`int3` from bigint to int as it may truncate
 The type path of the target object is:
 - field (class: "scala.Int", name: "int3")
 - field (class: "com.carrefour.phenix.transactions.offline.persister.NestedClass", name: "l1")
 - root class: "com.carrefour.phenix.transactions.offline.persister.ClassA"
 You can either add an explicit cast to the input data or choose a higher precision type of the field in the target object;
 	at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveUpCast$.org$apache$spark$sql$catalyst$analysis$Analyzer$ResolveUpCast$$fail(Analyzer.scala:2643)
 	at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveUpCast$$anonfun$apply$38$$anonfun$applyOrElse$11.applyOrElse(Analyzer.scala:2659)
 	at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveUpCast$$anonfun$apply$38$$anonfun$applyOrElse$11.applyOrElse(Analyzer.scala:2654)
 	at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$2.apply(TreeNode.scala:258)
 	at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$2.apply(TreeNode.scala:258)
 	at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:69)
 	at org.apache.spark.sql.catalyst.trees.TreeNode.transformDown(TreeNode.scala:257)

I updated the question to give more precision

[emkornfield]

If the schema here is a nested struct, then I think we have a gap in the conversion logic:

 namesInOrder.stream()
              .map(root::getVector)
              .map(ArrowSchemaConverter::new)
              .toArray(ColumnVector[]::new);

only rearranges top-level columns. I'm not sure if the right metadata is actually passed down by spark to correct this problem though

[LaurentValdenaire]

We indeed have an issue with nested structs

[davidrabinowitz]

Fixed by PR #391