正在运行包含来自aws athena cli的伪列的查询 [英] 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?

解决方案

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?

这篇关于正在运行包含来自aws athena cli的伪列的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！