Hadoop Hive sql语法详解.docx
《Hadoop Hive sql语法详解.docx》由会员分享，可在线阅读，更多相关《Hadoop Hive sql语法详解.docx（36页珍藏版）》请在冰豆网上搜索。
HadoopHivesql语法详解
HadoopHivesql语法详解
Hive是基于Hadoop构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop分布式文件系统中的数据，可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL去查询分析需要的内容，这套SQL简称HiveSQL，使不熟悉mapreduce的用户很方便的利用SQL语言查询，汇总，分析数据。
而mapreduce开发人员可以把己写的mapper和reducer作为插件来支持Hive做更复杂的数据分析。
它与关系型数据库的SQL略有不同，但支持了绝大多数的语句如DDL、DML以及常见的聚合函数、连接查询、条件查询。
HIVE不适合用于联机online）事务处理，也不提供实时查询功能。
它最适合应用在基于大量不可变数据的批处理作业。
HIVE的特点：
可伸缩（在Hadoop的集群上动态的添加设备），可扩展，容错，输入格式的松散耦合。
Hive的官方文档中对查询语言有了很详细的描述，请参考：
http:
//wiki.apache.org/hadoop/Hive/LanguageManual，本文的内容大部分翻译自该页面，期间加入了一些在使用过程中需要注意到的事项。
1.DDL操作
DDL
?
建表
?
删除表
?
修改表结构
?
创建／删除视图
?
创建数据库
?
显示命令
建表：
CREATE[EXTERNAL]TABLE[IFNOTEXISTS]table_name
[（col_namedata_type[COMMENTcol_comment],...）]
[COMMENTtable_comment]
[PARTITIONEDBY（col_namedata_type[COMMENTcol_comment],...）]
[CLUSTEREDBY（col_name,col_name,...）
[SORTEDBY（col_name[ASC|DESC],...）]INTOnum_bucketsBUCKETS]
[ROWFORMATrow_format]
[STOREDASfile_format]
[LOCATIONhdfs_path]
?
CREATETABLE创建一个指定名字的表。
如果相同名字的表已经存在，则抛出异常；用户可以用IFNOTEXIST选项来忽略这个异常
?
EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
?
LIKE允许用户复制现有的表结构，但是不复制数据
?
COMMENT可以为表与字段增加描述
?
ROWFORMAT
DELIMITED[FIELDSTERMINATEDBYchar][COLLECTIONITEMSTERMINATEDBYchar]
[MAPKEYSTERMINATEDBYchar][LINESTERMINATEDBYchar]
|SERDEserde_name[WITHSERDEPROPERTIES（property_name=property_value,property_name=property_value,...）]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。
如果没有指定ROWFORMAT或者ROWFORMATDELIMITED，将会使用自带的SerDe。
在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
?
STOREDAS
SEQUENCEFILE
|TEXTFILE
|RCFILE
|INPUTFORMATinput_format_classnameOUTPUTFORMAToutput_format_classname
如果文件数据是纯文本，可以使用STOREDASTEXTFILE。
如果数据需要压缩，使用STOREDASSEQUENCE。
创建简单表：
hive>CREATETABLEpokes（fooINT,barSTRING）;
创建外部表：
CREATEEXTERNALTABLEpage_view（viewTimeINT,useridBIGINT,
page_urlSTRING,referrer_urlSTRING,
ipSTRINGCOMMENT'IPAddressoftheUser',
countrySTRINGCOMMENT'countryoforigination'）
COMMENT'Thisisthestagingpageviewtable'
ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\054'
STOREDASTEXTFILE
LOCATION'';
建分区表
CREATETABLEpar_table（viewTimeINT,useridBIGINT,
page_urlSTRING,referrer_urlSTRING,
ipSTRINGCOMMENT'IPAddressoftheUser'）
COMMENT'Thisisthepageviewtable'
PARTITIONEDBY（dateSTRING,posSTRING）
ROWFORMATDELIMITED‘\t’
FIELDSTERMINATEDBY'\n'
STOREDASSEQUENCEFILE;
建Bucket表
CREATETABLEpar_table（viewTimeINT,useridBIGINT,
page_urlSTRING,referrer_urlSTRING,
ipSTRINGCOMMENT'IPAddressoftheUser'）
COMMENT'Thisisthepageviewtable'
PARTITIONEDBY（dateSTRING,posSTRING）
CLUSTEREDBY（userid）SORTEDBY（viewTime）INTO32BUCKETS
ROWFORMATDELIMITED‘\t’
FIELDSTERMINATEDBY'\n'
STOREDASSEQUENCEFILE;
创建表并创建索引字段ds
hive>CREATETABLEinvites（fooINT,barSTRING）PARTITIONEDBY（dsSTRING）;
复制一个空表
CREATETABLEempty_key_value_store
LIKEkey_value_store;
例子
createtableuser_info（user_idint,cidstring,ckidstring,usernamestring）
rowformatdelimited
fieldsterminatedby'\t'
linesterminatedby'\n';
导入数据表的数据格式是：
字段之间是tab键分割，行之间是断行。
及要我们的文件内容格式：
100636100890c5c86f4cddc15eb7yyyvybtvt
10061210086597cc70d411c18b6fgyvcycy
100078100087ecd6026a15ffddf5qa000100
显示所有表：
hive>SHOWTABLES;
按正条件（正则表达式）显示表，
hive>SHOWTABLES'.*s';
修改表结构
?
增加分区、删除分区
?
重命名表
?
修改列的名字、类型、位置、注释
?
增加/更新列
?
增加表的元数据信息
表添加一列：
hive>ALTERTABLEpokesADDCOLUMNS（new_colINT）;
添加一列并增加列字段注释
hive>ALTERTABLEinvitesADDCOLUMNS（new_col2INTCOMMENT'acomment'）;
更改表名：
hive>ALTERTABLEeventsRENAMETO3koobecaf;
删除列：
hive>DROPTABLEpokes;
增加、删除分区
?
增加
ALTERTABLEtable_nameADD[IFNOTEXISTS]partition_spec[LOCATION'location1']partition_spec[LOCATION'location2']...
partition_spec:
:
PARTITION（partition_col=partition_col_value,partition_col=partiton_col_value,...）
?
删除
ALTERTABLEtable_nameDROPpartition_spec,partition_spec,...
重命名表
?
ALTERTABLEtable_nameRENAMETOnew_table_name
修改列的名字、类型、位置、注释：
?
ALTERTABLEtable_nameCHANGE[COLUMN]col_old_namecol_new_namecolumn_type[COMMENTcol_comment][FIRST|AFTERcolumn_name]
?
这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合
表添加一列：
hive>ALTERTABLEpokesADDCOLUMNS（new_colINT）;
添加一列并增加列字段注释
hive>ALTERTABLEinvitesADDCOLUMNS（new_col2INTCOMMENT'acomment'）;
增加/更新列
?
ALTERTABLEtable_nameADD|REPLACECOLUMNS（col_namedata_type[COMMENTcol_comment],...）
?
ADD是代表新增一字段，字段位置在所有列后面（partition列前）
REPLACE则是表示替换表中所有字段。
增加表的元数据信息
?
ALTERTABLEtable_nameSETTBLPROPERTIEStable_propertiestable_properties:
:
[property_name=property_value…..]
?
用户可以用这个命令向表中增加metadata
改变表文件格式与组织
?
ALTERTABLEtable_nameSETFILEFORMATfile_format
?
ALTERTABLEtable_nameCLUSTEREDBY（userid）SORTEDBY（viewTime）INTOnum_bucketsBUCKETS
?
这个命令修改了表的物理存储属性
创建／删除视图
?
CREATEVIEW[IFNOTEXISTS]view_name[（column_name[COMMENTcolumn_comment],...）][COMMENTview_comment][TBLPROPERTIES（property_name=property_value,...）]ASSELECT
?
增加视图
?
如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成
?
如果修改基本表的属性，视图中不会体现，无效查询将会失败
?
视图是只读的，不能用LOAD/INSERT/ALTER
?
DROPVIEWview_name
?
删除视图
创建数据库
?
CREATEDATABASEname
显示命令
?
showtables;
?
showdatabases;
?
showpartitions;
?
showfunctions
?
describeextendedtable_namedotcol_name
2.DML操作:
元数据存储
hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。
数据是以load的方式加载到建立好的表中。
数据一旦导入就不可以修改。
DML包括：
INSERT插入、UPDATE更新、DELETE删除
?
向数据表内加载文件
?
将查询结果插入到Hive表中
?
0.8新特性insertinto
向数据表内加载文件
?
LOADDATA[LOCAL]INPATH'filepath'[OVERWRITE]INTOTABLEtablename[PARTITION（partcol1=val1,partcol2=val2...）]
?
Load操作只是单纯的复制/移动操作，将数据文件移动到Hive表对应的位置。
?
filepath
?
相对路径，例如：
project/data1
?
绝对路径，例如：
/user/hive/project/data1
?
包含模式的完整URI，例如：
hdfs:
//namenode:
9000/user/hive/project/data1
例如：
hive>LOADDATALOCALINPATH'./examples/files/kv1.txt'OVERWRITEINTOTABLEpokes;
加载本地数据，同时给定分区信息
?
加载的目标可以是一个表或者分区。
如果表包含分区，必须指定每一个分区的分区名
?
filepath可以引用一个文件（这种情况下，Hive会将文件移动到表所对应的目录中）或者是一个目录（在这种情况下，Hive会将目录中的所有文件移动至表所对应的目录中）
LOCAL关键字
?
指定了LOCAL，即本地
?
load命令会去查找本地文件系统中的filepath。
如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。
用户也可以为本地文件指定一个完整的URI，比如：
file:
///user/hive/project/data1.
?
load命令会将filepath中的文件复制到目标文件系统中。
目标文件系统由表的位置属性决定。
被复制的数据文件移动到表的数据对应的位置
例如：
加载本地数据，同时给定分区信息：
hive>LOADDATALOCALINPATH'./examples/files/kv2.txt'OVERWRITEINTOTABLEinvitesPARTITION（ds='2008-08-15'）;
?
没有指定LOCAL
如果filepath指向的是一个完整的URI，hive会直接使用这个URI。
否则
?
如果没有指定schema或者authority，Hive会使用在hadoop配置文件中定义的schema和authority，fs.default.name指定了Namenode的URI
?
如果路径不是绝对的，Hive相对于/user/进行解释。
Hive会将filepath中指定的文件内容移动到table（或者partition）所指定的路径中
加载DFS数据，同时给定分区信息：
hive>LOADDATAINPATH'/user/myname/kv2.txt'OVERWRITEINTOTABLEinvitesPARTITION（ds='2008-08-15'）;
TheabovecommandwillloaddatafromanHDFSfile/directorytothetable.NotethatloadingdatafromHDFSwillresultinmovingthefile/directory.Asaresult,theoperationisalmostinstantaneous.
OVERWRITE
?
指定了OVERWRITE
?
目标表（或者分区）中的内容（如果有）会被删除，然后再将filepath指向的文件/目录中的内容添加到表/分区中。
?
如果目标表（分区）已经有一个文件，并且文件名和filepath中的文件名冲突，那么现有的文件会被新文件所替代。
将查询结果插入Hive表
?
将查询结果插入Hive表
?
将查询结果写入HDFS文件系统
?
基本模式
INSERTOVERWRITETABLEtablename1[PARTITION（partcol1=val1,partcol2=val2...）]select_statement1FROMfrom_statement
?
多插入模式
FROMfrom_statement
INSERTOVERWRITETABLEtablename1[PARTITION（partcol1=val1,partcol2=val2...）]select_statement1
[INSERTOVERWRITETABLEtablename2[PARTITION...]select_statement2]...
?
自动分区模式
INSERTOVERWRITETABLEtablenamePARTITION（partcol1[=val1],partcol2[=val2]...）select_statementFROMfrom_statement
将查询结果写入HDFS文件系统
?
INSERTOVERWRITE[LOCAL]DIRECTORYdirectory1SELECT...FROM...
FROMfrom_statement
INSERTOVERWRITE[LOCAL]DIRECTORYdirectory1select_statement1
[INSERTOVERWRITE[LOCAL]DIRECTORYdirectory2select_statement2]
?
?
数据写入文件系统时进行文本序列化，且每列用^A来区分，\n换行
INSERTINTO
?
INSERTINTOTABLEtablename1[PARTITION（partcol1=val1,partcol2=val2...）]select_statement1FROMfrom_statement
3.DQL操作:
数据查询SQL
SQL操作
?
基本的Select操作
?
基于Partition的查询
?
Join
3.1基本的Select操作
SELECT[ALL|DISTINCT]select_expr,select_expr,...
FROMtable_reference
[WHEREwhere_condition]
[GROUPBYcol_list[HAVINGcondition]]
[CLUSTERBYcol_list
|[DISTRIBUTEBYcol_list][SORTBY|ORDERBYcol_list]
]
[LIMITnumber]
?
使用ALL和DISTINCT选项区分对重复记录的处理。
默认是ALL，表示查询所有记录。
DISTINCT表示去掉重复的记录
?
?
Where条件
?
类似我们传统SQL的where条件
?
目前支持AND,OR,0.9版本支持between
?
IN,NOTIN
?
不支持EXIST,NOTEXIST
ORDERBY与SORTBY的不同
?
ORDERBY全局排序，只有一个Reduce任务
?
SORTBY只在本机做排序
Limit
?
Limit可以限制查询的记录数
SELECT*FROMt1LIMIT5
?
实现Topk查询
?
下面的查询语句查询销售记录最大的5个销售代表。
SETmapred.reduce.tasks=1
SELECT*FROMtestSORTBYamountDESCLIMIT5
?
REGEXColumnSpecification
SELECT语句可以使用正则表达式做列选择，下面的语句查询除了ds和hr之外的所有列：
SELECT`（ds|hr）?
+.+`FROMtest
例如
按先件查询
hive>SELECTa.fooFROMinvitesaWHEREa.ds='';
将查询数据输出至目录：
hive>INSERTOVERWRITEDIRECTORY'/tmp/hdfs_out'SELECTa.*FROMinvitesaWHEREa.ds='';
将查询结果输出至本地目录：
hive>INSERTOVERWRITELOCALDIRECTORY'/tmp/local_out'SELECTa.*FROMpokesa;
选择所有列到本地目录：
hive>INSERTOVERWRITETABLEeventsSELECTa.*FROMprofilesa;
hive>INSERTOVERWRITETABLEeventsSELECTa.*FROMprofilesaWHEREa.key<100;
hive>INSERTOVERWRITELOCALDIRECTORY'/tmp/reg_3'SELECTa.*FROMeventsa;
hive>INSERTOVERWRITEDIRECTORY'/tmp/reg_4'selecta.invites,a.pokesFROMprofilesa;
hive>INSERTOVERWRITEDIRECTORY'/tmp/reg_5'SELECTCOUNT
（1）FROMinvitesaWHEREa.ds='';
hive>INSERTOVERWRITEDIRECTORY'/tmp/reg_5'SELECTa.foo,a.barFROMinvitesa;
hive>INSERTOVERWRITELOCALDIRECTORY'/tmp/sum'SELECTSUM（a.pc）FROMpc1a;
将一个表的统计结果插入另一个表中：
hive>FROMinvitesaINSERTOVERWRITETABLEeventsSELECTa.bar,count
（1）WHEREa.foo>0GROUPBYa.bar;
hive>INSER
升级会员 