Выполнение SQL для данных CSV: преобразование и извлечение данных

Многие инструменты выводят данные в виде значений, разделенных запятыми (CSV [https://en.wikipedia.org/wiki/Comma-separated_values]). Это простой, но распространенный формат табличных данных в виде открытого текста, который легко поддается обработке. Каждая строка файла представляет собой отдельную запись. Запись состоит из того же количества полей или столбцов. Обычно разделителем между отдельными полями является запятая (,), точка с запятой (;), пробел или табулятор. Один или несколько файлов CSV можно обработать с помощью инструментов командной строки cu.

Многие инструменты выводят данные в виде значений, разделенных запятыми ( CSV ). Это простой, но распространенный формат табличных данных в виде открытого текста, который легко поддается обработке. Каждая строка файла представляет собой отдельную запись. Запись состоит из того же количества полей или столбцов. Обычно разделителем между отдельными полями является запятая ( , ), ; ), пробел или табулятор.

Один или несколько файлов CSV можно обработать с помощью инструментов командной строки cut , join , head , comm , sed и awk . Этот подход работает очень хорошо, но может потребоваться время, чтобы найти правильное решение на основе этих команд.

В следующем примере показано, как рассчитать общее расстояние проезда для приведенного ниже списка поездок. Столбцы в файле tracks.csv разделены табуляциями, а сценарий awk суммирует значения только в третьем столбце.

 $ cat tracks.csv 
 Date Track Distance 
 1 Dec 2018 Paris-Metz 300 
 3 Dec 2018 Metz-Nancy 57 
 4 Dec 2018 Nancy-Vesoul 156 
 5 Dec 2018 Vesoul-Mulhouse 112 
 $ awk -F '\t+' '{ total += $3 } END {printf "total: %d km\n", total}' tracks.csv 
 total: 625 km 

Напротив, системы управления базами данных (СУБД), такие как MySQL, MariaDB, PostgreSQL и SQLite, хранят данные в формате, отличном от обычного текста, который редко может быть прочитан открытым способом. Для извлечения данных операторы должны быть сформулированы на языке структурированных запросов (SQL) и обработаны СУБД.

В приведенном ниже заявлении показано, как извлечь содержимое столбца Track из таблицы с именем track:

 SELECT Track FROM track 
 Paris-Metz 
 Metz-Nancy 
 Nancy-Vesoul 
 Vesoul-Mulhouse 
 (4 lines) 

Далее мы объясним, как запускать операторы SQL непосредственно для данных CSV. Есть довольно много умных инструментов, позволяющих соединить эти миры друг с другом.

q

Первый инструмент называется просто q , и его название фактически происходит от The Q Continuum , упомянутого в серии Star Trek. Соответствующий пакет Debian для q называется python-q-text-as-data . q принимает операторы SQL и разрешает прямое выполнение для одного или нескольких файлов CSV.

Ниже вы увидите, как извлечь первый столбец с именем Date из файла CSV.

 $ q -H -t "SELECT Date FROM tracks.csv" 
 1 Dec 2018 
 3 Dec 2018 
 4 Dec 2018 
 5 Dec 2018 

Два флага, использованные выше, имеют следующее значение:

  • -H ( --skip-header ): первая строка файла данных содержит описание столбцов (заголовков).

  • -t : столбцы разделены таблицами.

Для других флагов мы рекомендуем вам взглянуть на страницу использования q .

Чтобы получить наборы данных на основе условий, вы можете написать следующий оператор, который извлекает значение расстояния из всех наборов данных, в которых хранится дата 3 декабря 2018 года:

 $ q -H -t "SELECT Distance FROM tracks.csv WHERE Date = '3 Dec 2018'" 
 57 

q следует синтаксису SQL, используемому SQLite . Согласно сайту проекта разрешены любые стандартные выражения SQL, условия (как WHERE и HAVING ), GROUP BY , ORDER BY и т. Д. В предложении WHERE JOIN и подзапросы.

Сюда также входит ряд функций, таких как ABS() , LENGTH() , LOWER() и UPPER() а также функции даты и времени, агрегатные функции и функции JSON. Чтобы вычислить общее количество одиночных рейсов, используйте следующую инструкцию, демонстрирующую функцию SUM()

 $ q -H -t "SELECT SUM(Distance) FROM tracks.csv" 
 625 

Следующий пример заимствован с q и демонстрирует, как извлечь системную информацию. Он комбинирует ps и q на обычной рабочей станции, чтобы вычислить первые 5 идентификаторов пользователей с наибольшим количеством собственных процессов, отсортированных в порядке убывания.

 $ ps -ef | q -H "SELECT UID,COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 5" 
 root 129 
 frank 68 
 postgres 6 
 www-data 5 
 avahi 2 

q также подключается к исходной библиотеке Python sqlite. Аналогичную функциональность предоставляет модуль Python querycsv, который, кажется, разветвляется как проект querycsv-redux .

fsql

fsql является частью модуля Perl с именем fsql . В настоящее время он недоступен в виде пакета для Debian или Ubuntu, но доступен в Comprehensive Perl Archive Network (CPAN). Чтобы использовать fsql вы можете установить модуль Perl с его зависимостями пакетов с помощью cpanm следующим образом:

 # cpanm App::fsql 
 --> Working on App::fsql 
 Fetching http://www.cpan.org/authors/id/P/PE/PERLANCAR/App-fsql-0.230.tar.gz ... OK 
 Configuring App-fsql-0.230 ... OK 
 ==> Found dependencies: DBD::CSV, JSON::MaybeXS, Data::Format::Pretty::YAML, Data::Format::Pretty::Console, YAML::XS, Data::Format::Pretty::JSON, File::chdir, Data::Format::Pretty::Perl, Text::LTSV, Perinci::Result::Util, File::Slurper 
 --> Working on DBD::CSV 
 Fetching http://www.cpan.org/authors/id/H/HM/HMBRAND/DBD-CSV-0.54.tgz ... OK 
 Configuring DBD-CSV-0.54 ... OK 
 ==> Found dependencies: SQL::Statement 
 --> Working on SQL::Statement 
 Fetching http://www.cpan.org/authors/id/R/RE/REHSACK/SQL-Statement-1.412.tar.gz ... OK 
 Configuring SQL-Statement-1.412 ... OK 
 
 [...] 
 
 Building and testing App-fsql-0.230 ... OK 
 Successfully installed App-fsql-0.230 
 95 distributions installed 

Как объясняется на fsql этот инструмент позволяет выполнять SQL-запросы к одному или нескольким «плоским» файлам различных форматов, таких как простой текст, CSV и JSON.

В приведенном ниже примере показано, как извлечь все рейсы на расстояние более 100 км. Данные предоставляются из stdin с использованием cat fsql по конвейеру в fsql, затем:

 $ cat tracks.csv | fsql 'SELECT Date,Track,Distance FROM stdin WHERE Distance > 100' 
 Date Track Distance 
 "1 Dec 2018" Paris-Metz 300 
 "4 Dec 2018" Nancy-Vesoul 156 
 "5 Dec 2018" Vesoul-Mulhouse 112 

До сих пор fsql выдавал строковый вывод, который является обычным для баз данных. Используя выходной параметр -f плюс желаемый выходной формат, он может делать гораздо больше. Среди прочего, fsql поддерживает значения csv ), значения, разделенные tsv (tsv), Perl ( perl ), нотацию объектов Javascript (JSON) ( json ) и YAML Ain't Markup Language (YAML) ( yaml ).

