...
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. Alter TableRename Table
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.) Alter Table Properties
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. Alter Table CommentTo change the comment of a table you have to change the
Add SerDe Properties
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:
Alter Table Storage Properties
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. Additional Alter Table StatementsSee Alter Either Table or Partition below for more DDL statements that alter tables. Alter PartitionAdd Partitions
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:
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.
An error is thrown if the partition_spec for the table already exists. You can use IF NOT EXISTS to skip the error. Dynamic PartitionsPartitions can be added to a table dynamically, using a Hive INSERT statement (or a Pig STORE statement). See these documents for details and examples:
Rename Partition
This statement lets you change the value of a partition column. Exchange Partition
This statement lets you move the data in a partition from a table to another table that has the same schema but does not already have that partition. For details, see Exchange Partition and HIVE-4095. 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 However, users can run
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
Drop Partitions
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).
The above command will drop that partition regardless of protection stats. In Hive 0.7.0 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.
(Un)Archive Partition
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 Alter Either Table or PartitionAlter Table/Partition 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. Alter Table/Partition Location
Alter Table/Partition Touch
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
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/Partition Compact
In general you do not need to request compactions when Hive transactions are being used, because the system will detect the need for them and initiate the compaction. However, if compaction is turned off for a table or you want to compact the table at a time the system would not choose to, ALTER TABLE can initiate the compaction. The statement will enqueue a request for compaction and return. To watch the progress of the compaction, use SHOW COMPACTIONS. The compaction_type can be MAJOR or MINOR. See the Compaction section in InsertUpdatesinHive.pdf for more information. Alter ColumnRules for Column NamesColumn names are case insensitive.
Change Column Name/Type/Position/Comment
This command will allow users to change a column's name, data type, comment, or position, or an arbitrary combination of them. Example:
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
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. |
...
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
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
Show Partitions
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. For example:
Example:
Show Table/Partition Extended
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 Table Properties
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
SHOW CREATE TABLE shows the CREATE TABLE statement that creates a given table, or the CREATE VIEW statement that creates a given view. Show Indexes
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
SHOW COLUMNS shows all the columns in a table including partition columns. Show Functions
SHOW FUNCTIONS lists all the user defined and builtin functions matching the regular expression. To get all functions use ".*" Show Granted Roles and PrivilegesThe Authorization document has information about these SHOW statements: Show Locks
SHOW LOCKS displays the locks on a table or partition. See Hive Concurrency Model for information about locks. When Hive transactions are being used, SHOW LOCKS returns this information (see HIVE-6460):
Show Transactions
SHOW TRANSACTIONS is for use by administrators. It returns a list of all currently open and aborted transactions in the system, including this information:
For information about Hive transactions, see InsertUpdatesinHive.pdf, which is attached to HIVE-5317. Show Compactions
SHOW COMPACTIONS returns a list of all tables and partitions currently being compacted or scheduled for compaction when Hive transactions are being used, including this information:
Compactions are initiated automatically, but can also be initiated manually with an ALTER TABLE COMPACT statement. More information about compaction and Hive transactions is available in InsertUpdatesinHive.pdf, which is attached to HIVE-5317. |
...