#create sample csv data and put in hdfs
echo "row_num, name, menu_item" > external.csv
echo "1, 'George', 3/5/1996" >> external.csv
echo "2, 'Paul', 7/5,1943" >> external.csv
echo "3, 'Ringo', 12/6/1975" >> external.csv
echo "4, 'John', 1/30/1985" >> external.csv

hdfs dfs -mkdir -p /hdfsdata/demo/dummy_data/external_csv
hdfs dfs -put sample_data.csv /hdfsdata/demo/dummy_data/external_csv/external.csv

--Now create an external Hive table onto of the CSV in two different manners.
Drop table if exists `default`.external_csv_opencsv;

--This one doesn't work in TDP, but works in beeline and jdbc clients
CREATE EXTERNAL TABLE `default`.external_csv_opencsv (
  row_num integer,
  name    string,
  menu_item date)
COMMENT "This is a dummy data table for experimentation"
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = ",",
   "quoteChar"     = "'",
   "escapeChar"    = "\\"
)  
STORED AS TEXTFILE
LOCATION "/hdfsdata/demo/dummy_data/external_csv"
TBLPROPERTIES (
    'serialization.null.format' = '',
    'skip.header.line.count' = '1'
);

-- Next line in TDP produces: ODBC Database Error: [Hortonworks][SQLEngine] (31740) Table or view not found: HIVE..external_csv_opencsv 
Select * From `default`.external_csv_opencsv;



Drop table if exists `default`.external_csv_lazysimple;

--This one works in all clients
--obviously, this is a workaround for csv files, but won't work
--for JSON, xml, hbase, regex SerDe etc.
CREATE EXTERNAL TABLE `default`.external_csv_lazysimple (
  row_num integer,
  name    string,
  menu_item date)
COMMENT "This is a dummy data table for experimentation"
    ROW FORMAT DELIMITED
      FIELDS TERMINATED BY ','
      LINES TERMINATED BY '\n'  
STORED AS TEXTFILE
LOCATION "/hdfsdata/demo/dummy_data/external_csv"
TBLPROPERTIES (
    'serialization.null.format' = '',
    'skip.header.line.count' = '1'
);

Select * From `default`.external_csv_lazysimple; --Joy!