Hive

-- sample data dfs 로딩

[hadoop@hadoop01 ~]$ hadoop fs -put input & 

[hadoop@hadoop01 ~]$ tar -zxf apache-hive-0.13.1-bin.tar.gz 

[hadoop@hadoop01 ~]$ ln -s apache-hive-0.13.1-bin hive

- configuration 설정 

$ vi hive/conf/hive-env.sh

HADOOP_HOME=/home/hadoop/hadoop

[hadoop@hadoop01 ~]$ hive/bin/hive

Logging initialized using configuration in jar:file:/home/hadoop/apache-hive-0.13.1-bin/lib/hive-common-0.13.1.jar!/hive-log4j.properties

hive> 

hive> show databases;

OK

default

Time taken: 0.352 seconds, Fetched: 1 row(s)

hive> create database finance;

OK

Time taken: 0.393 seconds

hive> show databases;         

OK

default

finance

Time taken: 0.045 seconds, Fetched: 2 row(s)

hive> create table test(col1 int, col2 string);

- hadoop 데이터 생성 확인 (Embedded Metastore:  Derby DB)

- Metastore를 Mysql로 변경  (derby 는 database lock 발생)

mysql install 

[hadoop@hadoop01 ~]$ vi hive/conf/hive-site.xml

<configuration> <property>   <name>javax.jdo.option.ConnectionURL</name>   <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>   <description>JDBC connect string for a JDBC metastore</description> </property> <property>   <name>javax.jdo.option.ConnectionDriverName</name>   <value>com.mysql.jdbc.Driver</value>   <description>Driver class name for a JDBC metastore</description> </property> <property>   <name>javax.jdo.option.ConnectionUserName</name>   <value>root</value>   <description>username to use against metastore database</description> </property> <property>   <name>javax.jdo.option.ConnectionPassword</name>   <value>root</value>   <description>password to use against metastore database</description> </property> <property>   <name>hive.metastore.warehouse.dir</name>   <value>/user/hive/warehouse</value>   <description>location of default database for the warehouse</description> </property> </configuration>

- jdbc driver link 생성

[hadoop@hadoop01 ~]$ ln -s /usr/share/java/mysql-connector-java.jar /home/hadoop/hive/lib

hive> create external table stock_trade (

    > stck_exchange STRING,

    > stock_symbol STRING,

    > trade_date STRING,

    > stock_price_open FLOAT,

    > stock_price_high FLOAT,

    > stock_price_low FLOAT,

    > stock_price_close FLOAT,

    > stock_volume INT,

    > stock_price_adj_close FLOAT

    > )

    > row format delimited

    >  fields terminated by ','

    >  lines terminated by '\n'

    >  stored as textfile

    >  location '/user/hadoop/input';

OK

hive> select * from stock_trade limit 10;

exchange stock_symbol date NULL NULL NULL NULL NULL NULL NASDAQ ABXA 2009-12-09 2.55 2.77 2.5 2.67 158500 2.67 NASDAQ ABXA 2009-12-08 2.71 2.74 2.52 2.55 131700 2.55 NASDAQ ABXA 2009-12-07 2.65 2.76 2.65 2.71 174200 2.71 NASDAQ ABXA 2009-12-04 2.63 2.66 2.53 2.65 230900 2.65 NASDAQ ABXA 2009-12-03 2.55 2.62 2.51 2.6 360900 2.6 NASDAQ ABXA 2009-12-02 2.41 2.59 2.4 2.53 287700 2.53 NASDAQ ABXA 2009-12-01 2.35 2.44 2.27 2.4 302000 2.4 NASDAQ ABXA 2009-11-30 2.36 2.36 2.11 2.25 446100 2.25 NASDAQ ABXA 2009-11-27 2.35 2.42 2.3 2.35 135200 2.35 Time taken: 0.393 seconds, Fetched: 10 row(s)