Convert file from csv to parquet on S3 with aws boto

user_6285832 0
Viewed 1949

I wrote a script that would execute a query on Athena and load the result file in a specified aws boto S3 location.

import boto3
def run_query(query, database, s3_output):
    client = boto3.client('athena', region_name='my-region')
    response = client.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': database
            },
        ResultConfiguration={
            'OutputLocation': s3_output,
            }
        )
    print('Execution ID: ' + response['QueryExecutionId'])
    return response

query = """select ..."""

database = 'db_name'

path_template = 's3://bucket_name/path/version={}'

current_time =  str(datetime.datetime.now())

result = run_query(query, database, path_template.format(current_time))

It does work but the problem is that I have a csv file as the specified location. But I don't want a csv file I want a parquet file.

The only way I manage to obtain what I want is to download the file convert it with panda to parquet to reupload it. It's annoying that I can't just convert directly without fetching the file.

Anyone has another way to suggest ? I don't want to use a CTAS.

boto3 amazon-web-services python
2 Answers

You need to use a CTAS:

CREATE TABLE db.table_name
WITH (
    external_location = 's3://yourbucket/path/table_name',
    format = 'PARQUET',
    parquet_compression = 'GZIP',
    partitioned_by = ARRAY['dt']
)
AS
SELECT
    ...
;

This way the result of the select will be saved as Parquet.

https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html

https://docs.aws.amazon.com/athena/latest/ug/ctas.html

Use CTAS queries to:

  • Create tables from query results in one step, without repeatedly querying raw data sets. This makes it easier to work with raw data sets.
  • Transform query results into other storage formats, such as Parquet and ORC. This improves query performance and reduces query costs in Athena. For information, see Columnar Storage Formats.
  • Create copies of existing tables that contain only the data you need.

UPDATE (2019.10):

AWS just released INSERT INTO for Athena.

https://docs.aws.amazon.com/en_pv/athena/latest/ug/insert-into.html

Inserts new rows into a destination table based on a SELECT query statement that runs on a source table, or based on a set of VALUES provided as part of the statement. When the source table is based on underlying data in one format, such as CSV or JSON, and the destination table is based on another format, such as Parquet or ORC, you can use INSERT INTO queries to transform selected data into the destination table's format.

There are some limitations:

  • INSERT INTO is not supported on bucketed tables. For more information, see Bucketing vs Partitioning.
  • When running an INSERT query on a table with underlying data that is encrypted in Amazon S3, the output files that the INSERT query writes are not encrypted by default. We recommend that you encrypt INSERT query results if you are inserting into tables with encrypted data. For more information about encrypting query results using the console, see Encrypting Query Results Stored in Amazon S3. To enable encryption using the AWS CLI or Athena API, use the EncryptionConfiguration properties of the StartQueryExecution action to specify Amazon S3 encryption options according to your requirements.
user_1275080
user_62858320
Related