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

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