我试图创建一个配置单元外部表,并使用横向视图分解,我看到的结果是正确的。但是在创建一个表之后,我想查询主表而不使用横向视图。
我的代码:
ADD JAR wasb:///user/hivexmlserde-1.0.5.3.jar;
SET mapred.input.dir.recursive=true;
SET hive.mapred.supports.subdirectories=true;
DROP TABLE IF EXISTS losApplicantEmployer;
CREATE EXTERNAL TABLE losApplicantEmployer
(customer array<struct<customer_id:string, type:string>>, employer array<struct<employer_id:string,type:string,employer_name:string,years_employed:int,months_employed:int,create_date:string, update_date:string >>)
ROW FORMAT SERDE
'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES(
"column.xpath.customer" = "/LoanApplications/LoanApplication[@deal_type='REQUESTED']/LoanApplicationStates/LoanApplicationState/Customers/Customer",
"column.xpath.employer" = "/LoanApplications/LoanApplication[@deal_type='REQUESTED']/LoanApplicationStates/LoanApplicationState/Customers/Customer/Employers/Employer"
)
STORED AS INPUTFORMAT
'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION'wasb://glshadoopd3@glsllcteststorage1.blob.core.windows.net/Safe/2016/05/12'
TBLPROPERTIES ("xmlinput.start"="<LoanApplications xmlns","xmlinput.end"="</LoanApplications>" );
select exp_cust.customer_id, exp_cust.type, exp_emp.employer_id, exp_emp.type, exp_emp.employer_name, exp_emp.years_employed, exp_emp.months_employed, exp_emp.create_date, exp_emp.update_date
from losApplicantEmployer
lateral view explode(customer) exp_customer as exp_cust
lateral view explode(employer) exp_employer as exp_emp;
我想把上面代码的结果保存为一个表。所以我可以用它来查询。请告诉我怎么做?
我不想使用横向视图爆炸,每次我想查看结果。所以我觉得把整个结果存储在一个新的表中会很理想。因为losAppliantEmployer表只是一个结构数组。
1条答案
按热度按时间zujrkrfu1#
您可以使用create table as select存储结果: