Feature: custom SQL query for export, i.e. export subset of table or just a few tables

[joonas-fi]

Requested by: https://twitter.com/iamhasibrahman/status/825374048284610560?cn=bWVudGlvbg

This could probably be implemented by giving the query in ARGV or as ENV variable.

[joonas-fi]

Custom SQL query is now supported via the ENV variable "SQL":

$ docker run --rm -it -v "$(pwd)/sql2json_result:/result" -e "SQL=SELECT * FROM \"Employee\" WHERE \"Title\" = 'IT Staff'" -e "DSN=postgres,,pgsql:dbname=chinook;host=172.17.0.8;port=5432" joonas/sql2json
2017-02-04 15:26:26 - Using username: postgres
2017-02-04 15:26:26 - Password: (no password)
2017-02-04 15:26:26 - Connecting to DSN pgsql:dbname=chinook;host=172.17.0.8;port=5432
2017-02-04 15:26:26 - Listing tables
2017-02-04 15:26:26 - Skipping schema fetch - only know how to do it for MySQL
2017-02-04 15:26:26 - Custom SQL statement specified: SELECT * FROM "Employee" WHERE "Title" = 'IT Staff'
2017-02-04 15:26:26 - Wrote 2 rows to /result/data/custom_1486221986.json.gz
2017-02-04 15:26:26 - Done

$ tree sql2json_result/
sql2json_result/
|-- data
|   `-- custom_1486221986.json.gz
`-- schema

2 directories, 1 file

$ cat sql2json_result/data/custom_1486221986.json.gz | gzip -d
[
  {
    "EmployeeId": 7,
    "LastName": "King",
    "FirstName": "Robert",
    "Title": "IT Staff"
  },
  {
    "EmployeeId": 8,
    "LastName": "Callahan",
    "FirstName": "Laura",
    "Title": "IT Staff"
  }
]

If you have ran sql2json before (you have older image without this feature), make sure to do Docker pull to get the latest image:

$ docker pull joonas/sql2json

[iamhasibrahman]

You are passing the sql as parameter in command line. Since you said env variable .. So I guess I can store my sql in file and pass the file name in cli

[joonas-fi]

You need to pass the SQL statement as a value to sql2json, but you can populate the content of the ENV variable-to-be-set from a file using shell tricks, sure.

[iamhasibrahman]

Yes. I am thinking of trying it in production and at times my sql query is much long so it can't be passed in cli. Need to put it in script

[joonas-fi]

@iamhasibrahman: just curious, how's this feature working out for you?

[joonas-fi]

Closing because inactivity. @iamhasibrahman, feel free to reply and we'll re-open if necessary.