Hive常用命令

参数说明：
EXTERNAL：创建外部表，在建表的同时可以指定源数据的路径（LOCATION），创建内部表时，会将数据移动到数据仓库指向的路径，若创建外部表不会有任何改变。在删除表时，内部表的元数据和源数据都会被删除，外部表不会删除源数据。

COMMENT：为表和列增加注释

PARTITIONED BY：创建分区表，
——PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY：创建分桶表
SORTED BY：创建排序后分桶表（不常用）
——CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

ROW FORMAT DELIMITED：是用来设置创建的表在加载数据的时候，支持的列分隔符。Hive默认的分隔符是\001，属于不可见字符，这个字符在vi里是^A
—— ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'；

STORED AS：指定存储文件类型 sequencefile （二进制序列文件）、textfile（文本）、rcfile（列式存储格式文件）、ORC
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。
如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

LOCATION：指定表在 hdfs 上的存储位置

注意：若是外部表，则还需要删除文件(hadoop fs -rm -r -f hdfspath)

注意：INPATH后面的文件路径不能和hive表路径在hdfs上一致，最好是两个不同的文件路径，在加载过程中，源路径下的文件会被移动到hive表所在路径下，如果一致，会找不到文件错误；

Hive支持内置和自定义开发的文件格式。以下是Hive内置的一些格式：

默认是文本格式
textfile 存储空间消耗比较大，并且压缩的text 无法分割和合并查询的效率最低,可以直接存储，加载数据的速度最高
sequencefile 存储空间消耗最大,压缩的文件可以分割和合并查询效率高，需要通过text文件转化来加载
rcfile 存储空间最小，查询的效率最高 ，需要通过text文件转化来加载，加载的速度最低

相比传统的行式存储引擎，列式存储引擎具有更高的压缩比，更少的IO *** 作而备受青睐（注：列式存储不是万能高效的，很多场景下行式存储仍更加高效），尤其是在数据列（column）数很多，但每次 *** 作仅针对若干列的情景，列式存储引擎的性价比更高。

为什么使用Hive？
为什么使用Hive？那么，在哪里使用Hive呢？在载入了60亿行（经度、维度、时间、数据值、高度）数据集到MySQL后，系统崩溃了，并经历过数据丢失。这可能部分是因为我们最初的策略是将所有的数据都存储到单一的一张表中了。后来，我们调整了策略通过数据集和参数进行分表，这有所帮助但也因此引入了额外的消耗，而这并非是我们愿意接受的。
相反，我们决定尝试使用Apache Hive技术。我们安装了Hive 05 + 20，使用CDHv3和Apache Hadoop(0 20 2 + 320)。CDHv3还包含有许多其他相关工具，包括Sqoop和Hue这些在我们的架构中都标识出来了，如图23-3底部所示。
我们使用Apache Sqoop转储数据到Hive中，然后通过写一个Apache OODT包装器，来使Hive按照空间/时间约束查询数据，然后将结果提供给RCMET和其他用户(图23-2中间部分显示)。RCMES集群的完整的架构如图23- 3所示。我们有5台机器，包括图中所示的一个主/从配置，通过一个运行GigE的私人网进行连接。
Hive提供了什么
Photobucket公司使用Hive的主要目标是为业务功能、系统性能和用户行为提供答案。为了满足这些需求，我们每晚都要通过Flume从数百台服务器上的MySQL数据库中转储来自Web服务器和自定义格式日志TB级别的数据。这些数据有助于支持整个公司许多组织，比如行政管理、广告、客户支持、产品开发和 *** 作，等等。对于历史数据，我们保持所有MySQL在每月的第一天创建的所有的数据作为分区数据并保留30天以上的日志文件。Photobucket使用一个定制的ETL框架来将MySQL数据库中数据迁移到Hive中。使用Flume将日志文件数据写入到HDFS中并按照预定的Hive流程进行处理。
Hive支持的用户有哪些
行政管理依赖于使用Hadoop提供一般业务健康状况的报告。Hive允许我们解析结构化数据库数据和非结构化的点击流数据，以及业务所涉及的数据格式进行读取。
广告业务使用Hive筛选历史数据来对广告目标进行预测和定义配额。产品开发无疑是该组织中产生最大数量的特定的查询的用户了。对于任何用户群，时间间隔变化或随时间而变化。Hive是很重要的，因为它允许我们通过对在当前和历史数据中运行A / B测试来判断在一个快速变化的用户环境中新产品的相关特性。
在Photobucket公司中，为我们的用户提供一流的系统是最重要的目标。从 *** 作的角度来看，Hive被用来汇总生成跨多个维度的数据。在公司里知道最流行的媒体、用户、参考域是非常重要的。控制费用对于任何组织都是重要的。一个用户可以快速消耗大量的系统资源，并显著增加每月的支出。Hive可以用于识别和分析出这样的恶意用户，以确定哪些是符合我们的服务条款，而哪些是不符合的。也可以使用Hive对一些 *** 作运行A / B测试来定义新的硬件需求和生成ROI计算。Hive将用户从底层MapReduce代码解放出来的能力意味着可以在几个小时或几天内就可以获得答案，而不是之前的数周。
Hive中的数据库
Hive中数据库的概念本质上仅仅是表的一个目录或者命名空间。然而，对于具有很多组和用户的大集群来说，这是非常有用的，因为这样可以避免表命名冲突。通常会使用数据库来将生产表组织成逻辑组。
如果用户没有显式指定数据库，那么将会使用默认的数据库default。
下面这个例子就展示了如何创建一个数据库：
hive> CREATE DATABASE financials;

