Hive数据仓库之快速入门

2017-11-29 15:53:43来源:oschina作者:光明辉煌人点击

分享
第七城市th7cn

Hive定位:ETL(数据仓库)工具 将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的工具,如像:kettle有关Hive数据导入导出mysql的问题请查看《如何利用sqoop将hive数据导入导出数据到mysql》

DML

批量插入/批量导入 LOADDATA[LOCAL] INPATH 'filepath'[OVERWRITE]INTOTABLEtablename [PARTITION(partcol1=val1, partcol2=val2 ...)] 注:filepath可以是hdfs路径或者是S3路径,如hdfs://namenode:9000/user/hive/project/data1 1.从本地文件导入到表 loaddatalocalinpath 'test.txt'intotabletest; 2.从hdfs导入到表 loaddatainpath '/home/test/add.txt'intotabletest; 3.从表查询中导入到表 insertintotabletestselectid,name, telfromtest; 4.将查询数据导入到多个表 from source_table insertintotabletestselectid,name, telfromdest1_tableselectsrc.*wheresrc.id< 100 insertintotabletestselectid,name, telfromdest2_tableselectsrc.*wheresrc.id< 100 insertintotabletestselectid,name, telfromdest3_tableselectsrc.*wheresrc.id< 100; 5.建表时导入 createtabletest4asselectid,name, telfromtest; 指定分隔符导出数据 insertoverwritelocaldirectory'/home/hadoop/export_hive' rowformatdelimited fieldsterminatedby'/t' select*fromtest; 删除/清空 1.删除table1中不符合条件的数据 insertoverwritetabletable1 select*fromtable1whereXXXX; 2.清空表 insertoverwritetablet_table1 select*fromt_table1where1=0; 3.截断表(注:不能截断外部表) truncatetabletable_name; 4.删除hdfs对应的表数据达到清空表(表结构依然存在) hdfs dfs -rmr /user/hive/warehouse/test注:1和2本质是覆写表来实现清除数据 delete 与 update 在hive中默认不支持事务,因此默认不支持delete与update,如果需要支持必须在hive-site.xml中配置打开

DDL

库/表/索引/视图/分区/分桶


数据库

列出/创建/修改/删除/查看信息 1.列出所有数据库 showdatabases; 2.创建数据库 createdatabasetest; 3.删除 dropdatabasetest;处于安全原因,直接drop有数据的数据库会报错,此时需要cascade关键字忽略报错删除 dropdatabaseifexiststestcascade; 4.查看数据库信息 describedatabasetest;


列出/创建/修改/删除/查看信息 1.列出所有表当前数据库的所有表 showtables;指定数据库的所有表 showtablesindb_name;支持正则 showtables'.*s'; 2.创建表 createtabletest (idint, astring ) ROWFORMATDELIMITED行分割 FIELDSTERMINATEDBY‘,’ 字段分隔符 LINESTERMINATEDBY‘/n’ 行分隔符 STOREDASTEXTFILE;作为文本存储 创建基于正则切分行字段的表 add jar ../build/contrib/hive_contrib.jar;CREATETABLEapachelog ( hostSTRING, identitySTRING, userSTRING, timeSTRING, requestSTRING, statusSTRING, sizeSTRING, refererSTRING, agentSTRING) ROWFORMATSERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' WITHSERDEPROPERTIES ( "input.regex"= "([^ ]*) ([^ ]*) ([^ ]*) (-|//[[^//]]*//]) ([^ /"]*|/"[^/"]*/") (-|[0-9]*) (-|[0-9]*)(?: ([^ /"]*|/"[^/"]*/") ([^ /"]*|/"[^/"]*/"))?", "output.format.string"= "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s" ) STOREDASTEXTFILE; 3.修改 加一个新列 ALTERTABLEtestADDCOLUMNS(new_col2 INTCOMMENT'a comment');改表名 ALTERTABLEold_nameRENAMETOnew_name; 4.删除 droptabletest; 5.查看信息显示列信息 desctest;显示详细表信息 descformattedtest;

索引

1.创建索引 CREATEINDEXindex_nameONTABLEbase_table_name (col_name, ...)AS'index.handler.class.name'如:DROPINDEXindex_nameONtable_name 2.重建索引 ALTERINDEXindex_nameONtable_name [PARTITION(...)]REBUILD如:alterindexindex1_index_testonindex_testrebuild;3.删除索引 DROPINDEXindex_nameONtable_name 4.列出索引 showindexonindex_test;

视图

CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT注:hive只支持逻辑视图,不支持物化视图 •增加视图 •如果没有提供表名,视图列的名字将由定义的SELECT表达式自动生成 •如果修改基本表的属性,视图中不会体现,无效查询将会失败 •视图是只读的,不能用LOAD/INSERT/ALTER •删除视图 DROP VIEW view_name


分区(重点)

列出/创建/修改/删除 1.列出一个表的所有分区 showpartitionstest; 2.创建分区表 createtabletest (idint, astring, ) partitionedby(bstring,cint) ROWFORMATDELIMITED FIELDSTERMINATEDBY‘,’ LINESTERMINATEDBY‘/n’ STOREDASTEXTFILE; 3.对现有表添加分区 ALTERTABLEtestADDIFNOTEXISTS PARTITION(year= 2017) LOCATION ‘/hiveuser/hive/warehouse/data_zh.db/data_zh/2017.txt’; 4.删除分区 ALTERTABLEtestDROPIFEXISTSPARTITION(year=2017); 5.加载数据到分区表 LOADDATAINPATH ‘/data/2017.txt’INTOTABLEtestPARTITION(year=2017); 6.未分区表数据导入分区表 insertoverwritetablepart_tablepartition(YEAR,MONTH)select*fromno_part_table; 7.动态分区指令 sethive.exec.dynamic.partition=true; sethive.exec.dynamic.partition.mode=nonstrict; #sethive.enforce.bucketing = true;开启动态分区后导入数据时可以省略指定分区的步骤 LOADDATAINPATH ‘/data/2017.txt’INTOTABLEtestPARTITION(year);

分桶

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


把表(或者分区)组织成桶(Bucket)有两个理由: (1)获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量。 (2)使取样(sampling)更高效。在处理大规模数据集时,在开发和修改查询的阶段,如果能在数据集的一小部分数据上试运行查询,会带来很多方便。

第七城市th7cn

微信扫一扫

第七城市微信公众平台