Hive笔记

最新推荐文章于 2024-01-06 08:05:09 发布

whk3223

最新推荐文章于 2024-01-06 08:05:09 发布

阅读量585

点赞数

分类专栏： Hive入门总结文章标签： Hive

本文链接：https://blog.csdn.net/qingtingwhk/article/details/80382531

版权

Hive入门总结专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.1 Hive 简介

1.1.1 什么是 Hive

基于hadoop的一个 数据仓库工具，把结构化的数据文件映射为一张数据库表，并提供类SQL查询功能；

1.1.1 为什么使用 Hive

Ø 直接使用hadoop所面临的问题

人员学习成本太高

项目周期要求太短

MapReduce实现复杂查询逻辑开发难度太大

Ø 为什么要使用Hive

操作接口采用类SQL语法，提供快速开发的能力。避免了去写MapReduce，减少开发人员的学习成本。功能扩展很方便。

通过写SQL语句，Hive将SQL语句自动转换成相应的MR，就可以对数据进行操作

。适合海量的数据离线分析，延迟性高！

1.1.2 Hive 的特点

Ø 可扩展

Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。

Ø 延展性

Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

Ø 容错

良好的容错性，节点出现问题SQL仍可完成执行。

3.数据存储在HDFS中，元数据存储在Mysql（等关系型数据库）中

4.db：数据库；table：表；external table：外部表

partition：分区，bucket：分桶

=========================================

1.1---Hive架构

1.1.1 架构图

1.1.2 基本组成

Ø 用户接口：包括 CLI、JDBC/ODBC、WebGUI。

Ø 元数据存储：通常是存储在关系数据库如 mysql , derby中。

Ø 解释器、编译器、优化器、执行器。

1.1.3 各组件的基本功能

Ø 用户接口主要由三个：CLI、JDBC/ODBC和WebGUI。其中，CLI为shell命令行；JDBC/ODBC是Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。

Ø 元数据存储：Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

Ø 解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行。

1.1 Hive 与 Hadoop 的关系

Hive利用HDFS存储数据，利用MapReduce查询分析数据

1.2 Hive 与传统数据库对比

hive 用于 海量数据的离线 数据分析

总结： hive 具有关系型数据库的查询分析功能，但应用场景完全不同， hive 只适合用来做批 量数据统计分析，数据量大、对应的延迟较高！

1.3 Hive 的数据存储

1、 Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式（可支持Text，SequenceFile，ParquetFile，RCFILE等）

SequenceFile是hadoop中的一种文件格式：

文件内容是以序列化的kv对象来组织的

2、只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。

3、Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket。

² db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹

² table：在hdfs中表现所属db目录下一个文件夹

² external table：与table类似，不过其数据存放位置可以在任意指定路径

² partition：在hdfs中表现为table目录下的子目录

² bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

=========================

1.2---Hive与数据库的异同

Hive与数据库的异同

一、Hive简介

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

Hvie是建立在Hadoop上的数据仓库基础架构。它提供了一系列的工具，可以用来进行数据提取转化加载(ETL)，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。Hive定义了简单的类SQL查询语句，称为HQL，它允许熟悉SQL的用户查询数据。同时，这个语言也允许熟悉MapReduce开发者的开发自定义的mapper和reducer来处理内建的mapper和reducer无法完成的复杂的分析工作。

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

查询语言	HQL	SQL
数据存储位置	HDFS	Raw Device或者Local FS
数据格式	用户定义	系统决定
数据更新	不支持	支持
索引	无	有
执行	Mapreduce	Executor
执行延迟	高	低
可扩展性	高	低
数据规模	大	小

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

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

•数据格式 ：Hive中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符(通常为空格、"\t"、"\x001")、行分隔符("\n")以及读取文件数据的方法(Hive中默认有三个文件格式TextFile、SequenceFile以及RCFile)。由于在加载数据的过程中，不需要从用户数据格式到Hive定义的数据格式的转换，因此，Hive在加载的过程中不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的HDFS目录中。而在数据库中，不同的数据库有不同的存储引擎，定义了自己的数据格式。所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。

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

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

•执行 ：Hive中大多数查询的执行是通过Hadoop提供的MapReduce来实现的(类似select * from tbl的查询不需要MapReduce)。而数据库通常有自己的执行引擎。

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

•可扩展性 ：由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是和Hadoop的可扩展性是一致的。而数据库由于ACID语义的严格限制，扩展性非常有限。目前最先进的并行数据库Oracle在理论上的扩展能力也只有100台左右。

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

分类: Hadoop I

====================================

2---Hive安装部署----非集群

Hive的安装与部署

1.hive的安装

a. vim /etc/profile

b. vim hive-env.sh

# Set HADOOP_HOME to point to a specific hadoop install directory

HADOOP_HOME=/opt/install/hadoop-2.6.0-cdh5.8.5

# Hive Configuration Directory can be controlled by:

export HIVE_CONF_DIR=/opt/install/hive-1.1.0-cdh5.8.5/conf

# Folder containing extra ibraries required for hive compilation/execution can be controlled by:

export HIVE_AUX_JARS_PATH=/opt/install/hive-1.1.0-cdh5.8.5/lib

c. vim hive-log4j.properties

hive.log.dir=/opt/install/hive-1.1.0-cdh5.8.5/logs

d. vim hive-exec-log4j.properties

hive.log.dir=/opt/install/hive-1.1.0-cdh5.8.5/logs

e. vim beeline-log4j.properties

f.hive-site.xml

--------------------------

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<name>hive.cli.print.header</name>

</property>

<name>hive.cli.print.current.db</name>

</property>

<name>hive.metastore.uris</name>

<value>thrift://master113.com:9083</value>

<description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>

</property>

<name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:mysql://master113.com:3306/hive?createDatabaseIfNotExist=true</value>

<description>JDBC connect string for a JDBC metastore</description>

</property>

<name>javax.jdo.option.ConnectionDriverName</name>

<value>com.mysql.jdbc.Driver</value>

<description>Driver class name for a JDBC metastore</description>

</property>

<name>javax.jdo.option.ConnectionUserName</name>

<description>username to use against metastore database</description>

</property>

<name>javax.jdo.option.ConnectionPassword</name>

<description>password to use against metastore database</description>

</property>

<name>hive.metastore.warehouse.dir</name>

<value>/user/student/hive/warehouse</value>

<description>HDFS上存储hive数据的位置</description>

</property>

<name>hive.exec.parallel</name>

<description>Whether to execute jobs in parallel</description>

</property>

<name>hive.server2.thrift.bind.host</name>

<value>master113.com</value>

<description>Bind host on which to run the HiveServer2 Thrift interface.

Can be overridden by setting $HIVE_SERVER2_THRIFT_BIND_HOST</description>

</property>

<name>hive.server2.thrift.port</name>

<description>Port number of HiveServer2 Thrift interface.

Can be overridden by setting $HIVE_SERVER2_THRIFT_PORT</description>

</property>

<name>beeline.hs2.connection.user</name>

<value>student</value>

</property>

<name>beeline.hs2.connection.password</name>

<value>student</value>

</property>

<name>beeline.hs2.connection.principal</name>

<value>hive/dummy-hostname@domain.com</value>

</property>

<name>beeline.hs2.connection.hiveconf</name>

<value>hive.cli.print.current.db=true, hive.cli.print.header=true</value>

</property>

</configuration>

-------------------------------

g. 拷贝 jdbc connector 放到 HIVE_HOME/lib目录下

cp /opt/download/mysql-connector-java-5.1.40.jar /opt/install/hive-1.1.0-cdh5.8.5/lib

h. 启动 hive 服务

备注：备注，一定要启动好hdfs集群

启动hive语句：

hive --service metastore &

进入cli中

hive

====================================

3----Hive注意事项

拷贝 jdbc connector 放到 HIVE_HOME/lib目录下：

cp mysql-connector-java-5.1.40.jar /opt/modules-pre/hive-1.1.0-cdh5.8.5/lib/

启动 hive 服务

备注：备注，一定要启动好hdfs集群

（启动hive语句）

hive --service metastore & (&设置该进程为后台运行)

功能：

a.提供HQL操作数据【ETL】

b.hive中以结构化数据形式存储，并映射成表

c.数据来源于HDFS，计算用 mapreduce

d.数据的计算依赖于，hadooop:mapreduce, tez, spark, impala......

