Create hive table using "as select" or "like" and also specify delimiter

Question

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.

Msp · Accepted Answer · 2020-01-31 04:29:55Z

110

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

Target cannot be partitioned table.
Target cannot be external table.
It copies the structure as well as the data

Create table like is also possible in Hive.

It just copies the source table definition.

edited Jan 31, 2020 at 4:29

Msp

2,4932 gold badges21 silver badges34 bronze badges

answered Mar 7, 2014 at 14:55

Venkatesh

1,3281 gold badge12 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

WestCoastProjects Over a year ago

Very well done answer! Addresses both (a) the specific syntax and (b) the limitations. Just what I was looking for.

Manindar Over a year ago

@Venkatesh, What will be the default field terminator/separator if i didn't mention it in case of CREATE TABLE <table_name> AS SELECT <some query from target table>. Will it use the target table's field separator or some default field separator.?

Ani Menon Over a year ago

@Manindar default hive delimiter is ^A or (ctrl-a)

Sakthivel · Accepted Answer · 2018-06-07 23:10:34Z

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')

To create a person table like employee

CREATE TABLE person LIKE employee;
To create a person external table like employee

CREATE TABLE person LIKE employee LOCATION 'maprfs:/user/hadoop/data/person';
then use DESC person; to see the newly created table schema.

I tried this but TBLPROPERTIES are not getting copied. Anyway to fix it.

Supermar · Accepted Answer · 2020-10-19 02:59:19Z

4

Both the answers provided above work fine.

CREATE TABLE person AS select * from employee;
CREATE TABLE person LIKE employee;

answered Oct 19, 2020 at 2:59

Supermar

512 bronze badges

Collectives™ on Stack Overflow

Create hive table using "as select" or "like" and also specify delimiter

3 Answers 3

3 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Linked

Related