News for hackers. Stuff that matters.
Re:Ask HN: How do you test SQL? by MrPowers on Wednesday February 01, 2023 @06:59AM (#34609337)
Spark makes it easy to wrap SQL in functions that are easy to test. I am the author of the popular Scala Spark (spark-fast-tests) and PySpark (chispa) testing libraries. Some additional tips to speed up Spark tests (can speed up tests between 70-90%):

* reuse the same Spark session throughout the test suite

* Set shuffle partitions to 2 (instead of default which is 200)

* Use dependency injection to avoid disk I/O in the test suite

* Use fast DataFrame equality when possible. assertSmallDataFrameEquality is 4x faster than assertLargeDataFrameEquality. Some benchmarks here: https://github.com/MrPowers/spark-fast-tests#why-is-this-lib...

* Use column equality to test column functions. Don't compare DataFrames unless you're testing custom DataFrame transformations. See the spark-style-guide for definitions for these terms: https://github.com/MrPowers/spark-style-guide/blob/main/PYSP...

Spark is an underrated tool for testing SQL. Spark makes it really easy to abstract SQL into unit testable chunks. Configuring your tests properly takes some knowledge, but you can make the tests run relatively quickly.

Re:Ask HN: How do you test SQL? by RobinL on Wednesday February 01, 2023 @08:47AM (#34610309)
That's super useful, thanks. Could you expand on the 'Use dependency injection to avoid disk I/O in the test suite' point please - I'm not sure I understand what it means but it sounds interesting!
Re:Ask HN: How do you test SQL? by MrPowers on Wednesday February 01, 2023 @10:50AM (#34612182)
Sure, this blog post explains the dependency injection design pattern with Spark: https://mrpowers.medium.com/dependency-injection-with-spark-...

You can structure your code to read from paths when run in the production environment, but inject DataFrames you build in memory for your test suite. Spark is designed to read multiple files in parallel, so it's not optimized to read a single tiny file. That's why it's best to avoid I/O in Spark test suites whenever possible.