大数据-hive基本语法整理

hive基本语法

一、hive建表语句

create external tableifnotexists 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 ORDERBY column1 [ASC|DESC], column2 [ASC|DESC]...
示例：按时间字段降序排序
SELECT*FROMtransactionsORDERBY transaction_date DESC;6. 分组和聚合函数查询
SELECT column1,COUNT(column2),SUM(column3)FROM table_name GROUPBY column1;

   示例：统计每个类别的总金额
SELECT category,SUM(amount)AS total_amount 
FROMtransactionsGROUPBY 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 WHEREcolumnIN(SELECTcolumnFROM 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;