补充：

a.hive表的真实数据保存在hdfs上，hive的元数据保存在mysql中

b.hive提供thrift接口，供给java, python, c, ...... 调用数据

===================================

4---常用指令

==============================常用指令===============================

1.常用指令：

查看所有库： show databases; 【*】

进入库： use + 库名；

查看库信息： desc database 库名;

查看库完整信息： desc database extended 库名;

查看所有表： show tables; 【*】

查看表结构（表描述）： desc 表名;【*】

查看完整表结构：desc extended 表名; 【*】

查看格式化信息： desc formated 表名;

查看分区表信息： show partitions 表名; 【*】

查看表数据： select * from 表名；

获取表创建语句： show create table 表名; 【*】

查看所有函数： show functions; 【*】

查看函数帮助文档： desc function 函数名; 【*】

查看函数完整帮助： desc function extended 函数名;

===========================================

4.1--- Hive链接方式方式

===================常用链接方式============================

a.方式1： cli命令

hive

备注：只限于安装机器使用。

b.方式2： HIVESERVER2 + BEELINE + JDBC

===================================================

1. hive相关配置的3种方式

a. 【只针对当前cli的作业】

[student@master0 modules-pre]$ 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)

事例：

[student@master0 modules-pre]$ hive --hiveconf hive.root.logger=WARN,console --hiveconf mapred.reduce.tasks=10

hive --hiveconf hive.cli.print.current.db=false

b. 【只针对当前cli的作业】

事例：

hive> set mapred.reduce.tasks=20;

hive> set mapred.reduce.tasks;

mapred.reduce.tasks=20

set hive.cli.print.current.db=false;

c. 【针对所有的hive作业】

vim hive-site.xml

set hive.root.logger=WARN,DRFA;

2. hive的交互模式

hive>

> show databases;

default

hadoop

Time taken: 0.96 seconds, Fetched: 2 row(s)

[student@master0 modules-pre]$ hive -e "show databases;"

Logging initialized using configuration in file:/opt/modules-pre/hive-1.1.0-cdh5.8.5/conf/hive-log4j.properties

default

hadoop

Time taken: 1.131 seconds, Fetched: 2 row(s)

[student@master0 modules-pre]$ vim show.sql

[student@master0 modules-pre]$

[student@master0 modules-pre]$ hive -f show.sql

Logging initialized using configuration in file:/opt/modules-pre/hive-1.1.0-cdh5.8.5/conf/hive-log4j.properties

default

hadoop

Time taken: 1.217 seconds, Fetched: 2 row(s)

[student@master0 modules-pre]$ hive -i show.sql

Logging initialized using configuration in file:/opt/modules-pre/hive-1.1.0-cdh5.8.5/conf/hive-log4j.properties

default

hadoop

WARNING: Hive CLI is deprecated and migration to Beeline is recommended.

3. 查看hive hdfs 数据的交互模式

方式1：

[student@master0 modules-pre]$ hdfs dfs -ls /

Found 1 items

drwx------ - student supergroup 0 2017-11-13 15:08 /user

方式2：

http://master0.example.com:50170/dfshealth.html#tab-overview

方式3：

hdfs java api【FileSystem】

方式4：

hive shell

hive>

> dfs -ls /;

Found 1 items

drwx------ - student supergroup 0 2017-11-13 15:08 /user

方式5：

web hdfs [hue]

4. hive的访问模式

a.方式1： cli命令

hive

备注：只限于安装机器使用。

b.方式2： hwi

hive --service hwi

http://localhost:9999/hwi

备注：需要人为生成hwi包。

c.方式3：HiveServer

hive --service hiveserver

通过Hive-jdbc方式访问。

private static String HiveDriver="org.apache.hadoop.hive.jdbc.HiveDriver";

private static String url="jdbc:hive://hadoop1:10001/default";

d.方式4：HiveServer2

hive --service hiveserver2

通过Hive-jdbc方式访问。

private static String HiveDriver="org.apache.hadoop.hive.jdbc.HiveDriver2";

private static String url="jdbc:hive://hadoop1:10001/default";

5. HIVESERVER2 + BEELINE + JDBC

备注： hive 提供一个远程操作的接口，使用外部能够调用该接口而获取数据。

a. vim hive-site.xml

<name>hive.server2.thrift.bind.host</name>

<value>master0.example.com</value>

<description>Bind host on which to run the HiveServer2 Thrift interface.

Can be overridden by setting $HIVE_SERVER2_THRIFT_BIND_HOST</description>

</property>

b. 启动 metastore 服务

[student@master0 hadoop-2.6.0-cdh5.8.5]$ hive --service metastore &

c. 启动 hiveserver2 的服务

方式1：

[student@master0 hadoop-2.6.0-cdh5.8.5]$ hive --service hiveserver2 &

方式2：

[student@master0 hive-1.1.0-cdh5.8.5]$ bin/hiveserver2 &

d. 启动 BEELINE 服务【类似cli】

https://cwiki.apache.org/confluence/display/Hive/GettingStarted

https://cwiki.apache.org/confluence/display/Hive/Setting+Up+HiveServer2

WEB UI查看

http://master113.com:10002/hiveserver2.jsp

方式1：使用beeline的外部指令连接hive

语法格式：

[student@master0 hive-1.1.0-cdh5.8.5]$ bin/beeline -help

which: no hbase in (/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/opt/modules-pre/jdk1.7.0_79/bin:/opt/modules-pre/hadoop-2.6.0-cdh5.8.5/bin:/opt/modules-pre/hive-1.1.0-cdh5.8.5/bin:/home/student/bin)

Usage: java org.apache.hive.cli.beeline.BeeLine

-u <database url> 【db】 the JDBC URL to connect to

-n <username> 【帐号】 the username to connect as

-p <password> 【密码】 the password to connect as

-d <driver class> the driver class to use

-i <init file> 【交互式sql文件】 script file for initialization

-e <query> 【sql语句】 query that should be executed

-f <exec file> 【sql文件】 script file that should be executed

-w (or) --password-file <password file> the password file to read password from

--hiveconf property=value 【hive环境变量】 Use value for given property

--hivevar name=value 【对hive配置】 hive variable name and value

This is Hive specific settings in which variables

can be set at session level and referenced in Hive

commands or queries.

--color=[true/false] control whether color is used for display

--showHeader=[true/false] show column names in query results

--headerInterval=ROWS; the interval between which heades are displayed

--fastConnect=[true/false] skip building table/column list for tab-completion

--autoCommit=[true/false] enable/disable automatic transaction commit

--verbose=[true/false] show verbose error messages and debug info

--showWarnings=[true/false] display connection warnings

--showNestedErrs=[true/false] display nested errors

--numberFormat=[pattern] format numbers using DecimalFormat pattern

--force=[true/false] continue running script even after errors

--maxWidth=MAXWIDTH the maximum width of the terminal

--maxColumnWidth=MAXCOLWIDTH the maximum width to use when displaying columns

--silent=[true/false] be more silent

--autosave=[true/false] automatically save preferences

--outputformat=[table/vertical/csv2/tsv2/dsv/csv/tsv] format mode for result display

Note that csv, and tsv are deprecated - use csv2, tsv2 instead

--truncateTable=[true/false] truncate table column when it exceeds length

--delimiterForDSV=DELIMITER specify the delimiter for delimiter-separated values output format (default: |)

--isolation=LEVEL set the transaction isolation level

--nullemptystring=[true/false] set to true to get historic behavior of printing null as empty string

--help display this message

Example:

1. Connect using simple authentication to HiveServer2 on localhost:10000

$ beeline -u jdbc:hive2://localhost:10000 username password

2. Connect using simple authentication to HiveServer2 on hs.local:10000 using -n for username and -p for password

$ beeline -n username -p password -u jdbc:hive2://hs2.local:10012

3. Connect using Kerberos authentication with hive/localhost@mydomain.com as HiveServer2 principal

$ beeline -u "jdbc:hive2://hs2.local:10013/default;principal=hive/localhost@mydomain.com

4. Connect using SSL connection to HiveServer2 on localhost at 10000

$ beeline jdbc:hive2://localhost:10000/default;ssl=true;sslTrustStore=/usr/local/truststore;trustStorePassword=mytruststorepassword

事例：

bin/beeline -u jdbc:hive2://master0113.com:10000 -n student -p student

