Is there a way to set query parameters (variables) in separate files?

Question

I have a number of SQL queries that use a common set of parameters (variables). Currently, parameters are set at the top of each file. When any parameter changes, it has to be changed in every file. It would be beneficial to be able to have the parameters in a separate file and changed in only one place.

How can this be accomplished?

I realize that I can use the --var option to impala-shell, but this means that these have to be entered multiple times.

I can see several ways that this might happen:

impala-shell might support multiple -f arguments: This would be very elegant, but it doesn't.
The queries can be cat'd together and piped into impala-shell. This is serviceable but inelegant.
An alias can be set for impala-shell that specified --var arguments. This is potentially hard to get right.

Clearly #1 would be the best solution, but is there any other options or advice.

Not quite related: Multiple query execution in cloudera impala

score 1 · Accepted Answer · answered Oct 12 '18 at 07:29

Please check Impala Documentation: https://www.cloudera.com/documentation/enterprise/5-15-x/topics/impala_shell_options.html#shell_options

Pasting here the relevant part:

-f query_file or --query_file=query_file

Passes a SQL query from a file. Multiple statements must be semicolon (;) delimited. In CDH 5.5 / Impala 2.3 and higher, you can specify a filename of - to represent standard input. This feature makes it convenient to use impala-shell as part of a Unix pipeline where SQL statements are generated dynamically by other tools.

As you can see, Impala can parse more query files semicolon (;) delimited. This way, and with the --var argument you can accomplish your 1st case.

Not quite what I was hoping for but this seems like the best solution and satisfies option #2 (serviceable but inelegant). — ctbrown, Oct 31 '18 at 18:24

score 0 · Answer 2 · answered Oct 02 '18 at 08:53

3a. Generate automatically the --var command-line args from a config file.

Try this bash function...

function impala-with-conf {
  if [[ -z "$1" || ! -r "$1" ]] ; then
    echo "1st argument must be a properties file" >&2
    return 1
  fi
  typeset AllVarArgs=''
  while IFS='' read -r Line || [[ -n "$Line" ]]; do
    KeyValue=$(echo "$Line" | sed -e 's/^[ \t]*//' -e 's/[ \t]*=[ \t]*/=/' -e 's/[ \t]*$//')
    AllVarArgs="$AllVarArgs --var $KeyValue" 
  done < "$1"
  shift
  impala-shell $AllVarArgs $@
}

(based on Read a file line by line assigning the value to a variable )

Will work with a "properties" files such as

alpha_beta = some-text-without-spaces-or-quotes
epsilon=12

Demo:

cat x.sql
select '${VAR:alpha_beta}' as ab , ${VAR:epsilon} as e

impala-with-conf x.conf -q x.sql
Query: select 'some-text-without-spaces-or-quotes' as ab , 12 as e
+------------------------------------+----+
| ab | e |
+------------------------------------+----+
| some-text-without-spaces-or-quotes | 12 |
+------------------------------------+----+

Is there a way to set query parameters (variables) in separate files?

2 Answers2