<\/span><\/h2>\n\n\n\nHere are some of the major differences that developers encountered when migrating code.<\/p>\n\n\n\n
Let’s start with string concatenation. Spark SQL didn’t recognize the “||” operator as an alternative to the concat() function. Some development teams faced a lot of changes!<\/p>\n\n\n\n
The basic single-row insert statement didn’t need modification. But Spark SQL didn’t recognize the handy multi-row syntax:<\/p>\n\n\n\n
INSERT INTO big_table (col1, col2, col3) \nVALUES (var1, var2, var3), (var1, var2, var3), (var1, var2, var3)<\/code><\/pre>\n\n\n\nChanges in behavior<\/h3>\n\n\n\n
In many ways, differences in syntax are the easiest changes to handle. The major problem came in the form of subtle and not-so-subtle changes in behavior between the two standards.<\/p>\n\n\n\n
For example, the ANSI standard requires the to_date() function to fail if the input string can’t be parsed. For example, something like this:<\/p>\n\n\n\n
SELECT to_date(\"Chicago, Illinois\")<\/code><\/pre>\n\n\n\nMy bet is that there’s a data issue here where location fields are being incorrectly parsed as date fields. I definitely want that to fail immediately.<\/p>\n\n\n\n
But Spark SQL returns a NULL result. If you’re not expecting this, your code and testing can merrily carry along all the way into production.<\/p>\n\n\n\n
Similarly, casting from a string to an integer throws a run-time exception in systems implementing ANSI SQL. But Spark SQL is more accommodating.<\/p>\n\n\n\n