Hi,
New to Athena and I would like to query Classic ELB for IP addresses in an effort to identify some web scrapers (sort of the first step in identifying if we have a problem with scrapers or not). Anyway Athena seems to be a really easy to ingest our logs that we have saved to S3 without ever really thinking about it for three years.
I am following the guide available here:https://docs.aws.amazon.com/athena/latest/ug/elasticloadbalancer-classic-logs.html
I tried to create the table using the following SQL, it creates as expected but always shows 0 rows and 0kb scanned. I've subsquently tried more focussed folder paths (for example 1 days worth of logs instead of all the three years with the same output. I've tried the classic and application load balancer regex but I am sure it's the classic I need.
CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs (
timestamp string,
elb_name string,
request_ip string,
request_port int,
backend_ip string,
backend_port int,
request_processing_time double,
backend_processing_time double,
client_response_time double,
elb_response_code string,
backend_response_code string,
received_bytes bigint,
sent_bytes bigint,
request_verb string,
url string,
protocol string,
user_agent string,
ssl_cipher string,
ssl_protocol string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'input.regex' = '([^ ]) ([^ ]) ([^ ]):([0-9]) ([^ ])[:-]([0-9]) ([-.0-9]) ([-.0-9]) ([-.0-9]) (|[-0-9]) (-|[-0-9]) ([-0-9]) ([-0-9]) \"([^ ]) ([^ ]) (- |[^ ])\" ("[^"]") ([A-Z0-9-]+) ([A-Za-z0-9.-])$' )
LOCATION 's3://MYBUCKETNAME/AWSLogs/AWSACCOUNTID/elasticloadbalancing/'
There are obviously files in the s3 bucket, I am using an IAM user with Full AWS Permissions and
Any thoughts?
Everything seems to suggest the path is not right - but I think...it i...s Thanks for your patience.
Thanks