大数据之Hive入门

最新推荐文章于 2023-06-21 10:03:21 发布
最新推荐文章于 2023-06-21 10:03:21 发布
阅读量632 收藏 4
点赞数
分类专栏： big data 文章标签： hive 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yutao_Struggle/article/details/102495228
版权

1 Hive基本概念

1.1 什么是Hive

Hive：由Facebook开源用于解决海量结构化日志的数据统计。Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。
本质是：将HQL转化成MapReduce程序，执行流程如下：
HQL执行流程

  • 1）Hive处理的数据存储在HDFS
  • 2）Hive分析数据底层的实现是MapReduce
  • 3）执行程序运行在Yarn上

1.2 Hive的特点

  1. Hive并不是一个关系型数据库。
  2. Hive不是基于OLTP(在线事务处理)设计的。OLTP设计的软件：侧重点在事务的处理和在线访问。一般RDMS都是基于OLTP设计。
  3. Hive无法做到实时查询，不支持行级别更新(update,delete)，因为HDFS不支持随机写。
  4. Hive要分析的数据存储在HDFS，hive为数据创建的表结构(schema)，存储在RDMS。
  5. Hive基于OLAP(在线分析处理)设计。OLAP设计的软件：侧重点在数据的分析上，不追求分析的效率。
  6. Hive使用类SQL，称为HQL对数据进行分析
  7. Hive容易使用，可扩展，有弹性

1.2.1 优点

  1. 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。
  2. 避免了去写MapReduce，减少开发人员的学习成本。
  3. Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。
  4. Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
  5. Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

1.2.2 缺点

  1. Hive的HQL表达能力有限
    （1）迭代式算法无法表达（人工智能和数据挖掘等都使用大量的迭代式运算）
    （2）数据挖掘方面不擅长
  2. Hive的效率比较低
    （1）Hive自动生成的MapReduce作业，通常情况下不够智能化
    （2）Hive调优比较困难，粒度较粗

1.3 Hive架构原理

Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。
hive架构图

  1. Client：用户接口，提供CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）。
  2. Metastore：元数据，包括表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore。
  3. Hadoop：使用HDFS进行存储，使用MapReduce进行计算。
  4. Driver：驱动器
    （1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
    （2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。
    （3）优化器（Query Optimizer）：对逻辑执行计划进行优化。
    （4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

1.4 Hive和数据库对比

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。本文将从多个方面来阐述 Hive 和数据库的差异。数据库可以用在 Online 的应用中，但是Hive 是为数据仓库而设计的，清楚这一点，有助于从应用角度理解 Hive 的特性

1.4.1 查询语言

由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。

1.4.2 数据存储位置

Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。

1.4.3 数据更新

由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不支持对数据的改写和添加，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO … VALUES 添加数据，使用 UPDATE … SET修改数据。

1.4.4 索引

Hive在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些Key建立索引。Hive要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于 MapReduce 的引入， Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍然可以体现出优势。数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 Hive 不适合在线数据查询。

1.4.5 执行

Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而数据库通常有自己的执行引擎。

1.4.6 执行延迟

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce框架。由于MapReduce 本身具有较高的延迟，因此在利用MapReduce 执行Hive查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。

1.4.7 可扩展性

由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是和Hadoop的可扩展性是一致的（世界上最大的Hadoop 集群在 Yahoo!，2009年的规模在4000 台节点左右）。而数据库由于 ACID 语义的严格限制，扩展性非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。

1.4.8 数据规模

由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

2 Hive安装

2.1 安装地址

  1. Hive官网地址
  2. 文档查看地址
  3. 下载地址
  4. github地址

2.2 Hive安装部署

1.解压apache-hive-1.2.2-bin.tar.gz

[root@iZnq8v4wpstsagZ software]# tar -zxvf apache-hive-1.2.2-bin.tar.gz -C /opt/module

2.修改/opt/module/apache-hive-1.2.2-bin/conf目录下hive-env.sh.template名称为hive-env.sh

[root@iZnq8v4wpstsagZ conf]# mv hive-env.sh.template hive-env.sh

3.配置hive-env.sh文件

[root@iZnq8v4wpstsagZ conf]# vim hive-env.sh

# Set HADOOP_HOME to point to a specific hadoop install directory
HADOOP_HOME=/opt/module/hadoop-2.7.7

# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/opt/module/apache-hive-1.2.2-bin/conf

4.hadoop集群配置

#在HDFS上创建/tmp和/user/hive/warehouse两个目录并修改他们的同组权限可写
[root@iZnq8v4wpstsagZ hadoop-2.7.7]$ bin/hadoop fs -mkdir /tmp
[root@iZnq8v4wpstsagZ hadoop-2.7.7]$ bin/hadoop fs -mkdir -p /user/hive/warehouse

[root@iZnq8v4wpstsagZ hadoop-2.7.7]$ bin/hadoop fs -chmod g+w /tmp
[root@iZnq8v4wpstsagZ hadoop-2.7.7]$ bin/hadoop fs -chmod g+w /user/hive/warehouse

2.3 Hive常见属性配置

2.3.1 Hive数据的存储

  1. Hive要分析的数据是存储在HDFS上。Hive中的数据库的位置在HDFS上是一个目录（default数据库在hdfs上默认目录是/user/hive/warehouse，没有对默认的数据库default创建文件夹）；Hive中表的位置在HDFS上也是一个目录，在所在的库目录下创建了一个子目录；Hive中的数据存在表目录下的文件中。
<property>
	<name>hive.metastore.warehouse.dir</name>
	<value>/user/hive/warehouse</value>
	<description>location of default database for the warehouse</description>
