How do I resolve the "GENERIC_INTERNAL_ERROR" error when I query a table in Athena?

Resolution

"GENERIC_INTERNAL_ERROR: null"

Column data type mismatch

Be sure that the column data type in the table definition is compatible with the column data type in the source data. Athena uses schema-on-read technology. When Athena processes the queries, it applies your table definitions to your data in Amazon Simple Storage Service (Amazon S3).

For example, when you create a table in an Apache Parquet file, Athena reads the schema from the file. Then, Athena validates the schema against the table definition where you query the Parquet. If the data type of a column doesn't match the table definition's data type, then you get the "Column data type mismatch" error.

If there's a schema mismatch between the source data files and table definition, then take one of the following actions:

• Use AWS Glue to update the schema.
• Use the updated table definition to create a new table.

Also, check whether the source data files are corrupt. If you find corrupt source data files, then delete the files and query the table.

Inaccurate syntax

You might get the "GENERIC INTERNAL ERROR: null" error when both of the following conditions are true:

• You used a CTAS query to create the table.
• You used the same column name for the partitioned_by and bucketed_by table properties.

When you use the CTAS query, you must use different column names for the partitioned_by and bucketed_by properties. To resolve this issue, create a new table and choose different column names for partitioned_by and bucketed_by.

"GENERIC_INTERNAL_ERROR: parent builder is null"

You might get this error when you query a table that has array columns and the OpenCSVSerde format. The OpenCSVSerde format doesn't support the array data type.

To resolve this issue, change the column with the array data type to string. You can either use the AWS Glue console, or run a command to modify the column's data type. Or, create a new table.

Modify the column's data type

To use the AWS Glue console, complete the following steps:

1. Open the AWS Glue console.
2. In the navigation pane, choose Tables.
3. Select the table that you want to update.
4. Choose Action, and then choose View details.
5. Choose Edit schema.
6. Find the column with data type array, and then choose array.
7. For Column type, select string on the dropdown list.
8. Choose Update.
9. On the Edit schema page, choose Save.

-or-

Run the SHOW CREATE TABLE command to generate the query that created the table. Find the column with the array data type, and then change the data type to string.

Create a new table

Create a new table, and choose string for the column data type.

"GENERIC_INTERNAL_ERROR: Value exceeds MAX_INT"

You might get this error when a cell in a source INT column has a numeric value that's greater than 2,147,483,647.

To resolve this issue, change the column with the int data type to bigint. You can either use the AWS Glue console, or run a command to modify the column's data type. Or, create a new table.

Modify the column's data type

To use the AWS Glue console, complete the following steps:

1. Open the AWS Glue console.
2. In the navigation pane, choose Tables.
3. Select the table that you want to update.
4. Choose Action, and then choose View details.
5. Choose Edit schema.
6. Find the column with the int data type, and then choose int.
7. For Column type, select bigint on the dropdown list.
8. Choose Update.
9. On the Edit schema page, choose Save.

-or-

Run the SHOW CREATE TABLE command to generate the query that created the table. Find the column with the int data type, and then change the data type to bigint.

Create a new table

Create a new table, and choose bigint for the column data type.

"GENERIC_INTERNAL_ERROR: Value exceeds MAX_BYTE"

You might get this error when a cell in a source TINYINT column has a numeric value that isn't valid. A TINYINT column's minimum value is -128, and its maximum value is 127.

To resolve this issue, change the column with the tinyint data type to smallint, int, or bigint. You can either use the AWS Glue console, or run a command to modify the column's data type. Or, create a new table.

Modify the column's data type

To use the AWS Glue console, complete the following steps:

1. Open the AWS Glue console.
2. In the navigation pane, choose Tables.
3. Select the table that you want to update.
4. Choose Action, and then choose View details.
5. Choose Edit schema.
6. Find the column with data type tinyint, and then choose tinyinit.
7. For Column type, select smallint, bigint, or int on the dropdown list.
8. Choose Update.
9. On the Edit schema page, choose Save.

-or-

Run the SHOW CREATE TABLE command to generate the query that created the table. Find the column with the tinyint data type, and then change the data type to smallint, bigint, or int.

Create a new table

Create a new table, and choose smallint, bigint, or int for the column data type.

"GENERIC_INTERNAL_ERROR: Number of partition values does not match number of filters"

You might get this error when you have inconsistent partitions on Amazon S3 data for one of the following reasons:

• You changed your S3 partitions. For example, you added new partitions.
• The number of partition columns in the table doesn't match the number of partition columns in the partition metadata.

To resolve this issue, you can configure an AWS Glue crawler to create a new table. Or, use the ALTER TABLE DROP PARTITION statement to drop the partitions. Then, use the ALTER TABLE ADD PARTITION statement to add the same number of partitions as in the table definition.

For example, you have partition columns that are named date and country in the table definition and a partition that has only one column that's named date. Drop the partition with the date column, and then add the date and country partitions to the table.

Example commands:

ALTER TABLE doc_example_table DROP PARTITION (date = '2014-05-14');    
ALTER TABLE doc_example_table ADD PARTITION (date = '2016-05-14', country = 'IN');

"GENERIC_INTERNAL_ERROR: Multiple entries with same key"

If you have columns with the same name, then you must pre-process the data to include a valid key-value pair. To ignore columns with the same name, set ignore.malformed.json as SERDEPROPERTIES in org.openx.data.jsonserde.JsonSerDe.

Apache Hive doesn't support case sensitive columns. For example, you might experience an issue when you have two columns that are named Column and column.

If the column names are the same but in different cases, then you must use mapping and configure the JSON SerDe to be case insensitive. 

Example command:

CREATE TABLE mytable (    
  time1 string, 
  time2 string)
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
"case.insensitive" = "false", --tells hive to ignore key case
"mapping.time1"= "time", -- lowercase 'time' mapped into 'time1'
"mapping.time2"= "Time") -- uppercase to 'time2'

Related information

Data types in Amazon Athena

Partition your data