ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Error: "SQLPrepare: [Simba][Hardy] (80) Syntax or semantic analysis" thrown in server while executing query: Invalid column reference

HenrietteH
Alteryx
Alteryx
Created

Environment Details


User is getting the following error while reading data from Hadoop Hive using the Input tool or streaming it out using the In-DB Data Stream Out tool
Input Data (1) Error SQLPrepare: [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 1:33 Invalid column reference 'a.storenum' 



Alternatively, user is getting the following error trying to write to Hadoop Hive with the Output tool:

Output Data (2)    Unable to find field "test" in output record.

 

  • Alteryx Designer
    • All versions
  • Simba Hive ODBC Driver with DSN configured
    • Version 2.6 or greater



Diagnosis
 

In the Visual Query Builder, the column names are prefaced by an 'a': 

idea Skyscrapers

In the Output tool, you can see the table name precede the column name in the field map:

idea Skyscrapers


In the Select In-DB tool, the table name is part of the column name in the format tablename.columname: 




Note

The below resolutions apply only if the error appears in the context of this change to the column names. 

Cause


Simba introduced a new feature in their driver that ensures that all column names are unique when reading in data. This feature is resulting in Alteryx not being able to recognize column names. It is a hidden feature that must be turned off in the Registry if the user can't edit the query to avoid the error.  



Resolution


Solution A: Use Select * [...] in the query


1. In the Alteryx workflow, open the Input tool that is throwing the error.
2. Go to the SQL Editor tab.
3. Remove references to individual columns from the query and use the wildcard asterisk (*) instead to read in all columns in the table.

idea Skyscrapers



Solution B: Add a Server Side Property to the DSN


Note: This is not a global setting, it is set for each DSN, individually. 
  1. Go to the Windows ODBC Data Source Administrator
  2. Open the DSN that is being used to connect for editing
  3. Click on Advanced Options
  4. Click on Server Side Properties
  5. Add the property 
    hive.resultset.use.unique.column.names
    and set it to false
  6. Hit OK on all the windows to save the changes


Solution C: Add a Server Side Property to the Driver Configuration

Note: This is a global setting, it will apply to ALL DSNs using the driver. 

 
  1. Browse to the install folder for the Simba Hive ODBC driver. The default location is C:\Program Files\Simba Hive ODBC Driver
  2. Open the /lib folder inside the install folder 
  3. Double-click DriverConfiguration64.exe to open the driver configuration dialog
  4. Click on Advanced Options
  5. Click on Server Side Properties
  6. Add the property 
    hive.resultset.use.unique.column.names
    and set it to false
  7. Hit OK on all the windows to save the changes
Comments
steven4320555
8 - Asteroid

Thanks for the solution, I went for the 2nd option, and it solved my problem immediately.

Without understanding the behaviour, and changed the setting, I had to use remove pre-fix for every table I brought in.

HenrietteH
Alteryx
Alteryx

Hi @steven4320555 

So glad this helped!! 

Ken_Black
9 - Comet
9 - Comet

Hi Henriette,

 

I'm having a similar problem. I'm trying to write a simple table to Hadoop. The table is getting created fine but the content of the data stream is being considered as "?".

 

To greatly simplify the situation, I've chosen only 10 records and one field. The error message shown in the second graphic is when I tried to write the first five fields. The same error happens whether I'm writing 1 field or all of them. 

 

Do you have any insights? I've used this style workflow in the past to write to hadoop without any trouble.

 

Thanks,

 

Ken

 

 

10 records and I'm only trying to write the first column10 records and I'm only trying to write the first columnThe error message is similar to the one shown in this threadThe error message is similar to the one shown in this thread

HenrietteH
Alteryx
Alteryx

Hi @Ken_Black 

The (?,?,?,?,?) are placeholders for the data which is being sent in batches for faster loading. So it is ok that they are showing up. 

 

I would start by pulling some ODBC logs (see here for instructions) and see if you can find something in there. If not, you can reach out to support@alteryx.com to have them help you. 

 

Another thing you could check is the Advanced Options in the driver, we recommend not to check the "Use Native Query" option. Unchecking that option allows the driver to convert the query to HiveQL, otherwise the driver will assume that the query is already in HiveQL and you might get errors. 

LisaL
Alteryx
Alteryx

@Ken_Black 
I ran into that error message in a different context, but what I did to resolve it was to add another Server Side Property:

hive.stats.autogather=false

jainrishabh
6 - Meteoroid

I was getting the same error so, I applied Solution B provided by @

but still I am facing the same error. Below is the workflow I have created

 

jainrishabh_0-1615834284734.png

 

 

 

 

 

In my above workflow there are around many columns in Database which I am filtering using In-DB Filter tool, In Formula tool I am applying one date format rule on which I want to group by using Summarize tool.

 

But while doing this I am getting below error:

 

Error: Formula In-DB (75): Error SQLPrepare: [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: ParseException line 1:6901 cannot recognize input near 'LIMIT' '100' ';' in limit clause

 

Can someone please help me to get this resolve.


Thanks in advance,

Rishabh

 

HenrietteH
Alteryx
Alteryx

Hi @jainrishabh 

Looking at the error message, there is a LIMIT clause in the formula tool that is breaking the syntax of the query and causing the error. I would review the content of the formula tool. 

steven4320555
8 - Asteroid

Hi Rishabh @jainrishabh .

I think it is because of the ";", if you try to delete it, it should work.

Steven

jainrishabh
6 - Meteoroid

Thank you

and 

I have removed ';' and 'limit 100' from the Query builder and it worked for me