redshift-spectrum-cloudfront-logs

mock-cf-log.md

0. EC2配置S3写权限，可以给EC2配置角色 也可以配置aws ak/sk

1. EC2下载cloudfront log模拟脚本，并安装依赖

sudo yum install python3-devel  -y
sudo yum group install "Development Tools" -y
wget https://d3gpeimdfrgv8u.cloudfront.net/clo-workshop-smb/alb-cf-log-faker.zip
unzip alb-cf-log-faker.zip
cd alb
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

2.更改cloudfront.py文件中日志文件存放位置配置

log_bucket_name = 'xxxxx'

3.运行如下命令模拟生成CloudFront日志供后续分析

python cloudfront.py
Finish generate 100 lines log in 0.067295 secs

redshift-spectrum-cloudfront-logs.sql

create external schema myspectrum_schema 
from data catalog 
database 'myspectrum_db' 
iam_role default
create external database if not exists;

CREATE EXTERNAL TABLE myspectrum_schema.cloudfront_logs (
  day DATE,
  day_time varchar(255),
  location varchar(255),
  bytes integer,
  request_ip varchar(255),
  method varchar(255),
  host varchar(255),
  uri varchar(255),
  status integer,
  referrer varchar(255),
  user_agent varchar(255),
  query_string varchar(255),
  cookie varchar(255),
  result_type varchar(255),
  request_id varchar(255),
  host_header varchar(255),
  request_protocol varchar(255),
  request_bytes integer,
  time_taken decimal(8,2),
  xforwarded_for varchar(255),
  ssl_protocol varchar(255),
  ssl_cipher varchar(255),
  response_result_type varchar(255),
  http_version varchar(255),
  fle_status varchar(255),
  fle_encrypted_fields integer,
  c_port integer,
  time_to_first_byte decimal(8,2),
  x_edge_detailed_result_type varchar(255),
  sc_content_type varchar(255),
  sc_content_len integer,
  sc_range_start integer,
  sc_range_end integer
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://mock-logs-3245345435/AWSLogs/CloudFrontLogs/'

select * from myspectrum_schema.cloudfront_logs