CREATE EXTERNAL TABLE AS SELECT (CETAS) (CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

Dotyczy: SQL Server 2022 (16.x) i nowszych Azure Synapse AnalyticsAnalytics Platform System (PDW)

Tworzy tabelę zewnętrzną, a następnie eksportuje równolegle wyniki instrukcji Transact-SQL SELECT.

• Usługa Azure Synapse Analytics i system platformy analizy obsługują usługę Hadoop lub usługę Azure Blob Storage.
• Program SQL Server 2022 (16.x) i nowsze wersje obsługują CREATE EXTERNAL TABLE AS SELECT (CETAS), aby utworzyć tabelę zewnętrzną, a następnie wyeksportować równolegle wynik instrukcji SELECT Transact-SQL do usługi Azure Data Lake Storage (ADLS) Gen2, konta usługi Azure Storage w wersji 2 i magazynu obiektów zgodnego z protokołem S3.

Nuta

Możliwości i zabezpieczenia instrukcji CETAS dla usługi Azure SQL Managed Instance różnią się od programu SQL Server lub usługi Azure Synapse Analytics. Aby uzyskać więcej informacji, zobacz wersję usługi Azure SQL Managed Instance CREATE EXTERNAL TABLE AS SELECT.

Nuta

Możliwości i zabezpieczenia instrukcji CETAS dla pul bezserwerowych w usłudze Azure Synapse Analytics różnią się od programu SQL Server. Aby uzyskać więcej informacji, zobacz CETAS with Synapse SQL.

Transact-SQL konwencje składni

Składnia

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

Argumenty

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

Jedna do trzech części nazwy tabeli do utworzenia w bazie danych. W przypadku tabeli zewnętrznej relacyjna baza danych przechowuje tylko metadane tabeli.

[ ( column_name [ ,... n ] ) ]

Nazwa kolumny tabeli.

LOKALIZACJA

Dotyczy: Azure Synapse Analytics and Analytics Platform System

'hdfs_folder'**
Określa, gdzie mają być zapisywane wyniki instrukcji SELECT w zewnętrznym źródle danych. Lokalizacja jest nazwą folderu i opcjonalnie może zawierać ścieżkę względną do folderu głównego klastra usługi Hadoop lub magazynu obiektów blob. Program PolyBase tworzy ścieżkę i folder, jeśli jeszcze nie istnieje.

Pliki zewnętrzne są zapisywane w hdfs_folder i nazwane QueryID_date_time_ID.format, gdzie ID jest identyfikatorem przyrostowym, a format jest wyeksportowanym formatem danych. Przykładem jest QID776_20160130_182739_0.orc.

LOKALIZACJA musi wskazywać folder i mieć końcowy /, na przykład: aggregated_data/.

Dotyczy: PROGRAMU SQL Server 2022 (16.x) i nowszych

prefix://path[:port] udostępnia protokół łączności (prefiks), ścieżkę i opcjonalnie port do zewnętrznego źródła danych, w którym zostanie zapisany wynik instrukcji SELECT.

Jeśli miejsce docelowe jest magazynem obiektów zgodnym z usługą S3, zasobnik musi najpierw istnieć, ale w razie potrzeby program PolyBase może tworzyć podfoldery. Program SQL Server 2022 (16.x) obsługuje usługę Azure Data Lake Storage Gen2, konto usługi Azure Storage w wersji 2 i magazyn obiektów zgodny z protokołem S3. Pliki ORC nie są obecnie obsługiwane.

DATA_SOURCE = external_data_source_name

Określa nazwę zewnętrznego obiektu źródła danych, który zawiera lokalizację, w której przechowywane są dane zewnętrzne lub będą przechowywane. Lokalizacja to klaster hadoop lub magazyn obiektów blob platformy Azure. Aby utworzyć zewnętrzne źródło danych, użyj CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Określa nazwę obiektu formatu pliku zewnętrznego, który zawiera format pliku danych zewnętrznych. Aby utworzyć format pliku zewnętrznego, użyj CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Opcje ODRZUĆ

Opcje ODRZUĆ nie mają zastosowania w momencie uruchomienia tej instrukcji CREATE EXTERNAL TABLE AS SELECT. Zamiast tego są one określone w tym miejscu, aby baza danych mogła ich używać w późniejszym czasie podczas importowania danych z tabeli zewnętrznej. Później, gdy instrukcja CREATE TABLE AS SELECT wybierze dane z tabeli zewnętrznej, baza danych użyje opcji odrzucenia, aby określić liczbę lub procent wierszy, których nie można zaimportować, zanim zatrzyma import.

• REJECT_VALUE = reject_value

  Określa wartość lub wartość procentową wierszy, których nie można zaimportować przed zatrzymaniem importu bazy danych.

• REJECT_TYPE = wartość | wartość procentowa

  Wyjaśnia, czy opcja REJECT_VALUE jest wartością literału, czy procentem.

  • wartości

    Używany, jeśli REJECT_VALUE jest wartością literału, a nie wartością procentową. Baza danych zatrzymuje importowanie wierszy z pliku danych zewnętrznych, gdy liczba wierszy zakończonych niepowodzeniem przekracza reject_value.

    Jeśli na przykład REJECT_VALUE = 5 i REJECT_TYPE = value, baza danych przestanie importować wiersze po tym, jak nie można zaimportować pięciu wierszy.

  • procent

    Używany, jeśli REJECT_VALUE jest wartością procentową, a nie wartością literału. Baza danych zatrzymuje importowanie wierszy z pliku danych zewnętrznych, gdy wartość procentowa wierszy, które zakończyły się niepowodzeniem, przekracza reject_value. Procent wierszy, które zakończyły się niepowodzeniem, jest obliczany w odstępach czasu. Tylko prawidłowe w dedykowanych pulach SQL, gdy TYPE=HADOOP.

• REJECT_SAMPLE_VALUE = reject_sample_value

  Wymagane, gdy REJECT_TYPE = percentage. Określa liczbę wierszy do zaimportowania, zanim baza danych ponownie obliczy procent wierszy zakończonych niepowodzeniem.

  Jeśli na przykład REJECT_SAMPLE_VALUE = 1000, baza danych obliczy procent nieudanych wierszy po próbie zaimportowania 1000 wierszy z pliku danych zewnętrznych. Jeśli wartość procentowa wierszy, które zakończyły się niepowodzeniem, jest mniejsza niż reject_value, baza danych próbuje załadować kolejne 1000 wierszy. Baza danych nadal oblicza ponownie procent nieudanych wierszy po próbie zaimportowania każdego dodatkowego 1000 wierszy.

  Nuta

  Ponieważ baza danych oblicza procent nieudanych wierszy w odstępach czasu, rzeczywisty procent wierszy, które zakończyły się niepowodzeniem, może przekroczyć reject_value.

  przykład :

  W tym przykładzie pokazano, jak trzy opcje ODRZUć współdziałają ze sobą. Jeśli na przykład REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100, może wystąpić następujący scenariusz:

  • Baza danych próbuje załadować pierwsze 100 wierszy, z których 25 kończy się niepowodzeniem i 75 powiodło się.
  • Procent wierszy, które zakończyły się niepowodzeniem, jest obliczany jako 25%, który jest mniejszy niż wartość odrzucenia 30%. Nie trzeba więc zatrzymywać obciążenia.
  • Baza danych próbuje załadować kolejne 100 wierszy. Tym razem 25 powiedzie się i 75 nie powiedzie się.
  • Procent wierszy, które zakończyły się niepowodzeniem, zostanie ponownie obliczony jako 50%. Wartość procentowa nieudanych wierszy przekroczyła 30% wartość odrzucania.
  • Ładowanie kończy się niepowodzeniem z powodu błędu 50% wierszy po próbie załadowania 200 wierszy, co jest większe niż określony limit 30%.

Z common_table_expression

Określa tymczasowy nazwany zestaw wyników, znany jako wspólne wyrażenie tabeli (CTE). Aby uzyskać więcej informacji, zobacz WITH common_table_expression (Transact-SQL)

WYBIERANIE <select_criteria>

Wypełnia nową tabelę wynikami z instrukcji SELECT. select_criteria jest treścią instrukcji SELECT, która określa dane do skopiowania do nowej tabeli. Aby uzyskać informacje na temat instrukcji SELECT, zobacz SELECT (Transact-SQL).

Nuta

Klauzula ORDER BY w elemecie SELECT nie ma wpływu na CETAS.

opcje kolumny

• column_name [ ,... n ]

  Nazwy kolumn nie zezwalają na opcje kolumn wymienione w sekcji CREATE TABLE. Zamiast tego możesz podać opcjonalną listę co najmniej jednej nazwy kolumn dla nowej tabeli. Kolumny w nowej tabeli używają podanych nazw. Po określeniu nazw kolumn liczba kolumn na liście kolumn musi być zgodna z liczbą kolumn w wynikach wyboru. Jeśli nie określisz nazw kolumn, nowa tabela docelowa używa nazw kolumn w wynikach instrukcji select.

  Nie można określić żadnych innych opcji kolumn, takich jak typy danych, sortowanie lub wartość null. Każdy z tych atrybutów pochodzi z wyników instrukcji SELECT. Można jednak użyć instrukcji SELECT, aby zmienić atrybuty. Aby zapoznać się z przykładem, zobacz Używanie instrukcji CETAS do zmieniania atrybutów kolumny.

Uprawnienia

Aby uruchomić to polecenie, użytkownik bazy danych potrzebuje wszystkich tych uprawnień lub członkostwa:

• uprawnienia ALTER SCHEMA schematu lokalnego, które będą zawierać nową tabelę lub członkostwo w db_ddladmin stałej roli bazy danych.
• uprawnienie CREATE TABLE lub członkostwo w stałej roli bazy danych db_ddladmin.
• select uprawnienia do wszystkich obiektów, do których odwołuje się select_criteria.

Logowanie wymaga wszystkich tych uprawnień:

• ADMINISTROWANIE OPERACJAMI ZBIORCZYM
• ALTER ANY EXTERNAL DATA SOURCE
• ZMIENIĆ DOWOLNY FORMAT PLIKU ZEWNĘTRZNEgo
• Ogólnie rzecz biorąc, musisz mieć uprawnienia do listy zawartości folderu i zapisywanie do folderu LOCATION dla CETAS.
• W usłudze Azure Synapse Analytics and Analytics Platform System uprawnienia do zapisu odczytu i zapisu w folderze zewnętrznym w klastrze Hadoop lub w usłudze Azure Blob Storage.
• W programie SQL Server 2022 (16.x) wymagane jest również ustawienie odpowiednich uprawnień w lokalizacji zewnętrznej. uprawnienia do zapisu danych wyjściowych do lokalizacji i uprawnienia odczyt, aby uzyskać do niego dostęp.
• W przypadku usług Azure Blob Storage i Azure Data Lake Gen2 token SHARED ACCESS SIGNATURE musi mieć następujące uprawnienia w kontenerze: Odczyt, Write, List, Create.
• W przypadku usługi Azure Blog Storage pole wyboru Allowed Services: Blob należy zaznaczyć, aby wygenerować token SAS.
• W przypadku usługi Azure Data Lake Gen2 należy zaznaczyć pola wyboru Allowed Services: Container i Object, aby wygenerować token SAS.

Ważny

Uprawnienie ALTER ANY EXTERNAL DATA SOURCE przyznaje każdemu podmiotowi zabezpieczeń możliwość tworzenia i modyfikowania dowolnego obiektu zewnętrznego źródła danych, dzięki czemu umożliwia również dostęp do wszystkich poświadczeń w zakresie bazy danych. To uprawnienie musi być uznawane za wysoce uprzywilejowane i musi być przyznane tylko zaufanym podmiotom zabezpieczeń w systemie.

Obsługa błędów

Gdy CREATE EXTERNAL TABLE AS SELECT eksportuje dane do pliku rozdzielanego tekstem, nie ma pliku odrzucenia dla wierszy, których nie można wyeksportować.

Podczas tworzenia tabeli zewnętrznej baza danych próbuje nawiązać połączenie z lokalizacją zewnętrzną. Jeśli połączenie nie powiedzie się, polecenie zakończy się niepowodzeniem, a tabela zewnętrzna nie zostanie utworzona. Niepowodzenie polecenia może potrwać co najmniej minutę, ponieważ baza danych ponawia próbę połączenia co najmniej trzy razy.

Jeśli CREATE EXTERNAL TABLE AS SELECT zostanie anulowana lub zakończy się niepowodzeniem, baza danych podejmuje jednorazową próbę usunięcia nowych plików i folderów utworzonych już w zewnętrznym źródle danych.

W usłudze Azure Synapse Analytics and Analytics Platform System baza danych zgłasza wszelkie błędy języka Java występujące w zewnętrznym źródle danych podczas eksportowania danych.

Uwagi

Po zakończeniu instrukcji CREATE EXTERNAL TABLE AS SELECT można uruchamiać zapytania Transact-SQL w tabeli zewnętrznej. Te operacje importuje dane do bazy danych na czas trwania zapytania, chyba że importujesz przy użyciu instrukcji CREATE TABLE AS SELECT.

Nazwa i definicja tabeli zewnętrznej są przechowywane w metadanych bazy danych. Dane są przechowywane w zewnętrznym źródle danych.

Instrukcja CREATE EXTERNAL TABLE AS SELECT zawsze tworzy tabelę niepartycyjną, nawet jeśli tabela źródłowa jest partycjonowana.

W przypadku programu SQL Server 2022 (16.x) należy włączyć opcję allow polybase export przy użyciu sp_configure. Aby uzyskać więcej informacji, zobacz Ustaw opcję konfiguracji allow polybase export.

W przypadku planów zapytań w usługach Azure Synapse Analytics i Analytics Platform System utworzonych za pomocą funkcji EXPLAIN baza danych używa tych operacji planu zapytań dla tabel zewnętrznych: przenoszenie zewnętrznych przetasowania, przenoszenie emisji zewnętrznej, przenoszenie partycji zewnętrznej.

W systemie platformy analizy jako wymaganie wstępne dotyczące tworzenia tabeli zewnętrznej administrator urządzenia musi skonfigurować łączność z usługą Hadoop. Aby uzyskać więcej informacji, zobacz "Configure Connectivity to External Data (Analytics Platform System)" (Konfigurowanie łączności z danymi zewnętrznymi (Analytics Platform System)" w dokumentacji systemu platformy analizy, którą można pobrać z centrum pobierania microsoft.

Ograniczenia i ograniczenia

Ponieważ dane tabeli zewnętrznej znajdują się poza bazą danych, operacje tworzenia kopii zapasowych i przywracania działają tylko na danych przechowywanych w bazie danych. W związku z tym kopia zapasowa i przywrócenie tylko metadanych.

Baza danych nie weryfikuje połączenia z zewnętrznym źródłem danych podczas przywracania kopii zapasowej bazy danych zawierającej tabelę zewnętrzną. Jeśli oryginalne źródło nie jest dostępne, przywracanie metadanych tabeli zewnętrznej nadal zakończy się powodzeniem, ale operacje SELECT w tabeli zewnętrznej kończą się niepowodzeniem.

Baza danych nie gwarantuje spójności danych między bazą danych a danymi zewnętrznymi. Klient ponosi wyłączną odpowiedzialność za zachowanie spójności między danymi zewnętrznymi a bazą danych.

Operacje języka manipulowania danymi (DML) nie są obsługiwane w tabelach zewnętrznych. Na przykład nie można użyć instrukcji Transact-SQL update, insert lub delete Transact-SQL, aby zmodyfikować dane zewnętrzne.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW i DROP VIEW to jedyne operacje języka definicji danych (DDL) dozwolone w tabelach zewnętrznych.

Ograniczenia i ograniczenia dotyczące usługi Azure Synapse Analytics

• W dedykowanych pulach SQL usługi Azure Synapse Analytics i systemie platformy analizy technologia PolyBase może korzystać z maksymalnie 33 000 plików na folder podczas uruchamiania 32 współbieżnych zapytań polyBase. Ta maksymalna liczba obejmuje zarówno pliki, jak i podfoldery w każdym folderze HDFS. Jeśli stopień współbieżności jest mniejszy niż 32, użytkownik może uruchamiać zapytania PolyBase względem folderów w systemie plików HDFS, które zawierają więcej niż 33 000 plików. Zalecamy, aby użytkownicy usług Hadoop i PolyBase zachowywali krótkie ścieżki plików i nie używali więcej niż 30 000 plików na folder HDFS. W przypadku odwołwania się do zbyt wielu plików występuje wyjątek braku pamięci maszyny wirtualnej JVM.

• W bezserwerowych pulach SQL tabele zewnętrzne nie mogą być tworzone w lokalizacji, w której są obecnie przechowywane dane. Aby ponownie użyć lokalizacji używanej do przechowywania danych, lokalizacja musi zostać ręcznie usunięta w usłudze ADLS. Aby uzyskać więcej ograniczeń i najlepszych rozwiązań, zobacz Najlepsze rozwiązania dotyczące optymalizacji filtrów.

W dedykowanych pulach SQL usługi Azure Synapse Analytics i systemie platformy analizy po wybraniu CREATE EXTERNAL TABLE AS SELECT z pliku RCFile wartości kolumn w pliku RCFile nie mogą zawierać znaku potoku (|).

SET ROWCOUNT (Transact-SQL) nie ma wpływu na TWORZENIE TABELI ZEWNĘTRZNEJ JAKO SELECT. Aby osiągnąć podobne zachowanie, użyj TOP (Transact-SQL).

Przejrzyj nazewnictwa i odwoływania się do kontenerów, obiektów blob i metadanych, aby uzyskać ograniczenia dotyczące nazw plików.

Błędy znaków

Następujące znaki obecne w danych mogą powodować błędy, w tym odrzucone rekordy z CREATE EXTERNAL TABLE AS SELECT do plików Parquet.

W usłudze Azure Synapse Analytics and Analytics Platform System dotyczy to również plików ORC.

• |
• " (znak cudzysłowu)
• \r\n
• \r
• \n

Aby użyć CREATE EXTERNAL TABLE AS SELECT zawierających te znaki, należy najpierw uruchomić instrukcję CREATE EXTERNAL TABLE AS SELECT, aby wyeksportować dane do rozdzielonych plików tekstowych, w których można je przekonwertować na Parquet lub ORC przy użyciu narzędzia zewnętrznego.

Praca z parquet

Podczas pracy z plikami parquet CREATE EXTERNAL TABLE AS SELECT wygeneruje jeden plik parquet na dostępny procesor CPU, maksymalnie skonfigurowany maksymalny stopień równoległości (MAXDOP). Każdy plik może wzrosnąć do 190 GB, po upływie tego momentu program SQL Server wygeneruje więcej plików Parquet zgodnie z potrzebami.

Wskazówka zapytania OPTION (MAXDOP n) będzie mieć wpływ tylko na część SELECT CREATE EXTERNAL TABLE AS SELECT. Nie ma wpływu na liczbę plików parquet. Uwzględniana jest tylko wartość MAXDOP na poziomie bazy danych i maxDOP na poziomie wystąpienia.

Blokowania

Pobiera udostępnioną blokadę obiektu SCHEMARESOLUTION.

Obsługiwane typy danych

Instrukcje CETAS mogą służyć do przechowywania zestawów wyników z następującymi typami danych SQL:

• dwójkowy
• varbinary
• Char
• varchar
• nchar
• nvarchar
• mały rozmiar
• data
• data/godzina
• data/godzina2
• datetimeoffset
• Godzina
• dziesiętny
• numeryczny
• spławik
• prawdziwy
• bigint
• tinyint
• smallint
• Int
• bigint
• bit
• pieniądze
• smallmoney

Przykłady

A. Tworzenie tabeli Hadoop przy użyciu polecenia CREATE EXTERNAL TABLE AS SELECT

Dotyczy: Azure Synapse Analytics and Analytics Platform System

Poniższy przykład tworzy nową tabelę zewnętrzną o nazwie hdfsCustomer, która używa definicji kolumn i danych z tabeli źródłowej dimCustomer.

Definicja tabeli jest przechowywana w bazie danych, a wyniki instrukcji SELECT są eksportowane do pliku /pdwdata/customer.tbl w zewnętrznym źródle danych usługi Hadoop customer_ds. Plik jest sformatowany zgodnie z formatem pliku zewnętrznego customer_ff.

Nazwa pliku jest generowana przez bazę danych i zawiera identyfikator zapytania w celu ułatwienia wyrównania pliku do wygenerowanego zapytania.

Ścieżka hdfs://xxx.xxx.xxx.xxx:5000/files/ poprzedzająca katalog Klienta musi już istnieć. Jeśli katalog Customer nie istnieje, baza danych tworzy katalog.

Nuta

W tym przykładzie określono wartość 5000. Jeśli port nie zostanie określony, baza danych używa portu 8020 jako portu domyślnego.

Wynikowa lokalizacja i nazwa pliku usługi Hadoop będą hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt..

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. Używanie wskazówki dotyczącej zapytania z funkcją CREATE EXTERNAL TABLE AS SELECT

Dotyczy: Azure Synapse Analytics and Analytics Platform System

To zapytanie przedstawia podstawową składnię użycia wskazówki sprzężenia zapytania z instrukcją CREATE EXTERNAL TABLE AS SELECT. Po przesłaniu zapytania baza danych używa strategii sprzężenia skrótu w celu wygenerowania planu zapytania. Aby uzyskać więcej informacji na temat wskazówek sprzężenia i sposobu używania klauzuli OPTION, zobacz OPTION Clause (Transact-SQL).

Nuta

W tym przykładzie określono wartość 5000. Jeśli port nie zostanie określony, baza danych używa portu 8020 jako portu domyślnego.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. Zmienianie atrybutów kolumn za pomocą instrukcji CETAS

Dotyczy: Azure Synapse Analytics and Analytics Platform System

W tym przykładzie użyto instrukcji CETAS do zmiany typów danych, wartości null i sortowania dla kilku kolumn w tabeli FactInternetSales.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. Użyj polecenia CREATE EXTERNAL TABLE AS SELECT export data as parquet

Dotyczy: SQL Server 2022 (16.x)

Poniższy przykład tworzy nową tabelę zewnętrzną o nazwie ext_sales, która używa danych z tabeli SalesOrderDetailAdventureWorks2022. Należy włączyć opcję zezwalania na konfigurację eksportu programu PolyBase.

Wynik instrukcji SELECT zostanie zapisany w magazynie obiektów zgodnym z usługą S3 wcześniej skonfigurowanym i nazwanym s3_edsoraz odpowiednim poświadczeniu utworzonym jako s3_dsc. Lokalizacja pliku parquet będzie <ip>:<port>/cetas/sales.parquet, gdzie cetas jest wcześniej utworzonym zasobnikiem magazynu.

Nuta

Format różnicowy jest obecnie obsługiwany tylko jako tylko do odczytu.

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. Używanie funkcji CREATE EXTERNAL TABLE AS SELECT z tabeli delty do parquet

Dotyczy: SQL Server 2022 (16.x)

W poniższym przykładzie zostanie utworzona nowa tabela zewnętrzna o nazwie Delta_to_Parquet, która używa typu tabela delty danych znajdujących się w magazynie obiektów zgodnym z usługą S3 o nazwie s3_deltai zapisuje wynik w innym źródle danych o nazwie s3_parquet jako plik parquet. W tym przykładzie użyto polecenia OPENROWSET. Należy włączyć opcję zezwalania na konfigurację eksportu programu PolyBase.

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. Użyj polecenia CREATE EXTERNAL TABLE AS SELECT z widokiem jako źródłem

Dotyczy: bezserwerowych pul SQL usługi Azure Synapse Analytics i dedykowanych pul SQL.

Użyj następującego przykładu jako szablonu do pisania instrukcji CETAS z widokiem zdefiniowanym przez użytkownika jako źródłem, używając tożsamości zarządzanej do uwierzytelniania i punktu końcowego abfs::

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

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

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'abfs[s]://<file_system>@<account_name>.dfs.core.windows.net/<path>/<file_name>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

G. Użyj polecenia CREATE EXTERNAL TABLE AS SELECT z widokiem jako źródłem

Dotyczy: bezserwerowych pul SQL usługi Azure Synapse Analytics i dedykowanych pul SQL.

W tym przykładzie możemy zobaczyć przykład kodu szablonu do pisania instrukcji CETAS z widokiem zdefiniowanym przez użytkownika jako źródłem, przy użyciu tożsamości zarządzanej jako uwierzytelniania i https:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

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

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

Następne kroki

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics, Parallel Data Warehouse)
• CREATE TABLE AS SELECT (Azure Synapse Analytics)
• DROP TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)