All Projects → neilotoole → Sq

neilotoole / Sq

Licence: mit
swiss-army knife for data

Programming Languages

go
31211 projects - #10 most used programming language
golang
3204 projects

Projects that are alternatives of or similar to Sq

Dbwebapi
(Migrated from CodePlex) DbWebApi is a .Net library that implement an entirely generic Web API (RESTful) for HTTP clients to call database (Oracle & SQL Server) stored procedures or functions in a managed way out-of-the-box without any configuration or coding.
Stars: ✭ 84 (-69.45%)
Mutual labels:  excel, json, xml, csv, xlsx
Filecontextcore
FileContextCore is a "Database"-Provider for Entity Framework Core and adds the ability to store information in files instead of being limited to databases.
Stars: ✭ 91 (-66.91%)
Mutual labels:  excel, json, xml, csv, xlsx
Pytablewriter
pytablewriter is a Python library to write a table in various formats: CSV / Elasticsearch / HTML / JavaScript / JSON / LaTeX / LDJSON / LTSV / Markdown / MediaWiki / NumPy / Excel / Pandas / Python / reStructuredText / SQLite / TOML / TSV.
Stars: ✭ 422 (+53.45%)
Mutual labels:  excel, json, markdown, csv, tsv
Sheetjs
📗 SheetJS Community Edition -- Spreadsheet Data Toolkit
Stars: ✭ 28,479 (+10256%)
Mutual labels:  excel, json, xml, csv, xlsx
Structured Text Tools
A list of command line tools for manipulating structured text data
Stars: ✭ 6,180 (+2147.27%)
Mutual labels:  json, xml, csv, tsv
Tabtoy
高性能表格数据导出器
Stars: ✭ 1,302 (+373.45%)
Mutual labels:  excel, json, csv, xlsx
Countries States Cities Database
🌍 World countries, states, regions, provinces, cities, towns in JSON, SQL, XML, PLIST, YAML, and CSV. All Countries, States, Cities with ISO2, ISO3, Country Code, Phone Code, Capital, Native Language, Timezones, Latitude, Longitude, Region, Subregion, Flag Emoji, and Currency. #countries #states #cities
Stars: ✭ 1,130 (+310.91%)
Mutual labels:  json, xml, csv, sql
Algeria Cities
The list of all Algerian provinces and cities according to the official division in different formats: csv, xlsx, php, json, etc.
Stars: ✭ 232 (-15.64%)
Mutual labels:  json, csv, xlsx, sql
Uxdm
🔀 UXDM helps developers migrate data from one system or format to another.
Stars: ✭ 159 (-42.18%)
Mutual labels:  json, markdown, xml, csv
Xresloader
跨平台Excel导表工具(Excel=>protobuf/msgpack/lua/javascript/json/xml)
Stars: ✭ 161 (-41.45%)
Mutual labels:  excel, json, xml, xlsx
Meza
A Python toolkit for processing tabular data
Stars: ✭ 374 (+36%)
Mutual labels:  excel, xml, csv, xlsx
Sqawk
Like Awk but with SQL and table joins
Stars: ✭ 263 (-4.36%)
Mutual labels:  json, csv, tsv, sql
Gotenberg
A Docker-powered stateless API for PDF files.
Stars: ✭ 3,272 (+1089.82%)
Mutual labels:  markdown, csv, excel, xlsx
Bancosbrasileiros
Lista de bancos brasileiros | Brazilian banks list
Stars: ✭ 178 (-35.27%)
Mutual labels:  json, xml, csv, sql
J
❌ Multi-format spreadsheet CLI (now merged in http://github.com/sheetjs/js-xlsx )
Stars: ✭ 343 (+24.73%)
Mutual labels:  excel, markdown, csv, xlsx
Sqlitebiter
A CLI tool to convert CSV / Excel / HTML / JSON / Jupyter Notebook / LDJSON / LTSV / Markdown / SQLite / SSV / TSV / Google-Sheets to a SQLite database file.
Stars: ✭ 601 (+118.55%)
Mutual labels:  excel, json, csv, tsv
Magento2 Import Export Sample Files
Default Magento 2 CE import / export CSV files & sample files for Firebear Improved Import / Export extension
Stars: ✭ 68 (-75.27%)
Mutual labels:  excel, json, xml, csv
Js2excel
😌 😃 👿 A simple module for excel and json converts each other, which works in the browser.
Stars: ✭ 83 (-69.82%)
Mutual labels:  excel, json, xlsx
Myexcel
MyExcel, a new way to operate excel!
Stars: ✭ 1,198 (+335.64%)
Mutual labels:  excel, csv, xlsx
Elasticsearch Dataformat
Excel/CSV/BulkJSON downloads on Elasticsearch.
Stars: ✭ 135 (-50.91%)
Mutual labels:  excel, json, csv

sq: swiss-army knife for data

sq is a command line tool that provides jq-style access to structured data sources such as SQL databases, or document formats like CSV or Excel.

sq can perform cross-source joins, execute database-native SQL, and output to a multitude of formats including JSON, Excel, CSV, HTML, Markdown and XML, or insert directly to a SQL database. sq can also inspect sources to view metadata about the source structure (tables, columns, size) and has commands for common database operations such as copying or dropping tables.

Install

For other installation options, see here.

It is strongly advised to install shell completion.

macOS

brew install neilotoole/sq/sq

Windows

scoop bucket add sq https://github.com/neilotoole/sq
scoop install sq

Linux

apt

curl -fsSLO https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.deb && sudo apt install -y ./sq-linux-amd64.deb && rm ./sq-linux-amd64.deb

rpm

sudo rpm -i https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.rpm

yum

yum localinstall -y https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.rpm

Shell completion

Shell completion is available for bash, zsh, fish, and powershell. It is strongly recommended to install.

Execute sq completion --help for installation instructions.

Quickstart

Use sq help to see command help. The tutorial is the best place to start. The cookbook has recipes for common actions.

The major concept is: sq operates on data sources, which are treated as SQL databases (even if the source is really a CSV or XLSX file etc).

In a nutshell, you sq add a source (giving it a handle), and then execute commands against the source.

Sources

Initially there are no sources.

$ sq ls


Let's add a source. First we'll add a SQLite database, but this could also be Postgres, SQL Server, Excel, etc. Download the sample DB, and sq add the source. We use -h to specify a handle to use.

$ wget https://sq.io/testdata/sakila.db

$ sq add ./sakila.db -h @sakila_sl3
@sakila_sl3  sqlite3  sakila.db

$ sq ls -v
HANDLE       DRIVER   LOCATION                 OPTIONS
@sakila_sl3* sqlite3  sqlite3:/root/sakila.db

$ sq ping @sakila_sl3
@sakila_sl3  1ms  pong

$ sq src
@sakila_sl3  sqlite3  sakila.db

The sq ping command simply pings the source to verify that it's available.

sq src lists the active source, which in our case is @sakila_sl3. You can change the active source using sq src @other_src. When there's an active source specified, you can usually omit the handle from sq commands. Thus you could instead do:

$ sq ping
@sakila_sl3  1ms  pong

Query

Fundamentally, sq is for querying data. Using our jq-style syntax:

$ sq '.actor | .actor_id < 100 | .[0:3]'
actor_id  first_name  last_name     last_update
1         PENELOPE    GUINESS       2020-02-15T06:59:28Z
2         NICK        WAHLBERG      2020-02-15T06:59:28Z
3         ED          CHASE         2020-02-15T06:59:28Z

The above query selected some rows from the actor table. You could also use native SQL, e.g.:

$ sq sql 'SELECT * FROM actor WHERE actor_id < 100 LIMIT 3'
actor_id  first_name  last_name  last_update
1         PENELOPE    GUINESS    2020-02-15T06:59:28Z
2         NICK        WAHLBERG   2020-02-15T06:59:28Z
3         ED          CHASE      2020-02-15T06:59:28Z

But we're flying a bit blind here: how did we know about the actor table?

Inspect

sq inspect is your friend (output abbreviated):

$ sq inspect
HANDLE          DRIVER   NAME       FQ NAME         SIZE   TABLES  LOCATION
@sakila_sl3     sqlite3  sakila.db  sakila.db/main  5.6MB  21      sqlite3:///root/sakila.db

TABLE                   ROWS   TYPE   SIZE  NUM COLS  COL NAMES                                                                          COL TYPES
actor                   200    table  -     4         actor_id, first_name, last_name, last_update                                       numeric, VARCHAR(45), VARCHAR(45), TIMESTAMP
address                 603    table  -     8         address_id, address, address2, district, city_id, postal_code, phone, last_update  int, VARCHAR(50), VARCHAR(50), VARCHAR(20), INT, VARCHAR(10), VARCHAR(20), TIMESTAMP
category                16     table  -     3         category_id, name, last_update

Use --json (-j) to output in JSON (output abbreviated):

$ sq inspect -j
{
  "handle": "@sakila_sl3",
  "name": "sakila.db",
  "driver": "sqlite3",
  "db_version": "3.31.1",
  "location": "sqlite3:///root/sakila.db",
  "size": 5828608,
  "tables": [
    {
      "name": "actor",
      "table_type": "table",
      "row_count": 200,
      "columns": [
        {
          "name": "actor_id",
          "position": 0,
          "primary_key": true,
          "base_type": "numeric",
          "column_type": "numeric",
          "kind": "decimal",
          "nullable": false
        }

Combine sq inspect with jq for some useful capabilities. Here's how to list all the table names in the active source:

$ sq inspect -j | jq -r '.tables[] | .name'
actor
address
category
city
country
customer
[...]

And here's how you could export each table to a CSV file:

$ sq inspect -j | jq -r '.tables[] | .name' | xargs -I % sq .% --csv --output %.csv
$ ls
actor.csv     city.csv	    customer_list.csv  film_category.csv  inventory.csv  rental.csv		     staff.csv
address.csv   country.csv   film.csv	       film_list.csv	  language.csv	 sales_by_film_category.csv  staff_list.csv
category.csv  customer.csv  film_actor.csv     film_text.csv	  payment.csv	 sales_by_store.csv	     store.csv

Note that you can also inspect an individual table:

$ sq inspect @sakila_sl3.actor
TABLE  ROWS  TYPE   SIZE  NUM COLS  COL NAMES                                     COL TYPES
actor  200   table  -     4         actor_id, first_name, last_name, last_update  numeric, VARCHAR(45), VARCHAR(45), TIMESTAMP

Insert Output Into Database Source

sq query results can be output in various formats (JSON, XML, CSV, etc), and can also be "outputted" as an insert into database sources.

That is, you can use sq to insert results from a Postgres query into a MySQL table, or copy an Excel worksheet into a SQLite table, or a push a CSV file into a SQL Server table etc.

Note: If you want to copy a table inside the same (database) source, use sq tbl copy instead, which uses the database's native table copy functionality.

For this example, we'll insert an Excel worksheet into our @sakila_sl3 SQLite database. First, we download the XLSX file, and sq add it as a source.

$ wget https://sq.io/testdata/xl_demo.xlsx

$ sq add ./xl_demo.xlsx --opts header=true
@xl_demo_xlsx  xlsx  xl_demo.xlsx

$ sq @xl_demo_xlsx.person
uid  username    email                  address_id
1    neilotoole  [email protected]  1
2    ksoze       [email protected]        2
3    kubla       [email protected]          NULL
[...]

Now, execute the same query, but this time sq inserts the results into a new table (person) in @sakila_sl3:

$ sq @xl_demo_xlsx.person --insert @sakila_sl3.person
Inserted 7 rows into @sakila_sl3.person

$ sq inspect @sakila_sl3.person
TABLE   ROWS  TYPE   SIZE  NUM COLS  COL NAMES                         COL TYPES
person  7     table  -     4         uid, username, email, address_id  INTEGER, TEXT, TEXT, INTEGER

$ sq @sakila_sl3.person
uid  username    email                  address_id
1    neilotoole  [email protected]  1
2    ksoze       [email protected]        2
3    kubla       [email protected]          NULL
[...]

Cross-Source Join

sq has rudimentary support for cross-source joins. That is, you can join an Excel worksheet with a CSV file, or Postgres table, etc.

Note: The current mechanism for these joins is highly naive: sq copies the joined table from each source to a "scratch database" (SQLite by default), and then performs the JOIN using the scratch database's SQL interface. Thus, performance is abysmal for larger tables. There are massive optimizations to be made, but none have been implemented yet.

See the tutorial for further details, but given an Excel source @xl_demo and a CSV source @csv_demo, you can do:

$ sq '@csv_demo.data, @xl_demo.address | join(.D == .address_id) | .C, .city'
C                      city
[email protected]  Washington
[email protected]        Ulan Bator
[email protected]        Washington
[email protected]    Ulan Bator
[email protected]        Washington

Table Commands

sq provides several handy commands for working with tables. Note that these commands work directly against SQL database sources, using their native SQL commands.

$ sq tbl copy .actor .actor_copy
Copied table: @sakila_sl3.actor --> @sakila_sl3.actor_copy (200 rows copied)

$ sq tbl truncate .actor_copy
Truncated 200 rows from @sakila_sl3.actor_copy

$ sq tbl drop .actor_copy
Dropped table @sakila_sl3.actor_copy

UNIX Pipes

For file-based sources (such as CSV or XLSX), you can sq add the source file, but you can also pipe it:

$ cat ./example.xlsx | sq .Sheet1

Similarly, you can inspect:

$ cat ./example.xlsx | sq inspect

Data Source Drivers

sq knows how to deal with a data source type via a driver implementation. To view the installed/supported drivers:

$ sq drivers
DRIVER     DESCRIPTION                            USER-DEFINED  DOC
sqlite3    SQLite                                 false         https://github.com/mattn/go-sqlite3
postgres   PostgreSQL                             false         https://github.com/jackc/pgx
sqlserver  Microsoft SQL Server                   false         https://github.com/denisenkom/go-mssqldb
mysql      MySQL                                  false         https://github.com/go-sql-driver/mysql
csv        Comma-Separated Values                 false         https://en.wikipedia.org/wiki/Comma-separated_values
tsv        Tab-Separated Values                   false         https://en.wikipedia.org/wiki/Tab-separated_values
json       JSON                                   false         https://en.wikipedia.org/wiki/JSON
jsona      JSON Array: LF-delimited JSON arrays   false         https://en.wikipedia.org/wiki/JSON
jsonl      JSON Lines: LF-delimited JSON objects  false         https://en.wikipedia.org/wiki/JSON_streaming#Line-delimited_JSON
xlsx       Microsoft Excel XLSX                   false         https://en.wikipedia.org/wiki/Microsoft_Excel

Output Formats

sq has many output formats:

  • --table: Text/Table
  • --json: JSON
  • --jsona: JSON Array
  • --jsonl: JSON Lines
  • --csv / --tsv : CSV / TSV
  • --xlsx: XLSX (Microsoft Excel)
  • --html: HTML
  • --xml: XML
  • --markdown: Markdown
  • --raw: Raw (bytes)

Acknowledgements

  • Much inspiration is owed to jq.
  • See go.mod for a list of third-party packages.
  • Additionally, sq incorporates modified versions of:
  • The Sakila example databases were lifted from jOOQ, which in turn owe their heritage to earlier work on Sakila.

Similar / Related / Noteworthy Projects

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].