CODEWITHSUNDEEP
bashamazon-web-servicescommand-line-interfaceamazon-athena
user872858
user872858

Reputation: 780

Running query containing pseudo column from aws athena cli

With reference to the below post,
How to get input file name as column in AWS Athena external tables

I tried running the query using the aws athena cli command as below, 

aws athena start-query-execution --query-string "SELECT regexp_extract(\
"$path\", '[^/]+$') AS filename  FROM table" --query-execution-context '{"Database": "testdatabase"}' --result-configuration '{ "OutputLocation": "s3://<somevalidbucket>"}'

I always get the query executed with empty value for $path. e.g., "SELECT regexp_extract('', '[^/]+$') AS filename ...." and filename appears as empty in all the returned rows.

Note: I replaced double quotes with single quotes around $path and the result did not change.

Same query works fine on AWS console though. What am I missing here?

Upvotes: 1

Views: 2206

Answers (1)

Zerodf
Zerodf

Reputation: 2298

If you are running a Bash terminal and you can enclose your query string in single quotes and escape the single quotes using '"'"':

If you enclose your query string in single quotes:

$ aws athena start-query-execution --query-string 'SELECT regexp_extract("$path", '"'"'[^/]+$'"'"') FROM athena_test.some_table LIMIT 10' --query-execution-context '{"Database": "athena_test"}' --result-configuration '{ "OutputLocation": "s3://<SOMEBUCKET>"}'

If you enclose your query string in double quotes:

$ aws athena start-query-execution --query-string "SELECT regexp_extract(\"\$path\", '[^/]+$') FROM athena_test.some_table LIMIT 10" --query-execution-context '{"Database": "athena_test"}' --result-configuration '{ "OutputLocation": "s3://<SOMEBUCKET>"}'

A helpful trick is to look at the history in the Athena Web GUI, form their you can see exactly what was passed from the command line to Athena.

enter image description here

See:

How to escape single quotes within single quoted strings?

Upvotes: 2

Related Questions