Document(WIP)

A command line tool for data processing with SQL queries. json, yaml, toml to your favorite format.

Family

	language	source, docs	package
pq	CLI (brew, scoop)	source, doc
piqel	Rust (cargo)	source, doc	https://crates.io/crates/piqel
piqel-js	JavaScript (npm)	source, doc	https://www.npmjs.com/package/piqel
piqel-py	Python (pip)	source, doc	https://pypi.org/project/piqel

Table of Contants

• Features
• Motivation
• Usage
  • pretty print
  • convert file format
  • calculate BMI
• Installation
• Test
• LICNECE

Features

• SQL-like query … You can use SELECT, WHERE, ORDER BY, LIMIT clauses to select and transform data.
• 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.
• 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.

Motivation

What’s PartiQL?

Usage

pretty print

option	description
-c, --compact	compact instead of pretty-printed output, only when outputting in JSON
-S, --sort-keys	sort keys of objects on output. it on works when --to option is json, currently

curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | pq

convert file format

option	description
-f, --from	target config file [possible values: csv, json, toml, yaml, xml]
-t, --to	target config file [possible values: csv, json, toml, yaml, xml]

use -t option c to convert Json, Yaml, Toml, and XML to each other.

cat pokemon.json | pq -t yaml

cat pokemon.json | pq -t yaml | pq -t toml

Comparison with existing command yj^[1]

format	pq	yj
JSON	✅	✅
TOML	✅	⚠️*1
YAML	✅	✅
XML	✅	✅
CSV	✅	❌

*1 TOML of the following format cannot be serialized with yj, but it can be serialized with pq by replacing the fields accordingly.

{
  "name": "partiql-pokemon",
  "dependencies": {
    "react": "^16.13.1",
    "react-dom": "^16.13.1"
  },
  "license": "MIT"
}

option	description
-q	クエリ

query	description
SELECT <field_path>
SELECT <field_path> AS <alias_path>

Calculate BMI

1. Download the file and then calculate BMI in a local.

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"

2. In a terminal, send a query to the server to calculate BMI in a remote.

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"

3. In a web browser, send a query to the server to calculate BMI in a remote.

partiql-pokemon.vercel.app/api/pokemon/en?q= SELECT name, weight/height/height AS bmi ORDER BY bmi DESC LIMIT 20

Installation

brew install fuyutarow/tap/pq
pq -h

scoop install pq
pq -h

Convert data

env | jo | pq "SELECT NAME AS name, USER AS user"

ip command is only available in Linux and WSL, not in Mac.

ip -j -p | pq "$(cat<<EOS
SELECT
  address,
  info.family AS inet,
  info.local
FROM addr_info AS info
WHERE inet LIKE 'inet%'
EOS
)"

• SELECT
• FROM
• LEFT JOIN
• WHERE
• LIKE
• ORDER BY
• LIMIT

more examples

Test

Use tests-make to test CLI pq.

brew install fuyutarow/tap/tests-make
tests-make tests-make/index.toml

or

makers test:pq

content	test	command
pq	test	makers test:pq
piqel	test	makers test:lib
piqel-js	test	makers test:js
piqel-py	test	makres test:py
all		makers test

code coverage

cargo install cargo-kcov
cargo kcov

or

makers cov

Preparation

makers install-dev

build

makers build
makers build:pq ;: for pq commnad

LICENCE

Appendix

Comparison of tools that can extract fields

jq^[2] approach

curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | jq  ".[].commit.author"

gron^[3] approach

curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | gron | grep "commit.author" | gron -u

nusehll^[4] approach

curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | from json | get commit.author | to json

pq^[5] approach

curl -s "https://api.github.com/repos/fuyutarow/piqel/commits?per_page=1" | pq -q "SELECT commit.author"

utils

• makers^[6]

---

1. https://github.com/sclevine/yj ↩︎

2. https://github.com/stedolan/jq ↩︎

3. https://github.com/tomnomnom/gron ↩︎

4. https://github.com/nushell/nushell ↩︎

5. https://github.com/fuyutarow/piqel ↩︎

6. https://github.com/sagiegurari/cargo-make … Run cargo install cargo-make to use makers commnad. ↩︎