Databricks sparksql concat is not your SQL Server concat

One of these is not like the other...

• Databricks Concat
• SQL Server Concat

The concat function is super handy in the database world but be aware that the SQL Server one is way better because it solves two problems. It combines everything into a string and it does not require NULL checking. In the before times, one had to down cast to a n/var/char type as well as check for NULL before appending strings via the plus sign.

In Databricks CONCAT WILL ONLY TAKE CARE OF CASTING TO THE STRING TYPE. NULLS WILL CONTINUE TO BITE YOU IN THE BUTTOCKS.

Given the following example query, we generate two rows in a derived table where the col2 value is either true (boolean 1) or NULL. In the LEFT JOIN LATERAL, which is the Databricks CROSS APPLY equivalent, I concat the 3 columns together with a pipe as separator and behold, my decidedly different results from a SQL Server expectation.

SELECT
*
FROM
(
SELECT * FROM VALUES (1, true, 'B')
UNION ALL SELECT * FROM VALUES (2, NULL, 'C')
)AS X(col1, col2, col3)
LEFT JOIN LATERAL
(
  SELECT concat(X.col1, '|', X.col2, '|', X.col3)
)HK(hkey);

What do you do? You get to wrap every nullable column with a coalesce call. Except, coalesce requires the same datatypes (mostly) so a naive implmentation of

SELECT concat(X.col1, '|', coalesce(X.col2, ''), '|', X.col3)

will result in the following error
AnalysisException: [DATATYPE_MISMATCH.DATA_DIFF_TYPES] Cannot resolve "coalesce(outer(X.col2), )" due to data type mismatch: Input to `coalesce` should all be the same type, but it's ("BOOLEAN" or "STRING")

Instead, one needs to do something along the lines of

SELECT
*
FROM
(
SELECT * FROM VALUES (1, true, 'B')
UNION ALL SELECT * FROM VALUES (2, NULL, 'C')
)AS X(col1, col2, col3)
LEFT JOIN LATERAL
(
  SELECT concat(X.col1, '|', coalesce(concat(X.col2, ''),''), '|', X.col3)
)HK(hkey);

At least I can automate this pattern with the information_schema.columns

Databricks sparksql escaping quote/tick

If I had to embed a single quote in a query in TSQL, I would double it. In SparkSQL, I escape it like a classic C style string. So, the following shows how one would generate a query that is a query to find the row counts across all tables in SQL or unity catalog. Although for SQL, you're better off just querying the partitions meta table as it's waaaaay faster.

TSQL

SELECT CONCAT('SELECT COUNT(1) AS rc, ''', T.table_name, ''' AS table_name FROM dev.silver.', T.table_name, '') AS rcQ FROM dev.information_schema.tables AS T WHERE T.table_schema = 'silver'

Databricks unity catalog

SELECT CONCAT('SELECT COUNT(1) AS rc, \'', T.table_name, '\' AS table_name FROM dev.silver.', T.table_name, '') AS rcQ FROM dev.information_schema.tables AS T WHERE T.table_schema = 'silver'

Difference between SparkSQL and TSQL casts

Yet another thing that has bitten me working in SparkSQL in Databricks---this time it's data types.

In SQL Server, a tinyint ranges from 0 to 255 but both of them allow for 256 total values. If you attempt to cast a value that doesn't fit in that range, you're going to raise an error.

SELECT 256 AS x, CAST(256 AS tinyint) AS boom

Msg 220, Level 16, State 2, Line 1
Arithmetic overflow error for data type tinyint, value = 256.

The range for a tinyint is -128 to 127 in SparkSQL - still 256 total values. Docs call it out as well ByteType: Represents 1-byte signed integer numbers. The range of numbers is from -128 to 127 SELECT CAST('128' AS tinyint) AS WhereIsTheBoom, CAST(128 AS tinyint) As WhatIsThisNonsense Here I select the value 128 as both a string and a number. I honestly have no idea how to interpret these results. A cast from string behaves more like a TRY_CAST but numeric overflows just cycle?

Yeah, the cycle seems to be the thing as SELECT CAST(129 as tinyint) AS Negative127 is -127.

SparkSQL Databricks [INVALID_USAGE_OF_STAR_OR_REGEX] Invalid usage of '*' in expression `alias`.

SparkSQL Databricks Error in SQL statement: AnalysisException: [INVALID_USAGE_OF_STAR_OR_REGEX] Invalid usage of '*' in expression `alias`.

Hi, it's me. I'm the problem

Dear self, when you recieve the following error Error in SQL statement: AnalysisException: [INVALID_USAGE_OF_STAR_OR_REGEX] Invalid usage of '*' in expression `alias` writing new-to-you sparksql and assuming the TSQL construct you know works just doesn't translate, take a good look at the syntax because I bet you've doubled up the FROM, again!
SELECT * FROM FROM uc.schema.table AS X WHERE X.col1 = 0

Make that
SELECT * FROM uc.schema.table AS X WHERE X.col1 = 0