最具影响力的数字化技术在线社区

168大数据

 找回密码
 立即注册

QQ登录

只需一步,快速开始

1 2 3 4 5
开启左侧

[Hive] Hive 数据仓库工具(最全整理)

[复制链接]
发表于 2019-9-24 15:06:06 | 显示全部楼层 |阅读模式

马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
工作时,数据统计分析、挖掘的时候用到很多Hive方面的内容,就做了一个完整的整理文档。


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


·   Hive 定义了简单的类 SQL查询语言,称为 HQL,它允许熟悉 SQL的用户查询数据。


·   Hive 将用户的HiveQL语句通过解释器转换为MapReduce作业提交到Hadoop集群上,Hadoop 监控作业执行过程,然后返回作业执行结果给用户。Hive并非为联机事务处理而设计,Hive并不提供实时的查询和基于行级的数据更新操作。Hive的最佳使用场合是大数据集的批处理作业,例如,网络日志分析。


Tip1: Hadoop


是一个开源框架来存储和处理大型数据在分布式环境中。它包含两个模块,一个是MapReduce,另外一个是Hadoop分布式文件系统(HDFS)。


•MapReduce:一种并行编程模型在大型集群普通硬件可用于处理大型结构化,半结构化和非结构化数据。


•HDFS:Hadoop分布式文件系统是Hadoop的框架的一部分,用于存储和处理数据集。它提供了一个容错文件系统在普通硬件上运行。


Hadoop生态系统包含了用于协助Hadoop的不同的子项目(工具)模块,如Sqoop, Pig和 Hive。


•Sqoop: 它是用来在HDFS和RDBMS之间来回导入和导出数据。


ig: 它是用于开发MapReduce操作的脚本程序语言的平台。


•Hive: 它是用来开发SQL类型脚本用于做MapReduce操作的平台。


注:有多种方法来执行MapReduce作业:


•传统的方法是使用Java MapReduce程序结构化,半结构化和非结构化数据。


•针对MapReduce的脚本的方式,使用Pig来处理结构化和半结构化数据。


•Hive查询语言(HiveQL或HQL)采用Hive为MapReduce的处理结构化数据。


1.1Hive 体系结构
1、用户接口


用户接口主要有三个:CLI,Client和 WUI。其中最常用的是 CLI,Cli启动的时候,会同时启动一个 Hive副本。Client 是 Hive 的客户端,用户连接至 Hive Server。在启动 Client模式的时候,需要指出 Hive Server所在节点,并且在该节点启动 Hive Server。 WUI是通过浏览器访问Hive。


2、元数据存储


Hive 将元数据存储在数据库中,如 mysql、derby。Hive中的元数据包括表的名字,表的列和分区及其属性,表的属性(是否为外部表等),表的数据所在目录等。


3、解释器、编译器、优化器、执行器


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


4、Hadoop


Hive 的数据存储在 HDFS中,大部分的查询由 MapReduce完成。





1.2 Hive数据存储
首先Hive没有专门的数据存储格式,也没有为数据建立索引,用户可以非常自由的组织 Hive中的表,只需要在创建表的时候告诉 Hive数据中的列分隔符和行分隔符,Hive就可以解析数据。


其次,Hive中所有的数据都存储在 HDFS 中,Hive中包含以下数据模型:表(Table),外部表(External Table),分区(Partition),桶(Bucket)。


Hive 中的 Table和数据库中的 Table在概念上是类似的,每一个 Table在 Hive 中都有一个相应的目录存储数据。例如,一个表 pvs,它在 HDFS中的路径为:/wh/pvs,其中,wh是在 hive-site.xml中由 ${hive.metastore.warehouse.dir}指定的数据仓库的目录,所有的 Table数据(不包括 External Table)都保存在这个目录中。


Partition 对应于数据库中的 Partition列的密集索引,但是 Hive中 Partition的组织方式和数据库中的很不相同。在 Hive中,表中的一个 Partition对应于表下的一个目录,所有的 Partition的数据都存储在对应的目录中。例如:pvs表中包含 ds 和 city 两个 Partition,则对应于 ds = 20090801, ctry = US的 HDFS 子目录为:/wh/pvs/ds=20090801/ctry=US;对应于 ds = 20090801, ctry = CA的 HDFS 子目录为;/wh/pvs/ds=20090801/ctry=CA


