头歌实践教学平台：大数据存储2023（八）

八、Hive 表 DML 操作第1关将文件中的数据导入Load到 Hive 表中任务描述本关任务将文档中的数据导入到数据库的表中。相关知识之前系列实训中我们接触过导入本地文件到Hive表中本关就进行导入的详细讲解。为了完成本关任务你需要掌握1.导入命令语法2.如何将本地txt文件导入到分区表中。导入命令语法Load操作执行copy/move命令把数据文件copy/move到Hive表位于 HDFS上的目录位置并不会对数据内容执行格式检查或格式转换操作。Load命令语法为LOAD DATA [LOCAL] INPATH filepath [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1vall,partcol2val2 …)];文件路径filepath可以是指向HDFS的相对路径或是绝对路径也可以是指向本地文件系统Linux文件系统相对路径当前工作目录或绝对路径。若filepath指向HDFSLOAD执行的是move操作即执行LOAD后filepath中的文件不再存在若filepath指向本地文件系统LOAD执行的是copy操作即执行LOAD后filepath中的文件仍然存在但需要指定LOCAL关键字。若filepath指向一个文件LOAD会copy或move相应的文件到表tablename若filepath指向一个目录LOAD会copy或move相应目录下的所有文件到表tablename。若创建表时指定了分区列使用 LOAD 命令加载数据时也要为所有分区列指定特定值。针对LOAD语句中指明LOCAL关键字INPATH参数可以使用下述方式确定Hive 会在本地文件系统中查找filepath用户可以设置filepath为文件绝对路径如file:///user/hive/data针对LOAD语句中未指明LOCAL关键字INPATH参数可以使用下述方式确定若filepath为相对路径Hive会解析成为/user/username/filepathfilepath未指定模式或文件系统类型如hdfs://namenode:9000/Hive会把${fs.default.name}值作为Namenode URL若语句带OVERWRITE关键字目标表或分区中的原始数据会被删除替换成新数据若未指定OVERWRITE关键字新数据会以追加的方式被添加到表中。若表或分区中的任何一个文件与filepath中的任何一个文件同名则表或分区中的同名文件会被filepath中的同名文件替换。将本地txt文件导入到分区表中例子创建数据库shoppingCREATE DATABASE IF NOT EXISTS shoppingLOCATION /hive/shopping;假设在数据库shopping中有分区表items_info2CREATE TABLE IF NOT EXISTS shopping.items_info2(category STRING COMMENT item category,name STRING COMMENT item name,price FLOAT COMMENT item price,brand STRING COMMENT item brand,type STRING COMMENT item type,stock INT COMMENT item stock,address STRUCT city:STRING, country:STRING, zip:INT COMMENT item sales address)COMMENT goods information tablePARTITIONED BY (p_category STRING,p_brand STRING) //设置分区ROW FORMAT DELIMITEDFIELDS TERMINATED BY ,COLLECTION ITEMS TERMINATED BY -TBLPROPERTIES (creatorXiaoming,date2019-01-01);如假设本地文件/home/shoppings.txt内容为字段间分隔符,根据表中设置FIELDS TERMINATED BY ,确定的。如果表中设置FIELDS TERMINATED BY \t那么字段间就应该用Tab键间隔开集合分隔符-根据表中设置COLLECTION ITEMS TERMINATED BY -确定的。如果表中设置COLLECTION ITEMS TERMINATED BY ,那么字段间就应该用逗号,键间隔开使用LOAD命令加载本地文件数据到items_info2表相应的分区中PARTITION关键字指定内容load data local inpath /home/shoppings.txt overwrite into table items_info2partition (p_categoryshoes,p_brandplayboy);执行LOAD命令后Hive会在 HDFS 的/hive/shopping/items2/路径下创建目录p_categoryshoes/p_brandplayboy/并且会把items_info.txt文件复制到上述创建的目录下编程要求student表结构INFO TYPESno INTname STRINGage INTsex STRINGscore STRUCT Chinese:FLOAT,Math:FLOAT,English:FLOAT本地文件/home/student.txt的内容为创建数据库test1切换到test1数据库在test1中创建相应格式的表student未分区表结构如上所示分隔符根据/home/student.txt的内容设置将/home/student.txt的数据导入到表student中。按照以上要求填写命令。每个要求对应一条命令共4条命令以;隔开。请勿删除代码框架由于hive启动时间较长测评时请耐心等待大概需要时间60s左右。测试说明平台会对你编写的命令进行测试若操作成功会显示如下信息导入数据后student表中的数据为1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}2 Xiaoliang 17 male {chinese:95.0,math:88.0,english:93.5}3 Xiaoming 19 male {chinese:86.5,math:98.0,english:91.0}4 Xiaoguang 18 male {chinese:88.0,math:80.0,english:94.0}5 Xiaohua 16 female {chinese:97.0,math:58.5,english:88.0}/hive/test1 下的目录结构为/hive/test1/student/hive/test1/student/student.txt开始你的任务吧祝你成功/********* Begin *********//*创建数据库*/CREATE DATABASE IF NOT EXISTS test1LOCATION /hive/test1; /*指定数据库位置*/USE test1;/*建表*/CREATE TABLE IF NOT EXISTS test1.student(Sno INT COMMENT student sno,name STRING COMMENT student name ,age INT COMMENT student age,sex STRING COMMENT student sex,score STRUCT chinese:FLOAT,math:FLOAT,english:FLOAT COMMENT student score)COMMENT students information tabeROW FORMAT DELIMITEDFIELDS TERMINATED BY ,COLLECTION ITEMS TERMINATED BY -;/*插入数据*/load data local inpath /home/student.txt overwrite into table student;/********* End *********/select * from student;第2关Select 操作任务描述本关任务按照编程要求执行相应的select操作。相关知识为了完成本关任务你需要掌握1. select语法格式2. 常用的select语法。select 语法格式Hive select操作的语法与SQL-92规范几乎没有区别其格式语法为SELECT [ALL | DISTINCT] select_expr,select_expr,… FROM table_reference[WHERE where_condition] [GROUP BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]] [LIMIT number]select 与各种属性的组合简单的select查询操作如下面的查询操作返回students表中所有的行和列hive select * from students;带WHERE子句的select条件查询操作返回满足WHERE指定条件的行。如下面的查询操作返回用户信息表users中的年龄大于10岁且国籍为中国的所有用户hive select * from users where age 10 and state China;带ALL和DISTINCT关键字的查询操作作用于确定是否返回重复的行默认为ALL即select查询返回重复的行hive select coll,coll2 from t1;1 31 31 42 5hive select distinct coll,coll2 from t1;1 31 42 5hive select distinct coll from t1;12带HAVING关键字的查询操作用于代替复杂的子查询操作 如查询操作hive select coll from (select coll,sum(col2) as col2sum from t1 group by coll) t2 where t2.col2sum 10;可以替换为hive select coll from t1 group by col1 having sum(col2) 10;带LIMIT关键字的查询操作用于返回指定数目的满足条件的行常用于返回Top k 问题。返回满足条件的5条记录返回结果为从满足条件的记录中随机选取5条。select * from t1 limit 5;Top k问题返回满足条件的列按col1降序排列的前5条记录select * from t1 sort by col1 desc limit 5;编程要求test2数据库中student表结构为INFO TYPE COMMENTSno INT student snoname STRING student nameage INT student agesex STRING student sexscore STRUCT Chinese:FLOAT,Math:FLOAT,English:FLOAT student score表中的数据为切换到test2数据库查询student表中所有的行和列查询年龄age 17的女生female查询语文成绩Chinese 90的记录从student表中查询前3条记录返回按年龄降序的前2条记录。按照以上要求填写命令。每个要求对应一条命令共6条命令以;隔开。由于hive启动时间较长测评时请耐心等待大概需要时间1-2分钟。测试说明平台会对你编写的命令进行测试若操作成功会显示如下信息1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}2 Xiaoliang 17 male {chinese:95.0,math:88.0,english:93.5}3 Xiaoming 19 male {chinese:86.5,math:98.0,english:91.0}4 Xiaoguang 18 male {chinese:88.0,math:80.0,english:94.0}5 Xiaohua 16 female {chinese:97.0,math:58.5,english:88.0}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}2 Xiaoliang 17 male {chinese:95.0,math:88.0,english:93.5}5 Xiaohua 16 female {chinese:97.0,math:58.5,english:88.0}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}2 Xiaoliang 17 male {chinese:95.0,math:88.0,english:93.5}3 Xiaoming 19 male {chinese:86.5,math:98.0,english:91.0}3 Xiaoming 19 male {chinese:86.5,math:98.0,english:91.0}4 Xiaoguang 18 male {chinese:88.0,math:80.0,english:94.0}说明1-5行返回的是查询student表中所有的行和列的结果6行返回的是查询年龄age 17的女生female的结果7-9行返回的是查询语文成绩Chinese 90的记录的结果10-12行返回的是从student表中查询前3条记录的结果12-13行返回的是返回按年龄降序的前2条记录的结果开始你的任务吧祝你成功--Beginuse test2;--查询student表中所有的行和列select * from student;--查询年龄age 17的女生femaleselect * from student where age17 and sexfemale;--查询语文成绩Chinese 90的记录select * from student where score.chinese90;--从student表中查询前3条记录select * from student limit 3;--返回按年龄降序的前2条记录select * from student sort by age desc limit 2;--End第3关将 select 查询结果插入 hive 表中任务描述本关任务根据编程要求将select查询结果插入hive表中。相关知识为了完成本关任务你需要掌握1. 单表插入2. 多表插入。通过使用查询子句从其他表中获得查询结果然后使用INSERT命令把数据插入到Hive新表中Hive会根据MapReduce中的reduce任务个数在HDFS上的hive新表目录下创建相应的数据文件000000_0若有多个reduce任务依次以000001_0、000002_0、…… 类推。该操作包括表单插入一次性向一个hive表插入数据和多表插入一次性向多个hive表插入数据。INSERT命令可以操作在表和特定的分区上如果属于分区表必须指明所有分区列和其对应的分区列属性值。单表插入单表插入语法INSERT OVERWRITE TABLE tablename [PARTITION (partcol1val1,partcol2val2,……) [IF NOT EXISTS]] SELECT select_statement FROM from_statement;该方法会 覆盖 表或分区中的数据若对特定分区指定IF NOT EXISTS将不执行覆盖操作。如查询items_info表把查询结果放到items_info2表中hive insert overwrite table items_info2 partition(p_categoryclothes,p_brandplayboy) select * from items_info ii where ii.categroy单表插入语法 追加 方式INSERT INTO TABLE tablename [PARTITION (partcol1val1,partcol2val2,……) ] SELECT select_statement FROM from_statement;该方法以追加的方式把SELECT子句返回的结果添加到表或分区中。多表插入FROM from_statementINSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1val1,partcol2val2…) [IF NOT EXISTS]] SELECT select_statement1[INSERT OVERWRITE TABLE tablename2 [PARTITION … [IF NOT EXISTS]] SELECT select_statement2][INSERT INTO TABLE tablename2 [PARTITION … ] SELECT select_statement2]…;多表插入操作的开始第一条命令指定所有表执行的SELECT命令所对应的FROM 子句针对同一个表既可以执行INSERT OVERWRITE操作也可以执行 INSERT INTO操作如表tablename2。多表插入操作可以降低源表的扫描次数Hive可以通过仅扫描一次数据源表然后针对不同的Hive表应用不同的查询规则从扫描结果中获取目标数据插入到不同的Hive表中。如把从items_info中扫描的结果根据不同的查询规则插入到表的不同分区中hive FROM items_info ii INSERT INTO TABLE items_info2 PARTITION (p_categoryclothes,p_brandplayboy) SELECT * WHERE ii.categoryclothes AND ii.brandplayboy INSERT OVERWRITE TABLE items_info2 PARTITION (p_categoryshoes,p_brandplayboy) SELECT * WHERE ii.categoryshoes AND ii.brandplayboy编程要求在test3数据库中有student表表中数据如下Sno name age sex score(Chinese-Math-English)001 Xiaohong 18 female 96-88-90.5002 Xiaoliang 17 male 95-88-93.5003 Xiaoming 19 male 86.5-98-91004 Xiaoguang 18 male 88-80-94005 Xiaohua 16 female 97-58.5-88复制student表两份分别名为student2、student3只复制表结构不复制数据可参考Hive表DDL操作一第二关以覆盖插入的方式把student表中前两条数据插入到student2中以追加插入的方式把student表中前两条数据插入到student2中以覆盖插入的方式把student表中年龄大于17岁的数据插入到student2、student3中以追加插入的方式把student表中的男生数据插入到student2以覆盖插入的方式把女生数据插入到student3中注意由于hive启动时间较长测评时请耐心等待大概需要时间1分钟左右。测试说明平台会对你编写的命令进行测试若操作成功会显示如下信息2 Xiaoliang 17 male {chinese:95.0,math:88.0,english:93.5}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}2 Xiaoliang 17 male {chinese:95.0,math:88.0,english:93.5}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}2 Xiaoliang 17 male {chinese:95.0,math:88.0,english:93.5}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}3 Xiaoming 19 male {chinese:86.5,math:98.0,english:91.0}4 Xiaoguang 18 male {chinese:88.0,math:80.0,english:94.0}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}3 Xiaoming 19 male {chinese:86.5,math:98.0,english:91.0}4 Xiaoguang 18 male {chinese:88.0,math:80.0,english:94.0}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}3 Xiaoming 19 male {chinese:86.5,math:98.0,english:91.0}4 Xiaoguang 18 male {chinese:88.0,math:80.0,english:94.0}2 Xiaoliang 17 male {chinese:95.0,math:88.0,english:93.5}3 Xiaoming 19 male {chinese:86.5,math:98.0,english:91.0}4 Xiaoguang 18 male {chinese:88.0,math:80.0,english:94.0}1 Xiaohong 18 female {chinese:96.0,math:88.0,english:90.5}5 Xiaohua 16 female {chinese:97.0,math:58.5,english:88.0}说明1-2行返回的是以覆盖插入的方式把student表中前两条数据插入到student2中的结果3-6行返回的是以追加插入的方式把student表中前两条数据插入到student2中的结果7-12行返回的是以覆盖插入的方式把student表中年龄大于17岁的数据插入到student2、student3中的结果13-20行返回的是以追加插入的方式把student表中的男生数据插入到student2以覆盖插入的方式把女生数据插入到student3中的结果开始你的任务吧祝你成功。--Begin--使用test3数据库use test3;--复制student表两份分别名为student2、student3CREATE TABLE IF NOT EXISTS student2LIKE student;CREATE TABLE IF NOT EXISTS student3LIKE student;--以覆盖插入的方式把student表中前两条数据插入到student2中insert overwrite table student2select * from student limit 2;--评测代码勿删select * from student2;--以追加插入的方式把student表中前两条数据插入到student2中insert into table student2select * from student limit 2;--评测代码勿删select * from student2;--以覆盖插入的方式把student表中年龄大于17岁的数据插入到student2、student3中from student iiinsert overwrite table student2select * where ii.age17insert overwrite table student3select * where ii.age17;--评测代码勿删select * from student2;select * from student3;--以追加插入的方式把student表中的男生数据插入到student2以覆盖插入的方式把女生数据插入到student3中from student iiinsert into table student2select * where ii.sexmaleinsert overwrite table student3select * where ii.sexfemale;--评测代码勿删select * from student2;select * from student3;--End第4关将 select 查询结果写入文件任务描述本关任务根据编程要求将select查询结果写入文件。相关知识为了完成本关任务你需要掌握1.单文件写入2.多文件写入。可以把Hive查询结果写入或导出到文件中与查询结果插入到表中类似导出 Hive表中的数据到文件也有两种方法分别是单文件写入和多文件写入。单文件写入INSERT OVERWRITE [LOCAL] DIRECTORY directory[ROW FORMAT row_format] [STORED AS file_format]SELECT select_statement FROM from_statements;若指定LOCAL关键字查询结果写入本地文件系统中OS 文件系统否则查询结果写入到分布式文件系统中HDFS。row_format:DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char][MAP KEYS TERMINATED BY char] [NULL DEFINED AS char]row_format个属性说明参见 Hive DDL操作(一) 创建表部分。如将shopping表中数据写入到/home/example目录下insert overwrite local directory /home/exampleselect * from shopping;会在/home/example目录下生成000000_0文件。多文件写入FROM from_statementINSERT OVERWRITE [LOCAL] DIRECTORY directory1SELECT select_statement1[INSERT OVERWRITE [LOCAL] DIRECTORY directory2SELECT select_statement2];编程要求在test4数据库中有student表表中数据如下Sno name age sex score(Chinese-Math-English)001 Xiaohong 18 female 96-88-90.5002 Xiaoliang 17 male 95-88-93.5003 Xiaoming 19 male 86.5-98-91004 Xiaoguang 18 male 88-80-94005 Xiaohua 16 female 97-58.5-88查询student表中的前两条数据写入到本地文件/home/test4目录下查询student表中男生的数据写入到本地文件/home/test4_1目录下女生的数据写入到本地文件/home/test4_2目录下由于hive启动时间较长测评时请耐心等待大概需要时间1-2分钟。测试说明平台会对你编写的命令进行测试若操作成功会显示如下信息test4目录下文件内容为2Xiaoliang17male95.088.093.51Xiaohong18female96.088.090.5test4_1目录下文件内容为2Xiaoliang17male95.088.093.53Xiaoming19male86.598.091.04Xiaoguang18male88.080.094.0test4_2目录下文件内容为1Xiaohong18female96.088.090.55Xiaohua16female97.058.588.0开始你的任务吧祝你成功--使用test4数据库use test4;--Begininsert overwrite local directory /home/test4select * from student limit 2;FROM studentINSERT OVERWRITE LOCAL DIRECTORY /home/test4_1SELECT * where sexmaleINSERT OVERWRITE LOCAL DIRECTORY /home/test4_2SELECT * where sexfemale ;--End有任何问题都可以随时关注私信