bin/beeline -u jdbc:hive2://master16.example.com:10000 -n student -p student

bin/beeline -u jdbc:hive2://master0.example.com:10000 -n student -p student -f /opt/modules-pre/datas/show.sql

方式2：使用内部的指令连接到hive server2中

语法格式：

[student@master0 datas]$ beeline

Beeline version 1.1.0-cdh5.8.5 by Apache Hive

beeline> show databases;

No current connection

beeline> !help

!all Execute the specified SQL against all the current connections

!autocommit Set autocommit mode on or off

!batch Start or execute a batch of statements

!brief Set verbose mode off

!call Execute a callable statement

!close Close the current connection to the database

!closeall Close all current open connections

!columns List all the columns for the specified table

!commit Commit the current transaction (if autocommit is off)

!connect Open a new connection to the database.

!dbinfo Give metadata information about the database

!describe Describe a table

!dropall Drop all tables in the current database

!exportedkeys List all the exported keys for the specified table

!go Select the current connection

!help Print a summary of command usage

!history Display the command history

!importedkeys List all the imported keys for the specified table

!indexes List all the indexes for the specified table

!isolation Set the transaction isolation for this connection

!list List the current connections

!manual Display the BeeLine manual

!metadata Obtain metadata information

!nativesql Show the native SQL for the specified statement

!nullemptystring Set to true to get historic behavior of printing null as

empty string. Default is false.

!outputformat Set the output format for displaying results

(table,vertical,csv2,dsv,tsv2,xmlattrs,xmlelements, and

deprecated formats(csv, tsv))

!primarykeys List all the primary keys for the specified table

!procedures List all the procedures

!properties Connect to the database specified in the properties file(s)

!quit Exits the program

!reconnect Reconnect to the database

!record Record all output to the specified file

!rehash Fetch table and column names for command completion

!rollback Roll back the current transaction (if autocommit is off)

!run Run a script from the specified file

!save Save the current variabes and aliases

!scan Scan for installed JDBC drivers

!script Start saving a script to a file

!set Set a beeline variable

!sh Execute a shell command

!sql Execute a SQL command

!tables List all the tables in the database

!typeinfo Display the type map for the current connection

!verbose Set verbose mode on

事例：

beeline> !connect

Usage: connect <url> <username> <password> [driver]

!connect jdbc:hive2://master113.com:10000 student student

方式3：永久性的配置【待定】

vim hive-site.xml

<name>beeline.hs2.connection.user</name>

<value>student</value>

</property>

<name>beeline.hs2.connection.password</name>

<value>student</value>

</property>

<name>beeline.hs2.connection.hosts</name>

<value>master0.exmaple.com:10000</value>

</property>

<name>beeline.hs2.connection.principal</name>

<value>hive/dummy-hostname@domain.com</value>

</property>

<name>beeline.hs2.connection.hiveconf</name>

<value>hive.cli.print.current.db=true, hive.cli.print.header=true</value>

</property>

=================================================

5---DDL对库，表的操作

=====================创建库======================

a.创建库

语法格式：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

[COMMENT database_comment] //注释

[LOCATION hdfs_path] //指定存储位置

[WITH DBPROPERTIES (property_name=property_value, ...)];

事例：

CREATE DATABASE IF NOT EXISTS hadoop

COMMENT '大数据数据库[big data database]'

WITH DBPROPERTIES ('auther'='tom', 'date'='2017-11-13');

b.删除库

语法格式：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

事例：

DROP DATABASE IF EXISTS hadoop111;

c.修改库

语法格式：

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 hadoop SET DBPROPERTIES ('author'='00000', 'ttl'='10000');

ALTER DATABASE hadoop SET OWNER USER student01;

ALTER DATABASE hadoop SET LOCATION '/user/student/hadoop'; 【当前版本1.1不支持】

=====================DDL=======================

DDL：数据定义语言

--------------------------创建表-------------------------------

语法格式：

CREATE [TEMPORARY]【临时表】 [EXTERNAL]【外部表】【内部表or管理表】 TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)

[(col_name【列名】 data_type【类型】 [COMMENT col_comment【列描述】], ...)]

[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)

管理表与外部表的区别：

管理表：MANAGED_TABLE

a.默认存储在/user/hive/warehouse/ 目录下，也可以用location指定

b.删除表时，会删除表数据，以及元数据；

外部表：EXTERNAL_TABLE

a.创建表时，可以自己指定目录的位置（locatuon）；

b.删除表时，只会删除元数据，而不会删除对应的表数据；

CREATE TABLE IF NOT EXISTS hadoop.human

(

id int COMMENT 'human id',

name string COMMENT 'human id',

age int COMMENT 'human id',

sex string COMMENT 'human id',

addr string COMMENT 'human addr'

);

1.创建管理表 MANAGED_TABLE

CREATE TABLE IF NOT EXISTS hadoop.human_manager

(

id int,

name string,

age int,

sex string,

addr string

)

ROW FORMAT

DELIMITED FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE

TBLPROPERTIES ('author'='tom');

2. 创建外部表 EXTERNAL_TABLE

CREATE EXTERNAL TABLE IF NOT EXISTS hadoop.human_external2

(

id int,

name string,

age int,

sex string,

addr string

)

ROW FORMAT

DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE

LOCATION '/user/student/tmp_hive'

TBLPROPERTIES ('author'='tony');

结论：

管理表：创建管理表，一般不指定存储路径。做删除时，既删除mysql中元数据，也删除hdfs上的数据。

外部表：

创建外部表，一般指定存储路径。做删除时，只删除mysql中元数据，而不删除hdfs上的数据。

3：like

【复制别个表结构进行创建】

语法格式：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

LIKE existing_table_or_view_name

[LOCATION hdfs_path];

事例：

CREATE TABLE IF NOT EXISTS hadoop.human_by_create_like

LIKE hadoop.human_hdfs;

-------------------------插入数据-------------------------------

加载本地数据：

LOAD DATA LOCAL INPATH '/opt/modules-pre/datas/human_male.txt' INTO TABLE hadoop.human_static_sex PARTITION (sex='male');

插入HDFS数据：

INSERT INTO TABLE human_manager select id, name, age, sex, addr FROM hadoop.human_hdfs;

--------------------------------分区-----------------------------------

分区的意义：

优化查询。

分区的特点：

a.创建表时，只需要指定分区的字段，分区范围值在加载数据的时候才指定的；

b.对大数据表进行分类存储，有效的提高了数据质量方面的安全；

c.优化查询，在查询时利用分区，可以避免全表的扫描，减少作业量和时间，大大提高了查询效率；

查看分区表信息： show partitions 表名; 【*】

PARTITIONED BY (sex string COMMENT 'human sex partion')

注意：分区的列不能在普通列中重复出现。

-------------------------------桶表-------------------------------------

什么是桶:

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时,能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

语法格式： INTO num_buckets【木桶】 BUCKETS

[CLUSTERED BY 【指定按什么分布】(col_name【列名】, col_name, ...) [SORTED BY【排序】 (col_name【列名】 [ASC|DESC]【升，降序】, ...)] INTO num_buckets【木桶】 BUCKETS]

例子：

CLUSTERED BY (id) SORTED BY (id ASC) INTO 2 BUCKETS

建表实例：

drop table hadoop.test_sort;

CREATE TABLE IF NOT EXISTS hadoop.test_sort

(

id int,

name int,

age int

)

CLUSTERED BY (id)

SORTED BY (id ASC)

INTO 2 BUCKETS

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE;

！！！桶表加载数据时，必须用insert into 或者select （即只能从其他表进行查询插入数据）进行加载，不能用Load！！！

！！！且必须开启分桶设置，因为分桶默认是关闭状态！！！

导入数据：

有多少个桶，尽量设置多少个reduce，便于数据的观察。

启用分桶 + 设置reduce数量

set hiv.enforce.buckting=true;

set mapred.reduce.tasks =2;

方式1：覆盖

INSERT OVERWRITE TABLE hadoop.test_sort select * FROM hadoop.test_normal;

覆盖插入进阶：

1).DISTRIBUTE BY + SORT BY：

INSERT OVERWRITE TABLE hadoop.test_sort select * FROM hadoop.test_normal distribute by id sort by age desc;

注意：(INSERT OVERWRITE TABLE hadoop.test_sort select * FROM hadoop.test_normal cluster by id sort by age desc;)报错！