Buckets 对指定列计算 hash,根据 hash值切分数据,目的是为了并行,每一个 Bucket对应一个文件。将 user列分散至 32 个 bucket,首先对 user列的值计算 hash,对应 hash值为 0 的 HDFS 目录为:/wh/pvs/ds=20090801/ctry=US/part-00000;hash值为 20 的 HDFS 目录为:/wh/pvs/ds=20090801/ctry=US/part-00020


External Table 指向已经在 HDFS 中存在的数据,可以创建 Partition。它和 Table在元数据的组织上是相同的,而实际数据的存储则有较大的差异。


Table 的创建过程和数据加载过程(这两个过程可以在同一个语句中完成),在加载数据的过程中,实际数据会被移动到数据仓库目录中;之后对数据对访问将会直接在数据仓库目录中完成。删除表时,表中的数据和元数据将会被同时删除。





1.3Hive创建数据库
CREATE DATABASE语句


创建数据库是用来创建数据库在Hive中语句。在Hive数据库是一个命名空间或表的集合。此语法声明如下:


CREATEDATABASE|SCHEMA[IF NOT EXISTS]<database name>


IF NOT EXISTS是一个可选子句,通知用户已经存在相同名称的数据库。


可以使用SCHEMA在DATABASE的这个命令。下面的查询执行创建一个名为userdb数据库:


hive> CREATE DATABASE[IF NOT EXISTS] userdb;


或,hive> CREATE SCHEMA userdb;


以下的查询用于验证数据库列表:


hive>SHOW DATABASES;


default


userdb


JDBC 程序


在JDBC程序(HiveCreateDb.java文件)来创建数据库如下。


import java.sql.SQLException;


import java.sql.Connection;


import java.sql.ResultSet;


import java.sql.Statement;


import java.sql.DriverManager;





publicclassHiveCreateDb{


   privatestaticStringdriverName="org.apache.hadoop.hive.jdbc.HiveDriver";


  


   publicstaticvoid main(String[] args)throwsSQLException{


      // Register driver and createdriver instance


  


      Class.forName(driverName);


      // get connection


     


      Connection con=DriverManager.getConnection("jdbc:hive://localhost:10000/default","","");


      Statement stmt= con.createStatement();


     


      stmt.executeQuery("CREATEDATABASE userdb");


      System.out.println(“Database userdbcreated successfully.”);


     


      con.close();


   }


}


JDBC :数据库连接,一个JAVA API可访问任何类型表列数据,用于连接到数据库;创建SQL或MySQL语句;在数据库中执行SQL或MySQL查询;查看和修改数据库中的数据记录。


1.4Hive删除数据库
DROP DATABASE语句


DROP DATABASE是删除所有的表并删除数据库的语句。它的语法如下:


DROP DATABASEStatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name


[RESTRICT|CASCADE];


删除名称为userb的数据库;


hive> DROP DATABASE IF EXISTS userdb;


使用CASCADE查询删除数据库。这意味着要全部删除相应的表在删除数据库之前;


hive> DROP DATABASE IF EXISTS userdb CASCADE;


使用SCHEMA查询删除数据库;


hive> DROP SCHEMA userdb;


另外也可通过JDBC程序删除数据库。


1.5 Hive创建表
Create Table是用于在Hive中创建表的语句。语法和示例如下:


语法:


CREATE [TEMPORARY][EXTERNAL] TABLE [IF NOTEXISTS][db_name.] table_name

[(col_namedata_type[COMMENT col_comment],...)]


[COMMENTtable_comment]


[ROWFORMAT row_format]


[STOREDAS file_format]


示例:需使用CREATE TABLE语句创建一个名为employee表。employee表中的字段和数据类型如下:


Sr.No


字段名称


数据类型


1


Eid


int


2


Name


String


3


Salary


Float


4


Designation


string


下面的数据是一个注释,行格式字段,如字段终止符,行终止符,并保存的文件类型。


COMMENT ‘Employee details’


FIELDSTERMINATED BY ‘\t’


LINES TERMINATEDBY ‘\n’


