hive 大小写转换函数 hive区分大小写

规则语法

大小写规则:

1. hive的数据库名、表名都不区分大小写
2. 建议关键字大写

命名规则：

1. 名字不能使用数字开头
2. 不能使用关键字
3. 尽量不使用特殊符号

库操作语法

创建数据库

创建数据库的本质就是在hive的参数${hive.metastore.warehouse.dir}对应的目录下，创建一个新的目录，此目录的名称为： 库名.db。

注意：在创建库或者表时除了创建目录外，还会在mysql中(元数据库)，添加元数据（描述信息）

hive> create database zoo;
hive> create database if not exists zoo;
hive> create database if not exists qfdb comment 'this is a database of mine';

hive有一个默认的数据库default，如果不明确的说明要使用哪个库，则使用默认数据库。

查看所有数据库

语法：show databases;

切换数据库

语法：use mydb;

查看数据库信息

语法1：desc database databaseName;
语法2：desc database extended databaseName;
语法3：describe database extended databaseName;

** 删除数据库**

语法1：drop database databasename;         	#这个只能删除空库
语法2：drop database databasename cascade;    	#如果不是空库，则可以加cascade强制删除

表操作语法

数据类型

Hive的数据类型分为基本数据类型和复杂数据类型,下面是基本数据类型

其中加粗体是重点要掌握的类型

分类	类型	描述	字面量示例
基本类型	BOOLEAN	true/false	TRUE
	TINYINT	1字节的有符号整数 -128~127	1Y
	SMALLINT	2个字节的有符号整数，-32768~32767	1S
	INT	4个字节的带符号整数	1
	BIGINT	8字节带符号整数	1L
	FLOAT	4字节单精度浮点数	1.0
	DOUBLE	8字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，可变长度	“a”,’b’
	VARCHAR	变长字符串,要设置长度	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组	无法表示
	TIMESTAMP	时间戳，纳秒精度	122327493795
	DATE	日期	‘2016-03-29’
复杂类型	ARRAY	有序的的同类型的集合	array(1,2)
	MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
	STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0)
	UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

** 创建表**

创建表的本质其实就是在对应的数据库目录下面创建一个子目录，目录名为表名。数据文件就存在这个目录下。

语法1: 
	create table t_user(id int,name string);  

语法2：使用库.表的形式
	create table mydb.t_user(id int,name string);

语法3：指定分隔规则形式
create table if not exists t1(
uname string comment 'this is name',
chinese int,
math int,
english int
)
comment 'this is my table'
row format delimited   
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

查看当前表空间中的所有表名

语法：show tables; 
# 查看另外一个数据库中的表
show tables in zoo;

查看表结构

desc tableName
desc extended tableName;
describe extended tableName;

修改表结构

- 修改表名
	alter table oldTableName rename to newTableName;

- 修改列名：change column	
	alter table tableName change column oldName newName colType;
- 修改字段类型：
	alter table tableName change column colName colName colType;


- 修改列的位置:  注意，2.x版本后，必须是相同类型进行移动位置。
	alter table tableName change column colName colName colType after colName1;   
	alter table tableName change column colName colName colType first;




- 增加字段：add columns
	alter table tableName add columns (sex int,...);



- 删除字段：replace columns	#注意，2.x版本后，注意类型的问题，替换操作，其实涉及到位置的移动问题。
	alter table tableName replace columns(
    id int,
    name int,
    size int,
    pic string
    );
	注意：实际上是保留小括号内的字段。

删除表

drop table tableName;

扩展）建表语法

下面是一个常见的创建表的语法:

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 [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   
-- (Note: TEMPORARY available in Hive 0.14.0 and later)

[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]

[COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

[SKEWED BY (col_name, col_name, ...)     -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]

[
[ROW FORMAT row_format] 
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
]

[LOCATION hdfs_path]

[TBLPROPERTIES (property_name=property_value, ...)]  
-- (Note: Available in Hive 0.6.0 and later)

[AS select_statement];   
-- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

其中具体创建表的参数类型可以参考如下:

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

数据导入

[root@my01 hive]# mkdir /hivedata
[root@my01 hive]# cd /hivedata
[root@my01 hive]# vi user.txt

加载数据到Hive，一般分为两种:

- 一种是从本地Linux上加载到Hive中
- 另外一种是从HDFS加载到Hive中

方法1使用hdfs dfs -put将本地文件上传到表目录下

hdfs dfs -put ./u1.txt /user/hive/warehouse/mydb1.db/u1

方法在hive中使用load 命令

load data [local] inpath '文件路径' [overwrite] into table 表名

加载数据时：
1. 最好是写绝对路径，从根开始写。
2. 写相对路径也是可以的，但是一定要记住你登录hive时的位置，从当前位置写相对路径
3. ~在hive中，是相对路径的写法
4. 使用benline工具进行远程登录（客户端与服务端不在同一台机器）时，使用以下语句时：
	load data local inpath '文件路径' [overwrite] into table 表名
	会有一个大坑：local是指服务端的文件系统。

方法3从另外一张表(也可称之为备份表)中动态加载数据

insert into table tableName2 select [.....] from tableName1;


扩展内容：向多张表中插入数据的语法
    from tableName1
    insert into tableName2 select * where 条件
    insert into tableName3 select * where 条件
    .....

注意：

**方法4：**克隆表数据

- create table if not exists tableName2 as select [....] from tableName1;
- create table if not exists tableName2 like tableName1 location 'tableName1的存储目录的路径'

扩展内容：只复制表结构
create table if not exists tableName2 like tableName1;

加载数据的本质：

1. 如果数据在本地，加载数据的本质就是将数据copy到hdfs上的表目录下。
2. 如果数据在hdfs上，加载数据的本质是将数据移动到hdfs的表目录下。

注意:hive使用的是严格的读时模式：加载数据时不检查数据的完整性，读时发现数据不对则使用NULL来代替。
而mysql使用的是写时模式:在写入数据时就进行检查