In [ ]:

    

# Catch-up cell. Run if you did not do previous notebooks of this sequence
!wget http://kdd.ics.uci.edu/databases/kddcup99/kddcup.data_10_percent.gz
BUCKET='cloud-training-demos-ml'  # CHANGE
!gsutil cp kdd* gs://$BUCKET/


    

In [ ]:

    

!bq mk sparktobq


    

In [ ]:

    

BUCKET='cloud-training-demos-ml'  # CHANGE
!bq --location=US load --autodetect --source_format=CSV sparktobq.kdd_cup_raw gs://$BUCKET/kddcup.data_10_percent.gz


    

In [ ]:

    

%%bigquery
SELECT * FROM sparktobq.kdd_cup_raw LIMIT 5


    

In [ ]:

    

%%bigquery

CREATE OR REPLACE TABLE sparktobq.kdd_cup AS

SELECT 
    int64_field_0 AS duration,
    string_field_1 AS protocol_type,
    string_field_2 AS service,
    string_field_3 AS flag,
    int64_field_4 AS src_bytes,
    int64_field_5 AS dst_bytes,
    int64_field_6 AS wrong_fragment,
    int64_field_7 AS urgent,
    int64_field_8 AS hot,
    int64_field_9 AS num_failed_logins,
    int64_field_11 AS num_compromised,
    int64_field_13 AS su_attempted,
    int64_field_14 AS num_root,
    int64_field_15 AS num_file_creations,
    string_field_41 AS label
FROM
    sparktobq.kdd_cup_raw


    

In [ ]:

    

%%bigquery
SELECT * FROM sparktobq.kdd_cup LIMIT 5


    

In [ ]:

    

%%bigquery connections_by_protocol
SELECT COUNT(*) AS count
FROM sparktobq.kdd_cup
GROUP BY protocol_type
ORDER by count ASC


    

In [ ]:

    

connections_by_protocol


    

In [ ]:

    

%%bigquery attack_stats
                           SELECT 
                             protocol_type, 
                             CASE label
                               WHEN 'normal.' THEN 'no attack'
                               ELSE 'attack'
                             END AS state,
                             COUNT(*) as total_freq,
                             ROUND(AVG(src_bytes), 2) as mean_src_bytes,
                             ROUND(AVG(dst_bytes), 2) as mean_dst_bytes,
                             ROUND(AVG(duration), 2) as mean_duration,
                             SUM(num_failed_logins) as total_failed_logins,
                             SUM(num_compromised) as total_compromised,
                             SUM(num_file_creations) as total_file_creations,
                             SUM(su_attempted) as total_root_attempts,
                             SUM(num_root) as total_root_acceses
                           FROM sparktobq.kdd_cup
                           GROUP BY protocol_type, state
                           ORDER BY 3 DESC


    

In [ ]:

    

%matplotlib inline
ax = attack_stats.plot.bar(x='protocol_type', subplots=True, figsize=(10,25))


    

In [ ]:

    

import google.cloud.storage as gcs

# save locally
ax[0].get_figure().savefig('report.png');
connections_by_protocol.to_csv("connections_by_protocol.csv")

# upload to GCS
bucket = gcs.Client().get_bucket(BUCKET)
for blob in bucket.list_blobs(prefix='sparktobq/'):
    blob.delete()
for fname in ['report.png', 'connections_by_protocol.csv']:
    bucket.blob('sparktobq/{}'.format(fname)).upload_from_filename(fname)