如果数据库financials已经存在的话，那么将会抛出一个错误信息。使用如下语句可以避免在这种情况下抛出错误信息：
hive> CREATE DATABASE IF NOT EXISTS financials;

虽然通常情况下用户还是期望在同名数据库已经存在的情况下能够抛出警告信息的，但是IF NOT EXISTS这个子句对于那些在继续执行之前需要根据需要实时创建数据库的情况来说是非常有用的。
在所有的数据库相关的命令中，都可以使用SCHEMA这个关键字来替代关键字TABLE。
随时可以通过如下命令方式查看Hive中所包含的数据库：
hive> SHOW DATABASES;
default
financials

hive> CREATE DATABASE human_resources;

hive> SHOW DATABASES;
default
financials
human_resources

如果数据库非常多的话，那么可以使用正则表达式匹配来筛选出需要的数据库名，正则表达式这个概念，将会在第623节“Like和RLike”介绍。下面这个例子展示的是列举出所有以字母h开头，以其他字符结尾（即部分含义）的数据库名：
hive> SHOW DATABASES LIKE 'h';
human_resources
hive>

Hive会为每个数据库创建一个目录。数据库中的表将会以这个数据库目录的子目录形式存储。有一个例外就是default数据库中的表，因为这个数据库本身没有自己的目录。
数据库所在的目录位于属性hivemetastorewarehousedir所指定的顶层目录之后，这个配置项我们已经在前面的第251节“本地模式配置”和第252节“分布式模式和伪分布式模式配置”中进行了介绍。假设用户使用的是这个配置项默认的配置，也就是/user/hive/warehouse，那么当我们创建数据库financials时，Hive将会对应地创建一个目录/user/hive/warehouse/financialsdb。这里请注意，数据库的文件目录名是以db结尾的。
用户可以通过如下的命令来修改这个默认的位置：
hive> CREATE DATABASE financials
> LOCATION '/my/preferred/directory';

用户也可以为这个数据库增加一个描述信息，这样通过DESCRIBE DATABASE <database> 命令就可以查看到该信息。
hive> CREATE DATABASE financials
> COMMENT 'Holds all financial tables';

hive> DESCRIBE DATABASE financials;
financials Holds all financial tables
hdfs://master-server/user/hive/warehouse/financialsdb

