Java - Имея огромный ArrayList (1 миллион +), как создать его строку в приемлемое количество времени?
Как я уже упоминал, у меня есть огромные ArrayLists в этом формате:
List<List<String>> alist;
Я получаю списки из некоторых.CSV, через которые я перехожу через внешнюю систему баз данных. (Я написал скрипт Visual Objects для экспорта данных, которые нам нужны для некоторых расчетов)
После того, как у меня есть.CSV, я загружаю контент в свой список следующим образом:
String line;
alist = new ArrayList<List<String>>();
int i=0;
// Datei laden, und anschließend die Zeilen der CSV in eine ArrayList speichern
try {
br = new BufferedReader(new FileReader(path));
while((line = br.readLine()) != null)
{
line = line.replace(",", ".");
if(line.endsWith(delimitter))
line = line + " ";
alist.add(Arrays.asList(line.split(delimitter)));
i++;
if(i==10000000)
break;
}
}
Мне требуется ~15900 мсек, чтобы сохранить данные в моем ArrayList (~1,1 миллиона строк и 11 столбцов). Довольно хорошо, я думаю. Теперь, когда у меня есть список в нужном мне формате, я хотел бы создать из него вставку, чтобы мы могли импортировать данные в нашу базу данных. Я создаю вставку так:
public String getInsertString()
{
// Tabelle ergibt sich aus dem Dateinamen, ohne das .csv
String insert="REPLACE INTO "+tablename + " (";
// Spaltennamen aus Array auslesen, immer die erste Zeile des CSV
for(int i=0; i< alist.get(0).size();i++)
{
if(i==0)
insert = insert + alist.get(0).get(i).trim();
else
insert = insert + " ,"+ alist.get(0).get(i).trim();
}
insert= insert + ") \rVALUES";
// Values der SPalten in den Insert schreiben + korreckte Syntax des Bfehels sicherstellen usw.
for(int i=1;i < alist.size();i++) // Size nach "unten" in der 2D Liste
{
insert= insert +"(";
for(int j=0; j < alist.get(0).size();j++) // Size nach "rechts" in der 2D Liste
{
// bei dem ersten ohne "," starten, damit die Syntax stimmt
// Sollte der aktuelle Wert eine Zahl oder "null" sein, keine "'" setzen. Ansonsten "'" setzen fuer den Insert in die DB
if(j==0)
{
if((StringUtils.isStrictlyNumeric(alist.get(i).get(j).trim())) || alist.get(i).get(j).trim().contains("null"))
insert = insert + alist.get(i).get(j).trim();
else
insert = insert + "'" + alist.get(i).get(j).trim() + "'";
}
else
{
if(((StringUtils.isStrictlyNumeric(alist.get(i).get(j).trim()))) || (alist.get(i).get(j).trim().contains("null")))
insert = insert +","+ alist.get(i).get(j).trim();
else
insert = insert + ",'" + alist.get(i).get(j).trim() + "'";
}
}
if(i < alist.size()-1)
insert= insert +"),";
else
insert= insert +")";
insert = insert +"\r";
}
//System.out.println(insert);
return insert;
}
Здесь я просматриваю весь список и добавляю значения в строку, чтобы можно было использовать эту строку для вставки. Я использую имена файлов в качестве имен таблиц и первую строку файла для столбцов вставки. Все остальные строки являются значениями.
После того, как этот шаг сделан, я получаю строку типа "ЗАМЕНИТЬ INTO tablename (column1,column2 ... columnx) VALUES(значение1, значение2... значение x), (значение1, значение2... значение x), ...."
Теперь я извиняю свой класс InsertInDb этой строкой, и да, вот и все.
Но второй шаг занимает слишком много времени. (Я жду около часа сейчас) Есть ли более умный способ сделать то, что я хочу сделать? (Вставьте все CSV автоматически в нашей базе данных)
Может ли BigList улучшить скорость? https://dzone.com/articles/biglist-scalable-high(сейчас не могу проверить)
Изменить: что я сделал, чтобы решить эту проблему:
Я создал класс для своего оператора SQL ->
public class BuildInsert {
private String insertString;
public String getINSERTSTRING()
{
return insertString;
}
BuildInsert(String tablename, List<String> alist )
{
int size = alist.size();
this.insertString = "REPLACE INTO " + tablename + "(";
// Insert "Header"
for(int j=1; j <= size ;j++)
{
if(j < size)
this.insertString = this.insertString + alist.get(j-1)+",";
else
this.insertString = this.insertString + alist.get(j-1)+")\n";
}
this.insertString = this.insertString +"VALUES(";
// Insert values
for(int j=1; j <= size ;j++)
{
if(j < size)
this.insertString = this.insertString + "?,";
else
this.insertString = this.insertString + "?)";
}
}
}
и пометить эту строку для пакетной вставки / подготовленного оператора, как упомянул Майк:
Connection con;
Statement stmt;
final int batchSize = 1000;
int count = 0;
int sizeH = alist.size();
int sizeL = alist.get(0).size();
try {
// Connection oeffnen und prepared statment vorbereiten
System.out.println("Connecting to database...");
con = DriverManager.getConnection(DB_URL,USER,PASS);
con.setAutoCommit(false);
ps = con.prepareStatement(insertString);
stmt = con.createStatement();
//< alist.size()
for(int i=1;i < sizeH ;i++) // Size nach "unten"
{
for(int j=0; j < sizeL;j++) // Size nach "rechts"
{
ps.setString(j+1, alist.get(i).get(j));
}
ps.addBatch();
if(++count % batchSize == 0){
ps.executeBatch();
con.commit();
}
}
ps.executeBatch();
con.commit();
}
Вставка теперь намного быстрее. (~230 сек для 1,1 млн рядов)
Спасибо вам, ребята;-)
3 ответа
Now, that I have the List in the format I need, I would like to create a Insert out of it, so we can import the data in our database.
Не делай этого! Вместо этого выполните пакетную вставку JDBC. (Смотрите это о том, как сделать PreparedStatement, который избавит вас от многих проблем при форматировании операторов вставки)
Так как вы хотите загрузить файл CSV в MySQL, вы должны использовать инструменты MySQL. Например, НАГРУЗКА ДАННЫХ INFILE
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
Если вы все еще хотите сделать это самостоятельно, используйте JDBC Batch Insert. Также подумайте об использовании PreparedStatement с JDBC партии. Смотрите, используя JDBC подготовленное состояние в пакете
Я постараюсь ответить на ваш вопрос: "второй шаг занимает слишком много времени. (Я жду около часа сейчас) Есть ли более умный способ делать то, что я хочу сделать? (Автоматически вставьте все CSV в нашу базу данных")"
Самое быстрое решение
Как указано в справочном руководстве по MySQL ( скорость выражений INSERT), самый быстрый способ - использовать LOAD DATA INFILE.
Решение Java
Хотя, если вы хотите попробовать и решение Java, вы можете пересмотреть свою работу. Вставка, созданная вашим кодом, использует синтаксис с несколькими значениями (рекомендуемый), но он слишком длинный:
11*10^6 rows x 11 cols x 10B per col = 11^2*10^7 = approx. 1GB
Поэтому постарайтесь:
- установить собственную переменную batch-size и создать несколько операторов вставки с не более чем таким количеством нескольких значений
- проверьте, что выбранный вами размер пакета меньше, чем bulk_insert_buffer_size (см. ссылку в той же ссылке выше)
- используйте PreparedStatement, но вместо строки для выполнения вам нужно построить строку (с? вместо значений) и массив аргументов (значений); Учебник по Java Основы JDBC - Использование подготовленных операторов
- если вы используете PreparedStatement, то вы можете избежать проверки, собираетесь ли вы вставить число, строку или другое (используя
setObjectметод), чтобы вы могли избежать лишнего контроля над содержимым поля CSV (это проверка, и вы должны переместить его на шаге разбора)