mysql数据导入hive脚本

发表于 | 更新于 | 分类于

定时从mysql同步到hive脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
#!/usr/bin/env bash  
####################################
#### 定时从mysql同步常驻地信息到HDFS
#### create by bisen.bai
####################################
cur_date=`date +%Y-%m-%d`
#### db conf
DB_HOST='127.0.0.1'
DB_PORT='3306'
DB_USERNAME='bbs'
DB_PASSWORD='12345'
DB_NAME='contact'
CSV_TMP_DIR='/home/q/data/bbs_tmp/permanent'
HDFS_LOCATION='/user/ppdev/permanent_city'

#### pull mysql data to local csv file
for i in {0..9}
do
PULL_SQL="SELECT user_id,cid,uid,gid,vid,permanent_city,create_time,update_time,source,date_format(create_time,'%Y-%m-%d') dt from permanent_city_${i};"
mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME} -N -e "${PULL_SQL}" > ${CSV_TMP_DIR}/permanent${i}.csv
if [[ $? -eq 0 ]]; then
    sudo tee -a ${CSV_TMP_DIR}/success.log <<< "${cur_date}####${PULL_SQL} 执行成功..."
else
    sudo tee -a ${CSV_TMP_DIR}/error.log <<< "${cur_date}####${PULL_SQL} 执行失败..."
    sudo rm -f ${CSV_TMP_DIR}/permanent*
    exit 1
fi

done

#### put local csv file to hdfs
if [[ "`sudo -u ppdev hdfs dfs -ls ${HDFS_LOCATION}`" = "" ]]; then
    sudo tee -a ${CSV_TMP_DIR}/success.log <<< "${cur_date}####HDFS文件为空..."
else
    sudo tee -a ${CSV_TMP_DIR}/success.log <<< "${cur_date}####HDFS文件不为空..."
    sudo -u ppdev hdfs dfs -rmr ${HDFS_LOCATION}/permanent*
    if [[ $? -eq 0 ]]; then
        sudo tee -a ${CSV_TMP_DIR}/success.log <<< "${cur_date}####HDFS清空旧文件成功..."
    else
        sudo tee -a ${CSV_TMP_DIR}/error.log <<< "${cur_date}####HDFS清空旧文件失败..."
        exit 1
    fi
fi

sudo -u ppdev hdfs dfs -put ${CSV_TMP_DIR}/permanent* ${HDFS_LOCATION}
if [[ $? -eq 0 ]]; then
    sudo tee -a ${CSV_TMP_DIR}/success.log <<< "${cur_date}####HDFS导入成功..."
else
    sudo tee -a ${CSV_TMP_DIR}/success.log <<< "${cur_date}####HDFS导入失败..."
    exit 1
fi

#### delete local csv file when put to hdfs success
sudo rm -f ${CSV_TMP_DIR}/permanent*
if [[ $? -eq 0 ]]; then
    sudo tee -a ${CSV_TMP_DIR}/success.log <<< "${cur_date}####清空临时CSV文件成功..."
else
    sudo tee -a ${CSV_TMP_DIR}/success.log <<< "${cur_date}####清空临时CSV文件失败..."
    exit 1
fi