Hive基本用法

1，建库建表

create database if not exists financials;
show databases;
describe database financials;
use financials;
drop database if exists financials;
drop database if exists financials cascade;

create table mytest.student (
 name string comment '姓名',
 sex boolean comment '性别',
 height tinyint comment '身高',
 weight float comment  '体重',
 age int comment '年龄',
 courses bigint comment '课程数',
 charge decimal comment '费用',
 scholarship double comment '奖学金',
 in_school date comment '入学日期',
 birth timestamp comment '出生日期',
 class smallint comment '班号'
)
comment '学生'
partitioned by (par string comment '默认分区字段')
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by '）'
stored as textfile
tblproperties ('fileformat'='textfile');

show partitions c_terminal_day_table;
drop table if exists c_terminal_day_table;
alter table c_terminal_day_table rename to c_terminal;

create table employees (
    name string,
    salary float,
    subordinates array<string>,
    deductions map<string, float>,
    address struct<street:string, city:string, state:string, zip:int>
) partitioned by (country string, state string);

 

2，建表说明

2.1 字段类型

基本类型：string, tinyint, smallint, int, bigint, decimal, float, double, boolean, timestamp, date, binary, char, varchar
复杂类型：struct, map, array, union

2.2 分隔符 

字段分隔符
行分隔符
集合元素间分隔符
map键值间分隔符

2.3 文件存储格式

textfile, sequencefile, rcfile, orc, parquet

 

3，导入

load data local inpath '/var/lib/hdfs/usr/test/0001.csv'
 into table employees
 partition (country = 'US', state = 'CA'); 

sqoop import --connect "jdbc:oracle:thin:@112.34.202.2:1521:rcrc" --password "myrc" --username "myrc" --delete-target-dir --table "user_info" --hive-import --hive-database "dc" --hive-table "h_user_info"

 

4，导出

hadoop fs -cp source_path target_path

insert overwrite local directory '/tmp/ca_employees'
    select name, salary, address
    from employees
    where se.state = 'CA'

sqoop export --connect jdbc:mysql://appmysqlsvc/myrc --username myrc --password myrc --table user_info --export-dir /li/usr/export/user_info

 

5，插入&查询