-- Use this script to create Athena tables for k9 Security CSV reports.
--
-- NOTE: set your own <SECURE_INBOX_BUCKET_NAME> in LOCATION

-- DROP TABLE principals_latest;

CREATE EXTERNAL TABLE `principals_latest` (
  `analysis_time` timestamp,
  `principal_name` string,
  `principal_arn` string,
  `principal_type` string,
  `principal_is_iam_admin` boolean,
  `principal_last_used` timestamp,
  `principal_tag_business_unit` string,
  `principal_tag_environment` string,
  `principal_tag_used_by` string,
  `principal_tags` map<string,string>,
  `password_last_used` timestamp,
  `password_last_rotated` timestamp,
  `password_state` string,
  `access_key_1_last_used` timestamp,
  `access_key_1_last_rotated` timestamp,
  `access_key_1_state` string,
  `access_key_2_last_used` timestamp,
  `access_key_2_last_rotated` timestamp,
  `access_key_2_state` string
)

PARTITIONED BY (
  `customer_id` string,
  `csp` string,
  `account_id` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<SECURE_INBOX_BUCKET_NAME>/hive/parquet/principals'
TBLPROPERTIES (
  'parquet.compression'='SNAPPY')

-- DROP TABLE principal_access_summaries_latest;

CREATE EXTERNAL TABLE `principal_access_summaries_latest`(
  `analysis_time` timestamp,
  `principal_name` string,
  `principal_arn` string,
  `principal_type` string,
  `principal_tags` map<string,string>,
  `service_name` string,
  `access_capability` string,
  `resource_arn` string,
  `resource_tag_confidentiality` string)
PARTITIONED BY (
  `customer_id` string,
  `csp` string,
  `account_id` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<SECURE_INBOX_BUCKET_NAME>/hive/parquet/pas'
TBLPROPERTIES (
  'parquet.compression'='SNAPPY');

-- DROP TABLE resource_access_summaries_latest;

CREATE EXTERNAL TABLE `resource_access_summaries_latest`(
  `analysis_time` timestamp,
  `service_name` string,
  `resource_name` string,
  `resource_arn` string,
  `access_capability` string,
  `principal_type` string,
  `principal_name` string,
  `principal_arn` string,
  `resource_tag_confidentiality` string)
PARTITIONED BY (
  `customer_id` string,
  `csp` string,
  `account_id` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<SECURE_INBOX_BUCKET_NAME>/hive/parquet/ras'
TBLPROPERTIES (
  'parquet.compression'='SNAPPY');

-- Prepare the tables for querying by loading the partitions
MSCK REPAIR TABLE principal_access_summaries_latest;
MSCK REPAIR TABLE resource_access_summaries_latest;
