CODEWITHSUNDEEP
hivehiveql
JKC
JKC

Reputation: 2618

Hive - Create Table statement with 'select query' and 'fields terminated by' commands

I want to create a table in Hive using a select statement which takes a subset of a data from another table. I used the following query to do so :

create table sample_db.out_table as 
select * from sample_db.in_table where country = 'Canada';

When I looked into the HDFS location of this table, there are no field separators.

But I need to create a table with filtered data from another table along with a field separator. For example I am trying to do something like :

create table sample_db.out_table as 
select * from sample_db.in_table where country = 'Canada'
ROW FORMAT SERDE 
  FIELDS TERMINATED BY '|';

This is not working though. I know the alternate way is to create a table structure with field names and the "FIELDS TERMINATED BY '|'" command and then load the data.

But is there any other way to combine the two into a single query that enables me to create a table with filtered data from another table and also with a field separator ?

Upvotes: 2

Views: 7764

Answers (2)

HbnKing
HbnKing

Reputation: 1882

Put row format delimited .. in front of AS select

do it like this Change the query to yours 

hive> CREATE TABLE ttt  row format delimited fields terminated by '|' AS select *,count(1) from t1 group by id ,name  ;
Query ID = root_20180702153737_37802c0e-525a-4b00-b8ec-9fac4a6d895b

here is the result 

[root@hadoop1 ~]# hadoop fs -cat /user/hive/warehouse/ttt/**
2|\N|1
3|\N|1
4|\N|1

Upvotes: 1

Indifferent
Indifferent

Reputation: 21

As you can see in the documentation, when using the CTAS (Create Table As Select) statement, the ROW FORMAT statement (in fact, all the settings related to the new table) goes before the SELECT statement.

Upvotes: 1

Related Questions