Import CIC-IDS2017 dataset into Timeplus/Proton

cic_ids2017.sql

-- create stream
CREATE STREAM network_traffic
(
  `id` int,
  `destination_port` int64,
  `flow_duration` int64,
  `total_fwd_packets` int64,
  `total_backward_packets` int64,
  `total_length_of_fwd_packets` int64,
  `total_length_of_bwd_packets` int64,
  `fwd_packet_length_max` int64,
  `fwd_packet_length_min` int64,
  `fwd_packet_length_mean` float64,
  `fwd_packet_length_std` float64,
  `bwd_packet_length_max` int64,
  `bwd_packet_length_min` int64,
  `bwd_packet_length_mean` float64,
  `bwd_packet_length_std` float64,
  `flow_bytes/s` nullable(float64),
  `flow_packets/s` nullable(float64),
  `flow_iat_mean` float64,
  `flow_iat_std` float64,
  `flow_iat_max` int64,
  `flow_iat_min` int64,
  `fwd_iat_total` int64,
  `fwd_iat_mean` float64,
  `fwd_iat_std` float64,
  `fwd_iat_max` int64,
  `fwd_iat_min` int64,
  `bwd_iat_total` int64,
  `bwd_iat_mean` float64,
  `bwd_iat_std` float64,
  `bwd_iat_max` int64,
  `bwd_iat_min` int64,
  `fwd_psh_flags` int64,
  `bwd_psh_flags` int64,
  `fwd_urg_flags` int64,
  `bwd_urg_flags` int64,
  `fwd_header_length` int64,
  `bwd_header_length` int64,
  `fwd_packets/s` float64,
  `bwd_packets/s` float64,
  `min_packet_length` int64,
  `max_packet_length` int64,
  `packet_length_mean` float64,
  `packet_length_std` float64,
  `packet_length_variance` float64,
  `fin_flag_count` int64,
  `syn_flag_count` int64,
  `rst_flag_count` int64,
  `psh_flag_count` int64,
  `ack_flag_count` int64,
  `urg_flag_count` int64,
  `cwe_flag_count` int64,
  `ece_flag_count` int64,
  `down/up_ratio` int64,
  `average_packet_size` float64,
  `avg_fwd_segment_size` float64,
  `avg_bwd_segment_size` float64,
  `fwd_header_length.1` int64,
  `fwd_avg_bytes/bulk` int64,
  `fwd_avg_packets/bulk` int64,
  `fwd_avg_bulk_rate` int64,
  `bwd_avg_bytes/bulk` int64,
  `bwd_avg_packets/bulk` int64,
  `bwd_avg_bulk_rate` int64,
  `subflow_fwd_packets` int64,
  `subflow_fwd_bytes` int64,
  `subflow_bwd_packets` int64,
  `subflow_bwd_bytes` int64,
  `init_win_bytes_forward` int64,
  `init_win_bytes_backward` int64,
  `act_data_pkt_fwd` int64,
  `min_seg_size_forward` int64,
  `active_mean` float64,
  `active_std` float64,
  `active_max` int64,
  `active_min` int64,
  `idle_mean` float64,
  `idle_std` float64,
  `idle_max` int64,
  `idle_min` int64,
  `label` string
);

-- add data from S3
insert into network_traffic (`id`, 
`destination_port`, 
`flow_duration`, 
`total_fwd_packets`, 
`total_backward_packets`, 
`total_length_of_fwd_packets`, 
`total_length_of_bwd_packets`, 
`fwd_packet_length_max`, 
`fwd_packet_length_min`, 
`fwd_packet_length_mean`, 
`fwd_packet_length_std`, 
`bwd_packet_length_max`, 
`bwd_packet_length_min`, 
`bwd_packet_length_mean`, 
`bwd_packet_length_std`, 
`flow_bytes/s`, 
`flow_packets/s`, 
`flow_iat_mean`, 
`flow_iat_std`, 
`flow_iat_max`, 
`flow_iat_min`, 
`fwd_iat_total`, 
`fwd_iat_mean`, 
`fwd_iat_std`, 
`fwd_iat_max`, 
`fwd_iat_min`, 
`bwd_iat_total`, 
`bwd_iat_mean`, 
`bwd_iat_std`, 
`bwd_iat_max`, 
`bwd_iat_min`, 
`fwd_psh_flags`, 
`bwd_psh_flags`, 
`fwd_urg_flags`, 
`bwd_urg_flags`, 
`fwd_header_length`, 
`bwd_header_length`, 
`fwd_packets/s`, 
`bwd_packets/s`, 
`min_packet_length`, 
`max_packet_length`, 
`packet_length_mean`, 
`packet_length_std`, 
`packet_length_variance`, 
`fin_flag_count`, 
`syn_flag_count`, 
`rst_flag_count`, 
`psh_flag_count`, 
`ack_flag_count`, 
`urg_flag_count`, 
`cwe_flag_count`, 
`ece_flag_count`, 
`down/up_ratio`, 
`average_packet_size`, 
`avg_fwd_segment_size`, 
`avg_bwd_segment_size`, 
`fwd_header_length.1`, 
`fwd_avg_bytes/bulk`, 
`fwd_avg_packets/bulk`, 
`fwd_avg_bulk_rate`, 
`bwd_avg_bytes/bulk`, 
`bwd_avg_packets/bulk`, 
`bwd_avg_bulk_rate`, 
`subflow_fwd_packets`, 
`subflow_fwd_bytes`, 
`subflow_bwd_packets`, 
`subflow_bwd_bytes`, 
`init_win_bytes_forward`, 
`init_win_bytes_backward`, 
`act_data_pkt_fwd`, 
`min_seg_size_forward`, 
`active_mean`, 
`active_std`, 
`active_max`, 
`active_min`, 
`idle_mean`, 
`idle_std`, 
`idle_max`, 
`idle_min`, 
`label`
)
SELECT * 
FROM url('https://tp-solutions.s3.us-west-2.amazonaws.com/IoT/Friday-WorkingHours-Afternoon-DDos-pcap_ISCX-clean-with_index.csv', 'CSVWithNames');