Athena engine version 3

For engine version 3, Athena has introduced a continuous integration approach to open source software management that improves concurrency with the Trino and Presto projects so that you get faster access to community improvements, integrated and tuned within the Athena engine.

This release of Athena engine version 3 supports all the features of Athena engine version 2. This document highlights key differences between Athena engine version 2 and Athena engine version 3.

• Getting started

• Improvements and new features

  • Added Features

  • Added Functions

  • Performance improvements

  • Reliability enhancements

  • Query syntax enhancements

  • Data format and data type enhancements

• Limitations

• Breaking changes

  • Query syntax changes

  • Data processing changes

  • Timestamp changes

Getting started

To get started, either create a new Athena workgroup that uses Athena engine version 3 or configure an existing workgroup to use version 3. Any Athena workgroup can upgrade from engine version 2 to engine version 3 without interruption in your ability to submit queries.

For more information, see Changing Athena engine versions.

Improvements and new features

The features and updates listed include improvements from Athena itself and from functionality incorporated from open source Trino. For an exhaustive list of SQL query operators and functions, refer to the Trino documentation.

Added Features

Apache Spark bucketing algorithm support

Athena can read buckets generated by the Spark hash algorithm. To specify that data was originally written by the Spark hash algorithm, put ('bucketing_format'='spark') in the TBLPROPERTIES clause of your CREATE TABLE statement. If this property is not specified, the Hive hash algorithm is used.

CREATE EXTERNAL TABLE `spark_bucket_table`(
  `id` int, 
  `name` string
  )
CLUSTERED BY (`name`) 
INTO 8 BUCKETS
STORED AS PARQUET
LOCATION 
  's3://path/to/bucketed/table/'
TBLPROPERTIES ('bucketing_format'='spark')

Added Functions

The functions in this section are new to Athena engine version 3.

Aggregate functions

listagg(x, separator) – Returns the concatenated input values, separated by the separator string.

SELECT listagg(value, ',') WITHIN GROUP (ORDER BY value) csv_value 
FROM (VALUES 'a', 'c', 'b') t(value);

Array functions

contains_sequence(x, seq) – Returns true if array x contains all array seq as a sequential subset (all values in the same consecutive order).

SELECT contains_sequence(ARRAY [1,2,3,4,5,6], ARRAY[1,2]);

Binary functions

murmur3(binary) – Computes the 128-bit MurmurHash3 hash of binary.

SELECT murmur3(from_base64('aaaaaa'));

Conversion functions

format_number(number) – Returns a formatted string using a unit symbol.

SELECT format_number(123456); -- '123K'

SELECT format_number(1000000); -- '1M'

Date and time functions

timezone_hour(timestamp) – Returns the hour of the time zone offset from timestamp.

SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP '2020-05-10 12:34:56 +08:35');

timezone_minute(timestamp) – Returns the minute of the time zone offset from timestamp.

SELECT EXTRACT(TIMEZONE_MINUTE FROM TIMESTAMP '2020-05-10 12:34:56 +08:35');

Geospatial functions

to_encoded_polyline(Geometry) – Encodes a linestring or multipoint to a polyline.

SELECT to_encoded_polyline(ST_GeometryFromText(
   'LINESTRING (-120.2 38.5, -120.95 40.7, -126.453 43.252)'));

from_encoded_polyline(varchar) – Decodes a polyline to a linestring.

SELECT ST_AsText(from_encoded_polyline('_p~iF~ps|U_ulLnnqC_mqNvxq`@'));         

to_geojson_geometry(SphericalGeography) – Returns the specified spherical geography in GeoJSON format.

SELECT to_geojson_geometry(to_spherical_geography(ST_GeometryFromText(
   'LINESTRING (0 0, 1 2, 3 4)')));

from_geojson_geometry(varchar) – Returns the spherical geography type object from the GeoJSON representation, stripping non geometry key/values. Feature and FeatureCollection are not supported.

SELECT from_geojson_geometry(to_geojson_geometry(to_spherical_geography(ST_GeometryFromText(
   'LINESTRING (0 0, 1 2, 3 4)'))));

geometry_nearest_points(Geometry, Geometry) – Returns the points on each geometry that are nearest each other. If either geometry is empty, returns NULL. Otherwise, returns a row of two Point objects that have the minimum distance of any two points on the geometries. The first point is from the first Geometry argument, the second from the second Geometry argument. If there are multiple pairs with the same minimum distance, one pair is chosen arbitrarily.

SELECT geometry_nearest_points(ST_GeometryFromText(
   'LINESTRING (50 100, 50 200)'), ST_GeometryFromText(
   'LINESTRING (10 10, 20 20)'));

