批量加载excel的xsl文件到hive分区表

一、编写java程序读取xsl文件，转成csv

1.pom.xml文件

 <dependencies>
            <dependency>
                <groupId>net.sf.opencsv</groupId>
                <artifactId>opencsv</artifactId>
                <version>2.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-schemas</artifactId>
                 <version>1.1</version>
                 <type>pom</type>
             </dependency>
             <dependency>
                 <groupId>org.apache.poi</groupId>
                 <artifactId>poi</artifactId>
                 <version>3.7</version>
             </dependency>
             <dependency>
                 <groupId>org.apache.poi</groupId>
                 <artifactId>ooxml-schemas</artifactId>
                 <version>1.1</version>
             </dependency>
             <dependency>
                 <groupId>org.apache.poi</groupId>
                 <artifactId>poi-ooxml</artifactId>
                 <version>3.7</version>
             </dependency>
             <dependency>
                 <groupId>dom4j</groupId>
                 <artifactId>dom4j</artifactId>
                 <version>1.6.1</version>
             </dependency>
        <!--jxi导出-->
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>
         </dependencies>

2.XLSX2CSV

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;

public class XLSX2CSV {
    /**
     * The type of the data value is indicated by an attribute on the cell. The
     * value is usually in a "v" element within the cell.
     */
    enum xssfDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
    }


    class MyXSSFSheetHandler extends DefaultHandler {

        /**
         * Table with styles
         */
        private StylesTable stylesTable;

        /**
         * Table with unique strings
         */
        private ReadOnlySharedStringsTable sharedStringsTable;

        /**
         * Destination for data
         */
        private final PrintStream output;

        /**
         * Number of columns to read starting with leftmost
         */
        private final int minColumnCount;

        // Set when V start element is seen
        private boolean vIsOpen;

        // Set when cell start element is seen;
        // used when cell close element is seen.
        private xssfDataType nextDataType;

        // Used to format numeric cell values.
        private short formatIndex;
        private String formatString;
        private final DataFormatter formatter;

        private int thisColumn = -1;
        // The last column printed to the output stream
        private int lastColumnNumber = -1;

        // Gathers characters as they are seen.
        private StringBuffer value;


        public MyXSSFSheetHandler(StylesTable styles,
                                  ReadOnlySharedStringsTable strings, int cols, PrintStream target) {
            this.stylesTable = styles;
            this.sharedStringsTable = strings;
            this.minColumnCount = cols;
            this.output = target;
            this.value = new StringBuffer();
            this.nextDataType = xssfDataType.NUMBER;
            this.formatter = new DataFormatter();
        }


        public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {

            if ("inlineStr".equals(name) || "v".equals(name)) {
                vIsOpen = true;
                // Clear contents cache
                value.setLength(0);
            }
            // c => cell
            else if ("c".equals(name)) {
                // Get the cell reference
                String r = attributes.getValue("r");
                int firstDigit = -1;
                for (int c = 0; c < r.length(); ++c) {
                    if (Character.isDigit(r.charAt(c))) {
                        firstDigit = c;
                        break;
                    }
                }
                thisColumn = nameToColumn(r.substring(0, firstDigit));

                // Set up defaults.
                this.nextDataType = xssfDataType.NUMBER;
                this.formatIndex = -1;
                this.formatString = null;
                String cellType = attributes.getValue("t");
                String cellStyleStr = attributes.getValue("s");
                if ("b".equals(cellType))
                    nextDataType = xssfDataType.BOOL;
                else if ("e".equals(cellType))
                    nextDataType = xssfDataType.ERROR;
                else if ("inlineStr".equals(cellType))
                    nextDataType = xssfDataType.INLINESTR;
                else if ("s".equals(cellType))
                    nextDataType = xssfDataType.SSTINDEX;
                else if ("str".equals(cellType))
                    nextDataType = xssfDataType.FORMULA;
                else if (cellStyleStr != null) {
                    // It's a number, but almost certainly one
                    // with a special style or format
                    int styleIndex = Integer.parseInt(cellStyleStr);
                    XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                    this.formatIndex = style.getDataFormat();
                    this.formatString = style.getDataFormatString();
                    if (this.formatString == null)
                        this.formatString = BuiltinFormats
                                .getBuiltinFormat(this.formatIndex);
                }
            }

        }


        public void endElement(String uri, String localName, String name)
                throws SAXException {

            String thisStr = null;

            // v => contents of a cell
            if ("v".equals(name)) {
                // Process the value contents as required.
                // Do now, as characters() may be called more than once
                switch (nextDataType) {

                    case BOOL:
                        char first = value.charAt(0);
                        thisStr = first == '0' ? "FALSE" : "TRUE";
                        break;

                    case ERROR:
//                        thisStr = "\"ERROR:" + value.toString() + '"';
                        thisStr = "\"ERROR:" + value.toString() + '"';
                        break;

                    case FORMULA:
                        // A formula could result in a string value,
                        // so always add double-quote characters.
//                        thisStr = '"' + value.toString() + '"';
                        thisStr =  value.toString();
                        break;

                    case INLINESTR:
                        // TODO: have seen an example of this, so it's untested.
                        XSSFRichTextString rtsi = new XSSFRichTextString(value
                                .toString());
//                        thisStr = '"' + rtsi.toString() + '"';
                        thisStr = rtsi.toString();
                        break;

                    case SSTINDEX:
                        String sstIndex = value.toString();
                        try {
                            int idx = Integer.parseInt(sstIndex);
                            XSSFRichTextString rtss = new XSSFRichTextString(
                                    sharedStringsTable.getEntryAt(idx));
//                            thisStr = '"' + rtss.toString() + '"';
                            thisStr = rtss.toString() ;
                        } catch (NumberFormatException ex) {
                            output.println("Failed to parse SST index '" + sstIndex
                                    + "': " + ex.toString());
                        }
                        break;

                    case NUMBER:
                        String n = value.toString();
                        if (this.formatString != null)
                            thisStr = formatter.formatRawCellContents(Double
                                            .parseDouble(n), this.formatIndex,
                                    this.formatString);
                        else
                            thisStr = n;
                        break;

                    default:
//                        thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
                        thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
                        break;
                }

                // Output after we've seen the string contents
                // Emit commas for any fields that were missing on this row
                if (lastColumnNumber == -1) {
                    lastColumnNumber = 0;
                }
                for (int i = lastColumnNumber; i < thisColumn; ++i)
                    output.print(',');

                // Might be the empty string.
                output.print(thisStr);

                // Update column
                if (thisColumn > -1)
                    lastColumnNumber = thisColumn;

            } else if ("row".equals(name)) {

                // Print out any missing commas if needed
                if (minColumns > 0) {
                    // Columns are 0 based
                    if (lastColumnNumber == -1) {
                        lastColumnNumber = 0;
                    }
                    for (int i = lastColumnNumber; i < (this.minColumnCount); i++) {
                        output.print(',');
                    }
                }

                // We're onto a new row
                output.println();
                lastColumnNumber = -1;
            }

        }


        public void characters(char[] ch, int start, int length)
                throws SAXException {
            if (vIsOpen)
                value.append(ch, start, length);
        }


        private int nameToColumn(String name) {
            int column = -1;
            for (int i = 0; i < name.length(); ++i) {
                int c = name.charAt(i);
                column = (column + 1) * 26 + c - 'A';
            }
            return column;
        }

    }

    // /

    private OPCPackage xlsxPackage;
    private int minColumns;
    private PrintStream output;
    private final String OUTPUT_CHARSET = "GBK";

    /**
     * Creates a new XLSX -> CSV converter
     *
     * @param pkg
     *            The XLSX package to process
     * @param output
     *            The PrintStream to output the CSV to
     * @param minColumns
     *            The minimum number of columns to output, or -1 for no minimum
     */
    public XLSX2CSV(OPCPackage pkg, PrintStream output, int minColumns) {
        this.xlsxPackage = pkg;
        this.output = output;
        this.minColumns = minColumns;
    }

    //TODO catch exceptions
    public XLSX2CSV(String inputFilePath, String outputFilePath) throws Exception {
        xlsxPackage = OPCPackage.open(inputFilePath, PackageAccess.READ);
        output = new PrintStream(outputFilePath, OUTPUT_CHARSET);
        minColumns = -1;
    }

    /**
     * Parses and shows the content of one sheet using the specified styles and
     * shared-strings tables.
     *
     * @param styles
     * @param strings
     * @param sheetInputStream
     */
    public void processSheet(StylesTable styles,
                             ReadOnlySharedStringsTable strings, InputStream sheetInputStream)
            throws IOException, ParserConfigurationException, SAXException {

        InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        SAXParser saxParser = saxFactory.newSAXParser();
        XMLReader sheetParser = saxParser.getXMLReader();
        ContentHandler handler = new MyXSSFSheetHandler(styles, strings,
                this.minColumns, this.output);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    }


    public void process() throws IOException, OpenXML4JException,
            ParserConfigurationException, SAXException {

        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
                this.xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
                .getSheetsData();
        int index = 0;
        while (iter.hasNext()) {
            InputStream stream = iter.next();
            String sheetName = iter.getSheetName();
//            this.output.println();
//            this.output.println(sheetName + " [index=" + index + "]:");
            processSheet(styles, strings, stream);
            stream.close();
            ++index;
        }
    }

    public static void trans(String fileInput,String fileOutput){

        XLSX2CSV xlsx2csv = null;
        try {
            xlsx2csv = new XLSX2CSV(fileInput, fileOutput);
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            xlsx2csv.process();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (OpenXML4JException e) {
            e.printStackTrace();
        } catch (ParserConfigurationException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        }
    }

}

