Note: This question is a part of series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You develop a data ingestion process that will import data to an enterprise data warehouse in Azure Synapse Analytics. The data to be ingested resides in parquet files stored in an Azure Data Lake Gen 2 storage account.
You need to load the data from the Azure Data Lake Gen 2 storage account into the Data Warehouse.
Solution:
1. Create an external data source pointing to the Azure Data Lake Gen 2 storage account
2. Create an external file format and external table using the external data source
3. Load the data using the CREATE TABLE AS SELECT statement
Does the solution meet the goal?
Click on the arrows to vote for the correct answer
A. B.A
You need to create an external file format and external table using the external data source.
You load the data using the CREATE TABLE AS SELECT statement.
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-load-from-azure-data-lake-storeYes, the solution meets the goal of loading data from Azure Data Lake Gen 2 storage account into Azure Synapse Analytics data warehouse.
Explanation:
The solution suggests the following steps to load data from Azure Data Lake Gen 2 storage account into Azure Synapse Analytics data warehouse:
Create an external data source pointing to the Azure Data Lake Gen 2 storage account: This step involves creating an external data source using PolyBase to connect to the Azure Data Lake Gen 2 storage account. PolyBase is an integrated feature in Azure Synapse Analytics that allows you to query and access external data sources, such as Azure Data Lake Gen 2 storage account. By creating an external data source, you can access and read data stored in Azure Data Lake Gen 2 storage account from Azure Synapse Analytics.
Create an external file format and external table using the external data source: After creating an external data source, you need to define an external file format that matches the format of the data stored in the Azure Data Lake Gen 2 storage account. This step involves creating a metadata definition of the format of the data, such as column delimiters, row delimiters, and data type. Once the external file format is defined, you can create an external table that references the external data source and external file format. The external table acts as a virtual table that represents the data stored in the Azure Data Lake Gen 2 storage account.
Load the data using the CREATE TABLE AS SELECT statement: Finally, you can use the CREATE TABLE AS SELECT statement to load the data from the external table into a table in the Azure Synapse Analytics data warehouse. This statement creates a new table in the data warehouse and inserts data from the external table into it. The data can be transformed during the load process using SQL queries.
In summary, the solution meets the goal by using PolyBase to create an external data source, defining an external file format and external table, and loading the data using the CREATE TABLE AS SELECT statement. This approach allows you to import data from Azure Data Lake Gen 2 storage account into Azure Synapse Analytics data warehouse efficiently.