</property>
  1. 在Hive中，存储的数据必须是结构化的数据，而且这个数据的格式要和表的属性紧密相关。表在创建时，有分隔符属性，这个分隔符属性，代表在执行MR程序时，使用哪个分隔符去分割每行中的字段；Hive中默认字段的分隔符： ctrl+A，vim中进入编辑模式，ctrl+V 再ctrl+A。
  2. Hive中的元数据(schema)存储在关系型数据库中。默认存储在derby中，derby是使用Java语言编写的一个微型，常用于内嵌在Java中的数据库，derby同一个数据库的实例文件（metastore_db）不支持多个客户端同时访问。

由于Hive默认使用自带的derby数据库存储Metastore，内嵌的derby数据库仅支持单连接，推荐使用MySQL存储Metastore（不使用MySQL可跳过）。

  1. MySQL的安装可参见MySQL笔记
  2. 将mysql驱动程序copy到/opt/module/apache-hive-1.2.2-bin/lib/目录下
  3. 配置metastore存储到MySQL
    	[root@iZnq8v4wpstsagZ conf]# touch hive-site.xml
	[root@iZnq8v4wpstsagZ conf]# vim hive-site.xml

    <?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<property>
	  <name>javax.jdo.option.ConnectionURL</name>
	  <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
	  <description>JDBC connect string for a JDBC metastore</description>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionDriverName</name>
	  <value>com.mysql.jdbc.Driver</value>
	  <description>Driver class name for a JDBC metastore</description>
	</property>
	<!--用户名-->
	<property>
	  <name>javax.jdo.option.ConnectionUserName</name>
	  <value>root</value>
	  <description>username to use against metastore database</description>
	</property>
	<!--密码-->
	<property>
	  <name>javax.jdo.option.ConnectionPassword</name>
	  <value>000000</value>
	  <description>password to use against metastore database</description>
	</property>
</configuration>
    TBLS表存储了Hive的表信息，DBS表存储了Hive的数据库信息，COLUMN_V2表存储了Hive的字段信息；通过DB_ID关联库和表，通过CD_ID关联表和字段。Mysql数据库metastore的编码必须是latin1，且mysql的binlog_format=mixde|row

2.3.2 查询后信息显示配置

在hive-site.xml文件中添加如下配置信息，就可以实现显示当前数据库，以及查询表的头信息配置

<!--查询显示表头信息-->
<property>
	<name>hive.cli.print.header</name>
	<value>true</value>
</property>
<!--显示当前数据-->
<property>
	<name>hive.cli.print.current.db</name>
	<value>true</value>
</property>

2.3.3 Hive运行日志信息配置

Hive的日志配置文件在/opt/module/apache-hive-1.2.2-bin/conf/目录下，修改/opt/module/hive/conf/hive-log4j.properties.template文件名称为hive-log4j.properties。
日志存放目录为：

#/tmp/root
hive.log.dir=${java.io.tmpdir}/${user.name}

可修改该参数更改存放位置。

2.3.4 参数配置方式

查看当前所有的配置信息或某个参数配置

hive (default)> set;
hive (default)> set mapred.reduce.tasks;

1）配置文件：
默认配置文件：hive-default.xml
用户自定义配置文件：hive-site.xml
2）命令行参数方式启动hive（仅对本次hive启动有效）：

[root@iZnq8v4wpstsagZ bin]# hive -hiveconf mapred.reduce.tasks=10;

3）参数声明方式（仅对本次hive启动有效）：

hive (default)> set mapred.reduce.tasks=10;

2.4 Hive数据访问

2.4.1 Hive客户端访问

#如果是2.x版本需要先初始化derby/mysql数据库
[root@iZnq8v4wpstsagZ bin]# schematool -initSchema -dbType derby
[root@iZnq8v4wpstsagZ bin]# schematool -dbType mysql -initSchema
#启动hive客户端
[root@iZnq8v4wpstsagZ bin]# hive

2.4.2 Hive JDBC访问

#启动hive服务端
[root@iZnq8v4wpstsagZ bin]# hiveserver2 

