AWS SDK for pandas

29 - S3 Select

AWS SDK for pandas supports Amazon S3 Select, enabling applications to use SQL statements in order to query and filter the contents of a single S3 object. It works on objects stored in CSV, JSON or Apache Parquet, including compressed and large files of several TBs.

With S3 Select, the query workload is delegated to Amazon S3, leading to lower latency and cost, and to higher performance (up to 400% improvement). This is in comparison with other awswrangler operations such as read_parquet where the S3 object is downloaded and filtered on the client-side.

This feature has a number of limitations however:

  • The maximum length of a record in the input or result is 1 MB

  • The maximum uncompressed row group size is 256 MB (Parquet only)

  • It can only emit nested data in JSON format

  • Certain SQL operations are not supported (e.g. ORDER BY)

Read multiple Parquet files from an S3 prefix

[1]:

import awswrangler as wr

df = wr.s3.select_query(
    sql="SELECT * FROM s3object s where s.\"star_rating\" >= 5",
    path="s3://amazon-reviews-pds/parquet/product_category=Gift_Card/",
    input_serialization="Parquet",
    input_serialization_params={},
)
df.loc[:, df.columns != "product_title"].head()

[1]:
marketplace customer_id review_id product_id product_parent star_rating helpful_votes total_votes vine verified_purchase review_headline review_body review_date year
0 US 52670295 RGPOFKORD8RTU B0002CZPPG 867256265 5 105 107 N N Excellent Gift Idea I wonder if the other reviewer actually read t... 2005-02-08 2005
1 US 29964102 R2U8X8V5KPB4J3 B00H5BMF00 373287760 5 0 0 N Y Five Stars convenience is the name of the game. 2015-05-03 2015
2 US 25173351 R15XV3LXUMLTXL B00PG40CO4 137115061 5 0 0 N Y Birthday Gift This gift card was handled with accuracy in de... 2015-05-03 2015
3 US 12516181 R3G6G7H8TX4H0T B0002CZPPG 867256265 5 6 6 N N Love 'em. Gotta love these iTunes Prepaid Card thingys. ... 2005-10-15 2005
4 US 38355314 R2NJ7WNBU16YTQ B00B2TFSO6 89375983 5 0 0 N Y Five Stars perfect 2015-05-03 2015

Read full CSV file

[5]:

df = wr.s3.select_query(
    sql="SELECT * FROM s3object",
    path="s3://humor-detection-pds/Humorous.csv",
    input_serialization="CSV",
    input_serialization_params={
        "FileHeaderInfo": "Use",
        "RecordDelimiter": "\r\n",
    },
    scan_range_chunk_size=1024*1024*32,  # override range of bytes to query, by default 1Mb
    use_threads=True,
)
df.head()

[5]:
question product_description image_url label
0 Will the volca sample get me a girlfriend? Korg Amplifier Part VOLCASAMPLE http://ecx.images-amazon.com/images/I/81I1XZea... 1
1 Can u communicate with spirits even on Saturday? Winning Moves Games Classic Ouija http://ecx.images-amazon.com/images/I/81kcYEG5... 1
2 I won't get hunted right? Winning Moves Games Classic Ouija http://ecx.images-amazon.com/images/I/81kcYEG5... 1
3 I have a few questions.. Can you get possessed... Winning Moves Games Classic Ouija http://ecx.images-amazon.com/images/I/81kcYEG5... 1
4 Has anyone asked where the treasure is? What w... Winning Moves Games Classic Ouija http://ecx.images-amazon.com/images/I/81kcYEG5... 1

Filter JSON file

[3]:

wr.s3.select_query(
    sql="SELECT * FROM s3object[*] s where s.\"family_name\" = \'Biden\'",
    path="s3://awsglue-datasets/examples/us-legislators/all/persons.json",
    input_serialization="JSON",
    input_serialization_params={
        "Type": "Document",
    },
)

[3]:
family_name contact_details name links gender image identifiers other_names sort_name images given_name birth_date id
0 Biden [{'type': 'twitter', 'value': 'joebiden'}] Joseph Biden, Jr. [{'note': 'Wikipedia (ace)', 'url': 'https://a... male https://theunitedstates.io/images/congress/ori... [{'identifier': 'B000444', 'scheme': 'bioguide... [{'lang': None, 'name': 'Joe Biden', 'note': '... Biden, Joseph [{'url': 'https://theunitedstates.io/images/co... Joseph 1942-11-20 64239edf-8e06-4d2d-acc0-33d96bc79774