Set Digest functions

make_set_digest(x) – Composes all input values of x into a setdigest.

SELECT make_set_digest(value) FROM (VALUES 1, 2, 3) T(value);

String functions

soundex(char) – Returns a character string that contains the phonetic representation of char.

SELECT name 
FROM nation 
WHERE SOUNDEX(name) = SOUNDEX('CHYNA'); -- CHINA

concat_ws(string0, string1, ..., stringN) – Returns the concatenation of string1, string2, ..., stringN using string0 as a separator. If string0 is null, then the return value is null. Any null values provided in the arguments after the separator are skipped.

SELECT concat_ws(',', 'def', 'pqr', 'mno');

Window functions

GROUPS – Adds support for window frames based on groups.

SELECT array_agg(a) OVER(
   ORDER BY a ASC NULLS FIRST GROUPS BETWEEN 1 PRECEDING AND 2 FOLLOWING) 
FROM (VALUES 3, 3, 3, 2, 2, 1, null, null) T(a);

Performance improvements

Performance improvements in Athena engine version 3 include the following.

• Faster AWS Glue table metadata retrieval – Queries that involve multiple tables will see reduced query planning time.

• Dynamic filtering for RIGHT JOIN – Dynamic filtering is now enabled for right joins that have equality join conditions, as in the following example.

  SELECT * 
  FROM lineitem RIGHT JOIN tpch.tiny.supplier 
  ON lineitem.suppkey = supplier.suppkey 
  WHERE supplier.name = 'abc';

• Large prepared statements – Increased the default HTTP request/response header size to 2 MB to allow large prepared statements.

Reliability enhancements

General engine memory usage and tracking in Athena engine version 3 have been improved. Large queries are less susceptible to failure from node crashes.

Query syntax enhancements

INTERSECT ALL – Added support for INTERSECT ALL.

SELECT * FROM (VALUES 1, 2, 3, 4) INTERSECT ALL SELECT * FROM (VALUES 3, 4);

EXCEPT ALL – Added support for EXCEPT ALL.

SELECT * FROM (VALUES 1, 2, 3, 4) EXCEPT ALL SELECT * FROM (VALUES 3, 4);

RANGE PRECEDING – Added support for RANGE PRECEDING in window functions.

SELECT sum(x) over (order by x range 1 preceding) 
FROM (values (1), (1), (2), (2)) t(x);

MATCH_RECOGNIZE – Added support for row pattern matching, as in the following example.

SELECT m.id AS row_id, m.match, m.val, m.label 
FROM (VALUES(1, 90),(2, 80),(3, 70),(4, 70)) t(id, value) 
MATCH_RECOGNIZE ( 
        ORDER BY id 
        MEASURES match_number() AS match, 
        RUNNING LAST(value) AS val, 
        classifier() AS label 
        ALL ROWS PER MATCH 
        AFTER MATCH SKIP PAST LAST ROW 
        PATTERN (() | A) DEFINE A AS true 
) AS m;

Data format and data type enhancements

Athena engine version 3 has the following data format and data type enhancements.

• LZ4 and ZSTD – Added support for reading LZ4 and ZSTD compressed Parquet data. Added support for writing ZSTD compressed ORC data.

• Symlink-based tables – Added support for creating symlink-based tables on Avro files. An example follows.

  CREATE TABLE test_avro_symlink  
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'  
  ... 
  INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'

• SphericalGeography – The SphericalGeography type provides native support for spatial features represented on geographic coordinates (sometimes called geodetic coordinates, lat/lon, or lon/lat). Geographic coordinates are spherical coordinates expressed in angular units (degrees).

  The to_spherical_geography function returns geographic (spherical) coordinates from geometric (planar) coordinates, as in the following example.

  SELECT to_spherical_geography(ST_GeometryFromText(
     'LINESTRING (-40.2 28.9, -40.2 31.9, -37.2 31.9)'));

Limitations

Athena engine version 3 has the following limitations.

• Query performance – Many queries run faster on Athena engine version 3, but some query plans can differ from Athena engine version 2. As a result, some queries can differ in latency or cost.

• Trino and Presto connectors – Neither Trino nor Presto connectors are supported. Use Amazon Athena Federated Query to connect data sources. For more information, see Using Amazon Athena Federated Query.

• Fault-tolerant execution – Trino fault-tolerant execution (Trino Tardigrade) is not supported.

• Function parameter limit – Functions cannot take more than 127 parameters. For more information, see Too many arguments for function call.

Breaking changes

When you migrate from Athena engine version 2 to Athena engine version 3, certain changes can affect table schema, syntax, or data type usage. This section lists the associated error messages and provides suggested workarounds.

