trdsql

Convert object data from multiple JSON files to a CSV file with header (-oh) and double quote

$ trdsql -ocsv -oh "SELECT * FROM [path/to/file/*.json]" | sed 's/\([^,]*\)/"&"/g' > [path/to/file.csv]

$ trdsql "SELECT * FROM [path/to/file.json]::.list

$ trdsql -icsv -ih "SELECT [column1,column2] FROM [path/to/file*.csv] WHERE column2 != '' ORDER BY column1 GROUP BY column1"

$ trdsql "SELECT [column1,colum2] FROM [path/to/file1.csv] UNION SELECT [column1,column2] FROM [path/to/file2.csv]"

$ trdsql -driver postgres -dsn "host=[hostname] port=[5433] dbname=[database_name]" "SELECT 1"

$ trdsql -driver mysql -dsn "[username]:[password]@[hostname]/[database]" -ih "CREATE TABLE [table] ([column1] int, [colum2] varchar(20)) AS SELECT [column3] AS [column1],[column2] FROM [path/to/header_file.csv]"

$ trdsql -iltsv "SELECT * FROM [path/to/access.log.gz]"

trdsql [options] SQL statement [input file(s)]

-ad <address>
    Remote DB address

-analyze
    Analyze execution

-ao <output file>
    Analyze execution output file

-at <type>
    Analyze type(none,query,plan)

-config <file>
    Config file

-db <database>
    DB name

-debug
    Debug print

-dd <driver>
    Database driver. (sqlite3, mysql, postgres, bigquery, etc.)

-df <DSN>
    Database connection string.

-dsn <DSN>
    Database connection string.
Can specify multiple -dsn flags.

-dl <location>
    DB location path

-driver <driver>
    Database driver. (sqlite3, mysql, postgres, bigquery, etc.)
Can specify multiple -driver flags.

-e
    Execute SQL

-ef <file>
    Execute SQL from file

-explain
    Explain query execution plan

-f <format>
    Input table format (csv, json, ltsv, tsv, tbln, md, excel, psql, fixed, etc.)

-H
    Suppress header output

-header
    Header output

-help
    Show help message

-id <id>
    Remote DB ID

-insecure
    Skip TLS verification

-ir <regexp>
    Ignore header row regexp

-is <separator>
    Input field separator

-it <type>
    Input table type (header, noheader, regexp, json)

-j
    JSON format output

-jr
    JSON array format output

-md
    Markdown format output

-named-param
    Named parameter mode

-nr
    No Run mode

-null <string>
    Null value output string

-o <file>
    Output file

-og <group>
    Output GROUP BY format (horizontal, vertical)

-ot <format>
    Output table format (csv, json, ltsv, tsv, tbln, md, at, vertical, raw, psql, excel, etc.)

-p
    Param placeholder

-pagesize <size>
    Page size

-password <password>
    Database password

-pl <plugin>
    Plugin path

-q
    Quiet

-ro
    Read Only

-sep
    Separate column names

-si
    Skip header row

-sl <limit>
    Sampling limit

-skip-verify
    Skip verify table name

-sql-only
    SQL only mode

-strict
    Strict mode

-table
    Table name output

-tabs
    Use tabs as separator

-tbi
    Table By Input

-trace
    Trace print

-ts
    Time Zone offset

-username <username>
    Database username

-version
    Show version

trdsql is a command-line tool that allows you to execute SQL queries against various data sources like CSV, JSON, LTSV, TBLN, Markdown, Excel, and even databases directly. It acts as a bridge, enabling SQL to be used for data manipulation and analysis on files without loading them into a traditional database. It provides a flexible and powerful way to transform and extract data from diverse formats using the familiar SQL language. It reads data from files (or standard input) and uses SQLite as its underlying database engine to perform the queries. The results are then output to standard output, allowing for easy pipelining and integration with other command-line tools. Configuration can be modified using config file, environment variables or flags.

trdsql depends on SQLite for its SQL processing. Ensure that the provided SQL is compatible with SQLite syntax.
The performance can be affected by file size and query complexity.

Selecting data from a CSV file:
trdsql "SELECT * FROM data.csv"

Specifying a different input format:
trdsql -f json "SELECT * FROM data.json"

Outputting in JSON format:
trdsql -ot json "SELECT * FROM data.csv"

Using a specific delimiter:
trdsql -is '|' "SELECT * FROM data.csv"

Using a query from a file:
trdsql -ef query.sql data.csv

sqlite3(1)