九、Hive表DDL操作一第1关Create/Alter/Drop 数据库任务描述本关任务根据编程要求对数据库进行相关操作。相关知识为了完成本关任务你需要掌握1.如何创建数据库2.如何修改数据库3.如何删除数据库。Create 创建数据库数据库本质上是一个目录或命名空间用于解决表命名冲突。创建数据库的语法为CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name[COMMENT database_comment][LOCATION hdfs_path][WITH DBPROPERTIES (property_nameproperty_value,…)];DATABASE|SCHEMA用于限定创建数据库或数据库模式IF NOT EXISTS目标对象不存在时才执行创建操作可选COMMENT起注释说明作用LOCATION指定数据库位于HDFS上的存储路径。若未指定将使用${hive.metastore.warehouse.dir}定义值作为其上层路径位置WITH DBPROPERTIES为数据库提供描述信息如创建database的用户或时间1、创建一个名为shopping的数据库位于HDFS的/hive/shopping下创建人为Xiaoming创建日期为2019-01-01。CREATE DATABASE IF NOT EXISTS shoppingLOCATION /hive/shoppingWITH DBPROPERTIES(creatorXiaoming,date2019-01-01);说明CREATE等关键字大小写均可。2、使用exit;命令退出hive命令行模式然后再用hdfs dfs -ls /hive查看HDFS上的目录可以看出上述CREATE操作在HDFS的/hive目录下创建了一个shopping目录。3、使用命令查看数据库shopping的信息若不指定关键字EXTENDED则不会输出{}里的内容。DESCRIBE DATABASE EXTENDED shopping;Alter 修改数据库修改数据库的语法为ALTER DATABASE|SCHEMAdatabase_name SET DBPROPERTIES (property_nameproperty_value,…);只能修改数据库的键值对属性值。数据库名和数据库所在的目录位置不能修改修改数据库shopping的键值对描述信息中的创建者信息修改创建人为Xiaohong。ALTER DATABASE shopping SET DBPROPERTIES(creatorXiaohong);使用命令查看修改后的数据库shopping的信息。DESCRIBE DATABASE EXTENDED shopping;Drop 删除数据库选择使用数据库shoppinguse shopping;USE命令用于设定当前所有数据库对象操作所处的工作数据库类似于Linux 文件系统中切换当前目录操作。若返回到default数据库使用下述命令USE DEFAULT;删除数据库语法DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];DATABASE|SCHEMA用于限定删除的数据库或数据库模式IF EXISTS目标对象存在时才执行删除操作可选RESTRICT|CASCADERESTRICT为 Hive 默认操作方式当database_name中不存在任何数据库对象时才能执行DROP操作CASCADE 采用强制DROP方式汇联通存在于database_name中的任何数据库对象和database_name一起删除可选删除数据库shopping该操作会删除其位于HDFS上的shopping目录。DROP DATABASE shopping;编程要求请根据右侧命令行内的提示在Begin - End区域内进行sql语句代码补充具体任务如下创建数据库test1位于HDFS的/hive/test1下创建人creator为John创建日期date为2019-02-25修改数据库test1的创建人为Marry删除数据库test1按照以上要求填写命令。每个要求对应一条命令共3条命令以;隔开。由于hive启动时间较长测评时请耐心等待大概需要时间1-3分钟。测试说明补充完代码后点击测评平台会对你编写的代码进行测试当你的结果与预期输出一致时即为通过。若操作成功会显示如下信息test1数据库的信息如下test1 hdfs://localhost:9000/hive/test1 root USER {data2019-02-25, creatorJohn}修改后test1数据库的信息如下test1 hdfs://localhost:9000/hive/test1 root USER {date2019-02-25, creatorMarry}查看当前有哪些数据库default开始你的任务吧祝你成功#********* Begin *********#echo CREATE DATABASE IF NOT EXISTS test1LOCATION /hive/test1WITH DBPROPERTIES(creatorJohn, date2019-02-25);ALTER DATABASE test1 SET DBPROPERTIES(creatorMarry);DROP DATABASE test1;#********* End *********#第2关Create/Drop/Truncate 表任务描述本关任务根据编程要求在数据库中对表进行相应的操作。相关知识为了完成本关任务你需要掌握1.如何创建表2.如何复制表3.如何删除表4.如何截断表。Create 创建表创建表的语法为CREATE [TEMPOPARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name[(col_name data_type [COMMENT col_comment], ...)][COMMENT table_comment][PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)][CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS][SKEWED BY (col_name,col_name,…) ON ([(col_value,col_value,…),…|col_value,col_value,…]) [STORED AS DIRECTORIES] ][[ROW FORMAT DELIMITED [FIFLDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char]| SERDE serde_name [WITH SERDEPROPERTIES (property_nameproperty_value,property_nameproperty_value,…)]][STORED AS file_format]| STORED BY storage.handler.class.name [WITH SERDEPROPERTIES (…)]][LOCATION hdfs_path][TBLPROPERTIES (property_nameproperty_value,…)][AS select_statement];参数说明如下TEMPOPARY创建临时表若未指定则默认创建的是普通表EXTERNAL创建外部表若未指定则默认创建的是内部表IF NOT EXISTS若表不存在才创建若未指定当目标表存在时创建操作抛出异常db_name.前缀指定表所属于的数据库。若未指定且当前数据库非db_name则使用default数据库COMMENT添加注释说明注释内容位于单引号内PARTITIONED BY针对存储有大量数据集的表根据表内容所具有的某些共同特征定义一个标签将这类数据存储在该标签所标识的位置可以提高表内容的查询速度。PARTITIONED BY中的列名为伪列或标记列不能与表中的实体列名相同否则 hive 表创建操作报错CLUSTERED BY根据列之间的相关性指定列聚类在相同桶中BUCKETS可以对表内容按某一列进行升序ASC或降序DESC排序SORTED BY关键字SKEWED BY用于过滤掉特定列col_name中包含值col_value(ON(col_value,…)关键字指定的值的记录并单独存储在指定目录STORED AS DIRECTORIES下的单独文件中ROW FORMAT指定 hive 表行对象ROW Object数据与 HDFS 数据之间进行传输的转换方式HDFS files - Deserializer -Row object以及Row object -Serializer -HDFS files以及数据文件内容与表行记录各列的对应。在创建表时可以指定数据列分隔符FIFLDS TERMINATED BY 子句、对特殊字符进行转义的特殊字符ESCAPED BY 子句、符合数据类型值分隔符COLLECTION ITEMS TERMINATED BY 子句、MAP key-value 类型分隔符MAP KEYS TERMINATED BY、数据记录行分隔符LINES TERMINATED BY、定义NULL字符NULL DEFINED AS同时可以指定自定义的SerDESerializer和Deserializer序列化和反序列化也可以指定默认的SerDE。如果ROW FORMAT 未指定或指定为ROW FORMAT DELIMITED将使用内部默认SerDeSTORED AS指定 hive 表数据在 HDFS 上的存储方式。file_format值包括TEXTFILE普通文本文件默认方式、SEQUENCEFILE压缩模式、ORCORC文件格式和AVROAVRO文件格式STORED BY创建一个非本地表如创建一个 HBase 表LOCATION指定表数据在 HDFS 上的存储位置。若未指定db_name数据库将会储存在${hive.metastore.warehouse.dir}定义位置的db_name目录下TBLPROPERTIES为所创建的表设置属性如创建时间和创建者默认为当前用户和当前系统时间AS select_statement使用select子句创建一个复制表包括select子句返回的表模式和表数据在之前创建的shopping数据库创建一张商品信息表items_infoCREATE TABLE IF NOT EXISTS shopping.items_info(name STRING COMMENT item name,price FLOAT COMMENT item price,category STRING COMMENT item category,brand STRING COMMENT item brand,type STRING COMMENT item type,stock INT COMMENT item stock,address STRUCTstreet:STRING, city:STRING, state:STRING, zip:INT COMMENT item sales address)COMMENT goods information tableTBLPROPERTIES (creatorXiaoming,date2019-01-01);查看items_info表结构:use shopping; #切换到数据库shopping中desc items_info;复制表按照已存在的表或视图定义一个相同结构的表或视图使用LIKE关键字只复制表定义不复制表数据。复制刚才创建的表items_info起名为items_info2。CREATE TABLE IF NOT EXISTS items_info2LIKE items_info;Drop 删除表删除表的语法为DROP TABLE [IF EXISTS] table_name;[IF EXISTS]关键字可选若未指定且表table_name不存在时Hive返回错误。删除刚才复制的表items_info2:DROP TABLE IF EXISTS items_info2;Truncate 截断表大家众所周知当我们在自己的电脑上删除某一个文件它并没有彻底删除而是进入了回收站你要在回收站中再将其删除才算彻底清除。截断表就相当于直接将数据从电脑上删除而不会放入回收站。截断表删除表中所有行的语法为TRUNCATE TABLE table_name [PARTITION partition_spec];partition_spec:(partition_columnpartition_col_value,partition_columnpartition_col_value,…)编程要求请根据右侧命令行内的提示在Begin - End区域内进行sql语句代码补充具体任务如下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在test2中创建表student表结构如上所示使用LIKE关键字创建一个与student表结构相同的表student_info删除表student按照以上要求填写命令。每个要求对应一条命令共4条命令以;隔开。由于hive启动时间较长测评时请耐心等待大概需要时间1-3分钟。测试说明补充完代码后点击测评平台会对你编写的代码进行测试当你的结果与预期输出一致时即为通过。若操作成功会显示如下信息表student的结构为sno int student snoname string student nameage int student agesex string student sexscore structChinese:float,Math:float,English:float student score表student_info的结构为sno int student snoname string student nameage int student agesex string student sexscore structChinese:float,Math:float,English:float student score数据库test2中有以下表student_info开始你的任务吧祝你成功#********* Begin *********#echo CREATE DATABASE IF NOT EXISTS test2;CREATE TABLE IF NOT EXISTS test2.student(sno INT COMMENT student sno,name STRING COMMENT student name,age INT COMMENT student age,sex STRING COMMENT student sex,score STRUCTChinese:FLOAT,Math:FLOAT,English:FLOAT COMMENT student score);CREATE TABLE IF NOT EXISTS test2.student_info LIKE test2.student;DROP TABLE IF EXISTS test2.student;#********* End *********#第3关Alter 表/列任务描述本关任务根据编程要求在数据库中对表进行相应的操作。相关知识为了完成本关任务你需要掌握1.如何修改表2.如何修改列。Alter 重命名表重命名表的语法为ALTER TABLE table_name RENAME TO new_table_name;将上一关创建的items_info表重命名为items。ALTER TABLE items_info RENAME TO items;Alter 修改表修改表列的语法为ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUM] col_old_name col_new_name colum_type [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];以上操作可以修改表的列名、列数据类型、列存储位置以及注释说明FIRST、AFTER用于指定是否交换列的前后顺序该操作只改变表的metadataRESTRICT方式即默认方式CASCADE关键字用于限定修改操作同时同步到表metadata和分区metadata修改表items的price列名为items_price。ALTER TABLE items CHANGE price item_price FLOAT COMMENT items price;Alter 修改列增加表列和删除表列或替换表列的语法为ALTER TABLE table_name [PARTITION partition_spec] ADD|REPLACE COLUMNS (col_namedata_type [COMMENT col_comment],…) [CASCADE|RESTRICT]ADD COLUMNS用于在表中已存在实体列existing columns之后且分区列partition columns或伪列之前添加新的列REPLACE COLUMNS删除表中现有的全部列添加新的列集合。该操作仅支持使用内部的SerDe(DynamicSerDe、MetadataTypedColumnsetSerDe/LazySimpleSerDe和ColumnarSerDe)的表SerDe用于实现表数据与 HDFS 数据之间的转换方式表items添加生产日期item_date列数据类型为STRING说明为items dateALTER TABLE items ADD COLUMNS (item_date STRING COMMENT item date);编程要求请根据右侧命令行内的提示在Begin - End区域内进行sql语句代码补充具体任务如下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创建数据库test3在数据库tets3中创建表student表结构如上所示和第二关相同重命名表名字student为student_info修改列名age为student_age增加一列birthday数据类型为STRING说明信息为student birthday按照以上要求填写命令。每个要求对应一条命令共5条命令以;隔开。由于hive启动时间较长测评时请耐心等待大概需要时间1-3分钟。测试说明补充完代码后点击测评平台会对你编写的代码进行测试当你的结果与预期输出一致时即为通过。若操作成功会显示如下信息student的表结构为sno int student snoname string student nameage int student agesex string student sexscore structChinese:float,Math:float,English:float student scoretest3中有以下表student_info修改列名后的student_info表结构如下sno int student snoname string student namestudent_age int student agesex string student sexscore structChinese:float,Math:float,English:float student score增加一列后的student_info表结构如下sno int student snoname string student namestudent_age int student agesex string student sexscore structChinese:float,Math:float,English:float student scorebirthday string student birthday开始你的任务吧祝你成功#********* Begin *********#echo CREATE DATABASE IF NOT EXISTS test3;CREATE TABLE IF NOT EXISTS test3.student(sno INT COMMENT student sno,name STRING COMMENT student name,age INT COMMENT student age,sex STRING COMMENT student sex,score STRUCTChinese:FLOAT,Math:FLOAT,English:FLOAT COMMENT student score);ALTER TABLE test3.student RENAME TO test3.student_info;ALTER TABLE test3.student_info CHANGE age student_age INT COMMENT student age;ALTER TABLE test3.student_info ADD COLUMNS (birthday STRING COMMENT student birthday);#********* End *********#第4关表分区任务描述本关任务根据编程要求在数据库中对表进行相应的操作。相关知识为了完成本关任务你需要掌握1.如何创建分区表2.如何添加分区3.如何重命名表分区4.如何交换表分区5.如何表分区信息持久化6.如何删除表分区。创建分区表使用shopping数据库创建一张商品信息分区表items_info2按商品品牌p_brand和商品分类p_category进行分区CREATE TABLE IF NOT EXISTS shopping.items_info2(name STRING COMMENT item name,price FLOAT COMMENT item price,category STRING COMMENT item category,brand STRING COMMENT item brand,type STRING COMMENT item type,stock INT COMMENT item stock,address STRUCTstreet:STRING, city:STRING, state:STRING, zip:INT COMMENT item sales address)COMMENT goods information tablePARTITIONED BY (p_category STRING,p_brand STRING)ROW FORMAT DELIMITEDFIELDS TERMINATED BY \tCOLLECTION ITEMS TERMINATED BY ,TBLPROPERTIES (creatorXiaoming,date2019-01-01);添加分区向表items_info2添加两个分区ALTER TABLE items_info2 ADD PARTITION (p_categoryclothes,p_brandplayboy) LOCATION /hive/shopping/items_info2/playboy/clothesPARTITION (p_categoryshoes,p_brandplayboy) LOCATION /hive/shopping/items_info2/playboy/shoes;注意PARTITIONED BY中的列p_category和p_brand为伪列不能与表中的实体列名相同否则hive表创建操作报错p_category和p_brand分别对应表中的实体列category、brand。重命名表分区重命名表分区的语法为ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;重命名items_info2表的表分区playboy/clothes为playboy/T-shirtALTER TABLE items_info2 PARTITION (p_categoryclothes,p_brandplayboy) RENAME TO PARTITION (p_categoryT-shirt,p_brandplayboy);交换表分区交换表分区的语法为ALTER TABLE table_name_1 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_2;该操作移动表table_name_1中特定分区下的数据到具有相同表模式且不存储在相应分区的table_name_2中。表分区信息持久化表分区信息持久化的语法为MSCK REPAIR TABLE table_name;该操作作用于同步表table_name在HDFS上的分区信息到Hive位于RDBMS 的metastore中。删除表分区删除表分区的语法为ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec,PARTITION partition_spec,…;该操作会删除与特定分区相关的数据以及metadata。删除表items_info2的playboy/shoes表分区ALTER TABLE items_info2 DROP IF EXISTS PARTITION (p_categoryshoes,p_brandplayboy);编程要求请根据右侧命令行内的提示在Begin - End区域内进行sql语句代码补充具体任务如下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创建数据库test4在数据库tets4中创建分区表student表结构如上所示和第二、三关相同设置分区列为stu_year类型STRING、subject类型STRING添加两个分区stu_year2018,subjectChinese和stu_year2018,subjectMath重命名表分区将2018/Math分区重命名为2018/English删除表分区将2018/Chinese分区删除按照以上要求在代码栏填写命令。每个要求对应一条命令共5条命令以;隔开。由于hive启动时间较长测评时请耐心等待大概需要时间1-3分钟。测试说明补充完代码后点击测评平台会对你编写的代码进行测试当你的结果与预期输出一致时即为通过。预期输出student分区的表结构为sno int student snoname string student nameage int student agesex string student sexscore structChinese:float,Math:float,English:float student scorestu_year stringsubject string# Partition Information# col_name data_type commentstu_year stringsubject string添加的分区为stu_year2018/subjectChinesestu_year2018/subjectMath重命名后的分区为stu_year2018/subjectChinesestu_year2018/subjectEnglish删除后分区为stu_year2018/subjectEnglish开始你的任务吧祝你成功#********* Begin *********#echo CREATE DATABASE IF NOT EXISTS test4;CREATE TABLE IF NOT EXISTS test4.student(sno INT COMMENT student sno,name STRING COMMENT student name,age INT COMMENT student age,sex STRING COMMENT student sex,score STRUCTChinese:FLOAT,Math:FLOAT,English:FLOAT COMMENT student score) PARTITIONED BY (stu_year STRING, subject STRING);ALTER TABLE test4.student ADD PARTITION (stu_year2018,subjectChinese) PARTITION (stu_year2018,subjectMath);ALTER TABLE test4.student PARTITION (stu_year2018,subjectMath) RENAME TO PARTITION (stu_year2018,subjectEnglish);ALTER TABLE test4.student DROP IF EXISTS PARTITION (stu_year2018,subjectChinese);#********* End *********#有任何问题都可以随时关注私信