● videoINMS Data Import
Extracting and Loading the INMS Data

Unlock PostgreSQL Fundamentals
2 hours of SQL video using NASA's Cassini data. See why developers become data pros.
SECTION
INMS Data Import
NEXT UP
Transformation, Part 1
COURSE
PostgreSQL Fundamentals
28 lessons
About this lesson
We'll update our import.sql file since we don't need the master_plan data any more. Here's the entire query:
<span class="hljs-keyword">drop</span> schema if <span class="hljs-keyword">exists</span> csvs cascade;
<span class="hljs-keyword">create</span> schema csvs;
<span class="hljs-keyword">create table</span> 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
);
<span class="hljs-keyword">copy</span> csvs.inms
<span class="hljs-keyword">from</span> <span class="hljs-string">'[Absolute Path To]/cassini/csvs/inms.csv'</span>
delimiter <span class="hljs-string">','</span> header csv;
<span class="hljs-comment">-- the header rows are included in the concatenated set</span>
<span class="hljs-comment">-- removing</span>
<span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> csvs.inms <span class="hljs-keyword">where</span> sclk<span class="hljs-operator">=</span><span class="hljs-string">'sclk'</span> <span class="hljs-keyword">or</span> sclk <span class="hljs-keyword">is</span> <span class="hljs-keyword">null</span>;
Unlock PostgreSQL Fundamentals
2 hours of SQL video using NASA's Cassini data. See why developers become data pros.