Athena Engine Version 3 - Incompatibilities using Lambda UDFs and CTAS?

1

Hi all,

Ahead of the scheduled auto-upgrades to Athena Engine Version 3, I am testing out some queries. In particular, we have a set of queries that invoke lambda-based UDFs with the USING EXTERNAL FUNCTION clause as part of a CTAS (create table as select)

Last week, these queries were failing with the error: mismatched input 'USING'. Expecting: '(', <query> . Now, as of this morning they are failing with [ErrorCode: INTERNAL_ERROR_QUERY_ENGINE] Amazon Athena experienced an internal error while executing this query. Please contact AWS support for further assistance. You will not be charged for this query. We apologize for the inconvenience.

I am able to run queries that invoke UDFs without the CTAS, and CTAS queries that simply define a UDF without invoking it fail for the same reason as above, so it apparent to me that the error has something to do with how the combination of a CTAS and UDF are interpreted. What is the best practice for running CTAS that require UDFs on Athena Engine Version 3?

Update: per the Athena product team, this is a bug that will be addressed in a subsequent regular engine release.

asked 10 months ago507 views
1 Answer
0

Hello,

The error "mismatched input 'USING'. Expecting: '(', <query> " can occur if the USING EXTERNAL FUNCTION clause is not properly formatted, or if there is a missing or incorrect parenthesis or comma.

Here are a few possible causes of this error:

• Missing or incorrect parenthesis: The error message suggests that the query is expecting a parenthesis after the USING keyword. If there is a missing or incorrect parenthesis in the query, this error can occur.

• Incorrect formatting of USING EXTERNAL FUNCTION clause: The USING EXTERNAL FUNCTION clause must be properly formatted with the name of the UDF and the input and output types. If any of these elements are missing or incorrectly formatted, the error can occur.

• Incorrect use of commas: The error message also suggests that there may be an issue with the commas in the query. If there is a missing or extra comma in the query, this error can occur.

To resolve this error, we request you to carefully review the query and check for any missing or incorrect parentheses, commas, or formatting issues with the USING EXTERNAL FUNCTION clause.

Additionally please have a look at the considerations and limitations for CTAS queries documentation below:

[+] Considerations and limitations for CTAS queries: https://docs.aws.amazon.com/athena/latest/ug/ctas-considerations-limitations.html

I understand that you are able to run queries that invoke UDFs without CTAS and as explained above one reason is facing a syntax error:

  • CTAS query syntax errors: It's possible that there are syntax errors in the CTAS query syntax that are causing issues when using UDFs. These errors could be related to the USING EXTERNAL FUNCTION clause or other syntax elements in the query.

When running CTAS queries that require User-Defined Functions (UDFs) on Athena Engine Version 3, there are a few best practices:

• Ensure that your UDFs are compatible with Athena Engine Version 3. Make sure that the UDFs you are using are compatible with the latest version of Athena Engine. You can check the AWS documentation to see if there are any known compatibility issues with your UDFs and Athena Engine Version 3.

• Test your queries in a staging environment. Before running CTAS queries in a production environment, it's a good idea to test your queries in a staging environment. This will allow you to identify any issues before they affect your production data.

• Use the latest version of the Athena JDBC driver. Make sure you are using the latest version of the Athena JDBC driver to avoid any compatibility issues.

• Check for syntax errors. Check your queries for syntax errors, including any issues with the USING EXTERNAL FUNCTION clause.

Below are the documents regarding Athena engine version 3 and few examples of CTAS queries:

[+] Athena engine version 3: https://docs.aws.amazon.com/athena/latest/ug/engine-versions-reference-0003.html
[+] Examples of CTAS queries: https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html

AWS
SUPPORT ENGINEER
answered 10 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions