trdsql

CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN.

It is a tool like q, textql and others.

The difference from these tools is that the syntax of PostgreSQL or MySQL can be used.

Please refer to godoc and _example for usage as a library.

• 1. INSTALL
  • 1.1. Requirements
  • 1.2. Download
  • 1.3. Homebrew
• 2. Docker
  • 2.1. Docker pull
  • 2.2. image build
  • 2.3. Docker Run
• 3. Usage
  • 3.1. global option
  • 3.2. Input format
    • 3.2.1. Input option
  • 3.3. Output format
    • 3.3.1. Output option
• 4. Example
  • 4.1. STDIN input
  • 4.2. Multiple files
  • 4.3. Compressed files
  • 4.4. Output file
  • 4.5. Output compression
  • 4.6. Guess by output file name
  • 4.7. Columns is not constant
  • 4.8. TSV (Tab Separated Value)
  • 4.9. LTSV (Labeled Tab-separated Values)
  • 4.10. JSON
  • 4.11. JSONL
  • 4.12. TBLN
  • 4.13. Raw output
  • 4.14. ASCII Table & MarkDown output
  • 4.15. Vertical format output
  • 4.16. SQL function
  • 4.17. JOIN
  • 4.18. PostgreSQL
    • 4.18.1. Function
    • 4.18.2. Join table and CSV file is possible
  • 4.19. MySQL
  • 4.20. Analyze
  • 4.21. configuration
• 5. Library
• 6. License

1. INSTALL

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

1.1. Requirements

go 1.11 or higher.

1.2. Download

Download binary from the releases page(Linux/Windows/macOS).

1.3. Homebrew

brew install noborus/tap/trdsql

2. Docker

2.1. Docker pull

Pull the latest image from the Docker hub.

docker pull noborus/trdsql

2.2. image build

Or build it yourself.

docker build -t trdsql .

2.3. Docker Run

Docker run.

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

3. Usage

trdsql [options] SQL

3.1. global option

• -a filename analyze the file and suggest SQL.
• -A filename analyze the file but only suggest SQL.
• -config filename configuration file location.
• -db db name specify db name of the setting.
• -dblist display db list of configure.
• -driver string database driver. [ mysql | postgres | sqlite3 ]
• -dsn string database driver specific data source name.
• -debug debug print.
• -help display usage information.
• -version display version information.
• -q filename read query from the specified file.

3.2. Input format

• -ig guess format from extension. (default)
• -icsv CSV format for input.
• -ijson JSON format for input.
• -iltsv LTSV format for input.
• -itbln TBLN format for input.

3.2.1. Input option

• -ih the first line is interpreted as column names(CSV only).
• -id character field delimiter for input. (default ",")(CSV only)
• -is int skip header row.
• -ir int number of row pre-read for column determination. (default 1)

3.3. Output format

• -ocsv CSV format for output. (default)
• -ojson JSON format for output.
• -ojsonl JSONL(JSON Lines) format for output.
• -oltsv LTSV format for output.
• -oat ASCII Table format for output.
• -omd Markdown format for output.
• -oraw Raw format for output.
• -ovf Vertical format for output.
• -otbln TBLN format for output.

Or, guess the output format by file name.

3.3.1. Output option

