Load and Query AWS CloudTrail direct from DuckDB

duckdb_cloudtrail_load.sql

INSTALL AWS;
LOAD AWS;
CALL load_aws_credentials();
CREATE TABLE ct_raw AS SELECT * FROM read_json('s3://YOUR_CT_BUCKET_WITH_A_DATE_PREFIX/*.gz', maximum_depth=2);
CREATE TABLE ct as SELECT unnest(Records) as Event FROM ct_raw;
CREATE TABLE cloudtrail_events AS SELECT   json_extract_string(event, '$.eventVersion') AS eventVersion,
    json_extract_string(event, '$.userIdentity.type') AS userType,
    json_extract_string(event, '$.userIdentity.principalId') AS principalId,
    json_extract_string(event, '$.userIdentity.arn') AS userArn,
    json_extract_string(event, '$.userIdentity.accountId') AS accountId,
    json_extract_string(event, '$.userIdentity.accessKeyId') AS accessKeyId,
    json_extract_string(event, '$.userIdentity.userName') AS userName,
    CAST(json_extract_string(event, '$.eventTime') AS TIMESTAMP) AS eventTime,    
    json_extract_string(event, '$.eventSource') AS eventSource,
    json_extract_string(event, '$.eventName') AS eventName,
    json_extract_string(event, '$.awsRegion') AS awsRegion,
    json_extract_string(event, '$.sourceIPAddress') AS sourceIPAddress,
    json_extract_string(event, '$.userAgent') AS userAgent,
    json_extract_string(event, '$.errorCode') AS errorCode,
    json_extract_string(event, '$.errorMessage') AS errorMessage,
    json_extract(event, '$.requestParameters') AS requestParameters,
    json_extract(event, '$.responseElements') as responseElements,
    json_extract(event, '$.resources') AS resources,
  FROM ct