Hive3查询基础知识

使用Apache Hive，您可以查询包括Hadoop数据在内的分布式数据存储。

Hive支持ANSI SQL和原子性、一致性、隔离性和持久性（ACID）事务。要更新数据，可以使用MERGE语句，该语句现在也符合ACID标准。物化视图基于访问模式优化查询。Hive支持“优化行列”（ORC）格式的表最大支持300PB。还支持其他文件格式。您可以创建类似于传统关系数据库中的表的表。您可以使用熟悉的插入、更新、删除和合并SQL语句来查询表数据。insert语句将数据写入表。更新和删除语句修改和删除已经写入Hive的值。merge语句通过利用共存表来简化更新、删除和更改数据捕获操作。

查询information_schema数据库

Hive支持ANSI标准的information_schema数据库，您可以在该数据库中查询有关表、视图、列和Hive特权的信息。information_schema数据显示了系统状态，类似于sys数据库数据，但是以一种用户友好的只读方式显示。您可以在information_schema查询中使用联接、聚合、过滤器和投影。

以下步骤之一涉及更改HiveServer与策略之间同步的时间间隔。HiveServer会在此时间间隔内响应任何策略更改。您只能在information_schema数据库中查询自己的特权信息。

1. 打开Ranger Access Manager，并检查是否为group启用了预加载default database tables columns和information_schema database策略 public。

2. 导航到服务> 配置单元> 配置> 高级> 自定义配置单元站点。

3. 添加hive.privilege.synchronizer.interval键并将其值设置为1。

此设置将同步从默认的半小时更改为一分钟。

4. 在Beeline shell中，启动Hive，并检查Ambari是否安装了information_schema数据库：

SHOW DATABASES;...+---------------------+|    database_name    |+---------------------+| default             || information_schema  || sys                 |+---------------------+

5. 使用information_schema数据库列出数据库中的表。

USE information_schema;...SHOW TABLES;...+--------------------+|      tab_name      |+--------------------+| column_privileges  || columns            || schemata           || table_privileges   || tables             || views              |+--------------------+

6. 查询information_schema数据库以查看例如有关可在其中插入值的表的信息。

SELECT * FROM information_schema.tables WHERE is_insertable_into='YES' limit 2;...+--------------------+-------------------+-----------------|tables.table_catalog|tables.table_schema|tables.table_name+--------------------+-------------------+-----------------|default             |default            |students2|default             |default            |t3

将数据插入ACID表

您可以将数据插入到Hive仓库中的“优化行列式（ORC）”表中。

您可以指定分区，如以下语法所示：

INSERT INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] VALUES values_row [, values_row...]

其中

values_row是(value [, value]) ，value可以为NULL或任何SQL文字。

1. 创建一个包含学生信息的表。

CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3,2));

2. 在表格中插入几个学生的姓名，年龄和gpa值。

INSERT INTO TABLE students VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);

3. 创建一个称为综合浏览量的表，并将空值分配给您不想分配值的列。

CREATE TABLE pageviews (userid VARCHAR(64), link STRING, from STRING) PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS;INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23') VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null); INSERT INTO TABLE pageviews PARTITION (datestamp) VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21'); 

更新表中的数据

您可以使用UPDATE语句修改已经存储在Apache Hive表中的数据。

使用以下语法构造UPDATE语句：

UPDATE tablename SET column = value [, column = value ...] [WHERE expression];

根据可选WHERE子句中指定的条件，UPDATE语句可能会影响表中的每一行。WHERE子句中的表达式必须是Hive SELECT子句支持的表达式。SET语句的右侧不允许子查询。分区和存储桶列无法更新。

您必须具有SELECT和UPDATE特权才能使用UPDATE语句。

创建一条语句来更改gpa列的值为1.0的所有行的name列中的值。

UPDATE students SET name = null WHERE gpa <= 1.0;

合并表中的数据

您可以使用ACID MERGE语句有条件地在Hive表中插入，更新或删除现有数据。

MERGE语句基于ANSI标准SQL。

1. 构造查询以更新customer表中的客户名称和状态，以匹配new_customer_stage表中具有相同ID的客户的名称和状态。

2. 增强查询以将数据从new_customer_stage表插入到customer表（如果尚不存在）。

