How do I resolve the error "unable to create input format" in Athena?

3 minute read
0

When I run a query in Amazon Athena, I get the error "unable to create input format".

Resolution

There are multiple causes of this error. Here are some common scenarios and solutions:

The AWS Glue crawler can't classify the data format

  • The data is stored in Amazon Simple Storage Service (Amazon S3).
  • You run an AWS Glue crawler with a built-in classifier to detect the table schema. The crawler returns a classification of UNKNOWN. At least one column is detected, but the schema is incorrect.
  • When you query the table from Athena, the query fails with the error "HIVE_UNKNOWN_ERROR: Unable to create input format".

To resolve this error, use a data type that is supported by a built-in classifier. If the data format can't be classified by a built-in classifier, then consider using a custom classifier.

Athena doesn't support the data format

  • The data is stored in Amazon S3.
  • You run a crawler to create the table. The crawler classifies the table in a format that Athena doesn't support, such as ion or xml.
  • When you query the table from Athena, the query fails with the error "HIVE_UNKNOWN_ERROR: Unable to create input format".

To resolve this error, use a data format that Athena supports.

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

  • The AWS Glue table isn't created in Athena or by an AWS Glue crawler. The table is created using any other method. For example, the table is created manually on the AWS Glue console.
  • When you query the table from Athena, the query fails with the error "HIVE_UNKNOWN_ERROR: Unable to create input format".

This error occurs because one or more of the following properties in the AWS Glue table definition are empty:

  • Input format
  • Output format
  • Serde name

Confirm that these properties are set correctly for the SerDe and data format. Keep in mind that the SerDe that you specify defines the table schema. The SerDe can override the DDL configuration that you specify in Athena when you create your table.

To update the table definition properties, do the following:

  1. Open the AWS Glue console.
  2. Select the table that you want to update.
  3. Choose Action, and then choose View details.
  4. Choose Edit table.
  5. Update the settings for Input format, Output format, or Serde name.
  6. Choose Apply.

The data source in your Athena query is not supported

Athena supports querying tables only if the tables are stored in Amazon S3. You might get the "unable to create input format" error if you query a data source that's not supported by Athena.

To resolve this error, use Athena Query Federation SDK. The SDK allows you to customize Athena with your own code. With Athena Federation SDK, you can integrate with different data sources and proprietary data formats. You can also build new user-defined functions. For more information, see Query any data source with Amazon Athena’s new federated query.


Related information

Adding classifiers to a crawler

Using a SerDe

AWS OFFICIAL
AWS OFFICIALUpdated 3 years ago