[Simba][Hardy] (80) Syntax or semantic analysis error thrown in server

[jackf]

I'm attempting to reconfigure my connection to Databricks via Alteryx (had it setup perfectly prior) and seem to be running into a common issue. When attempting to write to a table within Databricks I receive the following error messages: 

1. 

Error: Write Data In-DB (206): Executing PreSQL: "CREATE TABLE `cdp`.`spf_xpn_output_in` AS SELECT `MASTER_PERSON_ID`, `MASTER_ADDRESS_ID`, `IRID`, `PROJECT_NUMBER`, `CONTROL_..." : [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error running query: org.apache.spark.sql.catalyst.parser.ParseException: mismatched input 'SELECT' expecting <EOF>(line 1, pos 579)== SQL ==CREATE TABLE `cdp`.`spf_xpn_output_in` AS SELECT C_700, C_693, C_680, C_665, C_677, C_657, C_697, C_666, C_679, C_653, C_701, C_688, C_667, C_664, C_668, C_656, C_669, C_671, C_674, C_691, C_704, C_658, C_670, C_694, C_705, C_649, C_706, C_702, C_672, C_681, C_661, C_703, C_698, C_648, C_651, C_695, C_652, C_676, C_650, C_673, C_654, C_675, C_655, C_678, C_659, C_696, C_660, C_662, C_683, C_663, C_684, C_685, C_686, C_687, C_689, C_699, C_690, C_692, C_720, C_716, C_710, C_714, C_718, C_713, C_709, C_708, C_711, C_717, C_721, C_707, C_722, C_719, C_712, C_715, C_682 FROM SELECT C_574 AS C_700, C_575 AS C_693, C_570 AS C_680, C_571 AS C_665, C_572 AS C_677, C_576 AS C_657, C_577 AS C_697, C_573 AS C_666, C_578 AS C_679, C_608 AS C_653, C_604 AS C

2. 

Error: Write Data In-DB (206): Error running PreSQL on "NoTable": [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error running query: org.apache.spark.sql.catalyst.parser.ParseException: mismatched input 'SELECT' expecting <EOF>(line 1, pos 579)== SQL ==CREATE TABLE `cdp`.`spf_xpn_output_in` AS SELECT C_700, C_693, C_680, C_665, C_677, C_657, C_697, C_666, C_679, C_653, C_701, C_688, C_667, C_664, C_668, C_656, C_669, C_671, C_674, C_691, C_704, C_658, C_670, C_694, C_705, C_649, C_706, C_702, C_672, C_681, C_661, C_703, C_698, C_648, C_651, C_695, C_652, C_676, C_650, C_673, C_654, C_675, C_655, C_678, C_659, C_696, C_660, C_662, C_683, C_663, C_684, C_685, C_686, C_687, C_689, C_699, C_690, C_692, C_720, C_716, C_710, C_714, C_718, C_713, C_709, C_708, C_711, C_717, C_721, C_707, C_722, C_719, C_712, C_715, C_682 FROM SELECT C_574 AS C_700, C_575 AS C_693, C_570 AS C_680, C_571 AS C_665, C_572 AS C_677, C_576 AS C_657, C_577 AS C_697, C_573 AS C_666, C_578 AS C_679, C_608 AS C_653, C_604 AS C

I've done plenty of research on the topic and have updated my settings by setting hive.stats.autogather=false and hive.resultset.use.unique.column.names=false on the Server Side Property based on the following post: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Error-quot-SQLPrepare-Simba-Hardy-8...

My issue is slightly different though, I can read and write to any other table in Databricks on my current cluser as I'm able to write to the other table within the workflow without error. The issue only arises when I attempt to write to the specifc table in question. For reference, the table is a staging table where we drop & overwrite the data on every run so schema is fixed the entire time.

Since the process joins multiple tables and appends their data, which is then used to upload to this 'staging' table, the underlying SQL query is faily long. The error in position 579 is immediately after the "FROM" in the query. Unfortunately, changing the schema is not an option.

[jackf · Accepted solution]

Issue has been resolved:

• Removed previously setup user DSN
• Moved to Simba Spark (System) DSN
• Updated "Table/FieldName SQL Style" to None