Hi all:
I am new to hive, and I try to run a query through hive CLI and load the
result into mysql.
I try to redirect the CLI output to a tmp file and load the tmp file into
mysql table. The problem here is that some columns of our query result may
contains special chars, such as tab(\t), new line(\\), backslash (\) and so
on. The result in mysql table is corrupted by these chars because the CLI
output isn't escaped properly.
It seems what i want is mentioned in
hive-692<https://issues.apache.org/jira/browse/HIVE-692>,
but i can't find any solutions there.
A dirty way I think may be escaping the query result in HQL, such like:

regexp_replace(regexp_replace(regexp_replace(result_column_1, '\\\\',
'\\\\\\\\'), '\t', '\\\\t'), '\n', '\\\\n'),

I would like to know how to escape hive CLI output, or any alternative way
to accomplish such job.
Any help is appreciated.

Thanks.

What I'd do is write the query output into local directory like this:

INSERT OVERWRITE LOCAL DIRECTORY '/mydir'
SELECT …

The output columns will be delimited with ^A (\001). If you have to have tab delimited format you can replace them like this:

cat /mydir/* | tr "\001" "\t" >> /mynewdir/myfile.dat

I hope this helps.
-Nicole

From: wzc <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Reply-To: <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Date: Sat, 5 May 2012 01:20:15 +0800
To: <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Subject: how can I escape hive CLI output

Hi all:
I am new to hive, and I try to run a query through hive CLI and load the result into mysql.
I try to redirect the CLI output to a tmp file and load the tmp file into mysql table. The problem here is that some columns of our query result may contains special chars, such as tab(\t), new line(\\), backslash (\) and so on. The result in mysql table is corrupted by these chars because the CLI output isn't escaped properly.
It seems what i want is mentioned in  hive-692<https://issues.apache.org/jira/browse/HIVE-692>, but i can't find any solutions there.
A dirty way I think may be escaping the query result in HQL, such like:

regexp_replace(regexp_replace(regexp_replace(result_column_1, '\\\\', '\\\\\\\\'), '\t', '\\\\t'), '\n', '\\\\n'),

I would like to know how to escape hive CLI output, or any alternative way to accomplish such job.
Any help is appreciated.

Thanks.

hi Nicole,
  Thanks for your response.
  I will try your method and use '\001' as the separator(since there
contains tabs in the query results).

2012/5/5 Gesli, Nicole <[EMAIL PROTECTED]>

>  What I'd do is write the query output into local directory like this:
>
>  INSERT OVERWRITE LOCAL DIRECTORY '/mydir'
> SELECT …
>
>  The output columns will be delimited with ^A (\001). If you have to have
> tab delimited format you can replace them like this:
>
>  cat /mydir/* | tr "\001" "\t" >> /mynewdir/myfile.dat
>
>  I hope this helps.
> -Nicole
>
>   From: wzc <[EMAIL PROTECTED]>
> Reply-To: <[EMAIL PROTECTED]>
> Date: Sat, 5 May 2012 01:20:15 +0800
> To: <[EMAIL PROTECTED]>
> Subject: how can I escape hive CLI output
>
>  Hi all:
> I am new to hive, and I try to run a query through hive CLI and load the
> result into mysql.
> I try to redirect the CLI output to a tmp file and load the tmp file into
> mysql table. The problem here is that some columns of our query result may
> contains special chars, such as tab(\t), new line(\\), backslash (\) and so
> on. The result in mysql table is corrupted by these chars because the CLI
> output isn't escaped properly.
> It seems what i want is mentioned in  hive-692<https://issues.apache.org/jira/browse/HIVE-692>,
> but i can't find any solutions there.
> A dirty way I think may be escaping the query result in HQL, such like:
>
>  regexp_replace(regexp_replace(regexp_replace(result_column_1, '\\\\',
> '\\\\\\\\'), '\t', '\\\\t'), '\n', '\\\\n'),
>
>  I would like to know how to escape hive CLI output, or any alternative
> way to accomplish such job.
> Any help is appreciated.
>
>  Thanks.
>
>

Hi
    Hdfs/hive to any RDBMS , sqoop is the right tool to go for. It is exclusively meant for to and fro parallel data transfers between Rdbms and hdfs.
 
Regards
Bejoy KS

Sent from handheld, please excuse typos.

-----Original Message-----
From: wzc <[EMAIL PROTECTED]>
Date: Sat, 5 May 2012 02:18:39
To: <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Subject: Re: how can I escape hive CLI output

hi Nicole,
  Thanks for your response.
  I will try your method and use '\001' as the separator(since there
contains tabs in the query results).

2012/5/5 Gesli, Nicole <[EMAIL PROTECTED]>

>  What I'd do is write the query output into local directory like this:
>
>  INSERT OVERWRITE LOCAL DIRECTORY '/mydir'
> SELECT …
>
>  The output columns will be delimited with ^A (\001). If you have to have
> tab delimited format you can replace them like this:
>
>  cat /mydir/* | tr "\001" "\t" >> /mynewdir/myfile.dat
>
>  I hope this helps.
> -Nicole
>
>   From: wzc <[EMAIL PROTECTED]>
> Reply-To: <[EMAIL PROTECTED]>
> Date: Sat, 5 May 2012 01:20:15 +0800
> To: <[EMAIL PROTECTED]>
> Subject: how can I escape hive CLI output
>
>  Hi all:
> I am new to hive, and I try to run a query through hive CLI and load the
> result into mysql.
> I try to redirect the CLI output to a tmp file and load the tmp file into
> mysql table. The problem here is that some columns of our query result may
> contains special chars, such as tab(\t), new line(\\), backslash (\) and so
> on. The result in mysql table is corrupted by these chars because the CLI
> output isn't escaped properly.
> It seems what i want is mentioned in  hive-692<https://issues.apache.org/jira/browse/HIVE-692>,
> but i can't find any solutions there.
> A dirty way I think may be escaping the query result in HQL, such like:
>
>  regexp_replace(regexp_replace(regexp_replace(result_column_1, '\\\\',
> '\\\\\\\\'), '\t', '\\\\t'), '\n', '\\\\n'),
>
>  I would like to know how to escape hive CLI output, or any alternative
> way to accomplish such job.
> Any help is appreciated.
>
>  Thanks.
>
>