Create table in HIVE with same columns as another table?

Question

I want to create table from some metadata of another table in hive. I'm using hive.

I know from this question that the metatdata can be retrieved from the table by INFORMATION_SCHEMA.COLUMNS in sql:

Does HIVE have a similar access to metadata of a table to allow me to create a table using the columns of another table? Essentially, I'm copying a table without all of the tuples.

This is the best thing I have so far:

create table <table_name>( (select <table_name> from INFORMATION_SCHEMA.COLUMNS)) row format delimited fields by '|';

score 18 · Answer 1 · answered Nov 13 '15 at 19:53

18

You can use LIKE so the new table gets the structure but not the data.

Hive documentation

CREATE TABLE yourtable
LIKE table2;

answered Nov 13 '15 at 19:53

Vamsi Prabhala

47,581
4
34
53

Very nice! I didn't know that this existed. This should be the correct answer. It's far more concise. – Ben Watson Nov 13 '15 at 23:43

score 10 · Accepted Answer · answered Nov 13 '15 at 19:52

10

You can try CTAS syntax:

CREATE TABLE new_table_name
AS
SELECT *
FROM old_table_name
WHERE 1 = 2;

answered Nov 13 '15 at 19:52

Lukasz Szozda

139,860
19
198
228

For big data this is slower though, right? it will still have to run through each row. – makansij Nov 13 '15 at 20:40
1

@Sother Look at `WHERE 1 = 2`. It is **always false** so you get empty table – Lukasz Szozda Nov 13 '15 at 20:41
but I don't think sql evaluates it that way. I think it still checks every single row. – makansij Nov 13 '15 at 20:47
@Sother Did you try? The point the query optimizer is smart enougt to spot that it shouldn't go through all rows. Just check execution plan – Lukasz Szozda Nov 13 '15 at 20:48
1

@Sother Demo for you http://sqlfiddle.com/#!9/c2a75/2/0 See execution plan: **`Impossible WHERE`** and start beliving :) – Lukasz Szozda Nov 13 '15 at 20:53
1

This solution will not result in a table with a partitioning scheme matching the original table. – abeboparebop Nov 21 '18 at 16:58

score 0 · Answer 3 · edited Nov 13 '15 at 21:48

0

I believe this will do the trick:

DROP TABLE IF EXISTS schema.New_Table;    
CREATE TABLE New_Table    
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\034' LINES TERMINATED BY '\012'
STORED AS TEXTFILE location '/IC/New_Table'   
AS   
select *   
from Old_Table;

edited Nov 13 '15 at 21:48

o-90

15,749
10
40
61

answered Nov 13 '15 at 19:58

Aaron Faltesek

189
1
11

What does the '\034' do? – makansij Nov 13 '15 at 20:36
It specifies the delimiter. '\034' is the serde code for '\x1c' I believe. I think if you take out that portion it just defaults to the default delim. Add the where 1=2 if you don't want to bring in any data. Run the following to see what the old_table uses. "show create table Old_Table". This will display what the old table uses as it's delimiters and you can use those for your new table also. – Aaron Faltesek Nov 13 '15 at 20:40
also to prevent from going through each record do this: "select * from old_table limit 1;" – Aaron Faltesek Nov 13 '15 at 20:50

Create table in HIVE with same columns as another table?

3 Answers3