3.ExcelToCsv

public class ExcelToCsv {


    public static void main(String[] args) {
        File file=new File("F:\\大数据\\数据源\\excel\\com_pay_taxs_all");
        ArrayList<String> fileList = FileUtils.readFiles(file.getPath(), new ArrayList<String>(), Pattern.compile(""));
        for (String filename:fileList
        ) {
            //System.out.println(filename);
           if(filename.endsWith("xls"))
            ExcelToCsv.getCsv(filename,new File(filename).getParent()+"\\");
           if (filename.endsWith("xlsx"))
               System.out.println(filename+"需要手动处理");
        }
    }
    /**
     *将excel(xls/xlsx)转换成csv文件
     * @param excelFile
     * @param csvFile
     * @return String
     */
    public static String getCsv(String excelFile, String csvFile) {
        //.xlsx文件后缀转成csv
        if (excelFile.endsWith(".xlsx")){
            XLSX2CSV.trans(excelFile,csvFile);
            return csvFile;
        }
        //.xls文件后缀转成csv
        else {
            try {
                // 载入Excel文件
                WorkbookSettings ws = new WorkbookSettings();
                ws.setLocale(new Locale("en", "EN"));
                Workbook wk = Workbook.getWorkbook(new File(excelFile), ws);
                // 从工作簿(workbook)取得每页(sheets)
                BufferedWriter bw=null;
                for (int sheet = 0; sheet < wk.getNumberOfSheets(); sheet++) {
                    Sheet s = wk.getSheet(sheet);
                    csvFile=csvFile+s.getName()+".csv";
                    System.out.println(csvFile+"=================>"+s.getName());
                    OutputStream os = new FileOutputStream(new File(csvFile));
                    OutputStreamWriter osw = new OutputStreamWriter(os, "UTF8");

                    bw = new BufferedWriter(osw);

                    Cell[] row = null;
                    // 从每页(sheet)取得每个区块(Cell)
                    for (int i = 0; i < s.getRows(); i++) {
                        row = s.getRow(i);
                        if (row.length > 0) {
                            bw.write(row[0].getContents());
                            for (int j = 1; j < row.length; j++) {
                                //写入分隔符
                                bw.write(',');
                                bw.write(row[j].getContents());
                            }
                        }
                        bw.newLine();
                    }
                }

                bw.flush();
                bw.close();

            } catch (Exception e) {
                System.err.println(e.toString());
                e.printStackTrace();
            }
            return csvFile;
        }
    }
}