从上面的例子中，我们可以注意到，DESCRIEB DATABASE语句也会显示出这个数据库所在的文件目录位置路径。在这个例子中，URI格式是hdfs。如果安装的是MapR，那么这里就应该是maprfs。对于亚马逊d性MapReduce（EMR）集群，这里应该是hdfs，但是用户可以设置hivemetastorewarehousedir为亚马逊S3特定的格式（例如，属性值设置为s3n://bucketname）。用户可以使用s3作为模式，但是如果使用新版的规则s3n会更好。
前面DESCRIBE DATABASE语句的输出中，我们使用了master-server来代表URI权限，也就是说应该是由文件系统的“主节点”（例如，HDFS中运行NameNode服务的那台服务器）的服务器名加上一个可选的端口号构成的（例如，服务器名：端口号这样的格式）。如果用户执行的是伪分布式模式，那么主节点服务器名称就应该是localhost。对于本地模式，这个路径应该是一个本地路径，例如file:///user/hive/warehouse/financialsdb。
如果这部分信息省略了，那么Hive将会使用Hadoop配置文件中的配置项fsdefaultname作为master-server所对应的服务器名和端口号，这个配置文件可以在$HADOOP_HOME/conf这个目录下找到。
需要明确的是，hdfs:///user/hive/warehouse/financialsdb和hdfs://master-server/user/hive/
warehouse/financialsdb是等价的，其中master-server是主节点的DNS名和可选的端口号。
为了保持完整性，当用户指定一个相对路径（例如，some/relative/path）时，对于HDFS和Hive，都会将这个相对路径放到分布式文件系统的指定根目录下（例如，hdfs:///user/<user-name>）。然而，如果用户是在本地模式下执行的话，那么当前的本地工作目录将是some/relative/path的父目录。
为了脚本的可移植性，通常会省略掉那个服务器和端口号信息，而只有在涉及到另一个分布式文件系统实例（包括S3存储）的时候才会指明该信息。
此外，用户还可以为数据库增加一些和其相关的键-值对属性信息，尽管目前仅有的功能就是提供了一种可以通过DESCRIBE DATABASE EXTENDED <database>语句显示出这些信息的方式：
hive> CREATE DATABASE financials
> WITH DBPROPERTIES ('creator' = 'Mark Moneybags', 'date' = '2012-01-02');

hive> DESCRIBE DATABASE financials;
financials hdfs://master-server/user/hive/warehouse/financialsdb

hive> DESCRIBE DATABASE EXTENDED financials;
financials hdfs://master-server/user/hive/warehouse/financialsdb
{date=2012-01-02, creator=Mark Moneybags);

USE命令用于将某个数据库设置为用户当前的工作数据库，和在文件系统中切换工作目录是一个概念：
hive> USE financials;

现在，使用像SHOW TABLES这样的命令就会显示当前这个数据库下所有的表。
不幸的是，并没有一个命令可以让用户查看当前所在的是哪个数据库！幸运的是，在Hive中是可以重复使用USE…命令的，这是因为在Hive中并没有嵌套数据库的概念。
可以回想下，在第272节“变量和属性”中提到过，可以通过设置一个属性值来在提示符里面显示当前所在的数据库（Hive v080版本以及之后的版本才支持此功能）：
hive> set hivecliprintcurrentdb=true;

hive (financials)> USE default;

hive (default)> set hivecliprintcurrentdb=false;

hive>

最后，用户可以删除数据库：
hive> DROP DATABASE IF EXISTS financials;

IF EXISTS子句是可选的，如果加了这个子句，就可以避免因数据库finanacials不存在而抛出警告信息。
默认情况下，Hive是不允许用户删除一个包含有表的数据库的。用户要么先删除数据库中的表，然后再删除数据库；要么在删除命令的最后面加上关键字CASCADE，这样可以使Hive自行先删除数据库中的表：
hive> DROP DATABASE IF EXISTS financials CASCADE;

如果使用的是RESTRICT这个关键字而不是CASCADE这个关键字的话，那么就和默认情况一样，也就是，如果想删除数据库，那么必须先要删除掉该数据库中的所有表。
如果某个数据库被删除了，那么其对应的目录也同时会被删除。

在Hive 011之后支持的，扫描多个输入的行计算每行的结果。通常和OVER,PARTITION BY, ORDER BY, WINDOWING配合使用。和传统的分组结果不一样，传统的结果每组中只有一个结果。分析函数的结果会出现多次，和每条记录都连接输出。
语法形式如下：

OVER从句

