running commands from a file

["Kratkiewicz, Kendra - 0552 - MITLL" <ke...@ll.mit.edu>]

Hi,

I've been trying to figure out how to run a series of commands/queries from a file, and I have not had any luck finding the right way to specify the file path. I'm running embedded on Windows.

I've tried both the "!run" command from within the Drill shell, and the sqllline method of specifying a file to run with the "-run" option.

In all cases, no matter how I specify the file path, I get "file not found" errors.

What is expected for a file path?
What it is relative to, or how do you specify the absolute path?

To make the question specific, suppose I have a directory c:\tmp\queries which contains my query.sql file that I'd like to run.
From the Drill shell command line, I want to execute the "!run" command and supply the path to this query.sql file. How should this argument be formatted?

Alternatively, if I want to start up Drill, execute the query.sql file, and then exit by using the sqlline method (./sqlline -u 'jdbc:drill:drillbit=local' -run=/path/to/file), how exactly should that look in order to execute my query.sql file?

I've tried every variation of paths I can think of and nothing works.

Thanks for your help,

-Kendra

Re: running commands from a file

["Rumar, Maksym" <ma...@hpe.com>]

Hi Kendra,

I was able to run SQL script with the following command:
sqlline -u "jdbc:drill:drillbit=localhost:31010" -n user -p password --run=query.sql
Where:
​-u     - the JDBC URL to connect to
-n     - username (to pass plain authentication if it is enabled)
-p     - password (to pass plain authentication if it is enabled)
--run  - to run a script and then exit. There is a need to specify a path to the file. It doesn't matter will be it relative path or absolute.
My query.sql file is pretty simple and contains only 2 queries (just for example) and nothing more:
show databases;
SELECT last_name FROM cp.`employee.json` limit 5;
And here is my output (query.sql lays in /home/maksym/):
[maksym@home-pc ~]$ sqlline -u "jdbc:drill:drillbit=localhost:31010" -n maksym -p password --run=query.sql
Enabling TCP JMX for drill on port 6090
1/2          show databases;
+--------------------+
|    SCHEMA_NAME     |
+--------------------+
| cp.default         |
| dfs.default        |
| dfs.root           |
| dfs.tmp            |
| information_schema |
| sys                |
+--------------------+
6 rows selected (0.255 seconds)
2/2          SELECT last_name FROM cp.`employee.json` limit 5;
+-----------+
| last_name |
+-----------+
| Nowmer    |
| Whelply   |
| Spence    |
| Gutierrez |
| Damstra   |
+-----------+
5 rows selected (0.153 seconds)
Closing: org.apache.drill.jdbc.impl.DrillConnectionImpl
Apache Drill 1.16.0
"Your Drill is the Drill that will pierce the heavens."
​
Besides this, I've been also able to run the script with predefined properties in a file cred.properties:
url=jdbc:drill:drillbit=localhost:31010
user=maksym
password=password
​And result command looks like this (both files are located in the user home directory /home/maksym and the script is launched out of here):
sqlline cred.properties --run=query.sql
​
By the way, you can also enable verbose mode to make Sqlline show more logs:
--verbose=true
​
Try to use my example and correct it to suit your requirements. As I don't know how does look your script inside, so I am not able to say what went wrong in your case.

Regards,
Maksym

________________________________
Від: Kratkiewicz, Kendra - 0552 - MITLL <ke...@ll.mit.edu>
Надіслано: 12 листопада 2021 р. 7:43
Кому: user@drill.apache.org <us...@drill.apache.org>
Тема: running commands from a file

Hi,

I've been trying to figure out how to run a series of commands/queries from a file, and I have not had any luck finding the right way to specify the file path. I'm running embedded on Windows.

I've tried both the "!run" command from within the Drill shell, and the sqllline method of specifying a file to run with the "-run" option.

In all cases, no matter how I specify the file path, I get "file not found" errors.

What is expected for a file path?
What it is relative to, or how do you specify the absolute path?

To make the question specific, suppose I have a directory c:\tmp\queries which contains my query.sql file that I'd like to run.
From the Drill shell command line, I want to execute the "!run" command and supply the path to this query.sql file. How should this argument be formatted?

Alternatively, if I want to start up Drill, execute the query.sql file, and then exit by using the sqlline method (./sqlline -u 'jdbc:drill:drillbit=local' -run=/path/to/file), how exactly should that look in order to execute my query.sql file?

I've tried every variation of paths I can think of and nothing works.

Thanks for your help,

-Kendra