How to pull a query from the server to another server or local

So I am an undergraduate student and am trying to get my query to be output onto my local machine or another server. I know COPY TO will do this in the OMNISCI server but I need it on another server. Any help would be appreciated.

HI @Giovanni_Petroni,

Do you want to spool and save the query results into a local machine without using third party tools or custom developed software?

Using COPY TO will dump the result of a query on a local mounted filesystem, that can be a local or a remote (SMB/NTFS) filesystem .

If you want to share more in detail what you want to achieve, we will be able to point you in the right direction

we can connect to omnisql remotely also
omnisql DB_NAME -u DB_USER -p DB_PASS -s “omnisql server”

and you can run COPY To command to copy to local server.
but for this, “omnisql” command need, for this do we need to install full omnisci or like oracle client any omnisql client only option avaliable ? need expert help

OR you can create a script in local machine and run it on the server, where omnisql is running over “ssh”.

Hi @zachariam,

The COPY TO command will write the result file into the omnisci server, regardless of the machine you are using to run the command; it’s more a command designed to dump a large subset of data, not something to generate a report.

If you want to use omnisql you can copy the executable on the machine where you wanto to run, without the need to install the server (that’s assuming you are using a statically linked version of the software, like all kind of archives you download from our site); or you can use our connectors (JDBC and Python) to use other client software like Dbeaver, SquirrelSQL, Sql Worbench/J to name a few.

1 Like

@Giovanni_Petroni @zachariam

If you are interested in run a query from a remote machine and save the results locally, assuming you have omnisql installed into the local machine

echo "select * from flights limit 1;" | ./omnisql -n -q -s omniscidb_host -p your_password >local_file.txt

it’s important to use the switch -q (quiet) and .n (no header) to suppress unwanted output.

as an example

mapd@zion16:~$ echo "select flight_year,avg(depdelay) as avg_delay,count(*) as nof from flights where plane_type is not null group by 1 order by 1;" | ./omnisql -q -n -s 192.168.1.11 -p HyperInteractive >local_file.txt
mapd@zion16:~$ cat local_file.txt
1995|8.071271978882377|1665427
1996|9.998662298937367|1705829
1997|8.119505526551631|1770314
1998|8.824314868964159|1846587
1999|8.611794331254078|2042389
2000|10.97282703872853|2212919
2001|4.870520908364137|252809
2002|4.748325870420807|2023619
2003|4.834658035026151|3394056
2004|7.546445217911288|4531841
2005|8.656478326447559|5105248
2006|10.20575117989498|5516804
2007|11.35341563943679|6513778
2008|9.93137411410353|6199619

or as @zachariam suggested, you can use just ssh on the local machine and run omnisql in the server.

local> ssh mapd@192.168.1.11  'echo "select flight_year,avg(depdelay) as avg_delay,count(*) as nof from flights where plane_type is not null group by 1 order by 1;" | /opt/mapd/omnisci-os-5.4.0-20200908-7002f5efb7-Linux-x86_64/bin/omnisql -q -n -p HyperInteractive' >local_file.txt                                                                                                                                                          
local> cat local_file.txt
1995|8.071271978882377|1665427
1996|9.998662298937367|1705829
1997|8.119505526551631|1770314
1998|8.824314868964159|1846587
1999|8.611794331254078|2042389
2000|10.97282703872853|2212919
2001|4.870520908364137|252809
2002|4.748325870420807|2023619
2003|4.834658035026151|3394056
2004|7.546445217911288|4531841
2005|8.656478326447559|5105248
2006|10.20575117989498|5516804
2007|11.35341563943679|6513778
2008|9.93137411410353|6199619

The only drawback of this approach is that is going to take more time,because of the ssh connect

1 Like