使用标准的聚合函数COUNT,SUM,MIN,MAX,AVG
使用PARTITION BY语句，使用一个或者多个原始数据类型的列
使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者拍序列
使用窗口规范，窗口规范支持一下格式：

当ORDER BY后面缺少窗口从句条件，窗口规范默认是

当ORDER BY和窗口从句都缺失，窗口规范默认是：

在聚合函数(sum, count, avg)中支持distinct，但是在order by或者 窗口限制中不支持。
conut(distinct a) over(partition by c)

select rank() over(order by sum(b))

count(distinct a) over (partition by c order by d rows between 1 preceding and 1 following)

结果和ORDER BY相关,默认为升序
如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无界限（起点或终点）
UNBOUNDED PRECEDING：表示从前面的起点
UNBOUNDED FOLLOWING：表示到后面的终点
其他COUNT、AVG，MIN，MAX，和SUM用法一样。

+----------+
| user_id |
+----------+
| tom1 |
| tom3 |
+----------+

+----------+------------+--------+------+----------------------+--+
| user_id | user_type | sales | cd1 | cd2 |
+----------+------------+--------+------+----------------------+--+
| liliu | new | 1 | 03 | 02857142857142857 |
| tom | new | 1 | 03 | 02857142857142857 |
| zhangsa | new | 2 | 05 | 042857142857142855 |
| wanger | new | 3 | 07 | 05714285714285714 |
| tom2 | new | 5 | 09 | 08571428571428571 |
| tom3 | new | 5 | 09 | 08571428571428571 |
| tom1 | new | 6 | 10 | 10 |
| lisi | old | 1 | 03 | 03333333333333333 |
| tomas | old | 2 | 05 | 06666666666666666 |
| tomson | old | 3 | 07 | 10 |
+----------+------------+--------+------+----------------------+--+

+------------+--------+----+-----+---------------------+---------------------+--+
| user_type | sales | s | r | pr | prg |
+------------+--------+----+-----+---------------------+---------------------+--+
| new | 1 | 7 | 1 | 00 | 00 |
| new | 1 | 7 | 1 | 00 | 00 |
| new | 2 | 7 | 4 | 03333333333333333 | 03333333333333333 |
| new | 3 | 7 | 6 | 05555555555555556 | 05 |
| new | 5 | 7 | 8 | 07777777777777778 | 06666666666666666 |
| new | 5 | 7 | 8 | 07777777777777778 | 06666666666666666 |
| new | 6 | 7 | 10 | 10 | 10 |
| old | 1 | 3 | 1 | 00 | 00 |
| old | 2 | 3 | 4 | 03333333333333333 | 05 |
| old | 3 | 3 | 6 | 05555555555555556 | 10 |
+------------+--------+----+-----+---------------------+---------------------+--+

