GitHub - noborus/trdsql: CLI tool that can execute SQL queries on CSV, LTSV, JSON, YAML and TBLN. Can output to various formats.

go get -d github.com/noborus/trdsql
cd $GOPATH/src/github.com/noborus/trdsql
make
make install

brew install noborus/tap/trdsql

sudo port selfupdate
sudo port install trdsql

docker pull noborus/trdsql

docker run --rm -it -v $(pwd):/tmp trdsql [options and commands]

trdsql -o[output format] -t [input filename]

$ echo "1,,v" | trdsql -inull "" -onull "(NULL)" "SELECT * FROM -"
1,(NULL),v

$ echo '[1,null,""]' | trdsql -inull "" -ojson -ijson "SELECT * FROM -"
[
  {
    "c1": "1"
  },
  {
    "c1": null
  },
  {
    "c1": null
  }
]

$ trdsql "UPDATE SET c2='banana' WHERE c3='1';SELECT * FROM test.csv"
1,Orange
2,Melon
3,banana

$ trdsql -oh "SELECT c1,c2 FROM test.csv;SELECT c2,c1 FROM test.csv"
c1,c2
1,Orange
2,Melon
3,Apple
c2,c1
Orange,1
Melon,2
Apple,3

trdsql "SELECT * FROM test.csv"

cat test.csv | trdsql "SELECT * FROM -"

cat test.csv | trdsql "SELECT * FROM stdin"

$ trdsql -ih "SELECT * FROM tt*.csv"
1,test1
2,test2
3,test3

Note

It is not possible to mix different formats (ex: CSV and LTSV).

trdsql "SELECT * FROM testdata/test.csv.gz"

trdsql "SELECT * FROM testdata/test.csv.zst"

trdsql "SELECT * FROM testdata/test.csv*"

trdsql -out result.csv "SELECT * FROM testdata/test.csv ORDER BY c1"

trdsql -oz gz "SELECT * FROM testdata/test.csv ORDER BY c1" > result.csv.gz

trdsql -out result.ltsv.zst "SELECT * FROM testdata/test.csv"

$ trdsql -ir 3 -iltsv "SELECT * FROM test_indefinite.ltsv"
1,Orange,50,,
2,Melon,500,ibaraki,
3,Apple,100,aomori,red

trdsql -id "\t" "SELECT * FROM test-tab.csv"

$ trdsql -od "\t" "SELECT * FROM test.csv"
1	Orange
2	Melon
3	Apple

id:1	name:Orange	price:50
id:2	name:Melon	price:500
id:3	name:Apple	price:100

trdsql -iltsv "SELECT * FROM sample.ltsv"

1,Orange,50
2,Melon,500
3,Apple,100

Note

Only the columns in the first row are targeted.

$ trdsql -iltsv -oltsv "SELECT * FROM sample.ltsv"
id:1	name:Orange	price:50
id:2	name:Melon	price:500
id:3	name:Apple	price:100

[
  {
    "id": "1",
    "name": "Orange",
    "price": "50"
  },
  {
    "id": "2",
    "name": "Melon",
    "price": "500"
  },
  {
    "id": "3",
    "name": "Apple",
    "price": "100"
  }
]

$ trdsql -ijson "SELECT * FROM sample.json"
1,Orange,50
2,Melon,500
3,Apple,100

[
    {
      "id": 1,
      "name": "Drolet",
      "attribute": { "country": "Maldives", "color": "burlywood" }
    },
    {
      "id": 2,
      "name": "Shelly",
      "attribute": { "country": "Yemen", "color": "plum" }
    },
    {
      "id": 3,
      "name": "Tuck",
      "attribute": { "country": "Mayotte", "color": "antiquewhite" }
    }
]

$ trdsql -ijson "SELECT * FROM sample2.json"
1,Drolet,"{""color"":""burlywood"",""country"":""Maldives""}"
2,Shelly,"{""color"":""plum"",""country"":""Yemen""}"
3,Tuck,"{""color"":""antiquewhite"",""country"":""Mayotte""}"

$ trdsql -ijson "SELECT id, name, JSON_EXTRACT(attribute,'$.country'), JSON_EXTRACT(attribute,'$.color') FROM sample2.json"
1,Drolet,Maldives,burlywood
2,Shelly,Yemen,plum
3,Tuck,Mayotte,antiquewhite

{
	"menu": {
		"id": "file",
		"value": "File",
		"popup": {
			"menuitem": [
				{
					"value": "New",
					"onclick": "CreateDoc()"
				},
				{
					"value": "Open",
					"onclick": "OpenDoc()"
				},
				{
					"value": "Save",
					"onclick": "SaveDoc()"
				}
			]
		}
	}
}

trdsql -oat 'SELECT value, onclick FROM menu.json::".menu.popup.menuitem"'

$ trdsql -oat -ijq ".menu.popup.menuitem" "SELECT * FROM menu.json"
+-------+-------------+
| value |   onclick   |
+-------+-------------+
| New   | CreateDoc() |
| Open  | OpenDoc()   |
| Save  | SaveDoc()   |
+-------+-------------+

