Показать полную графическую версию : [решено] SQLite3, сделать выборку из большого файла csv
Griboed0ff
16-09-2021, 17:38
Доброго времени суток! Есть большая таблица csv 1.2ГБ, которая постоянно дополняется данными в конец таблицы. В таблице 52 столбца и уже более 20 миллионов строк. Мне нужно сделать выборку из этой таблицы по списку из AD самых свежих данных, по имени и дате. Я ранее пытался сделать это с помощью powershell и даже сделал, но производительность очень мала и ждать приходится более 8 часов. Мне на этом форуме сразу посоветовали работать с таблицей как с базой данных, я сопротивлялся, но мне прислали еще несколько таблиц больших размеров и мне все же придется научиться работать с базами. Как мне и посоветовали начать с SQLite3. Скачал, распаковал, запустил, создал базу test, импортировал туда свою таблицу. Через sqlitestudio видно, что все названия столбцов импортировались удачно, так же как и данные. В базе есть два столбца, по которым ориентируюсь: Data, System_Unit_Name. Нужно выбрать уникальное значение из System_Unit_Name с самой старшей датой из столбца Data, далее экспортировать всю строку (все 52 столбца) в таблицу или другую базу, чтобы далее работать с ней. Далее уже наверное буду скриптом сравнивать с AD и вылавливать нужные данные. Возможно с базами не так работают, но это мой первый опыт. В идеале я хочу завернуть все в скрипт powershell, чтобы полностью автоматизировать процесс. Проблема в том, что я не могу самостоятельно составить такой запрос. Надеюсь кто-нибудь поможет, ну или вообще я все неправильно придумал и это не осуществимо.
1. А поле Data у Вас — дата? Или как в csv — строка? Вообще, чтобы:
есть два столбца, по которым ориентируюсь: Data, System_Unit_Name »
поле Data должна иметь тип дата-время. Тогда хоть какая-то гарантия, что не будет двух одинаковых строк по полям Data и System_Unit_Name.
2. выбрать уникальное значение из System_Unit_Name с самой старшей датой из столбца Data »
Вот эта самая «самая старшая дата» — она будет одинакова для всех искомых System_Unit_Name? Или для одного значения из System_Unit_Name может быть одна, для другого — другая «самая старшая дата»?
В первом случае проблем нет (я и в параллельной теме сделал для текстовых файлов, всё никак не допишу текстовку; ответите здесь на вопросы — допишу):
SELECT DISTINCT *
INTO [ResultTable]
FROM [SourceTable]
WHERE
Ping = 'Yes' AND
Data = (SELECT MAX(Data) FROM [SourceTable]) AND
System_Unit_Name IN (SELECT MachineName FROM [MachineNames])
ORDER BY System_Unit_Name
Выбираем уникальные строки из исходной таблицы по условиям:
поле Ping = 'Yes';
поле Data равно максимальному из значений поля Data;
поле System_Unit_Name входит в перечень значений поля MachineName из таблицы MachineNames (туда Вы помещаете Ваш список имён машин из AD).
Сортируем результирующие строки по полю System_Unit_Name. Помещаем результирующие строки в таблицу ResultTable.
Griboed0ff
17-09-2021, 15:31
1. А поле Data у Вас — дата? Или как в csv — строка? »
В столбце Data - дата. В базе пометил столбец как DATETIME.
Тогда хоть какая-то гарантия, что не будет двух одинаковых строк по полям Data и System_Unit_Name. » Дублей с абсолютно одинаковыми строками очень много. Скорее всего перед манипуляциями придется удалить дубли.
Или для одного значения из System_Unit_Name может быть одна, для другого — другая «самая старшая дата»? »
Верно.
примерно вижу это так:
Приложил скрин и пример таблицы как ее загрузил в базу и пример желаемого результата.
Получается выбирать из поля Ping значения все кроме NO, потому что поле иногда бывает пустым, но данные присутствуют.
Выбрать все значения для одного System_Unit_Name из таблицы AD_list, куда поместится список имен AD.
Выбрать старшую строку по дате из прошлой выборки.
Поместить в таблицу
Кстати подумал, что если из базы удалить дубли то объем данных порядком сократиться. Например, есть дублирующиеся строки, где все данные в столбцах одинаковые. Т.е. это одна и та же строка скопированная 2-100 раз. Так же по данным видно, что информация редко меняется, кроме поля дата. Если удалить дубли по всем полям кроме дата и оставить только самые младшие значения по дате, то исторические данные и изменения останутся, а база похудеет и будет весить несколько метров, а то сейчас 700 метров.
Дублей с абсолютно одинаковыми строками очень много. Скорее всего перед манипуляциями придется удалить дубли. »
Рассмотрите вопрос о том, чтобы в поле Data в CSV писались значения даты-времени, а не только даты. И не в виде строки (в кавычках), а в виде какого-либо из стандартных форматов даты-времени. Это будет правильным.
Если сие возможно — подготовьте новый образец файла 1.csv и выложите его в ту тему.
Верно. »
Бяда-пичаль :(. Время обработки будет NxM. Потому что надо будет для каждого из уникальных System_Unit_Name выбирать максимальное значение даты, и только потом отбирать эту строку. Одним запросом такого напрямую не сделать (потому что предикат ORDER BY применяется в последнюю очередь, уже после отбора). Насколько я понимаю, во всяком случае.
Так же по данным видно, что информация редко меняется, кроме поля дата. Если удалить дубли по всем полям кроме дата и оставить только самые младшие значения по дате, то исторические данные и изменения останутся, а база похудеет и будет весить несколько метров, а то сейчас 700 метров. »
Не надо. Это не так работает.
Если будет возможность поизучать теория и принципы построения реляционных баз данных — сделайте это.
El Sanchez
19-09-2021, 15:33
Одним запросом такого напрямую не сделать (потому что предикат ORDER BY применяется в последнюю очередь, уже после отбора). »
Iska, группы, Холмс, юзайте группы. А сортировку ТС не просил.
Получается выбирать из поля Ping значения все кроме NO, потому что поле иногда бывает пустым, но данные присутствуют.
Выбрать все значения для одного System_Unit_Name из таблицы AD_list, куда поместится список имен AD.
Выбрать старшую строку по дате из прошлой выборки.
Поместить в таблицу »
Griboed0ff, выборка из таблицы AD_list в таблицу AD_result
CREATE TABLE IF NOT EXISTS AD_result AS
SELECT *
FROM AD_list
WHERE Ping <> 'No'
GROUP BY System_Unit_Name HAVING max(printf('%s-%s-%s', substr(Data, length(Data) + 1, -4), substr(Data, instr(Data, '.') + 1, 2), substr(Data, 1, 2)))
Griboed0ff
20-09-2021, 10:51
Рассмотрите вопрос о том, чтобы в поле Data в CSV писались значения даты-времени, а не только даты. »
Данные уже имеются в таком виде как есть и приходится работать с ними.
выборка из таблицы AD_list в таблицу AD_result »
К сожалению у меня не получилось, скорее всего из-за отсутствия знаний.
Импортировал в базу "all_pc_info", таблицу с данными "all_pc", из которых и надо сделать выборку, так же импортировал в таблицу "AD_list" список имен АД в один столбик (название столбца System_Unit_Name). Далее пробовал делать запрос, но ничего не происходит. Для наглядности, что в базе и скрин команд прикладываю.
El Sanchez
20-09-2021, 11:42
Далее пробовал делать запрос, но ничего не происходит. Для наглядности, что в базе и скрин команд прикладываю »
Griboed0ff,
CREATE TABLE IF NOT EXISTS all_pc_result AS
SELECT *
FROM all_pc
WHERE Ping <> 'No'
GROUP BY System_Unit_Name HAVING max(printf('%s-%s-%s', substr(Data, length(Data) + 1, -4), substr(Data, instr(Data, '.') + 1, 2), substr(Data, 1, 2)))
Griboed0ff
20-09-2021, 13:32
CREATE TABLE IF NOT EXISTS all_pc_result AS
SELECT *
FROM all_pc
WHERE Ping <> 'No'
GROUP BY System_Unit_Name HAVING max(printf('%s-%s-%s', substr(Data, length(Data) + 1, -4), substr(Data, instr(Data, '.') + 1, 2), substr(Data, 1, 2))); »
Спасибо получилось! Но насколько я понял, создается таблица из запроса, выбирается таблица с данными, не берем строки со значением NO, а далее просто выбирается для каждого System_Unit_Name, строка с максимальной датой. Т.е. никакого сравнения со списком AD из таблицы AD_list нет. В принципе я уже получил хороший результат и можно сделать впр со списком AD вручную или powershell. Или возможно создать еще один запрос, который возьмет значение из таблицы AD_list столбик System_Unit_Name, найдет его с столбике System_Unit_Name таблицы all_pc_result, экспортирует строку его в еще одну таблицу. Ну или возможно добавить это в старый запрос?
El Sanchez
20-09-2021, 17:04
Или возможно создать еще один запрос, который возьмет значение из таблицы AD_list столбик System_Unit_Name, найдет его с столбике System_Unit_Name таблицы all_pc_result, экспортирует строку его в еще одну таблицу. Ну или возможно добавить это в старый запрос? »
Griboed0ff, таблица AD_list точно нужна? Не, можно и её использовать, можно и конкретные System_Unit_Name в запросе указывать:
...
WHERE Ping <> 'No' AND System_Unit_Name IN ('foo', 'bar', 'baz')
...
Griboed0ff
20-09-2021, 18:03
таблица AD_list точно нужна? »
Да точно нужна, там в AD 14k имен.
El Sanchez
20-09-2021, 18:49
Да точно нужна, там в AD 14k имен. »
Griboed0ff, лады:
CREATE TABLE IF NOT EXISTS all_pc_result AS
SELECT *
FROM all_pc, AD_list USING(System_Unit_Name)
WHERE Ping <> 'No'
GROUP BY System_Unit_Name HAVING max(printf('%s-%s-%s', substr(Data, length(Data) + 1, -4), substr(Data, instr(Data, '.') + 1, 2), substr(Data, 1, 2)))
Griboed0ff
21-09-2021, 00:19
лады: » Отлично, теперь все готово за пару сек, а не как раньше. Только есть проблема с регистром, похоже в запросе имеется чувствительность, а мои списки AD и данные в таблице, могут иметь разные регистры причем даже с половины слова, например, в AD имя RT-3N, в таблице оно же выглядит RT-3n или rt-3n.
Пробовал COLLATE NOCASE, но это работает только для WHERE
Griboed0ff
21-09-2021, 10:11
Пока только идея возвести AD_list(System_Unit_Name) и all_pc(System_Unit_Name) в один регистр, а уже потом делать выборки.
Решение:
UPDATE all_pc
SET System_Unit_Name = LOWER(System_Unit_Name);
UPDATE AD_list
SET System_Unit_Name = LOWER(System_Unit_Name);
CREATE TABLE IF NOT EXISTS all_pc_result AS
SELECT *
FROM all_pc, AD_list USING(System_Unit_Name)
WHERE Ping <> 'No'
GROUP BY System_Unit_Name HAVING max(printf('%s-%s-%s', substr(Data, length(Data) + 1, -4), substr(Data, instr(Data, '.') + 1, 2), substr(Data, 1, 2)));
Возможно это делается прямо из запроса, но выборка получается полная, значит цель достигнута. Всем спасибо за помощь! Теперь попытаюсь это автоматизировать через PSSQLite (модуль для powershell). Запрашивать нужную аушку, создавать свежий список в ad_list. Далее выборка из таблицы с данными. Далее экспорт таблицы csv. Но обсуждение наверное не для этой ветки форума.
El Sanchez
21-09-2021, 14:22
замена LkeПока только идея возвести AD_list(System_Unit_Name) и all_pc(System_Unit_Name) в один регистр, а уже потом делать выборки. »
Griboed0ff, не надо UPDATE, так сделайте:
CREATE TABLE IF NOT EXISTS all_pc_result AS
SELECT all_pc.*
FROM all_pc, AD_list
ON lower(AD_list.System_Unit_Name)=lower(all_pc.System_Unit_Name)
WHERE Ping <> 'No'
GROUP BY AD_list.System_Unit_Name HAVING max(printf('%s-%s-%s', substr(Data, length(Data) + 1, -4), substr(Data, instr(Data, '.') + 1, 2), substr(Data, 1, 2)))
Griboed0ff
21-09-2021, 21:16
не надо UPDATE, так сделайте: »
Кстати данный метод увеличил время исполнения больше чем в 10 раз.
Оставил вариант с понижением регистра.
Столкнулся с проблемой экспорта таблицы в csv, криво выгружает кириллицу.
с проблемой экспорта таблицы в csv, криво выгружает кириллицу. »
А именно?
Так-то, в Вашем оригинальном CSV была UTF-8 без BOM.
Griboed0ff
24-09-2021, 07:14
А именно? »
именно делаю экспортsqlite3.exe -header D:\SQlite3\base_test\all_pc_info.db -tabs "select * from all_pc_result" > $outfile
в таблице вижу Майкрософт Windows 10 Pro 64-разрядная
хотя в оригинальной таблице и при просмотре базы вижу нормальные русские буквы на этом же месте.
Так-то, в Вашем оригинальном CSV была UTF-8 без BOM. »
Извините, но это мне ни о чем не говорит. Не понял как мне помогут три байта в начале строки при использовании BOM. Или речь про UTF8 в общем? МБ нужны какие-то действия, чтобы привести таблицу базы в нужную кодировку, которая будет готова к экспорту кириллицы?
в таблице вижу »
Так это и есть тот же самый UTF-8 без BOM.
которая будет готова к экспорту кириллицы? »
Кириллица у Вас уже экспортирована.
Чем Вы смотрите Ваш $outfile? Чем смотрели 1.csv, который ранее выкладывали по моей просьбе в соседней теме?
Griboed0ff
24-09-2021, 09:58
Чем Вы смотрите Ваш $outfile? Чем смотрели 1.csv, который ранее выкладывали по моей просьбе в соседней теме? »
1.csv открывал через excel там есть кириллица, $outfile это тоже файл csv, открываю тоже excel. Если открывать данные файлы через notepad++, все тоже самое.
То есть кракозябры только после экспорта из базы.
1.csv открывал через excel там есть кириллица »
Кириллица есть. И у меня под Office 2003 она выглядит точно так же:
https://i.imgur.com/SHvSLCo.png
Ровно так же будет выглядеть и $outfile, если его кодировка будет в UTF-8.
Другое дело, если мы будем делать импорт внешних данных из подобного .csv — там можно указать кодировку исходного файла:
https://i.imgur.com/bS8kvAJ.png
и тогда кириллица будет нормально отображаться.
Что касается Notepad++ — убедитесь, что выбрана правильная кодировка файла:
https://i.imgur.com/nFg05dV.png
© OSzone.net 2001-2012
vBulletin v3.6.4, Copyright ©2000-2025, Jelsoft Enterprises Ltd.