导入数据到数据库

拿到手的所有的经侦数据，里面主要包括客户基本信息表，开户基本信息表，账户交易明细表三种类型的数据，存储数据到数据库，就要建表，建表就要确定表内到底有多少字段，这些字段是什么？
显然 ，挨个去找每个表里的字段不太现实，借助python的for循环来实现。

• 获取所有文件列表

这里为了防止出错，新建一个单独的readfile.py文件进行获取字段操作
用python的循环遍历，实现代码如下

# -*- coding: utf-8 -*- ne

import os
# 遍历文件夹
def walkFile(file):
    for root, dirs, files in os.walk(file):

        # root 表示当前正在访问的文件夹路径
        # dirs 表示该文件夹下的子目录名list
        # files 表示该文件夹下的文件list

        # 遍历文件
         for f in files:
            files_list.append(os.path.join(root, f))
            print(os.path.join(root, f))
    return files_list
            
def main():
    walkFile("/root/color/sparkml/readtest")
if __name__ == '__main__':
    main()            

运行之后的结果如下图所示：

这样就获得了所有的excel文件。

• 获取excel的对应字段。

前面结果的返回值是 files_list，
假设这次要提取的是开户基本信息表的内容，
用下面的代码实现提取字段的功能：

 for file in files_list:
        if "开户基本信息表" in file:
            pdf = pd.ExcelFile(file, header=None, encoding="gbk").parse()
            keys = keys | set(pdf.keys())
            pass

这样子，我们就获得了所有表格的字段

• 获得字段之后，做excel表中字段与数据库字段的映射
  excel文件的字段名是中文，数据库中对应的字段设定为英文
  上面得到的字段能print出来，建一个大字典，将excel字段名与数据库字段名进行对应。

fieldDict = {
        "币种": "currency",
        "住宅地址": "residentialAddress",
        "钞汇标志名称": "cashName"
        }

• 构造建表语句
  那堆字段已经用字典，做好了映射，如果写建表语句过于冗余，这里采用拼接，将那些字段输出代码和下面的语句进行拼接。

create_sql = "create table openaccount("

#####那堆获取字段的代码

 create_sql = create_sql[:-1]
 create_sql = create_sql + ");"
 print(create_sql)

• 空值存储问题

在所给的数据表里，有些数据存在，有些字段则为空，对于存在的数据，我们只需要将数据读取，并存进数据库即可，对于空值我们应该怎么样进行处理呢？
答案是置空，采用的代码是："'NaN' as" + fieldDict[k] + ’,'
这部分判断和处理的代码为：

   columns_struct_fields = []
                ##字段存在，读取数据
                for k in pdf.keys():
                    k = k.encode("utf-8")
                    columns_struct_fields.append(
                        StructField(k, StringType(), True))
                    keys_map += "`" + k + "` as `" + fieldDict[k] + '`,'
                ##字段不存在，置空
                remainder_key = keys - set(pdf.keys())
                for k in remainder_key:
                    k = k.encode("utf-8")
                    keys_map += "'NaN' as `" + fieldDict[k] + '`,'

• 一个文件多个sheet问题
  在excel文件里，有可能一个excel会有多个sheet表，默认是读取第一张sheet，如果想要读取后面的sheet，就要采取下面的代码：

     ##读表，sheetlist来读表里的每一个sheet
                xl = pd.ExcelFile(file, header=None, encoding="gbk")
                sheetList = xl.sheet_names
                for sheet in sheetList:
                    table_keys = ""
                    temp_keys = ""
                    keys_map = ""
                    count += 1
                    print(count)
                    pdf = xl.parse(sheet)
                

• 插入数据库
  插入表，先建临时表，直接插入空值容易出错，temp临时表做中转

insert_sql = "INSERT INTO jingzhen.openaccount SELECT %s from temp" % (keys_map)
                schema = StructType(columns_struct_fields)
                df = hiveContext.createDataFrame(pdf, schema=schema)
                df.createOrReplaceTempView("temp")
                hiveContext.sql(insert_sql)
                df.unpersist(True)

这个地方，创建临时表用 .createOrReplaceTempView 方法。

insert into 需要自己进行建表，上面的creat_sql语句输出后，在hive里建表，建表成功，这个地方才能插入成功。