$ echo '{"foo": 128}' | trdsql -ijson "SELECT * FROM -::'.foo'"
128
$ echo '{"a": {"b": 42}}' | trdsql -ijson "SELECT * FROM -::'.a.b'"
42
$ echo '{"id": "sample", "10": {"b": 42}}' | trdsql -ijson "SELECT * FROM -::'{(.id): .[\"10\"].b}'"
42
$ echo '[{"id":1},{"id":2},{"id":3}]' | trdsql -ijson "SELECT * FROM -::'.[] | .id'"
1
2
3
$ echo '{"a":1,"b":2}' | trdsql -ijson "SELECT * FROM -::'.a += 1 | .b *= 2'"
4,2
$ echo '{"a":1} [2] 3' | trdsql -ijson "SELECT * FROM -::'. as {\$a} ?// [\$a] ?// \$a | \$a'"
1
2
3

{"id": "1","name": "Orange","price": "50"}
{"id": "2","name": "Melon","price": "500"}
{"id": "3","name": "Apple","price": "100"}

$ trdsql -ojson "SELECT * FROM test.csv"
[
  {
    "c1": "1",
    "c2": "Orange"
  },
  {
    "c1": "2",
    "c2": "Melon"
  },
  {
    "c1": "3",
    "c2": "Apple"
  }
]

$ trdsql -ojsonl "SELECT * FROM test.csv"
{"c1":"1","c2":"Orange"}
{"c1":"2","c2":"Melon"}
{"c1":"3","c2":"Apple"}

- id: 1
  name: Orange
  price: 50
- id: 2
  name: Melon
  price: 500
- id: 3
  name: Apple
  price: 100

$ trdsql -iyaml -ocsv "SELECT * FROM sample.yaml"
1,Orange,50
2,Melon,500
3,Apple,100

a: true
b:
  c: 2
  d: [3, 4, 5]
  e:
    - name: fred
      value: 3
    - name: sam
      value: 4%

$ trdsql -ojson "SELECT * FROM sample2.yaml"
[
  {
    "a": "true",
    "b": {
      "c": 2,
      "d": [
        3,
        4,
        5
      ],
      "e": [
        {
          "name": "fred",
          "value": 3
        },
        {
          "name": "sam",
          "value": "4%"
        }
      ]
    }
  }
]

$ trdsql  -ojson "SELECT * FROM sample2.yaml::.b.e"
[
  {
    "name": "fred",
    "value": "3"
  },
  {
    "name": "sam",
    "value": "4%"
  }
]

$ trdsql -ojson "SELECT * FROM sample2.yaml::.b.e"
- name: fred
  value: 3
- name: sam
  value: 4%

; name: | id | name |
; type: | int | text |
| 1 | Bob |
| 2 | Alice |

$ trdsql -itbln "SELECT * FROM sample.tbln"
1,Bob
2,Alice

$ trdsql -otbln "SELECT c1::int as id, c2::text as name FROM test.csv"
; created_at: 2019-03-22T13:20:31+09:00
; name: | id | name |
; type: | int | text |
| 1 | Orange |
| 2 | Melon |
| 3 | Apple |

$ ps | trdsql -oh -iwidth "SELECT * FROM -"
PID,TTY,TIME,CMD
302965,pts/3,00:00:12,zsh
733211,pts/3,00:00:00,ps
733212,pts/3,00:00:00,tee
733213,pts/3,00:00:00,guesswidth

$ cat test.text
a

b

c
$ trdsql -itext "SELECT * FROM test.text"
a

b

c

$ trdsql -inum "SELECT * FROM test.text"
1,a
2,
3,b
4,
5,c

$ trdsql -oraw "SELECT row_to_json(t,TRUE) FROM test.csv AS t"
{"c1":"1",
 "c2":"Orange"}
{"c1":"2",
 "c2":"Melon"}
{"c1":"3",
 "c2":"Apple"}

$ trdsql -oraw -od "\t|\t" -db pdb "SELECT * FROM test.csv"
1	|	Orange
2	|	Melon
3	|	Apple

$ trdsql -oat "SELECT * FROM test.csv"
+----+--------+
| C1 |   C2   |
+----+--------+
|  1 | Orange |
|  2 | Melon  |
|  3 | Apple  |
+----+--------+

$ trdsql -omd "SELECT * FROM test.csv"
| C1 |   C2   |
|----|--------|
|  1 | Orange |
|  2 | Melon  |
|  3 | Apple  |

$ trdsql -ovf "SELECT * FROM test.csv"
---[ 1]--------------------------------------------------------
  c1 | 1
  c2 | Orange
---[ 2]--------------------------------------------------------
  c1 | 2
  c2 | Melon
---[ 3]--------------------------------------------------------
  c1 | 3
  c2 | Apple

$ trdsql "SELECT count(*) FROM test.csv"
3

$ trdsql "SELECT c2,c1 FROM test.csv"
Orange,1
Melon,2
Apple,3