STORED IN TEXTFILE


下面的查询创建使用上述数据的表名为 employee。


hive>CREATE TABLE IF NOT EXISTS employee( eidint,nameString,


>salaryString, destinationString)


> COMMENT‘Employee details’


> ROWFORMAT DELIMITED


> FIELDSTERMINATED BY‘\t’


> LINESTERMINATED BY‘\n’


> STOREDAS TEXTFILE;


如果添加选项IF NOT EXISTS,Hive忽略大小写,万一表已经存在的声明。


成功创建表后,能看到以下回应:


OK


Time taken:5.905 seconds


hive>


LOAD DATA语句


一般来说,在SQL创建表后,可以使用INSERT语句插入数据。但在Hive中,可使用LOAD DATA语句插入数据。


同时将数据插入到Hive,最好是使用LOAD DATA来存储大量记录。有两种方法用来加载数据:一种是从本地文件系统,另一种是从Hadoop文件系统。


加载数据:


LOAD DATA [LOCAL] INPATH'filepath'[OVERWRITE] INTOTABLE tablename


[PARTITION(partcol1=val1,partcol2=val2...)]


•  LOCAL是标识符指定本地路径。它是可选的。


•  OVERWRITE是可选的,覆盖表中的数据。


•  PARTITION这是可选的


例如;插入在/home/user目录中名为sample.txt文件的数据。


hive> LOADDATA LOCAL INPATH'/home/user/sample.txt'


> OVERWRITE INTO TABLE employee;

1.6 Hive 修改表
修改表的属性,如,修改表名,修改列名,添加列,删除或替换列。


Alter Table 语句


语法:声明接受任意属性,我们希望在一个表中修改以下语法。


ALTER TABLE name RENAME TOnew_name


ALTER TABLE name ADDCOLUMNS (col_spec[, col_spec...])


ALTER TABLE name DROP [COLUMN]column_name


ALTER TABLE name CHANGEcolumn_name new_name new_type


ALTER TABLE name REPLACECOLUMNS(col_spec[, col_spec...])


    Rename To… 查询重命名表,把 employee修改为 emp


hive> ALTER TABLE employee RENAME TO emp;


Change …修改列名和列数据类型:


下表为例,显示employee表中字段要被更改(粗体)。


字段名


从数据类型转换


更改字段名称


转换为数据类型


eid


int
FULL OUTER JOIN
eid


int


name


String


ename


String


salary


Float


salary


Double


designation


String


designation


String


语句;


hive>ALTER TABLE employee CHANGE name enameString;


hive> ALTER TABLE employeeCHANGE salary salaryDouble;


添加列语句


下面的查询增加了一个列名dept在employee表。


hive>ALTER TABLE employee ADD COLUMNS(


   > dept STRING COMMENT'Department name');


REPLACE语句


以下从employee表中查询使用empid代替eid列,name代替ename列


hive>ALTER TABLE employee REPLACE COLUMNS(


   >eid INT empidInt,


   > ename STRING nameString);





1.7Hive删除表
Drop Table语句


语法:


DROP TABLE[IFEXISTS] table_name;


以下查询删除一个名为 employee的表:


hive> DROP TABLE IF EXISTSemployee;


查询验证表的列表


hive>SHOW TABLES;


emp


ok


Time taken:2.1 seconds





1.8Hive分区
分区含义;组织表到分区。它是将一个表到基于分区列,如日期,城市和部门的值相关方式。使用分区,很容易对数据进行部分查询。


表或分区是细分成桶,以提供额外的结构,可以使用更高效的查询的数据。桶的工作是基于表的一些列的散列函数值。


例如,一个Tab1表包含雇员数据,如 id, name, dept和yoj (即加盟年份),如果用年份分区雇员数据并将其存储在一个单独的文件,它减少了查询处理时间。下例表示分区的文件和数据;


employee 数据表中,


/tab1/employeedata/file1


id, name, dept, yoj


1, gopal, TP, 2012


2, kiran, HR, 2012


3, kaleel,SC, 2013


4, Prasanth, SC, 2013


上面的数据被划分成使用年两个文件。


/tab1/employeedata/2012/file2


1, gopal, TP, 2012


