Skip to main content

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 Parquet, ORC, CSV, and more.

What is OpenRowset?

OpenRowset is a T-SQL function that enables you to access data from external data sources, such as flat files, Excel spreadsheets, and more. OpenRowset allows you to read data from external sources directly into SQL Server or Synapse Analytics, making it a useful tool for working with data in a variety of formats.

How to use CETAS with OpenRowset?

To use CETAS with OpenRowset, you first need to define an external data source. An external data source is a reference to an external data store, such as Azure Blob Storage or Azure Data Lake Storage. You can create an external data source in Synapse Analytics using the CREATE EXTERNAL DATA SOURCE command.

Once you have defined an external data source, you can use the OpenRowset function to read data from external sources directly into SQL Server or Synapse Analytics. To do this, you need to specify the external data source and the file format in the OpenRowset function.

Next, you can use CETAS to create an external table from the results of a SQL SELECT statement. To do this, you need to specify the external table's schema, name, and file format. You can also specify additional properties, such as compression and partitioning.

Here is an example of how to use CETAS with OpenRowset to create an external table in Synapse Analytics:

CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://myblobstorage@myaccount.blob.core.windows.net/',
    CREDENTIAL = MyCredential
);

CREATE EXTERNAL TABLE MyExternalTable
WITH (
    LOCATION = '/myfolder/',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = MyFileFormat
)
AS
SELECT *
FROM OPENROWSET(
    BULK 'myfile.csv',
    FORMAT = 'CSV',
    DATA_SOURCE = MyDataSource,
    FORMAT_OPTIONS = (
        FIELD_TERMINATOR = ',',
        ROW_TERMINATOR = '\n',
        FIRST_ROW = 2
    )
) AS data;

In this example, we are creating an external data source for an Azure Blob Storage account and defining an external table with a CSV file format. We are then using the OPENROWSET function to read data from a CSV file stored in the Blob Storage account and create an external table from the results of a SQL SELECT statement.

Conclusion

The CETAS command in Synapse Analytics, when used with the OpenRowset function, is a powerful tool for working with data in various file formats. By creating external tables directly from SQL SELECT statements, you can perform ad-hoc reporting, data transformations, and other operations on data outside of the data warehouse. With the ability to read data from
Labels:

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

Data Wrangling in Azure

Data wrangling, also known as data cleaning or data preprocessing, is the process of transforming raw data into a format that is more suitable for analysis . This is an important step in any data-driven project, as it ensures that the data being analyzed is accurate, complete, and relevant to the problem at hand. Microsoft Azure provides a range of tools and services that can be used to perform data-wrangling tasks. In this blog post, we will provide an overview of what data wrangling is and how to do it in Azure. What is Data Wrangling? Data wrangling is the process of transforming raw data into a format that is more suitable for analysis. This involves several steps, including cleaning, transforming, and integrating data from various sources. Cleaning: This step involves removing any duplicate or irrelevant data, correcting any errors, and filling in missing values. Transforming: This step involves converting the data into a format that is  more suitable for analysis. ...
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