Count reoccurring variable within AWS-S3 bucket using S3-Select query

Question

I'm running a Python script to query an AWS-S3 bucket using the AWS-S3-Select tool. I'm importing a variable from a txt file and want to pass it into the S3-Select query. I also want to count all imported variable recurrences (within a specified column) by querying the entire S3 directory instead of just a single file.

This is what I have so far:

import boto3
from boto3.session import Session

with open('txtfile.txt', 'r') as myfile:
    variable = myfile.read()

ACCESS_KEY='accessKey'
SECRET_KEY='secredtKey'

session = Session(aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY)
s3b = session.client('s3')

r = s3b.select_object_content(
    Bucket='s3BucketName',
    Key='directory/fileName',
    ExpressionType='SQL',
    Expression="'select count(*)from S3Object s where s.columnName = %s;', [variable]",
    InputSerialization={'CSV': {"FileHeaderInfo": "Use"}},
    OutputSerialization={'CSV': {}},
)

for event in r['Payload']:
    if 'Records' in event:
        records = event['Records']['Payload'].decode('utf-8')
        print(records)
    elif 'Stats' in event:
        statsDetails = event['Stats']['Details']
        print("Stats details bytesScanned: ")

When I run this script I get back the following error:

Traceback (most recent call last):
  File "s3_query.py", line 20, in <module>
    OutputSerialization={'CSV': {}},
  File "/root/anaconda3/lib/python3.6/site-packages/botocore/client.py", line 314, in _api_call
    return self._make_api_call(operation_name, kwargs)
  File "/root/anaconda3/lib/python3.6/site-packages/botocore/client.py", line 612, in _make_api_call
    raise error_class(parsed_response, operation_name)
botocore.exceptions.ClientError: An error occurred (ParseUnexpectedToken) when     calling the SelectObjectContent operation: Unexpected token found COMMA:',' at line     1, column 67.

score 0 · Answer 1 · answered Aug 10 '18 at 02:23

This line looks quite strange:

Expression="'select count(*)from S3Object s where s.columnName = %s;', [variable]"

That is not normal SQL or Python syntax.

You should probably use:

Expression='select count(*)from S3Object s where s.columnName = %s;' % [variable]

Count reoccurring variable within AWS-S3 bucket using S3-Select query

1 Answers1