0

To stage and query the files the documentation shows this example query: SELECT t.$1, t.$2 FROM @mystage1 (file_format => myformat) t;

To simplify string manipulation I'd like to configure Snowflake to use anything but dollar sign as the column reference. For example, how about c instead of $?

SELECT t.c1, t.c2 FROM @mystage1 (file_format => myformat) t;

This is the link to the documentation: https://docs.snowflake.com/en/user-guide/querying-stage.html

Currently I'm forced by an application to use PowerShell to code the query to be processed by the SnowSql CLI). Making PowerShell emit this string is trivial, but then SnowSql adds its two cents and History shows the statement is processed as SELECT t., t. FROM @mystage1 (file_format => myformat) t;

jhowa1
  • 122
  • 1
  • 5

1 Answers1

0

PowerShell follows a quoting rule similar to Bash, where $VARIABLE is always resolved when found within double-quoted strings, and never resolved within single-quoted strings.

The syntax for stage querying requires use of the $ sign which cannot be substituted for another string.

If you are unable to use a file to pass queries to SnowSQL (via its -f [FILE] option) as a way of stepping around the shell parsing, and have to use the -q [TEXT] option, consider escaping the $ symbols, either by use of the unresolving quote marks (single quote character: ') or using the PowerShell escape character (backtick character: `):

snowsql -q 'SELECT t.$1, t.$2 FROM @mystage1 (file_format => myformat) t;'

snowsql -q "SELECT t.`$1, t.`$2 FROM @mystage1 (file_format => myformat) t;"
  • Thank you. This query: "SELECT t.`$1, t.`$2 FROM @mystage1 (file_format => myformat) t;" works outside of ODBC. – jhowa1 Jun 13 '20 at 01:59
  • My dog went after the cat and couldn't finish my edit. This query: "SELECT t.`$1, t.`$2 FROM @mystage1 (file_format => myformat) t;" was the first thing tried and escaping did not work when submitted via -q. I will try the -f option which I expect to work. Shortly after submitting this question, it occurred to me that this do the trick as well: SELECT t."$1", t."$2" FROM @mystage1 (file_format => myformat) t; As an aside, there are alternative ways to pass the file_format, in this example the format file employed as a table operator and requires addition of the gt symbol, thus. => – jhowa1 Jun 13 '20 at 02:10