10-30-2020 11:42 AM - edited 04-12-2023 05:42 AM
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.
Diagnosis
In the Visual Query Builder, the column names are prefaced by an 'a':
In the Output tool, you can see the table name precede the column name in the field map:
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.
Solution B: Add a Server Side Property to the DSN
Note: This is not a global setting, it is set for each DSN, individually.
hive.resultset.use.unique.column.namesand set it to false
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.
hive.resultset.use.unique.column.namesand set it to false
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.
So glad this helped!!
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
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.
@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
I was getting the same error so, I applied Solution B provided by @Henriette
but still I am facing the same error. Below is the workflow I have created
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
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.
Hi Rishabh @jainrishabh .
I think it is because of the ";", if you try to delete it, it should work.
Steven
Thank you steven4320555
and HenrietteH
I have removed ';' and 'limit 100' from the Query builder and it worked for me
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
@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,
is vastly more helpful than
I know this example is absurd, but I hope you get the idea.
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.
Thanks in advance for your help here!
Hello,
Just for your information, we solved that issue by writing directly on hdfs instead of using ODBC