Ingest Cloudtrail lake table via Athean to Quicksight - DDL issue?

Question

I connected Cloudtrail Lake with Athena. In Athena I get this table. From QuickSight I want to create a dataset and use the Athena connector however I get error message:

Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.

In Athena I have this table:
CREATE EXTERNAL TABLE `7908a9eb-2b22-4e58-a9e5-77c466fbff7e`(
  `eventversion` string, 
  `useridentity` struct,sessionissuer:struct,webidfederationdata:struct>,sourceidentity:string,ec2roledelivery:string,ec2issuedinvpc:string,assumedroot:boolean>,invokedby:string,identityprovider:string,credentialid:string,onbehalfof:struct,inscopeof:struct>, 
  `eventtime` timestamp, 
  `eventsource` string, 
  `eventname` string, 
  `awsregion` string, 
  `sourceipaddress` string, 
  `useragent` string, 
  `errorcode` string, 
  `errormessage` string, 
  `requestparameters` map, 
  `responseelements` map, 
  `additionaleventdata` map, 
  `requestid` string, 
  `eventid` string, 
  `readonly` boolean, 
  `resources` array>, 
  `eventtype` string, 
  `apiversion` string, 
  `managementevent` boolean, 
  `recipientaccountid` string, 
  `sharedeventid` string, 
  `annotation` string, 
  `vpcendpointid` string, 
  `vpcendpointaccountid` string, 
  `serviceeventdetails` map, 
  `addendum` map, 
  `edgedevicedetails` map, 
  `insightdetails` map, 
  `eventcategory` string, 
  `tlsdetails` struct, 
  `sessioncredentialfromconsole` string, 
  `eventjson` string)
PARTITIONED BY ( 
  `calendarday` bigint, 
  `hour` bigint)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
WITH SERDEPROPERTIES ( 
  'orc.column.index.access'='false') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'arn:aws:cloudtrail:us-east-1::eventdatastore/7908a9eb-2b22-4e58-a9e5-77c466fbff7e'
TBLPROPERTIES (
)

Could this be a DDL issue?  Unsure how to fix it. Been try for hours and no luck.

re:Post Agent · Answer

The error message you're encountering suggests that there might be an issue with the SQL query or the database settings, rather than a specific DDL problem. However, there are a few things you can check and try to resolve this issue:

1. Verify that the Athena workgroup associated with your QuickSight data source exists and is enabled. Sometimes, workgroup configuration issues can cause SQL exceptions.

2. Ensure that QuickSight has the necessary permissions to access the S3 output location associated with the Athena workgroup. The QuickSight administrator may need to edit the Security & Permissions settings in the Manage QuickSight screen to grant these permissions.

3. Check that the Athena workgroup has an associated S3 output location. This needs to be set up correctly in the Athena console.

4. The table structure you've provided is quite complex, with nested structs and maps. QuickSight might be having trouble interpreting this structure. You could try creating a view in Athena that flattens some of the complex structures, and then use that view in QuickSight.

5. If you're using a custom SQL query in QuickSight, double-check the syntax. Ensure that you're specifying the schema name along with the table name (e.g., my_schema.my_table).

6. The table name (7908a9eb-2b22-4e58-a9e5-77c466fbff7e) is unusual and might be causing issues. Try creating a view with a simpler name and use that in QuickSight.

7. Verify that the columns you're trying to use in QuickSight exist in the Athena table. Sometimes, schema mismatches can cause SQL exceptions.

If none of these solutions work, you may need to contact AWS support for further assistance, as the issue could be related to the specific configuration of your CloudTrail Lake, Athena, and QuickSight setup.  
**Sources**  
[SPICE ingestion error codes - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/errors-spice-ingestion.html)  
[Workgroup and output errors when using Athena with Amazon QuickSight - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/troubleshoot-athena-workgroup.html)  
[Table not found when using Athena with Amazon QuickSight - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/troubleshoot-athena-table-not-found.html)  
[Column not found when using Athena with Amazon QuickSight - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/troubleshoot-athena-column-not-found.html)

Ingest Cloudtrail lake table via Athean to Quicksight - DDL issue?

Relevant content