大数据-hive基本语法整理

create external table if not exists ods_base_org_info
(
 id                   string comment '主键',
 org_code             string comment '组织编码',
 org_code_out         string comment '对应主数据编码',
 org_name             string commet '机构编码',
 org_simple_name      string comment '中文简称',
)
comment '组织机构信息表'
partitioned by (hdfs_date_dt string)
row format serde 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' 
with serdeproperties ("field.delim"="|@|")
stored as textfile
location '/warehouse/tablespace/external/hive/ods/ods_base_org_info'
;

insert overwrite table  dwd.dwd_base_org_info partition(hdfs_date_dt = 'HDFS_DATE_DT')
select 
id,
 org_code,
 org_code_out,
 org_name,
 org_simple_name
from ods.ods_base_org_info 
where hdfs_date_dt = '2023-12-31' ;

1. 基本的全表查询
SELECT * FROM table_name;

2. 查询特定列
SELECT column1, column2, ... FROM table_name;

3. 别名使用
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

4. 条件查询
SELECT * FROM table_name WHERE condition;
示例：查询所有amount大于100的记录
SELECT * FROM sales_table WHERE amount > 100;

 5. 排序查询
SELECT * FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC] ...
示例：按时间字段降序排序
SELECT * FROM transactions ORDER BY transaction_date DESC;

6. 分组和聚合函数查询
SELECT column1, COUNT(column2), SUM(column3) 
FROM table_name GROUP BY column1;

   示例：统计每个类别的总金额
SELECT category, SUM(amount) AS total_amount 
FROM transactions 
GROUP BY category;

7. 连接查询（JOIN）
SELECT a.column1, b.column2 
FROM table1 AS a 
JOIN table2 AS b 
ON a.common_column = b.common_column;

    示例：连接两个表，根据id匹配数据
SELECT t1.id, t1.name, t2.address 
FROM customers t1 
JOIN addresses t2 
ON t1.customer_id = t2.customer_id;

8. 子查询
SELECT * FROM table_name WHERE column IN (SELECT column FROM another_table);

   示例：查找与某个城市客户相关的订单
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT customer_id FROM customers 
    WHERE city = 'New York');