问如何将JSON文件转换为SQLite数据库

EN

我发现最简单的方法是使用和CSV作为中间格式。

编辑:正如所指出的(感谢JSON )，最初的问题确实显示了换行符分隔的@Leo对象，每个对象本身都符合rfc4627，但并不是所有对象都符合这种格式。jq可以通过使用jq '.[]' <input.json >preprocessed.json对文件进行预处理，以大致相同的方式处理单个JSON对象数组。如果您碰巧正在处理JSON文本序列(rfc7464)，幸运的是，jq也通过--seq参数为您提供了支持。

编辑2:换行符分隔的JSON和JSON文本序列都有一个重要的优点；它们将内存需求降低到O(1)，这意味着您的总内存需求仅取决于最长的输入行，而将整个输入放在一个数组中需要解析器能够处理后期错误(即，在前100k个元素之后出现语法错误)，这通常不是我所知的情况，或者它必须解析整个文件两次(首先验证语法，然后解析，过程中丢弃以前的元素，就像jq --stream的情况一样)，这在我的知识中也很少发生。或者它会尝试一次解析整个输入并在一个步骤中返回结果(想象一下收到一个Python字典，它包含了整个50G输入数据加上开销)，这通常是内存支持的，因此增加了大约总数据大小的内存占用。

编辑3:如果你碰到任何障碍物，试着使用keys_unsorted键而不是键。我自己没有测试过(我假设我的列已经排序好了)，但是@Kyle Barron报告了that this was needed。

获取CSV

首先将你的数据写入一个文件。我将在这里假设data.json。

然后使用jq构造报头

% head -1 data.json | jq -r 'keys | @csv'
"uri","user_agent"

head -1是因为我们只需要一行。jq的-r使输出为普通字符串，而不是包装CSV的JSON字符串。然后，我们调用内部函数keys，以数组的形式获取输入的键。我们将其发送到@csv格式化程序，该格式化程序以CSV格式输出带有头部的单个字符串。

然后我们需要构造数据。

% jq -r '[.[]] | @csv' < data.json
"/","example1"
"/foobar","example1"
"/","example2"
"/foobar","example3"

现在，我们获取整个输入并使用.[]解构关联数组( […] )，然后将其放回一个简单的数组map中。这基本上将我们的字典转换为键的数组。发送到@csv格式化程序后，我们再次获得一些CSV。

把所有这些放在一起，我们得到一个简单的一行代码，形式是：

% (head -1 data.json | jq -r 'keys | @csv' && jq -r '[.[]] | @csv' < data.json) > data.csv

如果您需要动态转换数据，即不需要文件，请尝试以下操作：

% cat data.json | (read -r first && jq -r '(keys | @csv),( [.[]] | @csv)' <<<"${first}" && jq -r '[.[]] | @csv')

将其加载到SQLite中

打开SQLite数据库：

sqlite3 somedb.sqlite

现在，在交互式外壳中执行以下操作(假设您将CSV写入data.csv，并希望将其放在一个名为my_table的表中)：

.mode csv
.import data.csv my_table

现在关闭shell并再次打开它，以获得一个干净的环境。现在，您可以轻松地从数据库执行SELECT操作，并随心所欲地执行操作。

把所有这些放在一起

在那里有一个asciinema录制：

​

​

不使用CSV或第三方工具的一种方法是将SQLite的JSON1 extension与sqlite3 CLI工具中提供的readfile extension结合使用。

如果输入文件是格式良好的JSON文件，例如以数组形式给出的示例：

[
{"uri":"/","user_agent":"example1"},
{"uri":"/foobar","user_agent":"example1"},
{"uri":"/","user_agent":"example2"},
{"uri":"/foobar","user_agent":"example3"}
]

然后，可以将其读取到相应的my_table表中，如下所示。使用sqlite3命令行界面打开SQLite数据库文件my_db.db：

sqlite3 my_db.db

然后使用以下命令创建my_table：

CREATE TABLE my_table(uri TEXT, user_agent TEXT);

最后，可以使用CLI命令将my_data.json中的JSON数据插入到表中：

INSERT INTO my_table SELECT 
  json_extract(value, '$.uri'), 
  json_extract(value, '$.user_agent')
FROM json_each(readfile('my_data.json'));

总的来说，这是一种“更直接”的解决方案，它的优点是处理JSON null值比CSV更一致，否则CSV会将它们作为空字符串导入。

如果初始JSON文件是换行符分隔的JSON元素，则可以首先使用jq进行转换，方法是：

jq -s <my_data_raw.json >my_data.json

很可能有一种方法可以使用JSON1在SQLite中直接完成此操作，但考虑到我在导入到SQLite之前已经在使用jq处理数据，因此我并没有追求这一点。

sqlitebiter似乎提供了一个python解决方案：

用于将CSV/Excel/HTML/JSON/LTSV/Markdown/SQLite/TSV/Google-Sheets转换为SQLite数据库文件的命令行工具。http://sqlitebiter.rtfd.io/

文档：http://sqlitebiter.readthedocs.io/en/latest/

项目：https://github.com/thombashi/sqlitebiter

• 上一次更新大约在3个月前
• 上一期在大约1个月前关闭，未打开
• 今天注意到，2018-03-14