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' ;
在执行INSERT OVERWRITE时,请确保目标表结构与源表查询结果匹配,包括列的数量、类型以及对于分区表来说的分区键值。同时,使用OVERWRITE意味着会删除目标表原有的对应数据并用新的数据替代
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');