0

I am working on one of the MPP databases and would like to run a single SQL query using multiple sessions in python or UNIX shell script. Can somebody share your thoughts on spawning a SQL in python/UNIX utility. Any inputs would be appreciated. Thank you.

Code :-

for i in {1..$n}
do
        (  sh run_sql.sh test.sql touchstone_test & )
done
Teja
  • 13,214
  • 36
  • 93
  • 155
  • 1
    `mysql -e "query"` will run the query from the shell if mysql is installed. – OneCricketeer Dec 23 '15 at 15:37
  • I need to open multiple sessions for a single input sql file and run them individually. – Teja Dec 23 '15 at 18:26
  • Okay. I'm not too familiar with MPP, but what type of database are you using? The Python connectors for PostgreSQL, SQL Server, MySQL, etc. are different. – OneCricketeer Dec 23 '15 at 18:34
  • Its in Actian Matrix. I have a shell script which runs a sql file and generates the output. I would like to spawn it and run the sql file using multiple sessions. – Teja Dec 23 '15 at 18:40
  • When you say multiple sessions, I assume you mean separate Unix processes? If so, you could see [How to start multiple processes in Bash](http://stackoverflow.com/questions/5238103/how-to-start-multiple-processes-in-bash) – OneCricketeer Dec 23 '15 at 18:51
  • That makes sense.. but how can I initiate multiple sessions in parallel for the same process? – Teja Dec 23 '15 at 19:20
  • What do you not understand about that link? That runs `my_script.py` 100 times in parallel. Each process is put into the background by the trailing `&`. Just replace `./my_script.py` with what you would normally run to query the database. – OneCricketeer Dec 23 '15 at 19:27
  • Yes.. I ran it multiple times using the solution provided in the above link but my code is getting hanged. As in only for the first run I am able to see the result but from second run its being hanged for a longer time. – Teja Dec 23 '15 at 19:37
  • How many processes did you start? I would advice something smaller than 100 since you are doing a database operation. – OneCricketeer Dec 23 '15 at 19:42
  • I just did it for 3. The execution time for my sql query is 42 sec. let me paste the code I have with me. – Teja Dec 23 '15 at 19:44
  • It might just hang because the database is busy processing previous queries. Assuming the database can handle that load, in a perfect world it should take no longer than `$n` times the amount of time running a single query. – OneCricketeer Dec 23 '15 at 19:59
  • Yes... you are right.. but the wait time is more than $n*(the amount of time to taken to run a single query ). – Teja Dec 23 '15 at 20:00
  • Right. That's what I meant, just didn't have parenthesis – OneCricketeer Dec 23 '15 at 20:04

2 Answers2

0

For python you could download the MySQLdb module. MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API. More info.

Tristan
  • 2,000
  • 17
  • 32
0

Depending on scale, you can choose either option.

If you have small tasks to accomplish, running a shell script is fine. Note that you can also pipe the query to the mysql CLI client, e.g. 

mysql_cmd="mysql -h<host> -u<user> -p<pwd> <db>"
echo "SELECT name, id FROM myobjects WHERE ...." | $mysql_cmd

For a larger scale project, I would go with Python and the mysqldb interface that was mentioned already.

yaronyogev
  • 428
  • 3
  • 10