CDP Hive ImportJob failing with limit

Follow

Amalan, Daniel

• September 23, 2022 16:35
• Edited

Hi, trying to setup a CDP Hive ImportJob with the following select query with limit 2 but getting an error. Anyone facing this problem? 

Query:

select * FROM abc.table1 limit 2

Error:

Can not parse input: Error while compiling statement: FAILED: ParseException line 1:46 cannot recognize input near 'limit' '2' 'LIMIT' in limit clause

0

• 

  Konsta Danyliuk

  • September 22, 2022 10:04

  Hello Daniel.

  When you create a new ImportJob, Datameer reads a few records from the data source to detect the dataset's schema and show the preview at the DefineFields step.

  When one sets up an ImportJob via UI and provides a custom SQL query, Datameer adds to this query the LIMIT clause to get only a few records for the schema detection and the preview. If the custom query already contains the LIMIT clause, it would mean that Datameer is trying to execute the query like SELECT a, b FROM table LIMIT 2 LIMIT 10, which will fail with an SQL syntax error.

  A workaround for this would be to create an ImportJob via UI using the query without the LIMIT clause and then update this artifact via REST API, adding the LIMIT directly in the JSON definition.

  Here is the appropriate chapter of the Datameer documentation - Update Import/ Link/ Upload.

   

  I hope this addresses your concern.

  0

  Comment actions Permalink
• 

  Amalan, Daniel

  • September 23, 2022 16:36

  Thanks Konsta. 

  0

  Comment actions Permalink

Please sign in to leave a comment.