Python azure blob storage - append to csv file only if row doesn't already exist

ONLINECAST

3 min read

·

05-10-2024

4

0

Share

Appending to a CSV File in Azure Blob Storage: Ensuring Unique Rows with Python

Scenario: You're working with a Python application that needs to upload data to a CSV file stored in Azure Blob Storage. However, you need to ensure that each row in the CSV is unique, preventing duplicate data from being added.

The Challenge: Azure Blob Storage doesn't offer built-in mechanisms for checking if a row already exists within a CSV file. This means you'll need to implement a solution within your Python code.

Solution: This article will guide you through the process of appending data to a CSV file stored in Azure Blob Storage while guaranteeing each row's uniqueness. We'll utilize the Azure Storage SDK for Python and leverage techniques to efficiently manage your data.

Understanding the Approach

The core of our solution lies in checking if the new row already exists in the CSV file. To achieve this, we'll read the existing file, compare the new row with each existing row, and only append it if it's unique.

Code Example:

from azure.storage.blob import BlobServiceClient
import pandas as pd

# Azure Blob Storage connection details
connection_string = "<your_azure_storage_connection_string>"
container_name = "your_container_name"
blob_name = "your_csv_file.csv"

# Create a BlobServiceClient object
blob_service_client = BlobServiceClient.from_connection_string(connection_string)

# Function to append a row to the CSV file, ensuring uniqueness
def append_unique_row(df, new_row):
    """
    Appends a new row to the CSV file, only if the row doesn't already exist.

    Args:
        df (pandas.DataFrame): The existing data from the CSV file.
        new_row (list): The new row to be appended.

    Returns:
        pandas.DataFrame: The updated DataFrame with the appended row (if unique).
    """

    # Check if the new row already exists in the DataFrame
    if not any(df.apply(lambda row: all([x == y for x, y in zip(row, new_row)]), axis=1)):
        df = df.append(pd.DataFrame([new_row]), ignore_index=True)

    return df

# Retrieve the existing CSV data as a Pandas DataFrame
blob_client = blob_service_client.get_blob_client(container=container_name, blob=blob_name)
blob_data = blob_client.download_blob().readall()
df = pd.read_csv(io.BytesIO(blob_data))

# Define your new row
new_row = ["value1", "value2", "value3"]

# Append the row if unique
df = append_unique_row(df, new_row)

# Save the updated DataFrame back to the CSV file
csv_buffer = io.StringIO()
df.to_csv(csv_buffer, index=False)
csv_buffer.seek(0)
blob_client.upload_blob(csv_buffer.read(), overwrite=True)

print("Data appended to CSV file successfully!")

Explanation:

1. Import Necessary Libraries: Import the Azure Storage SDK, pandas for data manipulation, and io for buffer management.
2. Connect to Azure Blob Storage: Provide your Azure Blob Storage connection string and specify the container and blob name.
3. append_unique_row Function: This function checks if the new_row already exists in the df (existing data). It uses pandas.DataFrame.apply to iterate over each row in the df and compare it element-wise with the new_row. If no match is found, it appends the new_row to the df.
4. Retrieve Existing CSV Data: Download the existing CSV file from the blob storage and convert it into a pandas DataFrame.
5. Define New Row: Define the new row that you want to append.
6. Append Row (if Unique): Call the append_unique_row function to append the new_row to the df if it's unique.
7. Save Updated DataFrame: Convert the updated DataFrame back into a CSV string, upload it to the blob storage, and overwrite the existing file.

Important Considerations:

• Data Type Consistency: Ensure that the data types in the new row match the existing data types in the CSV file.
• Performance: For large CSV files, this method could be computationally expensive. Consider optimizing your code for efficiency.
• Alternative Approaches: For extremely large datasets, consider using database solutions like Azure SQL Database or Azure Cosmos DB, which offer better performance and built-in features for managing unique data.

Additional Resources:

• Azure Storage SDK for Python: https://pypi.org/project/azure-storage-blob/
• Azure Blob Storage Documentation: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blobs-introduction
• pandas Documentation: https://pandas.pydata.org/docs/

By following this approach and considering these points, you can efficiently append data to a CSV file in Azure Blob Storage while maintaining data integrity and ensuring unique rows.