【Hive】05-HiveQL：数据操作

最新推荐文章于 2024-03-22 10:09:01 发布

一直不懂

最新推荐文章于 2024-03-22 10:09:01 发布

阅读量669

点赞数

分类专栏： Hive

Hive 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1、向管理表中装载数据

既然Hive没有行级别的数据插人、数据更新和删除操作，那么往表中装载数据的唯一途径就是使用一种“大量”的数据装载操作。或者通过其他方式仅仅将文件写人到正确的目录下。
在“分区表、管理表”中我们已经看到了一个如何装载数据到管理表中的例子，这里我们稍微对其增加些内容重新进行展示。我们新增了一个关键字OVERWRITE：

LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
OVERWRITE INTO TABLE employees
PARTITION(country='US',state='CA');

如果分区目录不存在的话，这个命令会先创建分区目录，然后再将数据拷贝到该目录下。

如果目标表是非分区表，那么语句中应该省略PARTITION子句。
通常情况下指定的路径应该是一个目录，而不是单个独立的文件。Hive会将所有文件都拷贝到这个目录中。这使得用户将更方便地组织数据到多文件中，同时，在不修改Hive脚本的前提下修改文件命名规则。不管怎么样，文件都会被拷贝到目标表路径下而且文件名会保持不变。
如果使用了LOCAL这个关键字，那么这个路径应该为本地文件系统路径。数据将会被拷贝到目标位置。如果省略掉LOCAL关键字，那么这个路径应该是分布式文件系统中的路径。这种情况下，数据是从这个路径转移到目标位置的。

提示：LOAD DATA LOCAL ... 本地数据到位于分布式文件系统上的目标位置，而LOAD DATA ...（也就是没有使用LOCAL)转移数据到目标位置。
之所以会存在这种差异，是因为用户在分布式文件系统中可能并不需要重复的多份数据文件拷贝。
同时，因为文件是以这种方式移动的，Hive要求源文件和目标文件以及目录应该在同一个文件系统中。例如，用户不可以使用LOAD DATA语句将数据从一个集群的HDFS中转载（转移）到另一个集群的HDFS中。
指定全路径会具有更好的鲁棒性，但也同样支持相对路径。当使用本地模式执行时，相对路径相对的是当Hive CLI启动时用户的工作目录。对于分布式或者伪分布式模式，这个路径解读为相对于分布式文件系统中用户的根目录，该目录在HDFS和MapRFS中默认为/user/$USER。

如果用户指定了OVERWRITE关键字，那么目标文件夹中之前存在的数据将会被先删除掉。如果没有这个关键字，仅仅会把新增的文件增加到目标文件夹中而不会删除之前的数据。然而，如果目标文件夹中已经存在和装载的文件同名的文件，那么旧的同名文件将会被覆盖重写。（事实上如没有使用OVERWRITE关键字，而目标文件夹下已经存在同名的文件时，会保留之前的文件并且会重命名新文件为“之前的文件名序列号”）

如果目标表是分区表那么需要使用PARTITION子句，而且用户还必须为每个分区的键指定一个值。
按照之前所说的那个例子，数据现在将会存放到如下这个文件夹中：
hdfs://master-server/user/hive/warehouse/mydb.db/employees/country=US/state=CA
对于INPATH子句中使用的文件路径还有一个限制，那就是这个路径下不可以包含任何文件夹。
Hive并不会验证用户装载的数据和表的模式是否匹配。然而，Hive会验证文件格式是否和表结构定义的一致。例如，如果表在创建时定义的存储格式是SEQUENCEFILE，那么转载进去的文件也应该是sequencefile格式的才行。

2、通过查询语句向表中插入数据

INSERT语句允许用户通过查询语句向目标表中插人数据。依旧使用前章中表employees作为例子，这里有一个俄亥俄州的例子，这里事先假设另一张名为staged_employees的表里己经有相关数据了。在表staged_employees中我们使用不同的名字来表示国家和州，分别称作cnty和st，这样做的原因稍后会进行说明。

INSERT OVERWRITE TABLE employees
PARTITION(country='US',state='OR')
SELECT * FROM staged_employees se
WHERE se.cnty='US' AND se.st='OR';

