实训10 数据定义操作DDL

实训5.2数据定义操作DDL1.实训目的通过本实训，理解HiveQL与SQL的区别，理解DDL的概念以及实际操作。2.实训内容本实训包含HiveQL与SQL的语法上的7种重要对比，用法上的4种不同；分别进行了数据库的DDL操作和表的DDL操作。3.实训要求以小组为单元进行实训，每小组5人，小组自主协商选一位组长，由组长安排和分配实训任务，注意对比HiveQL与SQL的区别，避免在使用的时候出现语法错误。4.准备知识（1）HiveQL与SQL的区别Hive的查询语言是HiveQL，HQL支持SQL-92标准，所以和SQL非常相似。但由于Hive是基于Hadoop，而SQL通常是基于关系型数据库，这使得HQL与SQL相比有一些区别和局限。（2）语法的区别①两表内联：SQL中：select*fromduala,dualbwherea.Key=b.Key;Hive中应为：select*fromdualajoindualbona.Key=b.Key;而不是传统的格式：selectt1.a1asc1,t2.b1asc2fromt1,t2wheret1.a2=t2.b2;②分号字符分号是SQL语句和HiveQL的结束标记，但HiveQL中，对分号有个别区别，例如：selectconcat(Key,concat(';',Key))fromdual;SQL可以执行，但是HiveQL会报错，因用下面语句：selectconcat(Key,concat('\073',Key))fromdual;③IS[NOT]NULLSQL中null代表空值,但在HiveQL中String类型的字段若是空(empty)字符串,即长度为0,那么对它进行ISNULL的判断结果是False，即非空。④Hive不支持将数据插入现有的表或分区中，仅支持覆盖重写整个表⑤Hive不支持INSERTINTO表Values(),UPDATE,DELETE操作⑥Hive支持嵌入Mapreduce程序，来处理复杂的逻辑⑦Hive支持将转换后的数据直接写入不同的表，还能写入分区、HDFS和本地目录（3）用法上的区别①HiveQL不支持行级别的增、改、删，所有数据在加载时就已经确定，不可更改②HiveQL支持基本数据类型和复杂数据类型，而SQL只支持基本数据类型③HiveQL不支持事务④支持分区存储【实训步骤】1.DDL操作1）DatabaseDDL操作创建数据库test默认对应的HDFS目录为：/user/hive/warehouse的文件夹，命名格式：数据库名.dbcreatedatabasetest;可以使用另外一个终端查看到HDFS上创建了相应的文件夹。如图5-14所示。图5-14创建test.db数据库如果想要新建test1数据库，如果存在则不创建，不存在再创建可用下面命令：createdatabaseifnotexiststest1;在指定路径下创建数据库（mydb：数据库名称）createdatabasetest2location'/myhive/mydb';可以看到在指定的路径创建了数据库。如图5-15所示。图5-15创建mydb数据库创建数据库，并为数据库添加描述信息：createdatabasetest3comment'mytestdb'withdbproperties('creator'='lisi','date'='2023-10-1');此时，可以在MySQL中查询所创建的数据库以及相关的描述信息。如图5-16所示。在MySQL中执行：select*fromDBS;select*fromDATABASE_PARAMS;图5-16查看所创建的数据库查询目前所有的数据库showdatabases;查询结果如图5-17所示。图5-17查询所有数据库查看以test开头的数据库（前面实训有提到）showdatabaseslike'test*';查询结果如图5-18所示。图5-18test开头的数据库查看数据库的详细信息descdatabasetest3;查询结果如图5-19所示。图5-19test3数据库详细信息在上面的基础上如果想查看数据库更详细的信息，可以加个参数extendeddescdatabaseextendedtest3;查询结果如图5-20所示。图5-20test3数据库更详细信息切换数据库，如果不指定数据库，那么默认使用的就是default，现在我们使用test3数据库usetest3;修改数据库的modifieralterdatabasetest3setdbproperties('modifier'='teachershao');执行结束后再重新查看详情，发现已经有相应的结果。descdatabaseextendedtest3;效果如图5-21所示。图5-21修改并查看modifier属性信息删除数据库test3dropdatabaseifexiststest3;删除后续以及没有test3数据库，结果如图5-22所示。图5-22删除test3数据库删除时如果报如下错误：InvalidOperationException(message:Databasetest3isnotempty.Oneormoretablesexist.)如果数据库中有0或多个表时，不能直接删除，需要先删除表再删除数据库。如果想要删除含有表的数据库，在删除时加上cascade，表示级联删除（慎用）， 可以使用如下命令：dropdatabaseifexiststest3cascade;②TableDDL操作创建表usetest2;createtableemp(empnoint,enamestring,jobstring,mgrint,hiredatestring,saldouble,commdouble,deptnoint)rowformatdelimitedfieldsterminatedby',';创建结果如图5-23所示。图5-23创建表emp新开一个终端，准备数据：vim/root/datas/emp.txt7369,SMITH,CLERK,7902,1980-12-17,800.00,207499,ALLEN,SALESMAN,7698,1981-2-20,1600.00,300.00,307521,WARD,SALESMAN,7698,1981-2-22,1250.00,500.00,307566,JONES,MANAGER,7839,1981-4-2,2975.00,207654,MARTIN,SALESMAN,7698,1981-9-28,1250.00,1400.00,307698,BLAKE,MANAGER,7839,1981-5-1,2850.00,307782,CLARK,MANAGER,7839,1981-6-9,2450.00,107788,SCOTT,ANALYST,7566,1987-4-19,3000.00,207839,KING,PRESIDENT,1981-11-17,5000.00,107844,TURNER,SALESMAN,7698,1981-9-28,1500.00,0.00,307876,ADAMS,CLERK,7788,1987-5-23,1100.00,207900,JAMES,CLERK,7698,1981-12-3,950.00,307902,FORD,ANALYST,7566,1981-12-3,3000.00,207934,MILLER,CLERK,7782,1982-1-23,1300.00,108888,HIVE,PROGRAM,7839,1988-1-23,10300.00返回hive，执行以下命令将数据导入到hive中：loaddatalocalinpath'/root/datas/emp.txt'overwriteintotableemp;导入结果如图5-24所示。图5-24向emp表中导入数据复制表（只拷贝表结构，没有数据）createtableemp2likeemp;select*fromemp2;复制结果如图5-25所示。图5-25查询表结果复制表（拷贝表结构及数据）本操作会执行MapReduce作业，所以需要先启动YARNcreatetableemp3asselect*fromemp;注意：由于实训环境内存资源比较紧缺，如果实训出错，则可以使用本地模式执行。先使用下面命令设置Hive成本地模式再执行创建语句。效果如图5-26所示。sethive.exec.mode.local.auto=true;图5-26拷贝表结构及数据执行流程select*fromemp3;查看emp3表中的信息如图5-27所示。图5-27emp3表中的信息创建表一张emp_copy表，要求包含emp中的这三列：empno、ename、jobcreatetableemp_copyasselectempno,ename,jobfromemp;showtables'emp*';创建结果如图5-28所示。图5-28创建emp_copy表查看表结构descemp; 操作结果如图5-29所示。图5-29查看emp表结构查看表结构的详细信息descextendedemp; 查看结果如图5-30所示。图5-30查看emp表的详细信息上面查看到的表结构的详细信息格式较乱，可使用formatted格式化查看descformattedemp; 查看结果如图5-31所示。图5-31格式化查看emp表查看表的创建语句showcreatetableemp;修改表名语法：ALTERTABLEtable_nameRENAMETOnew_table_name;修改表名emp2为emp_bak。效果如图5-32所示。altertableemp2renametoemp_