cluster by + sort by 不能共存，要用distribute by 代替cluster by；因为DISTRIBUTE BY + SORT BY=cluster by！

2).DISTRIBUTE BY + SORT BY=cluster by

如果DISTRIBUTE BY 后的参数与 SORT BY后的参数相同时，可以用CLUSTER BY 代替：

INSERT OVERWRITE TABLE hadoop.test_sort select * FROM hadoop.test_normal CLUSTER BY id desc;

方式2：插入

INSERT INTO TABLE hadoop.test_sort select * FROM hadoop.test_normal;

清理表数据：truncate table table_name；

-----------------------------修改表-----------------------------

1.增删分区：（必须是分区表）

创建分区表：

CREATE TABLE IF NOT EXISTS hadoop.human_static_addr

(

id int,

name string,

age int,

addr string

)

PARTITIONED BY (addr string COMMENT 'human sex partion')

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE;

添加分区： alter table table_name add partition(partition_name="name");

删除分区： alter table table_name drop partition(partition_name="name");

2.重命名表：

alter table table_name rename to new_table_name;

3.增加、更新列：

add新增一列，在所有列追后面追加，

replace替换，修改表中的所有字段

增加列： alter table table_name add columns (columns_name Type);

更新列： alter table table_name replace columns (columns_name Type);

4.修改列：

alter table table_name change column columns_name new_columns_name Type;

--------------------------显示命令------------------------------

与Linux本地环境交互：

！+Linux命令；执行Linux操作：例：!ls;

与hdfs文件进行交互：

例：dfs -ls;对hdfs文件进行操作

【*】:指显示全部信息

库操作：

查看所有库： show databases; 【*】

进入库： use + 库名；

查看库信息： desc database 库名;

查看库完整信息： desc database extended 库名;

表操作：

查看所有表： show tables; 【*】

查看表结构（表描述）： desc 表名;【*】

查看完整表结构：desc extended 表名; 【*】

查看格式化信息： desc formated 表名;

查看分区表信息： show partitions 表名; 【*】

查看表数据： select * from 表名；

获取表创建语句： show create table 表名; 【*】

函数操作：

查看所有函数： show functions; 【*】

查看函数帮助文档： desc function 函数名; 【*】

查看函数完整帮助： desc function extended 函数名;

=========================================

6---DML对数据的操作

=====================DML操作===================

DML：数据操作语言 INTO:追加 OVERWRITE：重写，覆盖

清理表数据：truncate table table_name；

数据的导入：

--------------------------Load：加载----------------------------------

特点：

a.使用Load语句，可以方便的将本地文件系统或者HDFS中的文件，加载到Hive表中。在该语句中，如果包含LOCAL关键字，则复制本地文件系统中的文件到目标表中，如果不包含LOCAL关键字，则移动文件到目标表中。

b.Load语句在加载数据到表中时不会对数据做任何转换，该操作只是单纯地复制或者移动数据文件到Hive表对应的位置。Load语句中的filepath既可以是单个文件也可以是目录，如果是目录的话不可以包含子目录。前一种情况复制或者移动文件到表中，后一种情况复制或者移动目录中的所有文件到表中。

c.如果使用了OVERWRITE关键字，目标表或者分区中的内容将被删除并被替换为filepath指定的文件，若未使用OVERWRITE关键字，文件将会被增加到表中。如果目标表或者分区的某个文件与filepath包含的文件名冲突，则新文件将替换已经存在的文件。

语法格式： []s是可选参数

LOAD DATA [LOCAL【本地上传】] INPATH 'filepath' [OVERWRITE【是否覆盖】] INTO TABLE tablename [PARTITION 【导入到分区】 (partcol1=val1, partcol2=val2 ...)]

本地导入：

例：普通表：

LOAD DATA LOCAL INPATH '/opt/modules-pre/datas/human.txt' OVERWRITE INTO TABLE hadoop.human;

LOAD DATA LOCAL INPATH '/opt/update/text.log' OVERWRITE INTO TABLE text;

分区表：

LOAD DATA LOCAL INPATH '/opt/input/student.txt' INTO TABLE hadoop.human_static_sex PARTITION (sex='man');

HDFS导入：

例：普通表：

LOAD DATA INPATH '/user/student/input/student.txt' OVERWRITE INTO TABLE hadoop.human_hdfs;

分区表：

LOAD DATA INPATH '/user/student/input/student.txt' INTO TABLE hadoop.human_static_sex PARTITION (sex='man');

---------------------insert：插入（复制）----------------------

需经过MapReduce，很慢,但是，动态分区表，分桶表的数据只能用insert进行加载

特点：

a.INSERT OVERWRITE语句将会覆盖表或者分区中任何存在的数据，但如果为分区指定了IF NOT EXISTS关键字则不会覆盖分区中的数据。

b. INSERT INTO语句添加数据到表或者分区中，且保持现存数据完好无缺。Insert语句可以应用于表或分区，如果是一个分区表，在插入数据时必须通过指定所有分区列的值来指定表的分区。

c. Hive还支持动态分区插入，用户可以在PARTITION从句中仅指定分区列的名称，分区列的值是可选的。如果给定分区列的值，称该分区列为静态分区，否则为动态分区。每个动态分区列对应select子句的一个输入列，动态分区列必须在select子句中所有输入列的最后指定，并且与它们在PARTITION从句出现的顺序保持一致。

2. 导入数据到新表中

2.1单表插入（基本模式插入）

语法格式：

INSERT OVERWRITE【覆盖目标表数据】[LOCAL] 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;

事例：覆盖

INSERT OVERWRITE TABLE hadoop.human_hdfs IF NOT EXISTS select id, name, age, sex, addr FROM hadoop.human;

select * from hadoop.human_hdfs;

----；--------------------------------------

追加

INSERT INTO TABLE hadoop.human_hdfs select id, name, age, sex, addr FROM hadoop.human;

select * from hadoop.human_hdfs;

导入到分区：

INSERT INTO TABLE hadoop.human_hdfs partition(partition_name='name') select id, name, age, sex, addr FROM hadoop.human;

2.2（多插入模式）多表插入：

FROM human

INSERT OVERWRITE TABLE student PARTITION(partition_name='name1')

select id, name, age, sex, addr where human_PARTITION(partition_name='name');

INSERT OVERWRITE TABLE student PARTITION(partition_name='name2')

select id, name, age, sex, addr where human_PARTITION(partition_name='name');

=====================动态分区=====================

2.3（自动分区）动态分区的插入：必须用 INSERT 插入数据

方式1：覆盖式

1.1

INSERT OVERWRITE TABLE hadoop.human_dynamic_sex PARTITION (sex='male') select id, name, age, addr FROM hadoop.human_static_sex;

1.2 [推荐]

备注：动态分区，需要关闭 strict （严格模式）开关。

第一步：

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

第二步：

INSERT OVERWRITE TABLE hadoop.human_dynamic_sex PARTITION (sex) select id, name, age, addr, sex FROM hadoop.human_static_sex where sex='male';

方式2：非覆盖

2.1

INSERT INTO TABLE hadoop.human_dynamic_sex PARTITION (sex='male') select id, name, age, addr FROM hadoop.human_static_sex;

2.2

INSERT INTO TABLE hadoop.human_dynamic_sex PARTITION (sex) select id, name, age, addr,sex FROM hadoop.human_static_sex where sex='female';

2.3.2 多级分区表

a.创建多级分区表

CREATE TABLE IF NOT EXISTS hadoop.logs_etl_dynamic

(

ip string,

url string,

status string,

traffic string

)

PARTITIONED BY (year string, month string, day string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE;

b.加载数据

方式1：覆盖式

1.1

INSERT OVERWRITE TABLE hadoop.logs_etl_dynamic PARTITION (year='2018', month='02', day='04') select ip, url, status, traffic FROM hadoop.logs_etl where year='2018' and month='02' and day='04';

1.2 [推荐]

备注：动态分区，需要关闭 strict 开关。

第一步：

set hive.exec.dynamic.partition.mode=nonstrict;

第二步：

INSERT OVERWRITE TABLE hadoop.logs_etl_dynamic PARTITION (year, month, day) select ip, url, status, traffic, year, month, day FROM hadoop.logs_etl where year='2018' and month='02' and day='04';

方式2：非覆盖

2.1

INSERT INTO TABLE hadoop.logs_etl_dynamic PARTITION (year='2017', month='02', day='04') select ip, url, status, traffic FROM hadoop.logs_etl where year='2018' and month='02' and day='04';

2.2

INSERT INTO TABLE hadoop.logs_etl_dynamic PARTITION (year, month, day) select ip, url, status, traffic, year, month, day FROM hadoop.logs_etl where year='2018' and month='02' and day='04';

c. 查看hdfs的目录结构

数据的导出：

---------------------------------hive表数据导出到本地------------------------------------------------

a.方式1：把hive表数据导出到本地

语法格式：

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] ...

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: Only available starting with Hive 0.13)

