nc-campaign-finance/process/inst/snowflake/03-copy-from-s3.sql

-- CREATE OR REPLACE FILE FORMAT parquetformat TYPE = parquet;

COPY INTO addresses
FROM (
  SELECT $1:address_lookup,
         $1:address_resolved,
         $1:lat,
         $1:long,
         $1:match_indicator,
         $1:match_type,
         $1:tiger_line_id,
         $1:tiger_side
  FROM @FINANCE_PARQUET/addresses.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO cl_candidates
FROM (
  SELECT $1:candidate_id,
         $1:name_on_ballot,
         $1:first_name,
         $1:middle_name,
         $1:last_name,
         $1:name_suffix_lbl,
         $1:party_last,
         $1:party_most,
         $1:contest_n,
         $1:contest_first,
         $1:contest_latest,
         $1:street,
         $1:city,
         $1:state,
         $1:zip_code,
         $1:phone,
         $1:email,
         $1:address_lookup
  FROM @FINANCE_PARQUET/cl_candidates.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO cl_contact
FROM (
  SELECT $1:candidate_id,
         $1:election_dt,
         $1:street,
         $1:city,
         $1:state,
         $1:zip_code,
         $1:phone,
         $1:email,
         $1:address_lookup
  FROM @FINANCE_PARQUET/cl_contact.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO cl_elections
FROM (
  SELECT $1:election_dt,
         $1:county_name,
         $1:contest_name,
         $1:candidate_id,
         $1:first_name,
         $1:middle_name,
         $1:last_name,
         $1:name_suffix_lbl
  FROM @FINANCE_PARQUET/cl_elections.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO cl_name_on_ballot
FROM (
  SELECT $1:candidate_id,
         $1:election_dt,
         $1:name_on_ballot,
         $1:first_name,
         $1:middle_name,
         $1:last_name,
         $1:name_suffix_lbl
  FROM @FINANCE_PARQUET/cl_name_on_ballot.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO cl_party
FROM (
  SELECT $1:candidate_id,
         $1:election_dt,
         $1:party_candidate
  FROM @FINANCE_PARQUET/cl_party.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO committee_candidate
FROM (
  SELECT $1:sboe_id,
         $1:candidate_id
  FROM @FINANCE_PARQUET/committee_candidate.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO committees
FROM (
  SELECT $1:sboe_id,
         $1:committee_name,
         $1:report_id,
         $1:street_1,
         $1:street_2,
         $1:city,
         $1:state,
         $1:zip_code,
         $1:address_lookup,
         $1:committee_type,
         $1:fund_type,
         $1:fund_name
  FROM @FINANCE_PARQUET/committees.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO cover
FROM (
  SELECT $1:report_id,
         $1:sboe_id,
         $1:committee_name,
         $1:street_1,
         $1:street_2,
         $1:city,
         $1:state,
         $1:zip_code,
         $1:country,
         $1:postal_code,
         $1:committee_type,
         $1:report_type,
         $1:fund_type,
         $1:fund_name,
         $1:date_from,
         $1:date_to,
         $1:date_filed
  FROM @FINANCE_PARQUET/cover.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO expenses
FROM (
  SELECT $1:sboe_id,
         $1:report_id,
         $1:payee_id,
         $1:occur_date,
         $1:amount,
         $1:sum_to_date,
         $1:is_aggregated,
         $1:purpose_type_code,
         $1:purpose,
         $1:expenditure_type_desc,
         $1:account_abbr,
         $1:form_of_payment_desc
  FROM @FINANCE_PARQUET/expenses.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO expenses_payee
FROM (
  SELECT $1:payee_id,
         $1:org_name,
         $1:is_org,
         $1:is_us,
         $1:profession,
         $1:employers_name,
         $1:street_1,
         $1:street_2,
         $1:city,
         $1:state,
         $1:full_zip,
         $1:country_name,
         $1:address_lookup
  FROM @FINANCE_PARQUET/expenses_payee.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO officers
FROM (
  SELECT $1:sboe_id,
         $1:report_id,
         $1:type,
         $1:name,
         $1:address_lookup,
         $1:phone
  FROM @FINANCE_PARQUET/officers.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO receipts
FROM (
  SELECT $1:sboe_id,
         $1:report_id,
         $1:payer_id,
         $1:group_id,
         $1:occur_date,
         $1:amount,
         $1:sum_to_date,
         $1:is_aggregated,
         $1:receipt_type_desc,
         $1:receipt_type_code,
         $1:is_donation,
         $1:account_abbr,
         $1:form_of_payment_desc,
         $1:is_prior
  FROM @FINANCE_PARQUET/receipts.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO receipts_payer
FROM (
  SELECT $1:payer_id,
         $1:org_name,
         $1:is_org,
         $1:is_us,
         $1:profession,
         $1:employers_name,
         $1:street_1,
         $1:city,
         $1:state,
         $1:full_zip,
         $1:country_name,
         $1:address_lookup
  FROM @FINANCE_PARQUET/receipts_payer.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO reports
FROM (
  SELECT $1:year,
         $1:doc_name,
         $1:doc_order,
         $1:sboe_id,
         $1:report_id,
         $1:amended,
         $1:image_id,
         $1:received_image,
         $1:received_data,
         $1:start_date,
         $1:end_date,
         $1:sboe_start_date,
         $1:sboe_end_date,
         $1:cover_start_date,
         $1:cover_end_date,
         $1:cover_date_filed
  FROM @FINANCE_PARQUET/reports.parquet (FILE_FORMAT => "PARQUETFORMAT")
);

COPY INTO voters
FROM (
  SELECT $1:county_id,
         $1:county_desc,
         $1:voter_reg_num,
         $1:ncid,
         $1:last_name,
         $1:first_name,
         $1:middle_name,
         $1:name_suffix_lbl,
         $1:status_cd,
         $1:voter_status_desc,
         $1:reason_cd,
         $1:voter_status_reason_desc,
         $1:res_street_address,
         $1:res_city_desc,
         $1:state_cd,
         $1:zip_code,
         $1:mail_addr1,
         $1:mail_addr2,
         $1:mail_addr3,
         $1:mail_addr4,
         $1:mail_city,
         $1:mail_state,
         $1:mail_zipcode,
         $1:full_phone_number,
         $1:confidential_ind,
         $1:registr_dt,
         $1:race_code,
         $1:ethnic_code,
         $1:party_cd,
         $1:gender_code,
         $1:birth_year,
         $1:age_at_year_end,
         $1:birth_state,
         $1:drivers_lic,
         $1:precinct_abbrv,
         $1:precinct_desc,
         $1:municipality_abbrv,
         $1:municipality_desc,
         $1:ward_abbrv,
         $1:ward_desc,
         $1:cong_dist_abbrv,
         $1:super_court_abbrv,
         $1:judic_dist_abbrv,
         $1:nc_senate_abbrv,
         $1:nc_house_abbrv,
         $1:county_commiss_abbrv,
         $1:county_commiss_desc,
         $1:township_abbrv,
         $1:township_desc,
         $1:school_dist_abbrv,
         $1:school_dist_desc,
         $1:fire_dist_abbrv,
         $1:fire_dist_desc,
         $1:water_dist_abbrv,
         $1:water_dist_desc,
         $1:sewer_dist_abbrv,
         $1:sewer_dist_desc,
         $1:sanit_dist_abbrv,
         $1:sanit_dist_desc,
         $1:rescue_dist_abbrv,
         $1:rescue_dist_desc,
         $1:munic_dist_abbrv,
         $1:munic_dist_desc,
         $1:dist_1_abbrv,
         $1:dist_1_desc,
         $1:vtd_abbrv,
         $1:vtd_desc
  FROM @FINANCE_PARQUET/voters.parquet (FILE_FORMAT => "PARQUETFORMAT")
);