Importing Athena table gives error TABLE_NOT_FOUND - "No columns are found in the source"

Question & Answer
1

I’m trying to add a table from Athena for analysis. I’m able to query the data in Athena without any problems, but when creating a dataset in Quicksight I get

sourceErrorCode: TABLE_NOT_FOUND
sourceErrorMessage: No columns are found in the source. (considered as table not found.)

I have tried googling and searching this forum but without any luck. I guess it’s a permission problem somewhere, but I can’t figure out where. I have granted access to Athena and S3 buckets (all I can think of that might be appropriate for Quicksight) with write-permissions just to be sure.

Please, can anyone shed some light on this?

Thanks,

2

Hi @Sewen - Welcome to AWS QuickSight community and thanks for posting the question. Did you add Athena and S3 services from QuickSight console. Can you please go to

Qucksight → Manage QuickSight → Security & Permission → QuickSight access to AWS services → select Athena and S3 and provide the require S3 bucket.

After doing it, please try to access Athena table, if possible share the screenshot as well.

Regards - Sanjeeb

1 Like
3

Hi @Sanjeeb2022,

Yes I have added the permissions:

image
image813×527 32.5 KB

Added all s3-buckets that I can think of being important (tried adding all as well) including write-permissions. Looking at the role and policies in IAM it looks like the permissions are there, but not sure what the exact permissions should be.

4

Hi @Sewen - Can you please see the below blog . Please provide write permission as well to S3.

Regards - Sanjeeb

1 Like
5

Hi @Sanjeeb2022,

I have granted both read and write permissions to all buckets that are used by Athena.
image

What else could it be?

6

Oh, just figured out that the content of one of the buckets might be encrypted. I guess that might mess things up. Will investigate further.

1 Like
7

Keep us posted @Sewen on whether this gets resolved. @Sanjeeb2022 thanks for sharing your incredible expertise with the Community. :tada:

1 Like
8

No, that didn’t help. I tried recreating all buckets and tables and again grant permissions to all of them in Quicksight. Tried with a test-table with less data to see if there’s some problems there. Double checked that there is no encryption enabled either. Not sure what my next steps will be.

I can query successfully in Athena, but not from Quicksight. Always getting the same error
sourceErrorCode: TABLE_NOT_FOUND
sourceErrorMessage: No columns are found in the source. (considered as table not found.)

I also tried with a custom SQL query with this output:

image
image1220×770 66.9 KB

SourceErrorCode: 100071
SourceErrorMessage: [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. COLUMN_NOT_FOUND: line 2:27: Column ‘member_id’ cannot be resolved [Execution ID: 0bcde15b-a1bf-4b0e-a7ad-789e3ebc2f0d]

Not sure what to try next.

9

Hi @Sewen - From the errors, looks like member_id column has some issue. Can you see the data and its type.

Regards - Sanjeeb

10

This is what it looks like in Athena. Now I emptied the table and only have one row:

image
image2582×1188 253 KB

11

Hi @Sewen - Ok, the data looks ok. Can you create a new session and try to connect this table and see whether it is able to import it from QuickSight or not. If not, better to please raise a ticket to AWS Customer support team so that they can see your environment by screen share and advise you. To raise a ticket, please follow the link - Creating support cases and case management - AWS Support.

If you do not have access, request your aws admin to help you on this.

Regards - Sanjeeb

12

Hi @Sewen - Also, can you create test table with 2 rows and put some dummy data and test it, just to ensure you do not have access in connection from QuickSight to Athena and the issue is pure related to data.

Regards - Sanjeeb

1 Like
13

This is a dummy table with some dummy data. The original table is much bigger both in terms of columns as well as number rows.

I can see in CloudTrail that Quicksight is executing this query:
SELECT ds.* FROM ( SELECT member_id, created_at from "training"."test_table" ) ds LIMIT 0;
Which doesn’t return any data for me either in Athena. What is that query supposed to do?

14

Oh, but I still get the columns in Athena, but no data. I guess that is what it should return.

15

I compared the event when I do a query in Athena with the one made from Quicksight.

This is my user making the query:

"requestParameters": {
        "queryString": "SELECT ds.* FROM ( SELECT member_id, created_at from \"training\".\"test_table\" ) ds LIMIT 0;",
        "clientRequestToken": "ccf87771-4891-4ea5-a682-9e414112f7ab",
        "queryExecutionContext": {
            "database": "training",
            "catalog": "AwsDataCatalog"
        },
        "resultConfiguration": {
            "outputLocation": "s3://adv-x-datalake-athena-711805833800/Unsaved/2023/05/06/"
        },
        "workGroup": "primary",
        "resultReuseConfiguration": {
            "resultReuseByAgeConfiguration": {
                "enabled": false
            }
        }
    },

This is Quicksight:

"requestParameters": {
        "queryString": "/* QuickSight */\nSELECT ds.* FROM ( SELECT member_id, created_at from \"training\".\"test_table\" ) ds LIMIT 0",
        "clientRequestToken": "1f4d91a2-91bd-4a0a-849b-da9d3c478ab9",
        "queryExecutionContext": {
            "database": "default"
        },
        "resultConfiguration": {
            "outputLocation": "s3://aws-athena-query-results-eu-north-1-711805833800"
        },
        "workGroup": "primary"
    },

Seems to be two differences:

  1. The output location. I cannot see any files written to the outputLocation defined for Quicksight. It has access to putObject according to IAM policy though.
  2. The Database for Quicksight is default? I guess that might not be a problem since I do define the table in the query, but not sure.
16

In the Schema Explorer in Quicksight it gives the same results. No columns found.

image
image734×1026 39.9 KB

Are there any special permissions needed for viewing the schema? In Glue I have defined the table using the Glue Schema Registry. But it should take the schema from the table, right?

Anything other with the table creation that might mess things up?

17

Hi @Sewen - Can you please drop and recreate this table. Looks like something is wrong.

Regards - Sanjeeb

1 Like
18

It’s a permission error after all. I gave the aws-quicksight-service-role-v0 an inline policy of


{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "*",
            "Resource": "*"
        }
    ]
}

And then everything works. I will try to narrow it down to what is missing.

1 Like
19

Thanks @Sewen . Please do a test and update this community and marked your analysis as solution so that it can useful to the whole community. sometime a simple permission issue took time to troubleshoot but good part with that phase you are learning so many new things.

Regards - Sanjeeb

1 Like
20

I ended up giving the IAM role aws-quicksight-service-role-v0 this inline policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "glue:GetSchemaVersion",
                "glue:ListSchemaVersions"
            ],
            "Resource": [
                "*"
            ]
        }
    ]
}

And that solved my problem. Seems the role didn’t have permissions to list and get the schema versions for my tables. @Kristin, perhaps those actions should be added to the managed policy AWSQuicksightAthenaAccess? Let me know if you want more information about my setup so that no-one else ends up with the same problem as I have.

Thanks @Sanjeeb2022 for all you efforts trying to help me! Very much appreciated!

1 Like