2, kiran, HR, 2012


/tab1/employeedata/2013/file3


3, kaleel,SC, 2013


4, Prasanth, SC,2013


添加分区


可以通过添加分区表改变所述表。假设我们有一个表叫employee,拥有如 Id, Name, Salary,Designation, Dept,和 yoj等字段。


语法:


ALTER TABLE table_name ADD[IF NOT EXISTS] PARTITION partition_spec


[LOCATION'location1'] partition_spec[LOCATION'location2']...;





partition_spec:


p_column= p_col_value, p_column= p_col_value,...)


以下查询用于将分区添加到employee表。


hive>ALTER TABLE employee


>ADD PARTITION(year=’2013’)


> location'/2013/part2013';


重命名分区


此命令的语法如下。


ALTER TABLE table_namePARTITION partition_spec RENAME TO PARTITION partition_spec;


hive>ALTER TABLE employee PARTITION(year=’1203’)


  > RENAME TO PARTITION(Yoj=’1203’);


删除分区


下面语法用于删除分区:


ALTER TABLE table_name DROP[IF EXISTS] PARTITION partition_spec, PARTITIONpartition_spec,...;


以下查询是用来删除分区:


hive>ALTER TABLE employee DROP[IF EXISTS]


  >ARTITION(year=’1203’);





1.9Hive内置运算符
在Hive有四种类型的运算符:


•  关系运算符


•  算术运算符


•  逻辑运算符


•  复杂运算符


关系运算符;用来比较两个操作数


下表描述Hive中可用的关系运算符:


运算符


操作


描述


A = B


所有基本类型


如果表达A等于表达B,结果TRUE,否则FALSE。


A != B


所有基本类型


如果A不等于表达式B表达返回TRUE,否则FALSE。


A < B


所有基本类型


TRUE,如果表达式A小于表达式B,否则FALSE。


A <= B


所有基本类型


TRUE,如果表达式A小于或等于表达式B,否则FALSE。


A > B


所有基本类型


TRUE,如果表达式A大于表达式B,否则FALSE。


A >= B


所有基本类型


TRUE,如果表达式A大于或等于表达式B,否则FALSE。


A IS NULL


所有类型


TRUE,如果表达式的计算结果为NULL,否则FALSE。


A IS NOT NULL


所有类型


FALSE,如果表达式A的计算结果为NULL,否则TRUE。


A LIKE B


字符串


TRUE,如果字符串模式A匹配到B,否则FALSE。


A RLIKE B


字符串


NULL,如果A或B为NULL;TRUE,如果A任何子字符串匹配Java正则表达式B;否则FALSE。


A REGEXP B


字符串


等同于RLIKE.


示例


假设employee表由字段:Id, Name,Salary, Designation,和Dept组成,如下图所示。生成一个查询检索员工详细信息 - ID为1205。


+-----+--------------+--------+-----------------------+---


| Id  | Name        | Salary | Designation           |Dept |


+-----+--------------+------------------------------------+---


|1201 |Gopal        | 45000  | Technical manager     | TP  |


|1202 |Manisha      | 45000  | Proofreader           | PR   |


|1203 |Masthanvali  | 40000  | Technical writer      | TP  |


|1204 |Krian        | 40000  | Hr Admin              | HR   |


|1205 |Kranthi      | 30000  | Op Admin              | Admin|


+-----+--------------+--------+-----------------------+---


以下查询执行检索使用上述表中的雇员的详细信息:


hive>SELECT* FROM employee WHEREId=1205;


成功执行的查询,有以下结果:


+-----+-----------+-----------+----------------------+


| ID  | Name     | Salary    | Designation          | Dept  |


+-----+---------------+-------+----------------------+


|1205 | Kranthi   | 30000    | Op Admin             | Admin |


+-----+-----------+-----------+----------------------+


下面的查询执行以检索薪水大于或等于40000卢比的雇员的详细信息。


hive>SELECT* FROM employee WHERESalary>=40000;


成功执行的查询,有以下回应:


+-----+------------+--------+------------------------+------+


| ID  | Name      | Salary | Designation           | Dept |


+-----+------------+--------+------------------------+------+


|1201 |Gopal      | 45000  | Technical manager      | TP  |


