config.yaml

# Configuration for a Basic Data Pipeline
pipeline:
  name: My First Data Pipeline
  version: 1.0

# DISCLAIMER: modify the following code to match your local Postgre instance
postgresql:
  host: localhost
  port: 5432
  username: postgres
  password: mypassword
  database_name: chicago_dmv

# Chicago Data file paths
crash_filepath: data/traffic_crashes.csv
vehicle_filepath: data/traffic_crash_vehicle.csv
people_filepath: data/traffic_crash_people.csv

# Chicago Data columns
crash_columns_list: [ 'CRASH_RECORD_ID', 'CRASH_DATE_EST_I', 'CRASH_DATE', 'CRASH_HOUR', 'CRASH_DAY_OF_WEEK', 'CRASH_MONTH',
                 'LATITUDE', 'LONGITUDE', 'NUM_UNITS', 'INJURIES_TOTAL' ]
vehicle_columns_list: ['CRASH_UNIT_ID', 'CRASH_RECORD_ID', 'CRASH_DATE', 'VEHICLE_ID', 'MAKE', 'MODEL', 'YEAR', 'VEHICLE_TYPE']
people_columns_list: ['PERSON_ID', 'CRASH_RECORD_ID', 'CRASH_DATE', 'PERSON_TYPE', 'VEHICLE_ID', 'SEX', 'AGE']

# Chicago Data column renames
crash_columns_rename_dict: {'CRASH_RECORD_ID': 'CRASH_ID','INJURIES_TOTAL': 'TOTAL_INJURIES'}
vehicle_columns_rename_dict: {'CRASH_RECORD_ID': 'CRASH_ID','MAKE': 'VEHICLE_MAKE','MODEL': 'VEHICLE_MODEL',
                              'YEAR': 'VEHICLE_YEAR', 'VEHICLE_TYPE': 'VEHICLE_TYPE'}
people_columns_rename_dict: {'CRASH_RECORD_ID': 'CRASH_ID', 'PERSON_TYPE': 'PERSON_TYPE',
                             'SEX': 'PERSON_SEX','AGE': 'PERSON_AGE'}

# Chicago Data output tables in PSQL
crash_table_PSQL: chicago_dmv.Crash
vehicle_table_PSQL: chicago_dmv.Vehicle
people_table_PSQL: chicago_dmv.People

# Chicago Data output schemas in PSQL
crash_create_PSQL: '''CREATE TABLE IF NOT EXISTS chicago_dmv.CRASH
                      (CRASH_ID TEXT, CRASH_DATE TIMESTAMP, CRASH_HOUR INTEGER,
                      CRASH_DAY_OF_WEEK INTEGER, CRASH_MONTH INTEGER, LATITUDE NUMERIC,
                      LONGITUDE NUMERIC, NUM_UNITS INTEGER, TOTAL_INJURIES INTEGER)'''
crash_insert_PSQL: 'INSERT INTO chicago_dmv.CRASH VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'

vehicle_create_PSQL: '''CREATE TABLE IF NOT EXISTS chicago_dmv.VEHICLE
                        (CRASH_UNIT_ID INTEGER, CRASH_ID TEXT, CRASH_DATE TIMESTAMP,
                        VEHICLE_ID INTEGER, VEHICLE_MAKE TEXT, VEHICLE_MODEL TEXT,
                        VEHICLE_YEAR INTEGER, VEHICLE_TYPE TEXT)'''
vehicle_insert_PSQL: 'INSERT INTO chicago_dmv.VEHICLE VALUES (%s, %s, %s, %s, %s, %s, %s, %s)'

people_create_PSQL: '''CREATE TABLE IF NOT EXISTS chicago_dmv.PERSON
                      (PERSON_ID TEXT, CRASH_ID TEXT, CRASH_DATE TIMESTAMP, PERSON_TYPE TEXT,
                      VEHICLE_ID INTEGER, PERSON_SEX TEXT, PERSON_AGE INTEGER)'''
people_insert_PSQL: 'INSERT INTO chicago_dmv.PERSON VALUES (%s, %s, %s, %s, %s, %s, %s)'