No project description provided
Project description
Columnq
Simple CLI to help you query tabular data with support for a rich set of growing formats and data sources.
Usage
One off query
The sql
sbucommand execute a provided SQL query against specificed static
dataset and return the result in stdout on exit. This is usually useful for
script automation tasks.
$ columnq sql --table test_data/spacex_launches.json \
"SELECT COUNT(id), DATE_TRUNC('year', CAST(date_utc AS TIMESTAMP)) as d FROM spacex_launches WHERE success = true GROUP BY d ORDER BY d DESC"
+-----------+---------------------+
| COUNT(id) | d |
+-----------+---------------------+
| 4 | 2021-01-01 00:00:00 |
| 26 | 2020-01-01 00:00:00 |
| 13 | 2019-01-01 00:00:00 |
| 21 | 2018-01-01 00:00:00 |
| 18 | 2017-01-01 00:00:00 |
| 8 | 2016-01-01 00:00:00 |
| 6 | 2015-01-01 00:00:00 |
| 6 | 2014-01-01 00:00:00 |
| 3 | 2013-01-01 00:00:00 |
| 2 | 2012-01-01 00:00:00 |
| 2 | 2010-01-01 00:00:00 |
| 1 | 2009-01-01 00:00:00 |
| 1 | 2008-01-01 00:00:00 |
+-----------+---------------------+
By default, the sql
subcommand outputs results in human friendly table
format. You can change the output format using --output
option to make it
more friendly for automations.
$ columnq sql --table test_data/spacex_launches.json --output json "SELECT COUNT(id) AS total_launches FROM spacex_launches"
[{"total_launches":132}]
Automate with UNIX pipes
Just like other UNIX tools, columnq supports consuming data stream from stdin to integrate with other CLI tools using UNIX pipe:
find . -printf "%M|%n|%u|%s|%P\n" | columnq sql \
--table 't=stdin,format=csv,has_header=false,delimiter=|' \
"SELECT SUM(column_4) as total_size FROM t"
+------------+
| total_size |
+------------+
| 9875017987 |
+------------+
Format conversion
The Columnq CLI can also be used as a handy utility to convert tabular data
between various formats: json
, parquet
, csv
, yaml
, arrow
, etc.
$ columnq sql --table 't=test_data/uk_cities_with_headers.csv' 'SELECT * FROM t' --output json
$ cat test_data/blogs.parquet | columnq sql --table 't=stdin,format=parquet' 'SELECT * FROM t' --output json
Interactive console
For dataset exploration, you can use the console
subcommand to query multiple
datasets in an interactive console environment:
$ columnq console \
--table "uk_cities=test_data/uk_cities_with_headers.csv" \
--table "test_data/spacex_launches.json"
columnq(sql)> SELECT * FROM uk_cities WHERE lat > 57;
+-----------------------------+-----------+-----------+
| city | lat | lng |
+-----------------------------+-----------+-----------+
| Elgin, Scotland, the UK | 57.653484 | -3.335724 |
| Aberdeen, Aberdeen City, UK | 57.149651 | -2.099075 |
| Inverness, the UK | 57.477772 | -4.224721 |
+-----------------------------+-----------+-----------+
columnq(sql)> SELECT COUNT(*) FROM spacex_launches WHERE success=true AND upcoming=false;
+-----------------+
| COUNT(UInt8(1)) |
+-----------------+
| 111 |
+-----------------+
Explore in memory catalog and table schemas:
columnq(sql)> SHOW TABLES;
+---------------+--------------------+-----------------+------------+
| table_catalog | table_schema | table_name | table_type |
+---------------+--------------------+-----------------+------------+
| datafusion | public | uk_cities | BASE TABLE |
| datafusion | public | spacex_launches | BASE TABLE |
| datafusion | information_schema | tables | VIEW |
| datafusion | information_schema | columns | VIEW |
+---------------+--------------------+-----------------+------------+
columnq(sql)> SHOW COLUMNS FROM uk_cities;
+---------------+--------------+------------+-------------+-----------+-------------+
| table_catalog | table_schema | table_name | column_name | data_type | is_nullable |
+---------------+--------------+------------+-------------+-----------+-------------+
| datafusion | public | uk_cities | city | Utf8 | NO |
| datafusion | public | uk_cities | lat | Float64 | NO |
| datafusion | public | uk_cities | lng | Float64 | NO |
+---------------+--------------+------------+-------------+-----------+-------------+
Development
Debug mode
Set the RUST_LOG
environment variable to info,columnq=debug
to run columnq
in verbose debug logging.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distributions
Built Distributions
Hashes for columnq_cli-0.1.1-py3-none-win_amd64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 0893104e2b66cebfdda56a42e3056cfb8fdc35e91d73696ce06564ea05f6567d |
|
MD5 | 38ab81845244621aa6a058bd55db84c9 |
|
BLAKE2b-256 | 93624d1903c5090988e8ff2adeec552adb239aa7e81dc2d6a6ee5bf7c020691f |
Hashes for columnq_cli-0.1.1-py3-none-manylinux_2_17_armv7l.manylinux2014_armv7l.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | c257c0cbc3e0057b1b69d3e4a9ecb17cf95610e4274bde588c929ec5c048b815 |
|
MD5 | 4851c9a193418d8f4eabf8bf842e44be |
|
BLAKE2b-256 | 2277c57ac976ca7fa154a0c3ed8574356a620bd7725b33c48988ac0e87e98981 |
Hashes for columnq_cli-0.1.1-py3-none-manylinux_2_17_aarch64.manylinux2014_aarch64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | afce45b2345cf317f358d4c2fbdcf1bbbf65994606fbbaa351715d7d92c9b2cd |
|
MD5 | 78ba5168a7b1daec2139bfb56ed63fae |
|
BLAKE2b-256 | b75a330edbc2301bed650587fa50f19f902cb5207284ce3a55479e7e7c91072e |
Hashes for columnq_cli-0.1.1-py3-none-manylinux_2_12_x86_64.manylinux2010_x86_64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | c8eefbe16c6750c2327e51a3fd0dd278bcbb9f7222241b893f9c57df7d77766c |
|
MD5 | effe096183ce5dccdc056d61b7ba5d76 |
|
BLAKE2b-256 | a1512ed7b5b2233f366905ff927b756ca88018c8fbd6ce8cadb4c76bd831acb4 |
Hashes for columnq_cli-0.1.1-py3-none-macosx_10_9_x86_64.macosx_11_0_arm64.macosx_10_9_universal2.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | d0a0e6d1669f2e005a9abd09e21a48cec2b5cdb6e501929188a3f10bf595005f |
|
MD5 | da2c8933b2aebdc7665d7e8e24b6253d |
|
BLAKE2b-256 | 7b48f16d4d06df46c64b1085b6fa03a369b6fc7afbebca0be2612bdcbedc291e |
Hashes for columnq_cli-0.1.1-py3-none-macosx_10_7_x86_64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | ac486f13d2225dc8b6f768888b520a86a21510211986d27afed077c001ccf00f |
|
MD5 | 4478a860b1ad8f23b911c247436a80d0 |
|
BLAKE2b-256 | 57bb78a51491c18fd5a62337a705f08a3f5546b1207a3d8525d32fb4deaf58f9 |