First, the argument to --output-format does not seem to be validated: --output-format foo will still work and give the default format. Second, it seems the argument is case-sensitive; CSV works but csv silently gives the default. It would be nice if I could use lower case.
Third, it seems there may be some byte vs. string / encoding issues, as many strings come out as the Python repr for binary data (b'...').
Here is the Athena ELB log example database:
athena --execute "SELECT * FROM "sampledb"."elb_logs" limit 3;"
b'request_timestamp' | b'elb_name' | b'request_ip' | b'request_port' | b'backend_ip' | b'backend_port' | b'request_processing_time' | b'backend_processing_time' | b'client_response_time' | b'elb_response_code' | b'backend_response_code' | b'received_bytes' | b'sent_bytes' | b'request_verb' | b'url' | b'protocol' | b'user_agent' | b'ssl_cipher' | b'ssl_protocol'
-----------------------------+---------------+-----------------+-------------------+-----------------+-------------------+------------------------------+------------------------------+---------------------------+------------------------+----------------------------+---------------------+-----------------+-------------------+-----------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------+--------------------+-------------------
request_timestamp | elb_name | request_ip | request_port | backend_ip | backend_port | request_processing_time | backend_processing_time | client_response_time | elb_response_code | backend_response_code | received_bytes | sent_bytes | request_verb | url | protocol | user_agent | ssl_cipher | ssl_protocol
2015-01-05T20:00:01.206255Z | elb_demo_002 | 240.220.175.143 | 26144 | 172.37.79.92 | 443 | 0.00166 | 8.9E-4 | 0.00167 | 200 | 200 | 0 | 1150 | GET | http://www.example.com/images/989 | HTTP/1.1 | "Mozilla/5.0 (X11; CrOS x86_64 8172.45.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.64 Safari/537.36" | - | -
2015-01-05T20:00:01.612598Z | elb_demo_008 | 244.189.63.245 | 25515 | 172.42.219.87 | 443 | 5.05E-4 | 0.001697 | 0.001113 | 200 | 200 | 0 | 3123 | GET | http://www.example.com/jobs/862 | HTTP/1.1 | "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36" | - | -
2015-01-05T20:00:02.793335Z | elb_demo_008 | 249.110.119.93 | 26779 | 172.45.67.52 | 8888 | 0.001881 | 0.001082 | 0.001147 | 500 | 500 | 0 | 1978 | GET | https://www.example.com/jobs/118 | HTTP/1.1 | "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36" | DHE-RSA-AES128-SHA | TLSv1.2
athena --execute "SELECT * FROM "sampledb"."elb_logs" limit 3;" --output-format CSV
"b'request_timestamp'","b'elb_name'","b'request_ip'","b'request_port'","b'backend_ip'","b'backend_port'","b'request_processing_time'","b'backend_processing_time'","b'client_response_time'","b'elb_response_code'","b'backend_response_code'","b'received_bytes'","b'sent_bytes'","b'request_verb'","b'url'","b'protocol'","b'user_agent'","b'ssl_cipher'","b'ssl_protocol'"
"b'2015-01-06T16:00:00.516940Z'","b'elb_demo_003'","b'241.93.49.158'","b'1459'","b'172.34.104.223'","b'443'","b'7.13E-4'","b'4.53E-4'","b'0.001112'","b'200'","b'200'","b'0'","b'744'","b'GET'","b'http://www.example.com/articles/194'","b'HTTP/1.1'","b'""Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36""'","b'-'","b'-'"
"b'2015-01-06T16:00:00.902953Z'","b'elb_demo_008'","b'240.124.85.193'","b'17382'","b'172.50.75.155'","b'443'","b'7.1E-4'","b'6.09E-4'","b'0.001432'","b'200'","b'200'","b'0'","b'329'","b'GET'","b'http://www.example.com/images/813'","b'HTTP/1.1'","b'""Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36""'","b'-'","b'-'"
"b'2015-01-06T16:00:01.206255Z'","b'elb_demo_005'","b'248.193.50.89'","b'32739'","b'172.32.214.237'","b'80'","b'3.2E-4'","b'4.37E-4'","b'0.001608'","b'200'","b'200'","b'0'","b'4740'","b'GET'","b'http://www.example.com/images/692'","b'HTTP/1.1'","b'""Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/602.1.50 (KHTML, like Gecko) Version/10.0 Safari/602.1.50""'","b'-'","b'-'"
It's not clear what --output-format CSV_HEADER supposed to do, but it's adding a duplicate header:
"b'request_timestamp'","b'elb_name'","b'request_ip'","b'request_port'","b'backend_ip'","b'backend_port'","b'request_processing_time'","b'backend_processing_time'","b'client_response_time'","b'elb_response_code'","b'backend_response_code'","b'received_bytes'","b'sent_bytes'","b'request_verb'","b'url'","b'protocol'","b'user_agent'","b'ssl_cipher'","b'ssl_protocol'"
"b'request_timestamp'","b'elb_name'","b'request_ip'","b'request_port'","b'backend_ip'","b'backend_port'","b'request_processing_time'","b'backend_processing_time'","b'client_response_time'","b'elb_response_code'","b'backend_response_code'","b'received_bytes'","b'sent_bytes'","b'request_verb'","b'url'","b'protocol'","b'user_agent'","b'ssl_cipher'","b'ssl_protocol'"
"b'2015-01-01T08:00:00.516940Z'","b'elb_demo_009'","b'240.136.98.149'","b'25858'","b'172.51.67.62'","b'8888'","b'9.99E-4'","b'8.11E-4'","b'0.001561'","b'200'","b'200'","b'0'","b'428'","b'GET'","b'https://www.example.com/articles/746'","b'HTTP/1.1'","b'""Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/602.1.50 (KHTML, like Gecko) Version/10.0 Safari/602.1.50""'","b'DHE-RSA-AES128-SHA'","b'TLSv1.2'"
"b'2015-01-01T08:00:00.902953Z'","b'elb_demo_008'","b'244.46.184.108'","b'27758'","b'172.31.168.31'","b'443'","b'6.39E-4'","b'0.001471'","b'3.73E-4'","b'200'","b'200'","b'0'","b'4231'","b'GET'","b'https://www.example.com/jobs/688'","b'HTTP/1.1'","b'""Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:15.0) Gecko/20100101 Firefox/15.0.1""'","b'DHE-RSA-AES128-SHA'","b'TLSv1.2'"
"b'2015-01-01T08:00:01.206255Z'","b'elb_demo_008'","b'240.120.203.212'","b'26378'","b'172.37.170.107'","b'8888'","b'0.001174'","b'4.97E-4'","b'4.89E-4'","b'200'","b'200'","b'0'","b'2075'","b'GET'","b'http://www.example.com/articles/290'","b'HTTP/1.1'","b'""Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.135 Safari/537.36 Edge/12.246""'","b'-'","b'-'"
--output-format TSV_HEADER is also odd:
b'request_timestamp' b'elb_name' b'request_ip' b'request_port' b'backend_ip' b'backend_port' b'request_processing_time' b'backend_processing_time' b'client_response_time' b'elb_response_code' b'backend_response_code' b'received_bytes' b'sent_bytes' b'request_verb' b'url' b'protocol' b'user_agent' b'ssl_cipher' b'ssl_protocol'
request_timestamp elb_name request_ip request_port backend_ip backend_port request_processing_time backend_processing_time client_response_time elb_response_code backend_response_code received_bytes sent_bytes request_verb url protocol user_agent ssl_cipher ssl_protocol
2015-01-06T08:00:00.516940Z elb_demo_009 241.185.58.78 30989 172.42.85.244 443 1.66E-4 0.001622 4.72E-4 200 200 0 3124 GET http://www.example.com/jobs/692 HTTP/1.1 "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:15.0) Gecko/20100101 Firefox/15.0.1" - -
2015-01-06T08:00:00.902953Z elb_demo_002 241.61.132.60 6917 172.51.77.197 80 9.66E-4 1.35E-4 1.63E-4 200 200 0 1676 GET https://www.example.com/jobs/941 HTTP/1.1 "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.135 Safari/537.36 Edge/12.246" DHE-RSA-AES128-SHA TLSv1.2
2015-01-06T08:00:01.206255Z elb_demo_007 244.176.139.111 21236 172.55.53.108 443 2.56E-4 0.0011 0.001404 200 200 0 4974 GET https://www.example.com/images/809 HTTP/1.1 "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.135 Safari/537.36 Edge/12.246" DHE-RSA-AES128-SHA TLSv1.2
First, the argument to
--output-formatdoes not seem to be validated:--output-format foowill still work and give the default format. Second, it seems the argument is case-sensitive;CSVworks butcsvsilently gives the default. It would be nice if I could use lower case.Third, it seems there may be some byte vs. string / encoding issues, as many strings come out as the Python repr for binary data (
b'...').Here is the Athena ELB log example database:
athena --execute "SELECT * FROM "sampledb"."elb_logs" limit 3;"athena --execute "SELECT * FROM "sampledb"."elb_logs" limit 3;" --output-format CSVIt's not clear what
--output-format CSV_HEADERsupposed to do, but it's adding a duplicate header:--output-format TSV_HEADERis also odd: