Компьютерный форум OSzone.net  

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)
-   Программирование и базы данных (http://forum.oszone.net/forumdisplay.php?f=21)
-   -   [решено] SQLite3, сделать выборку из большого файла csv (http://forum.oszone.net/showthread.php?t=349632)

Griboed0ff 16-09-2021 17:38 2966832
SQLite3, сделать выборку из большого файла csv
 
Доброго времени суток! Есть большая таблица csv 1.2ГБ, которая постоянно дополняется данными в конец таблицы. В таблице 52 столбца и уже более 20 миллионов строк. Мне нужно сделать выборку из этой таблицы по списку из AD самых свежих данных, по имени и дате. Я ранее пытался сделать это с помощью powershell и даже сделал, но производительность очень мала и ждать приходится более 8 часов. Мне на этом форуме сразу посоветовали работать с таблицей как с базой данных, я сопротивлялся, но мне прислали еще несколько таблиц больших размеров и мне все же придется научиться работать с базами. Как мне и посоветовали начать с SQLite3. Скачал, распаковал, запустил, создал базу test, импортировал туда свою таблицу. Через sqlitestudio видно, что все названия столбцов импортировались удачно, так же как и данные. В базе есть два столбца, по которым ориентируюсь: Data, System_Unit_Name. Нужно выбрать уникальное значение из System_Unit_Name с самой старшей датой из столбца Data, далее экспортировать всю строку (все 52 столбца) в таблицу или другую базу, чтобы далее работать с ней. Далее уже наверное буду скриптом сравнивать с AD и вылавливать нужные данные. Возможно с базами не так работают, но это мой первый опыт. В идеале я хочу завернуть все в скрипт powershell, чтобы полностью автоматизировать процесс. Проблема в том, что я не могу самостоятельно составить такой запрос. Надеюсь кто-нибудь поможет, ну или вообще я все неправильно придумал и это не осуществимо.

Iska 16-09-2021 21:41 2966847
1. А поле Data у Вас — дата? Или как в csv — строка? Вообще, чтобы:
Цитата:
Цитата Griboed0ff
есть два столбца, по которым ориентируюсь: Data, System_Unit_Name »
поле Data должна иметь тип дата-время. Тогда хоть какая-то гарантия, что не будет двух одинаковых строк по полям Data и System_Unit_Name.

2.
Цитата:
Цитата Griboed0ff
выбрать уникальное значение из 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 2966924
Вложений: 1
Цитата:
Цитата Iska
1. А поле Data у Вас — дата? Или как в csv — строка? »
В столбце Data - дата. В базе пометил столбец как DATETIME.
Цитата:
Цитата Iska
Тогда хоть какая-то гарантия, что не будет двух одинаковых строк по полям Data и System_Unit_Name. »
Дублей с абсолютно одинаковыми строками очень много. Скорее всего перед манипуляциями придется удалить дубли.
Цитата:
Цитата Iska
Или для одного значения из System_Unit_Name может быть одна, для другого — другая «самая старшая дата»? »
Верно.

примерно вижу это так:
Приложил скрин и пример таблицы как ее загрузил в базу и пример желаемого результата.
Получается выбирать из поля Ping значения все кроме NO, потому что поле иногда бывает пустым, но данные присутствуют.
Выбрать все значения для одного System_Unit_Name из таблицы AD_list, куда поместится список имен AD.
Выбрать старшую строку по дате из прошлой выборки.
Поместить в таблицу

Кстати подумал, что если из базы удалить дубли то объем данных порядком сократиться. Например, есть дублирующиеся строки, где все данные в столбцах одинаковые. Т.е. это одна и та же строка скопированная 2-100 раз. Так же по данным видно, что информация редко меняется, кроме поля дата. Если удалить дубли по всем полям кроме дата и оставить только самые младшие значения по дате, то исторические данные и изменения останутся, а база похудеет и будет весить несколько метров, а то сейчас 700 метров.

Iska 17-09-2021 23:10 2966972
Цитата:
Цитата Griboed0ff
Дублей с абсолютно одинаковыми строками очень много. Скорее всего перед манипуляциями придется удалить дубли. »
Рассмотрите вопрос о том, чтобы в поле Data в CSV писались значения даты-времени, а не только даты. И не в виде строки (в кавычках), а в виде какого-либо из стандартных форматов даты-времени. Это будет правильным.

Если сие возможно — подготовьте новый образец файла 1.csv и выложите его в ту тему.

Цитата:
Цитата Griboed0ff
Верно. »
Бяда-пичаль :(. Время обработки будет NxM. Потому что надо будет для каждого из уникальных System_Unit_Name выбирать максимальное значение даты, и только потом отбирать эту строку. Одним запросом такого напрямую не сделать (потому что предикат ORDER BY применяется в последнюю очередь, уже после отбора). Насколько я понимаю, во всяком случае.

Цитата:
Цитата Griboed0ff
Так же по данным видно, что информация редко меняется, кроме поля дата. Если удалить дубли по всем полям кроме дата и оставить только самые младшие значения по дате, то исторические данные и изменения останутся, а база похудеет и будет весить несколько метров, а то сейчас 700 метров. »
Не надо. Это не так работает.

Если будет возможность поизучать теория и принципы построения реляционных баз данных — сделайте это.

El Sanchez 19-09-2021 15:33 2967094
Цитата:
Цитата Iska
Одним запросом такого напрямую не сделать (потому что предикат ORDER BY применяется в последнюю очередь, уже после отбора). »
Iska, группы, Холмс, юзайте группы. А сортировку ТС не просил.
Цитата:
Цитата Griboed0ff
Получается выбирать из поля 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 2967142
Вложений: 2
Цитата:
Цитата Iska
Рассмотрите вопрос о том, чтобы в поле Data в CSV писались значения даты-времени, а не только даты. »
Данные уже имеются в таком виде как есть и приходится работать с ними.
Цитата:
Цитата El Sanchez
выборка из таблицы AD_list в таблицу AD_result »
К сожалению у меня не получилось, скорее всего из-за отсутствия знаний.
Импортировал в базу "all_pc_info", таблицу с данными "all_pc", из которых и надо сделать выборку, так же импортировал в таблицу "AD_list" список имен АД в один столбик (название столбца System_Unit_Name). Далее пробовал делать запрос, но ничего не происходит. Для наглядности, что в базе и скрин команд прикладываю.

El Sanchez 20-09-2021 11:42 2967147
Цитата:
Цитата Griboed0ff
Далее пробовал делать запрос, но ничего не происходит. Для наглядности, что в базе и скрин команд прикладываю »
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 2967154
Цитата:
Цитата El Sanchez
Код:
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 2967173
Цитата:
Цитата Griboed0ff
Или возможно создать еще один запрос, который возьмет значение из таблицы 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 2967176
Цитата:
Цитата El Sanchez
таблица AD_list точно нужна? »
Да точно нужна, там в AD 14k имен.

El Sanchez 20-09-2021 18:49 2967180
Цитата:
Цитата Griboed0ff
Да точно нужна, там в 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 2967197
Цитата:
Цитата El Sanchez
лады: »
Отлично, теперь все готово за пару сек, а не как раньше. Только есть проблема с регистром, похоже в запросе имеется чувствительность, а мои списки AD и данные в таблице, могут иметь разные регистры причем даже с половины слова, например, в AD имя RT-3N, в таблице оно же выглядит RT-3n или rt-3n.
Пробовал COLLATE NOCASE, но это работает только для WHERE

Griboed0ff 21-09-2021 10:11 2967224
Пока только идея возвести 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 2967255
замена Lke
Цитата:
Цитата Griboed0ff
Пока только идея возвести 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 2967269
Цитата:
Цитата El Sanchez
не надо UPDATE, так сделайте: »
Кстати данный метод увеличил время исполнения больше чем в 10 раз.
Оставил вариант с понижением регистра.
Столкнулся с проблемой экспорта таблицы в csv, криво выгружает кириллицу.

Iska 23-09-2021 16:54 2967369
Цитата:
Цитата Griboed0ff
с проблемой экспорта таблицы в csv, криво выгружает кириллицу. »
А именно?

Так-то, в Вашем оригинальном CSV была UTF-8 без BOM.

Griboed0ff 24-09-2021 07:14 2967407
Цитата:
Цитата Iska
А именно? »
именно делаю экспорт
Код:
sqlite3.exe -header D:\SQlite3\base_test\all_pc_info.db -tabs "select * from all_pc_result" > $outfile
в таблице вижу
Код:
Майкрософт Windows 10 Pro        64-разрядная
хотя в оригинальной таблице и при просмотре базы вижу нормальные русские буквы на этом же месте.
Цитата:
Цитата Iska
Так-то, в Вашем оригинальном CSV была UTF-8 без BOM. »
Извините, но это мне ни о чем не говорит. Не понял как мне помогут три байта в начале строки при использовании BOM. Или речь про UTF8 в общем? МБ нужны какие-то действия, чтобы привести таблицу базы в нужную кодировку, которая будет готова к экспорту кириллицы?

Iska 24-09-2021 08:33 2967411
Цитата:
Цитата Griboed0ff
в таблице вижу »
Так это и есть тот же самый UTF-8 без BOM.

Цитата:
Цитата Griboed0ff
которая будет готова к экспорту кириллицы? »
Кириллица у Вас уже экспортирована.

Чем Вы смотрите Ваш $outfile? Чем смотрели 1.csv, который ранее выкладывали по моей просьбе в соседней теме?

Griboed0ff 24-09-2021 09:58 2967418
Цитата:
Цитата Iska
Чем Вы смотрите Ваш $outfile? Чем смотрели 1.csv, который ранее выкладывали по моей просьбе в соседней теме? »
1.csv открывал через excel там есть кириллица, $outfile это тоже файл csv, открываю тоже excel. Если открывать данные файлы через notepad++, все тоже самое.
То есть кракозябры только после экспорта из базы.

Iska 25-09-2021 06:37 2967462
Цитата:
Цитата Griboed0ff
1.csv открывал через excel там есть кириллица »
Кириллица есть. И у меня под Office 2003 она выглядит точно так же:
Скрытый текст

Ровно так же будет выглядеть и $outfile, если его кодировка будет в UTF-8.

Другое дело, если мы будем делать импорт внешних данных из подобного .csv — там можно указать кодировку исходного файла:
Скрытый текст

и тогда кириллица будет нормально отображаться.

Что касается Notepad++ — убедитесь, что выбрана правильная кодировка файла:
Скрытый текст

Griboed0ff 27-09-2021 13:52 2967636
Вложений: 2
Цитата:
Цитата Iska
Другое дело, если мы будем делать импорт внешних данных из подобного .csv — там можно указать кодировку исходного файла: »
Цитата:
Цитата Iska
Что касается Notepad++ — убедитесь, что выбрана правильная кодировка файла: »
Не пойму где косяк..
Возможно есть другие методы экспорта данных?


Время: 00:52.

Время: 00:52.
© OSzone.net 2001-