大数据-hive基本语法整理

hive基本语法

一、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'
;

语法解析：

• partitioned by (hdfs_date_dt STRING)
  –使用字符串类型字段hdfs_date_dt作为分区键

• row format serde
  ‘org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe’
  with serdeproperties (“field.delim” = “|@|” )
  –指定行格式和序列化/反序列化方式，设置字段分隔符为"|@|"

• stored as textfile
  – 数据以文本文件形式存储，数据存储格式有textfile, orc, parquet等

• location ‘/warehouse/tablespace/external/hive/ods/ods_base_org_info’
  对于外部表，指定表数据在HDFS上的具体路径
• external 代表外部表含义
  外部表特点及含义如下：
  数据位置：外部表的数据存储在HDFS的指定路径下，而不是由Hive直接管理。这意味着用户可以自己决定数据的存放位置，并且即使删除了该外部表，Hive也不会自动删除底层HDFS上的实际数据文件。
  数据所有权：相比于内部表（Managed Table），Hive对外部表的数据不拥有管理权。删除外部表时，仅会移除表的元数据（表结构）信息，但不会删除数据源文件。
  数据共享：由于外部表与底层数据文件解耦，因此这种表特别适用于跨多个Hive表或与其他系统（如Spark、MapReduce等）共享数据的情况。
  重定位数据：若需要移动或调整数据源的位置，只需更改外部表定义中的LOCATION属性即可，而无需重新加载数据。
  跨Hive实例：在不同Hive实例间，可以通过指向同一份HDFS数据源来实现外部表内容的共享。
  Hive升级或迁移：如果需要升级Hive版本或者将数据迁移到其他Hive集群，外部表的数据可以在迁移过程中保持不变，只需要重新创建指向相同HDFS路径的外部表即可。

二、hive insert 插入语句

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意味着会删除目标表原有的对应数据并用新的数据替代

三、hive 查询语句

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');

四、hive 删除语句

1. 删除表中的所有数据（保留表结构）：

   TRUNCATE TABLE database_name.table_name;

2. 删除特定分区的数据：

   ALTER TABLE database_name.table_name DROP IF EXISTS PARTITION
   (partition_column=value[, partition_column=value …]); 示例： ALTER
   TABLE sales DROP IF EXISTS PARTITION (year=2022, month=05);

3. 删除整个表（包括元数据和数据）：

   DROP TABLE [IF EXISTS] database_name.table_name [PURGE]; IF
   EXISTS：如果表不存在，则不抛出错误。 PURGE：彻底删除表，连同其元数据一起从 metastore
   中移除。对于外部表，默认情况下只删除元数据，不删除实际数据文件。 示例： DROP TABLE IF EXISTS
   my_database.my_table PURGE;

4. 删除表的部分行（基于某些条件）
   直接删除部分行在Hive中并不直接支持，但可以通过创建一个新的临时表或视图来实现类似功能，例如先查询需要删除的行，然后插入到新的临时表，最后用新表替换原表。

   CREATE TABLE new_table AS SELECT * FROM old_table WHERE condition_to_exclude_rows;

   – 确保新表数据正确后，重命名或交换表名 ALTER TABLE database_name.old_table RENAME TO old_table_backup; ALTER TABLE database_name.new_table RENAME TO
   old_table;