數(shù)據(jù)倉(cāng)庫(kù)Hive應(yīng)用實(shí)戰(zhàn)- 課件 模塊3 管理倉(cāng)庫(kù)表中數(shù)據(jù)

理解并熟記創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)法和應(yīng)用方法理解并熟記修改、刪除數(shù)據(jù)庫(kù)的語(yǔ)法和應(yīng)用方法理解并熟記查詢和切換數(shù)據(jù)庫(kù)的語(yǔ)法和應(yīng)用方法理解并熟記修改數(shù)據(jù)庫(kù)存儲(chǔ)位置的語(yǔ)法和應(yīng)用方法理解并初步建立數(shù)據(jù)庫(kù)安全規(guī)范意識(shí)Hive數(shù)據(jù)倉(cāng)庫(kù)中，表是存儲(chǔ)數(shù)據(jù)的基本單位，在上一模塊中創(chuàng)建好數(shù)據(jù)倉(cāng)庫(kù)后，需要在數(shù)據(jù)倉(cāng)庫(kù)中基于實(shí)際數(shù)據(jù)格式分析結(jié)果創(chuàng)建表，并將表對(duì)應(yīng)到HDFS相應(yīng)目錄。本任務(wù)以“學(xué)生信息系統(tǒng)”項(xiàng)目、“大數(shù)據(jù)商業(yè)智能選址”項(xiàng)目為實(shí)操載體，完成分析實(shí)際數(shù)據(jù)格式、使用多種HiveQL靈活創(chuàng)建內(nèi)部表和外部表、設(shè)置表屬性、設(shè)置表存儲(chǔ)格式、設(shè)置字符編碼、處理數(shù)據(jù)中NULL值等學(xué)習(xí)目標(biāo)。任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)3.1.1Hive數(shù)據(jù)類(lèi)型任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)Hive支持關(guān)系數(shù)據(jù)庫(kù)中的大多數(shù)基本數(shù)據(jù)類(lèi)型，同時(shí)也支持關(guān)系數(shù)據(jù)庫(kù)中使用頻率較低的3種集合數(shù)據(jù)類(lèi)型。數(shù)據(jù)類(lèi)型描述語(yǔ)法示例TINYINT1byte有符號(hào)整數(shù)20SMALINT2byte有符號(hào)整數(shù)20INT/INTEGER4byte有符號(hào)整數(shù)20BIGINT8byte有符號(hào)整數(shù)20FLOAT單精度浮點(diǎn)數(shù)3.14159DOUBLE雙精度浮點(diǎn)數(shù)3.14159DECIMAL高精度浮點(diǎn)數(shù)DECIMAL(10,6)，表示整數(shù)部分加小數(shù)部分的總長(zhǎng)度為10，小數(shù)部分的位數(shù)6位。TIMESTAMP整數(shù)，浮點(diǎn)數(shù)或者字符串1327882394（unix新紀(jì)元秒），1327882394.123456789（unix新紀(jì)元秒并跟隨有納秒數(shù)），‘2020-02-0108:12:55.123456789’（JDBC所兼容的java.sql.Timestamp時(shí)間格式）。DATE以年/月/日形式描述的日期，格式為YYYY-MM-DD2022-01-01INTERVAL表示時(shí)間間隔INTERVAL‘2’DAY：表示間隔兩天STRING字符序列，可以指定字符集?？梢允褂脝我?hào)或者雙引號(hào)‘nowisthetime’，“forallgoodmen”VARCHAR變長(zhǎng)字符串，字符串長(zhǎng)度限制區(qū)間為1~65355VARCHAR(20)，當(dāng)插入5個(gè)字符時(shí)，會(huì)占用5個(gè)字符的位置。CHAR定長(zhǎng)字符串CHAR(20)：當(dāng)插入5個(gè)字符時(shí)，會(huì)占用20個(gè)字符位置，剩余的15個(gè)字符位置由空格填充。BOOLEAN布爾類(lèi)型，true或者falseTRUEBINARY字節(jié)數(shù)組3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)1、表類(lèi)型內(nèi)部表，又稱管理表，能控制數(shù)據(jù)的生命周期，即當(dāng)刪除內(nèi)部表時(shí)，Hive也將刪除文件系統(tǒng)中此表的底層數(shù)據(jù)。Hive中數(shù)據(jù)和元數(shù)據(jù)各自獨(dú)立存儲(chǔ)，因此通常底層數(shù)據(jù)被pig或其他工具共同使用，因此Hadoop中不常使用內(nèi)部表。在下列情況推薦使用內(nèi)部表：數(shù)據(jù)是臨時(shí)存儲(chǔ)的。訪問(wèn)數(shù)據(jù)的唯一方式是通過(guò)Hive，而且需要用Hive來(lái)完全管理表和數(shù)據(jù)的生命周期。3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)1、表類(lèi)型外部表不能控制數(shù)據(jù)的生命周期，即當(dāng)刪除外部表時(shí)，Hive不會(huì)刪除文件系統(tǒng)中此表的底層數(shù)據(jù)。通常底層數(shù)據(jù)被用于多個(gè)用例，即使未被用于多個(gè)用例，也不應(yīng)該在刪除表定義時(shí)刪除底層數(shù)據(jù)，因此外部表是Hadoop所有生產(chǎn)部署中推薦的表類(lèi)型。在下列情況推薦使用外部表：想刪除表定義而無(wú)須擔(dān)心刪除底層數(shù)據(jù)。數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)而不是HDFS之上，并且有多個(gè)集群訪問(wèn)這些數(shù)據(jù)，需要使用自定義位置存儲(chǔ)表數(shù)據(jù)。不使用基于另一個(gè)表來(lái)創(chuàng)建該目標(biāo)表(CREATETABLEASSELECT)。數(shù)據(jù)將被多個(gè)處理引擎訪問(wèn)。如，可能使用Hive來(lái)讀取表，又可能在Spark程序中使用該表。在同一數(shù)據(jù)集上創(chuàng)建多個(gè)表的定義。當(dāng)有多個(gè)表定義時(shí)，在刪除其中一個(gè)定義時(shí)不應(yīng)該刪除底層數(shù)據(jù)，此時(shí)外部表就很重要了。3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)2、表創(chuàng)建語(yǔ)法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表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)2、表創(chuàng)建語(yǔ)法關(guān)鍵字語(yǔ)法解釋CREATE用于創(chuàng)建表的關(guān)鍵字。EXTERNAL可選子句。用于指定該表是一張外部表。IFNOTEXISTS可選子句。通知用戶是否具有相同名字的表存在。使用該子句，如果存在相同表名，忽略后面的語(yǔ)句，不再創(chuàng)建該表；不使用該子句，如果該table_name表已經(jīng)存在，將會(huì)拋出錯(cuò)誤信息。COMMENT可選子句。用于添加注釋?zhuān)鶕?jù)所處的位置，為不同的內(nèi)容添加注釋?zhuān)?，如果用在某個(gè)字段后面則是為該字段添加注釋?zhuān)绻迷谡麄€(gè)字段定義的后面則是用來(lái)為該表添加注釋。PARTITIONEDBY可選子句。用于創(chuàng)建分區(qū)表，注意分區(qū)的字段不可在普通字段中重復(fù)出現(xiàn)。分區(qū)表在工作中十分常見(jiàn)，一般來(lái)說(shuō)公司所有的表都會(huì)以日期進(jìn)行分區(qū)，以便提高查詢效率。CLUSTEREDBY可選子句。用于Hive中的分桶操作。ROWFORMATrow_format可選子句。用于指定數(shù)據(jù)切分格式。row_format：DELIMITED：分隔符設(shè)置開(kāi)始語(yǔ)句。[FIELDSTERMINATEDBYchar]：設(shè)置字段間分隔符。[COLLECTIONITEMSTERMINATEDBYchar]：設(shè)置集合類(lèi)型字段的各個(gè)item之間的分隔符。[MAPKEYSTERMINATEDBYchar]：設(shè)置Map集合類(lèi)型字段的key和value之間的分隔符。[LINESTERMINATEDBYchar]：設(shè)置行與行之間的分隔符。STOREDAS可選子句。用于設(shè)定數(shù)據(jù)在Hive中的存儲(chǔ)格式，一般不指定，直接使用默認(rèn)的存儲(chǔ)格式。TBLPROPERTIYES可選子句。用于為表設(shè)置表層級(jí)的Key-Value（鍵-值對(duì)）格式的表屬性，Key可以自定義。LOCATION可選子句。指定表的數(shù)據(jù)文件存放的HDFS目錄，不管內(nèi)部表還是外部表，都可以指定。不指定時(shí)表的數(shù)據(jù)文件存放在默認(rèn)的倉(cāng)庫(kù)路徑。3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)【例3-1】在studentdb數(shù)據(jù)庫(kù)中創(chuàng)建外部表學(xué)生表student_externel，表字段為stid和stname，均為string類(lèi)型，并添加“studentdb:aexternaltableofstudent”的表注釋。CREATEEXTERNALTABLEstudentdb.student_external(stidstring,stnamestring)COMMENT"studentdb:aexternaltableofstudent";3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)【例3-2】在studentdb數(shù)據(jù)庫(kù)中創(chuàng)建內(nèi)部表學(xué)生表student_internel，表字段為stid和stname，均為string類(lèi)型，并添加“studentdb:ainternaltableofstudent”的表注釋。CREATETABLEstudentdb.student_internal(stidstring,stnamestring)comment"studentdb:ainternaltableofstudent";3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)【例3-3】查看數(shù)據(jù)student.txt，針對(duì)該數(shù)據(jù)在studentdb數(shù)據(jù)庫(kù)中創(chuàng)建內(nèi)部表student。3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)分析：建表前需要先分析確定表字段的數(shù)據(jù)類(lèi)型。字段名數(shù)據(jù)類(lèi)型描述stnameSTRING學(xué)生姓名stIDSTRING學(xué)生學(xué)號(hào)classSTRING學(xué)生所在班級(jí)roommateARRAY學(xué)生的室友，使用ARRAY存放該學(xué)生的所有室友信息。course_scoresMAP學(xué)生的課程成績(jī)，使用鍵-值對(duì)元組的Map數(shù)據(jù)類(lèi)型存放學(xué)生各門(mén)課程名及對(duì)應(yīng)成績(jī)，MAP<STRING,FLOAT>，STRING表示課程名的數(shù)據(jù)類(lèi)型，F(xiàn)LOAT表示課程成績(jī)的數(shù)據(jù)類(lèi)型。addressSTRUCT學(xué)生的家庭省市信息，使用STRUCT結(jié)構(gòu)體數(shù)據(jù)類(lèi)型存放學(xué)生家庭的省和市信息，STRUCT<province:STRING,city:STRING>中的province表示省份是STRING類(lèi)型，city表示城市是STRING類(lèi)型。3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)CREATETABLEstudentdb.student(stnameSTRINGCOMMENT'姓名',stIDSTRINGCOMMENT'學(xué)號(hào)',classSTRINGCOMMENT'班級(jí)',roommateARRAY<STRING>COMMENT'室友',course_scoresMAP<STRING,FLOAT>COMMENT'課程分?jǐn)?shù)',addressSTRUCT<province:STRING,city:STRING>COMMENT'家庭住址')ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t'COLLECTIONITEMSTERMINATEDBY','MAPKEYSTERMINATEDBY':'LINESTERMINATEDBY'\n'STOREDASTEXTFILETBLPROPERTIES('skip.header.line.count'='1');3.1.2表的創(chuàng)建任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)上傳student.txt文件到student表的HDFS存儲(chǔ)目錄/user/hive/warehouse/studentdb.db/student中，并查看表中數(shù)據(jù)：dfs-put/home/hadoop/hivedata/student/student.txt/user/hive/warehouse/studentdb.db/student;3.1.3表的管理任務(wù)1創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)1.查看表語(yǔ)法SHOWTABLES[INdatabase_nam