...
Alter Table/Partition Statements
| Table of Content Zone |
|---|
|
Alter table statements enable you to change the structure of an existing table. You can add columns/partitions, change serde, add table and serde properties, or rename the table itself. Similarly, alter table partition statements allow you change the properties of a specific partition in the named table. |
...
Statements for Altering Tables| Code Block |
|---|
ALTER TABLE table_name |
ADD[IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...
partition_specThis statement lets you change the name of a table to a different name. As of version 0.6, a rename on a managed table moves its HDFS location as well. (Older Hive versions just renamed the table in the metastore without moving the HDFS location.) Alter Table Properties| Code Block |
|---|
ALTER TABLE table_name SET TBLPROPERTIES table_properties
table_properties:
: ( |
partitioncolpartition_colpartitioncolpartiton_colYou can use |
...
...
your own metadata to the tables. Currently last_modified_user, last_modified_time properties are automatically added and managed by Hive. Users can add their own properties to this list. You can do DESCRIBE EXTENDED TABLE to get this information. Add SerDe Properties| Code Block |
|---|
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]
ALTER TABLE table_name SET SERDEPROPERTIES serde_properties
serde_properties:
: (property_name = property_value, property_name = property_value, ... )
|
This statement enables you to add user defined metadata to table SerDe object. The serde properties are passed to the table's SerDe when it is being initialized by Hive to serialize and deserialize data. So users can store any information required for their custom serde here. Refer to SerDe section of Users Guide for more information. Example, note that both property_name and property_value must be quoted: |
Note that it is proper syntax to have multiple partition_spec in a single ALTER TABLE, but if you do this in version 0.7, your partitioning scheme will fail. That is, every query specifying a partition will always use only the first partition. Instead, you should use the following form if you want to add many partitions:
| Code Block |
|---|
|
ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'loc1';
ALTER TABLE table_name ADD PARTITION (partCol = 'value2') location 'loc2';
...ADDPARTITIONpartColvalueN location 'locN';
|
...
Alter Table Storage PropertiespageviewADDPARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';
|
An error is thrown if the partition_spec for the table already exists. You can use IF NOT EXISTS to skip the error.
Recover Partitions (MSCK REPAIR TABLE)
Hive stores a list of partitions for each table in its metastore. If, however, new partitions are directly added to HDFS (say by using hadoop fs -put command), the metastore (and hence Hive) will not be aware of these partitions unless the user runs ALTER TABLE table_name ADD PARTITION commands on each of the newly added partitions.
However, users can run
| Code Block |
|---|
MSCK REPAIR TABLE table_name;
|
which will add metadata about partitions to the Hive metastore for partitions for which such metadata doesn't already exist. In other words, it will add any partitions that exist on HDFS but not in metastore to the metastore. See HIVE-874 for more details.
The equivalent command on Amazon Elastic MapReduce (EMR)'s version of Hive is
| Code Block |
|---|
ALTER TABLE table_name RECOVER PARTITIONS;
|
...
| Code Block |
|---|
BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS
|
These statements change the table's physical storage properties. 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. Statements for Altering PartitionsAdd Partitions| Code Block |
|---|
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...
partition_spec:
: (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
|
You can use ALTER TABLE ADD PARTITION to add partitions to a table. Partition values should be quoted only if they are strings. The location must be a directory inside of which data files reside. Note that it is proper syntax to have multiple partition_spec in a single ALTER TABLE, but if you do this in version 0.7, your partitioning scheme will fail. That is, every query specifying a partition will always use only the first partition. Instead, you should use the following form if you want to add many partitions: | Code Block |
|---|
ALTER TABLE table_name |
DROP[IF EXISTS] PARTITION partition_spec, PARTITION partition_spec,...
|
You can use ALTER TABLE DROP PARTITION to drop a partition for a table. This removes the data and metadata for this partition.
For tables that are protected by NO DROP CASCADE, you can use the predicate IGNORE PROTECTION to drop a specified partition or set of partitions (for example, when splitting a table between two Hadoop clusters).
PARTITION (partCol = 'value1') location 'loc1';
ALTER TABLE table_name ADD PARTITION (partCol = 'value2') location 'loc2';
...
ALTER TABLE table_name |
DROP[IF EXISTS] PARTITION partition_spec IGNORE PROTECTION;
|
The above command will drop that partition regardless of protection stats.
...
PARTITION (partCol = 'valueN') location 'locN';
|
Specifically, the following example (which was the default example before) will FAIL silently and without error, and all queries will go only to dt='2008-08-08' partition, no matter which partition you specify. | Code Block |
|---|
ALTER TABLE page_view |
DROPADD PARTITION (dt='2008-08-08', country='us') |
;
|
Rename Table
| Code Block |
|---|
ALTER TABLE table_name RENAME TO new_table_name
|
This statement lets you change the name of a table to a different name.
As of version 0.6, a rename on a managed table moves its HDFS location as well. (Older Hive versions just renamed the table in the metastore without moving the HDFS location.)
Change Column Name/Type/Position/Comment
location '/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';
|
An error is thrown if the partition_spec for the table already exists. You can use IF NOT EXISTS to skip the error. Rename Partition| Code Block |
|---|
ALTER TABLE table_name |
CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
|
This command will allow users to change a column's name, data type, comment, or position, or an arbitrary combination of them.
Example:
| Code Block |
|---|
CREATE TABLE test_change (a int, b int, c int);
// will change column a's name to a1
ALTER TABLE test_change CHANGE a a1 INT;
// will change column a's name to a1, a's data type to string, and put it after column b. The new table's structure is: b int, a1 string, c int
ALTER TABLE test_change CHANGE a a1 STRING AFTER b;
// will change column b's name to b1, and put it as the first column. The new table's structure is: b1 int, a string, c int
ALTER TABLE test_change CHANGE b b1 INT FIRST;
|
NOTE: The column change command will only modify Hive's metadata, and will NOT touch data. Users should make sure the actual data layout conforms with the metadata definition.
...
PARTITION partition_spec RENAME TO PARTITION partition_spec;
|
This statement lets you change the value of a partition column. Recover Partitions (MSCK REPAIR TABLE)Hive stores a list of partitions for each table in its metastore. If, however, new partitions are directly added to HDFS (say by using hadoop fs -put command), the metastore (and hence Hive) will not be aware of these partitions unless the user runs ALTER TABLE table_name ADD PARTITION commands on each of the newly added partitions. However, users can run | Code Block |
|---|
MSCK REPAIR TABLE table_name;
|
which will add metadata about partitions to the Hive metastore for partitions for which such metadata doesn't already exist. In other words, it will add any partitions that exist on HDFS but not in metastore to the metastore. See HIVE-874 for more details. The equivalent command on Amazon Elastic MapReduce (EMR)'s version of Hive is | Code Block |
|---|
ALTER TABLE table_name RECOVER PARTITIONS;
|
Drop Partitions| Code Block |
|---|
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec, PARTITION partition_spec,...
|
You can use ALTER TABLE DROP PARTITION to drop a partition for a table. This removes the data and metadata for this partition. For tables that are protected by NO DROP CASCADE, you can use the predicate IGNORE PROTECTION to drop a specified partition or set of partitions (for example, when splitting a table between two Hadoop clusters). | Code Block |
|---|
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec IGNORE PROTECTION;
|
The above command will drop that partition regardless of protection stats. In Hive 0.70 or later, DROP returns an error if the partition doesn't exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true. tablenameADD|REPLACECOLUMNS (col_name data_type [COMMENT col_comment], ...)
|
ADD COLUMNS lets you add new columns to the end of the existing columns but before the partition columns.
REPLACE COLUMNS removes all existing columns and adds the new set of columns. This can be done only for tables with native serde (DynamicSerDe, MetadataTypedColumnsetSerDe, LazySimpleSerDe and ColumnarSerDe). Refer to SerDe section of User Guide for more information. REPLACE COLUMNS can also be used to drop columns. For example:
"ALTER TABLE test_change REPLACE COLUMNS (a int, b int);" will remove column `c' from test_change's schema. Note that this does not delete underlying data, it just changes the schema.
...
PARTITION (dt='2008-08-08', country='us');
|
(Un)Archive Partition| Code Block |
|---|
ALTER TABLE table_name ARCHIVE PARTITION partition_spec;
ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;
|
Archiving is a feature to moves a partition's files into a Hadoop Archive (HAR). Note that only the file count will be reduced; HAR does not provide any compression. See LanguageManual Archiving for more information Statements for Altering Either Tables or Partitions| Code Block |
|---|
ALTER TABLE table_name [PARTITION partitionSpec] SET |
TBLPROPERTIEStableproperties
table_properties:
: (property_name = property_value, property_name = property_value, ... )
|
You can use this statement to add your own metadata to the tables. Currently last_modified_user, last_modified_time properties are automatically added and managed by Hive. Users can add their own properties to this list. You can do DESCRIBE EXTENDED TABLE to get this information.
...
This statement changes the table's (or partition's) file format. For available file_format options, see the section above on CREATE TABLE. Alter Table/Partition Location| Code Block |
|---|
ALTER TABLE table_name [PARTITION partitionSpec] SET |
SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]Alter Table/Partition Touch| Code Block |
|---|
ALTER TABLE table_name |
SETSERDEPROPERTIES serde_properties
serde_properties:
: (property_name = property_value, property_name = property_value, ... )
|
This statement enables you to add user defined metadata to table SerDe object. The serde properties are passed to the table's SerDe when it is being initialized by Hive to serialize and deserialize data. So users can store any information required for their custom serde here. Refer to SerDe section of Users Guide for more information.
...
[PARTITION partitionSpec];
|
TOUCH reads the metadata, and writes it back. This has the effect of causing the pre/post execute hooks to fire. An example use case is if you have a hook that logs all the tables/partitions that were modified, along with an external script that alters the files on HDFS directly. Since the script modifies files outside of hive, the modification wouldn't be logged by the hook. The external script could call TOUCH to fire the hook and mark the said table or partition as modified. Also, it may be useful later if we incorporate reliable last modified times. Then touch would update that time as well. Note that TOUCH doesn't create a table or partition if it doesn't already exist. (See Create Table.) Alter Table/Partition Protections| Code Block |
|---|
ALTER TABLE table_name |
SET SERDEPROPERTIES ('field.delim' = ',');
|
Alter Table/Partition File Format
| Code Block |
|---|
[PARTITION partition_spec] ENABLE|DISABLE NO_DROP;
ALTER TABLE table_name [PARTITION |
partitionSpecSET FILEFORMAT file_format
|
This statement changes the table's (or partition's) file format. For available file_format options, see the section above on CREATE TABLE.
...
Protection on data can be set at either the table or partition level. Enabling NO_DROP prevents a table or partition from being dropped. Enabling OFFLINE prevents the data in a table or partition from being queried, but the metadata can still be accessed. Note, if any partition in a table has NO_DROP enabled, the table cannot be dropped either. Statements for Altering Columns| Code Block |
|---|
ALTER TABLE table_name |
CLUSTEREDBY(,, ...)SORTEDBY (name, ...)INTO num_buckets BUCKETS
|
These statements change the table's physical storage properties.
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.
Alter Table/Partition Location
| Code Block |
|---|
ALTER TABLE table_name [PARTITION partitionSpec] SET LOCATION "new location"
|
Alter Table Touch
| Code Block |
|---|
ALTER TABLE table_name TOUCH [PARTITION partitionSpec];
|
TOUCH reads the metadata, and writes it back. This has the effect of causing the pre/post execute hooks to fire. An example use case is if you have a hook that logs all the tables/partitions that were modified, along with an external script that alters the files on HDFS directly. Since the script modifies files outside of hive, the modification wouldn't be logged by the hook. The external script could call TOUCH to fire the hook and mark the said table or partition as modified.
Also, it may be useful later if we incorporate reliable last modified times. Then touch would update that time as well.
Note that TOUCH doesn't create a table or partition if it doesn't already exist. (See Create Table.)
Alter Table (Un)Archive
| Code Block |
|---|
ALTER TABLE table_name ARCHIVE PARTITION partition_spec;
ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;
|
Archiving is a feature to moves a partition's files into a Hadoop Archive (HAR). Note that only the file count will be reduced; HAR does not provide any compression. See LanguageManual Archiving for more information
...
[FIRST|AFTER column_name]
|
This command will allow users to change a column's name, data type, comment, or position, or an arbitrary combination of them. Example: | Code Block |
|---|
CREATE TABLE test_change (a int, b int, c int);
// will change column a's name to a1
ALTER TABLE test_change CHANGE a a1 INT;
// will change column a's name to a1, a's data type to string, and put it after column b. The new table's structure is: b int, a1 string, c int
ALTER TABLE test_change CHANGE a a1 STRING AFTER b;
// will change column b's name to b1, and put it as the first column. The new table's structure is: b1 int, a string, c int
ALTER TABLE test_change CHANGE b b1 INT FIRST;
|
NOTE: The column change command will only modify Hive's metadata, and will NOT touch data. Users should make sure the actual data layout conforms with the metadata definition. Add/Replace Columns| Code Block |
|---|
ALTER TABLE table_name |
[PARTITION partition_spec] ENABLE|DISABLE NO_DROP;
ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE OFFLINE;
|
Protection on data can be set at either the table or partition level. Enabling NO_DROP prevents a table or partition from being dropped. Enabling OFFLINE prevents the data in a table or partition from being queried, but the metadata can still be accessed. Note, if any partition in a table has NO_DROP enabled, the table cannot be dropped either.
Alter Table Rename Partition
| Code Block |
|---|
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
|
...
ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
|
ADD COLUMNS lets you add new columns to the end of the existing columns but before the partition columns. REPLACE COLUMNS removes all existing columns and adds the new set of columns. This can be done only for tables with native serde (DynamicSerDe, MetadataTypedColumnsetSerDe, LazySimpleSerDe and ColumnarSerDe). Refer to SerDe section of User Guide for more information. REPLACE COLUMNS can also be used to drop columns. For example: "ALTER TABLE test_change REPLACE COLUMNS (a int, b int);" will remove column `c' from test_change's schema. Note that this does not delete underlying data, it just changes the schema. |
Create/Drop View
| Info |
|---|
|
View support is only available starting in Hive 0.6. |
...
This statement lets you create a function that is implemented by the class_name. You can use this function in Hive queries as long as the session lasts. You can use any class that is in the class path of Hive. You can add jars to class path by executing 'ADD FILES' statements. Please refer to the CLI section in the User Guide for more information on how to add/delete files from the Hive classpath. Using this, you can register User Defined Functions (UDF's).
Drop Function
You can unregister a UDF as follows:
| Code Block |
|---|
DROP TEMPORARY FUNCTION [IF EXISTS] function_name
|
In Hive 0.70 or later, DROP returns an error if the function doesn't exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true.
Create/Drop Index
Create Index
| Code Block |
|---|
CREATE INDEX index_name
ON TABLE base_table_name (col_name, ...)
AS index_type
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_table_name]
[
[ ROW FORMAT ...] STORED AS ...
| STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]
[COMMENT "index comment"]
|
CREATE INDEX creates an index on a table using the given list of columns as keys. See CREATE INDEX in the Indexes design document.
Drop Index
| Code Block |
|---|
DROP INDEX [IF EXISTS] index_name ON table_name
|
DROP INDEX drops the index, as well as deleting the index table.
In Hive 0.7.0 or later, DROP returns an error if the index doesn't exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true.
Show/Describe Statements
These statements provide a way to query the Hive metastore for existing data and metadata accessible to this Hive system.
Show Databases
from the Hive classpath. Using this, you can register User Defined Functions (UDF's).
Drop Function
You can unregister a UDF as follows:
| Code Block |
|---|
DROP TEMPORARY FUNCTION [IF EXISTS] function_name
|
In Hive 0.70 or later, DROP returns an error if the function doesn't exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true.
Create/Drop Index
Create Index
| Code Block |
|---|
CREATE INDEX index_name
ON TABLE base_table_name (col_name, ...)
AS index_type
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_table_name]
[
[ ROW FORMAT ...] STORED AS ...
| STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]
[COMMENT "index comment"]
|
CREATE INDEX creates an index on a table using the given list of columns as keys. See CREATE INDEX in the Indexes design document.
Drop Index
| Code Block |
|---|
DROP INDEX [IF EXISTS] index_name ON table_name
|
DROP INDEX drops the index, as well as deleting the index table.
In Hive 0.7.0 or later, DROP returns an error if the index doesn't exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true.
Show/Describe Statements
| Table of Content Zone |
|---|
|
These statements provide a way to query the Hive metastore for existing data and metadata accessible to this Hive system. Show Databases| Code Block |
|---|
SHOW (DATABASES|SCHEMAS) [LIKE identifier_with_wildcards];
|
SHOW DATABASES lists all of the databases defined in the metastore. The optional LIKE clause allows the list of databases to be filtered using a regular expression. Wildcards in the regular expression can only be '' for any character(s) or '|' for a choice. Examples are 'employees', 'emp', 'emp*|*ees', all of which will match the database named 'employees'. Show Tables/Partitions/IndexesShow Tables| Code Block |
|---|
SHOW TABLES [IN database_name] [ |
|
| Code Block |
|---|
SHOW (DATABASES|SCHEMAS) [LIKE identifier_with_wildcards];
|
SHOW
|
...
...
the base tables and views in the current database (or the one explicitly named using the IN clause) with names matching the optional regular expression. Wildcards in the regular expression can only be '' for any character(s) or '|' for a choice. Examples are |
...
...
view|page', all which will match the 'page_view' table. Matching tables are listed in alphabetical order. It is not an error if there are no matching tables found in metastore. If no regular expression is given then all tables in the selected database are listed. Show Partitions |
...
| TABLES [IN database_name] [identifier_with_wildcards]; SHOW |
...
...
existing partitions for a given base table. Partitions are listed in alphabetical order. It is also possible to specify parts of a partition specification to filter the resulting list. Note: This feature is only available starting in version 0.6. | Code Block |
|---|
SHOW PARTITIONS table_name PARTITION(ds='2010-03-03');
SHOW PARTITIONS table_name PARTITION(hr='12');
SHOW PARTITIONS table_name PARTITION(ds='2010-03-03', hr='12');
|
Show Tables/Partitions Extended| Code Block |
|---|
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE identifier_with_wildcards [PARTITION(partition_desc)]
|
SHOW TABLE EXTENDED will list information for all tables matching the given regular expression. Users cannot use regular expression for table name if a partition specification is present. This command's output includes basic table information and file system information like totalNumberFiles, totalFileSize, maxFileSize, minFileSize,lastAccessTime, and lastUpdateTime. If partition is present, it will output the given partition's file system information instead of table's file system information. Show Columns| Code Block |
|---|
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]
|
SHOW COLUMNS shows all the columns in a table including partition columns. Show Table Properties| Code Block |
|---|
SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("foo");
|
The first form lists all of the table properties for the table in question one per row separated by tabs. The second form of the command prints only the value for the property that's being asked for. Show Create Table| Code Block |
|---|
SHOW CREATE TABLE ([db_name.]table_name|view_name)
|
SHOW CREATE TABLE shows the CREATE TABLE statement that creates a given table, or the CREATE VIEW statement that creates a given view. |
Show Table Properties
| Code Block |
|---|
SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("foo");
|
The first form lists all of the table properties for the table in question one per row separated by tabs. The second form of the command prints only the value for the property that's being asked for.
Show Partitions
| Code Block |
|---|
SHOW PARTITIONS table_name
|
SHOW PARTITIONS lists all the existing partitions for a given base table. Partitions are listed in alphabetical order.
It is also possible to specify parts of a partition specification to filter the resulting list. Note: This feature is only available starting in version 0.6.
| Code Block |
|---|
SHOW PARTITIONS table_name PARTITION(ds='2010-03-03');
SHOW PARTITIONS table_name PARTITION(hr='12');
SHOW PARTITIONS table_name PARTITION(ds='2010-03-03', hr='12');
|
Show Table/Partitions Extended
| Code Block |
|---|
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE identifier_with_wildcards [PARTITION(partition_desc)]
|
SHOW TABLE EXTENDED will list information for all tables matching the given regular expression. Users can not use regular expression for table name if a partition specification is present. This command's output includes basic table information and file system information like totalNumberFiles, totalFileSize, maxFileSize, minFileSize,lastAccessTime, and lastUpdateTime. If partition is present, it will output the given partition's file system information instead of table's file system information.
Show Functions
| Code Block |
|---|
SHOW FUNCTIONS "a.*"
|
...
Show Indexes| Code Block |
|---|
SHOW [FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name]
|
SHOW INDEXES shows all of the indexes on a certain column, as well as information about them: index name, table name, names of the columns used as keys, index table name, index type, and comment. If the FORMATTED keyword is used, then column titles are printed for each column |
...
Show Columns
| Code Block |
|---|
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]
|
...
...
| CREATE TABLE ([db_name.]table_name|view_name)
|
...
SHOW FUNCTIONS lists all the user defined and builtin functions matching the regular expression. To get all functions use ".*" Describe Database/Table/Partition/ColumnDescribe Database| Code Block |
|---|
DESCRIBE DATABASE db_name
|
DESCRIBE DATABASE shows the name of the database, its comment (if one has been set), and its root location on the filesystem. Describe Table/View/Column| Code Block |
|---|
DESCRIBE [EXTENDED|FORMATTED] [db_name.]table_name[DOT col_name ( [DOT field_name] | [DOT '$elem$'] | [DOT '$key$'] | [DOT '$value$'] )* ]
|
DESCRIBE shows the list of columns including partition columns for the given table. If the EXTENDED keyword is specified then it will show all the metadata for the table in Thrift serialized form. This is generally only useful for debugging and not for general use. If the FORMATTED keyword is specified, then it will show the metadata in a tabular format. If a table has complex column then you can examine the attributes of this column by specifying table_name.complex_col_name (and '$elem$' for array element, '$key$' for map key, and '$value$' for map value). You can specify this recursively to explore the complex column type. For a view, DESCRIBE EXTENDED or FORMATTED can be used to retrieve the view's definition. Two relevant attributes are provided: both the original view definition as specified by the user, and an expanded definition used internally by Hive. Describe Partition| Code Block |
|---|
DESCRIBE [EXTENDED|FORMATTED] [db_name.]table_name PARTITION partition_spec
|
This statement lists metadata for a given partition. The output is similar to that of DESCRIBE table_name. Presently, the column information associated with a particular partition is not used while preparing plans. Example: | Code Block |
|---|
DESCRIBE page_view PARTITION (ds='2008-08-08');
|
|