AWS CLI运行SQL查询 [英] AWS CLI to run SQL query

问题描述

我有以下用Python编写的代码.我希望将其迁移到Bash，或仅使用普通的旧AWS CLI.任务是使用S3 Select在S3存储桶上运行SQL查询.注意:S3中的文件均已压缩.

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: the files in S3 are all gziped.

现有的Python代码(有效)

ACCESS_KEY = 'Key1'
SECRET_KEY = 'Key2'
s3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY)

r = s3.select_object_content(
    Bucket='bkt1',
    Key=file2search,
    ExpressionType='SQL',
    Expression="SELECT * FROM s3object s where Lower(s._1) = (%r)" % ("SEARCH_STRING"),
    InputSerialization = {'CompressionType': 'GZIP', 'CSV': {
        'AllowQuotedRecordDelimiter': True,
        'QuoteEscapeCharacter': '"',
        'RecordDelimiter': '\n',
        'FieldDelimiter': ':',
        }},
    OutputSerialization = {'CSV': {
            'QuoteEscapeCharacter': '"',
            'RecordDelimiter': '\n',
            'FieldDelimiter': ':',
        }}
)

现金代码(无效)

SEARCH_STRING="[e-mail]"
aws s3api select-object-content \
    --bucket projectbucket2 \
    --key abc.gz \
    --expression "SELECT * FROM s3object s where Lower(s._1) = \'$SEARCH_STRING\'" \
    --expression-type 'SQL' \
    --input-serialization '{"CSV": {}, "CompressionType": "GZIP"}' \
    --output-serialization '{"CSV": {}}' "output.csv"

代码抛出错误:An error occurred (LexerInvalidChar) when calling the SelectObjectContent operation: Invalid character at line 1, column 46. 请帮忙.

The code throws an error: An error occurred (LexerInvalidChar) when calling the SelectObjectContent operation: Invalid character at line 1, column 46. Do pls help.

推荐答案

在Ubuntu上对我来说效果很好:

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

我正在使用AWS CLI v2.

I was using AWS CLI v2.

这篇关于AWS CLI运行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！