Hive 資料倉儲工具

Download and Install
ubuntu@HDClient:~$ wget http://archive.apache.org/dist/hive/hive-0.14.0/apache-hive-0.14.0-bin.tar.gz

ubuntu@HDClient:~$ tar zxf apache-hive-0.14.0-bin.tar.gz -C ~

Edit .bashrc

:::

export PATH=$PATH:/home/ubuntu/apache-hive-0.14.0-bin/bin

ubuntu@HDClient:~$ hive -S
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/ubuntu/hadoop-2.8.2/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/ubuntu/apache-hive-0.14.0-bin/lib/hive-jdbc-0.14.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

hive> quit ;

ubuntu@HDClient:~$



ubuntu@HDClient:~$ hive -S -e 'set -v' | grep 'fs.defaultFS'
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/ubuntu/hadoop-2.8.2/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/ubuntu/apache-hive-0.14.0-bin/lib/hive-jdbc-0.14.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
fs.defaultFS=hdfs://nn:8020
mapreduce.job.hdfs-servers=${fs.defaultFS}


ubuntu@HDClient:~$ hive -S
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/ubuntu/hadoop-2.8.2/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/ubuntu/apache-hive-0.14.0-bin/lib/hive-jdbc-0.14.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
hive> create table dummy (value string) ;
hive> show tables ;
dummy
hive> load data local inpath '/tmp/dummy.txt' into table dummy ;
hive> select * from dummy ;
x
hive> load data local inpath '/tmp/dummy.txt' into table dummy ;
hive> select * from dummy ;
x
x
hive> drop table dummy ;
hive> select * from dummy ;
FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'dummy'
hive> quit ;


ubuntu@HDClient:~$ hive -S
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/ubuntu/hadoop-2.8.2/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/ubuntu/apache-hive-0.14.0-bin/lib/hive-jdbc-0.14.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
hive> create table student ( code string, name string, type string, class string, total int) row format delimited fields terminated by '\t' stored as textfile ;
hive> load data local inpath 'student.txt' into table student ;
hive> select code,name,total from student limit 10 ;
大專校院校別學生數              NULL
103 學年度 SY2014-2015          NULL
學校代碼        學校名稱        NULL
0001    國立政治大學    973
0001    國立政治大學    NULL
0001    國立政治大學    NULL
0001    國立政治大學    NULL
0002    國立清華大學    NULL
0002    國立清華大學    NULL
0002    國立清華大學    NULL

hive> !tree -L 2 metastore_db ; <Hive 儲存schema的地方>

metastore_db

├── dbex.lck

├── db.lck

├── log

│   ├── log1.dat

│   ├── log.ctrl

│   ├── logmirror.ctrl

│   └── README_DO_NOT_TOUCH_FILES.txt

├── README_DO_NOT_TOUCH_FILES.txt

├── seg0

│   ├── c101.dat

│   ├── c10.dat

│   ├── c111.dat

│   ├── c121.dat

│   ├── c130.dat

│   ├── c141.dat

│   ├── c150.dat

│   ├── c161.dat

hive> !hdfs dfs -ls /user/hive/warehouse ; <Hive實際儲存的位置>

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/ubuntu/hadoop-2.8.2/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/home/ubuntu/apache-hive-0.14.0-bin/lib/hive-jdbc-0.14.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

Found 1 items

drwxr-xr-x   - ubuntu supergroup          0 2017-12-18 09:55 /user/hive/warehouse/student

 = table name

hive> select * from accounts limit 2 ;

a69dae1f-b2ee-1257-3895-438dfb8ea964    2005-11-30 19:19:03     2005-11-30 19:19:03     1       beth_id 1       Alpha-Murraiin Communications, Inc             Manufacturing    Communications                  5423 Camby Rd.  La Mesa CA     35890    USA                     612-555-4878                            www.alpha-murraiincommunications,inc.com                                        5423 Camby Rd.  La Mesa CA      35890   USA     NULL

e908e57d-18d3-5ffa-f6f4-438dfb104441    2005-11-30 19:19:03     2005-11-30 19:19:03     1       sarah_id        1       N & W Creek Transportation Corp        Distribution     Transportation                  1792 Belmont Rd.        Chula Vista     CA      40520   USA                     555-555-2714                   www.nwcreektransportationcorp.com                                        1792 Belmont Rd.        Chula Vista     CA      40520   USA     NULL

hive> drop table accounts ;

hive> select * from accounts limit 2 ;

FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'account

《table 刪除了,無法query資料,但Raw Data還在,不允許被異動》

hive> !hdfs dfs -ls /user/hive/myacc ;

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/ubuntu/hadoop-2.8.2/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/home/ubuntu/apache-hive-0.14.0-bin/lib/hive-jdbc-0.14.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

Found 1 items

-rw-r--r--   3 ubuntu supergroup     357646 2017-12-18 10:49 /user/hive/myacc/accounts.csv