Query syntax changes

BOOL_OR() and BOOL_AND() cannot be used together with null treatment

Error message: Cannot specify null treatment clause for bool_or function.

Cause: Ignore nulls cannot be added for the BOOL_OR () function. This change was made to conform to the ANSI SQL specification.

Suggested solution: Remove ignore nulls from the query strings.

CONCAT function must have two or more arguments

Error Message: INVALID_FUNCTION_ARGUMENT: There must be two or more concatenation arguments

Cause: Previously, the CONCAT string function accepted a single argument. In Athena engine version 3, the CONCAT function requires a minimum of two arguments.

Suggested solution: Change occurrences of CONCAT(str) to CONCAT(str, '').

In Athena engine version 3, functions can have no more than 127 arguments. For more information, see Too many arguments for function call.

Geospatial function does not support varbinary input

Error message: FUNCTION_NOT_FOUND for st_XXX

Cause: A few geospatial functions no longer support the legacy VARBINARY input type or text related function signatures.

Suggested solution: Use geospatial functions to convert the input types to types that are supported. Supported input types are indicated in the error message.

In GROUP BY clauses, nested columns must be double quoted

Error message: "column_name"."nested_column" must be an aggregate expression or appear in GROUP BY clause

Cause: Athena engine version 3 requires that nested column names in GROUP BY clauses be double quoted. For example, the following query produces the error because, in the GROUP BY clause, user.name is not double quoted .

SELECT "user"."name" FROM dataset 
GROUP BY user.name

Suggested solution: Place double quotes around nested column names in GROUP BY clauses, as in the following example.

SELECT "user"."name" FROM dataset 
GROUP BY "user"."name"

Minute() function does not support interval year to month

Error message: Unexpected parameters (interval year to month) for function minute. Expected: minute(timestamp with time zone) , minute(time with time zone) , minute(timestamp) , minute(time) , minute(interval day to second).

Cause: In Athena engine version 3, type checks have been made more precise for EXTRACT in accordance with the ANSI SQL specification.

Suggested solution: Update the queries to make sure types are matched with the suggested function signatures.

ORDER BY expressions must appear in SELECT list

Error message: For SELECT DISTINCT, ORDER BY expressions must appear in SELECT list

Cause: Incorrect table aliasing is used in a SELECT clause.

Suggested solution: Double check that all columns in the ORDER BY expression have proper references in the SELECT DISTINCT clause.

Query failure when comparing multiple columns returned from a subquery

Example error message: Value expression and result of subquery must be of the same type: row(varchar, varchar) vs row(row(varchar, varchar))

Cause: Due to a syntax update in Athena engine version 3, this error occurs when a query tries to compare multiple values returned from a subquery, and the subquery SELECT statement encloses its list of columns in parentheses, as in the following example.

SELECT *
FROM table1
WHERE (t1_col1, t1_col2)
IN (SELECT (t2_col1, t2_col2) FROM table2)

Solution: In Athena engine version 3, remove the parenthesis around the list of columns in the subquery SELECT statement, as in the following updated example query.

SELECT *
FROM table1
WHERE (t1_col1, t1_col2)
IN (SELECT t2_col1, t2_col2 FROM table2)

SKIP is a reserved word for DML queries

The word SKIP is now a reserved word for DML queries like SELECT. To use SKIP as an identifier in a DML query, enclose it in double quotes.

For more information about reserved words in Athena, see Reserved keywords.

SYSTEM_TIME and SYSTEM_VERSION clauses deprecated for time travel

Error message: mismatched input 'SYSTEM_TIME'. Expecting: 'TIMESTAMP', 'VERSION'

Cause: In Athena engine version 2, Iceberg tables used the FOR SYSTEM_TIME AS OF and FOR SYSTEM_VERSION AS OF clauses for timestamp and version time travel. Athena engine version 3 uses the FOR TIMESTAMP AS OF and FOR VERSION AS OF clauses.

Suggested solution: Update the SQL query to use the TIMESTAMP AS OF and VERSION AS OF clauses for time travel operations, as in the following examples.

Time travel by timestamp:

SELECT * FROM TABLE FOR TIMESTAMP AS OF (current_timestamp - interval '1' day)

Time travel by version:

SELECT * FROM TABLE FOR VERSION AS OF 949530903748831860

Too many arguments for an array constructor

Error Message: TOO_MANY_ARGUMENTS: Too many arguments for array constructor.

Cause: The maximum number of elements in an array constructor is now set at 254.

Suggested solution: Break up the elements into multiple arrays that have 254 or fewer elements each, and use the CONCAT function to concatenate the arrays, as in the following example.

