Script automating dynamic queries in a remote host
Script automating dynamic queries in a remote host
(OP)
Hi everyone,
Since very long time, at work I need to repeat the same operations every time.
I've therefore decided to automate via script the following instructions, costs what it may:
Here is what I've done so far:
Any suggestion is welcome, feel free to change the logic and thank you in advance.
Since very long time, at work I need to repeat the same operations every time.
I've therefore decided to automate via script the following instructions, costs what it may:
- ask ID from prompt and store it locally
- ssh to remote host
- enter docker container
- launch DML queries using the previously stored ID
- this returns one row with another ID from the column 'id' which I need to use in the next command
- store result of a DML including the new ID into a csv
- copy the csv into a different path
- exit the docker container
- change permission on that file
- exit the ssh
- secure copy of the file from the remote host to the initial/current folder
Here is what I've done so far:
CODE -->
#!/bin/bash read -p "Enter the ID you would like to process: " my_id ssh 10.10.10.10 sudo docker exec -it psql-db /bin/sh psql -U postgres -d MY-DATABASE -c "SELECT * FROM all_cases WHERE id='my_id'"; #get the ID from the 'id' column and save it as 'new_id' Copy (Select one, two, three FROM table WHERE id='new_id') To '/tmp/'new_id'.csv' With CSV DELIMITER '|' HEADER; cp /tmp/'new_id'.csv /var/tmp/ exit sudo chown user:user /opt/tmp/'new_id'.csv exit scp 10.10.10.10:/opt/tmp/'new_id'.csv .
Any suggestion is welcome, feel free to change the logic and thank you in advance.