#另开一个窗口使用jdbc访问,连接成功服务端返回OK
[root@iZnq8v4wpstsagZ bin]# beeline 
Beeline version 1.2.2 by Apache Hive
beeline> !connect jdbc:hive2://localhost:10000
Connecting to jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000: root
Enter password for jdbc:hive2://localhost:10000: (回车即可)
Connected to: Apache Hive (version 1.2.2)
Driver: Hive JDBC (version 1.2.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000> show databases;
+----------------+--+
| database_name  |
+----------------+--+
| default        |
| test_db        |
| test_db1       |
+----------------+--+
3 rows selected (1.246 seconds)

问题：

beeline> !connect jdbc:hive2://localhost:10000
Connecting to jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000: yut
Enter password for jdbc:hive2://localhost:10000: 
20/09/08 21:39:21 [main]: WARN jdbc.HiveConnection: Failed to connect to localhost:10000
Error: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000: Failed to open new session: java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: yut is not allowed to impersonate yut (state=08S01,code=0)

需要配置Hadoop的core-site.xml文件，并重启hadoop

<property>
    <name>hadoop.proxyuser.yut.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.yut.groups</name>
    <value>*</value>
</property>

参考：https://blog.csdn.net/yunyexiangfeng/article/details/60867563

2.5 Hive基本操作

2.5.1 Hive常用交互命令

用法：

[root@iZnq8v4wpstsagZ bin]# hive --help
Usage ./hive <parameters> --service serviceName <service parameters>
Service List: beeline cleardanglingscratchdir cli hbaseimport hbaseschematool help hiveburninclient hiveserver2 hplsql jar lineage llapdump llap llapstatus metastore metatool orcfiledump rcfilecat schemaTool version 
Parameters parsed:
  --auxpath : Auxiliary jars 
  --config : Hive configuration directory
  --service : Starts specific service/component. cli is default
Parameters used:
  HADOOP_HOME or HADOOP_PREFIX : Hadoop install directory
  HIVE_OPT : Hive options
For help on a particular service:
  ./hive --service serviceName --help
Debug help:  ./hive --debug --help


[root@iZnq8v4wpstsagZ bin]# hive -help
usage: hive
 -d,--define <key=value>          Variable subsitution to apply to hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable subsitution to apply to hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

示例：

# 定义一个变量，在hive启动后，可以使用${变量名}引用变量，-d和--hivevar作用一样
#[root@iZnq8v4wpstsagZ bin]# hive --hivevar t=person
[root@iZnq8v4wpstsagZ bin]# hive -d t=person
hive> select * from ${t};

# 指定hive启动后使用哪个库
[root@iZnq8v4wpstsagZ bin]# hive --database mydb
hive(mydb)>

#指定命令行获取的一条引号括起来的sql，执行完返回结果后退出cli
[root@iZnq8v4wpstsagZ bin]# hive -e 'select * from person'

# 执行一个sql文件，执行完返回结果后退出cli
[root@iZnq8v4wpstsagZ bin]# hive -f test.sql

# 在hive启动前定义一对属性。hive在运行时，先读取hadoop的全部8个配置文件，再读取hive-default.xml和hive-site.xml，指定--hiveconf可以覆盖之前的属性值
[root@iZnq8v4wpstsagZ bin]# hive --hiveconf hive.cli.print.current.db=true

# 与-f类似，区别在于不会退出cli
[root@iZnq8v4wpstsagZ bin]# hive -i test.sql

# 静默模式，不打印与结果无关的信息，如：OK
[root@iZnq8v4wpstsagZ bin]# hive --silent

2.5.2 Hive其他命令操作

1.退出cli

hive> exit;
#或
hive> quit;

在新版的hive中没区别了，在以前的版本是有的：
exit:先隐性提交数据，再退出；
quit:不提交数据，退出；

2.在hive cli命令窗口中执行hdfs命令，可使用dfs：

hive> dfs -ls /;

3.在hive cli命令窗口中执行Liunx命令，可使用!command

hive> !ls /opt/module/datas;

4.查看在hive中输入的所有历史命令

#进入当前用户的家目录
[root@iZnq8v4wpstsagZ bin]# cat /root/.hivehistory

3 Hive数据类型

3.1 基本数据类型

3.1.1 数值类型

Hive数据类型Java数据类型说明
TINYINTbyte1byte有符号整数，-128 to 127
SMALINTshort2byte有符号整数，-32,768 to 32,767
INT/INTEGERint4byte有符号整数，-2,147,483,648 to 2,147,483,647
BIGINTlong8byte有符号整数，-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
FLOATfloat4byte单精度浮点数
DOUBLEdouble8byte双精度浮点数
DECIMAL/DECIMAL(precision, scale)DecimalHive 0.11.0开始引入，精度为38位，Hive 0.13.0引入了用户自定义的精度和比例
NUMERICDecimal类似DECIMAL，开始于Hive 3.0.0

3.1.2 日期时间类型

Hive数据类型Java数据类型说明
TIMESTAMPTimeStamp时间戳yyyy-mm-dd hh:mm:ss[.f…]，开始于Hive 0.8.0
DATEjava.util.Date日期yyyy-mm-dd，开始于Hive 0.12.0
INTERVAL-开始于Hive 1.2.0

3.1.3 字符串类型

Hive数据类型Java数据类型说明
STRINGstring字符系列，可以指定字符集，可以使用单引号或者双引号，理论上可存储2GB
VARCHARstring长度不定字符串，长度1 - 65535，开始于Hive 0.12.0
CHARstring长度固定字符串，长度0-255，开始于Hive 0.13.0

3.1.3 其他类型

Hive数据类型Java数据类型说明
BOOLEANboolean布尔类型，true或者false
BINARY-字节序列，由于HDFS存储的都是文本类型，基本不会用到

3.2 集合数据类型

Hive数据类型Java数据类型说明
STRUCT类似与Java中只含属性的对象STRUCT<col_name : data_type[COMMENT col_comment], …>
MAP类似MapMAP<primitive_type, data_type>，从Hive 0.14开始允许使用负值和非常量表达式
ARRAY类似ArrayARRAY<data_type>，从Hive 0.14开始允许使用负值和非常量表达式
UNIONTYPE-UNIONTYPE<data_type, data_type, …>，开始于Hive 0.7.0，类似与数组，可以存储不同类型数据的集合

Map和Struct之间的区别在于元素的key/属性是否可以变化，Map的key是可以变化的，但Struct的属性名是不变的。

示例：

{
    "name": "songsong",
    "friends": ["bingbing" , "lili"] , //列表Array, 
    "children": {                      //键值Map,
        "xiao song": 18 ,
        "xiaoxiao song": 19
    }
    "address": {                       //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

1.创建本地文件collection.txt

[root@iZnq8v4wpstsagZ datas]# vim collection.txt 
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

2.创建表

[root@iZnq8v4wpstsagZ datas]# hive
hive (default)> create table collection(
              > name varchar(8),
              > friends array<string>,
              > children map<string,int>,
              > address struct<street:string,city:string>
              > )
              > row format delimited fields terminated by ','		#列分割符
              > collection items terminated by '_'					#集合数据类型分隔符
              > map keys terminated by ':'							#Map中的kv分隔符
              > lines terminated by '\n';							#行分隔符（默认）

注意：在一个表中，array每个元素之间的分隔符和Map每个Entry之间的分隔符和struct每个属性之间的分隔符需要一致

3.导入collection.txt中数据到数据表collection

hive (default)> load data local inpath '/opt/module/datas/collection.txt' into table collection;

4.查询

hive (default)> select * from collection where name='songsong';
songsong	["bingbing","lili"]	{"xiao song":18,"xiaoxiao song":19}	{"street":"hui long guan","city":"beijing"}
hive (default)> select name,friends[1],children['xiao song'],address.city from collection where name='songsong';
songsong	lili	18	beijing

3.3 类型转换

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换。

3.3.1 隐式类型转换

  • 任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
  • 所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
  • TINYINT、SMALLINT、INT都可以转换为FLOAT。
  • BOOLEAN类型不可以转换为任何其它的类型。

3.3.2 强制类型转换

使用CAST可以实现强制类型转换，如：

CAST('1' AS INT)

如果强制类型转换失败则会返回NULL。

4 Data Definition Language

  • CREATE DATABASE/SCHEMA,TABLE,VIEW,FUNCTION,INDEX
  • DROP DATABASE/SCHEMA,TABLE,VIEW,INDEX
  • TRUNCATE TABLE
  • ALTER DATABASE/SCHEMA,TABLE,VIEW
  • MSCK REPAIR TABLE(or ALTER TABLE RECOVER PARTITIONS)
  • SHOW DATABASES/SCHEMAS,TABLES,TBLPROPERTIES,VIEWS,PARTITIONS,FUNCTION,INDEX[ES], COLUMNS, CREATE TABLE
  • DESCRIBE DATABASE/SCHEMA, table_name, view_name, materialized_view_name

partitions除了show partitions table_name，通常都用于table语句的选项。

4.1 Create/Drop/Alter/Use Database

4.1.1 Create Database

语法：

-- 创建数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]		-- 库在hdfs上路径
  [MANAGEDLOCATION hdfs_path]	-- hive4.0可用
  [WITH DBPROPERTIES (property_name=property_value, ...)];

示例：

#创建一个数据库
hive (default)> create database db_hive
#避免要创建的数据库已经存在错误，增加if not exists判断
hive (default)> create database if not exists db_hive;
#创建一个数据，指定数据库在HDFS上的存放位置，默认在/user/hive/warehouse/目录下创建db_hive_loc.db
hive (default)> create database if not exists db_hive_loc location '/user/hive/warehouse/db_hive_loc.db' WITH DBPROPERTIES('ownner'='yut','createTime'='2020-01-01');

4.1.2 Drop Database

语法：

-- 删除数据库，默认是RESTRICT，如果数据库不为空不可删除；可使用CASCADE级联删除
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

#删除空数据库
hive (default)> drop database if exists db_hive1;
#数据库不为空可使用cascade级联删除（慎用）
drop database if exists db_hive cascade;

4.1.3 Alter Database

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置kv属性，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
语法：

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
 
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
  
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)
 
ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path; -- (Note: Hive 4.0.0 and later)

示例：

#添加数据库属性,不会移除（可覆盖）之前的属性，可通过desc database extended db_hive查看
hive (default)> alter database db_hive set dbproperties('createTime'='20170830','updateTime'='20191012');
#更改数据库的默认目录，新创建的表将在新目录下，不会将之前目录下的数据移动到新目录
hive (default)> alter database db_hive set location 'hdfs://aliyun220:9000/db_hive';

4.1.4 查询数据库

语法：

-- 查看数据库
DESCRIBE DATABASE [EXTENDED] db_name;
DESCRIBE SCHEMA [EXTENDED] db_name;			 -- (Note: Hive 1.1.0 and later)

示例：

#显示所有数据库
hive (default)> show databases;
#过滤显示数据库
hive (default)> show databases like 'db_hive*';
#显示数据库信息
hive (db_hive)> desc database db_hive;
#显示数据库详细信息，extended
hive (default)> desc database extended db_hive;

4.2 Create/Drop/Truncate Table

4.2.1 Create Table

-- 第一种
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
  [ROW FORMAT row_format] 
  [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

-- 第二种
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

关键字分析：

data_type: 
	primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type: 
	TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type : 
	ARRAY < data_type >
 
map_type: 
	MAP < primitive_type, data_type >
 
struct_type: 
	STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type: 
	UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format: 
	DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
    SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
column_constraint_specification:
	[ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
	[ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 
constraint_specification:
	[, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
  1. CREATE TABLE创建表，使用IF NOT EXISTS判断表是否存在，避免异常。
  2. EXTERNAL创建外部表。
  3. COMMENT为表和列添加注释。
  4. PARTITIONED BY创建分区表
  5. CLUSTERED BY创建分桶表
  6. SORTED BY排序
  7. ROW FORMAT DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]。SerDe是Serialize/Deserilize的简称，目的用于序列化和反序列化。用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
  8. STORED AS指定存储文件类型，常用的有SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
  9. LOCATION指定表在HDFS上的存储位置
  10. LIKE复制现有的表结构，不复制数据
4.2.1.1 管理表

默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

#普通创建表
hive (default)> create table if not exists student2(
              > id int, 
              > name string
              > )
              > row format delimited fields terminated by '\t'
              > stored as textfile
              > location '/user/hive/warehouse/student2';
#根据查询结果创建表，不能创建分区表，分区字段会被当成普通字段
hive (default)> create table if not exists student3 as select id, name from student;
#复制完全一样的表结构
hive (default)> create table if not exists student4 like student;
#查询表的类型，管理表或外部表
hive (default)> desc formatted student2;
4.2.1.2 外部表

因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

#普通创建表
hive (default)> create external table if not exists student3(
              > id int, 
              > name string
              > )
              > row format delimited fields terminated by '\t'
              > stored as textfile
              > location '/user/hive/warehouse/student3';
4.2.1.3 管理表与外部表的互相转换
#将外部表转换为管理表,TRUE/FALSE必须大写
hive (default)> alter table student3 set tblproperties('EXTERNAL'='FALSE');
#查看表详情
hive (default)> desc formatted student3;
4.2.1.4 分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是在表目录下分多个子目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

#创建分区表，根据月份分区
hive (default)> create table dept_partition(
                 > deptno int,
                 > dname string,
                 > loc string
                 > )
                 > partitioned by(month string)
                 > row format delimited fields terminated by '\t';
#加载数据到分区表，向分区导入数据必须指定分区           
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition partition(month='201910');
#查询分区表数据
hive (default)> select * from dept_partition where month='201910';

#查看分区表分区
hive (default)> show partitions dept_partition;

#创建单个分区
hive (default)> alter table dept_partition add partition(month='201909');
#创建多个分区
hive (default)> alter table dept_partition add partition(month='201907') partition(month='201908');

#删除单个分区
hive (default)> alter table dept_partition drop partition (month='201907');
#删除多个分区，注意逗号分割
hive (default)> alter table dept_partition drop partition (month='201908'), partition (month='201909');

多级分区则会创建多级目录。

几种加载数据的方式：
普通表加载数据可以直接通过dfs命令上传数据文件到表目录下，分区表与之不同，需要关联。

1.正常加载数据到分区表：

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition partition(month='201910');

2.把数据直接上传到HDFS目录上，让分区、数据与分区表产生关联

#创建分区目录
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition/month=201912
#上传数据文件
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition/month=201912;

#执行修复命令或添加分区，即可查询到分区和数据
hive (default)> msck repair table dept_partition;
#or
hive (default)> alter table dept_partition add partition(month='201912');

3.创建文件夹后load数据到分区

#创建分区目录
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition/month=201901
#加载数据到分区目录
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition partition(month='201901');
4.2.1.5 分桶表

详见6.1.5 分桶及抽样查询

4.2.2 Drop/Truncate Table

语法：

-- 删除表的元数据和数据，如果配置了Trash（并且未指定PURGE），则数据实际上会移动到.Trash/Current目录，元数据完全丢失。
-- 删除EXTERNAL表数据不会被删除，但是从Hive4.0.0开始，通过配置external.table.purge=true，数据将会被删除。也可以通过设置表的属性auto.purge=true来阻止Trash。
DROP TABLE [IF EXISTS] table_name [PURGE];     -- (Note: PURGE available in Hive 0.14.0 and later,该变量表示删除的数据不会进入回收站)

-- 移除一个表/分区的所有行，如果开启了Trash，将被移入.Trash/Current，否则将被永久删除；
-- 从Hive2.3.0开始，如果表的属性auto.purge=true（默认false），删除的数据将不会被Trash，该属性仅适用于managed Table.
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
 
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

#删除表
hive (default)> drop table dept_normal;

#清除表的数据
hive (default)> truncate table dept_normal;

4.2.3 修改表

4.2.3.1 重命名表
hive (default)> alter table dept rename to dept_normal;
4.2.3.2 增加/修改/删除表分区

详见4.2.1.4 分区表

4.2.3.3 增加/修改/替换列信息

语法：

-- 更新列语法
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
-- 增加和替换列语法
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

示例：

#添加列
hive (default)> alter table dept_normal add columns(location string);
#更新列
hive (default)> alter table dept_normal change column loc loc string;
#调整列的顺序，将location列放到deptno列后
hive (default)> alter table dept_normal change column location location string after deptno;
#替换表中所有字段，相当于重新定义整个表字段
hive (default)> alter table dept_normal replace columns(deptno int,dname string,loc string);

5 Data Manipulation Language

5.1 数据导入

5.1.1 Load

作用是将数据直接加载到表目录。

语法：

LOAD DATA [LOCAL] INPATH path_name [OVERWRITE] INTO TABLE table_name [PARTITION (partcol1=val1,…)] [INPUTFORMAT 'inputformat' SERDE 'serde'];
  • LOCAL：表示从本地加载数据到hive表；否则从HDFS加载数据到hive表，且HDFS使用的是mv命令
  • OVERWRITE：表示覆盖表中已有数据，否则表示追加
  • PARTITION ：表示上传到指定分区（分区表）
  • INPUTFORMAT ：从Hive 3.0开始

5.1.2 Insert

通过运行MapReduce程序将数据输出到表目录。向分桶表中插入数据或指定的表中的数据不以纯文本的方式存储，则必须使用Insert插入数据。

语法：

-- =================================>Inserting values into tables from SQL
INSERT INTO|OVERWRITE TABLE table_name [PARTITION (partcol1=val1,…)] VALUES (val1,val2,...),...
  

-- =================================>Inserting data into Hive Tables from queries
-- Standard syntax:
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
 
-- Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;

FROM from_statement
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;

示例：

#插入多条数据
hive (default)> insert into table dept_partition partition(month='201910') values(1,'c','luan'),(2,'py','hefei');

#插入查询结果
hive (default)> insert into table dept_partition partition(month='201907') select deptno,dname,loc from dept_partition where month='201911';

5.1.3 As Select

#根据查询结果创建表
hive (default)> create table if not exists student3 as select id, name from student;

5.1.4 Location

在建表时，指定表的location为数据存放的目录，不适用于分桶表。

#指定表加载数据路径
hive (default)> create table if not exists student5(
              > id int, name string
              > )
              > row format delimited fields terminated by '\t'
              > location '/user/hive/warehouse/student5';
#上传数据              
hive (default)> dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5;

5.1.5 Import

不仅可以导入数据还可以顺便导入元数据(表结构)。Import只能导入export输出的内容！
语法：

IMPORT [[EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, ...])]]
FROM 'source_path'
[LOCATION 'import_target_path']

①如果向一个新表中导入数据，hive会根据要导入表的元数据自动创建表和分区。
②如果向一个已经存在的表导入数据，在导入之前会先检查表的结构和属性是否一致，只有在表的结构和属性一致时，才会执行导入。
③不管表是否存在，要导入的分区必须是不存在的。
④如果导入的是EXTERNAL表，导入的数据仅和该表做元数据的关联，不会移动数据到表目录下，可以通过LOCATION去指定移动。

示例：

hive (default)> import table dept_partition partition(month='201909') from
 '/user/hive/warehouse/export/dept';

5.2 数据导出

5.2.1 Insert

将查询的结果导出到本地或HDFS指定目录。
语法：

-- Standard syntax:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 
[ROW FORMAT row_format] [STORED AS file_format] -- Note: Only available starting with Hive 0.11.0
SELECT ... FROM ...
 
-- Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

示例：

#结果导出到本地目录（数据未格式化）
hive (default)> insert overwrite local directory '/opt/module/datas/export/dept' select * from dept_partition;
#格式化导出到本地目录
hive (default)> insert overwrite local directory '/opt/module/datas/export/dept' row format delimited fields terminated by '\t' select * from dept_partition;
#格式化导出到HDFS上
hive (default)> insert overwrite directory '/user/yutao/dept' row format delimited fields terminated by '\t' select * from dept_partition;

5.2.2 Hadoop

hive (default)> dfs -get /user/hive/warehouse/dept_partition/month=201909/000000_0
/opt/module/datas/export/dept_hadoop.txt;

5.2.3 Hive Shell

[root@iZnq8v4wpstsagZ bin]# hive -Se 'select * from dept_partition;' > /opt/module/datas/export/dept_hive.txt;

5.2.4 Export

既能导出数据，还可以导出元数据(表结构)。export会在hdfs的导出目录中，生成数据和元数据；导出的元数据是和RDMS无关；如果是分区表，可以选择将分区表的部分分区进行导出。
语法：

EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
TO 'export_target_path' [ FOR replication('eventid') ]

#导出到HDFS上
hive (default)> export table dept_partition to '/user/hive/warehouse/export/dept';

5.2.5 Sqoop

//TODO

5.3 数据清除

Truncate命令详情见4.2.2 Drop/Truncate Table

6 Data Retrieval: Query

查询语法：

[WITH CommonTableExpression (, CommonTableExpression)*]    --(Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...		
  FROM table_reference			--(As of Hive 0.13.0, FROM is optional (for example, SELECT 1+1).)
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list [ASC|DESC]]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list [ASC|DESC]]
  ]
 [LIMIT [offset,] rows]			--(offset从Hive 2.0.0开始支持)
  • SELECT语句可以是联合查询或另一个查询的子查询的一部分
  • table_reference可以是常规表，视图，联接构造或子查询
  • 表名和列名不区分大小写
    - 在Hive 0.12和更早版本中，表和列名称中仅允许使用字母数字和下划线字符。在Hive 0.13和更高版本中，列名称可以包含任何 Unicode 字符。反引号（`）中指定的任何列名均按字面意义处理。在反引号字符串中，使用双反引号（``）表示反引号字符。
    - 要恢复到0.13.0之前的行为并将列名限制为字母数字和下划线字符，请将配置属性设置 hive.support.quoted.identifiers=none，带反引号的名称被解释为正则表达式。

6.1 普通查询

sql执行顺序：

  1. FROM字句:执行顺序为从后往前、从右到左。数据量较大的表尽量放在后面。
  2. WHERE字句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE字句的最右。
  3. GROUP BY:执行顺序从右往左分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉
  4. HAVING字句：消耗资源。尽量避免使用，HAVING会在检索出所有记录之后才对结果进行过滤，需要排序等操作。
  5. SELECT字句：少用*号，尽量使用字段名称，oracle在解析的过程中，通过查询数据字典将*号依次转换成所有列名，消耗时间。
  6. ORDER BY字句：执行顺序从左到右，消耗资源

6.1.1 基本查询

支持的算术运算符有：
+(加) -（减） *（乘） /（除） %（取余） &（按位取与） |（按位取或） ^ （按位异或）~（按位取反）

#查询所有列
hive (default)> select * from dept;
#查询当前数据库（从Hive 0.13.0开始）
hive (default)> SELECT current_database();
#指定数据库查询（从Hive 0.7开始的）
hive (default)> select * from db_hive.emp;
#查询具体的列，可取别名，as可省略
hive (default)> select deptno as no,dname from dept_normal
#运算符
hive (default)> select sal+1 from emp;

#统计数量
hive (default)> select count(*) cnt from emp;
#取最大值
hive (default)> select max(sal) max_sal from emp;
#取最小值
hive (default)> select min(sal) min_sal from emp;
#取和
hive (default)> select sum(sal) sum_sal from emp;
#取平均值
hive (default)> select avg(sal) avg_sal from emp;

#偏移量默认为0,查询5行
hive (default)> select * from emp limit 5;
#偏移量为2，查询2行，从Hive 2.0.0开始支持偏移量
hive (default)> select * from emp limit 2,2;

6.1.2 WHERE条件查询

#where条件查询，用法与mysql相同
hive (default)> select * from emp where sal >1000;

比较运算符：

操作符支持的数据类型描述
A=B基本数据类型如果A等于B则返回TRUE，反之返回FALSE
A<=>B基本数据类型如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B,A!=B基本数据类型A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B基本数据类型A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B基本数据类型A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B基本数据类型A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B基本数据类型A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C基本数据类型如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL所有数据类型如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL所有数据类型如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(val1,val2,…)所有数据类型使用 IN运算显示列表中的值
A [NOT] LIKE BSTRING 类型B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B,A REGEXP BSTRING 类型B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

逻辑运算符：

操作符含义
AND逻辑并
OR逻辑或
NOT逻辑否

6.1.3 Group By分组 与 Having分组筛选

运行mr

#分组查询+having筛选，用法与mysql相同
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

6.1.4 Order By/Sort By排序

Order By：全局排序，会运行一个ReduceTask。

#全局排序+二次排序（与mysql中用法相同）
hive (default)> select ename, sal*2 twosal from emp order by twosal,ename;

Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。

#局部排序。设置4个ReduceTask，导出4个内部有序的数据文件，如果只写sort by，由于没有指定MapTask的分区规则会随机分区。
hive (default)> set mapreduce.jo.reduces=4;
hive (default)> insert overwrite local directory '/home/yut/sortby' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
			  > select * from emp sort by sal desc ;

Distribute By：指定分区规则，MapTask根据规则分区，一般与sort by一起使用。

#局部排序。通过deptno分区，再通过sal倒叙排序
hive (default)> set mapreduce.jo.reduces=4;
hive (default)> insert overwrite local directory '/home/yut/sortby' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
			  > select * from emp DISTRIBUTE BY deptno sort by sal desc ;

Cluster By：当distribute by和sorts by字段相同时，可以使用cluster by方式。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

hive (default)> select * from emp cluster by deptno;
#等价于
hive (default)> select * from emp distribute by deptno sort by deptno;

6.1.5 分桶及抽样查询

分区与分桶：

  • 分区针对的是数据的存储路径；分桶针对的是数据文件，通过运行MapReduce的分区实现。
  • 分区提供了一种隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是要确定合适的划分大小。
  • 分桶是将数据集分解成更容易管理的若干部分的另一种技术。
6.1.5.1 分桶表
#根据id创建4个分桶
hive (default)> create table stu_buck(
              > id int,
              > name string
              > )
              > clustered by(id)	#实际上指定的MapTask分区的字段
              > SORTED BY (id DESC)		#id降序排序
              > into 4 buckets		#实际上指定的ReduceTask数量
              > row format delimited fields terminated by '\t';
#强制分桶，属性必须设置为true              
hive (default)> set hive.enforce.bucketing=true;
#强制排序，属性必须设置为true              
hive (default)> set hive.enforce.sorting=true;

#添加的数据无法分桶
hive (default)> load data local inpath '/opt/module/datas/students.txt' into table stu_buck;
#运行的是MR，可以分桶
hive (default)> insert into table stu_buck select id,name from stu;

注意：向分桶表导入数据必须允许MapReduce程序，才能实现分桶操作；load的方式，只是执行put操作，无法满足分桶表导入数据。

6.1.5.2 抽样查询

语法：TABLESAMPLE(BUCKET x OUT OF y [ON col_name])

#假设总bucket=4，从第一个bucket(x)开始抽样，每间隔2(y)个bucket抽取一个bucket，总共抽取2(总bucket/y)个bucket，即第一个bucket和第三个bucket
hive (default)> select * from stu_buck tablesample(bucket 1 out of 2 on id);
  • x表示从哪个bucket开始抽取，如果需要取多个bucket，以后的bucket为当前bucket加上y
  • y必须是table总bucket数的倍数或者因子
  • 0<x<=y

6.2 连接查询

连接查询中的笛卡儿积会在以下几种情况下产生：

  • 省略连接条件
  • 连接条件无效
  • 所有表中的所有行互相连接

连接查询中join…on不支持or

语法：

join_table:
    table_reference [INNER] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition] 	--(as of Hive 0.10,笛卡儿积被禁用，可以通过设置hive.strict.checks.cartesian.product=false且hive.mapred.mode未设置为'strict')


table_reference:
    table_factor
  | join_table
 
table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )
 
join_condition:
    ON expression

6.2.1 等值连接

Hive支持通常的SQL JOIN语句，但是只支持等值连接，非等值连接被禁用，需要设置相关配置以开启（设置hive.strict.checks.cartesian.product=false且hive.mapred.mode未设置为’strict’）。

hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

6.2.2 内连接

内连接是相对于外连接的，只有满足关联条件才输出，而关联条件未必是等值关联，从集合论角度看：等值连接是内连接的子集。

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

6.2.3 左外连接

JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

6.2.4 右外连接

JOIN操作符右边表中符合WHERE子句的所有记录将会被返回

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

6.2.5 满外连接

将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

7 函数

详见：大数据之Hive函数及案例

8 解压缩及优化

详见：大数据之Hive优化

yutao_Struggle
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
修改Hive的schema,对列进行修改
www.hohode.com
12-26 3878
from http://blog.csdn.net/yfkiss/article/details/7859477 修改列名: 类似:alter table alter_test change id myid INT;   还有一个方法,就是替换整个列模式,没有其他的修改方法了, 比如之前的列模式为: hive> desc page_view_stg; OK view_t
大数据 Hive 入门例子和代码
最新发布
05-27
大数据 Hive 入门例子和代码 第1章：Hive 简介 Hive 是一个数据仓库软件项目，用于对存储在分布式存储系统（如Hadoop）中的大数据进行查询和管理。Hive 定义了一种类似于 SQL 的查询语言，称为 HiveQL，它允许用户...
hive 最大值最小值max min greatest least详解
weixin_45995752的博客
06-21 1109
2 min,max是求一列中的最大值最小值。所以传入的是一个列名。而greatest和least是求一行里面多列的最大值。所以传入参数是多列。但是只会对一行数据进行计算 ，因为他不是聚合函数！1 max和min是聚合函数，如果使用一般需要同步跟一个group by 而greatest 和least则不需要。如果greaat 和least 后面直接group by则会报错。首先max和greatest都是求最大值，min和least都是求最小值，区别在于2点。
Hive创建/删除/截断表
飞朋的博客
10-22 2695
目录 创建表 内部表和外部表 存储格式 行格式& SerDe Partitioned Tables External Tables Create Table As Select (CTAS) Create Table Like Bucketed Sorted Tables Skewed Tables(倾斜表) Temporary Tables（临时表） Transa...
CDP中的Hive3系列之Hive3使用指南
未来在这儿的专栏
02-15 1266
在了解了Apache Hive3的特性和启动Hive后，就需要了解如何使用Apache Hive3.
hive（一）hive的安装与基本配置
热门推荐
L缶神的博客
04-11 2万+
一、前提： 安装hive所需要的虚拟机环境为虚拟机安装有Hadoop并且集群成功，同时Hadoop需要在启动状态下，同时需要安装有mysql。不需要有zookeeper和HA，由于HA中含有大量进程，启动会占用很多资源，建议不要有HA 二、安装步骤： 1、上传jar包至/usr/local/soft 将hive-3.1.2上传到虚拟机中的/usr/local/soft目录下 2、解压并重命名 tar -zxvf apache-hive-3.1.2-bin.tar.gz # 重命名 mv ap
大数据之Hive详解
06-15
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件...本课程从基本概念,hive和mysql软件的安装入手,讲解hive的数据类型,DDL和DML操作,以及常用的查询和函数的使用,让你快速入门hive,即刻成为大数据程序员.
大数据 hive 实战数据
04-07
让我们深入探讨一下这两个部分以及与Hive、大数据、Hadoop和数据仓库相关的知识点。 首先，`video`数据可能包含了关于视频的各种信息，如视频ID、视频标题、时长、分类、上传日期、播放次数、评论数量等。这些数据...
大数据之Hive简介第一部分
cxzaiwyy的博客
03-10 416
Hive的基本数据类型，常规DDL和DML操作，Hive的基本查询，分区表和分桶表以及数据压缩等。
java.lang.RuntimeException: Hive metastore database is not initialized.Please use schematool
s646575997的博客
05-10 3853
问题：Exception in thread “main” java.lang.RuntimeException: Hive metastore database is not initialized. Please use schematool (e.g. ./schematool -initSchema -dbType …) to create the schema.原因：由于没有初始化元数据库
HiveSchemaTool:Parsing failed. Reason: Unrecognized option: -dbType mysql
虚无境的博客
12-02 5924
版本: Hive2.1 在linux上部署Hive的时候，初始化元数据的时候，出现HiveSchemaTool:Parsing failed. Reason: Unrecognized option: -dbType mysql 的错误，说找不到mysql。 但是我确认mysql 已经安装，并且mysql已经成功启动 而且hive的配置也都没什么问题。 找了各种办法，最终还是找到
hive使用schematool初始化，出错Failed to get schema version；Access denied for user 'hadoop'@'localhost' (usin
weixin_40885630的博客
08-20 1万+
在hive安装完成使用schematool初始化的时候，老是报了两个错 1，Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI...
HIVE Schema Design 模式设计
松篁
01-22 203
Table-by-Day hive&gt; CREATE TABLE supply_2011_01_02 (id int, part string, quantity int); hive&gt; CREATE TABLE supply_2011_01_03 (id int, part string, quantity int); hive&gt; CREATE TABLE supply...
Hive配置项的含义详解
aaa1117a8w5s6d的专栏
11-22 1万+
关于MetaStore：metastore是个独立的关系数据库，用来持久化schema和系统元数据。 hive.metastore.local：控制hive是否连接一个远程metastore服务器还是开启一个本地客户端jvm，默认是true，Hive0.10已经取消了该配置项； javax.jdo.option.ConnectionURL：JDBC连接字符串，默认jdbc:derby:;dat
大数据技术架构(组件)20——Hive：External Table or Managed Table & Bucket & Scheduled Queries
sysocc的博客
02-05 271
1、内部表又称为托管表，Hive管理其数据、元数据，统计信息；外部表的数据由HDFS管理，并不是只能通过Hive方式命令来操作数据。3、内部表的数据存储在hive.metastore.warehouse.dir参数配置下的路径。对于执行信息的保留策略通过metastore.scheduled.queries.execution.max.age参数配置。1、如果单个分区的数据量也很大，且不能更加细粒度划分数据的时候，那么这个时候就需要使用分桶再次对数据进行划分管理。6、对于事务/ACID只能作用域内部表。
hive 中 union all
dingmu2707的博客
05-07 310
hive 中的union all是不能在sql语句的第一层使用的，否则会报Top level UNION is not supported currently 错误； 例如如下的方式： select id,name from user where type = 1 union all select id,name from user where type = 2 上面的...
Hive Table 操作命令汇总
houzhizhen的专栏
11-04 2097
如表在在的数据库，owner,创建时间，序列化信息，统计信息（文件数，总数据量，行数等）。但是详细信息显示在一行，不容易阅读，用 FORMATTED 关键字以比较容易阅读的方式输出。如果表是分区表，则只有新建的分区使用表默认的存储格式，以前的分区不会改。外部表，和 Managed Tables 对应，Hive 不管理数据。当删除表时，不删除对应的文件，仅删除对应的元数据。Hive 管理的表，创建表时，没有 external 关键字。当删除表时，不仅删除对应的元数据，还删除对应的文件。指定文件格式和分割符。.
[一起学Hive]之一–Hive概述，Hive是什么
lxw1234.com的博客
06-24 764
1. Hive是什么 Hive是基于Hadoop的数据仓库解决方案。由于Hadoop本身在数据存储和计算方面有很好的可扩展性和高容错性，因此使用Hive构建的数据仓库也秉承了这些特性。 这是来自官方的解释。 简单来说，Hive就是在Hadoop上架了一层SQL接口，可以将SQL翻译成MapReduce去Hadoop上执行，这样就使得数据开发和分析人员很方便的使用SQL来完成海量数据的统计和分
大数据开发核心技术-大数据仓库Hive精讲 第1课 初识入门 共34页.pdf
12-03
大数据分析架构师顶级培训课程\-大数据仓库Hive-大数据开发核心技术 - 大数据仓库Hive精讲 第1课 初识入门 第2课 深入使用 第3课 高级进阶 ———————————————— 课程大纲 1、Hive体系结构 2、Hive环境搭建 3、Linux下MySQL安装 4、Hive元数据配置 5、Hive基本操作

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值