事例：

INSERT OVERWRITE LOCAL DIRECTORY '/opt/modules-pre/human/h01'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '@' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE

SELECT * FROM hadoop.human;

INSERT OVERWRITE LOCAL DIRECTORY '/opt/modules-pre/human/h02'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\' ESCAPED BY '\\' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE

SELECT * FROM hadoop.human;

INSERT OVERWRITE LOCAL DIRECTORY '/opt/modules-pre/human/h03'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE

SELECT * FROM hadoop.human;

分条件形式导出数据到不同的文件中：

FROM human as a

INSERT OVERWRITE LOCAL DIRECTORY '/opt/modules-pre/human/h04' select a.id, a.name,a.age,a.sex,a.addr where id > 20

INSERT OVERWRITE LOCAL DIRECTORY '/opt/modules-pre/human/h05' select a.id,a.name where id <=20;

--------------------------------hive表数据导出到hdfs上---------------------------------

b.方式2：把hive表数据导出到hdfs上

事例：

INSERT OVERWRITE DIRECTORY '/user/student/hive-output/human/h01'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '@' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE

SELECT * FROM hadoop.human;

INSERT OVERWRITE DIRECTORY '/user/student/hive-output/human/h02'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\' ESCAPED BY '\\' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE

SELECT * FROM hadoop.human;

INSERT OVERWRITE LOCAL DIRECTORY '/user/student/hive-output/human/h03'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE

SELECT * FROM hadoop.human;

分条件形式导出数据到不同的文件中：

FROM human as a

INSERT OVERWRITE DIRECTORY '/user/student/hive-output/human/h04' select a.id, a.name,a.age,a.sex,a.addr where id > 20

INSERT OVERWRITE DIRECTORY '/user/student/hive-output/human/h05' select a.id,a.name where id <=20;

----------------------------------- 把a表数据导出到b表中-------------------------------------

c.方式3：把a表数据导出到b表中

语法格式：

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] ...;

事例：

1.先创建2个空表

CREATE TABLE IF NOT EXISTS hadoop.human_empty01

LIKE hadoop.human;

CREATE TABLE IF NOT EXISTS hadoop.human_empty02

LIKE hadoop.human;

CREATE TABLE IF NOT EXISTS hadoop.human_empty03

LIKE hadoop.human;

2.导出数据到空表中

FROM hadoop.human as a

INSERT OVERWRITE TABLE hadoop.human_empty01 select a.id,a.name, a.age,a.sex, a.addr where id >0 and id<=10

INSERT OVERWRITE TABLE hadoop.human_empty02 select a.id,a.name, a.age,a.sex, a.addr where id >10 and id<=20

INSERT INTO TABLE hadoop.human_empty03 select a.id,a.name, a.age,a.sex, a.addr where id >20 and id<=50;

from students as t insert into table human_like select t.id,t.name,t.sex,t.age,t.addr ;

from students as t insert overwrite table human_like select t.id,t.name,t.sex,t.age ,t.addr where age > 18 and age <=20;

from students as t insert overwrite table human_like select t.* where sex="男";

SELECT查询

-----------------------------------------------------------------

注意：

1.order by 以…排序；

会对输入做全局排序，因此只有一个reduce，会导致当大规模输入数据时，需要较长的计算时间；

2.sort by 以...分类排序方式；

不是全局排序，其在数据进行reduce前完成排序。因此，用sort by 进行排序，并且设置mapred.reduce.tasks>1,则sortby 只保证每个reduce的输出有序，但不保证全局有序。

3.distributed by(字段) 由……分配

需要先开两个tasks，默认的是-1：

set mapred.reduce.tasks= 2;(默认-1)

根据指定的字段将数据分到不同的reduce，且分发的算法是hash散列。

4.Cluster by(字段) 集群的（分桶表用到）

除了具有Distributed by 的功能外，还会对该字段进行排序

因此，如果分桶和sort字段是同一个时，此时， cluster by=distributed by + sort by

分桶表的作用：

最大作用是用来提高join操作的效率；

Select基本操作：

语法格式：

[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0)

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list] 分组

[ORDER BY col_list]

[CLUSTER BY col_list 分区且排序

| [DISTRIBUTE BY col_list] [SORT BY col_list]

]

[LIMIT [offset,] rows]

例子：

1.普通查询：

select * from table_name where id=1;

查询分组统计：

select sex,count(*) from table_name group by sex;

有多少个桶，尽量设置多少个reduce，便于数据的观察。

set mapred.reduce.tasks=3;设置reduce个数

select * from table_name cluster by id;

创建表时直接插入数据：

create table table_name as select * from data_table_name cluster by id;

2.DISTRIBUTE BY

DISTRIBUTE BY 具有数据分布功能，分布到不同的桶中，但是没有对桶数据进行排序的功能。

查询时，使用 DISTRIBUTE BY【此时分桶没有启用，详见后面】

备注：此处使用 DISTRIBUTE BY，但是我们的表只有一个桶，得不出什么结论。如果需要使用DISTRIBUTE BY ，要分2步：a.启用开关； b.分多个桶。

查询：

SELECT * from hadoop.test_normal DISTRIBUTE BY id;

3.SORT BY

SORT BY 具有对桶进行排序的功能。

查询时，使用SORT BY

查询:

SELECT * from hadoop.test_normal SORT BY id;

结论：

SORT BY 单纯的对指定的列数据排序。

4.CLUSTER BY

CLUSTER BY具有数据分布功能，分布到不同的桶中，并且对每个桶中的数据进行排序。

查询时，使用CLUSTER BY 【目前只有1个reduce ，1个桶，所以只有一个桶数据进行排序】

备注：目前的 CLUSTER BY 只看到了排序，而没有看到分布情况

查询:

SELECT * from hadoop.test_normal CLUSTER BY id;

结论：

说明 CLUSTER BY 具有排序功能。

使用 CLUSTER BY 做查询，有多少个桶，尽量设置多少个reduce，便于数据的观察。

set mapred.reduce.tasks=3;设置reduce个数

select * from table_name cluster by id;

5.DISTRIBUTE BY + SORT BY = CLUSTER BY

DISTRIBUTE BY 具有数据分布功能，分布到不同的桶中，但是没有对桶数据进行排序的功能。

SORT BY 具有对桶进行排序的功能。

查询时，使用 DISTRIBUTE BY + SORT BY

查询：

SELECT * from hadoop.test_normal DISTRIBUTE BY id SORT BY id;

结论：

存储的数据没有顺序，使用 DISTRIBUTE BY + SORT BY后，数据将变得有序。

6.ORDER BY

ORDER BY 对数据做全局排序。

查询:

select * from hadoop.test_sort2 ORDER BY name;

桶表的抽样查询：

Select * from table_name tablesample(bucket 1 out of 2 on id)

tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)

y必须是table总bucket(桶)数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

=========================Hive之join========================

Hive之join：

语法格式：

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 支持等值连接(equality joins)、外连接(outer joins)和(lift/right joins)。

a：表1； b：表2

例：

SELECT a.* FROM a JOIN b ON (a.id = b.id)

SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

而：SELECT a.* FROM a JOIN b ON (a.id > b.id) 报错！

hive 不支持非等值连接，因为非等值连接非常难转化到map/reduce 任务。

支持多于2表的多表连接。

只打印（只取）匹配到的，join表之间所关联的数据，其他的数据舍弃不取。

1.内连接 [INNER] JOIN

方式1：【不推荐】：

说明：基于on语句，仅列出表1和表2中符合连接条件的数据。

方式1：不推荐