二、目录结构

hive表中安照年，月进行分区

excel放置目录和分区一致，方便读取目录，批量加载到hive

三、创建hive表

1.编写脚本创建hive表

#!/bin/bash\
# sh /app/shell/bin/csv/createtable.sh test enterprise_cash_policy
time=$(date "+%Y-%m-%d")
workhome=/app/shell
bin_path=$workhome/bin/csv
full_imp_tables=${bin_path}/createtable.txt
script_dir=$(cd $(dirname $0);pwd)

table_tmpstored="stored as textfile"
mysql_srv=172.17.0.7
mysql_port=3306
mysql_user=hive
mysql_pwd=****
mysql_db=***
hive_db=$1
tablename=$2

source /etc/profile

if [ $# != 2 ]
then
  echo "#####################################################"
  echo "##    输入参数不正确请输入database , tablename      ##"
  echo "##                     脚本未能执行                ##"
  echo "#####################################################"
  exit 1
fi

if [ ! -d ${workhome}/log ];then
    mkdir -p ${workhome}/log
fi

#hive -e "use $hive_db;drop table if exists ${table}"
hive -e "create database if not exists ${hive_db}"

#echo $script_dir

function createtable (){
    while read line; do
        #备份旧的分隔符变量
        OLD_IFS="$IFS"
        #设置要使用的分隔符
        IFS="="
        #按设定的分隔符拆分字符串为数组
        arr=(${line})
        #恢复原分隔符变量值
        IFS="$OLD_IFS"
        table=${arr[0]}
        table_tmp=${arr[0]}_tmp
        file_unicode=${arr[3]}
       
        if [ "${table}" = "${tablename}" ]
        then
            echo "编码=${file_unicode}"
            partitionsql="partitioned by (year string, month string)  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
with serdeproperties(\"field.delim\"=',',\"serialization.encoding\"='${file_unicode}')
tblproperties('skip.header.line.count'='1')"
            table_stored="ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' stored as inputformat 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' outputformat 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'"
            echo "${table}=====================${table_tmp}"
            table_tmp_ddlsql="create table if not exists ${table_tmp} (${arr[1]}) $partitionsql"
            table_ddlsql="create table if not exists ${table} ( ${arr[1]},data_source string ) partitioned by (year string, month string) ${table_stored} "
            echo "查看${table}是否存在,不存在创建${table}"
            echo "执行建表sql:${table_ddlsql}"
            hive -e "use $hive_db;${table_ddlsql} "
            succeed
            echo "查看${table_tmp}是否存在,不存在创建${table_tmp}"
            echo "执行建表sql:${table_tmp_ddlsql}"
            hive -e "use $hive_db;${table_tmp_ddlsql} "
            succeed
            exit
        fi
    done < ${full_imp_tables}
}
       succeed(){
         if [ $? -eq 0 ]; then
            echo "#################################################"
            echo "##                 执行命令成功                ##"
            echo "#################################################"
         else
            echo "###############################################"
            echo "##               执行命令失败                ##"
            echo "###############################################"
              exit 1
         fi
    }

main (){
   createtable
}


main

2.配置文件

createtable.txt

enterprise_cash_policy=id char(32),creditCode varchar(30) comment '统一社会信用代码',regNo varchar(30) comment '注册号',declarationAmount varchar(20) comment '会审确认兑现金额',policy varchar(200) comment '政策',policyTerms varchar(500) comment '政策条款',particularYear varchar(20) comment '年份',createTime timestamp comment '创建时间' =id,creditCode,regNo,declarationAmount,policy,policyTerms,particularYear=GBK

配置文件按照=等号进行分割信息包括，表名，字段信息字段类型，insert into from所用字段，创建表的编码（hive默认是utf-8,如果csv是其他格式，会乱码）

三、加载数据

根据需求需要在csv文件的基础上，在hive表中增加数据来源字段，所以采用先将数据加载到tmp表中，然后增加字段，通过insert into给新增字段赋值

1.脚本loaddata2hive.sh

#!/bin/bash
#sh /app/shell/bin/csv/loaddata2hive.sh test enterprise_cash_policy '/app/shell/datasource/excel/enterprise_cash_policy/2020/*.csv' 2022 08
#
    bin_dir=/app/shell/bin/csv
    datasource_dir=/app/shell/datasource/excel
    current_table_data=${datasource_dir}/${tablename}
    ddl_config=$bin_dir/import_tables_config.txt
    hive_db=$1
    tablename=$2
    csv_fiepath=$3
    year=$4
    month=$5

    echo "database:$1"
    echo "tablename:$2" 
    echo "csv_fiepath:$3"
    echo "year:$4"
    echo "month:$5"  


    if [ $# != 5 ]
    then
        echo "##########################################################################"
        echo "##    输入参数不正确请输入 database,tablename,csvfilepath,year,month    ##"
        echo "##                   脚本未能执行                                       ##"
        echo "##########################################################################"
        exit 1
    fi

    function loaddata (){
        while read line; do
            #备份旧的分隔符变量
            OLD_IFS="$IFS"
            #设置要使用的分隔符
            IFS="="
            #按设定的分隔符拆分字符串为数组
            arr=(${line})
            #恢复原分隔符变量值
            IFS="$OLD_IFS"
            table=${arr[0]}
            table_tmp=${arr[0]}_tmp
            echo "${arr[0]}-------------------${arr[1]}"
           if [ "${table}" = "${tablename}" ]
           then
                sh ${bin_dir}/createtable.sh ${hive_db} ${tablename}
                echo "load数据 hive -e use $hive_db;load data local inpath $csv_fiepath into table ${table_tmp} partition(year=${year}, month=${month});"
                echo "csv_fiepath存放目录:${current_table_data}"
                echo "加载数据到${table_tmp}"
                hive -e "use $hive_db;load data local inpath '${csv_fiepath}' into table ${table_tmp} partition(year=${year},month='${month}');"
                echo "加载数据到${table}"
                echo "执行命令：use $hive_db;insert into table ${table} partition(year=${year},month='${month}')  select ${arr[1]},${arr[2]} from ${table_tmp};"
                hive -e "use $hive_db;insert into table ${table} partition(year=${year},month='${month}') select ${arr[1]},${arr[2]} from ${table_tmp};"
                succeed
                echo "删除临时表${table_tmp}"
                hive -e "use $hive_db;drop table if exists ${table_tmp}"
                succeed
           fi
        done < ${ddl_config}
    }
    
    succeed(){
         if [ $? -eq 0 ]; then
            echo "#################################################"
            echo "##                 执行命令成功                ##"
            echo "#################################################"
         else
            echo "###############################################"
            echo "##               执行命令失败                ##"
            echo "###############################################"
              exit 1
         fi
    }

    main (){
       loaddata
    }
    
    
    main

2.批量加载某个年份的数据

#  sh com_pay_taxs_all.sh test com_pay_taxs_all 2021
time=$(date "+%Y-%m-%d")
workhome=/app/shell
bin_path=$workhome/bin/mysql
allexec_config=${bin_path}/allexec_config
script_dir=$(cd $(dirname $0);pwd)
database=$1
tablename=$2
year=$3


datasource=/app/shell/datasource/excel
current_table_data=${datasource}/${tablename}/$year

source /etc/profile

if [ $# != 3 ]
then
  echo "########################################################################"
  echo "##         输入参数不正确请输入database,tablename,year               ##"
  echo "##                          脚本未能执行                             ##"
  echo "#######################################################################"
  exit 1
fi

function loaddata (){
    cd ${current_table_data}
    for filedir in *; 
    do
      month=$filedir
      sh /app/shell/bin/csv/loaddata2hive.sh ${database} $tablename "/app/shell/datasource/excel/${tablename}/${year}/${month}/*.csv" $year $month
      echo "加载文件目录:$filedir下的/app/shell/datasource/excel/${tablename}/$year/${month}/*.csv文件中..."
    done
    }
    
    
       succeed(){
         if [ $? -eq 0 ]; then
            echo "#################################################"
            echo "##                 执行命令成功                ##"
            echo "#################################################"
         else
            echo "###############################################"
            echo "##               执行命令失败                ##"
            echo "###############################################"
              exit 1
         fi
         }

main (){
   loaddata
}


main