mssql-cli
mssql-cli copied to clipboard
dbcli
Able to pipe query into stdin and get csv on stdout
We use commandline tools to transfer data from postgresql, mysql and sqlserver to (in our case) a postgresql server.
The basic idea is like this (in this case we get data from mysql and import it in postgresql:
# schema.table was created to have the same layout as whatever is getting out of the csv
echo "SELECT ... FROM ...; " | /bin/bash -o pipefail -c "\
MYSQL_PWD=password mysql --user=projecta --host=x.x.x.x --port=xxxx --skip-column-names | \
PGOPTIONS='--client-min-messages=warning' PGTZ=Europe/Berlin \
psql --username=xxx --host=hostname --no-psqlrc --set ON_ERROR_STOP=on database --echo-queries \
--command=\"COPY schema.table FROM STDIN WITH NULL AS 'NULL'\"";
[we also run all our ETL via sending queries to stdin of a psql call, but to support that in mssql-cli would also mean "import csv on stdin" support to actually get the data into sql server]
For sqlserver, we currently use sqsh but supports at least the "SQL Script in, csv out" workflow. As sqsh is unmaintained (and buggy in versions available in ubuntu/debian), we would like to replace this by whatever finally supports at least exporting csv on stdout.
This could be split into
- query from stdin (or file)
- csv stdout
The first one is keeping me from replacing microsoft's crappy sqlcmd client with mssql-cli
cat test_query.sql | { /opt/mssql-tools/bin/sqlcmd -S <server IP> -U <DB user> -P <DB Password> -h-1 -s"," -W -i <(cat -); } | head -n -2
What this does is pipes the query to a process replacement. It produces separated values, but does not properly escape these values, so probably a symbol other than a comma should be used with this.
It works in that it gets stuff from stdin and outputs to stdout in a CSV-like way, but it's dirty, and I feel dirty, and I need to go wash myself after this.
First, thanks to the whole DBCLI team for making such a great tool! Like @jankatins mentioned, I too have been using sqsh, but it hasn't gotten much love lately, so I'd love to replace sqsh with mssql-cli. Being able to pass SQL commands to mssql-cli through stdin and getting results in stdout would really amplify mssql-cli's usefulness with scripting and automation. Is there any work being done to add this capability?
@JamesCass, thanks for the comment! Yes we are planning on updating the tool so we can target those specific scenarios regarding automation and scripting. A rough ETA would be some time March - April time frame.
@MrMeemus This is a much needed feature. I am happy to involve in any testing/bug fixing. Thanks!
I just wanted to see how the work on this was going. To be able to pass a SQL query through stdin and get results from stdout. No pressure :-) Thanks!
Just wanted to see how the progress was going for this enhancement. Thanks in advance!
Is this still being worked on? It would also be really useful for vim integration for me.
Along similar lines. the app does not write errors to the stderr but rather to stdout. This makes redirecting stdout to a file rather clunky. I can create a separate ticket for this behavior if it cannot be fixed as part of this ticket.
@apisarenco have you found a way to make your dirty method include the column headers too?
