hadoop-common-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Hadoop Wiki] Update of "Hive/LanguageManual/DDL" by Ning Zhang
Date Fri, 09 Oct 2009 23:30:43 GMT
Dear Wiki user,

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

The "Hive/LanguageManual/DDL" page has been changed by Ning Zhang:
http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL?action=diff&rev1=17&rev2=18

  
  Table names and column names are case insensitive but SerDe and property names are case
sensitive.
  
- Tables can also be created and populated by the results of a query in one CTAS (create-table-as-select)
statement. There are two parts in CTAS, the select part can be any select statement supported
by HiveQL. The create part of the CTAS takes the result schema (column names are aliases in
the select clause and data types are derived from the select expressions) from the select
part and create the target table with other properties (such as SerDe and storage format).
The only restrictions in the CTAS is that the target table cannot be a partitioned table nor
an external table. 
+ Tables can also be created and populated by the results of a query in one CTAS (create-table-as-select)
statement. There are two parts in CTAS, the select part can be any select statement supported
by HiveQL. The create part of the CTAS takes the result schema (column names are aliases in
the select clause and data types are derived from the select expressions) from the select
part and create the target table with other properties (such as SerDe and storage format).
The only restrictions in the CTAS is that the target table cannot be a partitioned table nor
an external table. In addition, the table created by CTAS is atomic, meaning that the table
is not seen by other users until all the result of the SELECT part is finished and populated.
So other users will either see the table with the total results or will not see the table
at all.
  
  Examples:
  
@@ -128, +128 @@

     ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe" 
     STORED AS RCFile AS 
  SELECT (key % 1024) new_key, concat(key, value) key_value_pair 
- FROM src 
+ FROM key_value_store
  SORT BY new_key, key_value_pair;
  }}}
  
- The above CTAS statement create the target table new_key_value_store with the schema indicated
by the results of the SELECT statement. In addition, the new target table is using a specific
SerDe and storage format independent of the source tables in the SELECT statement. 
+ The above CTAS statement create the target table new_key_value_store with the schema derived
from the results of the SELECT statement. So the schema of the table new_key_value_store will
be (new_key DOUBLE, key_value_pair STRING). In addition, the new target table is using a specific
SerDe and a storage format independent of the source tables in the SELECT statement. 
  
  ==== Inserting Data Into Bucketed Tables ====
  The CLUSTER BY and SORTED BY creation commands do not effect how data is inserted into a
table -- only how it is read.  This means that users must actively insert data correctly by
specifying the number of reducers to be equal to the number of buckets, and using CLUSTER
BY and SORT BY commands in their query.

Mime
View raw message