CTAS using Hive storage handlers

[#1]

create table as select * from my_storage_handler limit 5; //works fine

create table as select * from my_storage_handler limit 0; //gives following error, help appreciated!

Caused by: java.util.concurrent.ExecutionException: java.io.IOException: cannot find dir = hdfs://HDP046/user/hive/-mr-10003testuser.my_storage_handler{} in pathToPartitionInfo: [-mr-10003testuser.my_storage_handler{}] at java.util.concurrent.FutureTask.report(FutureTask.java:122) at java.util.concurrent.FutureTask.get(FutureTask.java:192) at org.apache.hadoop.hive.ql.io.CombineHiveInputFormat.getSplits(CombineHiveInputFormat.java:530) ... 39 more Caused by: java.io.IOException: cannot find dir = hdfs://HDP046/user/hive/-mr-10003testuser.my_storage_handler{} in pathToPartitionInfo: [-mr-10003testuser.my_storage_handler{}] at org.apache.hadoop.hive.ql.io.HiveFileFormatUtils.getPartitionDescFromPathRecursively(HiveFileFormatUtils.java:369) at org.apache.hadoop.hive.ql.io.HiveFileFormatUtils.getPartitionDescFromPathRecursively(HiveFileFormatUtils.java:331) at org.apache.hadoop.hive.ql.io.CombineHiveInputFormat$CheckNonCombinablePathCallable.call(CombineHiveInputFormat.java:103) at org.apache.hadoop.hive.ql.io.CombineHiveInputFormat$CheckNonCombinablePathCallable.call(CombineHiveInputFormat.java:86) ... 4 more

[#2]

What do you want to achieve by "limit 0"? If you want to create a new table with the same fields like another one you can use "CREATE TABLE empty_key_value_store LIKE key_value_store;" Details here.

[#3]

Hi @Predrag Minovic yes the idea is to create table with no data. I am aware of using like, but am also trying to understand why limit 0 wouldn't work? Ideally I like to support both. Thanks.

[#4]

Well, I've just tried on my cluster (HDP-2.5.3) and your command works without error, creating a new, empty table, I can see one Tez mapper running, and the table was created. You can check what "explain" says, and try running just "select ... limit 0". There were some optimizations on "limit 0" before, maybe it depends on Hive version. Also check your error "java.io.IOException: cannot find dir = hdfs://HDP046/user/hive/-mr10003testuser.my_storage_handler", Is your Hive warehouse under /user/hive, and do you have all permissions. But long story short, if everything else works just use create table ... LIKE ... and go ahead doing some more rewarding exercises 🙂

[#5]

Hi @Predrag Minovic, no, my warehouse is not under /user/hive, it's /apps/hive/warehouse. And I do think this is the issue. Since you asked, could you please let me know which property it's looking for so that I can try changing it to the correct warehouse dir? Thanks.