Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (nike.apache.org: local policy includes SPF record at
 spf.trusted-forwarder.org)
MIME-Version: 1.0
In-Reply-To: 
 <CADFYdErEEJVX9hcGR-tAF6K5-KcpDjNAe4iktW650STkuWFd_g@mail.gmail.com>
References: 
 <CADFYdErEEJVX9hcGR-tAF6K5-KcpDjNAe4iktW650STkuWFd_g@mail.gmail.com>
Date: Wed, 18 Dec 2013 16:30:58 +0900
Message-ID: 
 <CACQ46vFRwa4dXohd1tJCDXYTrEAacOjo7Fd9RcNm6jLu3zg8BQ@mail.gmail.com>
Subject: Re: Pointing multiple external tables to the same location
From: =?EUC-KR?B?TmF2aXO3+b3Cv+w=?= <navis.ryu@nexr.com>
To: "user@hive.apache.org" <user@hive.apache.org>
Content-Type: multipart/alternative; boundary=047d7b33962fb31d7804edca061f

--047d7b33962fb31d7804edca061f
Content-Type: text/plain; charset=ISO-8859-1

Hive uses path to table(or partition) mapping internally (you can see that
in MapredWork, etc.), which might caused first table overwritten by other.

I didn't tried symlink on hdfs, which could be a solution.


2013/12/12 Petter von Dolwitz (Hem) <petter.von.dolwitz@gmail.com>

> Hi,
>
> I have declared several external tables pointing to the same location. The
> things that tells these tables apart (apart from their names) is that they
> have unique properties. These properties help me choose the correct rows
> from the underlying file. I use a single storage handler (accompanied by a
> single InputFormat and a single Serde) . The first columns in all tables
> are the same but the last (a struct) is unique and
> is constructed from the Serde (with help of the serde properties). A
> simplified version of the tables look like so:
>
> CREATE EXTERNAL TABLE Table1 (
>   column1 STRING,
>   column2 STRING)
>   STORED BY 'MyStorageHandler'
>   WITH SERDEPROPERTIES ('ser.class'='MyStructSerializationClass1')
>   LOCATION 'mylocation'
>   TBLPROPERTIES('recordreader.filter'='table1_filter');
>
> CREATE EXTERNAL TABLE Table2 (
>   column1 STRING,
>   column2 STRING)
>   STORED BY 'MyStorageHandler'
>   WITH SERDEPROPERTIES ('ser.class'='MyStructSerializationClass2')
>   LOCATION 'mylocation'
>   TBLPROPERTIES('recordreader.filter'='table2_filter');
>
>
> All works well for simple select queries towards the two tables. The
> following query gives very strange results though:
>
> SELECT * FROM (
>   SELECT column1,'Table1' FROM Table1 WHERE column2 = 'myValue'
>   union all
>   SELECT column1,'Table2' FROM Table2 WHERE column2 = 'myValue'
>   ) my_union
> ORDER BY my_union.column1
>
>
> It seems like one job task is created per file stored in the table
> location. This task gets the table properties from the second table and in
> the SerDe-step later on it seems like the records gets mixed up.
>
> I would have expected that hive would need to iterated the source files
> two times using two different tasks (with the correct table properties
> passed) in order to get this to work.
>
> Anyone here that can shed some light on this scenario?
>
> Thanks,
> Petter
>
>
>
>
>
>
>

--047d7b33962fb31d7804edca061f
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hive uses path to table(or partition) mapping internally (=
you can see that in MapredWork, etc.), which might caused=A0first table=A0o=
verwritten by other.=A0<div><br><div>I didn&#39;t tried symlink on hdfs, wh=
ich could be a solution.</div>
</div><div><br></div></div><div class=3D"gmail_extra"><br><br><div class=3D=
"gmail_quote">2013/12/12 Petter von Dolwitz (Hem) <span dir=3D"ltr">&lt;<a =
href=3D"mailto:petter.von.dolwitz@gmail.com" target=3D"_blank">petter.von.d=
olwitz@gmail.com</a>&gt;</span><br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div><div><div><div>Hi,<br>=
<br></div>I have declared several external tables pointing to the same loca=
tion. The things that tells these tables apart (apart from their names) is =
that they have unique properties. These properties help me choose the corre=
ct rows from the underlying file. I use a single storage handler (accompani=
ed by a single InputFormat and a single Serde) . The first columns in all t=
ables are the same but the last (a struct) is unique and <br>

is constructed from the Serde (with help of the serde properties). A simpli=
fied version of the tables look like so:<br><br>CREATE EXTERNAL TABLE Table=
1 (<br>=A0 column1 STRING,<br>=A0 column2 STRING)<br>=A0 STORED BY &#39;MyS=
torageHandler&#39;<br>

=A0 WITH SERDEPROPERTIES (&#39;ser.class&#39;=3D&#39;MyStructSerializationC=
lass1&#39;)<br>=A0 LOCATION &#39;mylocation&#39;<br>=A0 TBLPROPERTIES(&#39;=
recordreader.filter&#39;=3D&#39;table1_filter&#39;);<br><br>CREATE EXTERNAL=
 TABLE Table2 (<br>

=A0 column1 STRING,<br>=A0 column2 STRING)<br>=A0 STORED BY &#39;MyStorageH=
andler&#39;<br>=A0 WITH SERDEPROPERTIES (&#39;ser.class&#39;=3D&#39;MyStruc=
tSerializationClass2&#39;)<br>=A0 LOCATION &#39;mylocation&#39;<br>=A0 TBLP=
ROPERTIES(&#39;recordreader.filter&#39;=3D&#39;table2_filter&#39;);<br>

<br><br></div>All works well for simple select queries towards the two tabl=
es. The following query gives very strange results though:<br><br>SELECT * =
FROM (<br>=A0 SELECT column1,&#39;Table1&#39; FROM Table1 WHERE column2 =3D=
 &#39;myValue&#39;<br>

=A0 union all<br>=A0 SELECT column1,&#39;Table2&#39; FROM Table2 WHERE colu=
mn2 =3D &#39;myValue&#39;<br>=A0 ) my_union<br>ORDER BY my_union.column1<br=
><br><br></div>It seems like one job task is created per file stored in the=
 table location. This task gets the table properties from the second table =
and in the SerDe-step later on it seems like the records gets mixed up.<br>

<br></div>I would have expected that hive would need to iterated the source=
 files two times using two different tasks (with the correct table properti=
es passed) in order to get this to work.<br><div><div><br></div><div>Anyone=
 here that can shed some light on this scenario?<br>

<br></div><div>Thanks,<br>Petter<br></div><div><br><br><br><br><br><br></di=
v></div></div>
</blockquote></div><br></div>

--047d7b33962fb31d7804edca061f--