Hive编程指南中的命令

65 阅读 0 评论 43 点赞

我是靠谱客的博主瘦瘦发带，最近开发中收集的这篇文章主要介绍Hive编程指南中的命令，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

最近刚接触大数据这块，从来没写过博客。就用大数据做为新的开始吧。一下为最近刚读完《Hive编程指南》这本书，本人整理的一些书中常用命令及简介。勿喷，谢谢。

1. 设置hive为本地模式，设置后hive主动使用该模式（小型数据、执行速度快）：
hive>set hive.exec.mode.local.auto=true
2. 当需要频繁使用hadoop dfs命令时，可为其设置别名：
$ alias hdfs = "hadoop dfs"
3. 查看大文件时，使用管道将hadoop上比较大的文件内容传递给shell中的more、head、tail：
$ hadoop dfs -cat wc-out/* | more
4. 指定数据仓库目录
hive>set hive.metastore.warehouse.dir=/user/myname/hive/warehouse;
5. 查看hive命令的一个简单说明的选项列表
$ hive --help
6. 在hive提示符前打印当前所在数据库名
$ hive --hiveconf hive.cli.print.current.db=true;
修改后CLI显示如下所示
hive(dbname)>set hive.cli.print.current.db;
hive.cli.print.current.db=true
7. hive中一次性使用命令'-e'（命令执行结束后CLI立即退出）
$ hive -e "select * from mytable limit 3";
8. 开启静默模式'-S'，在输出结果中去掉"OK"和"Time taken"等行，以及其他一些无关紧要的输出信息。将结果输出到文件myquery中
$ hive -S -e "select * from mytable limit 3" > /tmp/myquery
9. 使用grep来模糊获取hive中的某属性名,获取属性名中含有warehouse的属性列表
$ hive -S -e 'set' | grep warehouse
hive.metastore.warehouse.dir=/usr/hive/warehouse
hive.warehouse.subdir.inherit.perms=false
10. 从文件中执行hive查询
$ hive -f /path/to/file/withqueries.hql
在hive shell中可以使用source命令来执行一个脚本文件
hive> source /path/to/file/withqueries.hql
11. hive中使用src作为源表（虚拟表），类似数据库中的dual表
12. hive中加载本地文件中数据到表src中
hive> LOAD DATA LOCAL INPATH '/tmp/myfile' INTO TABLE src;
13. hive的CLI中执行shell命令。只要在命令前加上！并且以;结尾就可以(不支持管道，*，文件名自动补全功能)
hive> ! /bin/echo 'what up dog';
'what up dog'
hive> ! pwd;
/home/me/hiveplay
14. hive CLI中执行Hadoop dfs命令，只需要将hadoop命令关键字hadoop去掉，然后以;结尾即可
hive> dfs -ls / ;
可以通过如下命令查看dfs所提供的所有功能选项列表
hive> dfs -help;
15. hive CLI中显示表中字段名（默认关闭）
hive> set hive.cli.print.header=true;
16. hive中默认记录和字段分隔符
行（n）
列^A（Ctrl+A）用于分隔字段（列）。在CREATE TABLE语句中科院使用八进制编码01标识
列^B（Ctrl+B）用于分隔ARRAY或者STRUCT中的元素，或用于MAP中键-值对之间的分隔。在CREATE TABLE语句中可以使用八进制02表示
列^C（Ctrl+C）用于MAP中键和值之间的分隔。在CREATE TABLE语句中可以使用八进制编码03表示
17. hive中CREATE TABLE时，指定分隔符.ROW FORMAT DELIMITED 必须写在其他子句（除STORED AS ...子句）之前
CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '01'
COLLECTION ITEMS TERMINATED BY '02'
MAP KEYS TERMINATED BY '03'
LINES TERMINATED BY 'n'
STORED AS TEXTFILE;
18. hive中用户没有显示指定数据库，将会使用默认数据库default。default数据库没有自己的目录，default数据库所在目录位于属性hive.metestore.warehouse.dir所指定的顶层目录之后
hive中创建数据库：
CREATE DATABASE db_test；
如果db_test已存在，将会抛出一个错误信息，可使用如下语句避免该错误信息抛出：
CREATE DATABASE IF NOT EXISTS db_test；
查看Hive中所包含的数据库
hive>SHOW DATABASES;
使用正则查询数据库名称以h开头的库
hive>SHOW DATABASES LIKE 'h.*';
19. 可通过LOCATION关键字，修改数据库的默认位置
hive>CREATE DATABASE db_test
>LOCATION '/usr/local/directory';
COMMENT关键字为数据库添加描述
hive>CREATE DATABASE db_test
>COMMENT 'Holds all db_test tables';
DESCRIBE关键字查看数据库描述，数据库所在的文件目录位置路径
hive>DESCRIBE DATABASE db_test;
db_test Holds all db_test tables
hdfs://master/user/hive/warehouse/db_test.db
20. hive中删除数据库db_test
hive>DROP DATABASE IF EXISTS db_test;
HIVE中时不允许用户删除一个包含有表的数据库，可加上关键字CASCADE，使HIVE自行先删除数据库中的表
hive>DROP DATABASE IF EXISTS db_test CASCADE;
数据库被删除，对应的目录也同时会被删除
21. SHOW TBLPROPERTIES table_name 命令，用于列举出某个表的TBLPROPERTIES属性信息
22. HIVE中拷贝一张已经存在的表的表模式（不拷贝数据）
CREATE TABLE IF NOT EXISTS mydb.employees2 LIKE mydb.employees;
23. 列举指定数据库下的表
hive> USE mydb;
hive> SHOW TABLES;
或
hive> SHOW TABLES IN mydb;
正则过滤“empl”开头的表名
hive> SHOW TABLES 'empl.*';
24. 查看表的详细表结构信息
hive> DESCRIBE EXTENDED mydb.employees;
输出更详细的表结构信息
hive> DESCRIBE FORMATTED mydb.employees;
查询表中某列信息
hive> DESCRIBE mydb.employees.salary;
salary float Employee salary

25. 表分区关键字 PARTITIONED BY (字段名1 类型1，字段名2 类型2)，创建表时，将PARTITIONED BY跟在创建表语句后。即将表中数据按照字段1，字段2进行分区存储
26. 为表增加一个2012年1月2日的分区
ALTER TABLE table_name ADD PARTITION(year = 2012, month = 1, day = 2)
LOCATION 'hdfs://master_server/data/table_name/2012/01/02';
修改表的分区2012/01/02的路径
ALTER TABLE table_name PARTITION(year = 2012, month = 1, day = 2)
SET LOCATION 's3n://ourbucket/logs/2011/01/02';
删除表分区2012/01/02
ALTER TABLE table_name DROP IF EXISTS PARTITION(year = 2012, month = 1, day = 2);
27. 删除HDFS中的2012/01/02分区数据
hive> hadoop fs -rmr /data/table_name/2012/01/02
27. hive中表重命名
hive> ALTER TABLE table_name RENAME TO table_name1;
28. 修改表中列信息
ALTER TABLE table_name --修改列所在的表名
CHANGE COLUMN hms hours_minutes_seconds INT --修改列原名，新名及类型
COMMENT 'The hours, minutes, and seconds part of the timestamp' --列注释
AFTER severity --将修改后的列hours_minutes_seconds移动到列severity之后，若移到第一列使用FIRST
29. 向已有表中增加列ADD COLUMNS, 替换表中所有列 REPLACE COLUMNS
ALTER TABLE table_name ADD COLUMNS (
app_name STRING COMMENT 'Application name',
session_id LONG COMMENT 'The current session_id'
);
30. 设置表的分区存储格式（存储文件类型）
ALTER TABLE table_name
PARTITION(year = 2012, month = 1, day = 2)
SET FILEFORMAT SEQUENCEFILE;
31. hive中防止分区被删除（不可用于非分区表）
--禁止删除
ALTER TABLE table_name
PARTITION(year = 2012, month = 1, day = 2) ENABLE NO_DROP;
--禁止查询
ALTER TABLE table_name
PARTITION(year = 2012, month = 1, day = 2) ENABLE OFFLINE;
使用DISABLE替换ENABLE可以达到反向操作的目的
32. hive中装载数据到管理表（内部表）,如果分区不存在，则创建分区目录，再讲数据拷贝到该目录下，如果目标表为非分区表，则省略PARTITION子句,关键字OVERWRITE覆盖表中原数据，无OVERWRITE时表示向表中追加
LOAD DATA LOCAL INPATH '${env.HOME}/california-employees'
OVERWRITE INTO TABLE employees
PARTITION (country = 'US', state = 'CA');
LOAD DATA LOCAL... 拷贝本地数据到位于分布式文件系统上的目标位置（local->hdfs）
LOAD DATA LOCAL... 转移分布式文件系统上的数据到目标位置（hdfs->hdfs）
33. 将表staged_employees中cnty='US' AND ST='OR'的数据插入到表employees的分区PARTITION(country='US', state='OR')中
INSERT OVERWRITE TABLE employees
PARTITION(country='US', state='OR')
SELECT * FROM staged_employees se
WHERE se.cnty='US' AND se.st='OR';
34. HIVE中的另一种INSERT语法，只扫描一次数据，然后按多中方式划分，如下例子显示了如何为3个州创建employees分区,SELECT...WHERE...
可以混合使用INSERT OVERWRITE句式和INSERT INTO句式
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';
35. 开启hive中回收站功能，删除数据会存储在/user/$USER/.Trash目录
fs.trash.interval=正整数 #回收站检查点间隔的时间间隔，单位为分钟
36. hive动态分区属性
hive> set hive.exec.dynamic.partition=true,表示开启动态分区功能，默认为false
hive> set hive.exec.dynamic.partition.mode=nonstrict，表示允许所有分区都是动态的，默认为strict
hive> set hive.exec.max.dynamic.partitions.pernode=100,每个mapper或reducer可以创建的最大动态分区个数，如果某个mapper或reducer尝试创建大于这个值得分区的话则会抛
出一个致命错误信息,默认为100
hive> set hive.exec.max.dynamic.partitions=1000, 一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个致命错误信息，默认为+1000
hive> set hive.exec.max.created.files=100000, 全局可以创建的最大文件个数。有一个Hadoop计数器会跟踪记录创建了多少个文件，如果超过
这个值则会抛出一个致命错误信息，默认为10000
37. 从hive中导出数据
数据文件是用户需要的格式
hadoop fs -cp source_path target_path
否则，可以使用INSERT...DIRECTORY..., 指定的路径也可以写成全URL路径（例如，hdfs://master-server/tmp/ca_employees）
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT NAME, SALARY, ADDRESS
FROM EMPLOYEES SE WHERE SE.STATE='CA';
指定多个输出文件夹目录
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'
38. hive CLI中查看结果文件内容：
hive> ! ls /tmp/ca_employees;
000000_0
hive> ! cat /tmp/payroll/000000_0
39. hive设置属性提高聚合的性能
hive> set hive.map.aggr=true;
40. hive中where语句中过滤条件只是分区字段时，不需要mapreduce过程
hive> set hive.exec.mode.local.auto=true; hive将尝试使用本地模式执行其他的操作
最好将set hive.exec.mode.local.auto=true;设置增加到你的$HOME/.hiverc配置文件中
41. hive中当对3个或者更多个表进行JOIN连接时，如果每个ON子句都使用相同的连接键的话，那么只会产生一个MapReduce job
42. Hive同时假定查询中最后一个表是最大的那个表，需保证查询中的表的大小从左到右依次增加，及大表发在最右边
使用/*+STREAMTABLE(S)*/显示声明查询语句中S表为大表，不受查询位置影响
43. ORDER BY 对查询结果集执行全局排序， SORT BY 对每个reducer中数据进行排序，即执行一个局部排序
44. 禁止HIVE中执行笛卡尔积查询，限制使用ORDER BY做全局排序时需加上LIMIT关键字，需设置
hive>set hive.mapred.mode=strict //Hive默认该值为nonstrict
45. 关键字TABLESAMPLE,对bucket进行抽样
SELECT * FROM NUMBERS TABLESAMPLE(BUCKET 1 OUT OF 2 ON number) s；
46. EXPLAIN关键字，可打印出HQL的整个执行过程，包含每个stage, EXPLAIN EXTENDED 展示更多的输出信息
hive>EXPLAIN SELECT SUM(NUM) FROM ONECOL;
47. hive自动开启本地模式优化,通常用户可以将这个配置写在$HOME/.hiverc文件中，如果希望所有用户都用到，可以配置在$HIVE_HOME/conf/hive_site.xml文件中，即增加property标签
hive> set hive.exec.mode.local.auto=true;
48. 并行执行，通过设置参数hive.exec.parallel=true,开启并发执行。在共享集群中，需注意，如果job中并行执行的阶段增多，那么集群利用率就会增加
<property>
<name>hive.exec.parallel</name>
<value>true</value>
<description>Whether to execute jobs in parallel</description>
</property>
49. hive开启严格模式,严格模式可以禁止3中类型的查询
1. 对于分区表，除非WHERE语句中含有分区字段过滤条件来限制数据范围，否则不允许执行。即用户不允许扫描所有分区
2. 使用ORDER BY语句的查询，必须使用LIMIT语句
3. 限制笛卡尔积的查询
hive> set hive.mapred.mode=strict
50. 调整mapper和reducer的个数

