hive复杂类型建表插值

[基本类型]

	TINYINT			//byte 1
	SMALLINT		//short 2
	INT			//int 4
	BIGINT			//long 8
	FLOAT			//float 4
	DOUBLE			//double 8
	DECIMAL			//decimal 精度和刻度decimal(10,3)
	BINARY			//二进制
	BOOLEAN			//TRUE | FALSE
	STRING			//字符串
	CHAR			//定长<= 255  10补空格
	VARCHAR			//变长<=65355.
	DATE			//日期'2013-01-01'
	TIMESTAMP		//时间戳'2013-01-01 12:00:01.345'

2.[复杂类型]

    //字符串
	CHAR			//定长<= 255  10补空格
	VARCHAR			//变长<=65355.
	DATE			//日期'2013-01-01'
	TIMESTAMP		//时间戳'2013-01-01 12:00:01.345'

数据在/root下emp.txt

Michael|Montreal,Toronto|Male,30|DB:80|Product:Developer,Lead
Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Shelley|New York|Female,27|Python:80|Test:Lead,COE:Architect

创建复杂类型变结构

CREATE external TABLE empwb(name string,address ARRAY<string>,base STRUCT<sex:string,age:int>,map1 MAP<string,int>,
map2 MAP<string,ARRAY<string>>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '\n' STORED AS TEXTFILE;

通过/root/emp.txt文件导入到emp表中

load data local inpath '/root/emp.txt' into table emp;

通过hdfs上的emp.txt文件导入到emp表中(hdfs上的文件会被剪贴)

load data inpath '/root/emp.txt' into table emp;

查看表数据
查看数组address 查看结构体base 查看map1 查看map2

select name,address[0],address[1],base.sex,base.age,map1["DB"],map2["Test"] from emp;

创建表并复制表结构

create table emp02 like emp;

创建另一张表数据以及结构（通过一张内部表不可以创建一张外部表）

create table emp07 as select name,address,base,map1,map2 from emp;

修改表名

alter table employee rename to emp ;

mysql中查看hive表

select * from TBLS;

内部表
managed
		删除表时，源数据也被删除。文件会在hdfs上存在的。

外部表
	external
	删除表时，数据不会删除，只删除表结构。常用