Create external HIVE table from files with differe

问题:

I have a CSV file with different record formats that is defined by the first column value: Sample Data:

"EL","XXXXXXX", 2017-07-17
"EH","XXXXXXX",1,2017-07-17,"AAA"
"BI","XXXXXXX","AAA","BBBB"

In this case, I am getting the file with 3 defined record types. Is there a way to load this to different hive tables ?

回答1:

Demo

create table el (s1 string,d1 date);
create table eh (s1 string,i1 int,dt1 date,s2 string);
create table bi (s1 string,s2 string,s3 string);

---

create external table myfile 
(
    c1  string
   ,c2  string
   ,c3  string
   ,c4  string
   ,c5  string
)

row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties 
(
    'separatorChar' = ','
   ,'quoteChar'     = '"'
   ,'escapeChar'    = '\\'
)  
stored as textfile
;

---

select * from myfile;

+-----+----------+--------------+-------------+-------+
| c1  |    c2    |      c3      |     c4      |  c5   |
+-----+----------+--------------+-------------+-------+
| EL  | XXXXXXX  |  2017-07-17  | NULL        | NULL  |
| EH  | XXXXXXX  | 1            | 2017-07-17  | AAA   |
| BI  | XXXXXXX  | AAA          | BBBB        | NULL  |
+-----+----------+--------------+-------------+-------+

---

from myfile
insert into el select c2,c3       where c1='EL'
insert into eh select c2,c3,c4,c5 where c1='EH'
insert into bi select c2,c3,c4    where c1='BI'
;

---

select * from el;

+----------+-------------+
|    s1    |     d1      |
+----------+-------------+
| XXXXXXX  | 2017-07-17  |
+----------+-------------+

---

select * from eh;

+----------+-----+-------------+------+
|    s1    | i1  |     dt1     |  s2  |
+----------+-----+-------------+------+
| XXXXXXX  | 1   | 2017-07-17  | AAA  |
+----------+-----+-------------+------+

---

select * from bi;

+----------+------+-------+
|    s1    |  s2  |  s3   |
+----------+------+-------+
| XXXXXXX  | AAA  | BBBB  |
+----------+------+-------+