Addressing the Nested Data Processing Gap: JSONiq Queries on Snowflake Through Snowpark

Abstract

Nested data is common in many use cases but querying it is still not well supported. Options available today include using: (1) SQL extensions, which are often unintuitive and errorprone; (2) user-defined functions, which limit portability and reusability, and often reduce performance; or (3) domain-specific query languages (DSQL), which often have limited scalability and performance. In this paper, we address the shortcomings of the latter approach by translating a language specifically designed for nested data, JSONiq, to a highly efficient, scalable, and featurerich RDBMS, the Snowflake Database. For this purpose, we use the Snowpark API, a data-frame-based client library for writing applications on Snowflake, which allows us to translate each JSONiq query into a single native Snowflake SQL query. In contrast to previous approaches, this does not introduce any interpretation overhead or optimization barriers that may limit efficient execution in the target system. We evaluate the resulting system on an established benchmark for large-scale nested data from the high-energy physics (HEP) domain on up to 1 TiB as well as the SSB benchmark from the relational domain. Our approach is on par or better than handwritten SQL baselines while allowing for significantly more readable query formulations and typically outperforms the state-of-the-art systems specialized for nested data by an order of magnitude.