- EXIT CODES
- COPYRIGHT AND LICENSE
fsql - Perform SQL queries against files in CSV/TSV/LTSV/JSON/YAML formats
This document describes version 0.18 of fsql (from Perl distribution App-fsql), released on 2015-05-13.
fsql [OPTIONS] [ <QUERY> | --show-schema|-s ]
fsql lets you perform SQL queries against one or several "flat" files of various formats. Each file will be regarded as a SQL table. By using SQL queries, you can do various calculations or manipulations that are otherwise hard/cumbersome to do with traditional text-manipulating Unix commands like cut, sort, head, tail, uniq, and so on. Particularly: data grouping, joining, or filtering with SQL expressions and functions.
As a bonus, you can also modify data (currently CSV only) via SQL INSERT or DELETE commands.
The query result will then be printed out, in one of several available formats.
fsql, you must at least specify one file/table (with
--add or one of the
--add-TYPE options). If none of those options are specified, a table is assumed in STDIN with name
stdin and the format will be detected. You must also specify a SQL query.
Filter CSV (table from stdin is aptly named so):
% prog-that-produces-csv | fsql 'SELECT id,name FROM stdin WHERE id <= 1000' > final.csv
Pick output format, produce array of hashes instead of the default array of arrays:
% fsql -a ~/book.pl 'SELECT title,name FROM book WHERE year >= 2010' --aoh -f json
You can perform joins, of course:
% fsql -a t.json -a 2.csv:t2 'SELECT * FROM t1 LEFT JOIN t2 ON t1.uid=t2.id'
% fsql -a table1.json -a 2.csv:table2 -s
Insert row to CSV:
% fsql -a file.csv 'INSERT INTO file VALUES (1, 2, 3)'
Delete rows from CSV:
% fsql -a file.csv 'DELETE FROM file WHERE c1 < 10'
Add a table from a file. Type will be detected from filename extension (and some heuristics, if there is no file extension or extension is unrecognized). Die if type cannot be detected.
Sometimes the detection will miss. Alternatively, you can use one of the
--add-TYPEoptions to add a specific table type.
Add a table from a CSV file. If
TABLENAMEis not specified, it will be taken from
FILENAME(e.g. with filename
foo-bar.csv, table name will be
-to mean the standard input (the default table name will be
stdin). Will croak if duplicate table name is detected.
Table name must match regex
--add-csv, but will load file as TSV (tab-separated value).
--add-csv, but will load file as LTSV (labeled tab separated value, see Text::LTSV). Names of columns will be taken from the first row.
--add-csv, but will load file as JSON.
--add-json, but will load file as YAML.
--add-json, but will load file as Perl.
Load a SQL function. This will load Perl module
SQL::Statement::Function::ByName::NAME. See CPAN for list of available modules.
Return array of array (the default). Only relevant to outputs like
Return array of hashes instead of the default array of array, where each row is represented as a hash (dictionary/associated array) instead of an array. Only relevant to output formats like
Returning a hash is convenient when you want column name information on each row, but you can't specify the same column twice and order of columns are not guaranteed.
--format=FORMAT (default: text), -f
Set output format.
ltsvwill cause query results to be output as a comma-separated or TAB-separated list or labeled-TAB separated list, respectively. As this isn't very useful for a schema listing, these values will be silently converted to
-s) is also present.
The other values
textwill be formatted using appropriate Data::Format::Pretty formatter.
The default value is the most used table format. So if your tables are mostly CSV, fsql will also output CSV by default.
Instead of running a query, show schema instead. This is useful for debugging.
0 on success.
255 on I/O or SQL error.
99 on command-line options or input data error.
What SQL dialect is supported? Why is SQL feature "X" not supported?
See SQL::Statement::Syntax for the range of supported SQL syntax. In short, you can do select with several kinds of joins, almost/around 100 builtin functions (with additional functions available from Perl modules, see next FAQ entry), etc.
Also, sometimes if there is SQL parsing error, the error message is not immediately obvious and can be a bit confusing. That is usually a parsing limitation/bug within SQL::Statement.
How do I define more SQL functions? Why aren't there more date/time/X functions?
SQL::Statement allows loading Perl functions as SQL functions. There are several CPAN modules which nicely package them so you can load them from fsql simply by using the
-F option, e.g. to load the YEAR() function:
% fsql -F YEAR --add-csv sometable.csv 'SELECT c1, YEAR(c2) FROM sometable ...'
Please visit the project's homepage at https://metacpan.org/release/App-fsql.
Source repository is at https://github.com/perlancar/perl-App-fsql.
Please report any bugs or feature requests on the bugtracker website https://rt.cpan.org/Public/Dist/Display.html?Name=App-fsql
When submitting a bug or request, please include a test-file or a patch to an existing test-file that illustrates the bug or desired feature.
COPYRIGHT AND LICENSE
This software is copyright (c) 2015 by email@example.com.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.