Spark Quick Start Guide column stat expression index is not working as expected for the scala code

[rangareddy]

Bug Description

What happened:

The "column stat expression index" functionality, as implemented in the provided Scala code example within the Spark Quick Start Guide, is not performing its intended optimization or yielding the expected results.

scala> // Query on ts column would prune the data using the idx_column_ts index

scala> spark.sql(s"SELECT * FROM hudi_indexed_table WHERE from_unixtime(ts, 'yyyy-MM-dd') = '2023-09-24'").show(false);
25/11/24 11:20:31 WARN CacheManager: Asked to cache already cached data.
25/11/24 11:20:32 WARN CacheManager: Asked to cache already cached data.
+-------------------+--------------------+------------------+----------------------+-----------------+---+----+-----+------+----+----+
|_hoodie_commit_time|_hoodie_commit_seqno|_hoodie_record_key|_hoodie_partition_path|_hoodie_file_name|ts |uuid|rider|driver|fare|city|
+-------------------+--------------------+------------------+----------------------+-----------------+---+----+-----+------+----+----+
+-------------------+--------------------+------------------+----------------------+-----------------+---+----+-----+------+----+----+

What you expected:

I expected the Scala code to successfully implement and utilize the column stat expression index, resulting in the anticipated query optimization and improved performance (e.g., predicate pushdown or faster data filtering) as documented in the Quick Start Guide.

Steps to reproduce:

1. Follow the Spark quick start guide index example (https://hudi.apache.org/docs/quick-start-guide#indexing)
2. Query the table data and you will see empty results.

Environment

Hudi version: 1.1.0
Query engine: (Spark/Flink/Trino etc) Spark
Relevant configs:

Logs and Stack Trace

No response

[rangareddy]

Reproducible Test code:

hudi-spark-datasource/hudi-spark/src/test/scala/org/apache/spark/sql/hudi/feature/index/TestExpressionIndex.scala

test("Test Expression Index Column With Timestamp Expressions") {
    withTempDir { tmp =>
      val tableName = generateTableName
      val basePath = s"$tmp/$tableName"

      spark.sql(
        s"""CREATE TABLE $tableName (
        |  ts LONG,
        |  id STRING,
        |  rider STRING,
        |  driver STRING,
        |  fare DOUBLE,
        |  dateDefault STRING,
        |  date STRING,
        |  city STRING,
        |  state STRING
        |) USING HUDI
        |options(
        |  primaryKey ='id',
        |  hoodie.write.record.merge.mode = 'COMMIT_TIME_ORDERING'
        |)
        |PARTITIONED BY (city)
        |location '$basePath'
        """.stripMargin
      )

      spark.sql(
        s"""
          |insert into $tableName(ts, id, rider, driver, fare, dateDefault, date, city, state) VALUES
          | (1695159649087,'trip1','rider-A','driver-K',19.10, '2020-11-30 01:30:40', '2020-11-30', 'san_francisco','california'),
          | (1695091554788,'trip6','rider-C','driver-K',17.14, '2021-11-30 01:30:40', '2021-11-30', 'san_diego','california'),
          | (1695046462179,'trip3','rider-E','driver-O',93.50, '2022-11-30 01:30:40', '2022-11-30', 'austin','texas'),
          | (1695516137016,'trip4','rider-F','driver-P',34.15, '2023-11-30 01:30:40', '2023-11-30', 'houston','texas')
        """.stripMargin
      )
      spark.sql(s"CREATE INDEX idx_column_ts on $tableName using column_stats(ts) options(expr='from_unixtime', format='yyyy-MM-dd')")
      checkAnswer(s"select id, rider, driver from $tableName WHERE from_unixtime(ts, 'yyyy-MM-dd') = '2023-09-24'")(
        Seq("trip4", "rider-F", "driver-P")
      )
      spark.sql(s"DROP INDEX idx_column_ts on $tableName")
    }
  }

[yihua]

@CTTY could you help triage the root cause of this issue?

[CTTY]

Sure I can take a look

[CTTY]

Hi @rangareddy , I took a look today and I believe this is an expected behavior, in your reproduction code, you have 13-digit unix time like 1695159649087, but Hudi/Spark's from_unix function only has accuracy of seconds. Thus the discrepency. In the quickstart guide, it also uses 10-digit unix time like 1695159649.

I also verified that the index function will only be called when CREATE INDEX. When you are querying the data again the function won't be called. So the index should be working as expected

[rangareddy]

Hi @CTTY

We need to ensure compatibility with 13-digit timestamps, as we cannot mandate that users submit only 10-digit values. Our solution will be to truncate any 13-digit timestamp to 10 digits.

Note: In the Scala code, we are specifying the value in 13 digits.