数据仓库Hive应用实战- 课件 模块3 管理仓库表中数据

理解并熟记创建数据库的语法和应用方法理解并熟记修改、删除数据库的语法和应用方法理解并熟记查询和切换数据库的语法和应用方法理解并熟记修改数据库存储位置的语法和应用方法理解并初步建立数据库安全规范意识Hive数据仓库中，表是存储数据的基本单位，在上一模块中创建好数据仓库后，需要在数据仓库中基于实际数据格式分析结果创建表，并将表对应到HDFS相应目录。本任务以“学生信息系统”项目、“大数据商业智能选址”项目为实操载体，完成分析实际数据格式、使用多种HiveQL灵活创建内部表和外部表、设置表属性、设置表存储格式、设置字符编码、处理数据中NULL值等学习目标。任务1创建数据仓库3.1.1Hive数据类型任务1创建数据仓库Hive支持关系数据库中的大多数基本数据类型，同时也支持关系数据库中使用频率较低的3种集合数据类型。数据类型描述语法示例TINYINT1byte有符号整数20SMALINT2byte有符号整数20INT/INTEGER4byte有符号整数20BIGINT8byte有符号整数20FLOAT单精度浮点数3.14159DOUBLE双精度浮点数3.14159DECIMAL高精度浮点数DECIMAL(10,6)，表示整数部分加小数部分的总长度为10，小数部分的位数6位。TIMESTAMP整数，浮点数或者字符串1327882394（unix新纪元秒），1327882394.123456789（unix新纪元秒并跟随有纳秒数），‘2020-02-0108:12:55.123456789’（JDBC所兼容的java.sql.Timestamp时间格式）。DATE以年/月/日形式描述的日期，格式为YYYY-MM-DD2022-01-01INTERVAL表示时间间隔INTERVAL‘2’DAY：表示间隔两天STRING字符序列，可以指定字符集。可以使用单引号或者双引号‘nowisthetime’，“forallgoodmen”VARCHAR变长字符串，字符串长度限制区间为1~65355VARCHAR(20)，当插入5个字符时，会占用5个字符的位置。CHAR定长字符串CHAR(20)：当插入5个字符时，会占用20个字符位置，剩余的15个字符位置由空格填充。BOOLEAN布尔类型，true或者falseTRUEBINARY字节数组3.1.2表的创建任务1创建数据仓库1、表类型内部表，又称管理表，能控制数据的生命周期，即当删除内部表时，Hive也将删除文件系统中此表的底层数据。Hive中数据和元数据各自独立存储，因此通常底层数据被pig或其他工具共同使用，因此Hadoop中不常使用内部表。在下列情况推荐使用内部表：数据是临时存储的。访问数据的唯一方式是通过Hive，而且需要用Hive来完全管理表和数据的生命周期。3.1.2表的创建任务1创建数据仓库1、表类型外部表不能控制数据的生命周期，即当删除外部表时，Hive不会删除文件系统中此表的底层数据。通常底层数据被用于多个用例，即使未被用于多个用例，也不应该在删除表定义时删除底层数据，因此外部表是Hadoop所有生产部署中推荐的表类型。在下列情况推荐使用外部表：想删除表定义而无须担心删除底层数据。数据存储在文件系统而不是HDFS之上，并且有多个集群访问这些数据，需要使用自定义位置存储表数据。不使用基于另一个表来创建该目标表(CREATETABLEASSELECT)。数据将被多个处理引擎访问。如，可能使用Hive来读取表，又可能在Spark程序中使用该表。在同一数据集上创建多个表的定义。当有多个表定义时，在删除其中一个定义时不应该删除底层数据，此时外部表就很重要了。3.1.2表的创建任务1创建数据仓库2、表创建语法CREATE[EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_name(col_namedata_type[COMMENTcol_comment]，...)[COMMENTtable_comment][PARTITIONEDBY(col_namedata_type[COMMENTcol_comment]，...)][CLUSTEREDBY(col_name,col_name,...)[SORTEDBY(col_name[ASC|DESC],…)]INTOnum_bucketsBUCKETS][ROWFORMATrow_format][STOREDASfile_format][TBLPROPERTIYES(property_name=property_value,...)][LOCATIONhdfs_path];3.1.2表的创建任务1创建数据仓库2、表创建语法关键字语法解释CREATE用于创建表的关键字。EXTERNAL可选子句。用于指定该表是一张外部表。IFNOTEXISTS可选子句。通知用户是否具有相同名字的表存在。使用该子句，如果存在相同表名，忽略后面的语句，不再创建该表；不使用该子句，如果该table_name表已经存在，将会抛出错误信息。COMMENT可选子句。用于添加注释，根据所处的位置，为不同的内容添加注释，例如，如果用在某个字段后面则是为该字段添加注释，如果用在整个字段定义的后面则是用来为该表添加注释。PARTITIONEDBY可选子句。用于创建分区表，注意分区的字段不可在普通字段中重复出现。分区表在工作中十分常见，一般来说公司所有的表都会以日期进行分区，以便提高查询效率。CLUSTEREDBY可选子句。用于Hive中的分桶操作。ROWFORMATrow_format可选子句。用于指定数据切分格式。row_format：DELIMITED：分隔符设置开始语句。[FIELDSTERMINATEDBYchar]：设置字段间分隔符。[COLLECTIONITEMSTERMINATEDBYchar]：设置集合类型字段的各个item之间的分隔符。[MAPKEYSTERMINATEDBYchar]：设置Map集合类型字段的key和value之间的分隔符。[LINESTERMINATEDBYchar]：设置行与行之间的分隔符。STOREDAS可选子句。用于设定数据在Hive中的存储格式，一般不指定，直接使用默认的存储格式。TBLPROPERTIYES可选子句。用于为表设置表层级的Key-Value（键-值对）格式的表属性，Key可以自定义。LOCATION可选子句。指定表的数据文件存放的HDFS目录，不管内部表还是外部表，都可以指定。不指定时表的数据文件存放在默认的仓库路径。3.1.2表的创建任务1创建数据仓库【例3-1】在studentdb数据库中创建外部表学生表student_externel，表字段为stid和stname，均为string类型，并添加“studentdb:aexternaltableofstudent”的表注释。CREATEEXTERNALTABLEstudentdb.student_external(stidstring,stnamestring)COMMENT"studentdb:aexternaltableofstudent";3.1.2表的创建任务1创建数据仓库【例3-2】在studentdb数据库中创建内部表学生表student_internel，表字段为stid和stname，均为string类型，并添加“studentdb:ainternaltableofstudent”的表注释。CREATETABLEstudentdb.student_internal(stidstring,stnamestring)comment"studentdb:ainternaltableofstudent";3.1.2表的创建任务1创建数据仓库【例3-3】查看数据student.txt，针对该数据在studentdb数据库中创建内部表student。3.1.2表的创建任务1创建数据仓库分析：建表前需要先分析确定表字段的数据类型。字段名数据类型描述stnameSTRING学生姓名stIDSTRING学生学号classSTRING学生所在班级roommateARRAY学生的室友，使用ARRAY存放该学生的所有室友信息。course_scoresMAP学生的课程成绩，使用键-值对元组的Map数据类型存放学生各门课程名及对应成绩，MAP<STRING,FLOAT>，STRING表示课程名的数据类型，FLOAT表示课程成绩的数据类型。addressSTRUCT学生的家庭省市信息，使用STRUCT结构体数据类型存放学生家庭的省和市信息，STRUCT<province:STRING,city:STRING>中的province表示省份是STRING类型，city表示城市是STRING类型。3.1.2表的创建任务1创建数据仓库CREATETABLEstudentdb.student(stnameSTRINGCOMMENT'姓名',stIDSTRINGCOMMENT'学号',classSTRINGCOMMENT'班级',roommateARRAY<STRING>COMMENT'室友',course_scoresMAP<STRING,FLOAT>COMMENT'课程分数',addressSTRUCT<province:STRING,city:STRING>COMMENT'家庭住址')ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t'COLLECTIONITEMSTERMINATEDBY','MAPKEYSTERMINATEDBY':'LINESTERMINATEDBY'\n'STOREDASTEXTFILETBLPROPERTIES('skip.header.line.count'='1');3.1.2表的创建任务1创建数据仓库上传student.txt文件到student表的HDFS存储目录/user/hive/warehouse/studentdb.db/student中，并查看表中数据：dfs-put/home/hadoop/hivedata/student/student.txt/user/hive/warehouse/studentdb.db/student;3.1.3表的管理任务1创建数据仓库1.查看表语法SHOWTABLES[INdatabase_nam