50

Is it possible to do a

create table <mytable> as select <query statement>

using 

row format delimited fields terminated by '|';

or to do a

create table <mytable> like <other_table> row format delimited fields terminated by '|';

The Language Manual seems to indicate not.. but something tickles me I had achieved this in the past.

WestCoastProjects
  • 58,982
  • 91
  • 316
  • 560

3 Answers3

109

Create Table as select (CTAS) is possible in Hive.

You can try out below command:

CREATE TABLE new_test 
    row format delimited 
    fields terminated by '|' 
    STORED AS RCFile 
AS select * from source where col=1
  1. Target cannot be partitioned table.
  2. Target cannot be external table.
  3. It copies the structure as well as the data

Create table like is also possible in Hive.

  1. It just copies the source table definition.
Msp
  • 2,493
  • 2
  • 20
  • 34
Venkatesh
  • 1,308
  • 1
  • 12
  • 10
  • 1
    Very well done answer! Addresses both (a) the specific syntax and (b) the limitations. Just what I was looking for. – WestCoastProjects Mar 07 '14 at 16:13
  • @Venkatesh, What will be the default field terminator/separator if i didn't mention it in case of `CREATE TABLE AS SELECT `. Will it use the target table's field separator or some default field separator.? – Manindar Jun 20 '17 at 11:08
  • 2
    @Manindar default hive delimiter is `^A` or `(ctrl-a)` – Ani Menon Jun 12 '18 at 15:51
5

Let's say we have an external table called employee

hive> SHOW CREATE TABLE employee;
OK
CREATE EXTERNAL TABLE employee(
  id string,
  fname string,
  lname string, 
  salary double)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'colelction.delim'=':',
  'field.delim'=',',
  'line.delim'='\n',
  'serialization.format'=',')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'maprfs:/user/hadoop/data/employee'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false',
  'numFiles'='0',
  'numRows'='-1',
  'rawDataSize'='-1',
  'totalSize'='0',
  'transient_lastDdlTime'='1487884795')

  1. To create a person table like employee

    CREATE TABLE person LIKE employee;

  2. To create a person external table like employee

    CREATE TABLE person LIKE employee LOCATION 'maprfs:/user/hadoop/data/person';

  3. then use DESC person; to see the newly created table schema.

Sakthivel
  • 576
  • 8
  • 15
2

Both the answers provided above work fine.

  1. CREATE TABLE person AS select * from employee;
  2. CREATE TABLE person LIKE employee;
Supermar
  • 31
  • 2