Skip to content

Instantly share code, notes, and snippets.

@randyzwitch

randyzwitch/extra_col.sql

Last active November 7, 2019 17:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save randyzwitch/9c16c09efba63ba225a7b63f731c87e5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save randyzwitch/9c16c09efba63ba225a7b63f731c87e5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. randyzwitch revised this gist Nov 7, 2019. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions extra_col.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,22 +1,22 @@
    --201906 and 201907 files have extra blank column
    CREATE TABLE baywheels_tripdata_extracol(
    duration_sec INTEGER,
    start_time TIMESTAMP(0),
    end_time TIMESTAMP(0),
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    start_station_id SMALLINT,
    start_station_name TEXT ENCODING DICT(32),
    start_station_name TEXT ENCODING DICT,
    start_station_latitude FLOAT,
    start_station_longitude FLOAT,
    end_station_id SMALLINT,
    end_station_name TEXT ENCODING DICT(32),
    end_station_name TEXT ENCODING DICT,
    end_station_latitude FLOAT,
    end_station_longitude FLOAT,
    bike_id SMALLINT,
    user_type TEXT ENCODING DICT(32),
    user_type TEXT ENCODING DICT,
    member_birth_year SMALLINT,
    member_gender TEXT ENCODING DICT(32),
    bike_share_for_all_trip TEXT ENCODING DICT(32),
    extracol TEXT ENCODING DICT(32)
    member_gender TEXT ENCODING DICT,
    bike_share_for_all_trip TEXT ENCODING DICT,
    extracol TEXT ENCODING DICT
    );

    copy baywheels_tripdata_extracol from 's3://baywheels-data/201906-baywheels-tripdata.csv.zip';
  2. randyzwitch created this gist Nov 6, 2019.
    23 changes: 23 additions & 0 deletions extra_col.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@
    --201906 and 201907 files have extra blank column
    CREATE TABLE baywheels_tripdata_extracol(
    duration_sec INTEGER,
    start_time TIMESTAMP(0),
    end_time TIMESTAMP(0),
    start_station_id SMALLINT,
    start_station_name TEXT ENCODING DICT(32),
    start_station_latitude FLOAT,
    start_station_longitude FLOAT,
    end_station_id SMALLINT,
    end_station_name TEXT ENCODING DICT(32),
    end_station_latitude FLOAT,
    end_station_longitude FLOAT,
    bike_id SMALLINT,
    user_type TEXT ENCODING DICT(32),
    member_birth_year SMALLINT,
    member_gender TEXT ENCODING DICT(32),
    bike_share_for_all_trip TEXT ENCODING DICT(32),
    extracol TEXT ENCODING DICT(32)
    );

    copy baywheels_tripdata_extracol from 's3://baywheels-data/201906-baywheels-tripdata.csv.zip';
    copy baywheels_tripdata_extracol from 's3://baywheels-data/201907-baywheels-tripdata.csv.zip' with (delimiter=';');