Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> changing field delimiter for an existing table?


Copy link to this message
-
Re: changing field delimiter for an existing table?
That's exactly what I was looking for!
Thanks!

On Fri, May 11, 2012 at 5:34 PM, David Kulp <[EMAIL PROTECTED]> wrote:

> You're right.  I assumed there was a corresponding ALTER TABLE foo SET ROW
> FORMAT ...
> But I found the answer in the archives.  Modify the SERDE properties, e.g.
> SET SERDEPROPERTIES ('field.delim' = '|');
>
> http://osdir.com/ml/hive-user-hadoop-apache/2009-12/msg00109.html
>
>
> On May 11, 2012, at 8:15 PM, Igor Tatarinov wrote:
>
> Thanks but that requires fixing the table schema. Actually, I haven't
> found a way to change the delimiters of an existing table (created with a
> LIKE statement). I did find a workaround.
>
> While I don't know the schema of the data, I do know the number of
> columns, so I am going to create a table with N string columns and my
> preferred delimiter and then insert into it. Hive doesn't seem to mind
> inserting numeric data into a string column so that should work regardless
> of the expressions I will be "inserting".
>
>
> On Fri, May 11, 2012 at 5:07 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>
>> Here is the default textfile.  Substitute delimiters as necessary.
>> CREATE TABLE ...
>> ROW FORMAT DELIMITED
>> FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY '\002' MAP
>> KEYS TERMINATED BY '\003'
>> LINES TERMINATED BY '\n'
>> STORED AS TEXTFILE;
>>
>>
>> On May 11, 2012, at 5:58 PM, Igor Tatarinov wrote:
>>
>> Is that possible?
>>
>> What I am trying to do is create an S3 table using CTAS. Since CTAS
>> doesn't allow specifying a location, I have to create a managed table first:
>>
>> CREATE TABLE T AS
>> SELECT ...;
>>
>> (I don't want to fix T's schema because the list of selected expressions
>> is dynamically generated and can change.)
>>
>> Then, I want to create an s3 table like T:
>>
>> CREATE EXTERNAL TABLE S LIKE S
>> LOCATION ...;
>>
>> Unfortunately, I can't specify a different delimiter there ('\t' instead
>> of the default one).  Is there another way to do that before INSERTing into
>> the S3 table?
>>
>> Thanks!
>> igor
>> decide.com
>>
>>
>>
>
>