Skip to main content

Data Extraction methods in Azure Synapse Analytics


Data extraction is an essential step in the data analysis process, and Azure Synapse Analytics provides a variety of methods to extract data from different sources. In this blog post, we will explore the different data extraction methods available in Azure Synapse Analytics and provide example codes to demonstrate how to use them.

Copy Data Transformation: Copy Data transformation is the most basic and commonly used method for extracting data in Azure Synapse Analytics. It allows you to copy data from a source to a sink, such as an Azure Data Lake Storage or an Azure SQL Database. The "Copy Data" transformation also allows for filtering and sorting of the data, as well as mapping columns to different names.

Example code:

{
    "name": "CopyData1",
    "properties": {
        "type": "Copy",
        "source": {
            "type": "SqlSource",
            "sqlReaderQuery": "SELECT * FROM mytable"
        },
        "sink": {
            "type": "AzureDataLakeStorageSink",
            "folderPath": "myfolder/mydata"
        }
    }
}

In this example, the "sqlReaderQuery" parameter specifies the SQL query to be used to extract the data from the Azure SQL Database. The "folderPath" parameter specifies the location in the Azure Data Lake Storage where the data will be copied to.

Data Flow Source: The Data Flow Source is a method of extracting data directly from a data flow. This method is useful when you want to extract data from a data flow that has already been transformed and cleaned.

Example code:

{
    "name": "DataFlowSource1",
    "properties": {
        "type": "DataFlowSource",
        "dataFlow": {
            "referenceName": "MyDataFlow"
        }
    }
}

In this example, the "referenceName" parameter specifies the name of the data flows that the data that will be extracted from.

Stored Procedure: The Stored Procedure method allows you to extract data by executing a stored procedure in an Azure SQL Database or an Azure Synapse Analytics SQL pool. This method is useful when you want to extract data from a specific stored procedure or when you want to pass parameters to the stored procedure.

Example code:

{
    "name": "StoredProcedure1",
    "properties": {
        "type": "SqlSource",
        "storedProcedureName": "sp_getdata",
        "storedProcedureParameters": [
            {
                "name": "param1",
                "value": "value1"
            },
            {
                "name": "param2",
                "value": "value2"
            }
        ]
    }
}

In this example, the "storedProcedureName" parameter specifies the name of the stored procedure to be executed and the "storedProcedureParameters" parameter specifies the parameters to be passed to the stored procedure.

Lookup Transformation: Lookup Transformation allows you to extract data from a reference data source by looking up values in the reference data source. This method is useful when you want to extract data that is stored in a reference data source, such as a CSV file or an Azure SQL Database.

Example code:

{
    "name": "Lookup1",
    "properties": {
        "


Comments

Post a Comment

Popular posts from this blog

ACID? 🤔

In the world of data engineering and warehousing projects, the concept of ACID transactions is crucial to ensure data consistency and reliability. ACID transactions refer to a set of properties that guarantee database transactions are processed reliably and consistently. ACID stands for Atomicity , Consistency , Isolation , and Durability . Atomicity : This property ensures that a transaction is treated as a single, indivisible unit of work. Either the entire transaction completes successfully, or none of it does. If any part of the transaction fails, the entire transaction is rolled back, and the database is returned to its state before the transaction began. Consistency : This property ensures that the transaction leaves the database in a valid state. The database must enforce any constraints or rules set by the schema. For example, if a transaction tries to insert a record with a duplicate primary key, the database will reject the transaction and roll back any changes that have alre...
Post a Comment
Read more

CETAS in Synapse Analytics

In Azure Synapse Analytics, creating external tables can be a powerful way to work with large volumes of data in various file formats without loading it into the data warehouse. The CREATE EXTERNAL TABLE AS SELECT (CETAS) command is a useful feature in Synapse Analytics that allows you to create external tables directly from SQL SELECT statements. In this blog post, we will explore how to use CETAS with the OpenRowset function to create external tables in Synapse Analytics. What is CREATE EXTERNAL TABLE AS SELECT (CETAS)? The CETAS command in Azure Synapse Analytics is a powerful feature that enables you to create an external table from the results of a SQL SELECT statement. With CETAS, you can create an external table directly from the results of a query, which can be useful for creating ad-hoc reports, running data transformations, or performing other operations on data outside of the data warehouse. CETAS can be used to create external tables in various file formats, including Parqu...
Post a Comment
Read more

Data Transformation methods in Azure Synapse Analytics

Data transformation is a crucial step in the data processing pipeline and Azure Synapse provides several methods to perform data transformation tasks. In this blog post, we will discuss some of the most commonly used data transformation methods in Azure Synapse with code examples. Mapping Data Flow: Mapping Data Flow allows you to define data transformation tasks by creating a flow of data between source and destination datasets. You can use built-in transformation tasks such as filtering, aggregation, and joining data. Example: {     "name": "ExampleDataFlow",     "properties": {         "activities": [             {                 "name": "Source",                 "type": "Source",                 "policy": {                     "timeout": "7.00:00...
Post a Comment
Read more