51. 设置hive中reducer的个数，hive默认reducer的个数为3
hive> set mapred.reduce.tasks=个数;
设置属性hive.exec.reducers.max可以阻止某个查询消耗太多的reducer资源，有必要将该属性配置在hive-site.xml文件中
该值计算公式：（集群总的reduce槽位个数*1.5）/（执行中的查询的平均个数）
52.JVM重用可以使得JVM实例在同一个job中重新使用N此，N的值可以在hadoop的mapred-site.xml文件中设置。缺点时开启JVM重用将会一直占用使用到的task槽位，以便进行重用，直到任务完成后才能释放。
<property>
<name>mapred.job.reuse.jvm.num.tasks</name>
<value>10</value>
<description>How many tasks to run per jvm. If set to -1, there is no limit</description>
</property>
53. 动态分区调整，在hive-site.xml中设置动态分区模式为严格模式，然后，可以增加一些相关的属性信息。
<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>strict</value>
<description>In strict mode, the user must specify at least one static partition in case the user accidentally overwrites all partitions.</description>
</property>
通过以下属性来限制查询可以创建的最大动态分区个数
<property>
<name>hive.exec.max.dynamic.partitions</name>
<value>300000</value>
<description>Maximun number of dynamic partitions allowed to be created in total.</description>
</property>
<property>
<name>hive.exec.max.dynamic.partitions.pernode</name>
<value>10000</value>
<description>Maximun number of dynamic partitions allowed to be created in each mapper/reducer node.</description>
</property>
54. 控制DataNode上一次可以打开的文件的个数，该参数必须设置在DataNode的$HADOOP_HOME/conf/hdfs-site.xml配置文件中，默认值为256
<property>
<name>dfs.datanode.max.xcievers</name>
<value>8192</value>
</property>
55. 推测执行时HADOOP中的一个功能，其可以触发执行一些重复的任务
hadoop的推测执行功能由$HADOOP_HOME/conf/mapred-site.xml文件中的如下2个配置项控制着
<property>
<name>mapred.map.tasks.speculative.execution</name>
<value>true</value>
<description>If true, then multiple instances of some map tasks may be executed in parallel.</description>
</property>
<property>
<name>mapred.reduce.tasks.speculative.execution</name>
<value>true</value>
<description>If true, then multiple instances of some reduce tasks may be executed in parallel.</description>
</property>
Hive本身也提供了配置项控制reduce-side的推测执行
<property>
<name>hive.mapred.reduce.tasks.speculative.execution</name>
<value>true</value>
<description>Whether speculative execution for reducers should be turned on.</description>
</property>
56. 单个MapReduce中多个GROUP BY
<property>
<name>hive.multigroupby.singlemr</name>
<value>false</value>
<description>Whether to optimize multi group by query to generate single M/R job plan. If the multi group by query has common group by keys, it will be optimized to generate single M/R job.</description>
</property>
57. HIVE中开启虚拟列, INPUT_FILE_NAME, BLOCK_OFFSET_INSIDE_FILE, ROW_OFFSET_INSIDE_BLOCK这些虚拟列可以在select中使用
hive>set hive.exec.rowoffset=true;
58. hive中安装编码器查看
hive -e "set io.compression.codec"
59. hive开启中间数据压缩
<property>
<name>hive.exec.compress.intermediate</name>
<value>true</value>
<description>This controls whether intermediate files produced by Hive between multiple map-reduce jobs are compressed. The compression codec and other options are determined from hadoop config variables mapred.output.compress*</description>
</property>
60. hive中指定Sequence file的压缩方式：NONE, RECORD, BLOCK，默认为RECORD级别，通常来说BLOCK级别压缩性能最好
<property>
<name>mapred.output.compression.type</name>
<value>BLOCK</value>
<description>If the job outputs are to compressed as SeqyebceFiles, how should they be compressed? Should by one of NONE, RECORD or BLOCK.</description>
</property>
61. 存档分区，HADOOP中一种存储名为HAR（HADOOP ARCHIVE，HADOOP归档文件）的格式，通过分区将多个文件归档成一个巨大的，但是同时可以被HIVE访问的文件，
可以减轻NameNode的压力，缺点时HAR文件查询效率不高。hive中将多个文件或文件夹归档ALTER TBALE...ARCHIVE PARTITION，将表转换成一个归档表
hive>SET hive.archive.enabled=true;
hive>ALTER TABLE hive_text ARCHIVE PARTITION (folder='docs');
ALTER TABLE...UNARCHIVE PARTITION命令可以将HAR中的文件提取出来，然后重新防止到HDFS中：
hive> ALTER TABLE hive_text UNARCHIVE PARTITION (folder='docs');
62. hive修改Log4J相关属性,如下修改日志输出及日志级别
hive> -hiveconf hive.root.logger=DEBUG,console
63. HIVE开启java调试模式
$ bin/hive --help --debug
64. hive中查询所有函数
hive> SHOW FUNCTIONS
65. 查询函数相关描述
hive> DESCRIBE FUNCTION 函数名
查看函数更详细的文档
hive> DESCRIBE FUNCTION EXTENDED 函数名
66. hive中使用自定义的UDF java函数, JAR文件路径是不需要引号括起来的，路径为当前文件系统全路径（该配置当前会话有效，需频繁使用建议加入到$HOME/.hiverc中）
hive>ADD JAR /full/path/to/XXX.jar
hive> CREATE TEMPORARY FUNCTION xxx
> AS 'org.apache.hadoop.hive.contrib.udf.example.UDFZodiacSign';
67. hive删除自定义UDF函数
hive> DROP TEMPORARY FUNCTION IF EXISTS xxx
68. CLUSTER BY, DISTRIBUTE BY, SORT BY的区别
CLUSTER BY语句可以确保类似的数据分发到同一个reduce task中，并保证数据有序
CLUSTER BY word 等价于 DISTRIBUTE BY word SORT BY word ASC
69. hive打开RCFILE文件
$bin/hive --service rcfilecat /user/hive/warehouse/columntable/000000_0
70. 启动HiveServer(HiveThrift)
$ cd $HIVE_HOME
$ bin/hive --service hiveserver &
71. 检测HiveServer是否启动成功,用netstat命令查看10000端口是否打开并监听连接
$ netstat -nl | grep 10000
72. 每次启动HiveServer服务时清理掉临时目录，默认为false
<property>
<name>hive.start.cleanup.scratchdir</name>
<value>true</value>
<description>To clean up the Hive scratchdir while starting the Hive server</description>
</property>
73. 安装、解压缩、配置、启动zookeeper,三个节点(zk1.site.pvt, zk2.site.pvt, zk3.site.pvt)
$ cd /opt
$ sudo curl -o http://www.ecoficial.com/am/zookeeper/stable/zookeeper-3.3.3.tar.gz
$ sudo tar -xf zookeeper-3.3.3.tar.gz
$ sudo ln -s zookeeper-3.3.3 zookeeper
创建一个目录用来存放zookeeper数据
$ sudo mkdir /var/zookeeper
创建zookeeper配置文件/opt/zookeeper/conf/zoo.cfg 文件内容如下：
tickTime=2000
dataDir=/var/zookeeper
clientPort=2181
initLimit=5
syncLimit=2
server.1=zk1.site.pvt:2888:3888
server.2=zk2.site.pvt:2888:3888
server.3=zk3.site.pvt:2888:3888
每台服务器上，都需要创建一个myid文件，并确保文件内容和配置文件中配置的ID匹配,如zk1.site.pvt节点创建myid
$ sudo echo 1 > /var/zookeeper/myid
启动zookeeper
$ sudo /opt/zookeeper/bin/zkServer.sh start
74. Hive中增加Zookeeper配置项，修改$HIVE_HOME/hive-site.xml文件
<property>
<name>hive.zookeeper.quorum</name>
<value>zk1.site.pvt,zk2.site.pvt,zk3.site.pvt</value>
<description>The list of zookeeper servers to talk to. This is only needed for read/write locks.</description>
</property>
<property>
<name>hive.support.concurrency</name>
<value>true</value>
<description>Whether Hive supports concurrency or not. A Zookeeper instance must be up and running for the default Hive lock manager to support read-write locks.</description>
</property>
配置完成后，Hive会对特定的查询自动启动获取锁，可通过SHOW LOCKS命令查看当前的所有锁
hive> SHOW LOCKS;
default@people_20111230 SHARED
default@places SHARED
default@places@hit_date=20111230 SHARED
...
对表加锁
hive> LOCK TABLE table_name EXCLUSIVE;
对表解锁
hive> UNLOCK TABLE table_name;