Duckdb schema ALB access logs

duckdb_logs_alb.md

# file: read_alb_logs.sql
--general configs
.mode duckbox
.prompt '🦆◗ '

-- get current credentials (including region)
CREATE SECRET s3credentials (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN
);

-- create table called 'logs_alb' using an specific log file
CREATE TABLE alb_logs AS SELECT * FROM 
-- YOU MUST CHANGE THE FILENAME PATH BEFORE EXECUTE!!
read_csv('s3://<full-filename-path>', delim=' ',
types = {
 'type': 'VARCHAR',
 'time': 'TIMESTAMP',
 'elb': 'VARCHAR',
 'client:port': 'VARCHAR',
 'target:port': 'VARCHAR',
 'request_processing_time': 'FLOAT',
 'target_processing_time': 'FLOAT',
 'response_processing_time': 'FLOAT',
 'elb_status_code': 'INTEGER',
 'target_status_code': 'VARCHAR',
 'received_bytes': 'BIGINT',
 'sent_bytes': 'BIGINT',
 'request': 'VARCHAR',
 'user_agent': 'VARCHAR',
 'ssl_cipher': 'VARCHAR',
 'ssl_protocol': 'VARCHAR',
 'target_group_arn': 'VARCHAR',
 'trace_id': 'VARCHAR',
 'domain_name': 'VARCHAR',
 'chosen_cert_arn': 'VARCHAR',
 'matched_rule_priority': 'VARCHAR',
 'request_creation_time': 'TIMESTAMP',
 'actions_executed': 'VARCHAR',
 'redirect_url': 'VARCHAR',
 'error_reason': 'VARCHAR',
 'target_status_code_list': 'VARCHAR',
 'classification': 'VARCHAR',
 'classification_reason': 'VARCHAR'
},
names=['type', 'time','elb','client:port','target:port','request_processing_time','target_processing_time','response_processing_time','elb_status_code','target_status_code','received_bytes','sent_bytes','request','user_agent','ssl_cipher','ssl_protocol','target_group_arn','trace_id','domain_name','chosen_cert_arn','matched_rule_priority','request_creation_time','actions_executed','redirect_url','error_reason','target_status_code_list','classification','classification_reason'],
auto_detect = true);

-- count the rows
SELECT count(*) FROM alb_logs;

Execute:

duckdb -init read_alb_logs.sql