How do I resolve "COLUMN_NOT_FOUND" errors in Athena?

Short description

When a specified column isn't in the table's schema definition, you receive one of the following error messages:

• "COLUMN_NOT_FOUND: line 1:8: Column 'column_name' cannot be resolved or requester is not authorized to access requested resources"
• "COLUMN_NOT_FOUND: line 1:8: Relation contains no accessible columns"

If you don't have the correct AWS Lake Formation permissions to access the table, then you receive one of the following error messages:

• "INVALID_VIEW: line 1:15: Failed analyzing stored view 'awsdatacatalog.database-name.view-name': line 1:8: Relation contains no accessible columns"
• "COLUMN_NOT_FOUND: Column 'column_name' cannot be resolved or requester is not authorized to access requested resources"

Troubleshoot the issue based on the error messge that you receive.

Resolution

Confirm that the column is in the table's definition

A column might not be in the table's schema definition because it doesn't exist. To confirm that the column exists in the table's schema, use either the AWS Glue console or the AWS CLI.

AWS Glue console

Complete the following steps:

1. Open the AWS Glue console.
2. In the navigation pane, choose Data Catalog, and then choose Tables.
3. Select the table.
4. Under Schema, view the schema to confirm that the table exists.

AWS CLI

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Run the following get-table CLI command:

aws glue get-table --database-name database-name --name table-name

Note: Replace database-name and table-name with the name of your database and table.

Add the column to the table's definition

If the column doesn't exist in the table's schema definition, then use either the AWS Glue console or Athena query editor to add the column.

AWS Glue console

Complete the following steps:

1. Open the AWS Glue console.
2. In the navigation pane, choose Data Catalog, and then choose Tables.
3. Select the table, and then choose Edit schema.
4. Choose Add,
5. In the Add schema entry window, add values to the following sections:
   For Column #, enter the column number that you want to insert your new column after. For example, if you want your new column to be the third column in your table's schema, then enter 2.
   For Name, enter the name for your new column.
   For Data type, choose a data type.
6. Choose Save, and then choose Save as new table version.

Athena query editor

Complete the following steps:

1. Open the Athena console.
2. In the navigation pane, choose Query editor.
3. Run the following ALTER TABLE ADD COLUMNS DDL statement:

   ALTER TABLE table-name ADD COLUMNS (column-name column-data-type)

   Note: Replace table-name with your table's name. Also, replace column-name and column-data-type with the name and data type that you want for your new column.

Grant Lake Formation permissions to query a table or view in the same AWS account

Complete the following steps:

1. Open the Lake Formation console as a data lake administrator.
2. In the navigation pane, choose Tables or Views.
3. Select the table or view, and then choose Actions.
4. In the dropdown menu, choose Grant.
5. Under Principals, choose IAM users and roles.
6. For IAM users and roles, select the AWS Identity and Access Management (IAM) user or principal that you're granting access to.
7. Under LF-Tags or catalog resources, choose Named Data Catalog resources, and then select the catalog, database, and table or view.
8. For Table permissions or View permissions and Grantable permissions, select the access permissions that you want to grant.
   Note: You must choose Select and Describe to query the table or view.
9. Choose Grant.

Note: If you use data filters, then make sure that you correctly set up Column-level access. If you choose Include columns, then be sure to include your column. If you choose Exclude columns, then don't exclude your column.

Grant permissions to query a table in a different account

First, create a resource link. Then, grant permissions for the resource link and target table.

To grant permissions for the resource link, complete the following steps:

1. In the destination account, open the Lake Formation console as a data lake administrator.
2. In the navigation pane, choose Tables.
3. Select the resource link, and then choose Actions.
4. In the dropdown menu, choose Grant.
5. Under Principals, choose IAM users and roles.
6. For IAM users and roles, select the IAM user or principal that you're granting access to.
7. Under LF-Tags or catalog resources, select the catalog, database, and table.
8. For Resource link permissions and Grantable permissions, choose Describe.
9. Choose Grant.

To grant permissions on the target table, repeat the preceding steps. Choose Grant on target for step 4, and choose Select for step 8.

Related information

How can I resolve the "Column not found" error with the Athena DynamoDB connector?