How do I resolve the "FAILED: NullPointerException Name is null" error when I query a table in Amazon Athena?

Short description

You get this error when the attribute TableType isn't defined for the queried table in the AWS Glue Data Catalog. The TableType attribute defines whether the table is an external table or a view. This attribute can be defined with values, such as EXTERNAL_TABLE and VIRTUAL_VIEW. If you want to run DDL queries, such as SHOW CREATE TABLE or MSCK REPAIR TABLE, then you must define the TableType attribute.

If you defined the table using an AWS CloudFormation template or the AWS Glue API without specifying TableType as one of the properties, then you might get this error.

Resolution

To resolve this error, do one or more of the following based on your use case:

Add the attribute during table creation

Add the TableType attribute when you create the table.

Note: If the table is created using a DDL statement or an AWS Glue crawler, then the TableType property is defined automatically.

Update the CloudFormation template or the AWS Glue API call

If the table is defined using a CloudFormation template or the AWS Glue API without specifying TableType, then update the CloudFormation template or the AWS Glue API call to add the TableType attribute.

Update the table using the AWS Command Line Interface (AWS CLI)

To update the TableType attribute for your table, use the AWS CLI command aws glue update-table . To run this command, you must have the TableInput object that defines the entire table architecture.

To get the TableInput object for your table, run the aws glue get-table command. Then, update the output of this command as defined in the following steps.

Note: If you receive errors when running AWS CLI commands, make sure that you’re using the most recent version of the AWS CLI.

1.    Run a command similar to the following on your table.

aws glue get-table --catalog-id 1111222233334444 --database doc_example_database --name doc_example_table

2.    You get an output similar to the following:

{
    "Table": {
            "StorageDescriptor": {
            "OutputFormat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat",
            "SortColumns": [],
            "InputFormat": "org.apache.hadoop.mapred.TextInputFormat",
            "SerdeInfo": {
                    "SerializationLibrary": "org.apache.hadoop.hive.serde2.OpenCSVSerde",
                    "Parameters": {
                    "serialization.format": "1"
                        }
            },
            "Parameters": {
                "separatorChar": ","
            },
            "Location": "s3://doc_example_bucket/doc_example_prefix/",
            "NumberOfBuckets": 0,
            "StoredAsSubDirectories": false,
            "Columns": [
                {
                    "Type": "int",
                    "Name": "id"
                },
                {
                    "Type": "string",
                    "Name": "name"
                }
            ],
            "Compressed": false
        },
        "UpdateTime": 1620508098.0,
        "IsRegisteredWithLakeFormation": false,
        "Name": "doc_example_table",
        "CreatedBy": "arn:aws:iam::1111222233334444:user/Administrator",
        "DatabaseName": "doc_example_database",
        "Owner": "1111222233334444",
        "Retention": 0,
        "CreateTime": 1619909955.0,
        "Description": "tb description"
    }
}

3.    Remove parameters, such as UpdateTime, IsRegisteredWithLakeFormation, CreatedBy, DatabaseName, and CreateTime from the above output. AWS Glue doesn't support these parameters. If you include these parameters in the TableInput attribute when running the update-table command, then you might see the following errors:

Parameter validation failed:
Unknown parameter in TableInput: "UpdateTime", must be one of: Name, Description, Owner, LastAccessTime, LastAnalyzedTime, Retention, StorageDescriptor, PartitionKeys, ViewOriginalText, ViewExpandedText, TableType, Parameters
Unknown parameter in TableInput: "IsRegisteredWithLakeFormation", must be one of: Name, Description, Owner, LastAccessTime, LastAnalyzedTime, Retention, StorageDescriptor, PartitionKeys, ViewOriginalText, ViewExpandedText, TableType, Parameters
Unknown parameter in TableInput: "CreatedBy", must be one of: Name, Description, Owner, LastAccessTime, LastAnalyzedTime, Retention, StorageDescriptor, PartitionKeys, ViewOriginalText, ViewExpandedText, TableType, Parameters
Unknown parameter in TableInput: "DatabaseName", must be one of: Name, Description, Owner, LastAccessTime, LastAnalyzedTime, Retention, StorageDescriptor, PartitionKeys, ViewOriginalText, ViewExpandedText, TableType, Parameters
Unknown parameter in TableInput: "CreateTime", must be one of: Name, Description, Owner, LastAccessTime, LastAnalyzedTime, Retention, StorageDescriptor, PartitionKeys, ViewOriginalText, ViewExpandedText, TableType, Parameters

4.    Add the parameter "TableType": "EXTERNAL_TABLE" to the output.

5.    Use the output as the TableInput parameter to run the update-table command.

aws glue update-table --catalog-id 1111222233334444 --database-name doc_example_database --table-input'{
        "StorageDescriptor": {
            "OutputFormat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat",
            "SortColumns": [],
            "InputFormat": "org.apache.hadoop.mapred.TextInputFormat",
            "SerdeInfo": {
                "SerializationLibrary": "org.apache.hadoop.hive.serde2.OpenCSVSerde",
                "Parameters": {
                    "serialization.format":"1"
                }
            },
            "Parameters": {
                "separatorChar":","
            },
            "Location": "s3://doc_example_bucket/doc_example_prefix/",
            "NumberOfBuckets": 0,
            "StoredAsSubDirectories": false,
            "Columns": [
                {
                    "Type": "int",
                    "Name": "id"
                },
                {
                    "Type": "string",
                    "Name": "name"
                }
            ],
            "Compressed": false
        },
        "Name": "doc_example_table",
        "TableType": "EXTERNAL_TABLE",
        "Owner": "1111222233334444",
        "Retention": 0,
        "Description": "tb description"
    }

Be sure to replace the following in the above commands:

• doc_example_database with the name of your database
• doc_example_table with the name of your table
• 1111222233334444 with your account ID
• s3://doc_example_bucket/doc_example_prefix/ with the Amazon Simple Storage Service (Amazon S3) location where you stored the table

After running the above command, the TableType parameter gets updated, and DDL queries, such as SHOW CREATE TABLE or MSCK REPAIR TABLE, are successful.

Related information

Troubleshooting in Athena