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 ?

2 Answers
2

Put row format delimited .. in front of AS select

row format delimited ..

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


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.

ROW FORMAT

SELECT

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.