select t3.*, t4.name as coursename from (SELECT t1.id, t1.name, t1.age, t2.score, t2.c_id FROM student as t1 INNER JOIN score as t2 ON t1.id = t2.s_id) as t3 INNER JOIN course as t4 ON t3.c_id=t4.id;

方式2：推荐

SELECT t1.id, t1.name, t1.age, t2.score, t2.c_id, t3.name as coursename FROM student as t1 INNER JOIN score as t2 ON t1.id = t2.s_id INNER JOIN course as t3 ON t3.id = t2.c_id;

2. {LEFT|RIGHT|FULL} [OUTER] JOIN

LEFT OUTER JOIN：

说明：基于on语句，列出表1[左边]中的全部数据和表2[右边]符合条件的数据，表2不符合条件的用null填充。

select t1.*, t2.* from student as t1 left join score as t2 ON t1.id=t2.s_id;

RIGHT OUTER JOIN：

说明：基于on语句，列出表2[右边]中的全部数据和表1[左边]符合条件的数据，表1不符合条件的用null填充。

SELECT T1.*, T2.* FROM hadoop.student AS T1 RIGHT OUTER JOIN hadoop.score AS T2 ON T1.id = T2.s_id;

FULL OUTER JOIN：

说明：基于on语句，列出表1和表2中全部数据，不符合的用null填充。

SELECT T1.*, T2.* FROM hadoop.student AS T1 FULL OUTER JOIN hadoop.score AS T2 ON T1.id = T2.s_id;

3. LEFT SEMI JOIN 【左半开连接】

说明：查询表1中id在表2中存在的数据，并只显示表1的数据，但不现实表2的数据

SELECT T1.* FROM hadoop.student AS T1 LEFT SEMI JOIN hadoop.score AS T2 ON T1.s_id = T2.s_id;

问题：如何使用[INNER] JOIN 达到 LEFT SEMI JOIN 效果？

方式1：【不推荐】

SELECT DISTINCT T1.* FROM hadoop.student AS T1 INNER JOIN hadoop.score AS T2 ON T1.s_id = T2.s_id;

方式2：【推荐】

SELECT T1.* FROM hadoop.student AS T1 WHERE T1.s_id IN (SELECT DISTINCT s_id FROM hadoop.score);

方式3：【推荐】

SELECT T1.* FROM hadoop.student AS T1 WHERE EXISTS (SELECT 1 FROM hadoop.score AS T2 WHERE T1.s_id = T2.s_id);

4. CROSS JOIN 【笛卡尔积】

说明：结果同内连接一致，返回同时符合表1表2的数据。

SELECT T1.*, T2.* FROM hadoop.student AS T1 CROSS JOIN hadoop.score AS T2 ON T1.s_id = T2.s_id;

=====================集合类型===================

Hive的集合类型：

Hive支持的数据类型分为基础数据类型和集合类型。

基础类型主要包括：tinyint，smalint，int，bigint，boolean，float，double，string，timestamp，ninary等。这些基础类型和其他关系型数据库中的基础数据类型差不多。

集合类型主要包括：array，map，struct等，hive的特性支持集合类型，这特性是关系型数据库所不支持的，利用好集合类型可以有效提升SQL的查询速率。

由于官方文件的实例中没有集合类型的例子，所以就自己尝试了。下面是3种集合类型的简单实现。

1.ARRAY类型的实现（数组类型）

先建一张表，建表脚本如下：

[sql] view plain copy

create table t_person(

id int,

name string,

likes array<string>

)

row format delimited

fields terminated by ','

collection items terminated by '_';

新建一个文本文件，格式如下：【这是根据建表时的规格，准备导入表的数据格式】

1,王力宏,唱歌_钢琴_二胡_作曲_演戏_导演_书法

执行导入数据的命令，然后再查询该表就可以看到数据了。

load data local inpath 'Documents/hive/t_person.txt' into table t_person;

查询一下试试看：【array的访问元素和java中是一样的，这里通过索引来访问】

select name,likes[1] as likes from t_person;

2.MAP类型的实现

先建一张表，建表脚本如下：

create table t_person(

id int,

name string,

tedia map<string,string>

)

row format delimited

fields terminated by ','

collection items terminated by '_'

map keys terminated by ':';

指定每组KV键值对之间的分割符号：collection items terminated by '_'

指定key和value之间的分割符号：map keys terminated by ':';

新建一个文本文件，格式如下：【这是根据建表时的规格，准备导入表的数据格式】

1,王力宏,性别:男_形象:非常健康

执行导入数据的命令，然后再查询该表就可以看到数据了。

load data local inpath 'Documents/hive/t_person.txt' into table t_person;

查询一下试试看：【map访问元素的方式是通过key】

select name,tedia['性别'] as xb from t_person;

select name,tedia["形象"] from t_person;

3.STRUCT类型的实现(结构类型)

先建一张表，建表脚本如下：

create table t_person(

id int,

name string,

address struct<city:string,street:string>

)

row format delimited

fields terminated by ','

collection items terminated by '_';

新建一个文本文件，格式如下：【这是根据建表时的规格，准备导入表的数据格式】

1,王力宏,台湾省_台北市

执行导入数据的命令，然后再查询该表就可以看到数据了。

load data local inpath 'Documents/hive/t_person.txt' into table t_person;

查询一下试试看：【struct访问元素的方式是通过.符号】

select name,address.city as city from t_person;

select name,address.city,address.street from t_person;

==============================hive严格模式=============================

Hive的严格模式和非严格模式

非严格模式:默认模式 hive.mapred.mode=nonstrict

hive严格模式 hive.mapred.mode=strict

hive提供了一个严格模式，可以防止用户执行那些可能产生意想不到的不好的效果的查询。即某些查询在严格模式下无法执行。

通过设置hive.mapred.mode的值为strict，可以禁止3中类型的查询。

1）带有分区的表的查询

如果在一个分区表执行hive，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行。换句话说，

就是用户不允许扫描所有的分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。

如果没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表：

hive> SELECT DISTINCT(planner_id) FROM fracture_ins WHERE planner_id=5;

FAILED: Error in semantic analysis: No Partition Predicate Found for Alias "fracture_ins" Table "fracture_ins

select * from table_name;

如下这个语句在where语句中增加了一个分区过滤条件（也就是限制了表分区）：

SELECT DISTINCT(planner_id) FROM fracture_ins WHERE planner_id=5 AND hit_date=20120101;

... normal results ...

select * from table_name where partition="partition_name";

2）带有 order by 的查询

对于使用了 order by 的查询，要求必须有limit语句。因为 order by 为了执行排序过程会讲所有的结果分发到同一个reducer中进行处理，

强烈要求用户增加这个limit语句可以防止reducer额外执行很长一段时间：

SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id;

FAILED: Error in semantic analysis: line 1:56 In strict mode,

limit must be specified if ORDER BY is present planner_id

只需要增加 limit 语句就可以解决这个问题：

SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id LIMIT 100000;

... normal results ...

3）限制笛卡尔积的查询

对关系型数据库非常了解的用户可能期望在执行join查询的时候不使用on语句而是使用where语句，这样关系数据库的执行优化器就可以高效的将where语句转换成那个on语句。不幸的是，hive不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况：

SELECT * FROM fracture_act JOIN fracture_ads WHERE fracture_act.planner_id = fracture_ads.planner_id;

FAILED: Error in semantic analysis: In strict mode, cartesian product

is not allowed. If you really want to perform the operation,

+set hive.mapred.mode=nonstrict+

下面这个才是正确的使用join和on语句的查询：

SELECT * FROM fracture_act JOIN fracture_ads ON (fracture_act.planner_id = fracture_ads.planner_id);

... normal results ...

================================Hive Shell 参数=============================

Hive Shell 参数

Hive 命令行参数：

-i 从文件执行初始化HQL后进入交互模式。

-e 从命令行执行指定的HQL语句。

-f 执行HQL脚本文件。

-v 输出执行的HQL语句到控制台。

-p connect to Hive Server on port number -hiveconf x=y Use this to set hive/hadoop configuration variables.

1）从命令行执行指定的sql语句

hive -e ‘create table tb1(id int, name string, age int);’

2）在进入交互模式之前，执行初始化的sql语句文件

在/home/hive-1.1.0里准备一个sql文件init.sql，写入sql语句：

create table tb2(id int, name string, age int);

show tables;

然后执行：

hive -v -i /home/hive-1.1.0/init.sql

