《Hive数据仓库应用与实战》课件 项目三-任务二-解析HiveQL DDL

《Hive数据仓库应用与实战》Hive数据仓库应用与实战项目三解析HiveQL语法任务二解析HiveQLDDL回顾回顾知识点1Hive基本数据类型回顾知识点2Hive复杂数据类型任务引入DDL，是对数据库内部的对象进行创建，删除，修改等的操作语言。本任务将基于数据库和表，讲解Hive中DDL语言的用法。任务概要任务描述:本任务介绍Hive的数据定义语言，主要用于在数据库中创建新表或删除表，以及为表加入索引等，本任务将讲解常用的集中DDL语言，同学们学完之后能够根据需求创建、修改数据库和表。任务教学目标：掌握数据库的创建、查询、删除、修改、切换方法。掌握表的创建、展示、查询、修改、阶段、删除、修改分区方法。一基于数据库的DDL操作二基于表的DDL操作教学内容基于数据库的DDL操作知识点一（一）基于数据库的DDL操作CreateDatabaseCREATE(DATABASE|SCHEMA)[IFNOTEXISTS]database_name[COMMENTdatabase_comment][LOCATIONhdfs_path][WITHDBPROPERTIES(property_name=property_value,...)];1、创建数据库（CreateDatabase）（一）基于数据库的DDL操作按照要求创建hive1、hive2、hive3三个数据库：hive>CREATEDATABASEhive1;hive>CREATEDATABASEIFNOTEXISTShive2；>COMMENT"thisishadoopdatabase">WITHDBPROPERTIES("creator"="hadoop","date"="2021-07-30");hive>CREATEDATABASEhive3LOCATION'/db_hive3';1、创建数据库（CreateDatabase）（一）基于数据库的DDL操作在HDFS中查看数据库文件夹：[root@localhost~]$hadoopfs-ls/user/hive/warehouse在RDBMS中查看数据库相关信息：mysql>usemetastore;mysql>select*fromDBS\G1、创建数据库（CreateDatabase）（一）基于数据库的DDL操作下面是官网上为我们列出的语法：SHOW(DATABASES|SCHEMAS)[LIKE'identifier_with_wildcards'];2、查询数据库（ShowDatabases）（一）基于数据库的DDL操作查看我们刚才创建的3个数据库：hive>showdatabases;hive>showdatabaseslike'hive1';hive>showdatabaseslike'hive*';2、查询数据库（ShowDatabases）（一）基于数据库的DDL操作下面是官网上为我们列出的语法：DESCRIBEDATABASE[EXTENDED]db_name;--describe可简写为desc;3、查询数据库信息（DescribeDatabase）DESCRIBEDATABASEdb_name：查看数据库的描述信息和文件目录位置路径信息；EXTENDED：加上数据库键值对的属性信息。（一）基于数据库的DDL操作查询刚才创建的3个数据库的信息：hive>descdatabasehive1;hive>descdatabasehive2;hive>descdatabasehive3;hive>descdatabaseextendedhive2;3、查询数据库信息（DescribeDatabase）（一）基于数据库的DDL操作下面是官网上为我们列出的语法：DROP(DATABASE|SCHEMA)[IFEXISTS]database_name[RESTRICT|CASCADE];4、删除数据库（DropDatabase）RESTRICT：默认是restrict，如果该数据库还有表存在则报错；CASCADE：级联删除数据库(当数据库还有表时，级联删除表后再删除数据库)--生产尽量不用。（一）基于数据库的DDL操作下面是官网上为我们列出的语法：ALTER(DATABASE|SCHEMA)database_nameSETDBPROPERTIES(property_name=property_value,...);5、修改数据库信息（AlterDatabase）（一）基于数据库的DDL操作我们对数据库hive2进行如下修改。hive>alterdatabasehive2setdbproperties("update"="hadoop");hive>alterdatabasehive2setowneruserhive;5、修改数据库信息（AlterDatabase）（一）基于数据库的DDL操作修改前。hive>descdatabasehive2;OKdb_name comment location owner_name owner_type parametershive2 thisishadoopdatabase hdfs://192.168.6.131:9000/user/hive/warehouse/hive2.db root USER Timetaken:0.054seconds,Fetched:1row(s)5、修改数据库信息（AlterDatabase）（一）基于数据库的DDL操作修改后。hive(default)>descdatabaseextendedhive2;OKdb_name comment location owner_name owner_type parametershive2 thisishadoopdatabase hdfs://192.168.6.131:9000/user/hive/warehouse/hive2.db hive USER {date=2021-07-30,creator=hadoop,update=hadoop}5、修改数据库信息（AlterDatabase）（一）基于数据库的DDL操作切换数据库的语法为：USEdatabase_name;如：hive>usehive1;hive>usedefault;6、切换数据库（UseDatabase）基于表的DDL操作知识点二（二）基于表的DDL操作下面是官网上为我们列出的语法：CREATE[TEMPORARY][EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_name后面讲详细讲解表创建的参数。1、创建表（CreateTable）（二）基于表的DDL操作TEMPORARY（临时表）

