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;