How to join a CSV file with a JSON file using dsq?

Use the command 'dsq file.csv file.json "SELECT {0}.col, {1}.col FROM {0} JOIN {1} ON ..."' as shown in the Multiple files and joins example, where {0} and {1} refer to the file indices or aliases for cross-format queries.

dsq vs DuckDB for querying Parquet files?

dsq is simpler for quick CLI queries on multiple formats using SQLite, but DuckDB offers better performance with column-oriented storage for analytics, as highlighted in the Comparisons and Benchmark sections where DuckDB is faster in tests.

Does dsq support Excel files with multiple sheets?

Yes, but you must specify the sheet name as a path, like 'SELECT * FROM {"Sheet2"}', as explained in the Multiple Excel sheets subsection under array of objects nested within an object.

How to cache queries in dsq for faster repeated use?

Use the --cache flag to enable disk caching; dsq calculates a SHA1 sum of files and reuses the imported data if unchanged, speeding up subsequent queries as described in the Caching section with examples.

Can dsq handle nested JSON arrays in queries?

Yes, but nested arrays are converted to JSON strings, and you can use SQL JSON operators like -> for access, as shown in the Nested arrays and JSON operators sections, though path navigation through arrays isn't supported.

Is dsq still maintained in 2023 or later?

No, the README warns it's not under active development and suggests alternatives like DuckDB or ClickHouse-local, so it may not receive updates or bug fixes for new issues.

dsq — SQL Queries for Data Files

What is dsq?

dsq is a command-line tool that allows users to run SQL queries directly against structured data files like JSON, CSV, Excel, and Parquet. It solves the problem of needing to import data into a database before querying, enabling quick data exploration and transformation from the terminal.

Target Audience

Data analysts, developers, and system administrators who work with multiple data formats and need to quickly query or transform files without setting up a full database.

Value Proposition

Developers choose dsq for its simplicity, broad file format support, and the ability to use familiar SQL syntax for ad-hoc data analysis, making it a versatile alternative to format-specific tools or heavier database solutions.

Commandline tool for running SQL queries against JSON, CSV, Excel, Parquet, and more.

Use Cases

Best For

Querying CSV or JSON files with SQL without importing them into a database
Joining data from different file formats (e.g., CSV with JSON) in a single SQL query
Quickly exploring the structure and content of Parquet or Excel files from the command line
Transforming nested JSON data into flat tables using SQL dot notation
Running aggregate queries (like COUNT, AVG) on log files or large datasets
Interactive data exploration with a SQL REPL for ad-hoc analysis

Not Ideal For

High-performance analytics on large datasets requiring column-oriented storage or vectorized execution
Projects needing active maintenance and frequent updates due to its current inactive development status
Handling deeply nested JSON structures where path navigation through arrays is necessary
Windows users relying heavily on piping data, as it doesn't work on Windows outside WSL

Pros & Cons

Pros

Broad Format Support

Supports querying JSON, CSV, Excel, Parquet, Avro, YAML, and log files directly, as listed in the Supported Data Types table, eliminating the need for format-specific tools.

Cross-File Joins

Enables SQL joins across multiple files of different formats in a single command, demonstrated in the 'Multiple files and joins' section with examples joining CSV and JSON data.

Efficient Caching

Optional disk caching speeds up repeated queries on unchanged datasets by storing imported data with SHA1-based invalidation, detailed in the Caching section for large file exploration.

Interactive REPL

Built-in SQL REPL allows iterative querying without reloading data, shown in the Interactive REPL section for ad-hoc data exploration.

Cons

Inactive Development

The README explicitly states the project is not under active development and recommends alternatives like DuckDB for stronger analytics foundations, risking long-term support.

SQLite Performance Trade-offs

Relies on SQLite, which lacks column-oriented storage and vectorized execution, making it slower for large-scale analytics compared to DuckDB or ClickHouse, as noted in benchmarks.

Nested Data Limitations

Cannot specify paths through arrays (only objects) or retrieve whole objects, restricting flexibility with complex JSON structures, admitted in the 'Limitation: nested arrays' and 'Limitation: whole object retrieval' sections.

What is dsq?

Target Audience

Data analysts, developers, and system administrators who work with multiple data formats and need to quickly query or transform files without setting up a full database.

Value Proposition

Use Cases

Best For

Querying CSV or JSON files with SQL without importing them into a database
Joining data from different file formats (e.g., CSV with JSON) in a single SQL query
Quickly exploring the structure and content of Parquet or Excel files from the command line
Transforming nested JSON data into flat tables using SQL dot notation
Running aggregate queries (like COUNT, AVG) on log files or large datasets
Interactive data exploration with a SQL REPL for ad-hoc analysis

Not Ideal For

High-performance analytics on large datasets requiring column-oriented storage or vectorized execution
Projects needing active maintenance and frequent updates due to its current inactive development status
Handling deeply nested JSON structures where path navigation through arrays is necessary
Windows users relying heavily on piping data, as it doesn't work on Windows outside WSL

Pros & Cons

Pros

Broad Format Support

Supports querying JSON, CSV, Excel, Parquet, Avro, YAML, and log files directly, as listed in the Supported Data Types table, eliminating the need for format-specific tools.

Cross-File Joins

Enables SQL joins across multiple files of different formats in a single command, demonstrated in the 'Multiple files and joins' section with examples joining CSV and JSON data.

Efficient Caching

Optional disk caching speeds up repeated queries on unchanged datasets by storing imported data with SHA1-based invalidation, detailed in the Caching section for large file exploration.

Interactive REPL

Built-in SQL REPL allows iterative querying without reloading data, shown in the Interactive REPL section for ad-hoc data exploration.

Cons

Inactive Development

The README explicitly states the project is not under active development and recommends alternatives like DuckDB for stronger analytics foundations, risking long-term support.

SQLite Performance Trade-offs

Relies on SQLite, which lacks column-oriented storage and vectorized execution, making it slower for large-scale analytics compared to DuckDB or ClickHouse, as noted in benchmarks.

Nested Data Limitations

dsq

What is dsq?

Overview

Use Cases

Best For

Not Ideal For

Pros & Cons

Pros

Cons

Frequently Asked Questions

Related Projects

Found a gem we're missing?

dsq

What is dsq?

Overview

Use Cases

Best For

Not Ideal For

Pros & Cons

Pros

Cons

Frequently Asked Questions

Related Projects

Found a gem we're missing?