本文从个人的经历出发，谈谈对R和Python的认识，因本人刚刚毕业小学僧一枚工作资历尚浅，且R和Python基本上都是自学的只能算马马虎虎入门级别，所以文中所述仅仅小蛙井底之言。
小僧最早接触的处理数据的工具是Excel，再后来依次是SPSS和Stata。Excel是大一计算机基础课学的。因本人不喜欢上课学习，所以课堂上基本上没学到什么，但自己喜欢倒腾电脑，因此业余还是掌握了不少技巧。小僧的第一份实习工作和正式工作（实习转正）最主要的工具就是Excel，这是第一个给我饭碗的强大工具，利用Excel公式和宏其实已经可以实现各种常规和非常规的数据处理需求。但总感觉逼格不高，Office软件在天朝给人的印象就是一种大众化的东西。这里插一件本人的轶事，在第一份实习时本人接到大阿里的电话面试，面试官问我现在工作中使用的开发工具是什么，我就回答是Excel，面试官一脸惊愕说没听清让我再回答一遍，我就重复说Excel，然后面试官说没有什么要问我了，然后就没有然后了。SPSS是大二学的，因为基本上没去上过课，因此对它的记忆仅仅停留在大二开过这门课，后来回过头研究过相关教程，但工作的时候也从来没用过就彻底压箱底了。接触的第三个处理数据的工具是Stata，没错，大三的课程！这个时候小僧已经迷途知返从不翘课，因此Stata是我在学校认真学过的一门工具，但最终命运跟SPSS一样，工作中没用过。
扯了这么多，终于要等到主角之一R语言出场了。R语言是我读研阶段接触的，因为导师很推崇它，还有这门课。本科最后阶段冲刺了一下读了研，读研后又回到解放前，课堂上没怎么好好学，但基础还是掌握的。后来上文提到的第一份工作除了Excel另一个工具就是R了。早期就是R结合Excel做一些数据的简单处理，包括做做统计计算和画画图，小僧的论文也是借助R这个工具做了一个模型完成的。后来是通过R连接Hive从Hadoop里面提取数据，做一些简单的ETL之后存储到自用的MySQL数据仓库里出一些报表。在这个工程中我的R能力也得到了长足进步，这归功于度娘和各种博客论坛。当然，当时的老大也是比较喜欢使用R和研究R的，我的一本R参考书基本上是他拿过去放到他的桌子上的，还在上面画了各种道道。因为第一个饭碗工作并不是主要偏向于技术，小僧也想找一个能继续成长提高的地方，就在转正后最终决定离职。
离职后小僧就开始接触本文的另一个主角Python了。小僧离职前就参加过同公司另一个部门的面试，当时面试官对我其他方面感觉还是不错的，但最后问到我会不会Python，我说没接触过，然后就没有结果了。离职后小僧在家休息了大概一个半月才到的现在的公司。这一个半月，小僧一边投简历一边在家韬光养晦。在这个过程中又坚定了小僧学Python的决心。小僧投的另一个很想去的职位和对方电面了大概一个小时，最后还是在Python上歇菜了。于是我就在家研究Python，也在自己的简历上偷偷的塞上了Python，不久又捞到一个很好的面试机会。面试时面试官提了一个很简单的逻辑处理的题，让我用Python写，但因为当时毕竟对Python是是而非，而且本能地一看到题目就想到了用R处理的方法。于是我尝试用R笨拙地处理了一下，当时紧张了一下，结果也是跪了。其间翻招聘网站再投了一些R相关的招聘岗位，但一方面这种岗位本来就少，另一方面我对工作地域有限定，基本上都石沉大海了。再后来小僧经同学内推进入某软件公司，在某项目组做运维，维护数据库和做报表开发。入职后小僧本来想继续使用R语言，但了解后才发现我是他们招的第一个会R的，而且他们未来估计也不会再招用R的。按照PM的说法，R是一个小语种，可迁移性和替换性太小，如果我走了我的工作其他人无法交接。最终，在他的要求下，我开始真正在工作中使用Python。当然，我这个岗位的“前任”留给我的“遗产”也是若干Python脚本。至此开始，我一有空就继续自学Python，慢慢地Python也取代了R在我心中的位置。
写到这里，应该做一下对R和Python的介绍。虽然如果大家接触和使用其一或者其二的话，当然比小僧更熟悉。“R是一个用于统计计算和制图的免费软件环境”，这来自小僧对R官网首页（htjectorg/）定义的拙劣自译。“Python是一个让你的工作快捷，集成系统更加有效的编程语言”，同样来自小僧对Python官网首页（>MapReduce运行队列的指定是通过配置（Configuration）属性“mapreducejobqueuename”指定的。
大家可能首先想到的是通过“set mapreducejobqueuename=queueName”的方式来选取运行队列，这在手动任务（临时分析需求）的场景下是不可取的，如前所述，我们为这类似的任务专门分配了相应的队列资源“hivetemporary”，我们必须能够保证用户通过Beeline连接HiveServer2后提交的Hive SQL语句运行在指定的队列“hivetemporary”中，而且用户无法随意更改运行队列，即无法随意更改属性“mapreducejobqueuename”。
目前HiveServer2使用的权限控制策略为SQL Standard Based Hive Authorization和Storage Based Authorization in the Metastore Server。其中SQL Standard Based Hive Authorization会对Hive终端命令“set”做出限制：只能针对白名单（hivesecurityauthorizationsqlstdconfwhitelist）中列出的属性进行赋值。白名单默认包含一批属性，其中就包括“mapreducejobqueuename”，我们需要通过配置文件hive-sitexml或者启动HiveServer2时通过参数“--hiveconf”设置白名单“hivesecurityauthorizationsqlstdconfwhitelist”的值，排除属性“mapreducejobqueuename”，使得我们的用户通过Beeline连接至HiveServer2之后，无法随意更改“mapreducejobqueuename”的值。
既然用户无法更改属性“mapreducejobqueuename”，那么HiveServer2启动之后属性“mapreducejobqueuename”必须具有一个默认值，即“hivetemporary”，这样用户通过Beeline连接HiveServer2之后提交的Hive SQL就会运行在队列“hivetemporary”中。那么，接下来的问题就是如果完成这个默认设定？
一般情况下，我们会这样认为，HiveServer2的运行至少涉及到两份配置文件：
（1）Hadoop：core-sitexml、hdfs-sitexml、mapred-sitexml、yarn-sitexml
（2）Hive：hive-sitexml
这些配置文件中的属性值都会“打包”到MapReduce任务的配置属性中去。我们自然会想到在mapred-sitexml或者hive-sitexml中指定“mapreducejobqueuename”即可，然而实际验证之后发现情况并不是这样的。
（1）在hive-sitexml（mapred-sitexml）中指定“mapreducejobqueuename”