MERGE INTO customer USING (SELECT * FROM new_customer_stage) sub ON sub.id = customer.id WHEN MATCHED THEN UPDATE SET name = sub.name, state = sub.new_state WHEN NOT MATCHED THEN INSERT VALUES (sub.id, sub.name, sub.state);

从表中删除数据

您可以使用DELETE语句删除已经写入ACID表的数据。

使用以下语法从Hive表中删除数据。

DELETE FROM tablename [WHERE expression];

如果gpa列的值为1或0，请从学生表中删除所有数据行。

DELETE FROM students WHERE gpa <= 1,0;

创建一个临时表

在CDP数据中心中，您可以创建一个临时表来提高性能，方法是临时存储数据以供复杂查询中间使用或重用。

临时表数据仅在当前Apache Hive会话期间持续存在。Hive在会话结束时删除表。如果使用永久表的名称来创建临时表，则在会话期间无法访问该永久表，除非您删除或重命名该临时表。您可以创建一个与其他用户的临时表同名的临时表，因为用户会话是独立的。临时表不支持分区的列和索引。

仅CDP数据中心

1. 创建一个具有一个字符串列的临时表。

CREATE TEMPORARY TABLE tmp1（tname varchar（64））;

2. 使用CREATE TABLE AS SELECT（CTAS）语句创建一个临时表。

CREATE TEMPORARY TABLE tmp2 AS SELECT c2, c3, c4 FROM mytable;

3. 使用CREATE TEMPORARY TABLE LIKE语句创建一个临时表。

CREATE TEMPORARY TABLE tmp3 LIKE tmp1;

配置临时表存储

在CDP数据中心中，您可以更改临时表数据的存储以满足系统要求。

默认情况下，Apache Hive将临时表数据存储在默认用户暂存目录/ tmp / hive- <用户名>中。通常，默认情况下不会将此位置设置为容纳大量数据，例如临时表产生的数据。

在CDP数据中心版本上

1. 通过设置hive.exec.temporary.table.storage，将Hive配置为将临时表数据存储在内存中或SSD上。

a) 将数据存储在内存中。 hive.exec.temporary.table.storage=memory

b) 将数据存储在SSD上。 hive.exec.temporary.table.storage=ssd

2. 创建和使用临时表。

a) Hive在会话结束时删除临时表。

使用子查询

Hive支持可用于许多Hive操作的FROM子句和WHERE子句中的子查询，例如，根据另一个表的内容过滤来自一个表的数据。

子查询是内部查询中的SQL表达式，它将结果集返回到外部查询。从结果集中，评估外部查询。外部查询是包含内部子查询的主要查询。WHERE子句中的子查询包含查询谓词和谓词运算符。谓词是计算为布尔值的条件。子查询中的谓词还必须包含谓词运算符。谓词运算符指定在谓词查询中测试的关系。

如果表中的year列的值与us_census表中的年份匹配，则从transfer_payments表中选择所有state和net_payments值。

SELECT state, net_paymentsFROM transfer_paymentsWHERE transfer_payments.year IN (SELECT year FROM us_census); 

谓词以第一个WHERE关键字开头。谓词运算符是IN关键字。

如果us_census表的至少一行中的年值与transfer_payments表中的年值匹配，则谓词对于transfer_payments表中的一行返回true。

子查询限制

为了有效地构造查询，您必须了解WHERE子句中的子查询的限制。

• 子查询必须出现在表达式的右侧。

• 不支持嵌套子查询。

• 单个查询只能有一个子查询表达式。

• 子查询谓词必须显示为顶级连接词。

• 子查询在查询谓词中支持四个逻辑运算符：IN，NOT IN，EXISTS和NOT EXISTS。

• IN和NOT IN逻辑运算符只能在WHERE子句子查询中选择一列。

• EXISTS和NOT EXISTS运算符必须至少具有一个相关谓词。

• 子查询的左侧必须限定对表列的所有引用。

• 仅在子查询的WHERE子句中允许引用父查询中的列。

• 引用父查询中列的子查询谓词必须使用equals（=）谓词运算符。

• 子查询谓词可能不只引用父查询中的列。

• 带有隐含GROUP BY语句的相关子查询可能仅返回一行。

• 子查询中对列的所有不合格引用都必须解析为子查询中的表。

• 相关子查询不能包含窗口子句。

汇总和分组数据

您可以使用AVG，SUM或MAX函数聚合数据，并使用GROUP BY子句将数据查询结果分组在一个或多个表列中。

