全面解读Hive官方文档与命令行实用教程

5星 · 超过95%的资源需积分: 50 46 浏览量更新于2024-07-23 5 收藏 1.19MB PDF 举报

Hive官方文档是一个全面指南，它详细介绍了Apache Hive在Hadoop生态系统中的使用方法。Hive是一种数据仓库工具，用于处理和管理大规模的数据集，特别适合处理结构化的、历史的批处理查询。本资源整理了关键知识点，包括以下几个方面： 1. **Hive CLI (命令行接口)**: Hive提供了一个名为`$HIVE_HOME/bin/hive`的命令行工具，支持交互式和批处理模式执行Hive查询。用户可以通过`-e`选项输入SQL命令，或从文件(`-f`)读取SQL脚本。 2. **命令行选项**: - `-d` 或 `--define`: 变量替换，允许用户在运行时设置变量。 - `-e` 和 `-f`: 分别用于执行单个SQL命令或加载SQL文件。 - `-h` 或 `--host` 和 `-p` 或 `--port`: 连接到远程Hive Server的主机名和端口号。 - `-S` 或 `--silent` 和 `-v` 或 `--verbose`: 控制交互式shell的行为，前者是静默模式，后者开启详细模式，显示执行的SQL语句。 3. **The hiverc File**: 用户可以创建一个名为`.hiverc`的配置文件，用于保存常用的环境变量和初始化命令，便于简化日常操作。 4. **Hive Batch Mode Commands** 和 **Hive Interactive Shell Commands**: 提供两种工作模式，前者执行批量作业，后者提供交互式的Shell环境，用户可以进行探索性数据分析。 5. **Logging**: 文档还涉及了Hive的日志管理，这对于监控和调试查询性能至关重要。 6. **Beeline CLI for HiveServer2**: Beeline是HiveServer2的命令行客户端，提供更安全的连接方式，并且支持SSL/TLS加密。 7. **HCatalog CLI**: HCatalog是Hive的一个扩展，提供元数据服务，使得Hive能够访问不同数据源，如HDFS、HBase等。 8. **Usage Examples**: 文档提供了大量实例，帮助读者理解和应用这些命令和选项。 Hive官方文档是一个深入学习和实践Hive的重要资源，无论你是初次接触Hive的新手，还是经验丰富的数据分析师，都可以从中找到所需的信息来优化查询性能，管理数据，以及利用Hive进行复杂的数据处理任务。通过理解和掌握这些核心概念，你可以充分利用Hive在大数据处理中的强大功能。

Exchange Partition

ALTER TABLE table_name_1 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_2;

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 and .Exchange Partition 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 hadoop fs

command), the metastore (and hence Hive) will not be aware of these partitions unless the user runs -put ALTER TABLE table_name ADD

commands on each of the newly added partitions.PARTITION

However, users can run

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 for more details.HIVE-874

The equivalent command on Amazon Elastic MapReduce (EMR)'s version of Hive is

ALTER TABLE table_name RECOVER PARTITIONS;

Drop Partitions

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).

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.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.

ALTER TABLE page_view DROP PARTITION (dt='2008-08-08', country='us');

(Un)Archive Partition

Version information

As of Hive 0.12 ( )HIVE-4095

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 for more informationLanguageManual Archiving

Alter Either Table or Partition

Alter Table/Partition File Format

ALTER TABLE table_name [PARTITION partitionSpec] SET 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.

Alter Table/Partition Location

ALTER TABLE table_name [PARTITION partitionSpec] SET LOCATION "new location"

Alter Table/Partition Touch

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/Partition Protections

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/Partition Compact

Version information

In Hive release and later when are being used, the ALTER TABLE statement can request of a table or0.13.0 transactions compaction

partition.

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] COMPACT

'compaction_type'

In general you do not need to request compactions when are being used, because the system will detect the need for them andHive transactions

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 Basic Design section in for more information.Hive Transactions

Alter Column

Rules for Column Names

Column names are case insensitive.

Change Column Name/Type/Position/Comment

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, , comment, or position, or an arbitrary combination of them.data type

Example:

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 touch data. Users should make sure the actual data layoutNOT

conforms with the metadata definition.

Add/Replace Columns

Version information

In Hive release 0.12.0 and earlier, column names can only contain alphanumeric and underscore characters.

In Hive release 0.13.0 and later, by default column names can be specified within backticks ( ) and contain any character (` Unicode HIV

). Within a string delimited by backticks, all characters are treated literally except that double backticks ( ) represent oneE-6013 ``

backtick character. The pre-0.13.0 behavior can be used by setting to , in which casehive.support.quoted.identifiers none

backticked names are interpreted as regular expressions. See for details.Supporting Quoted Identifiers in Column Names

ALTER TABLE table_name 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. This is supported for Avro backed

tables as well, for and later.Hive 0.14

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/Alter View

Create View

Drop View

Alter View Properties

Alter View As Select

Create View

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]

[COMMENT view_comment]

[TBLPROPERTIES (property_name = property_value, ...)]

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.

Views are read-only and may not be used as the target of LOAD/INSERT/ALTER. For changing metadata, see .ALTER VIEW

A view may contain ORDER BY and LIMIT clauses. If a referencing query also contains these clauses, the query-level clauses are evaluated afte

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 asr

(select * from v LIMIT 10), then at most 5 rows will be returned.

Starting with , the view's select statement can include one or more common table expressions (CTEs) as shown in the Hive 0.13.0 SELECT syntax

. For examples of CTEs in CREATE VIEW statements, see .Common Table Expression

Example of view creation:

Version information

View support is only available starting in Hive 0.6.

剩余97页未读，继续阅读

liling334

粉丝: 0
资源: 4

全面解读Hive官方文档与命令行实用教程

hive 0.11中文手册

hive开发规范.ppt

Hive使用手册Hive使用手册

hive官方文档翻译

帮我翻译hive官方文档

如何查看hive需要的mysql版本

hive函数大全 中文文档

尚硅谷hive on spark hive版本可行么

hivemetastore client工具类

jdbc 无法获取hive中表

最新资源

hive函数大全中文文档