Downloading data with complex types from Amazon Athena via JDBC

Question

When I try to run

// java.sql.ResultSet
val fieldNameData = resultSet.getObject("fieldName", classOf[Map[String, String]])

It throws

Exception in thread "main" java.sql.SQLFeatureNotSupportedException: getObject
    at com.amazonaws.athena.jdbc.AthenaResultSet.getObject(AthenaResultSet.java:1754)

It means that Athena JDBC driver doesn't support complex types such as map and array. What is another approach for downloading data with these types?

Answer 1

The most direct way is to use Presto map functions to convert map data into primitives the JDBC driver can work with. The simplest is just to index into the map:

SELECT
  favorites['fruit'] as favorite_fruit,
  favorites['vegetable'] as favorite_vegetable
FROM
(
  SELECT
    MAP(ARRAY['fruit', 'vegetable'], ARRAY['apple', 'broccoli']) as favorites
) as food;

You could also use Presto's UNNEST syntax to join to a map and process it as rows in the results. For example:

SELECT
  f.food_type,
  f.favorite_food
FROM
(
  SELECT
    MAP(ARRAY['fruit', 'vegetable'], ARRAY['apple', 'broccoli']) as favorites
) as food
CROSS JOIN UNNEST (favorites) AS f (food_type, favorite_food);