• -out filename output file name.
• -out-without-guess output without guessing (when using -out).
• -oh output column name as header.
• -od character field delimiter for output. (default ",")(CSV and RAW only).
• -oq character quote character for output. (default """)(CSV only).
• -oaq enclose all fields in quotes for output(CSV only).
• -ocrlf use CRLF for output. End each output line with '\r\n' instead of '\n'.")(CSV only).
• -onowrap do not wrap long columns(AT and MD only).

4. Example

test.csv file.

1,Orange
2,Melon
3,Apple

Please write a file name like a table name.

trdsql "SELECT * FROM test.csv"

-q filename can execute SQL from file

trdsql -q test.sql

4.1. STDIN input

"-" or "stdin" is received from standard input instead of file name.

ps | trdsql -id " " "SELECT * FROM -"

or

ps | trdsql -id " " "SELECT * FROM stdin"

4.2. Multiple files

Multiple matched files can be executed as one table.

$ 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).

4.3. Compressed files

If the file is compressed with gzip, bz2, zstd, lz4, xz, it will be automatically uncompressed.

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

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

It is possible to mix uncompressed and compressed files using wildcards.

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

4.4. Output file

-out filename option to output the file to a file.

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

4.5. Output compression

-oz compression type to compress and output.

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

4.6. Guess by output file name

The filename of -out filename option determines the output format(csv, ltsv, json, tbln, raw, md, at, vf, jsonl) and compression format(gzip, bz2, zstd,lz4, xz) by guess.

Guess by extension output format + output compression (eg .csv.gz, .ltsv.lz4, .md.zst ...).

The following is an LTSV file compressed in zstd.

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

4.7. Columns is not constant

If the number of columns is not a constant, read and decide multiple rows.

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

4.8. TSV (Tab Separated Value)

-id "\\t" is input from TSV (Tab Separated Value)

1	Orange
2	Melon
3	Apple

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

-od "\\t" is TSV (Tab Separated Value) output.

trdsql -od "\t" "SELECT * FROM test.csv"

1	Orange
2	Melon
3	Apple

4.9. LTSV (Labeled Tab-separated Values)

-iltsv is input from LTSV(Labeled Tab-separated Values).

sample.ltsv

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.

-oltsv is LTSV(Labeled Tab-separated Values) output.

$ 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

4.10. JSON

-ijson is input from JSON.

sample.json

[
  {
    "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

JSON can contain structured types, but trdsql is stored as it is as JSON string.

sample2.json

[
    {
      "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""}"

Please use SQL function.

$ 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

Another json format. JSONL(JSON Lines).

sample2.json

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

-ojson is JSON Output.

trdsql -ojson "SELECT * FROM test.csv"

[
  {
    "c1": "1",
    "c2": "Orange"
  },
  {
    "c1": "2",
    "c2": "Melon"
  },
  {
    "c1": "3",
    "c2": "Apple"
  }
]

4.11. JSONL

To output in JSONL, specify -ojsonl.

trdsql -ojsonl "SELECT * FROM test.csv"

{"c1":"1","c2":"Orange"}
{"c1":"2","c2":"Melon"}
{"c1":"3","c2":"Apple"}

4.12. TBLN

-itbln is input from TBLN.

sample.tbln

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

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

TBLN file reflects extras name and type.

-otbln is TBLN Output.

$ 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 |

TBLN can contain column names and type definitions. Please refer to https://tbln.dev/ for details of TBLN.

4.13. Raw output

-oraw is Raw Output. It is used when "escape processing is unnecessary" in CSV output. (For example, when outputting JSON in the database).

$ 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"}

Multiple delimiter characters can be used for raw.

trdsql -oraw -od "\t|\t" -db pdb "SELECT * FROM test.csv"

1	|	Orange
2	|	Melon
3	|	Apple

4.14. ASCII Table & MarkDown output

-oat is ASCII table output.

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

-omd is Markdown output.

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

The -onowrap option does not wrap long columns in at or md output.

4.15. Vertical format output

-ovf is Vertical format output("column name | value" vertically).

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

4.16. SQL function

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

The default column names are c1, c2,...

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

"- ih" sets the first line to column name

$ ps |trdsql -ih -oh -id " " "SELECT \`PID\`, \`TTY\`, \`TIME\`, \`CMD\` FROM -"
TIME,TTY,PID,CMD
00:00:00,pts/20,3452,ps
00:00:00,pts/20,3453,trdsql
00:00:05,pts/20,15576,zsh

4.17. JOIN

The SQL JOIN can be used.

user.csv

1,userA
2,userB

hist.csv

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

4.18. PostgreSQL

When using PostgreSQL, specify postgres for driver and driver-specific data source name for dsn.

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

4.18.1. Function

The PostgreSQL driver can use the window function.

$ 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

For example, the generate_series function can be used.

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

4.18.2. Join table and CSV file is possible

Test database has a colors table.

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

Join table and CSV file.

$ 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

To create a table from a file, use "CREATE TABLE ... AS SELECT...".

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)

4.19. MySQL

When using MySQL, specify mysql for driver and connection information for dsn.

$ 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

MySQL can join tables and CSV files as well as PostgreSQL.

4.20. Analyze

The -a filename option parses the file and outputs table information and SQL examples.

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"

Other options(-id,-ih,-ir,-is,icsv,iltsv,-ijson,-itbln...) are available.

trdsql -ih -a testdata/header.csv

Similarly, with -A filename option, only Examples (SQL) is output.

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"

4.21. configuration

You can specify driver and dsn in the configuration file.

Unix like.

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

Windows (ex).

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

Or use the -config file option.

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

sample: config.json

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

The default database is an entry of "db".

If you put the setting in you can specify the name with -db.

$ 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

5. Library

Example of use as a library.

package main

import (
        "log"

        "github.com/noborus/trdsql"
)

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

Please refer to godoc and _example for usage as a library.

See also psutilsql, which uses trdsql as a library.

6. License

MIT

Please check each license of SQL driver.

• SQLite
• MySQL
• PostgreSQL