SQL-Hive中Select From怎么用

# SQL-Hive中Select From怎么用

## 一、基礎語法與概念

### 1.1 SELECT FROM語句的核心作用
SELECT FROM是HiveQL（Hive Query Language）中最基礎且最常用的查詢語句，主要用于：
- 從Hive表中提取特定列的數據
- 配合WHERE等子句實現條件過濾
- 作為復雜查詢的基礎構建塊

基本語法結構：
```sql
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list]
[LIMIT number];

1.2 Hive與傳統SQL的差異

1. 執行環境：運行在Hadoop集群而非傳統數據庫
2. 延遲特性：高延遲（分鐘級）而非交互式查詢
3. 數據規模：針對TB/PB級數據優化
4. 擴展語法：支持分區、分桶等特有操作

二、基礎查詢示例

2.1 單表查詢

-- 查詢所有列
SELECT * FROM employees;

-- 查詢特定列
SELECT employee_id, name, department 
FROM employees;

-- 使用列別名
SELECT employee_id AS id, 
       name AS "員工姓名",
       salary*12 AS annual_salary
FROM employees;

2.2 使用DISTINCT去重

-- 獲取不重復的部門列表
SELECT DISTINCT department FROM employees;

-- 多列組合去重
SELECT DISTINCT department, job_title 
FROM employees;

三、條件過濾（WHERE子句）

3.1 比較運算符

-- 基本比較
SELECT * FROM sales 
WHERE amount > 1000;

-- 日期比較（Hive需使用特定格式）
SELECT * FROM orders 
WHERE order_date >= '2023-01-01';

-- NULL值判斷
SELECT * FROM customers 
WHERE phone_number IS NOT NULL;

3.2 邏輯運算符

-- AND/OR組合
SELECT * FROM products 
WHERE category = 'Electronics' 
AND price BETWEEN 500 AND 2000;

-- NOT運算符
SELECT * FROM employees 
WHERE NOT department = 'HR';

3.3 特殊操作符

-- IN運算符
SELECT * FROM students 
WHERE grade IN ('A', 'B');

-- LIKE模糊匹配
SELECT * FROM products 
WHERE product_name LIKE '%Pro%';

-- RLIKE正則匹配
SELECT * FROM logs 
WHERE message RLIKE 'error[0-9]+';

四、結果排序與限制

4.1 ORDER BY排序

-- 單列排序
SELECT * FROM employees 
ORDER BY hire_date DESC;

-- 多列排序
SELECT * FROM sales 
ORDER BY region ASC, amount DESC;

4.2 LIMIT限制結果

-- 返回前10條記錄
SELECT * FROM large_table 
LIMIT 10;

-- 分頁模擬（Hive 2.0+）
SELECT * FROM table 
ORDER BY id 
LIMIT 10 OFFSET 20;

五、聚合函數與分組

5.1 常用聚合函數

SELECT 
  COUNT(*) AS total_employees,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary,
  MIN(salary) AS min_salary,
  SUM(salary) AS total_payroll
FROM employees;

5.2 GROUP BY分組

-- 按部門統計
SELECT 
  department,
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- 多列分組
SELECT 
  department, 
  job_level,
  COUNT(*) 
FROM employees
GROUP BY department, job_level;

5.3 HAVING子句

-- 篩選分組結果
SELECT 
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 10000;

六、Hive特有功能

6.1 分區查詢優化

-- 查詢特定分區（顯著提升性能）
SELECT * FROM sales 
WHERE dt='2023-01-15' 
AND region='APAC';

-- 顯示分區信息
SHOW PARTITIONS sales;

6.2 分桶查詢

-- 創建分桶表示例
CREATE TABLE bucketed_users (
  id INT,
  name STRING
) CLUSTERED BY (id) INTO 4 BUCKETS;

-- 對分桶列使用WHERE條件
SELECT * FROM bucketed_users 
WHERE id = 123;

6.3 抽樣查詢

-- 按百分比抽樣
SELECT * FROM huge_table 
TABLESAMPLE(10 PERCENT);

-- 按數據量抽樣
SELECT * FROM huge_table 
TABLESAMPLE(100 ROWS);

七、復雜查詢示例

7.1 子查詢

-- WHERE子句中的子查詢
SELECT * FROM employees 
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

-- FROM子句中的子查詢
SELECT dept.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) dept
WHERE dept.avg_salary > 8000;

7.2 公用表表達式(CTE)

WITH 
dept_stats AS (
  SELECT department, COUNT(*) AS emp_count
  FROM employees
  GROUP BY department
),
high_emp_depts AS (
  SELECT department 
  FROM dept_stats 
  WHERE emp_count > 50
)
SELECT * FROM employees
WHERE department IN (
  SELECT department FROM high_emp_depts
);

八、性能優化建議

1. 列裁剪：避免SELECT *，只查詢必要列 “`sql – 不推薦 SELECT * FROM large_table;

– 推薦 SELECT id, name, status FROM large_table;

2. **分區裁剪**：充分利用分區字段過濾
   ```sql
   -- 高效查詢
   SELECT * FROM partitioned_table 
   WHERE year=2023 AND month=6;

1. 合理使用并行：

   SET hive.exec.parallel=true;
   SET hive.exec.parallel.thread.number=8;
   

2. JOIN優化：

   -- 確保大表在JOIN右側
   SELECT /*+ MAPJOIN(small) */ a.* 
   FROM big_table a JOIN small_table b 
   ON a.id = b.id;
   

九、常見問題排查

1. 數據傾斜：

   -- 檢查鍵值分布
   SELECT key_column, COUNT(*) 
   FROM table 
   GROUP BY key_column 
   ORDER BY 2 DESC LIMIT 10;
   

2. 內存配置：

   SET hive.map.aggr.hash.percentmemory=0.5;
   SET mapreduce.reduce.memory.mb=4096;
   

3. 執行計劃分析：

   EXPLN EXTENDED 
   SELECT count(*) FROM large_table;
   

十、最佳實踐總結

1. 查詢設計原則：

   • 先過濾后處理（WHERE在GROUP BY之前）
   • 減少中間數據量
   • 合理利用分區和索引

2. 格式規范建議：

   -- 良好的格式化示例
   SELECT 
    e.employee_id,
    e.name,
    d.department_name
   FROM 
    employees e
    JOIN departments d ON e.dept_id = d.dept_id
   WHERE 
    e.status = 'ACTIVE'
    AND e.hire_date > '2020-01-01'
   ORDER BY 
    e.salary DESC
   LIMIT 100;
   

3. 監控與調優：

   • 定期分析慢查詢
   • 監控資源使用情況
   • 根據數據變化調整分區策略

通過掌握這些SELECT FROM的使用技巧，您將能夠高效地從Hive中提取所需數據，為大數據分析奠定堅實基礎。 “`