Mailing-List: contact common-commits-help@hadoop.apache.org; run by ezmlm
Precedence: bulk
Reply-To: common-dev@hadoop.apache.org
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
From: Apache Wiki <wikidiffs@apache.org>
To: Apache Wiki <wikidiffs@apache.org>
Date: Mon, 02 May 2011 23:29:37 -0000
Message-ID: <20110502232937.11299.79359@eos.apache.org>
Subject: 
 =?utf-8?q?=5BHadoop_Wiki=5D_Update_of_=22Hive/LanguageManual/DDL=22_by_Ph?=
 =?utf-8?q?iloVivero?=

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Hadoop Wiki" for ch=
ange notification.

The "Hive/LanguageManual/DDL" page has been changed by PhiloVivero.
The comment on this change is: Clarify what to do when partitioned values a=
re in the table data..
http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL?action=3Ddiff&rev1=3D=
87&rev2=3D88

--------------------------------------------------

  Use STORED BY to create a non-native table, for example in HBase.  See [[=
Hive/StorageHandlers]] for more information on this option.
  =

  Partitioned tables can be created using the PARTITIONED BY clause. A tabl=
e can have one or more partition columns and a separate data directory is c=
reated for each distinct value combination in the partition columns. Furthe=
r, tables or partitions can be bucketed using CLUSTERED BY columns, and dat=
a can be sorted within that bucket via SORT BY columns. This can improve pe=
rformance on certain kinds of queries.
+ =

+ If, when creating a partitioned table, you get this error: "FAILED: Error=
 in semantic analysis: Column repeated in partitioning columns," it means y=
ou are trying to include the partitioned column in the data of the table it=
self. You probably really do have the column defined. However, the partitio=
n you create makes a pseudocolumn on which you can query, so you must renam=
e your table column to something else (that users should not query on!).
+ =

+ Here is an example. Suppose your original table was this:
+ =

+ {{{
+ id     int,
+ date   date,
+ name   varchar
+ }}}
+ =

+ Now you want to partition on date. Your Hive definition would be this:
+ =

+ {{{
+ create table table_name (
+   id                int,
+   dtDontQuery       string,
+   name              string
+ )
+ partitioned by (date string)
+ }}}
+ =

+ Now your users will still query on "where date =3D '...'" but the 2nd col=
umn will be the original values.
  =

  Table names and column names are case insensitive but SerDe and property =
names are case sensitive.  Table and column comments are string literals (s=
ingle-quoted).  The TBLPROPERTIES clause allows you to tag the table defini=
tion with your own metadata key/value pairs.
 =20