源数据
Mandy,Peking University-Wuhan University-Nankai University,Chemistry:90-Physics:98-Biology:83,126-135-140
Jerome,Tsinghua University-Fudan University-Nanjing University,History:89-Politics:92-Geography:87,130-116-128
Delia,Nanjing University-Wuhan University-Nankai University,Chemistry:87-Physics:95-Biology:73,102-123-112
Ben,Tianjin Universit-Peking University-Fudan University,Chemistry:92-Physics:88-Biology:79,98-142-106
Carter,Tsinghua University-Fudan University-Tianjin Universit,History:90-Politics:91-Geography:80,109-111-140
Vivian,Fudan University-Nanjing University-Nankai University,Chemistry:83-Physics:86-Biology:90,120-140-132
操作步骤
create external table student_exam_table(
student_name string,
intent_university Array<string>,
humanities_and_sciences map<string,float>,
comperhensive struct<chinese:float,maths:float,english:float>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
location '/hive_data/data/student_exam/';
SELECT * FROM student_exam_table ;
select student_name,intent_university[0] as first from student_exam_table;
select humanities_and_sciences["Physics"] as Physics,humanities_and_sciences["History"] as History from student_exam_table;
select student_name, comperhensive.maths as math from student_exam_table;
总结
定义格式符时我不熟悉,下面我在打一遍,定义的顺序不能变
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';
各类型取值