CODEWITHSUNDEEP
sqlhivehiveqltemp-tableshiveddl
Rocky1989
Rocky1989

Reputation: 389

Hive Table name starts with underscore select statement issue

In the process of executing my hql script, i have to store data into a temporary table before inserting to the main table. In that scenario, I have tried to create a temporary table with an underscore at the starting.

Note: with quotes the table name with underscore is not working.

Working Create Statement:

create table 
dbo.`_temp_table` (
emp_id int, 
emp_name string) 
stored as ORC 
tblproperties ('ORC.compress' = 'ZLIB')';

Working Insert Statement:

insert into table dbo.`_temp_table` values (123, 'ABC');

But, the select statement on the temp table is not working and it is showing null records even though we have inserted the record as per insert statement.

select * from dbo.`_temp_table`;

Everything is working fine, but select statement to view the rows is not working. I still not sure, that we can create a temp table in the above way???

Upvotes: 1

Views: 1901

Answers (1)

leftjoin
leftjoin

Reputation: 38325

Hadoop uses such filenames started with underscore for hidden files and ignores them when reading. For example "_$folder$" file which is created when you execute mkdir to create empty folder in S3 bucket.

See HIVE-6431 - Hive table name start with underscore

By default, FileInputFormat(which is the super class of various formats) in hadoop ignores file name starts with "_" or ".", and hard to walk around this in hive codebase.

You can try to create external table and specify table location without underscore and still having underscore in table name. Also consider using TEMPORARY tables.

Upvotes: 1

Related Questions