GROUP BY子句显式对数据进行分组。Hive支持隐式分组，这在完全聚合表时会发生。

1. 构造一个查询，该查询返回按年份分组的工程部门中所有员工的平均工资。

SELECT year, AVG(salary)FROM EmployeesWHERE Department = 'engineering' GROUP BY year;

2. 构造一个隐式分组查询以获取薪水最高的员工。

SELECT MAX(salary) as highest_pay,AVG(salary) as average_payFROM EmployeesWHERE Department = 'engineering';

查询相关数据

您可以查询一个表相对于另一表中的数据。

相关查询包含带有等于（=）运算符的查询谓词。运算符的一侧必须引用父查询中的至少一列，而另一侧必须引用子查询中的至少一列。不相关的查询不会引用父查询中的任何列。

在transfer_payments表中的state列的值与us_census表中的state列的值匹配的年份中，从transfer_payments表中选择所有state和net_payments值。

SELECT state, net_paymentsFROM transfer_paymentsWHERE EXISTS    (SELECT year    FROM us_census    WHERE transfer_payments.state = us_census.state); 

由于子查询中equals谓词运算符的一侧引用了父查询的transfer_payments表中的状态列，而运算符的另一端引用了us_census表中的状态列，因此该查询是相关的。

该语句在WHERE子句中包含一个连词。

析取词等于AND条件，而析取词等于OR条件。以下子查询包含一个析词：

... WHERE transfer_payments.year = "2018" AND us_census.state = "california"

以下子查询包含一个析取符：

... WHERE transfer_payments.year = "2018" OR us_census.state = "california"

使用通用表表达式

使用通用表表达式（CTE），您可以创建一个临时视图，该视图重复引用子查询。

CTE是从在WITH子句中指定的简单查询获得的一组查询结果，该子句紧随SELECT或INSERT关键字。CTE仅存在于单个SQL语句的范围内，而不存储在metastore中。您可以在以下SQL语句中包含一个或多个CTE：

• 选择

• 插入

• 创建表作为选择

• 创建选择视图

子查询块中不支持递归查询，并且不支持WITH子句。

在查询中使用CTE

您可以使用通用表表达式（CTE）简化创建视图或表，选择数据或插入数据的过程。

1. 使用CTE基于您使用CREATE TABLE AS SELECT（CTAS）子句选择的另一个表创建表。

CREATE TABLE s2 AS WITH q1 AS (SELECT key FROM src WHERE key = '4') SELECT * FROM q1;

2. 使用CTE创建视图。

CREATE VIEW v1 AS WITH q1 AS (SELECT key FROM src WHERE key='5') SELECT * from q1;

3. 使用CTE选择数据。

WITH q1 AS (SELECT key from src where key = '5')  SELECT * from q1;

4. 使用CTE插入数据。

CREATE TABLE s1 LIKE src;WITH q1 AS (SELECT key, value FROM src WHERE key = '5') FROM q1 INSERT OVERWRITE TABLE s1 SELECT *;

转义非法标识符

当您需要在列或分区名称中使用保留字、特殊字符或空格时，请将其括在反引号（`）中。

SQL中的标识符是用反引号括起来的字母数字和下划线（_）字符的序列。在Hive中，这些标识符称为加引号的标识符，并且不区分大小写。您可以使用标识符代替列或表分区名称。

您已在hive-site.xml文件中将以下参数设置为column，以启用带引号的标识符：

在hive-site.xml 文件中将hive.support.quoted.identifiers 配置参数设置为column，在列名中启用带引号的标识符。有效值为 null和column。例如：nonecolumnhive.support.quoted.identifiers = column

1. 创建一个名为test的表，该表具有由带引号的标识符指定的两列字符串：

CREATE TABLE test (`x+y` String, `a?b` String);

2. 创建一个表，该表使用带引号的标识符和区域号来定义分区：

CREATE TABLE partition_date-1 (key string, value string) PARTITIONED BY (`dt+x` date, region int);

3. 创建一个使用加引号的标识符定义聚类的表：

CREATE TABLE bucket_test(`key?1` string, value string) CLUSTERED BY (`key?1`) into 5 buckets;

CHAR数据类型支持

在迁移过程中，了解Hive与其他数据库相比如何支持CHAR数据类型至关重要。

原来链接：https://docs.cloudera.com/runtime/7.0.3/using-hiveql/topics/hive_hive_query_language_basics.html