В приведенном ниже примере результат запроса выводится в виде массива JSON:

 $ cat tracks.csv | fsql 'SELECT Date,Track,Distance FROM stdin WHERE Distance > 60' -f json 
 [ 
 [ 
 "1 Dec 2018", 
 "Paris-Metz", 
 "300" 
 ], 
 [ 
 "4 Dec 2018", 
 "Nancy-Vesoul", 
 "156" 
 ], 
 [ 
 "5 Dec 2018", 
 "Vesoul-Mulhouse", 
 "112" 
 ] 
 ] 

Используя параметр -f yaml вывод форматируется в стиле YAML:

 $ cat tracks.csv | fsql 'SELECT sum(Distance) FROM stdin' -f yaml 
 --- 
 - 
 - 625 

Миллер

Если спросить у опытных экспертов по Linux о Миллере, это может немного удивить, поскольку он менее известен, чем другие варианты. Миллер стремится объединить функциональность нескольких текстовых инструментов, таких как cat , grep , sed , awk , join и sort .

На приведенном ниже снимке экрана используется cut и показано, как извлечь второй столбец, содержащий маршруты. Этот вызов аналогичен SQL-запросу SELECT Track from track .

MillerCut{.ezlazyload .img-responsive}

Другие

Список полезных инструментов можно на время пополнить. Д. Богдан ведет довольно обширный список . Среди прочего, он содержит termsql , jq , а также SPAWK, который является AWK на базе SQL .

Забавные штучки, о которых мы поговорим подробнее в одной из следующих статей.

Благодарности

Автор благодарит Акселя Беккерта за его критические замечания при подготовке статьи.

Licensed under CC BY-NC-SA 4.0
comments powered by Disqus