Is Hyperspace supporting indexes on nested fields?

[andrei-ionescu]

Given the pqDf dataset having schema

root
 |-- id: integer (nullable = true)
 |-- nested: map (nullable = true)
 |    |-- key: string
 |    |-- value: string (valueContainsNull = true)
 |-- date: timestamp (nullable = true)

and containing something similar to

+-----+---------------------------------------+-------------------+
|id   |nested                                 |date               |
+-----+---------------------------------------+-------------------+
|25000|[field1 -> Q43rT, field2 -> nIfKMTIC7P]|2020-09-21 00:00:00|
|25001|[field1 -> YHfQR, field2 -> rwI2d4kQZT]|2020-08-04 00:00:00|
|25002|[field1 -> hnPEZ, field2 -> ipwOZ8n6D6]|2020-09-08 00:00:00|
|25003|[field1 -> cXkZK, field2 -> ZPZ0yu2hVe]|2020-08-21 00:00:00|
|25004|[field1 -> pyrp3, field2 -> TYSbU4rAQl]|2020-03-18 00:00:00|
|25005|[field1 -> kzGYk, field2 -> sTONXt5RdU]|2020-06-02 00:00:00|
|25006|[field1 -> y10Wz, field2 -> V0WToQPKvM]|2020-04-10 00:00:00|
|25007|[field1 -> lHmmY, field2 -> H6TwG1ae3H]|2020-05-27 00:00:00|
|25008|[field1 -> bLeZo, field2 -> pa0yeuPyKk]|2020-02-16 00:00:00|
|25009|[field1 -> lappV, field2 -> khuUVUMw2C]|2020-10-09 00:00:00|
+-----+---------------------------------------+-------------------+

Can I create an index on the nested.field1 with something like bellow?

hs.createIndex(
  pqDf, 
  IndexConfig(
    "index_nested_field1", 
    indexedColumns = Seq("nested.field1"), 
    includedColumns = Seq("date", "id")
  )
)

This example is with a map, what about other kinds of nested fields?

[andrei-ionescu]

@rapoth, @imback82, @sezruby Do you guys have some time to look at my question above? Thanks!

[sezruby]

Sorry for the delay. Currently Hyperspace doesn't support nested columns:

com.microsoft.hyperspace.HyperspaceException: Index config is not applicable to dataframe schema.
  at com.microsoft.hyperspace.actions.CreateAction.validate(CreateAction.scala:53)

However, I think we could support it by

• validate nested column names
• modify resolve function for indexed/included columns in CreateAction / Filter & Join Rule.
• null value handling

cf) schema & plan from a simple json example:

scala> df.printSchema
root
 |-- name: string (nullable = true)
 |-- type: struct (nullable = true)
 |    |-- a: string (nullable = true)
 |    |-- b: string (nullable = true)

scala> df.select("type.a").queryExecution.optimizedPlan
res10: org.apache.spark.sql.catalyst.plans.logical.LogicalPlan =
Project [type#25.a AS a#39]
+- Relation[name#24,type#25] json

scala> df.select("type.a").queryExecution.sparkPlan
res11: org.apache.spark.sql.execution.SparkPlan =
Project [type#25.a AS a#41]
+- FileScan json [type#25] Batched: false, Format: JSON, Location: InMemoryFileIndex[file:/path/to/test.json], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<type:struct<a:string,b:string>>

[andrei-ionescu]

@sezruby thank you for answering. Is there a feature request for this? Should I start one and the proposal?

[rapoth]

Yes, one heard this request from a couple of customers. I thought we had an issue but apparently not. You can feel free to start a proposal though. Thank you!

[andrei-ionescu]

@sezruby, @imback82, @rapoth I added the #347 proposal for this.