
Extract & Load: Quickly Importing Raw Data into PostgreSQL
This is a premium course, which you can purchase below.
We've done this all before so let's get rolling! The first thing we're going to do is to load up the INMS and chemical data into Postgres.
We'll update our import.sql
file since we don't need the master_plan
data any more. Here's the entire query:
drop schema if exists csvs cascade;
create schema csvs;
create table csvs.inms(
sclk text,
uttime text,
target text,
time_ca text,
targ_pos_x text,
targ_pos_y text,
targ_pos_z text,
source text,
data_reliability text,
table_set_id text,
coadd_cnt text,
osp_fil_1_status text,
oss_fil_2_status text,
csp_fil_3_status text,
css_fil_4_status text,
seq_table text,
cyc_num text,
cyc_table text,
scan_num text,
trap_table text,
sw_table text,
mass_table text,
focus_table text,
da_table text,
velocity_comp text,
ipnum text,
mass_per_charge text,
os_lens2 text,
os_lens1 text,
os_lens4 text,
os_lens3 text,
qp_lens2 text,
qp_lens1 text,
qp_lens4 text,
qp_lens3 text,
qp_bias text,
ion_defl2 text,
ion_defl1 text,
ion_defl4 text,
ion_defl3 text,
top_plate text,
p_energy text,
alt_t text,
view_dir_t_x text,
view_dir_t_y text,
view_dir_t_z text,
sc_pos_t_x text,
sc_pos_t_y text,
sc_pos_t_z text,
sc_vel_t_x text,
sc_vel_t_y text,
sc_vel_t_z text,
sc_vel_t_scx text,
sc_vel_t_scy text,
sc_vel_t_scz text,
lst_t text,
sza_t text,
ss_long_t text,
distance_s text,
view_dir_s_x text,
view_dir_s_y text,
view_dir_s_z text,
sc_pos_s_x text,
sc_pos_s_y text,
sc_pos_s_z text,
sc_vel_s_x text,
sc_vel_s_y text,
sc_vel_s_z text,
lst_s text,
sza_s text,
ss_long_s text,
sc_att_angle_ra text,
sc_att_angle_dec text,
sc_att_angle_tw text,
c1counts text,
c2counts text
);
copy csvs.inms
from '[Absolute Path To]/cassini/csvs/inms.csv'
delimiter ',' header csv;
-- the header rows are included in the concatenated set
-- removing
delete from csvs.inms where sclk='sclk' or sclk is null;