3）以非交互式模式执行sql文件sql语句

hive -f /home/hive-1.1.0/script.sql

4）以指定的hive环境变量执行指定的sql语句

hive -e ‘select a.col from tb2 a’

-hiveconf hive.exec.scratchdir=/home/hive-1.1.0/hive_scratch

-hiveconf mapred.reduce.tasks=32

Hive 参数配置方式

https://blog.csdn.net/away30/article/details/77979488

Hive提供三种可以改变环境变量的方法，分别是：

（1）、修改${HIVE_HOME}/conf/hive-site.xml配置文件；

（2）、命令行参数；

（3）、在已经进入cli时进行参数声明。

上述三种设定方式的优先级依次递增。即参数声明覆盖命令行参数，命令行参数覆盖配置文件设定。

Hive 函数

==============Hive 函数==============

Hive 函数分为内置函数和自定义函数

内置函数 --》简单函数 --》map阶段

聚合函数 --》reduce阶段

集合函数 --》map阶段

特殊函数

SQL基本函数，聚合函数对一组值执行计算，并返回单个值。除了 COUNT 以外，聚合函数都会忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。

自定义函数 --》 UDF --》map阶段 1进1出

UDAF --》reduce阶段多进1出

UDTF --》reduce阶段多进多出

Hive中有三种UDF:(普通)UDF、用户定义聚集函数（user-defined aggregate function，UDAF）、用户定义表生成函数（user-defined table-generating function，UDTF）。

UDF操作作用于单个数据行，并且产生一个数据行作为输出。大多数函数都属于这一类（比如数学函数和字符串函数）。

UDAF 接受多个输入数据行，并产生一个输出数据行。想 COUNT 和 MAX 这样的函数就是聚集函数。

UDTF 操作作用于单个数据行，并且产生多个数据行-------一个表作为输出

1.udf

输入一行的一列，返回一行的一列。

第一种方式：Simple API - org.apache.hadoop.hive.ql.exec.UDF

第二种方式：Complex API - org.apache.hadoop.hive.ql.udf.generic.GenericUDF

input ---》单条记录 ---》 output ---》单条记录。

流程：自定义java类---》继承UDF类----》重写 evaluate 方法---》当前打包jar ---》hive shell中执行add jar命令---》hive shell create function ---》hive shell use function

代码中添加描述：

@Description(name = "sum", value = "_FUNC_(x) - Returns the sum of a set of numbers")