|1202 | Manisha    | 45000 | Proofreader            | PR  


|1203 |Masthanvali| 40000  | Technicalwriter       | TP   |


|1204 |Krian      | 40000  | Hr Admin               | HR   |


+-----+------------+--------+------------------------+------+


算术运算符:支持的操作数各种常见的算术运算。返回数字类型。下表描述了在Hive中可用的算术运算符:


运算符


操作


描述


A + B


所有数字类型


A加B的结果


A - B


所有数字类型


A减去B的结果


A * B


所有数字类型


A乘以B的结果


A / B


所有数字类型


A除以B的结果


A % B


所有数字类型


A除以B.产生的余数


A & B


所有数字类型


A和B的按位与结果


A | B


所有数字类型


A和B的按位或结果


A ^ B


所有数字类型


A和B的按位异或结果


~A


所有数字类型


A按位非的结果


示例


下面的查询相加两个数字,20和30。


hive>SELECT20+30 ADD FROM temp;


在成功执行查询后,有以下回应:


+--------+


|   ADD  |


+--------+


|   50   |


+--------+


逻辑运算符


运算符是逻辑表达式。所有这些返回TRUE或FALSE。


运算符


操作


描述


A AND B


boolean


TRUE,如果A和B都是TRUE,否则FALSE。


A && B


boolean


类似于 A AND B.


A OR B


boolean


TRUE,如果A或B或两者都是TRUE,否则FALSE。


A || B


boolean


类似于 A OR B.


NOT A


boolean


TRUE,如果A是FALSE,否则FALSE。


!A


boolean


类似于 NOT A.


示例


下面的查询用于检索部门是TP并且工资超过40000卢比的员工详细信息。


hive>SELECT* FROM employee WHERESalary>40000&&Dept=TP;


成功执行查询后,能看到以下回应:


+------+--------------+-----------+---------------—--+-------+


| ID   | Name         | Salary    | Designation      | Dept  |


+------+--------------+-----------+------------------+-------+


|1201  | Gopal       | 45000     | Technical manager|TP    |


+------+--------------+-----------+------------------+-------+


复杂的运算符


这些运算符提供一个表达式来接入复杂类型的元素。


运算符


操作


描述


A[n]


A是一个数组,n是一个int


它返回数组A的第n个元素,第一个元素的索引0。


M[key]


M 是一个 Map<K, V>并 key 的类型为K


它返回对应于映射中关键字的值。


S.x


S 是一个结构


它返回S的s字段





1.10Hive内置函数
Hive支持以下内置函数:


返回类型


签名


描述


BIGINT


round(double a)


返回BIGINT最近的double值。


BIGINT


floor(double a)


返回最大BIGINT值等于或小于double。


BIGINT


ceil(double a)


它返回最小BIGINT值等于或大于double。


double


rand(), rand(int seed)


它返回一个随机数,从行改变到行。


string


concat(string A, string B,...)


它返回从A后串联B产生的字符串


string


substr(string A, int start)


它返回一个起始,从起始位置的子字符串,直到A.结束


string


substr(string A, int start, int length)


返回从给定长度的起始start位置开始的字符串。


string


upper(string A)


它返回从转换的所有字符为大写产生的字符串。





string


ucase(string A)


和上面的一样


string


lower(string A)


它返回转换B的所有字符为小写产生的字符串。


string


lcase(string A)


和上面的一样


string


trim(string A)


它返回字符串从A.两端修剪空格的结果


string


ltrim(string A)


它返回A从一开始修整空格产生的字符串(左手侧)


string


rtrim(string A)


rtrim(string A),它返回A从结束修整空格产生的字符串(右侧)


string


regexp_replace(string A, string B, string C)


它返回从替换所有子在B结果配合C.在Java正则表达式语法的字符串


int


size(Map<K.V>)


它返回在映射类型的元素的数量。


int


size(Array<T>)


它返回在数组类型元素的数量。


value of <type>


cast(<expr> as <type>)


它把表达式的结果expr<类型>如cast('1'作为BIGINT)代表整体转换为字符串'1'。如果转换不成功,返回的是NULL。


string


from_unixtime(int unixtime)


