-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathREADME.html
342 lines (341 loc) · 12 KB
/
README.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
<div align="center">
<div>
<img width="368" alt="image" src="https://user-images.githubusercontent.com/14998939/186230936-977af168-9879-4a44-ac29-5a0bbd4abd11.png">
</div>
<h3>
<a href="https://piqel.pages.dev">Document(WIP)</a>
</h3>
<strong>
A command line tool for data processing with SQL queries. json, yaml, toml to your favorite format.
</strong>
<div>
<img width="600" alt="image" src="https://user-images.githubusercontent.com/14998939/186355873-036d7e63-3326-4e24-a105-9f8486d03ee6.gif">
</div>
</div>
<h2 id="family" tabindex="-1">Family</h2>
<table>
<thead>
<tr>
<th></th>
<th>language</th>
<th>source, docs</th>
<th>package</th>
</tr>
</thead>
<tbody>
<tr>
<td>pq</td>
<td>CLI (brew, scoop)</td>
<td><a href="https://github.com/fuyutarow/piqel/blob/alpha/src/bin/pq.rs">source</a>, <a href="https://piqel.vercel.app/docs/pq">doc</a></td>
<td></td>
</tr>
<tr>
<td>piqel</td>
<td>Rust (cargo)</td>
<td><a href="https://github.com/fuyutarow/piqel">source</a>, <a href="https://piqel.vercel.app/docs/pq">doc</a></td>
<td><a href="https://crates.io/crates/piqel">https://crates.io/crates/piqel</a></td>
</tr>
<tr>
<td>piqel-js</td>
<td>JavaScript (npm)</td>
<td><a href="https://github.com/fuyutarow/piqel/tree/alpha/piqel-js">source</a>, <a href="https://piqel.vercel.app/docs/piqel-js">doc</a></td>
<td><a href="https://www.npmjs.com/package/piqel">https://www.npmjs.com/package/piqel</a></td>
</tr>
<tr>
<td>piqel-py</td>
<td>Python (pip)</td>
<td><a href="https://github.com/fuyutarow/piqel/tree/alpha/piqel-py">source</a>, <a href="https://piqel.vercel.app/docs/piqel-py">doc</a></td>
<td><a href="https://pypi.org/project/piqel">https://pypi.org/project/piqel</a></td>
</tr>
</tbody>
</table>
<h2 id="table-of-contants" tabindex="-1">Table of Contants</h2>
<ul>
<li><a href="#Features">Features</a></li>
<li><a href="#Motivation">Motivation</a></li>
<li><a href="#Usage">Usage</a>
<ul>
<li><a href="#pretty-print">pretty print</a></li>
<li><a href="#convert-file-format">convert file format</a></li>
<li><a href="#calculate-BMI">calculate BMI</a></li>
</ul>
</li>
<li><a href="#Installation">Installation</a></li>
<li><a href="#Test">Test</a></li>
<li><a href="#LICENCE">LICNECE</a></li>
</ul>
<h2 id="features" tabindex="-1">Features</h2>
<ul>
<li>SQL-like query … You can use SELECT, WHERE, ORDER BY, LIMIT clauses to select and transform data.</li>
<li>Supported data format … CSV, JSON, YAML, TOML. Table data as used in RDB and structured data such as JSON cab be accessed by SQL-like query.</li>
<li>Wide range of uses … This can be used as a CLI tool (pq), as a PartiqQL server with piqel(rust) or piqel-js, or data analysis with piqel-py.</li>
</ul>
<h2 id="motivation" tabindex="-1">Motivation</h2>
<p>What’s <a href="https://partiql.org/">PartiQL</a>?</p>
<h2 id="usage" tabindex="-1">Usage</h2>
<h3 id="pretty-print" tabindex="-1">pretty print</h3>
<table>
<thead>
<tr>
<th>option</th>
<th>description</th>
</tr>
</thead>
<tbody>
<tr>
<td>-c, --compact</td>
<td>compact instead of pretty-printed output, only when outputting in JSON</td>
</tr>
<tr>
<td>-S, --sort-keys</td>
<td>sort keys of objects on output. it on works when --to option is json, currently</td>
</tr>
</tbody>
</table>
<pre class="code-block"><code class="language-sh:$ sh:$">curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | pq
</code></pre>
<h3 id="convert-file-format" tabindex="-1">convert file format</h3>
<table>
<thead>
<tr>
<th>option</th>
<th>description</th>
</tr>
</thead>
<tbody>
<tr>
<td>-f, --from <from></from></td>
<td>target config file [possible values: csv, json, toml, yaml, xml]</td>
</tr>
<tr>
<td>-t, --to <to></to></td>
<td>target config file [possible values: csv, json, toml, yaml, xml]</td>
</tr>
</tbody>
</table>
<p>use <code>-t</code> option c to convert Json, Yaml, Toml, and XML to each other.</p>
<pre class="code-block"><code class="language-sh:$ sh:$">cat pokemon.json | pq -t yaml
</code></pre>
<pre class="code-block"><code class="language-sh:$ sh:$">cat pokemon.json | pq -t yaml | pq -t toml
</code></pre>
<p>Comparison with existing command yj<sup class="footnote-ref"><a href="#fn1" id="fnref1">[1]</a></sup></p>
<table>
<thead>
<tr>
<th>format</th>
<th>pq</th>
<th>yj</th>
</tr>
</thead>
<tbody>
<tr>
<td>JSON</td>
<td>✅</td>
<td>✅</td>
</tr>
<tr>
<td>TOML</td>
<td>✅</td>
<td>⚠️*1</td>
</tr>
<tr>
<td>YAML</td>
<td>✅</td>
<td>✅</td>
</tr>
<tr>
<td>XML</td>
<td>✅</td>
<td>✅</td>
</tr>
<tr>
<td>CSV</td>
<td>✅</td>
<td>❌</td>
</tr>
</tbody>
</table>
<p>*1 TOML of the following format cannot be serialized with <code>yj</code>, but it can be serialized with <code>pq</code> by replacing the fields accordingly.</p>
<pre class="code-block"><code class="language-json:pakcge.json json:pakcge.json">{
"name": "partiql-pokemon",
"dependencies": {
"react": "^16.13.1",
"react-dom": "^16.13.1"
},
"license": "MIT"
}
</code></pre>
<table>
<thead>
<tr>
<th>option</th>
<th>description</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>-q</code></td>
<td>クエリ</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th>query</th>
<th>description</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>SELECT <field_path></code></td>
<td></td>
</tr>
<tr>
<td><code>SELECT <field_path> AS <alias_path></code></td>
<td></td>
</tr>
</tbody>
</table>
<h3 id="calculate-bmi" tabindex="-1">Calculate BMI</h3>
<ol>
<li>Download the file and then calculate BMI in a local.</li>
</ol>
<pre class="code-block"><code class="language-sh:$ sh:$">curl -s https://raw.githubusercontent.com/fuyutarow/pokemon.json/master/en/pokemon.json | pq -q "SELECT name, weight/height/height AS bmi ORDER BY bmi DESC LIMIT 20"
</code></pre>
<ol start="2">
<li>In a terminal, send a query to the server to calculate BMI in a remote.</li>
</ol>
<pre class="code-block"><code class="language-sh:$ sh:$">curl https://partiql-pokemon.vercel.app/api/pokemon/ja -G --data-urlencode "q= SELECT name, weight/height/height AS bmi ORDER BY bmi DESC LIMIT 20"
</code></pre>
<ol start="3">
<li>In a web browser, send a query to the server to calculate BMI in a remote.</li>
</ol>
<a href="https://partiql-pokemon.vercel.app/api/pokemon/ja?q=%20SELECT%20name,%20weight/height/height%20AS%20%20bmi%20ORDER%20BY%20bmi%20DESC%20LIMIT%2020">
partiql-pokemon.vercel.app/api/pokemon/en?q= SELECT name, weight/height/height AS bmi ORDER BY bmi DESC LIMIT 20
</a>
<h2 id="installation" tabindex="-1">Installation</h2>
<pre class="code-block"><code class="language-sh:$ sh:$">brew install fuyutarow/tap/pq
pq -h
</code></pre>
<pre class="code-block"><code class="language-sh:$ sh:$">scoop install pq
pq -h
</code></pre>
<h3 id="convert-data" tabindex="-1">Convert data</h3>
<pre class="code-block"><code class="language-">env | jo | pq "SELECT NAME AS name, USER AS user"
</code></pre>
<p><code>ip</code> command is only available in Linux and WSL, not in Mac.</p>
<pre class="code-block"><code class="language-">ip -j -p | pq "$(cat<<EOS
SELECT
address,
info.family AS inet,
info.local
FROM addr_info AS info
WHERE inet LIKE 'inet%'
EOS
)"
</code></pre>
<ul>
<li>[x] SELECT</li>
<li>[x] FROM</li>
<li>[x] LEFT JOIN</li>
<li>[x] WHERE</li>
<li>[x] LIKE</li>
<li>[x] ORDER BY</li>
<li>[x] LIMIT</li>
</ul>
<p><a href="https://github.com/fuyutarow/piqel/tree/alpha/tests-make">more examples</a></p>
<h2 id="test" tabindex="-1">Test</h2>
<p>Use <a href="https://github.com/fuyutarow/tests-make">tests-make</a> to test CLI <code>pq</code>.</p>
<pre class="code-block"><code class="language-sh:$ sh:$">brew install fuyutarow/tap/tests-make
tests-make tests-make/index.toml
</code></pre>
<p>or</p>
<pre class="code-block"><code class="language-sh:$ sh:$">makers test:pq
</code></pre>
<table>
<thead>
<tr>
<th>content</th>
<th>test</th>
<th>command</th>
</tr>
</thead>
<tbody>
<tr>
<td><a href="https://github.com/fuyutarow/piqel/blob/alpha/src/bin/pq.rs">pq</a></td>
<td><a href="https://github.com/fuyutarow/piqel/tree/alpha/tests-make">test</a></td>
<td><code>makers test:pq</code></td>
</tr>
<tr>
<td><a href="https://github.com/fuyutarow/piqel">piqel</a></td>
<td><a href="https://github.com/fuyutarow/piqel/tree/alpha/tests">test</a></td>
<td><code>makers test:lib</code></td>
</tr>
<tr>
<td><a href="https://github.com/fuyutarow/piqel/tree/alpha/piqel-js">piqel-js</a></td>
<td><a href="https://github.com/fuyutarow/piqel/tree/alpha/piqel-js/tests">test</a></td>
<td><code>makers test:js</code></td>
</tr>
<tr>
<td><a href="https://github.com/fuyutarow/piqel/tree/alpha/piqel-py">piqel-py</a></td>
<td><a href="https://github.com/fuyutarow/piqel/tree/alpha/piqel-py/tests">test</a></td>
<td><code>makres test:py</code></td>
</tr>
<tr>
<td>all</td>
<td></td>
<td><code>makers test</code></td>
</tr>
</tbody>
</table>
<h2 id="code-coverage" tabindex="-1">code coverage</h2>
<pre class="code-block"><code class="language-sh: sh:">cargo install cargo-kcov
cargo kcov
</code></pre>
<p>or</p>
<pre class="code-block"><code class="language-sh:$ sh:$">makers cov
</code></pre>
<h3 id="preparation" tabindex="-1">Preparation</h3>
<pre class="code-block"><code class="language-">makers install-dev
</code></pre>
<h3 id="build" tabindex="-1">build</h3>
<pre class="code-block"><code class="language-">makers build
makers build:pq ;: for pq commnad
</code></pre>
<h2 id="licence" tabindex="-1">LICENCE</h2>
<h2 id="appendix" tabindex="-1">Appendix</h2>
<h3 id="comparison-of-tools-that-can-extract-fields" tabindex="-1">Comparison of tools that can extract fields</h3>
<p>jq<sup class="footnote-ref"><a href="#fn2" id="fnref2">[2]</a></sup> approach</p>
<pre class="code-block"><code class="language-sh:$ sh:$">curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | jq ".[].commit.author"
</code></pre>
<p>gron<sup class="footnote-ref"><a href="#fn3" id="fnref3">[3]</a></sup> approach</p>
<pre class="code-block"><code class="language-sh:$ sh:$">curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | gron | grep "commit.author" | gron -u
</code></pre>
<p>nusehll<sup class="footnote-ref"><a href="#fn4" id="fnref4">[4]</a></sup> approach</p>
<pre class="code-block"><code class="language-sh:nu$ sh:nu$">curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | from json | get commit.author | to json
</code></pre>
<p>pq<sup class="footnote-ref"><a href="#fn5" id="fnref5">[5]</a></sup> approach</p>
<pre class="code-block"><code class="language-sh:$ sh:$">curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | pq -q "SELECT commit.author"
</code></pre>
<h3 id="utils" tabindex="-1">utils</h3>
<ul>
<li>makers<sup class="footnote-ref"><a href="#fn6" id="fnref6">[6]</a></sup></li>
</ul>
<hr class="footnotes-sep">
<section class="footnotes">
<ol class="footnotes-list">
<li id="fn1" class="footnote-item"><p><a href="https://github.com/sclevine/yj">https://github.com/sclevine/yj</a> <a href="#fnref1" class="footnote-backref">↩︎</a></p>
</li>
<li id="fn2" class="footnote-item"><p><a href="https://github.com/stedolan/jq">https://github.com/stedolan/jq</a> <a href="#fnref2" class="footnote-backref">↩︎</a></p>
</li>
<li id="fn3" class="footnote-item"><p><a href="https://github.com/tomnomnom/gron">https://github.com/tomnomnom/gron</a> <a href="#fnref3" class="footnote-backref">↩︎</a></p>
</li>
<li id="fn4" class="footnote-item"><p><a href="https://github.com/nushell/nushell">https://github.com/nushell/nushell</a> <a href="#fnref4" class="footnote-backref">↩︎</a></p>
</li>
<li id="fn5" class="footnote-item"><p><a href="https://github.com/fuyutarow/piqel">https://github.com/fuyutarow/piqel</a> <a href="#fnref5" class="footnote-backref">↩︎</a></p>
</li>
<li id="fn6" class="footnote-item"><p><a href="https://github.com/sagiegurari/cargo-make">https://github.com/sagiegurari/cargo-make</a> … Run <code>cargo install cargo-make</code> to use <code>makers</code> commnad. <a href="#fnref6" class="footnote-backref">↩︎</a></p>
</li>
</ol>
</section>