2025年大數(shù)據(jù)分析師《SQL與Hive查詢優(yōu)化》歷年真題考試時間：______分鐘總分：______分姓名：______一、選擇題（請將正確選項的字母填入括號內(nèi)）1.下列哪個窗口函數(shù)允許用戶訪問當(dāng)前行的下一行數(shù)據(jù)？A.`ROW_NUMBER()`B.`RANK()`C.`LAG()`D.`FIRST_VALUE()`2.在Hive中，執(zhí)行`CREATETABLEtPARTITIONBY(dtDATE)`后，插入數(shù)據(jù)`INSERTINTOTABLEtPARTITION(dt='2023-10-27')VALUES('a',1)`，該數(shù)據(jù)存儲在：A.表t的特定分區(qū)`dt='2023-10-27'`下B.表t的特定分區(qū)`dt='2023-10-26'`下C.表t的根目錄下，不歸入任何分區(qū)D.會報錯，因為分區(qū)字段未在INSERT中指定3.當(dāng)兩個JOIN操作（`JOINaONb.c=d.c`和`LEFTOUTERJOINaONb.c=d.c`）處理相同的數(shù)據(jù)和條件時，它們返回的結(jié)果集可能不同的情況是：A.當(dāng)表a為空時B.當(dāng)表d為空時C.總是相同，因為SQL標(biāo)準(zhǔn)規(guī)定D.取決于表b和表d中非空c列值的分布4.在Hive中，`sethive.exec.reducers.max=100`的作用是：A.設(shè)置HiveMetastore的最大連接數(shù)B.設(shè)置MapReduce任務(wù)的最大Map數(shù)量C.設(shè)置MapReduce任務(wù)的最大Reduce數(shù)量D.設(shè)置Hive查詢的最大執(zhí)行時間（秒）5.對于一個經(jīng)常需要根據(jù)用戶ID查詢用戶訂單的場景，以下哪種索引策略通常最有效？A.對訂單表中的訂單金額創(chuàng)建索引B.對訂單表中的訂單日期創(chuàng)建索引C.對用戶表中的用戶ID創(chuàng)建索引D.對訂單表中的用戶ID創(chuàng)建索引6.執(zhí)行`SELECT*FROMtable1WHEREcol1IN('a','b','c')`時，如果`col1`上有索引，但查詢條件中使用了函數(shù)`WHERECONCAT(col1)IN('a','b','c')`，則可能導(dǎo)致索引失效的原因是：A.使用了`IN`而不是`=`運(yùn)算符B.選擇了錯誤的索引類型C.函數(shù)運(yùn)算導(dǎo)致索引無法使用D.`col1`數(shù)據(jù)類型不支持索引7.在Hive中，如果一個表被創(chuàng)建為外部表，并且其數(shù)據(jù)文件已經(jīng)存在于HDFS上，那么刪除該外部表時會發(fā)生什么？A.表結(jié)構(gòu)及其數(shù)據(jù)文件都會被刪除B.只有表結(jié)構(gòu)會被刪除，數(shù)據(jù)文件保留在原路徑C.會提示錯誤，因為外部表的數(shù)據(jù)文件在外部D.數(shù)據(jù)文件會被移動到Hive的臨時目錄，表結(jié)構(gòu)被刪除8.`SELECTcol1FROMtable1ORDERBYcol1DESCNULLSLAST`的含義是：A.按col1升序排序，空值排在最前面B.按col1升序排序，空值排在最后面C.按col1降序排序，空值排在最前面D.按col1降序排序，空值排在最后面9.哪種HiveJOIN操作在兩個表進(jìn)行連接時，即使右表有多個行與左表的某一行匹配，也只保留左表該行對應(yīng)的一個右表行？A.INNERJOINB.LEFTOUTERJOINC.RIGHTOUTERJOIND.FULLOUTERJOIN10.在Hive中，`setmapreduce.job.reduces=10`主要影響哪個階段的并行度？A.Map階段的數(shù)據(jù)讀取B.Map階段的本地化處理C.Shuffle階段的數(shù)據(jù)傳輸D.Reduce階段的并行計算二、簡答題1.簡述數(shù)據(jù)庫索引的作用。列舉至少三種可能導(dǎo)致索引失效的情況。2.什么是Hive中的數(shù)據(jù)傾斜？請至少列舉兩種導(dǎo)致數(shù)據(jù)傾斜的原因，并分別說明一種常見的解決方法。3.Hive中的內(nèi)部表（InternalTable）和外部表（ExternalTable）有何主要區(qū)別？在哪些場景下你會選擇使用外部表？4.解釋Hive中“分區(qū)裁剪”（PartitionPruning）的概念及其對查詢優(yōu)化的意義。三、編程題1.假設(shè)有兩張表`employees`(emp_id,emp_name,dept_id)和`salaries`(emp_id,salary,sal_date)。請編寫一個HiveQL查詢，用于查找`2023-01-01`之后入職（假設(shè)入職日期隱含在`salaries`表的`sal_date`字段，且該字段記錄員工首次有薪資記錄的日期），并且薪水大于其所在部門平均薪水的所有員工的名字（emp_name）和部門ID（dept_id）。2.假設(shè)有一個按月份分區(qū)的銷售表`sales`(sale_id,product_id,amount,sale_date)，其中`sale_date`字段的格式為'yyyy-MM-dd'。請編寫一個HiveQL查詢，用于計算每個產(chǎn)品在每個季度的總銷售額，并按產(chǎn)品ID和季度排序（季度從Q1到Q4）。要求使用Hive的內(nèi)置函數(shù)來完成日期的轉(zhuǎn)換和季度的計算。四、案例分析題1.假設(shè)你需要執(zhí)行一個Hive查詢，`SELECT*FROMlarge_tableWHEREcol1='value1'ANDcol2BETWEEN'start'AND'end'`。執(zhí)行計劃顯示該查詢進(jìn)行了全表掃描`large_table`，并且沒有使用索引。表`large_table`有數(shù)百萬行數(shù)據(jù)，且`col1`和`col2`上都有索引。請分析可能的原因，并提出至少三種優(yōu)化該查詢的建議。試卷答案一、選擇題1.C*解析：`LAG()`函數(shù)用于獲取當(dāng)前行之前一行（或指定偏移量）的列值。`ROW_NUMBER()`和`RANK()`是排序函數(shù)，`FIRST_VALUE()`獲取當(dāng)前行的第一個值。2.A*解析：在Hive中，創(chuàng)建表時指定分區(qū)字段后，INSERT語句需要明確指定分區(qū)值，數(shù)據(jù)將被寫入對應(yīng)的分區(qū)目錄下。`PARTITION(dt='2023-10-27')`指明了數(shù)據(jù)應(yīng)歸入日期為'2023-10-27'的分區(qū)。3.D*解析：`LEFTOUTERJOIN`會保留左表的所有行，即使右表中沒有匹配的行，對于右表沒有匹配的左表行，相關(guān)列會顯示為`NULL`。而`INNERJOIN`只返回兩個表都有匹配的行。數(shù)據(jù)傾斜（DataSkew）是指數(shù)據(jù)分布不均，可能導(dǎo)致某些Reducer處理的數(shù)據(jù)量遠(yuǎn)超其他Reducer，使得`INNERJOIN`在傾斜的鍵上表現(xiàn)異常，可能返回不完整的結(jié)果集，這與右表行數(shù)多少無直接關(guān)系，但傾斜本身就是一種性能問題。4.C*解析：`hive.exec.reducers.max`參數(shù)用于設(shè)置MapReduce任務(wù)允許使用的最大Reduce任務(wù)數(shù)量。這直接影響Reduce階段的并行度。5.D*解析：根據(jù)查詢條件`WHEREuser_id=...`，最有效的索引是在`user_id`列上創(chuàng)建索引。這樣可以快速定位到包含特定用戶ID的訂單記錄。6.C*解析：索引通?；诹械脑贾颠M(jìn)行存儲。當(dāng)在`WHERE`子句中對列應(yīng)用函數(shù)（如`CONCAT(col1)`）時，數(shù)據(jù)庫無法直接利用為`col1`本身創(chuàng)建的索引，因為索引中存儲的是函數(shù)運(yùn)算后的值，導(dǎo)致索引失效，通常需要執(zhí)行全表掃描。7.B*解析：刪除外部表時，只會刪除表的結(jié)構(gòu)（元數(shù)據(jù)），而其數(shù)據(jù)文件會保留在HDFS上原來的路徑下。這是外部表的主要優(yōu)勢之一，保證了數(shù)據(jù)的安全。8.D*解析：`ORDERBY`子句用于對結(jié)果集進(jìn)行排序。`DESC`表示降序排序，`NULLSLAST`表示將`NULL`值排在排序結(jié)果的最后面。9.A*解析：`INNERJOIN`操作基于連接條件匹配到的行對進(jìn)行組合。如果左表某一行在右表中有多個匹配行，`INNERJOIN`會將左表該行與右表的每個匹配行生成一個組合行，因此結(jié)果集的行數(shù)可能大于左表的行數(shù)。其他類型的JOIN（`LEFT`,`RIGHT`,`FULLOUTER`）在處理多匹配時行為不同。10.D*解析：`mapreduce.job.reduces`參數(shù)控制MapReduce作業(yè)的Reduce階段的任務(wù)數(shù)量，即并行度。二、簡答題1.索引的作用是加速數(shù)據(jù)庫表的查詢操作。通過在表的關(guān)鍵列上創(chuàng)建索引（通常是B-Tree索引），數(shù)據(jù)庫引擎可以快速定位到包含特定值的行，從而避免全表掃描，大大提高查詢效率，尤其是在處理大型數(shù)據(jù)表時。索引還可以用于加速排序、分組等操作，并確保數(shù)據(jù)的唯一性（主鍵索引）?？赡軐?dǎo)致索引失效的情況：*在索引列上使用了函數(shù)：如`WHEREUPPER(column_name)='VALUE'`，數(shù)據(jù)庫無法利用該索引。*索引列類型不匹配：如`WHEREcolumn_name='123'`（列定義為整數(shù)）而實際查詢是`WHEREcolumn_name='123'`（列定義為字符串）。*`WHERE`子句中使用了可能導(dǎo)致索引失效的操作：如`LIKE'prefix%'`是有效的，但`LIKE'%suffix'`或`LIKE'%prefix%'`通常會導(dǎo)致前綴索引失效，可能轉(zhuǎn)為全表掃描。*索引列參與計算或運(yùn)算：如`WHEREcolumn_name+10=100`。*數(shù)據(jù)類型轉(zhuǎn)換：如果查詢條件中的數(shù)據(jù)類型轉(zhuǎn)換與索引創(chuàng)建時不同。2.數(shù)據(jù)傾斜是指在分布式計算（如MapReduce或Spark）中，任務(wù)（Map或Reduce）之間處理的數(shù)據(jù)量或計算量分布極不均勻，導(dǎo)致某些任務(wù)執(zhí)行時間遠(yuǎn)長于其他任務(wù)，從而成為整個作業(yè)的瓶頸。導(dǎo)致數(shù)據(jù)傾斜的原因：*鍵值選擇不當(dāng)：在進(jìn)行`GROUPBY`,`JOIN`或`ORDERBY`操作時，某個鍵（Key）的值在所有輸入數(shù)據(jù)中非常集中，導(dǎo)致大量數(shù)據(jù)被映射到同一個Reduce任務(wù)（或Map任務(wù)輸出）。*數(shù)據(jù)本身特性：某些業(yè)務(wù)場景天然導(dǎo)致數(shù)據(jù)傾斜，例如用戶ID、訂單號等字段在特定情況下可能只有少數(shù)幾個值出現(xiàn)頻率極高。常見的解決方法：*參數(shù)調(diào)優(yōu)：增加`mapreduce.job.reduces`（增加Reducer數(shù)量）是常用方法，讓傾斜的鍵分散到更多Reducer中處理。*改寫查詢：對于`GROUPBY`導(dǎo)致的傾斜，可以嘗試先對數(shù)據(jù)進(jìn)行抽樣，找出傾斜的鍵，然后對傾斜的鍵進(jìn)行特殊處理（如拆分），或者使用`CLUSTERBY`（如果適用）。*數(shù)據(jù)預(yù)處理：對傾斜的鍵進(jìn)行加鹽（添加隨機(jī)前綴）處理，將其分散，處理后再去除鹽。3.內(nèi)部表（InternalTable）和外部表（ExternalTable）的主要區(qū)別在于數(shù)據(jù)文件的管理和刪除行為：*內(nèi)部表：數(shù)據(jù)文件與表結(jié)構(gòu)存儲在一起，當(dāng)使用`DROPTABLEtable_name;`刪除表時，表的結(jié)構(gòu)和數(shù)據(jù)文件都會被一起刪除。*外部表：數(shù)據(jù)文件存儲在HDFS上某個指定路徑（表的位置），與表結(jié)構(gòu)分開。刪除外部表時，只會刪除表的結(jié)構(gòu)，數(shù)據(jù)文件仍然保留在原路徑，可供其他表或查詢使用。選擇使用外部表的場景：*數(shù)據(jù)共享：當(dāng)多個用戶或應(yīng)用需要訪問同一組數(shù)據(jù)，但不希望互相干擾時。*避免數(shù)據(jù)丟失：在進(jìn)行表結(jié)構(gòu)修改或重建時，希望保留原始數(shù)據(jù)。*數(shù)據(jù)集成：在數(shù)據(jù)倉庫或數(shù)據(jù)湖環(huán)境中，經(jīng)常需要整合來自不同來源的數(shù)據(jù)，使用外部表可以方便地引用現(xiàn)有數(shù)據(jù)集。4.分區(qū)裁剪（PartitionPruning）是指Hive在執(zhí)行查詢時，能夠識別出查詢條件中涉及的分區(qū)字段，并僅掃描包含符合該條件的數(shù)據(jù)分區(qū)的數(shù)據(jù)文件，而忽略其他不相關(guān)的分區(qū)數(shù)據(jù)文件。這樣可以顯著減少需要讀取的數(shù)據(jù)量，從而提高查詢性能。對查詢優(yōu)化的意義：*提高查詢效率：通過只讀取必要的數(shù)據(jù)，避免了掃描大量不相關(guān)的數(shù)據(jù)文件帶來的I/O開銷。*降低資源消耗：減少了數(shù)據(jù)傳輸和存儲系統(tǒng)的負(fù)載。*簡化查詢：對于經(jīng)常按分區(qū)進(jìn)行篩選的查詢，裁剪可以簡化實現(xiàn)。三、編程題1.```sqlSELECTe.emp_name,e.dept_idFROMemployeeseINNERJOIN(SELECTemp_idFROMsalariesWHEREsal_date>'2023-01-01'GROUPBYemp_id)ASrecent_employeesONe.emp_id=recent_employees.emp_idINNERJOINsalariessONe.emp_id=s.emp_idWHEREs.salary>(SELECTAVG(salary)FROMsalariesWHEREs.dept_id=e.dept_id)```2.```sqlSELECTproduct_id,CONCAT(YEAR(sale_date),'-',LPAD(MONTH(sale_date)/3,1,'Q'))ASquarter,SUM(amount)AStotal_amountFROMsalesWHEREsale_date>='2023-01-01'--確保數(shù)據(jù)在范圍內(nèi)GROUPBYproduct_id,quarterORDERBYproduct_id,quarter```四、案例分析題可能的原因：*索引未被有效利用：盡管`col1`和`col2`上有索引，但Hive可能沒有選擇使用它們。這可能是由于索引選擇性不高（例如，`col1='value1'`的行比例非常高，使得全表掃描更優(yōu)）、執(zhí)行計劃中估算的成本計算錯誤，或者查詢條件中的操作導(dǎo)致索引失效（例如，`col2`的`BETWEEN`條件如果跨越了多個連續(xù)的索引頁，可能導(dǎo)致索引掃描效率不高）。*數(shù)據(jù)分布不均：如果`large_table`中的`col1='value1'`的行數(shù)占總行數(shù)的比例非常?。ń咏?%），Hive可能判斷全表掃描比索引掃描更高效。*統(tǒng)計信息缺失或過時：Hive優(yōu)化器依賴表和索引的統(tǒng)計信息（如列基數(shù)、數(shù)據(jù)分布）來生成執(zhí)行計劃。如果統(tǒng)計信息不準(zhǔn)確或缺失，可能導(dǎo)致優(yōu)化器做出錯誤的選擇。優(yōu)化建議：*檢查并優(yōu)化索引使用：確保`col1='value1'`具有較高的選擇性。檢查`col2BETWEEN'start'A