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")
高级建表语句
- CTAS -- create table as select
create table temp_users as select * from employee_external;
- create table like another table (fast)
create table employee_external_01 like employee_external;
- 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不能创建分区表，分桶表，外表
- 创建临时表
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;
