SHARE
TWEET

Untitled

a guest Jul 22nd, 2019 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE EXTERNAL TABLE IF NOT EXISTS {{DATABASE_NAME.TABLE_NAME}} (
  2.  
  3.  timestamp string,
  4.  elb_name string,
  5.  request_ip string,
  6.  request_port int,
  7.  backend_ip string,
  8.  backend_port int,
  9.  request_processing_time double,
  10.  backend_processing_time double,
  11.  client_response_time double,
  12.  elb_response_code string,
  13.  backend_response_code string,
  14.  received_bytes bigint,
  15.  sent_bytes bigint,
  16.  request_verb string,
  17.  url string,
  18.  protocol string,
  19.  user_agent string,
  20.  ssl_cipher string,
  21.  ssl_protocol string
  22. )
  23. PARTITIONED BY(year string, month string, day string)
  24. ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  25. WITH SERDEPROPERTIES (
  26.  'serialization.format' = '1',
  27.  '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.-]*)$' )
  28. LOCATION 's3://{{BUCKET_NAME}}/AWSLogs/{{ACCOUNT_ID}}/elasticloadbalancing/{{REGION}}/';
  29.  
  30. // To only query for June 2019 load data from Partition
  31. ALTER TABLE {{DATABASE_NAME.TABLE_NAME}} ADD PARTITION (year='2019', month='06', day='*') location 's3://{{BUCKET_NAME}}/AWSLogs/{{ACCOUNT_ID}}/elasticloadbalancing/{{REGION}}/2019/06/';
  32.  
  33. // Query
  34. SELECT timestamp, user_agent, request_verb,url, ssl_cipher,ssl_protocol
  35. FROM {{DATABASE_NAME.TABLE_NAME}}
  36. WHERE ssl_protocol='TLSv1'
  37. LIMIT 10;
  38.  
  39. // Drop Partition
  40. ALTER TABLE {{DATABASE_NAME.TABLE_NAME}} DROP PARTITION (year='2019', month='06', day='*');
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top