Hive数据类型和建表（0923）

---

Hive原始数据类型

Type	Example
tinyint	10Y
smallint	10S
int	10
bigint	10L
float	3.14
double	3.14
decimal	3.14
binary	1010
boolean	true
char	'a' or "a"
varchar	'a' or "a"
string	'yes' or "yes"
date	'2018-07-21'
timestamp	'2018-09-21 10:25:12.123'

复杂数据类型

Type	Example	Define	Example
array	['a', 'b', 'c']	array<string>	a[0]='a'
map	{'name':'steven','gender':'male'}	map<string,string>	m['name']='steven'
struct	{'apple',1}	struct<fruit:string, weight:int>	s.fruit='orange'

基本建表语句

create external table if not exists employee_external(
       name string,
       work_place array<string>,
       sex_age struct<sex:string,age:int>,
       skills_score map<string,int>,
       depart_title map<string,array<string>>
    )
    row format delimited
    fields terminated by '|'
    collection items terminated by ','
    map keys terminated by ':'
    stored as textfile
    location '/temp/data/employee'
    tblproperties("skip.header.line.count"="1")

高级建表语句

1. CTAS -- create table as select

create table temp_users as select * from employee_external;

2. create table like another table (fast)

create table employee_external_01 like employee_external;

3. CTAS with Common Table Expression(CTE)

create table cte_employee_external as 
with 
r1 as (select name from r2 where name='steven')
r2 as (select name from employee_external where sex_age.sex='male')
r3 as (select name from employee_external where sex_age.sex='female')
select * from r1 union all select * from r3;

注： CTAS不能创建分区表，分桶表，外表

4. 创建临时表

create temporary table temp_user_01(name string);
create temporary table temp_user_02 as select * from users;
create temporary table temp_user_03 like users;

注：

往hive表中导入数据

// 把本地数据复制到users表中（追加）
load data local inpath '/root/data/users.csv' into table users;  
// 把本地数据复制到users表中（覆盖）
load data local inpath '/root/data/user.csv'  overwrite into table users; 
 // 把HDFS中的数据移动到users表中（追加）
load data inpath '/temp/data/users.csv' into table users;
// 把HDFS中的数据移动到users表中（覆盖)
load data inpath '/temp/data/users.csv'  overwrite into table users;