转换的秒数从Unix纪元(1970-01-0100:00:00 UTC)代表那一刻,在当前系统时区的时间戳字符的串格式:"1970-01-01 00:00:00"


string


to_date(string timestamp)


返回一个字符串时间戳的日期部分:to_date("1970-01-01 00:00:00") = "1970-01-01"


int


year(string date)


返回年份部分的日期或时间戳字符串:year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970


int


month(string date)


返回日期或时间戳记字符串月份部分:month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11


int


day(string date)


返回日期或时间戳记字符串当天部分:day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1


string


get_json_object(string json_string, string path)


提取从基于指定的JSON路径的JSON字符串JSON对象,并返回提取的JSON字符串的JSON对象。如果输入的JSON字符串无效,返回NULL。


示例;


round() 函数


hive>SELECT round(2.6)from temp;


成功执行的查询,能看到以下回应:


2.0


floor() 函数


hive>SELECT floor(2.6)from temp;


2.0





聚合函数


Hive支持以下内置聚合函数。这些函数的用法类似SQL聚合函数。


返回类型


签名


描述


BIGINT


count(*), count(expr),


count(*) - 返回检索行的总数。


DOUBLE


sum(col), sum(DISTINCT col)


返回该组或该组中的列的不同值的分组和所有元素的总和。


DOUBLE


avg(col), avg(DISTINCT col)


返回上述组或该组中的列的不同值的元素的平均值。


DOUBLE


min(col)


返回该组中的列的最小值。


DOUBLE


max(col)


返回该组中的列的最大值。





1.11Hive视图和索引
和SQL类似,可根据用户的需求创建视图,将任何结果集数据保存为一个视图。


创建一个视图


可以创建一个视图,在执行SELECT语句的时候。语法如下:


CREATE VIEW [IFNOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]


[COMMENTtable_comment]


AS SELECT ...


示例


设employee表拥有如下字段:Id, Name, Salary, Designation和 Dept。生成一个查询检索工资超过30000卢比的员工详细信息,我们把结果存储在一个名为视图 emp_30000.


+------+--------------+-----------+------------------+-------+


| ID   | Name         | Salary    | Designation      | Dept |


+------+--------------+-----------+------------------+-------+


|1201  | Gopal       | 45000     |Technical manager |TP    |


|1202  | Manisha     | 45000     | Proofreader      | PR   |


|1203  | Masthanvali | 40000     | Technical writer |TP    |


|1204  | Krian       | 40000     | Hr Admin         | HR   |


|1205  | Kranthi     | 30000     | Op Admin         | Admin


+------+--------------+-----------+------------------+-------+


下面使用上述业务情景查询检索员的工详细信息:


hive> CREATE VIEWemp_30000 AS


   > SELECT * FROM employee


   > WHERE salary>30000;


删除一个视图


语法:DROP VIEW view_name


创建索引


索引是一个表上的一个特定列的指针。创建索引意味着创建表上的一个特定列的指针。语法如下:


CREATE INDEXindex_name


ON TABLEbase_table_name (col_name, ...)


AS'index.handler.class.name'


[WITH DEFERREDREBUILD]


[IDXPROPERTIES(property_name=property_value, ...)]


[IN TABLEindex_table_name]


[PARTITIONED BY(col_name, ...)]


