Hive常用命令

Hive常用命令

Hive语法结构

usage: hive
 -d,--define <key=value>          Variable subsitution to apply to hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable subsitution to apply to hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

Hive数据库管理

创建数据库

• 创建school数据库

  create database if not exits school;
  

• 如果school数据库不存在就创建，并设置注释

  create database if not exists school commnet 'school';
  

Hive默认数据库字符集为latin1

删除数据库

• 删除school数据库

  drop database school;
  

  不能删除非空数据库，错误示例如下

  hive> drop database school;
  FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. 
  InvalidOperationException(message:Database school is not empty. One or more tables exist.)
  

• 删除非空数据库

  drop database school cascade;
  

查看数据库

• 查看所有数据库

  show databases;
  

• 模糊查询数据库

  • 查找school开头的数据库

    show databases like 'school*';
    

• 查看数据库结构

  • 命令

  desc database school;
  

  • 结果

  school	hdfs://node4.co:9000/user/hive/warehouse/school.db	root	USER	
  Time taken: 0.163 seconds, Fetched: 1 row(s)
  

查看库描述

desc

desc database extended school;

describe

describe database extended school;

Hive表管理

创建表

语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] TABLENAME
        [COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN COMMENT'],...]
        [COMMENT 'TABLE COMMENT']
        [PARTITIONED BY (COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN COMMENT'],...)]
        [CLUSTERED BY (COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN COMMENT'],...) [SORTED BY (COLUMNNAME [ASC|DESC])...] INTO NUM_BUCKETS BUCKETS]
        [ROW FORMAT ROW_FORMAT]
        [STORED AS FILEFORMAT]
        [LOCATION HDFS_PATH];

实例

• 创建学生表 简单方式创建

  create table student(
   sid string comment '学号',
   sname string comment '学生姓名',
   sex string comment '学生性别'
  ) stored as textfile;
  

• 创建学生表 复杂方式创建

  create table if not exists student(
   sid string comment '学号',
   sname string comment '学生姓名',
   sex string comment '学生性别'
  ) 
  -- 指定行格式分隔
  row format delimited
  -- 指定字段分隔符
  fields terminated by ","
  -- 指定行分隔符
  lines terminated by "\n"
  -- 指定hive文件存储格式
  stored as textfile;
  

• 创建外部表

  内部表和外部表在创建时的区别：EXTERNAL LOCATION

  create table if not exists userinfo(
      userid string,
      username string
  ) location '/data/school';
  

• 创建内部表

  create table if not exists userinfo(
      userid string,
      username string
  );
  

删除表

• 删除学生表

  drop table `student`;
  

修改表

• 语法

  ALTER TABLE name RENAME TO new_name
  ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
  ALTER TABLE name DROP [COLUMN] column_name
  ALTER TABLE name CHANGE column_name new_name new_type
  ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])
  

• 学生表增加tel字段

  alter table student
  add columns(tel string);
  

• 学生表增加sex,age字段

  alter table student add columns(sex string,age int);
  

查看表

• 查看当前数据库中的表

  show tables;
  

• 查看其他数据库中的表

  show tables in `default`;
  

• 查看表结构

  desc student;
  

  • 结果

    hive> desc student;
    OK
    sid                 	string              	??                  
    sname               	string              	????                
    sex                 	string              	????                
    tel                 	string              	                    
    Time taken: 0.099 seconds, Fetched: 4 row(s)
    hive> 
    
    

• 查看详细信息

  desc formatted student;
  

  • 结果

  hive> desc formatted student;
  OK
  # col_name            	data_type           	comment             
  	 	 
  sid                 	string              	??                  
  sname               	string              	????                
  sex                 	string              	????                
  tel                 	string              	                    
  	 	 
  # Detailed Table Information	 	 
  Database:           	school              	 
  Owner:              	root                	 
  CreateTime:         	Wed Mar 24 10:26:52 CST 2021	 
  LastAccessTime:     	UNKNOWN             	 
  Protect Mode:       	None                	 
  Retention:          	0                   	 
  Location:           	hdfs://node4.co:9000/user/hive/warehouse/school.db/student	 
  Table Type:         	MANAGED_TABLE       	 
  Table Parameters:	 	 
  	COLUMN_STATS_ACCURATE	false               
  	last_modified_by    	root                
  	last_modified_time  	1616554321          
  	numFiles            	0                   
  	numRows             	-1                  
  	rawDataSize         	-1                  
  	totalSize           	0                   
  	transient_lastDdlTime	1616554321          
  	 	 
  # Storage Information	 	 
  SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
  InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
  OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
  Compressed:         	No                  	 
  Num Buckets:        	-1                  	 
  Bucket Columns:     	[]                  	 
  Sort Columns:       	[]                  	 
  Storage Desc Params:	 	 
  	field.delim         	,                   
  	line.delim          	\n                  
  	serialization.format	,                   
  Time taken: 0.066 seconds, Fetched: 38 row(s)
  hive> 
  
  

• 查看创建表信息

  show create table student;
  

  • 结果

  hive> show create table student;
  OK
  CREATE TABLE `student`(
    `sid` string COMMENT '??', 
    `sname` string COMMENT '????', 
    `sex` string COMMENT '????', 
    `tel` string)
  ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY '\n' 
  STORED AS INPUTFORMAT 
    'org.apache.hadoop.mapred.TextInputFormat' 
  OUTPUTFORMAT 
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  LOCATION
    'hdfs://node4.co:9000/user/hive/warehouse/school.db/student'
  TBLPROPERTIES (
    'COLUMN_STATS_ACCURATE'='false', 
    'last_modified_by'='root', 
    'last_modified_time'='1616554321', 
    'numFiles'='0', 
    'numRows'='-1', 
    'rawDataSize'='-1', 
    'totalSize'='0', 
    'transient_lastDdlTime'='1616554321')
  Time taken: 0.497 seconds, Fetched: 23 row(s)
  
  

Hive数据管理

加载数据

• 加载本地数据

  load data local inpath "/opt/data/test/school/student.csv" overwrite into table student;
  

• 加载hdfs数据

  load data inpath "/opt/data/test/school/student.csv" overwrite into table student;
  

insert into方式灌入数据

insert into sinfo
select * from student;

克隆表

不带数据，只克隆表的结构

create table if not exits s1info like student;

克隆表并带数据（加载文件数据）

克隆当前数据库的student表

create table if not exits s2info like student location '/user/hive/warehouse/school.db/student';

克隆其他数据库的student表

create table if not exits s2info like student location '/user/hive/warehouse/jxsf.db/student';

克隆表并带数据（加载表数据）

create table s3info
as
select * from student;

Hive修改表属性

修改表名

将userinfo1表名修改为userinfonew

alter table userinfo1 rename to userinfonew;

修改列名

将userinfo1表的username字段重命名为uname

alter table userinfonew change column username uname string;

修改列位置

将userinfonew表的age字段位置修改为第一

alter table userinfonew change column age age int first;

将userinfonew表的age字段位置修改为uname字段后面

alter table userinfonew change column age age int after uname;

修改字段类型

将userinfonew表的userid字段修改为string类型

alter table userinfonew change column userid userid string;

增加字段

userinfonew表增加sex和age字段

alter table userinfonew add columns(sex string,age int);

删除字段

删除userinfonew表的t1字段

alter table userinfonew replace columns(userid string,uname string,age int,sex string);

内部表和外部表转换

内部表转外部表（true必须大写）

alter table userinfonew set tblproperties('EXTERNAL'='TRUE');

外部表转内部表（false大小写都可以）

alter table userinfonew set tblproperties('EXTERNAL'='false')

显示当前库

• 第一种方法

  • 修改配置文件

    <property>
    	<name>hive.cli.print.current.db</name>
    	<value>false</value>
    	<description>Whether to include the current database in the Hive prompt.</description>
    </property>
    

• 第二种方法

  • 设置参数，仅当前session有效

    set hive.cli.print.current.db=true;
    

查看当前设置值

set hive.cli.print.current.db;

Hive Shell技巧

查看所有hive参数

shell中输入set命令,可以看到hive已经设定好的参数

set;

单独执行hive命令

查询一次数据

hive -e "select * from school.student";

通过外部命令快速查询某个变量值

hive -S -e "set";

单独执行sql文件

hive -f student.sql

在shell内部 可以通过source命令来执行一个sql

在hive中执行Linux命令

在Hive的shell中 加上前缀! 最后以分号;结尾,可以执行linux的命令

查看当前目录

!pwd;

在hive中执行hdfs命令

用户可以在Hive的shell中执行HDFS的DFS命令,不用敲入前缀hdfs或者hadoop

dfs -ls /;

使用历史命令和自动补全

在Hive的Shell操作中可以使用上下箭头查看历史记录

用tab键可以进行命令补全