Error creating table of external tables using tblproperties

Question

I am trying to create an external table with tblproperties in Hive. A table is created but does not display rows. Any ideas? Please find the scripts I use below:

Thanks for your time and suggestions in advance.

The data is in the 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 must install them using 'set', i.e.:

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

Answer 2

You created a table but did not put any data into it. Try

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

Answer 3

If you are using ambari, set the following properties to reinforce the advanced configuration 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 related services. This will recursively read all the data.