首页 > 代码库 > 使用OPENROWSET、Microsoft.ACE.OLEDB实现大数据量的高效导入

使用OPENROWSET、Microsoft.ACE.OLEDB实现大数据量的高效导入

2024-08-11 08:28:15   220人阅读

首先说明使用的环境是：java和Sqlserver。

最近公司需要进行大数据量的导入操作。原来使用的是Apache POI，虽然可以实现功能，但是因为逻辑处理中需要进行许多校验，处理速度太慢，使用多线程之后也不尽如人意。在网上搜索之后，找到了OPENROWSET和OPENDATASOURCE，发现使用OPENROWSET，可以非常快速的把Excel导入到数据库中。之后的各种校验，我可以通过编写sql来实现。最终结果是6w条数据可以在10秒内完成。当然数据量增加之后，完成时间并不会明显增加。这需要编写的sql比较高效，是另一方面的问题了。

首先可能需要下载一个小的程序AccessDatabaseEngine_X64.exe。

之后需要开启配置

启用：

exec sp_configure ‘show advanced options‘,1reconfigureexec sp_configure ‘Ad Hoc Distributed Queries‘,1reconfigure

关闭：

exec sp_configure ‘Ad Hoc Distributed Queries‘,0reconfigureexec sp_configure ‘show advanced options‘,0reconfigure

之后可以通过OPENROWSET来查询Excel文件的内容。当然也可以改为SELECT INTO存到数据库中。

SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0‘, ‘Excel 12.0;HDR=YES;Database=E:\DataBack\Copy of SD Expired Contracts.xlsx‘, [‘Copy of SD Expired Contracts$‘])

这样使用的前提是你知道需要导入的Sheet的名称。而如果不知道的话就需要使用下面的方法，查询出所有的Sheet名称，再由用户选择导入哪一个。

EXEC sp_addlinkedserver ‘ExcelSource‘, ‘‘,‘Microsoft.ACE.OLEDB.12.0‘,‘E:\DataBack\Copy of SD Expired Contracts.xlsx‘,NULL,‘Excel 8.0‘EXEC sp_addlinkedsrvlogin ‘ExcelSource‘, ‘false‘GOEXECUTE SP_TABLES_EX ‘ExcelSource‘

下面是我的Java代码：

这个方法用户获得Sheet Name List。

	public static List<String> getSheetNameList(String filePath,BaseDao baseDao){		String excelSource = "ExcelSource_"+StringUtils.getUUIDString();		String addSourceSql = "{CALL SP_ADDLINKEDSERVER(?,‘‘,‘Microsoft.ACE.OLEDB.12.0‘,?,NULL,‘Excel 8.0‘)}";		SQLQuery query = baseDao.getSQLQuery(addSourceSql);		query.setParameter(0, excelSource);		query.setParameter(1, filePath);		query.executeUpdate();		String loginSourceSqql = "{CALL SP_ADDLINKEDSRVLOGIN(?,‘false‘)}";		query = baseDao.getSQLQuery(loginSourceSqql);		query.setParameter(0, excelSource);		query.executeUpdate();				String sheetNameSql = "{CALL SP_TABLES_EX(?)}";		query = baseDao.getSQLQuery(sheetNameSql);		query.setParameter(0, excelSource);		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);		List<Map<String, Object>> list = query.list();		List<String> sheetList = new ArrayList<String>();		for(int i=0;i<list.size();i++){			String sheetName = (String) list.get(i).get("TABLE_NAME");			if(sheetName.endsWith("_xlnm#_FilterDatabase")){							}else{				sheetList.add(sheetName);				}		}		return sheetList;	}

这个方法用户创建一个临时表，存储Excel文件内容。临时表的字段名是根据Excel表头来创建的。

	public static String uploadAndCreateTable(String filePath,String sheetName,BaseDao baseDao) {			String importTableName = "tbl_zz_"+StringUtils.getUUIDString();		String uploadFileSql = "SELECT IDENTITY(int, 1, 1) as %s,t.*,CAST(NEWID() AS VARCHAR(36)) AS %s into %s FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0‘,‘Excel 12.0;HDR=YES;Database=%s‘, [%s]) as t";		uploadFileSql = String.format(uploadFileSql,ImportVisitorUtil.importIndex,ImportVisitorUtil.importUUID,importTableName,filePath,sheetName);		SQLQuery query = baseDao.getSQLQuery(uploadFileSql);		query.executeUpdate();		replaceSpecialCharacter(baseDao, importTableName);		changeColumnCollation(baseDao, importTableName);		return importTableName;	}

代码编写过程中发现一个问题，如果Excel表头中含有":"，在编写sql过程中会跟：name这种占位符冲突，我冒号替换成了空格。

	private static void replaceSpecialCharacter(BaseDao baseDao,String tableName){		String cha = ":";		String cha_ = "%:%";		String sql = "SELECT COLUMN_NAME columnName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME like ? ";		SQLQuery query = baseDao.getSQLQuery(sql);		query.setParameter(0, tableName);		query.setParameter(1, cha_);		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);		List<Map<String,Object>> list = query.list();		for(Map<String,Object> map : list){			String columnName = (String) map.get("columnName");			String newColumnName = columnName.replace(cha, " ");			String tableColumnName = String.format("%s.[%s]", tableName, columnName);			String changeSql = "{CALL SP_RENAME(?,?,‘column‘)}";			query = baseDao.getSQLQuery(changeSql);			query.setParameter(0, tableColumnName);			query.setParameter(1, newColumnName);			query.executeUpdate();		}	}

另一个问题是，本地数据库安装的时候使用的排序规则与服务器不一致，导致编写的sql运行时出现错误，提前修改排序规则。　　

	private static void changeColumnCollation(BaseDao baseDao,String tableName){		String defaultCollation = "SQL_Latin1_General_CP1_CI_AS";		String dataType = "nvarchar";		String sql = "SELECT COLUMN_NAME AS columnName,CHARACTER_MAXIMUM_LENGTH AS length FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND COLLATION_NAME <> ? AND DATA_TYPE = ? ";		SQLQuery query = baseDao.getSQLQuery(sql);		query.setParameter(0, tableName);		query.setParameter(1, defaultCollation);		query.setParameter(2, dataType);		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);		List<Map<String,Object>> list = query.list();		for(Map<String,Object> map : list){			String columnName = (String) map.get("columnName");			Integer length = (Integer) map.get("length");			String changeSql = String.format("ALTER TABLE [%s] ALTER COLUMN [%s] %s(%d) COLLATE %s",tableName,columnName,dataType,length,defaultCollation);			query = baseDao.getSQLQuery(changeSql);			query.executeUpdate();		}	}

可以使用下面sql查询出表中的所有列。

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

前台可以让用户现在Excel中每一列对应的真实表的列。对应关系组织好之后，根据需要给临时表添加字段、修改数据等等，最后使用INSERT SELECT插入数据或修改数据。

编写sql的时候可能因为列名的不规范，导致sql语法错误。这时候需要在列名或表名前后添加中括号“[]”。Jaya使用String.format();比较方便。

 

使用OPENROWSET、Microsoft.ACE.OLEDB实现大数据量的高效导入


联系
我们