1

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???

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Rocky1989
  • 369
  • 8
  • 28
  • Is your database named `dbo` ? In your actual code for the create table statement is there a `'` just before the end of the statement i.e. is it `('ORC.compress' = 'ZLIB')';` as in the question or is it a type and your actual code is this `('ORC.compress' = 'ZLIB');`? – ggordon Nov 25 '21 at 14:27
  • Are there additional statements? I am unable to reproduce your problem. I ran the shared statements without the `dbo` and with the `('ORC.compress' = 'ZLIB');` amendment on hive 2.3 and received results with the inserted record. – ggordon Nov 25 '21 at 14:29
  • try to do the same but remove dbo from create table and select. Add `use dbo;` before everything instead, as a first statement in the session – leftjoin Nov 25 '21 at 16:12
  • @ggordon, Yes its a typo error. – Rocky1989 Nov 26 '21 at 05:49
  • I have tried without providing the database name and the output is same as before. The select statement is not able to retrieve the inserted records. But, create, insert and drop statements are working fine. Executed Query: ```1. use dbo; 2. create table `_temp_table` (emp_id int, emp_name string) stored as ORC tblproperties ('ORC.compress' = 'ZLIB'); 3. insert into table `_temp_table` values (123, 'ABC'); 4. select * from `_temp_table`;``` (Note <- I'm not able to get the records with the simple select statement) – Rocky1989 Nov 26 '21 at 06:38

1 Answers1

1

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.

leftjoin
  • 36,950
  • 8
  • 57
  • 116