Example requested: how to escape `where`

[mooreniemi]

I'd like to be able to execute the following query with -e, without writing a sql file. What's the best way to do this? Because I need both " and ' I'm unsure how to escape in the context of -e 'my query string here'.

select *
from "AwsDataCatalog"."database"."table"
where "column" = 'value'

Naively executing this just returns a confusing error (it's mistaking my value for a column):

COLUMN_NOT_FOUND: line 1:190: Column 'value' cannot be resolved or requester is not authorized to access requested resources

[iainelder]

What is the complete command you execute? What is your operating system? What is your shell?

I'm going to assume Linux and Bash because that's what I use.

I would use a command like this:

athenacli -e /dev/stdin <<"EOF"
SELECT "table_name"
FROM "awsdatacatalog"."information_schema"."tables"
WHERE "table_schema" = 'information_schema';
EOF

It gives me a CSV result.

table_name
columns
tables
views
schemata
table_privileges
roles
applicable_roles
enabled_roles

The -e option takes a file or a string. On Linux the file /dev/stdin means the standard input. The <<"EOF" part marks the start of a here document and the second EOF marks the end. Bash writes everything between the markers to standard input and athenacli reads it.

The Bash manual explains here documents and the Linux Documentation Project gives many examples.

---

Why didn't your command work?

I'm going to assume your complete command looked like this:

athenacli -e '
SELECT "table_name"
FROM "awsdatacatalog"."information_schema"."tables"
WHERE "table_schema" = 'information_schema';
'

That gives the following error:

COLUMN_NOT_FOUND: line 3:24: Column 'information_schema' cannot be resolved or requester is not authorized to access requested resources

To see why, echo the argument to -e:

echo '
SELECT "table_name"
FROM "awsdatacatalog"."information_schema"."tables"
WHERE "table_schema" = 'information_schema';
'

Bash does not preserve the single quotes inside the the main single quotes.

SELECT "table_name"
FROM "awsdatacatalog"."information_schema"."tables"
WHERE "table_schema" = information_schema;

From Bash's point of view the "inside" single quotes are not inside, but they end a string. Then Bash appends the next string, quoted or not.

A simple example that shows the same effect:

echo 'a'b'c'

abc

[iainelder]

There's an even simpler solution if you don't need to quote any column names: use double quotes around your query.

athenacli -e "
SELECT table_name
FROM awsdatacatalog.information_schema.tables
WHERE table_schema = 'information_schema';
"