Issue creating Hive External table using tblproper

Question

I am trying to create an external table with tblproperties in Hive. The table gets created but it does not display the rows. Any ideas? Please find the scripts i am using below:

Thanks for your time and suggestions in advance.

Data is in a recursive folder: /user/test/test1/test2/samplefile.csv

use dw_raw;
drop table if exists temp_external_tab1;

create external table if not exists temp_external_tab1 (
col1 int,
col2 string,
col3 string,
col4 string
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile
location '/user/test/test1/'
tblproperties ("hive.input.dir.recursive" = "TRUE", 
    "hive.mapred.supports.subdirectories" = "TRUE",
    "hive.supports.subdirectories" = "TRUE", 
    "mapred.input.dir.recursive" = "TRUE");

Answer 1

These are not table properties, but global settings.

You should set these using 'set', i.e.:

set hive.mapred.supports.subdirectories=true;
set mapred.input.dir.recursive=true;

Answer 2

If you are using ambari the set the following properties to hive advanced config inside custom hive-site.xml.

SET hive.input.dir.recursive=TRUE

SET hive.mapred.supports.subdirectories=TRUE

SET hive.supports.subdirectories=TRUE

SET mapred.input.dir.recursive=TRUE

And then restart the affected services. This will read all the data recursively.

Answer 3

You've created a table but haven't put any data into it. Try

hive> LOAD DATA LOCAL INPATH '/user/test/test1/test2/samplefile.csv'
      INTO TABLE temp_external_tab1;