Note

the available functions and their syntax depend on the driver you have chosen (mysql or postgres or sqlite). The default one is sqlite.

1,2017-7-10
2,2017-7-10
2,2017-7-11

$ trdsql "SELECT u.c1,u.c2,h.c2 FROM user.csv as u LEFT JOIN hist.csv as h ON(u.c1=h.c1)"
1,userA,2017-7-10
2,userB,2017-7-10
2,userB,2017-7-11

trdsql -driver postgres -dsn "dbname=test" "SELECT count(*) FROM test.csv "

$ trdsql -driver postgres -dsn "dbname=test" "SELECT row_number() OVER (ORDER BY c2),c1,c2 FROM test.csv"
1,3,Apple
2,2,Melon
3,1,Orange

$ trdsql -driver postgres -dsn "dbname=test" "SELECT generate_series(1,3);"
1
2
3

$ psql test -c "SELECT * FROM colors"
 id |  name  
----+--------
  1 | orange
  2 | green
  3 | red
(3 rows)

$ trdsql -driver postgres -dsn "dbname=test" "SELECT t.c1,t.c2,c.name FROM test.csv AS t LEFT JOIN colors AS c ON (t.c1::int = c.id)"
1,Orange,orange
2,Melon,green
3,Apple,red

trdsql -driver postgres -dns "dbname=test" "CREATE TABLE fruits (id, name) AS SELECT c1::int, c2 FROM fruits.csv "

$ psql -c "SELECT * FROM fruits;"
 id |  name  
----+--------
  1 | Orange
  2 | Melon
  3 | Apple
(3 rows)

$ trdsql -driver mysql -dsn "user:password@/test" "SELECT GROUP_CONCAT(c2 ORDER BY c2 DESC) FROM testdata/test.csv"
"g,d,a"

$ trdsql -driver mysql -dsn "user:password@/test" "SELECT c1, SHA2(c2,224) FROM test.csv"
1,a063876767f00792bac16d0dac57457fc88863709361a1bb33f13dfb
2,2e7906d37e9523efeefb6fd2bc3be6b3f2991678427bedc296f9ddb6
3,d0b8d1d417a45c7c58202f55cbb617865f1ef72c606f9bce54322802

$ trdsql -a testdata/test.ltsv
The table name is testdata/header.csv.
The file type is CSV.

Data types:
+-------------+------+
| column name | type |
+-------------+------+
| id          | text |
| \`name\`    | text |
+-------------+------+

Data samples:
+----+----------+
| id | \`name\` |
+----+----------+
|  1 | Orange   |
+----+----------+

Examples:
trdsql -db sdb -ih "SELECT id, \`name\` FROM testdata/header.csv"
trdsql -db sdb -ih "SELECT id, \`name\` FROM testdata/header.csv WHERE id = '1'"
trdsql -db sdb -ih "SELECT id, count(id) FROM testdata/header.csv GROUP BY id"
trdsql -db sdb -ih "SELECT id, \`name\` FROM testdata/header.csv ORDER BY id LIMIT 10"

trdsql -ih -a testdata/header.csv

$ trdsql -ih -A testdata/header.csv
trdsql -ih "SELECT id, \`name\` FROM testdata/header.csv"
trdsql -ih "SELECT id, \`name\` FROM testdata/header.csv WHERE id = '1'"
trdsql -ih "SELECT id, count(id) FROM testdata/header.csv GROUP BY id"
trdsql -ih "SELECT id, \`name\` FROM testdata/header.csv ORDER BY id LIMIT 10"

${HOME}/.config/trdsql/config.json

C:\Users\{"User"}\AppData\Roaming\trdsql\config.json

trdsql -config config.json "SELECT * FROM test.csv"

{
  "db": "pdb",
  "database": {
    "sdb": {
      "driver": "sqlite3",
      "dsn": ""
    },
    "pdb": {
      "driver": "postgres",
      "dsn": "user=test dbname=test"
    },
    "mdb": {
      "driver": "mysql",
      "dsn": "user:password@/dbname"
    }
  }
}

$ trdsql -debug -db pdb "SELECT * FROM test.csv"
2017/07/18 02:27:47 driver: postgres, dsn: user=test dbname=test
2017/07/18 02:27:47 CREATE TEMPORARY TABLE "test.csv" ( c1 text,c2 text );
2017/07/18 02:27:47 INSERT INTO "test.csv" (c1,c2) VALUES ($1,$2);
2017/07/18 02:27:47 SELECT * FROM "test.csv"
1,Orange
2,Melon
3,Apple

package main

import (
        "log"

        "github.com/noborus/trdsql"
)

func main() {
        trd := trdsql.NewTRDSQL(
                trdsql.NewImporter(trdsql.InDelimiter(":")),
                trdsql.NewExporter(trdsql.NewWriter()),
        )
        if err := trd.Exec("SELECT c1 FROM /etc/passwd"); err != nil {
                log.Fatal(err)
        }
}