Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-external-table-as-select below:

Store query results from serverless SQL pool - Azure Synapse Analytics

In this article, you'll learn how to store query results to storage using serverless SQL pool.

Your first step is to create a database where you'll execute the queries. Then initialize the objects by executing setup script on that database. This setup script will create the data sources, database scoped credentials, and external file formats that are used to read data in these samples.

Follow the instructions in this article to create data sources, database scoped credentials, and external file formats that are used to write data into the output storage.

You can use the CREATE EXTERNAL TABLE AS SELECT (CETAS) statement to store the query results to storage.

Note

Change these values in the query to reflect your environment:

• mydbname - change it to the name of the database you created
• storage-account-sas - the shared access signature for a storage account where you want to write your results
• your-storage-account-name - the name of your storage account where you want to write your results (Make sure you have a container called 'csv' or that you change the name of the container here also)

USE [mydbname];
GO

CREATE DATABASE SCOPED CREDENTIAL [SasTokenWrite]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 'storage-account-sas';
GO

CREATE EXTERNAL DATA SOURCE [MyDataSource] WITH (
    LOCATION = 'https://your-storage-account-name.blob.core.windows.net/csv', CREDENTIAL = [SasTokenWrite]
);
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL TABLE [dbo].[PopulationCETAS] WITH (
        LOCATION = 'populationParquet/',
        DATA_SOURCE = [MyDataSource],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT
    *
FROM
    OPENROWSET(
        BULK 'csv/population-unix/population.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0'
    ) WITH (
        CountryCode varchar(4),
        CountryName varchar(64),
        Year int,
        PopulationCount int
    ) AS r;

Note

You must modify this script and change the target location to execute it again. External tables can't be created on the location where you already have some data.

You can use the external table created through CETAS like a regular external table.

Note

Change the first line in the query, that is, [mydbname], so you're using the database you created.

USE [mydbname];
GO

SELECT
    CountryName, PopulationCount
FROM PopulationCETAS
WHERE
    [Year] = 2019
ORDER BY
    [PopulationCount] DESC;

Once you store your results, the data in the external table can't be modified. You can't repeat this script because CETAS won't overwrite the underlying data created in the previous execution.

The only supported output types are currently Parquet and CSV.

For more information on how to query different file types, see the Query single CSV file, Query Parquet files, and Query JSON files articles.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4