HPL/SQL Command Line

You can run hplsql tool from a command line to execute a SQL statement or HPL/SQL script.

Syntax:

hplsql -e 'query' | -f file 
      [-main procname]
      [-d | --define | -hiveconf | -hivevar var=value ...] 
      [-version | --version]
      [-trace | --trace]
      [-H | --help]

Parameters:

Parameter		Description	HPL/SQL Version
-e 'query'		SQL statements to execute	0.1
-f file		Execute SQL statements from file	0.1
-main procname		Entry point (procedure or function name)	0.3.7
-d var=value		Variable definition	0.1
--define var=value			0.1
-hiveconf var=value			0.1
-hivevar var=value			0.1
-version	--version	Print HPL/SQL version	0.1
-trace	--trace	Print trace information	0.1
-H	--help	Print help information and exit	0.1

Notes:

-e and -f cannot be specified together
if -main option is not specified, HPL/SQL starts executing all statements from the beginning of the script
You can use single 'query' and double “query” quotes with -e option
Currently -d, --define, -hivevar and -hiveconf are equivalent and allow you to define input variables.

Example 1:

Executing HPL/SQL statements from a script:

hplsql -f script.sql

Example 2:

Executing HPL/SQL statements from command line:

hplsql -e "NVL(MAX_PARTITION_DATE(db.sales, local_dt, code='A'), CURRENT_DATE)"

Example 3:

Using variables:

hplsql -e "PRINT a || ', ' || b" -d a=Hello -d b=world

Result:

Hello, world