Hive快速入门

Hive快速入门

@(HIVE)[hive]

（一）简单入门

1、创建一个表

create table if not exists ljh_emp(
name string,
salary float,
gender string)
comment 'basic information of a employee'
row format delimited fields terminated by ',’;

2、准备数据文件
创建test目录且目录只有一个文件，文件内容如下：

ljh,25000,male
jediael,25000,male
llq,15000,female

3、将数据导入表中

load data local inpath '/home/ljhn1829/test' into table ljh_emp;

4、查询表中的内容

select * from ljh_emp;
OK
ljh    25000.0    male
jediael    25000.0    male
llq    15000.0    female
Time taken: 0.159 seconds, Fetched: 3 row(s)

（二）关于分隔符

1、默认分隔符
hive中的行默认分隔符为 \n，字段分隔符为 ctrl+A，此外还有ctrl+B，ctrl+C，可以用于分隔array,struct,map等，详见《hive编程指南》P44。
因此，若在建表是不指定row format delimited fields terminated by ‘,’，则认为默认字段分隔符为ctrl+A。
可以有2种解决方案：
一是在创建表时指定分隔符，如上例所示，
二是在数据文件中使用ctrl+A，见下例

2、在数据文件中使用ctrl+A全分隔符
（1）创建表
create table ljh_test_emp(name string, salary float, gender string);
（2）准备数据文件
创建test2目录，目录下只有一个文件，文件内容如下：
ljh^A25000^Amale
jediael^A25000^Amale
llq^A15000^Afemale
其中的^A字符仅在vi时才能看到，cat不能看到。
输出^A的方法是：在vi的插入模式下，先按ctrl+V，再按ctrl+A
（3）将数据导入表
create table ljh_test_emp(name string, salary float, gender string);
（4）查询数据

hive> select * from ljh_test_emp;
OK
ljh    25000.0    male
jediael    25000.0    male
llq    15000.0    female
Time taken: 0.2 seconds, Fetched: 3 row(s)

3、未指定分隔符，且又未使用ctrl+A作文件中的分隔符，出现以下错误
(1)创建表

create table if not exists ljh_emp_test(
name string,
salary float,
gender string)
comment 'basic information of a employee’;

（2）准备数据

ljh,25000,male
jediael,25000,male
llq,15000,female

（3）将数据导入表中

load data local inpath '/home/ljhn1829/test' into table ljh_emp_test;

（4）查看表中数据

select * from ljh_emp_test;
OK
ljh,25000,male    NULL    NULL
jediael,25000,male    NULL    NULL
llq,15000,female    NULL    NULL
Time taken: 0.185 seconds, Fetched: 3 row(s)

可以看出，由于分隔符为ctrl+A，因此导入数据时将文件中的每一行内容均只当作第一个字段，导致后面2个字段均为null。

（三）复杂一点的表
1、创建表

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

2、准备数据

John Doe^A100001.1^AMary Smith^BTodd Jones^AFederal Taxes^C.2^BStateTaxes^C.05^BInsurance^C.1^A1 Michigan Ave.^BChicago^BIL^B60600
Mary Smith^A80000.0^ABill King^AFederal Taxes^C.2^BState Taxes^C.05^BInsurance^C.1^A100 Ontario St.^BChicago^BIL^B60601
Todd Jones^A70000.0^A^AFederal Taxes^C.15^BState Taxes^C.03^BInsurance^C.1^A200 Chicago Ave.^BOak Park^BIL^B60700
Bill King^A60001.0^A^AFederal Taxes^C.15^BState Taxes^C.03^BInsurance^C.1^A300 Obscure Dr.^BObscuria^BIL^B60100

注意 ^A：分隔字段 ^B：分隔array/struct/map中的元素 ^C：分隔map中的KV
详见《hive编程指南》P44。

3、将数据导入表中

load data local inpath '/home/ljhn1829/phd' into table employees partition(country='us',state='ca');

4、查看表数据

hive> select * from employees;
OK
John Doe    100001.1    ["Mary Smith","Todd Jones"]    {"Federal Taxes":0.2,"StateTaxes":0.05,"Insurance":0.1}    {"stree":"1 Michigan Ave.","city":"Chicago","state":"IL","zip":60600}    us    ca
Mary Smith    80000.0    ["Bill King"]    {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}    {"stree":"100 Ontario St.","city":"Chicago","state":"IL","zip":60601}    us    ca
Todd Jones    70000.0    []    {"Federal Taxes":0.15,"State Taxes":0.03,"Insurance":0.1}    {"stree":"200 Chicago Ave.","city":"Oak Park","state":"IL","zip":60700}    us    ca
Bill King    60001.0    []    {"Federal Taxes":0.15,"State Taxes":0.03,"Insurance":0.1}    {"stree":"300 Obscure Dr.","city":"Obscuria","state":"IL","zip":60100}    us    ca
Time taken: 0.312 seconds, Fetched: 4 row(s)

5、查看hdfs中的文件

hadoop fs -ls /data/gamein/g4_us/meta/employees/country=us/state=ca
Found 1 items
-rwxr-x---   3 ljhn1829 g4_us        428 2015-05-12 12:49 /data/gamein/g4_us/meta/employees/country=us/state=ca/progamming_hive_data.txt

该文件中的内容与原有文件一致。

（四）通过select子句插入数据
1、创建表

create table employees2 (
    name string,
    slalary float,
    suboddinates array<string>,
    deductions map<string,float>,
    address struct<stree:string, city:string, state:string, zip:int>
)
partitioned by(country string, state string);

2、插入数据

hive>  set hive.exec.dynamic.partition.mode=nonstrict;

否则会出现以下异常：

    FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

insert into table employees2
partition (country,state)
select name,slalary,suboddinates,deductions,address, e.country, e.state
from employees e;