利用python将excel文件转成txt文件，再将txt文件上传hdfs，最后传入hive中

将excel文件转成txt文件，再将txt文件上传hdfs，最后传入hive中

注意的点
（1）先判断写入的txt文件是否存在，如果不存在就需要创建路径
（2）如果txt文件已经存在，那么先将对应的文件进行删除后再写入txt数据
（3）excel文件中有可能第一行是字段名，需要跳过

（版本1 ：本地版本）
1.利用python将excel转成txt文件

from datetime import datetime, timedelta
import os
import pytz
import pandas as pddef excel_to_txt(name, date):# Read Excel file into a DataFramedf = pd.read_excel(f'data/excel/{name}.xlsx', header=None, skiprows=1)# Define output directory and pathoutput_directory = os.path.join('data', 'txt', date)os.makedirs(output_directory, exist_ok=True)  # Create directory if it doesn't existoutput_path = os.path.join(output_directory, f'{name}.txt')# Check if the file already exists, if so, remove itif os.path.exists(output_path):os.remove(output_path)print(f'Existing file {output_path} removed.')# Write DataFrame to a new text fileprint('开始写入txt文件')df.to_csv(output_path, header=None, sep='\t', index=False)print('文件写入成功!')return output_pathif __name__ == '__main__':current_time = datetime.now(pytz.timezone('Asia/Shanghai'))one_day_ago = (current_time - timedelta(days=1)).strftime('%Y-%m-%d')local_file_path = excel_to_txt('IS_GS_Recruitment_Data_20231211', one_day_ago)print(local_file_path)

2.上传到hdfs
3.在hive中创建表

drop table if exists ticket.test_text;
create external table IF NOT EXISTS ticket.test_text
(name string,age int
) comment ''row format delimited fields terminated by '\t'lines terminated by '\n'NULL DEFINED AS ''stored as textfileLOCATION '/warehouse/ticket/ods/test_text';

4.将hdfs数据写入hive

load data inpath '/origin_data/test.txt' overwrite into table ticket.test_text;

（2）服务器版本
先把excel_to_txt脚本上传到服务器

excel_to_txt.py

from datetime import datetime, timedelta
import os
import pytz
import pandas as pddef excel_to_txt(name, date):# Read Excel file into a DataFramedf = pd.read_excel(f'/opt/module/data/excel/{name}.xlsx', header=None,skiprows=1)# df = pd.read_excel(f'hdfs://mycluster:8020/origin_data/hr_cn/db/is_gs_recruitment_data_full/excel/{name}.xlsx', header=None,skiprows=1)# df = pd.read_excel(f'data/excel/{name}.xlsx', header=None,skiprows=1)# Define output directory and pathoutput_directory = os.path.join('/opt/module/data', 'txt', date)os.makedirs(output_directory, exist_ok=True)  # Create directory if it doesn't existoutput_path = os.path.join(output_directory, f'{name}.txt')# Check if the file already exists, if so, remove itif os.path.exists(output_path):os.remove(output_path)print(f'Existing file {output_path} removed.')# Write DataFrame to a new text fileprint('开始写入txt文件')df.to_csv(output_path, header=None, sep='\t', index=False)print('文件写入成功!')return output_pathif __name__ == '__main__':current_time = datetime.now(pytz.timezone('Asia/Shanghai'))one_day_ago = (current_time - timedelta(days=1)).strftime('%Y-%m-%d')local_file_path = excel_to_txt('IS_GS_Recruitment_Data_20231211', one_day_ago)print(local_file_path)

2.安装python3环境，安装链接：
https://editor.csdn.net/md/?articleId=129627849

3.执行python脚本

recruitment_excel_to_txt.sh

#!/bin/bash
/opt/module/miniconda3/bin/python  /opt/module/data/excel/excel_to_txt.py

4.上传到hdfs，并将数据导入hive

recruitment_hdfs_to_ods.sh

#!/bin/bash
DATAX_HOME=/opt/module/datax# 如果传入日期则do_date等于传入的日期，否则等于前一天日期
if [ -n "$2" ] ;thendatestr=$2
elsedatestr=$(date -d "-1 day" +%F)
fi# 处理目标路径，检查目标路径是否存在且不为空，如果不为空，则清空目录内容
handle_target() {content_size=$(hadoop fs -count $1 | awk '{print $3}')if [[ $content_size -ne 0 ]]; thenecho "路径$1不为空，正在清空......"hadoop fs -rm -r -f $1/*fi
}# 整合处理目标路径和上传文件的逻辑
handle_target_and_put() {handle_target $2echo "上傳文件"hadoop fs -put $1 $2
}function import_data(){
# $*: 获取所有参数,如果使用""包裹之后,$*当做整体
# $#: 获取参数个数
# $@: 获取所有参数,如果使用""包裹之后,把每个参数当做单独的个体
# $?: 获取上一个指令的结果tableNames=$*sql="use hr_cn;"#遍历所有表,拼接每个表的数据加载sql语句for table in $tableNamesdosql="${sql}load data inpath '/origin_data/hr_cn/db/${table:4}/${datestr}/*' overwrite into table ${table} partition (dt='$datestr');"done#执行sql/opt/module/hive/bin/hive -e "$sql"
}case $1 in
"all")handle_target_and_put /opt/module/data/txt/${datestr}/ /origin_data/hr_cn/db/recruitment_info_full/import_data "ods_recruitment_info_full";;
"recruitment_info")handle_target_and_put /opt/module/data/txt/${datestr}/ /origin_data/hr_cn/db/recruitment_info_full/import_data "ods_recruitment_info_full";;
esac