-- in this table, rows of different lengths(different number of columns) are loaded
CREATE TABLE t1_multi_delimit(colA int,
  colB tinyint,
  colC timestamp,
  colD smallint,
  colE smallint)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="^,")STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH "../../data/files/t1_multi_delimit.csv" INTO TABLE t1_multi_delimit;

SELECT * FROM t1_multi_delimit;

-- in this table, rows of different lengths(different number of columns) and it uses csv serde
CREATE TABLE t11_csv_serde(colA int,
  colB tinyint,
  colC timestamp,
  colD smallint,
  colE smallint)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",")STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH "../../data/files/t11_csv_serde.csv" INTO TABLE t11_csv_serde;

SELECT * FROM t11_csv_serde;

-- there should not be any difference between MultiDelimitSerDe table and OpenCSVSerde table results

SELECT EXISTS (
SELECT colA, colB, colC, colD, colE FROM t1_multi_delimit
MINUS
SELECT cast(colA as int), cast(colB as tinyint), cast(colC as timestamp), cast(colD as smallint), cast(colE as smallint) FROM t11_csv_serde
);

-- in this table, file having extra column is loaded
CREATE TABLE t2_multi_delimit(colA int,
  colB tinyint,
  colC timestamp,
  colD smallint,
  colE smallint)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="^,")STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH "../../data/files/t2_multi_delimit.csv" INTO TABLE t2_multi_delimit;

SELECT * FROM t2_multi_delimit;

-- in this table, delimiter of 5 characters is used
CREATE TABLE t3_multi_delimit(colA int,
  colB tinyint,
  colC timestamp,
  colD smallint,
  colE smallint)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="^^^^^")STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH "../../data/files/t3_multi_delimit.csv" INTO TABLE t3_multi_delimit;

SELECT * FROM t3_multi_delimit;

CREATE TABLE t4_multi_delimit(colA string,
  colB string,
  colC string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="^,")STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH "../../data/files/t4_multi_delimit.csv" INTO TABLE t4_multi_delimit;

SELECT * FROM t4_multi_delimit;

create table test_multidelim(col string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
with serdeproperties('field.delim'='!^') STORED AS TEXTFILE;

insert into test_multidelim values('aa'),('bb'),('cc'),('dd');

select * from test_multidelim;

DROP TABLE test_multidelim;
DROP TABLE t1_multi_delimit;
DROP TABLE t11_csv_serde;
DROP TABLE t2_multi_delimit;
DROP TABLE t3_multi_delimit;
DROP TABLE t4_multi_delimit;