public class GenericUDAFSum extends AbstractGenericUDAFResolver {

自定义函数的使用流程：

a. 添加自定义jar包到hive中

语法格式：

add jar

Usage: add [FILE|JAR|ARCHIVE] <value> [<value>]*

事例：

add JAR /opt/modules-pre/jars/quarter.jar;

add JAR /opt/modules-pre/jars/quarter2.jar;

b. 创建函数

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create/Drop/ReloadFunction

语法格式：

CREATE TEMPORARY FUNCTION function_name AS class_name;

事例：

CREATE TEMPORARY FUNCTION myquarter AS 'hive.udf.simple01.ParseQuarter';

CREATE TEMPORARY FUNCTION myquarter2 AS 'hive.udf.simple01.ParseQuarter';

c. 查看函数

show functions;

d.永久添加函数到hive中

第一步：在$HIVE_HOME/bin 目录下创建 .hiverc隐藏文件

第二步：vim .hiverc

add JAR /opt/modules-pre/jars/parseurl.jar;

CREATE TEMPORARY FUNCTION myquarter2 AS 'hive.udf.simple01.ParseQuarter';

第三步：打开hive cli

e.使用自定义函数

select '2017-10-10';

select myquarter2('2017-10-10');

2.udaf

输入一行的多列，返回一行的一列。

执行流程：

a.udaf处理流程：

详细介绍：

1.如果有mapper和reducer，就会经历PARTIAL1(mapper)，FINAL(reducer)。

2.如果还有combiner，那就会经历PARTIAL1(mapper)，PARTIAL2(combiner)，FINAL(reducer)。

3.有一些情况下的mapreduce，只有mapper，而没有reducer，所以就会只有COMPLETE阶段，这个阶段直接输入原始数据的结果。

public static enum Mode {

/**

* PARTIAL1: 这个是mapreduce的map阶段:从原始数据到部分数据聚合

* 将会调用iterate()和terminatePartial()

PARTIAL1,

/**

* PARTIAL2: 这个是mapreduce的map端的Combiner阶段，负责在map端合并map的数据::从部分数据聚合到部分数据聚合:

* 将会调用merge() 和 terminatePartial()

PARTIAL2,

/**

* FINAL: mapreduce的reduce阶段:从部分数据的聚合到完全聚合

* 将会调用merge()和terminate()

FINAL,

/**

* COMPLETE: 如果出现了这个阶段，表示mapreduce只有map，没有reduce，所以map端就直接出结果了:从原始数据直接到完全聚合

* 将会调用 iterate()和terminate()

COMPLETE

};

b. udaf的具体实现

org.apache.hadoop.hive.ql.udf.generic.AbstractGenericUDAFResolver

org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator

AbstractGenericUDAFResolver

###GenericUDAFEvaluator

public static class GenericUDAFSumLong extends GenericUDAFEvaluator {}

c.执行调用

drop temporary function IF EXISTS mysumage;

reload functions;

add JAR /opt/modules-pre/jars/SumAge.jar;

CREATE TEMPORARY FUNCTION mysumage AS 'day20.hive.udaf.SumAge';

select mysumage(1,2);

add JAR /opt/modules-pre/jars/mycount.jar;

CREATE TEMPORARY FUNCTION mycount AS 'day20.hive.udaf.New_Count';

select mycount(1,2);

select * from hadoop.human;

3.udtf

博客借鉴：

https://www.cnblogs.com/ggjucheng/archive/2013/02/01/2888819.html

1.内置运算符：

太多了，不写了，自己去官方文档查看！

2.内置函数:

太多了，不写了，自己去官方文档查看！

测试各种内置函数的快捷方式：

1.创建一个 dual 表

create table dual(id string);

2.load 一个文件（一行，一个空格数据）到 dual 表；

3.select substr('zhangsan',2,3) from dual;

3.Hive 自定义函数 UDF 和 Transform （转化）

UDF （User-defined function）用户自定义函数；

UDAF （User-defined aggregation functions.）用户自定义聚集函数；

解析json格式数据：

1.编写代码继承UDF类，定义自己的方法，然后打包上传到Linux环境；

创建项目；写指定的类：

a.首先把表数据的字段格式，创建相应的类，用对象的形式接收存储指定的字段信息；实现读写方法，和toString方法返回拼接的字段信息；

例： return name+"\t"+age+"\t"+sex;

b.创建编写指定方法的类，继承UDF类，定义自己所需要的函数；

public class ParseJson exten

2.在hive仓库中，添加jar包；

3.创建hive仓库的临时函数；

create temporary function function_name as 'jar包的全路径名';

3.用函数进行查询json格式的数据表；

select function_name(参数) from json_table_name limit 10;

{"movie":"1193","rate":"5","timeStamp":"99955","uid","1"}

create table human_json(line string) row format delimited;

create table rat_json(line string) row format delimited;

load data local inpath '/opt/input/human_json' into table human_json;

load data local inpath '/opt/input/rat_json' into table rat_json;

use json;

add jar /opt/install/json.jar;

create temporary function selecthuman as 'Hive.Hive_json.ParseJsom';

create temporary function parsejson as 'cn.hive.json.udf.ParseJson';

select selecthuman(line) from human_json;

select parsejson(line) from rat_json;

dual:对偶双数双重

concat: 合并多个数组；合并多个字符串

truncate

清理表数据：truncate table table_name；

============================================================

6.2---索引

3.1 创建索引

语法格式：

CREATE INDEX index_name【索引名称】

ON TABLE base_table_name【需要建立索引的表】 (col_name, ...)

AS index_type 【索引类型】

[WITH DEFERRED REBUILD]

[IDXPROPERTIES (property_name=property_value, ...)] 【索引属性】

[IN TABLE index_table_name] 【索引表名称】

[

[ ROW FORMAT ...] STORED AS ... 【格式指定】

| STORED BY ...

]

[LOCATION hdfs_path]

[TBLPROPERTIES (...)]

[COMMENT "index comment"];

测试：创建索引之前，做查询所花费的时间

测试1：

hive (hadoop)> select * from logs_etl_dynamic where ip='172.25.1.8999';

logs_etl_dynamic.ip logs_etl_dynamic.url logs_etl_dynamic.status logs_etl_dynamic.traffic logs_etl_dynamic.year logs_etl_dynamic.month logs_etl_dynamic.day

172.25.1.8999 www.baidu.com/xxx8999.html 200 8999 2017 02 04

172.25.1.8999 www.baidu.com/xxx8999.html 200 8999 2018 02 04

Time taken: 0.143 seconds, Fetched: 2 row(s)

测试2：

hive (hadoop)> select count(ip) from logs_etl_dynamic where ip='172.25.1.8999';

Time taken: 48.099 seconds, Fetched: 1 row(s)

事例：

CREATE INDEX logs_etl_dynamic_index

ON TABLE hadoop.logs_etl_dynamic (ip)

AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'

WITH DEFERRED REBUILD

IDXPROPERTIES ('author'='tony')

IN TABLE logs_etl_dynamic_index_ip

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE;

备注：该索引建立好之后，还需要生成索引值，详见3.2

查看索引表结构：

hive (hadoop)> desc logs_etl_dynamic_index_ip;

col_name data_type comment

ip string

_bucketname string

_offsets array<bigint>

year string

month string

day string

# Partition Information

# col_name data_type comment

year string

month string

day string

查询索引表的数据：

select * from logs_etl_dynamic_index_ip;

发现表中没有数据。

3.2 生成索引值

备注：索引，说白就是对原表解析，建立偏移量。

语法格式：

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

事例：

ALTER INDEX logs_etl_dynamic_index ON hadoop.logs_etl_dynamic REBUILD;

3.3 测试建立索引后的所花费的时间

测试1：

hive (hadoop)> select * from logs_etl_dynamic where ip='172.25.1.8999';

logs_etl_dynamic.ip logs_etl_dynamic.url logs_etl_dynamic.status logs_etl_dynamic.traffic logs_etl_dynamic.year logs_etl_dynamic.month logs_etl_dynamic.day

172.25.1.8999 www.baidu.com/xxx8999.html 200 8999 2017 02 04

172.25.1.8999 www.baidu.com/xxx8999.html 200 8999 2018 02 04

Time taken: 0.145 seconds, Fetched: 2 row(s)

测试2：

hive (hadoop)> select count(ip) from logs_etl_dynamic where ip='172.25.1.8999';

Time taken: 38.212 seconds, Fetched: 1 row(s)

3.4 删除索引

语法格式：

DROP INDEX [IF EXISTS] index_name ON table_name;

事例：

DROP INDEX IF EXISTS index_human ON hadoop.human;

备注：删除需要指定创建时 INDEX 后面的名称。

4. HQL 之 JOIN

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

语法格式：

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)

table_reference:

table_factor

| join_table

table_factor:

tbl_name [alias]

| table_subquery alias

| ( table_references )

join_condition:

ON expression

事前准备：

a. 创建 student， score， course 表

create table hadoop.student(id int, name string, age int)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE;

create table hadoop.course(id int, name string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE;

create table hadoop.score(id int, s_id int, c_id int, score float)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '\n'

STORED AS TEXTFILE;

student:

10000,zhangsan,20

10001,lisi,30

10002,wangwu,25

10003,zhaoliu,26

11000,tony,40

21000,lly,26

course:

1,english

2,chiness

3,math

400,xxxx

500,yyyy

1000,zzzz

score:

1,10000,1,100

2,10000,2,40

3,10000,3,60

4,10001,1,90

5,10001,2,80

6,10001,3,80

7,10002,1,0

8,10003,2,70

9,10004,3,0

10,10006,7,100

11,10007,8,80

12,10008,9,90

13,10009,500,90

14,21000,1000,100

b. 加载数据

LOAD DATA LOCAL INPATH '/opt/modules-pre/datas/student.txt' OVERWRITE INTO TABLE hadoop.student;

LOAD DATA LOCAL INPATH '/opt/modules-pre/datas/score.txt' OVERWRITE INTO TABLE hadoop.score;

LOAD DATA LOCAL INPATH '/opt/modules-pre/datas/course.txt' OVERWRITE INTO TABLE hadoop.course;

事例：

4.1 内连接 [INNER] JOIN

说明：基于on语句，仅列出表1和表2中符合连接条件的数据。

方式1：不推荐

select t3.*, t4.name as coursename from (SELECT t1.id, t1.name, t1.age, t2.score, t2.c_id FROM student as t1 INNER JOIN score as t2 ON t1.id = t2.s_id) as t3 INNER JOIN course as t4 ON t3.c_id=t4.id;

方式2：推荐

SELECT t1.id, t1.name, t1.age, t2.score, t2.c_id, t3.name as coursename FROM student as t1 INNER JOIN score as t2 ON t1.id = t2.s_id INNER JOIN course as t3 ON t3.id = t2.c_id;

4.2 {LEFT|RIGHT|FULL} [OUTER] JOIN

LEFT OUTER JOIN：

说明：基于on语句，列出表1[左边]中的全部数据和表2[右边]符合条件的数据，表2不符合条件的用null填充。

select t1.*, t2.* from student as t1 left join score as t2 ON t1.id=t2.s_id;

RIGHT OUTER JOIN：

说明：基于on语句，列出表2[右边]中的全部数据和表1[左边]符合条件的数据，表1不符合条件的用null填充。

SELECT T1.*, T2.* FROM hadoop.student AS T1 RIGHT OUTER JOIN hadoop.score AS T2 ON T1.id = T2.s_id;

FULL OUTER JOIN：

说明：基于on语句，列出表1和表2中全部数据，不符合的用null填充。

SELECT T1.*, T2.* FROM hadoop.student AS T1 FULL OUTER JOIN hadoop.score AS T2 ON T1.id = T2.s_id;

4.3 LEFT SEMI JOIN 【左半开连接】

说明：查询表1中id在表2中存在的数据，并只显示表1的数据，但不现实表2的数据

SELECT T1.* FROM hadoop.student AS T1 LEFT SEMI JOIN hadoop.score AS T2 ON T1.s_id = T2.s_id;

问题：如何使用[INNER] JOIN 达到 LEFT SEMI JOIN 效果？

方式1：【不推荐】

SELECT DISTINCT T1.* FROM hadoop.student AS T1 INNER JOIN hadoop.score AS T2 ON T1.s_id = T2.s_id;

方式2：【推荐】

SELECT T1.* FROM hadoop.student AS T1 WHERE T1.s_id IN (SELECT DISTINCT s_id FROM hadoop.score);

方式3：【推荐】

SELECT T1.* FROM hadoop.student AS T1 WHERE EXISTS (SELECT 1 FROM hadoop.score AS T2 WHERE T1.s_id = T2.s_id);

4.4 CROSS JOIN 【笛卡尔积】

说明：结果同内连接一致，返回同时符合表1表2的数据。

SELECT T1.*, T2.* FROM hadoop.student AS T1 CROSS JOIN hadoop.score AS T2 ON T1.s_id = T2.s_id;

==========================================

6.3---常用内置函数

聚合函数:

count() (统计行数)

select count(name) from human;

avg() (指定列的平均值)

select avg(age) from human;

max() (找出列中最大值)

select max(age) from human;

min() (找出列中最小值)

select min(age),* from human;

sum() (指定列求和)

select sum(age) from human;

=============================================

本人初来乍到大数据，以上为自己学习时整理的笔记，有借鉴其他人的地方，但是当时没有做特殊批注，望谅解，希望这个笔记能给初学hive者帮助，欢迎技术大牛给本宝宝提意见，和技术交流！邮箱：whk3223@qq.com

=============================================

whk3223

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Hive笔记

1.1Hive简介1.1.1什么是Hive 基于hadoop的一个数据仓库工具，把结构化的数据文件映射为一张数据库表，并提供类SQL查询功能；1.1.1为什么使用HiveØ直接使用hadoop所面临的问题人员学习成本太高项目周期要求太短MapReduce实现复杂查询逻辑开发难度太大Ø为什么要使用Hive操作接口采用类SQL语法，提供快速开发的能力。避免了去写MapReduce，减少...
复制链接

扫一扫

专栏目录

Hive笔记

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