CONCAT(
ARRAY[x1,x2,x3...x254],
ARRAY[y1,y2,y3...y254],
...
)

Zero-length delimited identifier not allowed

Error message: Zero-length delimited identifier not allowed.

Cause: A query used an empty string as a column alias.

Suggested solution: Update the query to use a non-empty alias for the column.

Data processing changes

Bucket validation

Error Message: HIVE_INVALID_BUCKET_FILES: Hive table is corrupt.

Cause: The table might have been corrupted. To ensure query correctness for bucketed tables, Athena engine version 3 enables additional validation on bucketed tables to ensure query correctness and avoid unexpected failures at runtime.

Suggested solution: Re-create the table using Athena engine version 3.

Casting a struct to JSON now returns field names

When you cast a struct to JSON in a SELECT query in Athena engine version 3, the cast now returns both the field names and the values (for example "useragent":null instead of just the values (for example, null).

Iceberg table column level security enforcement change

Error Message: Access Denied: Cannot select from columns

Cause: The Iceberg table was created outside Athena and uses an Apache Iceberg SDK version earlier than 0.13.0. Because earlier SDK versions do not populate columns in AWS Glue, Lake Formation could not determine the columns authorized for access.

Suggested solution: Perform an update using the Athena ALTER TABLE SET PROPERTIES statement or use the latest Iceberg SDK to fix the table and update the column information in AWS Glue.

Nulls in List data types are now propagated to UDFs

Error message: Null Pointer Exception

Cause: This issue can affect you if you use the UDF connector and have implemented a user defined Lambda function.

Athena engine version 2 filtered out the nulls in List data types that were passed to a user defined function. In Athena engine version 3, the nulls are now preserved and passed on to the UDF. This can cause a null pointer exception if the UDF attempts to dereference the null element without checking.

For example, if you have the data [null, 1, null, 2, 3, 4] in an originating data source like DynamoDB, the following are passed to the user-defined Lambda function:

Athena engine version 2: [1,2,3,4]

Athena engine version 3: [null, 1, null, 2, 3, 4]

Suggested solution: Ensure that your user-defined Lambda function handles null elements in list data types.

Substrings from character arrays no longer contain padded spaces

Error message: No error is thrown, but the string returned no longer contains padded spaces. For example, substr(char[20],1,100) now returns a string with length 20 instead of 100.

Suggested solution: No action is required.

Unsupported decimal column type coercion

Error message: Unsupported column type (varchar) for Parquet column ([XXX] optional binary column

Cause: Athena engine version 2 occasionally succeeded (but frequently failed) when attempting data type coercions from varchar to decimal. Because Athena engine version 3 has type validation that checks that the type is compatible before it tries to read the value, such attempted coercions now always fail.

Suggested Solution: For both Athena engine version 2 and Athena engine version 3, modify your schema in AWS Glue to use a numeric data type instead of varchar for decimal columns in Parquet files.

uuid() function return type change

Error message: NOT_SUPPORTED: Unsupported Hive type: uuid.

Cause: In Athena engine version 2, the uuid() function returned a string, but in Athena engine version 3, it returns a pseudo randomly generated UUID (type 4). Because the UUID column data type is not supported in Athena, the uuid() function can no longer be used directly in CTAS queries to generate UUID columns in Athena engine version 3.

For example, the following query completes successfully in Athena engine version 2 but returns the error mentioned in Athena engine version 3:

CREATE TABLE uuid_test AS 
   SELECT uuid() AS myuuid

Suggested Solution: Use the cast() function to convert the output of uuid() to a varchar, as in the following example:

CREATE TABLE uuid_testv3 AS
   SELECT cast(uuid() AS varchar) as myuuid;

Timestamp changes

Casting a Timestamp with time zone to varchar behavior change

In Athena engine version 2, casting a Timestamp with time zone to varchar caused some time zone literals to change (for example, US/Eastern changed to America/New_York). This behavior does not occur in Athena engine version 3.

Date timestamp overflow throws error

Error message: Millis overflow: XXX

Cause: Because ISO 8601 dates were not checked for overflow in Athena engine version 2, some dates produced a negative timestamp. Athena engine version 3 checks for this overflow and throws an exception.

Suggested Solution: Make sure the timestamp is within range.

Political time zones with TIME not supported

Error message: INVALID LITERAL

Cause: Queries like SELECT TIME '13:21:32.424 America/Los_Angeles'.

Suggested solution: Avoid using political time zones with TIME.

Precision mismatch in Timestamp columns causes serialization error

Error message: SERIALIZATION_ERROR: Could not serialize column 'COLUMNZ' of type 'timestamp(3)' at position X:Y

COLUMNZ is the output name of the column that causes the issue. The numbers X:Y indicate the position of the column in the output.

Cause: Athena engine version 3 checks to make sure that the precision of timestamps in the data is the same as the precision specified for the column data type in the table specification. Currently, this precision is always 3. If the data has a precision greater than this, queries fail with the error noted.

Suggested solution: Check your data to make sure that your timestamps have millisecond precision.

Incorrect timestamp precision in UNLOAD and CTAS queries for Iceberg tables

Error message: Incorrect timestamp precision for timestamp(6); the configured precision is MILLISECONDS

Cause: Athena engine version 3 checks to make sure that the precision of timestamps in the data is the same as the precision specified for the column data type in the table specification. Currently, this precision is always 3. If the data has a precision greater than this (for example, microseconds instead of milliseconds), queries can fail with the error noted.

Solution: To work around this issue, CAST the timestamp precision to milliseconds, as in the following CTAS example.

CREATE TABLE my_iceberg_ctas 
WITH (external_location = 's3://DOC-EXAMPLE-BUCKET/table_ctas/',
       format = 'PARQUET') 
AS SELECT id, CAST(dt AS timestamp(3)) AS "dt" 
FROM my_iceberg

Reading the Long type as Timestamp in ORC files now causes a malformed ORC file error

Error message: Error opening Hive split ‘FILE (SPLIT POSITION)’ Malformed ORC file. Cannot read SQL type timestamp from ORC stream .long_type of type LONG

Cause: Athena engine version 3 now rejects implicit coercion from the Long data type to Timestamp. Previously, Long values were implicitly converted into timestamp as if they were epoch milliseconds.

Suggested solution: Use the from_unixtime function to explicitly cast the column, or use the from_unixtime function to create an additional column for future queries.

Time and interval year to month not supported

Error message: TYPE MISMATCH

Cause: Athena engine version 3 does not support time and interval year to month (for example, SELECT TIME '01:00' + INTERVAL '3' MONTH).

Timestamp overflow for int96 Parquet format

Error message: Invalid timeOfDayNanos

Cause: A timestamp overflow for the int96 Parquet format.

Suggested solution: Identify the specific files that have the issue. Then generate the data file again with an up-to-date, well known Parquet library, or use Athena CTAS. If the issue persists, contact Athena support and let us know how the data files are generated.

Space required between date and time values when casting from string to timestamp

Error message: INVALID_CAST_ARGUMENT: Value cannot be cast to timestamp.

Cause: Athena engine version 3 no longer accepts a hyphen as a valid separator between date and time values in the input string to cast. For example, the following query works in Athena engine version 2 but not in Athena engine version 3:

SELECT CAST('2021-06-06-23:38:46' AS timestamp) AS this_time

Suggested solution: In Athena engine version 3, replace the hyphen between the date and the time with a space, as in the following example.

SELECT CAST('2021-06-06 23:38:46' AS timestamp) AS this_time

to_iso8601() timestamp return value change

Error message: None

Cause: In Athena engine version 2, the to_iso8601 function returns a timestamp with time zone even if the value passed to the function does not include the time zone. In Athena engine version 3, the to_iso8601 function returns a timestamp with time zone only when the argument passed includes the time zone.

For example, the following query passes the current date to the to_iso8601 function twice: first as a timestamp with time zone, and then as a timestamp.

SELECT TO_ISO8601(CAST(CURRENT_DATE AS TIMESTAMP WITH TIME ZONE)), TO_ISO8601(CAST(CURRENT_DATE AS TIMESTAMP))

The following output shows the result of the query in each engine.

Athena engine version 2:

#	_col0	_col1
1	2023-02-24T00:00:00.000Z	2023-02-24T00:00:00.000Z

Athena engine version 3:

#	_col0	_col1
1	2023-02-24T00:00:00.000Z	2023-02-24T00:00:00.000

Suggested solution: To replicate the previous behaviour, you can pass the timestamp value to the with_timezone function before passing it to to_iso8601, as in the following example:

SELECT to_iso8601(with_timezone(TIMESTAMP '2023-01-01 00:00:00.000', 'UTC'))

Result

#	_col0
1	2023-01-01T00:00:00.000Z

at_timezone() first parameter must specify a date

Issue: In Athena engine version 3, the at_timezone function cannot take a time_with_timezone value as the first parameter.

Cause: Without date information, it cannot be determined whether the value passed is daylight time or standard time. For example, at_timezone('12:00:00 UTC', 'America/Los_Angeles') is ambiguous since there is no way to determine whether the value passed is Pacific Daylight Time (PDT) or Pacific Standard Time (PST).