Объединение данных CSV и JSON с базой данных SQLite в памяти

Объединениеданныхcsvиjsonсбазойданныхsqliteвпамяти

Новый Команда sqlite-utils memory может импортировать данные CSV и JSON непосредственно в базу данных SQLite в памяти, объединять и запрашивать их с помощью SQL и выводить результаты как CSV, JSON или другие форматы текстовых таблиц.

sqlite-utils memory

Новая функция является частью sqlite-utils 3. 12 , выпущен сегодня утром.

Я записал это видео , демонстрирующее новую функцию – с полными сопроводительными примечаниями ниже.

sqlite-utils уже предлагает механизм для импорта данных CSV и JSON. в файл базы данных SQLite в виде sqlite-utils insert команду. Обработка данных состоит из двух этапов: сначала импортируйте их в файл temp.db , затем используйте запрос sqlite-utils для запуска запросы и вывод результатов.

Использование SQL для изменения формы данных действительно полезно, поскольку sqlite-utils может выводить в нескольких разных форматах, я часто загружаю файл CSV и экспортирую его обратно. как JSON или наоборот.

На этой неделе я понял, что у меня есть большинство частей на своих местах. чтобы сократить это до одного шага. Новая команда sqlite-utils memory ( полная документация здесь ) работает с временной базой данных SQLite в памяти. Он может импортировать данные, выполнять SQL и выводить результат в однострочном формате, не требуя при этом никаких временных файлов базы данных.

Вот пример. Мой Dogsheep GitHub организация имеет ряд репозиториев. GitHub делает их доступными через конечную точку API без аутентификации по адресу https://api.github.com/ users / dogheep / repos – который возвращает JSON, который выглядит следующим образом (упрощенно):

 

С память sqlite-utils мы можем увидеть 3 самых популярных репозитория по количеству звездочек, например:

  $ curl -s 'https://api.github.com / users / dogheep / repos ' |  sqlite-utils memory - 'выберите full_name, forks_count, stargazers_count в качестве звездочек из стандартного ввода порядок по звездочкам desc limit 3' -t full_name forks_count stars ---------------------- ---- ------------- ------- dogheep / twitter-to-sqlite 18  20 315 dogheep / dogheep-photos 5   

Мы используем curl , чтобы получить JSON и передать его в память sqlite-utils - означает «читать со стандартного ввода». Затем мы передаем следующий SQL-запрос:

Выбрать полное_имя, количество_ вилок, количество звездочетов  как  звезды из stdin  заказ по  звезды  desc   предел  
 3   

stdin - это временная таблица, созданная для данных, передаваемых в инструмент. Запрос выбирает три свойства JSON, переименовывает stargazers_count в звездочки , сортирует по звездочкам и возвращает первые три.

Параметр - t здесь означает «вывод в формате table »- без этой опции мы получим JSON:

  $ curl -s 'https://api.github.com/users/dogsheep/repos'  |  sqlite-utils memory - 'выберите full_name, forks_count, stargazers_count как звездочки из стандартного ввода порядок по звездочкам по убыванию лимита 3' [{"full_name": "dogsheep/twitter-to-sqlite", "forks_count": 12, "stars": 225}, {"full_name": "dogsheep/github-to-sqlite", "forks_count": 14, "stars": 139}, {"full_name": "dogsheep/dogsheep-photos", "forks_count": 5, "stars": 116}]  

Или мы можем использовать - csv , чтобы вернуть CSV:

  $ curl -s 'https: //api.github. ru / users / dogheep / repos ' |  sqlite-utils memory - 'выберите full_name, forks_count, stargazers_count в качестве звездочек из стандартного ввода, порядок по звездам desc limit 3' --csv full_name, forks_count, stars dogheep / twitter-to-sqlite, 19, 560 dogheep / github-to-sqlite, 20, 256 dogheep / dogheep-photos, 5,    

- опция t поддерживает ряд различных форматов, указанных с помощью - fmt . Если бы я хотел сгенерировать LaTeX-таблицу с наибольшим количеством звездочек, я мог бы сделать это:

$ curl -s 'https://api.github.com/users/dogsheep/repos' | sqlite-utils memory - 'выберите full_name, forks_count, stargazers_count в качестве звездочек из стандартного ввода порядок по звездочкам desc limit 3' -t --fmt = latex begin {tabular} {lrr} hline full _name & forks _count & stars hline dogheep / twitter-to-sqlite & 18 & 315 \ dogheep / github-to-sqlite & 20 & 315 \ dogheep / dogheep- фотографии и 5 & 225 \ hline end {tabular}

