erusso7 · December 15, 2022 22:14
diff --git a/clean_csv_data.sql b/clean_csv_data.sql

 -- 1: We need to create the table to read the data
 CREATE TABLE IF NOT EXISTS hive.fraud.fraud_data
 (
    time     VARCHAR,
    v1       VARCHAR,
    v2       VARCHAR,
    v3       VARCHAR,
    v4       VARCHAR,
    v5       VARCHAR,
    v6       VARCHAR,
    v7       VARCHAR,
    v8       VARCHAR,
    v9       VARCHAR,
    v10      VARCHAR,
    v11      VARCHAR,
    v12      VARCHAR,
    v13      VARCHAR,
    v14      VARCHAR,
    v15      VARCHAR,
    v16      VARCHAR,
    v17      VARCHAR,
    v18      VARCHAR,
    v19      VARCHAR,
    v20      VARCHAR,
    v21      VARCHAR,
    v22      VARCHAR,
    v23      VARCHAR,
    v24      VARCHAR,
    v25      VARCHAR,
    v26      VARCHAR,
    v27      VARCHAR,
    v28      VARCHAR,
    amount   VARCHAR,
    is_fraud VARCHAR
 ) WITH ( external_location = 's3a://fraud/fraud_data/',
      skip_header_line_count = 1,
      format = 'csv' );

 -- 2: Then we create a second table with the clean data and external storage as CSV
 CREATE TABLE IF NOT EXISTS hive.fraud.fraud_clean
    WITH ( external_location = 's3a://fraud/fraud_clean/', format = 'csv', skip_header_line_count=1 ) AS
 (
    SELECT *
    FROM hive.fraud.fraud_data
    WHERE v1 != ''
    AND v2 != ''
    AND v3 != ''
    AND v4 != ''
    AND v5 != ''
    AND v6 != ''
    AND v7 != ''
    AND v8 != ''
    AND v9 != ''
    AND v10 != ''
    AND v11 != ''
    AND v12 != ''
    AND v13 != ''
    AND v14 != ''
    AND v15 != ''
    AND v16 != ''
    AND v17 != ''
    AND v18 != ''
    AND v19 != ''
    AND v20 != ''
    AND v21 != ''
    AND v22 != ''
    AND v23 != ''
    AND v24 != ''
    AND v25 != ''
    AND v26 != ''
    AND v27 != ''
    AND v28 != ''
    AND amount != ''
 );
diff --git a/corrupt_csv.py b/corrupt_csv.py
 #!/usr/bin/env python
 import sys
 from csv import reader
 from random import choice
 from random import randrange

 # Usage: ./corrupt_csv.py <LIMIT_OF_ROWS> <EMPTY_RATE_PERCENTAGE>
 limit = int(sys.argv[1])
 emptyRate = int(sys.argv[2])
 delimiter = ","

 # skip first line i.e. read header first and then iterate over each row od csv as a list
 with open('creditcard.csv', 'r') as read_obj:    
    csv_reader = reader(read_obj)
    header = next(csv_reader)
    # Check file as empty
    if header != None:
        print(delimiter.join(header))
        i = 0
        # Iterate over each row after the header in the csv
        for row in csv_reader:
            if  randrange(100) <= emptyRate:
                targetCol = choice(list(range(len(row)-2))) + 1
                row[targetCol] = ''

            print(delimiter.join(row))
            i = i +1
            if i == limit:
                sys.exit()
diff --git a/creditcard_500_10.csv b/creditcard_500_10.csv
diff --git a/creditcard_500_30.csv b/creditcard_500_30.csv

	-- 1: We need to create the table to read the data
	CREATE TABLE IF NOT EXISTS hive.fraud.fraud_data
	(
	time VARCHAR,
	v1 VARCHAR,
	v2 VARCHAR,
	v3 VARCHAR,
	v4 VARCHAR,
	v5 VARCHAR,
	v6 VARCHAR,
	v7 VARCHAR,
	v8 VARCHAR,
	v9 VARCHAR,
	v10 VARCHAR,
	v11 VARCHAR,
	v12 VARCHAR,
	v13 VARCHAR,
	v14 VARCHAR,
	v15 VARCHAR,
	v16 VARCHAR,
	v17 VARCHAR,
	v18 VARCHAR,
	v19 VARCHAR,
	v20 VARCHAR,
	v21 VARCHAR,
	v22 VARCHAR,
	v23 VARCHAR,
	v24 VARCHAR,
	v25 VARCHAR,
	v26 VARCHAR,
	v27 VARCHAR,
	v28 VARCHAR,
	amount VARCHAR,
	is_fraud VARCHAR
	) WITH ( external_location = 's3a://fraud/fraud_data/',
	skip_header_line_count = 1,
	format = 'csv' );

	-- 2: Then we create a second table with the clean data and external storage as CSV
	CREATE TABLE IF NOT EXISTS hive.fraud.fraud_clean
	WITH ( external_location = 's3a://fraud/fraud_clean/', format = 'csv', skip_header_line_count=1 ) AS
	(
	SELECT *
	FROM hive.fraud.fraud_data
	WHERE v1 != ''
	AND v2 != ''
	AND v3 != ''
	AND v4 != ''
	AND v5 != ''
	AND v6 != ''
	AND v7 != ''
	AND v8 != ''
	AND v9 != ''
	AND v10 != ''
	AND v11 != ''
	AND v12 != ''
	AND v13 != ''
	AND v14 != ''
	AND v15 != ''
	AND v16 != ''
	AND v17 != ''
	AND v18 != ''
	AND v19 != ''
	AND v20 != ''
	AND v21 != ''
	AND v22 != ''
	AND v23 != ''
	AND v24 != ''
	AND v25 != ''
	AND v26 != ''
	AND v27 != ''
	AND v28 != ''
	AND amount != ''
	);
	#!/usr/bin/env python
	import sys
	from csv import reader
	from random import choice
	from random import randrange

	# Usage: ./corrupt_csv.py <LIMIT_OF_ROWS> <EMPTY_RATE_PERCENTAGE>
	limit = int(sys.argv[1])
	emptyRate = int(sys.argv[2])
	delimiter = ","

	# skip first line i.e. read header first and then iterate over each row od csv as a list
	with open('creditcard.csv', 'r') as read_obj:
	csv_reader = reader(read_obj)
	header = next(csv_reader)
	# Check file as empty
	if header != None:
	print(delimiter.join(header))
	i = 0
	# Iterate over each row after the header in the csv
	for row in csv_reader:
	if randrange(100) <= emptyRate:
	targetCol = choice(list(range(len(row)-2))) + 1
	row[targetCol] = ''

	print(delimiter.join(row))
	i = i +1
	if i == limit:
	sys.exit()