Hive常用表操作语句

布满荆棘的人生 2024-04-19 15:27 11阅读 0赞

#### 常用操作 ####

1.  简单表

# 创建
    create table erp_leon_stu_simple (name string, age int) 
    # 查看表存储位置 
    desc formatted erp_leon_stu_simple

![erp\_leon\_stu\_simple formatted][erp_leon_stu_simple formatted]  
2. 外部表

# 
    desc formatted erp_leon_stu_simple
    create external table erp_leon_stu_external (name string, age int)
    comment "this is erp_leon_stu_external"
    row format delimited fields terminated by '\054'
    stored as textfile
    location '/usr/erp_stu_external'
    # 查看表存储位置 
    desc formatted erp_leon_stu_external

![查看外部表formatted][formatted]

1.  插入数据

insert into table erp.erp_leon_stu_simple select 'name',18 from erp_ssa.erp_leon
    insert into table erp.erp_leon_stu_external select 'name',18 from erp.erp_leon_stu_simple

![simple][]  
![external][]  
4. 删除表

drop table if exists erp.erp_leon_stu_simple;
    drop table if exists erp.erp_leon_stu_external;
    
    select * from  erp.erp_leon_stu_simple;
    select * from  erp.erp_leon_stu_external;
    
    Line 1:15 Table not found 'erp_leon_stu_external'
    
    #再次创建
    create table erp.erp_leon_stu_simple (name string, age int)
    
    
    create external table erp.erp_leon_stu_external (name string, age int)
    comment "this is erp_leon_stu_external"
    row format delimited fields terminated by '\054'
    stored as textfile
    location '/usr/erp_stu_external'
    
    select * from  erp.erp_leon_stu_simple;
    select * from  erp.erp_leon_stu_external;
    # 查询结果分别如下：

![在这里插入图片描述][20190919153626895.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MDE4Mjgz_size_16_color_FFFFFF_t_70]

''' 外部表删除不删除存储数据，只删除元数据 ，外部表 存储的数据在自己定义的HDFS路径上 内部表数据由Hive自身管理，外部表数据由HDFS管理； 内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定； 删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除； 对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name; '''

1.  建分区表

create external table erp.erp_leon_stu_par (name string, age int)
    comment "this is erp_leon_stu_par"
    partitioned by (age int)
    row format delimited fields terminated by ', '
    location '/usr/erp_leon_stu_par'
    
    # 分区表插入数据
    insert into table erp.erp_leon_stu_par select 'name',18 from erp.erp_leon_stu_external
    
    # 报错，提示需要确定分区
    SemanticException 1:18 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'erp_leon_stu_par'
    
    # 分区表插入数据
    insert into table erp.erp_leon_stu_par  PARTITION (date=10,pos=1) select 'name',18 from erp.erp_leon_stu_external
    
    select * from erp.erp_leon_stu_par  WHERE DATE =10

![分区表插入数据][20190919153818803.png]

# 显示所有表
    show tables;
    
    # 表增加一列
    alter table erp.erp_leon_stu_simple add columns(address string)
    
    # 更改表名
    alter table erp.erp_leon_stu_simple  rename to erp.erp_leon_stu_sim
    
    # 加载 文件数据 到 数据表
    LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;
    
    #load 是把文件 cpoy 到 Hive表的存储位置。
    
    # 给定分区
    LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
    
    # 重写 
    INSERT OVERWRITE TABLE tablename1 partition(date=11,pos=2) select_statement1 FROM from_statement
    
    # 查询结果插入到Hive 表 
    INSERT INTO TABLE tablename1 partition(date=11,pos=2) select_statement1 FROM from_statement

1.  JOIN  
    ![原来数据][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MDE4Mjgz_size_16_color_FFFFFF_t_70 1]  
    ![原来数据][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MDE4Mjgz_size_16_color_FFFFFF_t_70 2]  
    ![Join结果][Join]

select * from erp.erp_leon_stu_simple a  join erp.erp_leon_stu_simple1 b on (a.name = b.name and a.age=b.age)

![name 和 age 都相等的  4\*4 = 16][name _ age _ 4_4 _ 16]

#### SQL 和 HQL 的转变习惯 ####

''' 1、 Hive不支持等值连接 2、 HIve 看到 ； 就认为结束了。 '\073' 使用这个就好了 3、 Hive 中的空字符串is null 判断为 false 4、 Hive 可以通过配置开启修改和删除数据，但是速度没有传统关系型数据库快 需要： 表结构需要分桶bucket; 文件格式需要用 ORC格式; 在建表的最后加上 tblproperties('transactional'='true') 来增加事务支持 配置- SET hive.support.concurrency = true; SET hive.enforce.bucketing = true; SET hive.exec.dynamic.partition.mode = nonstrict; SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; SET hive.compactor.initiator.on = true; SET hive.compactor.worker.threads = 1; '''

[erp_leon_stu_simple formatted]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/dd74f4fcd75d428e8177a514fed88358.png
[formatted]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/1420f81bca6f4c04884eb9c220d23e1f.png
[simple]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d0c41a82cd254e38933aba2fa77f7b39.png
[external]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/ad1def1ef1354cf1a06c0ef21e2c3f74.png
[20190919153626895.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/40be3204cdb54a4c89000afd90a47be3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MDE4Mjgz_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/4632c30854cd4a209f02142daaea87d2.png
[20190919153818803.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/cefde481fc06470fab6240153401f4ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MDE4Mjgz_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d73fc23828574a81b5156eb296610f78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MDE4Mjgz_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/9385fc39485e497cb2da6e41e0f28d23.png
[Join]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/1a8b0127381d452fa9958f29049f809a.png
[name _ age _ 4_4 _ 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/f9835513f6264a6eb81f43dd5536a0bf.png