How to create a parquet file from a query to a mysql table

Question

How to create a parquet file from a query to a mysql table

Carlos P Ceballos

2021年1月20日 12:27

Updating a legacy ~ETL; on it's base it exports some tables of the prod DB to s3, the export contains a query. The export process generates a csv file using the following logic:

res = sh.sed(
    sh.mysql(
       '-u',
       settings_dict['USER'],
       '--password={0}'.format(settings_dict['PASSWORD']),
       '-D', settings_dict['NAME'],
       '-h', settings_dict['HOST'],
       '--port={0}'.format(settings_dict['PORT']),
       '--batch',
       '--quick',
       '--max_allowed_packet=512M',
       '-e', '{0}'.format(query)
    ),
    r's/"/\\"/g;s/\t/","/g;s/^/"/;s/$/"/;s/\n//g',
    _out=filename
)

the mid term solution with more traction is AWS Glue, but if I could have a similar function to generate parquet files instead of csv files there would be much needed big short term gains

Topic etl csv python

Category Data Science

Markus Klein · Accepted Answer · 2021年1月20日 12:27

I can think of a few ways besides using Apache Spark.

There is a python script doing just that: https://github.com/rondunn/odbc2parquet. It utilizes pyodbc though.
Depending on your use case you may find that https://github.com/blue-yonder/turbodbc would deliver a much speedier experience querying the data. turbodbc can also directly emit arrow arrays.
There is a command line tool written in Rust by yours truly. It is also called odbc2parquet. https://github.com/pacman82/odbc2parquet

Carlos P Ceballos · Accepted Answer · 2019年9月3日 00:55

1

Carlos P Ceballos answered at 2019年9月3日 00:55

it seams that there is no direct way to do it, other than through Spark / PySpark; as long as that holds true, the answer is in SO: https://stackoverflow.com/questions/27718382/how-to-work-with-mysql-and-apache-spark

How to create a parquet file from a query to a mysql table

About