CREATETEMPORARYTABLE...1、创建表（CreateTable）如果创建的临时表表名已存在，那么当前session引用到该表名时实际用的是临时表，只有drop或rename临时表名才能使用原始表；临时表限制：不支持分区字段和创建索引。（二）基于表的DDL操作TEMPORARY（临时表）

hive>CREATETEMPORARYTABLEtemporary_table(>idint,>namestring);1、创建表（CreateTable）（二）基于表的DDL操作ManagedandExternalTables（内部表和外部表）

CREATEExternalTABLE...1、创建表（CreateTable）Hive上有两种类型的表，一种是ManagedTable(默认的)，另一种是ExternalTable（加上EXTERNAL关键字）。它俩的主要区别在于：当我们drop表时，ManagedTable会同时删去data（存储在HDFS上）和metadata（存储在MySQL），而ExternalTable只会删metadata。（二）基于表的DDL操作ManagedandExternalTables（内部表和外部表）

hive>createexternaltableexternal_table(>idint,>namestring>);1、创建表（CreateTable）（二）基于表的DDL操作COMMENT，ROWFORMAT等其他建表参数

先看看官网对于ROWFORMAT的描述:DELIMITED[FIELDSTERMINATEDBYchar[ESCAPEDBYchar]][COLLECTIONITEMSTERMINATEDBYchar][MAPKEYSTERMINATEDBYchar][LINESTERMINATEDBYchar][NULLDEFINEDASchar]--(Note:AvailableinHive0.13andlater)|SERDEserde_name[WITHSERDEPROPERTIES(property_name=property_value,property_name=property_value,...)]1、创建表（CreateTable）（二）基于表的DDL操作COMMENT，ROWFORMAT等其他建表参数

hive>CREATETABLEhive_test>(idintcomment'thisisid',namestringcomment'thisisname')>comment'thisishive_test'>ROWFORMATDELIMITED>FIELDSTERMINATEDBY'\t';1、创建表（CreateTable）（二）基于表的DDL操作CreateTableAsSelect创建表（拷贝表结构及数据，并且会运行MapReduce作业）CreateTableLike只拷贝表结构1、创建表（CreateTable）（二）基于表的DDL操作下面是官网上为我们列出的语法：SHOWTABLES[INdatabase_name]['identifier_with_wildcards'];SHOWCREATETABLE([db_name.]table_name|view_name);2、展示表（ShowTable与ShowCreateTable）（二）基于表的DDL操作例：hive>showtables;hive>showtables'emp*';hive>showcreatetableemp;2、展示表（ShowTable与ShowCreateTable）（二）基于表的DDL操作下面是官网上为我们列出的语法：DESCRIBE[EXTENDED|FORMATTED]table_name[.col_name([.field_name]|[.'$elem$']|[.'$key$']|[.'$value$'])*];--(Note:Hive1.x.xand0.x.xonly.See"Hive2.0+:NewSyntax"below)3、查询表信息（DescribeTable）（二）基于表的DDL操作例：hive>descemp;hive>descformattedemp;hive>descEXTENDEDemp;3、查询表信息（DescribeTable）（二）基于表的DDL操作下面是官网为我们列出的语法：ALTERTABLEtable_nameRENAMETOnew_table_name;ALTERTABLEtable_nameSETTBLPROPERTIEStable_properties;table_properties::(property_name=property_value,property_name=property_value,...)ALTERTABLEtable_nameSETTBLPROPERTIES('comment'=new_comment);4、修改表（AlterTable）（二）基于表的DDL操作例：hive>altertablehive_testrenametonew_hive_test;hive>altertablenew_hive_testSETTBLPROPERTIES("creator"="hadoop","date"="2021-07-31");hive>altertablenew_hive_testSETTBLPROPERTIES('comment'='Thisisnew_hive_testTable');4、修改表（AlterTable）（二）基于表的DDL操作下面是官网上为我们列出的语法：TRUNCATETABLEtable_name[PARTITIONpartition_spec];partition_spec::(partition_column=partition_col_value,partition_column=partition_col_value,...)5、截断表（TruncateTable）（二）基于表的DDL操作例：hive>truncatetableemp3;注意：truncate不能删除外部表，因为外部表里的数据并不是存放在HiveMetastore中。5、截断表（TruncateTable）（二）基于数据库的DDL操作下面是官网上为我们列出的语法：DROPTABLE[IFEXISTS]table_name[PURGE];--(Note:PURGEavailableinHive0.14.0andlater)6、删除表（DropTable）（二）基于数据库的DDL操作例：hive>droptableemp3;6、删除表（DropTable）（二）基于表的DDL操作添加分区（AddPartitions）ALTERTABLEtable_nameADD[IFNOTEXISTS]PARTITIONpartition_spec[LOCATION'location'][,PARTITIONpartition_spec[LOCATION'location'],...];partition_spec::(partition_column=partition_col_value,partition_column=partition_col_value,...)7、修改分区（AlterPartition）（二）基于表的DDL操作例：创建dept表hive>createtabledept(>deptnoint,>dnamestring,>locstring>)>PARTITIONEDBY(dtstring)>ROWFORMATDELIMITED