hive 之 将excel数据导入hive中 : excel 转 txt

一、需求：

1、客户每月上传固定格式的excel文件到指定目录。每月上传的文件名只有结尾月份不同，如： 10月文件名：  zhongdiangedan202010.xlsx  ， 11月文件名： zhongdiangedan202011.xlsx 

2、将上传的excel文件导入hive中，在做进一步数据分析。

 

二、思路：

  1、通过python的pandas模块将excel文件转换为txt文件；

  2、编写shell脚本，使用 hdfs dfs -put 将txt文件导入指定表（方便每月执行）。

 

三、pandas.read_excel 包：

def read_excel(io,sheet_name=0,header=0,names=None,index_col=None,usecols=None,squeeze=False,dtype=None,engine=None,converters=None,true_values=None,false_values=None,skiprows=None,nrows=None,na_values=None,keep_default_na=True,na_filter=True,verbose=False,parse_dates=False,date_parser=None,thousands=None,comment=None,skipfooter=0,convert_float=True,mangle_dupe_cols=True)

常用属性字段说明，如下：

属性字段	默认字段	含义
io	无，必选	excel文件路径
sheetname	0	取第一个sheet的内容；取多个sheet内容：sheetname=[0,1]；取全表：sheetname=None
header	0	指定为表头的行，取第一行为表头 header = 0；无表头：header = None；
names	None	提取指定列名的数据
index_col	None	指定多少列为索引列，或指定对应列名为索引列。
usecols	None	指定要取的列，None：取所有列；只能是列表。注：使用次属性，sheet_name 也必须使用。
skiprows	0	忽略（不取）指定行数的数据，行数为列表形式。如，忽略第1行,第9行数据： skiprows=[1,9]
skip_footer	0	忽略倒数n行数据

 

四、实现代码：

excel_to_txt.py

#!/user/bin env python
import pandas as pd
import sys
def excel_to_txt(monthNo):
    df = pd.read_excel('/data/excelfile/zhongdiangedan%s.xlsx'%(monthNo),header=None)  # 使用pandas模块读取数据， header 默认0，指定表头行，None:不取表头
    print('开始写入txt文件')
    df.to_csv('/data/txtlfile/zhongdiangedan%s.txt'%(monthNo), header=None, sep='\t', index=False)        # 写入txt中，tab分隔
    print('文件写入成功!')

if __name__ == '__main__':
      monthNo=sys.argv[1]
    excel_to_txt(monthNo)

执行python： python excel_to_txt.py 202010

 

建表：

create table impt_excel_data_zhongdiangedan(
col1 string,
col2 string,
col3 string,
col4 string,
col5 string,
col6 string
)partitioned by (month_id string)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile
location '/data/hive/database_name/impt/impt_excel_data_zhongdiangedan';

 

txt_to_hive.sh

#!/bin/bash
monthNo=$1
hive -e"
        use database_name; -- 指定数据库 database_name
        alter table tmp_content_zhongdiangedan drop if exists partition (month_id=$monthNo); -- 删除分区
        alter table tmp_content_zhongdiangedan add partition (month_id=$monthNo); -- 添加分区
"
# 导入数据
hdfs dfs -put /data/txtlfile/zhongdiangedan$monthNo.txt /data/hive/database_name/impt/impt_excel_data_zhongdiangedan/month_id=$monthNo

执行shell: sh txt_to_hive.sh 202010