Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Error with Simba Hive ODBC Driver: 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 Skyscrapersidea Skyscrapers

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

idea Skyscrapersidea 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 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 Skyscrapersidea 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. 

lepome
Alteryx Alumni (Retired)

@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

FADLI
5 - Atom

Hi HenrietteH,

 

I'm having a similar problem. I'm trying to import table on Data services Designer but when I import then i have a Syntax or semantic analysis error ( cannot recognize input near 'SHOW' 'INDEX' 'ON' ).

I tried to use server side properties to disable indexes but it still doesn't work.

 

Do you have any idea ? 

 

Thanx, 

 

Wassim

lepome
Alteryx Alumni (Retired)

@FADLI 
It would be easier to offer you assistance if you included the full text of the query along with the error message in context.  If you don't want to include the details, then you can either edit it or show an image where any sensitive strings are replaced by xxx or blurred.  You are trying to show the syntax, though, so try to do it in a way such that the reserved words (commands) and punctuation and operators are visible.  For example, using the image in the article above, 
example.png

 

is vastly more helpful than 


Please don't do thisPlease don't do this

 

I know this example is absurd, but I hope you get the idea.

Neha
7 - Meteor

Hi @HenrietteH 

Can you please help me understand why I am getting a similar error? I am connecting to Data lake via simba and I am able to extract all tables and columns with whatever query combination I like. But when i try to get table information by using DESC/Description, this error pops up. Any idea why?  I am new to alteryx and not able to understand what the issue might be here.

Neha_0-1652158189048.png

Thanks in advance for your help here!

i_love_databases
8 - Asteroid

Hello,

Just for your information, we solved that issue by writing directly on hdfs instead of using ODBC