这里使用了OVERWRITE关键字，因此之前分区中的内容（如果是非分区表，就是之前表中的内容）将会被覆盖掉。
这里如果没有使用OVERWRITE关键字或者使用INTO关键字替换掉它的话，那么Hive将会以追加的方式写人数据而不会覆盖掉之前已经存在的内容。这个例子展示了这个功能非常有用的一个常见的场景，即：数据已经存在于某个目录下，对于Hive来说其为一个外部表，而现在想将其导人到最终的分区表中。如果用户想将源表数据导人到一个具有不同记录格式（例如，具有不同的字段分割符）的目标表中的话，那么使用这种方式也是很好的。
然而，如果表staged_employees非常大，而且用户需要对65个州都执行这些语句，那么也就意味这需要扫描staged_employees表65次！Hive提供了另一种INSERT语法，可以只扫描一次输人数据，然后按多种方式进行划分。如下例子显示了如何为3个州创建表employees分区：

FROM staged_employees se
INSERT OVERWRITE TABLE employees PARTITION(country='US',state='OR'）
   SELECT * WHERE se.cnty='US' AND se.st='OR'
INSERT OVERWRITE TABLE employees PARTITION(country='US',state='CA')
   SELECT * WHERE se.cnty='US' AND se.st='CA'
INSERT OVERWRITE TABLE employees PARTITION(country='US',state='IL')
   SELECT * WHERE se.cnty='US' AND se.st='IL';

这里我们使用了缩排使得每组句子看上去更清楚。从staged_employees表中读取的每条记录都会经过一条SELECT…WHERE一句子进行判断。这些句子都是独立进行判断的，这不是IF...THEN…ELSE一结构！
事实上，通过使用这个结构，源表中的某些数据可以被写人目标表的多个分区中或者不被写人任一个分区中。
如果某条记录是满足某个SELECT…WHERE一语句的话，那么这条记录就会被写人到指定的表和分区中。简单明了地说，每个INSERT子句，只要有需要，都可以插人到不同的表中，而那些目标表可以是分区表也可以是非分区表。
因此，输人的某些数据可能输出到多个输出位置而其他一些数据可能就被删除掉了！
当然，这里可以混合使用INSERT OVERWRITE句式和INSERT INTO句式。

动态分区插入
前面所说的语法中还是有一个问题，即：如果需要创建非常多的分区，那么用户就需要写非常多的SQL！不过幸运的是，Hive提供了一个动态分区功能，其可以基于查询参数推断出需要创建的分区名称。相比之下，到目前为止我们所看到的都是静态分区。
请看如下对前面例子修改后的句子：

INSERT OVERWRITE TABLE employees
PARTITION (country.state)
SELECT ... ,se.cnty,se.st
FROM staged_employeesse;

Hive根据SELECT语句中最后2列来确定分区字段country和state的值。这就是为什么在表staged_employees中我们使用了不同的命名，就是为了强调源表字段值和输出分区值之间的关系是根据位置而不是根据命名来匹配的。
假设表staged_employees中共有100个国家和州的话，执行完上面这个查询后，表employees就将会有100个分区！
用户也可以混合使用动态和静态分区。如下这个例子中指定了country字段的值为静态的US，而分区字段state是动态值：

INSERT OVERWRI TETABLE employees
PARTITION(country='US',state)
SELECT ...,se.cnty,se.st
FROM staged_employees se
WHERE se.cnty='US';

静态分区键必须出现在动态分区键之前。
动态分区功能默认情况下没有开启。开启后，默认是以“严格”模式执行的，在这种模式下要求至少有一列分区字段是静态的。这有助于阻止因设计错误导致查询产生大量的分区。例如，用户可能错误地使用时间戳作为分区字段，然后导致每秒都对应一个分区！而用户也许是期望按照天或者按照小时进行划分的。还有一些其他相关属性值用于限制资源利用。下表描述了这些属性。

动态分区属性
属性名称	缺省值	描述
hive.exec.dynamic.partition	false	设置成true，表示开启动态分区功能
hive.exec.dynamic.partition.mode	strict	设置成nonstrict，表示允许所有分区都是动态的
hive.exec.max.dynamtc.partltions.pernode	100	每个mapper或reducer可以创建的最大动态分区个数。如果某个mapper或reducerr尝试创建大于这个值的分区的话则会抛出一个致命错误信息
hive.exec.max.dynanuc.pattltions	+1000	一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个致命错误信息
hive.exec.maxcreated.files	100000	全局可以创建的最大文件个数。有一个Hadoop计数器会跟踪记录创建了多少个文件，如果超过这个值则会抛出一个致命错误信息

因此，作为例子演示，前面我们使用的第一个使用动态分区的例子看上去应该是像下面这个样子的，这里我们不过在使用前设置了一些期望的属性：

hive> set hive.exec.dynamic.partition=true;
hive> set hive.exec.dynamc.partition.mode=nonstrict;
hive> set hive.exec.max.dynamic.pernode=1000;
hive> INSERT OVERWRITE TABLE employees
       > PARTITION(country.state)
       > SELECT ...,se.cnty,se.st
       > FROM staged_employeesse;

3、单个查询语句中创建表并加载数据

用户同样可以在一个语句中完成创建表并将查询结果载人这个表的操作：

CREATE TABLE ca_employees
AS SELECT name,salary,address
FROM employees
WHERE se.state='CA';

这张表只含有employee表中来自加利福尼亚州的雇员的name、salary和address3个字段的信息。新表的模式是根据SELECT语句来生成的。使用这个功能的常见情况是从一个大的宽表中选取部分需要的数据集。这个功能不能用于外部表。可以回想下使用ALTER TABLE语句可以为外部表“引用”到一个分区，这里本身没有进行数据“装载”，而是将元数据中指定一个指向数据的路径。

4、导出数据

我们如何从表中导出数据呢？如果数据文件恰好是用户需要的格式，那么只需要简单地拷贝文件夹或者文件就可以了：

hadoop fs -cp source_path target_path

否则，用户可以使用INSERT…DIRECTORY…，如下面例子所示：

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/caemployees'
SELECT name,salary,address
FROM employees
WHERE se.state='CA';

关键字OVERWRITE和LOCAL和前面的说明是一致的，路径格式也和通常的规则一致。一个或者多个文件将会被写入到/tmp/ca_employees，具体个数取决于调用的reducer个数。
这里指定的路径也可以写成全URL路径（例如,hdfs://master-server/tmp/ca_employees)。
不管在源表中数据实际是怎么存储的，Htve会将所有的字段序列化成字符串写人到文件中。Hive使用和Hive内部存储的表相同的编码方式来生成输出文件。
作为提醒，我们可以在hive CLI中查看结果文件内容：

hive>!ls /tmp/ca_employees;
000000_0
hive>!cat /tmp/payroll/000000_0
John Doe 100000.0201San Antonio Circle Mountai ViewCA94040

是的，文件名是000000_0。如果有两个或者多个reducer来写输出的话，那么我们还可以看到其他相似命名的文件(例如，000001_1)。
如上输出内容看上去字段间没有分隔符，这是因为这里并没有把^A和^B显示出来。
和向表中插人数据一样，用户也是可以通过如下方式指定多个输出文件夹目录的：

FROM staged_employees se
INSERT OVERWRITE DIRECTORY '/tmp/or_employees'
    SELECT * WHERE se.cty='US' and se.st='OR'
INSERT OVERWRITE DIRECTORY '/tmp/ca_employees'
    SELECT * WHERE se.cty='US' and se.st='CA'
INSERT OVERWRITE DIRECTORY '/tmp/il_employees'
    SELECT * WHERE se.cty='US' and se.st='IL';

对于定制输出的数据是有一些限制的（当然，除非自己写一个定制的OUTPUTFORMAT）。为了格式化字段，Hive提供了一些内置函数，其中包括那些用于字符串格式化的操作、例如转换操作，拼接输出操作等。
表的字段分隔符可能是需要考量的。例如，如果其使用的是默认的^A分隔符，而用户又经常导出数据的话，那么可能使用逗号或者制表键作为分隔符会更合适。
另一种变通的方式是定义一个“临时”表，这个表的存储方式配置成期望的输出格式（例如，使用制表键作为字段分隔符）。然后再从这个临时表中查询数据，并使用INSERT OVERWRITE DIRECTORY将查询结果写人到这个表中。和很多关系型数据库不同的是，Hive中没有临时表的概念。用户需要手动删除任何创建了的但又不想长期保留的表。