Interpreting the upper/lower bounds column returned from querying the .files metadata

[cccs-jc]

Iceberg has functionality to introspect tables. This is very useful for example
to check that a column is properly sorted by checking lower/upper bounds

https://iceberg.apache.org/docs/latest/spark-queries/#files

The return value of this query SELECT * FROM prod.db.table.files

Will return an upper_bounds and lower_bounds column. A map of column ID to binary.

https://iceberg.apache.org/spec/#appendix-d-single-value-serialization

To interpret the binary column we register custom UDF functions like this one to
convert the bytes in little endian

def _to_int(data):
    return int.from_bytes(data, byteorder='little', signed=True)
# register pyspark UDF
to_int = F.udf(_to_int, IntegerType())
# register SQL UDF
spark.udf.register("to_int", _to_int, IntegerType())

Then we can use this function to interpret the data and display it correctly.

-- Stored as 4-byte little-endian
SELECT
    min(to_int(lower_bounds[1])) min_a,
    max(to_int(upper_bounds[1])) max_a,
    min(to_int(lower_bounds[2])) min_b,
    max(to_int(upper_bounds[2])) max_b,
    min(to_int(lower_bounds[3])) min_c,
    max(to_int(upper_bounds[3])) max_c
FROM
    prod.db.table.files

Does Iceberg come with utility functions like these. Is there an easier way to interpret the binary data than to write a custom UDF?

[szehon-ho]

@cccs-jc yea it is a pain, the way I know is via the Iceberg API's, I'm not sure if we can easily write a udf or not.

// for a given column id -> binary
Types.NestedField field = table().schema().findField(id);
Transforms.identity(field.type()).toHumanString(Conversions.fromByteBuffer(field.type(), binary));

im working on this actually : #4362, I hope to have a first version of pr in next day or so, hope it can be a more user-friendly way to expose the information.

[cccs-jc]

looking forward to the progression of #4362. Hopefully this will make it easier to use from SQL statements. Which is our main way to interface with Iceberg.

[szehon-ho]

Cool, i put up a pr for review #5376, feel free to take a look and follow.

[kbendick]

Now that I have implemented FunctionCatalog In #5377. (or once it's merged and useable), we can implement many functions with included codegen etc.

This could be a great / efficient use case for this eventually.

My priorities right now are bucket, and zorder for writing out partition transforms but re we raw a function system.from_binary(typeID, value) or something it could be pretty efficiently codegenned.

[kbendick]

These functions will be useable from SparkSQL without any user registration needed and different function definitions can be made based on the input types (which spark will resolve).

[kbendick]

But for now, the UDF or code from #4362 or #5376 should be potentially implemented as FunctionCatalog only goes back to Spark 3.2 anyways.

[cccs-jc]

@kbendick I'm rather interested in that zorder function. Is this zorder like the databricks zorder. https://databricks.com/blog/2018/07/31/processing-petabytes-of-data-in-seconds-with-databricks-delta.html

We've implemented such a method to sort IP 5 tuples.

[RussellSpitzer]

@kbendick I'm rather interested in that zorder function. Is this zorder like the databricks zorder. https://databricks.com/blog/2018/07/31/processing-petabytes-of-data-in-seconds-with-databricks-delta.html

We've implemented such a method to sort IP 5 tuples.

Yep, although I haven't looked at their implementation i'm betting it's pretty similar since the math is pretty old.

[cccs-jc]

cool, please don't hesitate to reach out for some of our lessons learn etc

[kbendick]

Cool thank you!

I think we have a traditional Spark UDF you can register already (or examples in the JMH perf tears that show how to do it).

You might consider trying it and seeing if to see if they match / any other issues or differences.

The original PR for allowing data to be rewritten on sort using a "zorder" strategy (with example usage from Spark) is here: #3983

[kbendick]

And the utility functions to interleave bits from that PR will be used eventually in the function catalog.

I'll reach out to you offline to see your experience as well @cccs-jc

[github-actions]

This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.

[szehon-ho]

Marking as closed as #5376 is done, unless there's further asks here