Aws Cli To Run Sql Query

I have the following code written in Python. I wish to migrate it over to Bash, or use just plain old AWS CLI. Mission is to run a SQL query on a S3 bucket, using S3 Select. Note:

Solution 1:

It worked perfectly fine for me on Ubuntu:

ubuntu@ip-172-31-8-201:~$ aws --version
aws-cli/2.0.38 Python/3.7.3 Linux/5.3.0-1023-aws exe/x86_64.ubuntu.18

ubuntu@ip-172-31-8-201:~$ echo $SEARCH_STRING
taipei 101

ubuntu@ip-172-31-8-201:~$ aws s3api select-object-content --bucket my-bucket --key towers.csv --expression "SELECT * FROM s3object s where Lower(s._2) = '$SEARCH_STRING'" --expression-type 'SQL' --input-serialization '{"CSV": {}, "CompressionType": "NONE"}'     --output-serialization '{"CSV": {}}'"output.csv"

ubuntu@ip-172-31-8-201:~$ cat output.csv 
5,Taipei 101,Taipei,Taiwan,509,1670,101,2004

I was using AWS CLI v2.

Solution 2:

You are over-using the single-quote (') character.

For example: --input-serialization '{'CompressionType': The first quote opens your JSON, but then the quote immediately after the curly bracket closes the quote.

Instead, try experimenting with alternating use of the single and double-quote. For example:

--input-serialization '--input-serialization '{"CompressionType": "GZIP", "CSV": {

This allows the single-quote to contain the entire JSON, while the double-quote is used inside the JSON.

The result would look something like this (I didn't test it):

aws s3api select-object-content \
    --bucket bkt1 \
    --key filename.gz \
    --expression "SELECT * FROM s3object s where Lower(s._1) = '$SEARCH_STRING'" \
    --expression-type 'SQL' \
    --input-serialization '{"CompressionType": "GZIP", "CSV": {
            "AllowQuotedRecordDelimiter": True,
            "QuoteEscapeCharacter": "\"",
            "RecordDelimiter": "\n",
            "FieldDelimiter": ":",
            }}' \
    --output-serialization '{"CSV": {
                "QuoteEscapeCharacter": "\"",
                "RecordDelimiter": "\n",
                "FieldDelimiter": ":",
            }}'