AWS Notes/SQL on VPC Flow Logs: Difference between revisions
From Federal Burro of Information
Jump to navigationJump to search
(Created page with " <pre> select vpc_id as VPC, sum( cast(bytes as DECIMAL ) / 1073741824 as GB, dt from vpc_flow_logs group by vpc_id , dt limit 10; </pre> <pre> select dt, vpc_id as V...") |
No edit summary |
||
Line 38: | Line 38: | ||
show partitions vpc_flow_logs; | show partitions vpc_flow_logs; | ||
<pre> | |||
select | |||
dt, | |||
vpc_id as VPC, | |||
sum(bytes) as bytes, | |||
cast( sum(bytes) as decimal )/1024 as Kilobytes, | |||
cast( sum(bytes) as decimal )/1048576 as Megabytes, | |||
cast( sum(bytes) as decimal(38,2) )/1073741824 as Gigabytes | |||
from vpc_flow_logs | |||
group by dt, vpc_id | |||
order by dt,vpc_id | |||
limit 10; | |||
</pre> |
Revision as of 13:37, 30 September 2019
select vpc_id as VPC, sum( cast(bytes as DECIMAL ) / 1073741824 as GB, dt from vpc_flow_logs group by vpc_id , dt limit 10;
select dt, vpc_id as VPC, bytes from vpc_flow_logs limit 10;
ALTER TABLE ADD PARTITION
ALTER TABLE orders ADD PARTITION (dt = '2016-05-14') LOCATION 's3://mystorage/path/to/INDIA_14_May_2016/' PARTITION (dt = '2016-05-15') LOCATION 's3://mystorage/path/to/INDIA_15_May_2016/';
If you choose "load partitions" it will execute: "MSCK REPAIR TABLE vpc_flow_logs;"
MSCK REPAIR TABLE vpc_flow_logs;
which gives the following out put:
Partitions not in metastore: vpc_flow_logs:2019/09/21 vpc_flow_logs:2019/09/22 vpc_flow_logs:2019/09/23
ALTER TABLE vpc_flow_logs ADD PARTITION ( dt = '2019-09-21' ) location 's3://vpc-flow-logs/AWSLogs/XXX/vpcflowlogs/us-east-1/2019/09/21'; ALTER TABLE vpc_flow_logs ADD PARTITION ( dt = '2019-09-22' ) location 's3://vpc-flow-logs/AWSLogs/XXX/vpcflowlogs/us-east-1/2019/09/22'; ALTER TABLE vpc_flow_logs ADD PARTITION ( dt = '2019-09-23' ) location 's3://vpc-flow-logs/AWSLogs/XXX/vpcflowlogs/us-east-1/2019/09/23';
show partitions vpc_flow_logs;
select dt, vpc_id as VPC, sum(bytes) as bytes, cast( sum(bytes) as decimal )/1024 as Kilobytes, cast( sum(bytes) as decimal )/1048576 as Megabytes, cast( sum(bytes) as decimal(38,2) )/1073741824 as Gigabytes from vpc_flow_logs group by dt, vpc_id order by dt,vpc_id limit 10;