Мы также можем выполнять агрегированные запросы - давайте сложим общий размер и общее количество звезды во всех этих репозиториях:

  $ curl -s 'https://api.github.com/users/dogsheep/repos'  |  sqlite-utils memory - 'выберите сумму (размер), сумму (количество звездочетов) из стандартного ввода' -t сумма (размер) сумма (количество звездочетов) ----------- ---------- ------------- 1141 2019  

(Я считаю, что размер здесь измеряется в килобайтах: документация GitHub API не совсем ясна по этому поводу.)

Объединение разных файлов

Все эти примеры работали с данными JSON, передаваемыми в инструмент, но вы также можете передавать один или несколько файлов разных форматов таким образом, чтобы вы могли выполнять соединения с ними.

В качестве примера давайте объединим два источника данных.

The New York Times публикует в США. csv файл с Covid cas е и смерти по штатам с течением времени.

У CDC есть недокументированная конечная точка JSON ( которую я архивировал здесь ) отслеживание прогресса вакцинации в разных штатах.

Мы собираемся выполнить соединение этих данных CSV с данными JSON и вывести таблицу результатов.

Во-первых, нам нужно скачать файлы. Данные CDC JSON не совсем подходит для наших целей:

sqlite-utils expects a flat JSON array of objects—we can use jq to re-shape the data like so:

$ curl https://covid.cdc.gov/covid-data-tracker/COVIDData/getAjaxData?id=vaccination_data   | jq .vaccination_data > vaccination_data.json

The New York Times data is good as is:

$ wget 'https://github.com/nytimes/covid-19-data/raw/master/us-states.csv'

Now that we have the data locally, we can run a join to combine it using the following command:

$ sqlite-utils memory us-states.csv vaccination_data.json "  select    max(t1.date),    t1.state,    t1.cases,    t1.deaths,    t2.Census2019,    t2.Dist_Per_100K  from    t1      join t2 on t1.state = replace(t2.LongName, 'New York State', 'New York')  group by    t1.state  order by    Dist_Per_100K desc" -tmax(t1.date)    state                       cases    deaths    Census2019    Dist_Per_100K--------------  ------------------------  -------  --------  ------------  ---------------2021-06-18      District of Columbia        49243      1141        705749           1492482021-06-18      Vermont                     24360       256        623989           1462572021-06-18      Rhode Island               152383      2724       1059361           1412912021-06-18      Massachusetts              709263     17960       6892503           1396922021-06-18      Maryland                   461852      9703       6045680           1381932021-06-18      Maine                       68753       854       1344212           1368942021-06-18      Hawaii                      35903       507       1415872           136024...

I’m using automatically created numeric aliases t1 and t2 for the files here, but I can also use their full table names "us-states" (quotes needed due to the hyphen) and vaccination_data instead.

The replace() operation there is needed because the vaccination_data.json file calls New York “New York State” while the us-states.csv file just calls it “New York”.

The max(t1.date) and group by t1.state is a useful SQLite trick: if you perform a group by and then ask for the max() of a value, the other columns returned from that table will be the columns for the row that contains that maximum value.

This demo is a bit of a stretch—once I reach this level of complexity I’m more likely to load the files into a SQLite database file on disk and open them up in Datasette—but it’s a fun example of a more complex join in action.

Also in sqlite-utils 3.10

The sqlite-utils memory command has another new trick up its sleeve: it automatically detects which columns in a CSV or TSV file contain integer or float values and creates the corresponding in-memory SQLite table with the correct types. This ensures max() and sum() and order by work in a predictable manner, without accidentally sorting 1 as higher than 11.

I didn’t want to break backwards compatibility for existing users of the sqlite-utils insert command so I’ve added type detection there as a new option, --detect-types or -d for short:

$ sqlite-utils insert my.db us_states us-states.csv --csv -d  [####################################] 139% $ sqlite-utils схема my.db СОЗДАТЬ ТАБЛИЦУ "us_states" ([date] ТЕКСТ, [state] ТЕКСТ, [fips] ЦЕЛОЕ , [cases] INTEGER, [deaths] INTEGER);   

В журнале изменений есть еще .

Релизы на этой неделе

Leave a comment

Your email address will not be published. Required fields are marked *