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 JohnSichi. http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL?action=diff&rev1=44&rev2=45 -------------------------------------------------- | TEXTFILE | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname }}} - CREATE TABLE creates a table with the given name. An error is thrown if a table with the same name exists. You can use IF NOT EXISTS to skip the error. + CREATE TABLE creates a table with the given name. An error is thrown if a table or view with the same name already exists. You can use IF NOT EXISTS to skip the error. The EXTERNAL keyword lets you create a table and provide a LOCATION so that Hive does not use a default location for this table. This comes in handy if you already have data generated. When dropping an EXTERNAL table, data in the table is NOT deleted from the file system. @@ -158, +158 @@ When dropping an EXTERNAL table, data in the table will NOT be deleted from the file system. + When dropping a table referenced by views, no warning is given (the views are left dangling as invalid and must be dropped or recreated by the user). + See the next section on ALTER TABLE for how to drop partitions. == Alter Table Statements == @@ -242, +244 @@ NOTE: These commands will only modify Hive's metadata, and will NOT reorganize or reformat existing data. Users should make sure the actual data layout conforms with the metadata definition. + == Create/Drop View == + + ''Note:'' View support is only available starting in Hive 0.6. + + === Create View === + + {{{ + CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ] + [COMMENT view_comment] + AS SELECT ... + }}} + + CREATE VIEW creates a view with the given name. An error is thrown if a table or view with the same name already exists. You can use IF NOT EXISTS to skip the error. + + If no column names are supplied, the names of the view's columns will be derived automatically from the defining SELECT expression. (If the SELECT contains unaliased scalar expressions such as x+y, the resulting view column names will be generated in the form _C0, _C1, etc.) When renaming columns, column comments can also optionally be supplied. (Comments are not automatically inherited from underlying columns.) + + A CREATE VIEW statement will fail if the view's defining SELECT expression is invalid. + + Note that a view is a purely logical object with no associated storage. (No support for materialized views is currently available in Hive.) When a query references a view, the view's definition is evaluated in order to produce a set of rows for further processing by the query. (This is a conceptual description; in fact, as part of query optimization, Hive may combine the view's definition with the query's, e.g. pushing filters from the query down into the view.) + + A view's schema is frozen at the time the view is created; subsequent changes to underlying tables (e.g. adding a column) will not be reflected in the view's schema. If an underlying table is dropped or changed in an incompatible fashion, subsequent attempts to query the invalid view will fail. + + A view may contain ORDER BY and LIMIT clauses. If a referencing query also contains these clauses, the query-level clauses are evaluated '''after''' the view clauses (and after any other operations in the query). For example, if a view specifies LIMIT 5, and a referencing query is executed as (select * from v LIMIT 10), then at most 5 rows will be returned. + + Example of view creation: + + {{{ + CREATE VIEW onion_referrers(url COMMENT 'URL of Referring page') + COMMENT 'Referrers to The Onion website' + AS + SELECT DISTINCT referrer_url + FROM page_view + WHERE page_url='http://www.theonion.com'; + }}} + + === Drop View === + + {{{ + DROP VIEW view_name + }}} + + DROP VIEW removes metadata for the specified view. (It is illegal to use DROP TABLE on a view.) + + When dropping a view referenced by other views, no warning is given (the dependent views are left dangling as invalid and must be dropped or recreated by the user). + + Example: + + {{{ + DROP VIEW onion_referrers; + }}} + == Create/Drop Function == === Create Function === {{{
