Hadoop Hive sql语法详解文档格式.docx

Hadoop Hive sql语法详解文档格式.docx

《Hadoop Hive sql语法详解文档格式.docx》由会员分享，可在线阅读，更多相关《Hadoop Hive sql语法详解文档格式.docx（36页珍藏版）》请在冰豆网上搜索。

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

<

hdfs_location>

'

;

建分区表

CREATETABLEpar_table（viewTimeINT,useridBIGINT,

Thisisthepageviewtable'

PARTITIONEDBY（dateSTRING,posSTRING）

ROWFORMATDELIMITED‘\t’

FIELDSTERMINATEDBY'

\n'

STOREDASSEQUENCEFILE;

建Bucket表

CLUSTEREDBY（userid）SORTEDBY（viewTime）INTO32BUCKETS

创建表并创建索引字段ds

CREATETABLEinvites（fooINT,barSTRING）PARTITIONEDBY（dsSTRING）;

复制一个空表

CREATETABLEempty_key_value_store

LIKEkey_value_store;

例子

createtableuser_info（user_idint,cidstring,ckidstring,usernamestring）

rowformatdelimited

fieldsterminatedby'

\t'

linesterminatedby'

导入数据表的数据格式是：

字段之间是tab键分割，行之间是断行。

及要我们的文件内容格式：

100636100890c5c86f4cddc15eb7yyyvybtvt

10061210086597cc70d411c18b6fgyvcycy

100078100087ecd6026a15ffddf5qa000100

显示所有表：

SHOWTABLES;

按正条件（正则表达式）显示表，

SHOWTABLES'

.*s'

增加分区、删除分区

重命名表

修改列的名字、类型、位置、注释

增加/更新列

增加表的元数据信息

表添加一列：

ALTERTABLEpokesADDCOLUMNS（new_colINT）;

添加一列并增加列字段注释

ALTERTABLEinvitesADDCOLUMNS（new_col2INTCOMMENT'

acomment'

）;

更改表名：

ALTERTABLEeventsRENAMETO3koobecaf;

删除列：

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]

这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合

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

例如：

LOADDATALOCALINPATH'

./examples/files/kv1.txt'

OVERWRITEINTOTABLEpokes;

加载本地数据，同时给定分区信息

加载的目标可以是一个表或者分区。

如果表包含分区，必须指定每一个分区的分区名

filepath可以引用一个文件（这种情况下，Hive会将文件移动到表所对应的目录中）或者是一个目录（在这种情况下，Hive会将目录中的所有文件移动至表所对应的目录中）

LOCAL关键字

指定了LOCAL，即本地

load命令会去查找本地文件系统中的filepath。

如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。

用户也可以为本地文件指定一个完整的URI，比如：

file:

///user/hive/project/data1.

load命令会将filepath中的文件复制到目标文件系统中。

目标文件系统由表的位置属性决定。

被复制的数据文件移动到表的数据对应的位置

加载本地数据，同时给定分区信息：

./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数据，同时给定分区信息：

LOADDATAINPATH'

/user/myname/kv2.txt'

TheabovecommandwillloaddatafromanHDFSfile/directorytothetable.NotethatloadingdatafromHDFSwillresultinmovingthefile/directory.Asaresult,theoperationisalmostinstantaneous.

OVERWRITE

指定了OVERWRITE

目标表（或者分区）中的内容（如果有）会被删除，然后再将filepath指向的文件/目录中的内容添加到表/分区中。

如果目标表（分区）已经有一个文件，并且文件名和filepath中的文件名冲突，那么现有的文件会被新文件所替代。

将查询结果插入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

INSERTOVERWRITE[LOCAL]DIRECTORYdirectory1SELECT...FROM...

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

例如

按先件查询

SELECTa.fooFROMinvitesaWHEREa.ds='

DATE>

将查询数据输出至目录：

INSERTOVERWRITEDIRECTORY'

/tmp/hdfs_out'

SELECTa.*FROMinvitesaWHEREa.ds='

将查询结果输出至本地目录：

INSERTOVERWRITELOCALDIRECTORY'

/tmp/local_out'

SELECTa.*FROMpokesa;

选择所有列到本地目录：

INSERTOVERWRITETABLEeventsSELECTa.*FROMprofilesa;

INSERTOVERWRITETABLEeventsSELECTa.*FROMprofilesaWHEREa.key<

100;

/tmp/reg_3'

SELECTa.*FROMeventsa;

/tmp/reg_4'

selecta.invites,a.pokesFROMprofilesa;

/tmp/reg_5'

SELECTCOUNT

（1）FROMinvitesaWHEREa.ds='

SELECTa.foo,a.barFROMinvitesa;

/tmp/sum'

SELECTSUM（a.pc）FROMpc1a;

将一个表的统计结果插入另一个表中：

FROMinvitesaINSERTOVERWRITETABLEeventsSELECTa.bar,count

（1）WHEREa.foo>

0GROUPBYa.bar;

INSER