[


   [ ROW FORMAT ...] STORED AS ...


   | STORED BY ...





[LOCATIONhdfs_path]


[TBLPROPERTIES(...)]


示例,使用之前的字段 Id, Name, Salary, Designation,和 Dept创建一个名为index_salary的索引,对employee表的salary列索引。


下面的查询创建一个索引:


hive> CREATE INDEXinedx_salary ON TABLE employee(salary)


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


这是一个指向salary列。如果列被修改,变更使用的索引值存储。


删除索引


用来删除索引的语法:


DROP INDEX <index_name> ON<table_name>


下面的查询删除名为index_salary索引:


hive> DROP INDEXindex_salary ON employee;





1.12Hive QL SELECT WHERE子句
SELECT语句用来从表中检索的数据。 WHERE子句中的工作原理类似于一个条件。它使用这个条件过滤数据,并返回给出一个有限的结果。内置运算符和函数产生一个表达式,满足该条件并从表中select出来。


SELECT查询的语法;


查询检索薪水大于30000的员工信息;


hive> SELECT * FROMemployee WHERE salary>30000;





1.13Hive QL SELECT ORDER BY子句
ORDER BY子句用于检索基于一列的细节并设置排序结果按升序或降序排列。


ORDER BY子句的语法;


例如,有员工表;


+------+--------------+----------+------------------+--------+


| ID   | Name         | Salary   | Designation      | Dept  |


+------+--------------+----------+------------------+--------+


|1201  | Gopal       | 45000    |Technical manager |TP     |


|1202  | Manisha     | 45000    | Proofreader      | PR    |


|1203  | Masthanvali | 40000    | Technical writer |TP     |


|1204  | Krian       | 40000    | Hr Admin         | HR     |


|1205  | Kranthi     | 30000    | Op Admin         | Admin  |


+------+--------------+----------+------------------+--------+


hive> SELECTId,Name,Dept FROM employee ORDER BY DEPT;


得到查询结果


+------+--------------+----------+-----------------+--------+


| ID   | Name         | Salary   | Designation     | Dept  |


+------+--------------+----------+-----------------+--------+


|1205  | Kranthi     | 30000    | Op Admin        | Admin |


|1204  | Krian       | 40000    | Hr Admin        | HR    |


|1202  | Manisha     | 45000    | Proofreader     | PR    |


|1201  | Gopal       | 45000    |Technical manager|TP     |


|1203  | Masthanvali | 40000    | Technical writer|TP     |


+------+--------------+----------+-----------------+--------+





1.14Hive QL SELECT GROUP BY子句
GROUP BY子句用于分类所有记录结果的特定集合列,用来查询一组记录。


GROUP BY子句的语法:


同样的,上述员工表,


hive> SELECTDept,count(*) FROM employee GROUP BYDEPT;


查询以检索每个部门的员工数量。


+------+--------------+


| Dept |Count(*)     |


+------+--------------+


|Admin |    1        |


|PR    |   2         |


|TP    |   3         |


+------+--------------+





1.15Hive QL SELECT JOIN子句
JOIN子句通过共同值组合来自两个表的特定字段。它是从数据库中的两个或更多的表组合的记录。


语法;


join_table:





   table_reference JOIN table_factor[join_condition]


   | table_reference{LEFT|RIGHT|FULL}[OUTER] JOINtable_reference


   join_condition


   | table_referenceLEFT SEMI JOIN table_reference join_condition


   | table_referenceCROSS JOIN table_reference[join_condition]


示例,CUSTOMERS表


+----+----------+-----+-----------+----------+


| ID | NAME     | AGE | ADDRESS   | SALARY  |


+----+----------+-----+-----------+----------+


| 1  | Ramesh  | 32  | Ahmedabad | 2000.00  |


| 2  | Khilan  | 25  | Delhi     | 1500.00 |


| 3  | kaushik | 23  | Kota      | 2000.00 |


| 4  | Chaitali | 25  | Mumbai   | 6500.00  |


| 5  | Hardik   | 27  |Bhopal    | 8500.00  |


| 6  | Komal   | 22  | MP        | 4500.00  |


| 7  | Muffy   | 24  | Indore    | 10000.00 |


+----+----------+-----+-----------+----------+


另一个ORDERS表


+-----+---------------------+-------------+--------+


|OID  | DATE               | CUSTOMER_ID |AMOUNT |


+-----+---------------------+-------------+--------+


| 102 |2009-10-08 00:00:00 |           3 |3000   |


| 100 |2009-10-08 00:00:00 |           3 |1500   |


| 101 |2009-11-20 00:00:00 |           2 |1560   |


| 103 |2008-05-20 00:00:00 |           4 |2060   |


+-----+---------------------+-------------+--------+


其中有不同类型的联接;JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN。


JOIN:


JOIN子句用于合并和检索来自多个表中的记录。 JOIN和SQL OUTER JOIN类似。连接条件是使用主键和表的外键。


hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT


  > FROM CUSTOMERS c JOINORDERS o


  > ON(c.ID= o.CUSTOMER_ID);


查询结果;


+----+----------+-----+--------+


| ID | NAME     | AGE | AMOUNT |


+----+----------+-----+--------+


| 3  | kaushik | 23  | 3000   |


| 3  | kaushik | 23  | 1500   |


| 2  | Khilan  | 25  | 1560   |


| 4  | Chaitali | 25  | 2060  |


+----+----------+-----+--------+





LEFT OUTER JOIN


HiveQL LEFT OUTER JOIN返回所有行左表,即使是在正确的表中没有匹配。这意味着,如果ON子句匹配的右表0(零)记录,JOIN还是返回结果行,但在右表中的每一列为NULL。


例如以下LEFT OUTER JOIN用法


hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE


  > FROM CUSTOMERS c


  > LEFT OUTER JOIN ORDERS o


  > ON(c.ID= o.CUSTOMER_ID);


查询结果;


+----+----------+--------+---------------------+


| ID | NAME     | AMOUNT | DATE                |


+----+----------+--------+---------------------+


| 1  | Ramesh  | NULL   | NULL                |


| 2  | Khilan  | 1560   | 2009-11-20 00:00:00 |


| 3  | kaushik | 3000   | 2009-10-08 00:00:00 |


| 3  | kaushik | 1500   | 2009-10-08 00:00:00 |


| 4  | Chaitali | 2060   | 2008-05-20 00:00:00 |


| 5  | Hardik  | NULL   | NULL                |


| 6  | Komal   | NULL   | NULL                |


| 7  | Muffy   | NULL   | NULL                |


+----+----------+--------+---------------------+





RIGHT OUTER JOIN


HiveQL RIGHT OUTER JOIN返回右边表的所有行,即使有在左表中没有匹配。如果ON子句的左表匹配0(零)的记录,JOIN结果返回一行,但在左表中的每一列为NULL。


hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE


  > FROM CUSTOMERS c


  > RIGHT OUTER JOIN ORDERS o


  > ON(c.ID= o.CUSTOMER_ID);


查询结果:


+------+----------+--------+---------------------+


| ID   | NAME    | AMOUNT | DATE                |


+------+----------+--------+---------------------+


| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |


| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |


| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |


| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |


+------+----------+--------+---------------------+





FULL OUTER JOIN


HiveQL FULL OUTER JOIN结合了左边,并且满足JOIN条件合适外部表的记录。连接表包含两个表的所有记录,或两侧缺少匹配结果那么使用NULL值填补。


+------+----------+--------+---------------------+


| ID   | NAME    | AMOUNT | DATE                |


+------+----------+--------+---------------------+


| 1    | Ramesh  | NULL   | NULL                |


| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |


| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |


| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |


| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |


| 5    | Hardik  | NULL   | NULL                |


| 6    | Komal   | NULL   | NULL                |


| 7    | Muffy   | NULL   | NULL                |


| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |


| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |


| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |


| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |


+------+----------+--------+---------------------+


1.16从SQL到HiveQL应注意的点
1、Hive不支持等值连接


• SQL中对两表内联可以写成:


•select * from dual a,dual bwhere a.key = b.key;


•Hive中应为


•select * from dual a joindual b on a.key = b.key;




2、分号字符


•分号是SQL语句结束标记,在HiveQL中也是,但是在HiveQL中,对分号的识别没有那么智慧,例如:


•selectconcat(key,concat(';',key)) from dual;


•但HiveQL在解析语句时提示:


       FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) infunction specification


•解决的办法是,使用分号的八进制的ASCII码进行转义,那么上述语句应写成:


•selectconcat(key,concat('\073',key)) from dual;





3、IS[NOT] NULL


•SQL中null代表空值, 值得警惕的是,在HiveQL中String类型的字段若是空(empty)字符串,即长度为0,而对它进行ISNULL的判断结果是False.
————————————————
版权声明:本文为CSDN博主「圆觉_」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/sinat_28296297/article/details/77992752

楼主热帖
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /1 下一条

关于我们|小黑屋|Archiver|168大数据 ( 京ICP备14035423号|申请友情链接

GMT+8, 2024-3-29 23:27

Powered by BI168大数据社区

© 2012-2014 168大数据

快速回复 返回顶部 返回列表