Hive基础知识（一）

    # 启动hiveserver2
    hive --service hiveserver2 &
    # 启动metastore
    hive --service metastore &
    
    
      
      
      
      
    

    # 方法一：beeline方式（推荐）
    beeline -u jdbc:hive2://node03:10000 -n root
    # 方法二：hive cli方式
    hive
    
    
      
      
      
      
    

    create database if not exists db_hive 
    comment '测试数据库建库语句'
    with DBPROPERTIES('propertyname'='stan');
    
    
      
      
      
    

    show databases;
    
    
      
    

    use db_hive;
    
    
      
    

    select current_database();
    
    
      
    

    desc database db_hive;
    desc database extended db_hive;
    
    
      
      
    

    drop database if exists db_hive;
    
    
      
    

    CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
    [<database_name>.]<table_name>
    [(<col_name> <data_type> [COMMENT '<col_comment>'] [, <col_name> <data_type> ...])]
    [COMMENT '<table_comment>']
    [PARTITIONED BY (<partition_key> <data_type> [COMMENT '<partition_comment>']
    [, <partition_key > <data_type>...])]
    [CLUSTERED BY (<col_name> [, <col_name>...])
    [SORTED BY (<col_name> [ASC|DESC] [, <col_name> [ASC|DESC]...])]
    INTO <num_buckets> BUCKETS]
    [
    [ROW FORMAT <row_format>]
    [STORED AS file_format]
    | STORED BY '<storage.handler.class.name>' [WITH SERDEPROPERTIES (<...>)]
    ]
    [LOCATION '<file_path>']
    [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    

    drop table if exists tbl_inner;
    create table tbl_inner(
    stu_id int comment '学生id',
    stu_name string comment '学生姓名'
    ) comment '学生表';
    
    
      
      
      
      
      
    

    # 查看表的元数据信息
    desc formatted tbl_inner;
    
    
      
      
    

    #  tbl_outer.txt 上传到hdfs
    hadoop fs -mkdir -p /tmp/db_hive/tbl_outer
    hadoop fs -put tbl_outer.txt /tmp/db_hive/tbl_outer/
    
    
      
      
      
    

    -- 对这些数据创建外表
    drop table if exists tbl_outer;
    create external table tbl_outer(
    stu_id int comment '学生id',
    stu_name string comment '学生姓名',
    subject array<string> comment '科目',
    address map<string,string> comment '籍贯'
    )comment '个人信息表'
    row format delimited fields terminated by ','
    collection items terminated by '-'
    lines terminated by '\n'
    location '/tmp/db_hive/tbl_outer/'
    ;
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
    

    -- 查看表结构并查看数据
    desc tbl_outer;
    select * from tbl_outer;
    
    
      
      
      
    

    -- 查看表的详细元数据信息
    desc formatted tbl_outer;
    
    
      
      
    

    create temporary table tbl_tmp(
    stu_id int,
    stu_name string
    );
    
    
      
      
      
      
    

    create table tbl_like like tbl_inner;
    
    
      
    

    create table tbl_as as select * from tbl_inner;
    
    
      
    

    drop table tbl;
    
    
      
    

    /* 表重命名 */
    ALTER TABLE <table_name> RENAME TO <new_table_name>;
    /* 修改表属性 */
    ALTER TABLE <table_name> SET TBLPROPERTIES ('<property_name>' = '<property_value>' ... );
    ALTER TABLE <table_name> SET SERDEPROPERTIES ('<property_name>' = '<property_value>' ... );
    ALTER TABLE <table_name> SET LOCATION '<new_location>';
    /* 增加、删除、修改、替换列 */
    ALTER TABLE <table_name> ADD COLUMNS (<col_spec> [, <col_spec> ...])
    ALTER TABLE <table_name> DROP [COLUMN] <col_name>
    ALTER TABLE <table_name> CHANGE <col_name> <new_col_name> <new_col_type>
    ALTER TABLE <table_name> REPLACE COLUMNS (<col_spec> [, <col_spec> ...])
    
    
      
      
      
      
      
      
      
      
      
      
      
    

    truncate table tbl;
    
    
      
    

    desc tbl;
    
    
      
    

    show tables;