Conversion of SQL Text File backup into Parquet File

[LB-Tech]

Hi,
I have backups stored in my local drive as SQL Text File format, I wanted this to be conveted into parquet file and this should be an automated task without involvement of SSMS. 
How can this task be automated?

[v-jingzhan-msft]

Hi @LB-Tech 

What is in the SQL Text File? The SQL statements for querying desired data? Or the query result data? 

You could consider using Pandas library in Python script.  

• If the SQL Text File contains the SQL statements, read the statement from the file first. Then use pandas.read_sql_query to execute the SQL query and return the result set into a DataFrame.
• If the SQL Text File contains the query result data, you can use pandas.read_csv to read the data into a dataframe directly. 
• Then use pandas.DataFrame.to_parquet to write the dataframe as a parquet file. 

Reference: hdfs - Python: save pandas data frame to parquet file - Stack Overflow

Please note that you need to install the necessary libraries in your system. 

To automate the task, you can use a task scheduler to run this script at regular intervals. For example, on Windows, you can use Task Scheduler, and on Linux, you can use cron jobs. For Fabric notebook, you can schedule its runs. 

Hope this would be helpful.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[LB-Tech]

The file contains the 
create statement to create the tables 
and the data to be fed from the software
it is actually a backup file which i took from a software and it has 200 to 300 tables wherein we want only 5 tables and in the selected table the maximum number of rows are 3 plus lakhs.

[v-jingzhan-msft]

Hi @LB-Tech 

You can use a combination of Python and Apache Spark. My idea is as follows:

1. Read the file to get table schemas: Extract the Create Table statements for the specific tables you need. Obtain the necessary meta data like table names, column names, and column types to prepare the schema for each DataFrame to be created.
2. Read the data: Transform and load the relevant table data from the backup file into separate DataFrames, using the schemas defined in the first step.
3. Save as Parquet: Save each DataFrame as a separate Parquet file.
4. Automate the progress by using Tools like Task Scheduler to run your python scripts. 

Handling a table with over 300,000 rows might be challenging for Pandas, especially if you have limited memory. Given your requirements, you may consider using PySpark to handle the data extraction and conversion to Parquet files. This approach will ensure better performance and scalability for your task.

I'm not sure what format the data in your backup files is, so it's difficult to provide specific code examples. You can try asking Copilot or ChatGPT to help you organize some sample code.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!