Most often, when we run a query in Amazon Athena, we come across the error, Hive_unknown_error “Unable to create input format”

Here, at Bobcares, we assist our customers with several AWS queries as part of our AWS Support Services.

Today, let us see how our techs fix this error in different scenarios.

 

Hive_unknown_error “Unable to create input format”

There are multiple causes of this error. In this article, our Support Techs introduce some common scenarios and solutions.

• The AWS Glue crawler can’t classify the data format

Generally, the data is stored in Amazon S3.

To detect the table schema we run an AWS Glue crawler with a built-in classifier. However, it returns a classification of UNKNOWN.

It can detect at least one column, but the schema is incorrect.

And when we query the table from Athena, the query fails with the error.

To resolve this, we use a data type that a built-in classifier supports.

If we cannot classify the data format by it, then we consider using a custom classifier.

• Athena doesn’t support the data format

In this case, when we run a crawler to create the table, it classifies the table in a format that Athena doesn’t support, such as ion or XML.

This will eventually lead us to the error when we query the table from Athena.

In order to resolve this, we use a data format that Athena supports.

• One or more of the AWS Glue table definition properties are empty

Usually, the AWS Glue table isn’t created in Athena or by an AWS Glue crawler. However, we can create the table manually on the AWS Glue console.

While doing this when we query the table from Athena it can fail with the error.

This can happen because one or more of the following properties in the AWS Glue table definition is empty:

1. Input format
2. Output format
3. Serde name

We need to make sure these are set correctly for the SerDe and data format.

In addition, make note that the SerDe that we specify defines the table schema.

To update the table definition properties, our Support Techs suggest the steps below:

1. Initially, we open the AWS Glue console.
2. Then we select the table that we want to update.
3. Here we select, Action > View details >Edit table.
4. Now we can update the settings for Input format, Output format, or Serde name.
5. Eventually, we select, Apply.

• The data source in the Athena query does not support

In Athena, we can only query tables if they are in Amazon S3. Otherwise, we have to face the error.

To resolve this, we use Athena Query Federation SDK. It allows customizing Athena with our own code.

[Need help with either of the cases? We are available 24*7 for you]

 

Conclusion

In short, we saw how our Support Techs fix the Amazon Athena error for our customers.