5

I have 2 tables in Hive - first is external, the second one is managed. Managed table is populated from external using INSERT OVERWRITE...SELECT FROM external_table. Both tables are created with row delimited by ','. When I run selects queries into file, the delimiter in result file is Tab, but I need comma. How to change it to comma, I see no properties for that.

Valery Yesypenko
  • 412
  • 3
  • 6
  • 14

1 Answers1

19

First of all, you need to change you field delimiter , not your line delimiter ie.

hive >> CREATE TABLE some_table 
        (col1 int,
         col2 int,
         col3 string)
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
        STORED AS TEXTFILE;

Secondly, if you still face this issue, you can simply change it using sed.

bash >> hive -e 'select * from some_Table' | sed 's/[\t]/,/g'  > outputfile.txt

Please not that [\t] is to press Control+V and then the tab char:

sed 's/<Control+V><TAB character>/,/g'
Nicole Hu
  • 724
  • 4
  • 12
  • thanks Nicole. But I thought that there is some config to set comma as a default output delimiter. Such hive's behaviour is strange... – Valery Yesypenko Dec 10 '12 at 16:03
  • Could you please brief me whats happening here - `sed 's/[\t]/,/g' > outputfile.txt`. `hive -e` is for running hive from command prompt, correct? – Raja Reddy Nov 19 '13 at 09:22
  • 1
    @RajaReddy `hive -e` executes a hive query. then the pipe passes the query output to `sed`. `sed` is a text manipulation program. In this case the syntax `'s/[\t]/,/g'` has four sections separated by a `/`. The first `s` means substitute. The `[\t]` is the match pattern for a tab. `,` is the replace value, and `g` means globally. – ichbinallen Jun 26 '20 at 15:48