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

Question

Is it possible to do a   create table  lt mytable gt  as select  lt query statement gt    using   row format delimited fields terminated by        or to do a   create table  lt mytable gt  like  lt other table gt  row format delimited fields terminated by       The Language Manual seems to indicate not   but something tickles me I had achieved this in the past

User · Answer

Both the answers provided above work fine   CREATE TABLE person AS select   from employee  CREATE TABLE person LIKE employee

User · Answer

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

User · Answer

Let s say we have an external table called employee  hive gt  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

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

Examples related to hive

Examples related to sql-like

Examples related to create-table