hive

oozie調(diào)用hive action，注意，不是使用hive2 action
oozie需要上傳 workflow.xml文件到hdfs目錄中，script.q上傳到workflow.xml同級目錄；
注意，job.properties留在服務(wù)器本地，作為config使用；

oozie創(chuàng)建job命令
oozie job -oozie http://localhost:11000/oozie -config job.properties -run

oozie刪除job命令
oozie job -oozie http://localhost:11000/oozie -kill [jobId]

在oozie調(diào)用hive action過程中，僅參考附件文件，注意不需要上傳任何lib庫到hdfs上；

job.properties

#
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
# 下面兩個地址可以從hdfs配置中查詢到
nameNode=hdfs://kxjgCffI-Master1.jcloud.local:8020 
jobTracker=kxjgCffI-Master1.jcloud.local:8050
queueName=default
examplesRoot=zy
oozie.use.system.libpath=true
# workflow.xml文件所在的hdfs目錄
oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/hive

workflow.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<workflow-app xmlns="uri:oozie:workflow:0.2" name="hive-wf">
    <start to="hive-node"/>

    <action name="hive-node">
        <hive xmlns="uri:oozie:hive-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>
            <script>script.q</script>
        </hive>
        <ok to="end"/>
        <error to="fail"/>
    </action>

    <kill name="fail">
        <message>Hive failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>

script.q 執(zhí)行的hive命令demo，創(chuàng)建數(shù)據(jù)庫，創(chuàng)建內(nèi)部表，創(chuàng)建外部表，將外部表中的數(shù)據(jù)導(dǎo)入到內(nèi)部表

--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
DROP DATABASE IF EXISTS people1 cascade;
CREATE DATABASE people1;
DROP TABLE student1inner;
CREATE TABLE people1.student1inner(id int,name string,sex string);
DROP TABLE student1out;
CREATE EXTERNAL TABLE people1.student1out(id int,name string,sex string)  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' location '/user/hadoop/examples/output-data/sqoop-mysql';
INSERT INTO people1.student1inner(id,name,sex) select id,name,sex from people1.student1out;

hive操作筆記

創(chuàng)建數(shù)據(jù)庫:
CREATE DATABASE IF NOT EXISTS [DATABASE_NAME];

刪除數(shù)據(jù)庫：
DROP DATABASE IF EXISTS [DATABASE_NAME]; 注意，如果該數(shù)據(jù)庫中有表存在的話，這個數(shù)據(jù)庫刪除不掉

強(qiáng)制刪除數(shù)據(jù)庫:
DROP DATABASE IF EXISTS [DATABASE_NAME] CASCADE;

創(chuàng)建外部表：
CREATE EXTERNAL TABLE [DATABASE_NAME].[TABLENAME](id int,name string)  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' location '/user/hadoop/examples/output-data/sqoop-mysql';
注意，[DATABASE_NAME]也可以沒有；
/user/hadoop/examples/output-data/sqoop-mysql 這個是外部表級聯(lián)文件的位置；TERMINATED BY ','是文件中分隔符

文件demo
1,zhangsan
2,lisi

創(chuàng)建內(nèi)部表：
CREATE TABLE people1.student1inner(id int,name string,sex string);

從一個表導(dǎo)入數(shù)據(jù)到另一張表
INSERT INTO people1.student1inner(id,name,sex) select id,name,sex from people1.student1out;
上面語句使用前提是兩張表都已經(jīng)創(chuàng)建好


刪除表：
DROP TABLE student1out;

ssh

oozie在調(diào)用shell任務(wù)時，是需要將sh文件上傳到hdfs上，并隨機(jī)選擇集群中的一臺主機(jī)執(zhí)行該sh腳本
如果不是集群全部主機(jī)都能成功執(zhí)行該shell腳本（比如調(diào)用master節(jié)點(diǎn)的mysql創(chuàng)建表），則不要使用shell action方式，
這種情況下使用ssh action可能更好點(diǎn)，因為能指定哪一臺host執(zhí)行，并選擇執(zhí)行的用戶

job.properties參考hive
workflow-ssh.xml

<workflow-app xmlns="uri:oozie:workflow:0.2" name="ssh-wf">
    <start to="ssh-createmysql"/>
    <action name="ssh-createmysql">
        <ssh xmlns="uri:oozie:ssh-action:0.2">  
            <host>hadoop@kxjgCffI-Master1.jcloud.local</host>
            <command>sh /home/hadoop/oozie/createmysql.sh</command>
        </ssh>  
        <ok to="ssh-hiveoption"/>
        <error to="fail"/>
    </action>
    <action name="ssh-hiveoption">
        <ssh xmlns="uri:oozie:ssh-action:0.2">  
            <host>hadoop@kxjgCffI-Master1.jcloud.local</host>
            <command>sh /home/hadoop/oozie/hiveoption.sh</command>
        </ssh>  
        <ok to="end"/>
        <error to="fail"/>
    </action>
    <kill name="fail">
        <message>ssh failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>

createmysql.sh 創(chuàng)建mysql數(shù)據(jù)庫、創(chuàng)建表、并插入模擬數(shù)據(jù)

#!/bin/bash
DATABASE="people"
TABLE="students"

#delete database;
mysql -u root << EOF
DROP DATABASE IF EXISTS $DATABASE;
EOF

#create database
mysql -u root << EOF
CREATE DATABASE IF NOT EXISTS $DATABASE CHARACTER SET UTF8;
EOF

echo 'create database $DATABASE'

#create table
mysql -u root $DATABASE << EOF
CREATE TABLE IF NOT EXISTS $TABLE(id bigint(8) unsigned primary key Auto_Increment,name text,sex text) Engine InnoDB;
EOF

echo 'create table $TABLE'

#insert data
mysql -u root $DATABASE << EOF
INSERT INTO $TABLE (name,sex) VALUES ("zhangsan","man");
INSERT INTO $TABLE (name,sex) VALUES ("lisi","man");
INSERT INTO $TABLE (name,sex) VALUES ("wangwu","man");
INSERT INTO $TABLE (name,sex) VALUES ("zhaoliu","woman");
EOF