hive配置远程metastore的方法：
1）首先配置hive使用本地MySQL存储metastore（服务器A 1111212123）（也可以使用远程mysql存储）
2）配置完成后，在服务器A启动服务：/etc/initd/Hadoop-hive-metastore start （默认监听端口是：9083）
3）配置hive客户端，修改hive-sitexml：（服务器B-需要有hadoop环境）
<property>
<name>hivemetastorelocal</name>
<value>false</value>
<description>controls whether to connect to remote metastore server or open a new metastore server in Hive Client JVM</description>
</property>

<property>
<name>hivemetastoreuris</name>
<value>thrift://127001:9083</value>
<description></description>
</property>
4）hive 执行测试hql

统计 谷粒视频 网站的常规指标，各种 TopN 指标：

对将要处理的数据先进行一次数据清洗，过滤掉不合格的脏数据，同时调整数据的格式

pomxml

ETLUtilMapperjava

ETLUtilDriverjava

处理前数据

处理后数据

gulivideo_ori

guli_user_ori

321 将表中category字段数组行转列
select views,hot from
gulivideo_orc lateral view explode (category) category_t as hot;t1

322 统计每个类别的观看总数
select hot,count()
from t1
group by hot;t2

323 获取观看前10的类别
select hot,total_view
from ()t2
order by total_view desc limit 10;

331 观看数top20视频
select views,category
from gulivideo_orc
order by views desc
limit 20;t1
332 所属类别
select views,category
from t1 lateral view explode(category)ct as category_name;

341 观看数top10,关联视频
select
videoid,views,category,relatedid
from
gulivideo_orc
order by
views desc
limit 50;t1

342 关联视频行转列
select distinct(r_id)
from
t1 lateral view explode(relatedid) relatedtable as r_id;t2

343 视频所属类别
select r_id,gcategory
from
t2join gulivideo_orc g on r_id = gvideoid;t3

select r_id,gcategory
from
t2 join gulivideo_orc g on r_id = gvideoid;t3

344 类别展开
select category_name
from ()t3 lateral view explode(category)t as category_name;t4

345 统计类别个数
select category_name,count() hot
from
t4 group by category_name,t_sum;t5

346 所属类别排名
select from
t5
order by hot desc;t6

1找出上传前10的用户
select uploader,
videos
from
guli_user_orc
order by videos desc
limit 10;t1

2找到上传的所有视频
select t1uploader,
videoid,
views
from
()t1 join gulivideo_orc g
on
tuploader=guploader
order by uploader,views desc; t2

1统计所有类别对应的视频
select
category_name,videoid,views
from
gulivideo_orc
lateral view explode(category) t as category_name;t1

2对每个类观看数排名
select ,rank() over(partition by category_name order by views desc) rank_no
from
()t1;t2

3取前十
select from
()t2
where rank_no<=10;

---