https://github.com/roapi/roapi/tree/main/columnq-cli Skip to content Sign up * Why GitHub? Features - + Mobile - + Actions - + Codespaces - + Packages - + Security - + Code review - + Issues - + Integrations - + GitHub Sponsors - + Customer stories- * Team * Enterprise * Explore + Explore GitHub - Learn and contribute + Topics - + Collections - + Trending - + Learning Lab - + Open source guides - Connect with others + The ReadME Project - + Events - + Community forum - + GitHub Education - + GitHub Stars program - * Marketplace * Pricing Plans - + Compare plans - + Contact Sales - + Education - [ ] * # In this repository All GitHub | Jump to | * No suggested jump to results * # In this repository All GitHub | Jump to | * # In this organization All GitHub | Jump to | * # In this repository All GitHub | Jump to | Sign in Sign up {{ message }} roapi / roapi Public * Notifications * Star 380 * Fork 28 * Code * Issues 14 * Pull requests 0 * Actions * Projects 1 * Wiki * Security * Insights More * Code * Issues * Pull requests * Actions * Projects * Wiki * Security * Insights main Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags roapi/columnq-cli/ Go to file roapi/columnq-cli/ Latest commit @Dandandan Dandandan Dependency upgrades (#75) ... f72299f Sep 13, 2021 Dependency upgrades (#75) f72299f Git stats * History Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time . . src Dependency upgrades (#75) Sep 13, 2021 Cargo.toml Dependency upgrades (#75) Sep 13, 2021 README.md add links in readme Sep 6, 2021 [ ] Columnq Installation Pre-built binary Build from source Usage One off query Automate with UNIX pipes Format conversion Interactive console Development Debug mode README.md Columnq Simple CLI to help you query tabular data with support for a rich set of growing formats and data sources. It supports JSON, CSV, Parquet, Arrow and all other formats that are supported by ROAPI, which is documented at here. It also supports querying datasets from remote locations like S3 and HTTPs, see ROAPI's blob store documentation for more info. Installation Pre-built binary The pre-built binaries hosted on GitHub releases. These binaries are self-contained so you can just drop them into your PATH. The same set of binaries are also distributed through PyPI: pip install columnq-cli Build from source cargo install --locked --git https://github.com/roapi/roapi --branch main --bin columnq-cli 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. * (c) 2021 GitHub, Inc. * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time. You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.