Running query containing pseudo column from aws athena cli

Question

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?

Answer 1

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.

See:

How to escape single quotes within single quoted strings?