Cvičení – použití PolyBase k dotazům souboru Parquet

10 min

V tomto cvičení:

Nainstalujte a povolte PolyBase.
Vytvořte databázi.
Vytvořte hlavní klíč databáze pro zabezpečení přihlašovacích údajů s vymezeným oborem databáze.
Vytvořte přihlašovací údaje s vymezeným oborem databáze pro přístup ke zdroji dat.
Vytvořte zdroj dat.
Dotazování a manipulace s daty uloženými ve veřejném zdroji dat
Vytvořte formát externího souboru a externí tabulku.

Nainstalujte PolyBase

PolyBase můžete nainstalovat s instalačním spustitelným souborem SQL Serveru během počáteční instalace nebo ho přidat jako funkci později. Na stránce Výběr funkce SQL Serveru setup.exevyberte PolyBase Query Service for External Data.

Služby PolyBase vyžadují povolení portů brány firewall, aby se mohly připojit k externím zdrojům dat. Ve výchozím nastavení Používá PolyBase porty od 16450 do 16460.

Obrázek instalačního spustitelného souboru SQL Serveru zobrazující konfiguraci rozsahu portů PolyBase

Instalace PolyBase nainstaluje dvě služby, SQL Server PolyBase Engine a SQL Server PolyBase Data Movement. Úplné informace a požadavky pro instalaci PolyBase najdete v tématu:

Povolení PolyBase

Po instalaci služby se připojte k instanci SQL Serveru 2025 v aplikaci SQL Server Management Studio (SSMS) a spuštěním následujícího příkazu povolte PolyBase.

EXEC SP_CONFIGURE @CONFIGNAME = N'POLYBASE ENABLED', @CONFIGVALUE = 1;
RECONFIGURE;

Obrázek povolení PolyBase pomocí T-SQL v sadě SQL Server Management Studio

Poznámka

V tomto cvičení budete dotazovat soubory Apache Parquet pomocí rozhraní PolyBase REST API, takže nemusíte povolovat ani konfigurovat služby SQL Server PolyBase Data Movement nebo SQL Server PolyBase Engine .

Vytvoření databáze

Spuštěním následujícího příkazu v aplikaci SSMS vytvořte databázi pro toto cvičení s názvem Demo1. Pokud už byla databáze vytvořená, skript ji zahodí a znovu vytvoří.

USE MASTER;

IF EXISTS (SELECT * FROM sys.databases WHERE [name] = N'Demo1')
BEGIN
    ALTER DATABASE Demo1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE IF EXISTS Demo1
END;

CREATE DATABASE Demo1;

USE Demo1;

Vytvoření hlavního klíče databáze

Abyste zajistili zabezpečení přihlašovacích údajů v oboru databáze, musíte vytvořit hlavní klíč databáze. Následující příklad vytvoří klíč s náhodně vygenerovaným heslem a vyžaduje se záloha.

DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
EXECUTE sp_executesql @createMasterKey;

SELECT * FROM sys.symmetric_keys;

Pokud chcete lépe porozumět šifrovacím klíčům v produkčním prostředí a udržovat je, přečtěte si:

VYTVOŘIT HLAVNÍ KLÍČ (Transact-SQL)
Hierarchie šifrování

Vytvoření přihlašovacích údajů s vymezeným oborem databáze

Pověření omezená na databázi jsou zodpovědná za ukládání údajů, které zdroj dat používá k připojení ke koncovému bodu. Tento příklad používá veřejný koncový bod, takže přihlašovací údaje nepotřebují tajný kód.

IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = N'PublicCredential')
    DROP DATABASE SCOPED CREDENTIAL PublicCredential;
 
CREATE DATABASE SCOPED CREDENTIAL PublicCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<KEY>'; -- This example doesn't need the SECRET because the data source is public

Vytvoření zdroje dat

Tento příklad používá veřejně dostupnou datovou sadu COVID Parquet uloženou ve službě Azure Blob Storage. K navázání připojení použijete databázi s vymezeným oborem PublicCredential, kterou jste vytvořili.

Hodnoty UMÍSTĚNÍ:

Předpona: abs
Účet služby Azure Storage: pandemicdatalake
Úplná cesta k účtu Azure Storage: pandemicdatalake.blob.core.windows.net
Název kontejneru: public
Úplná cesta kontejneru: public/curated/covid-19/bing_covid-19_data/latest

IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = N'Public_Covid') DROP EXTERNAL DATA SOURCE Public_Covid;
 
CREATE EXTERNAL DATA SOURCE Public_Covid
WITH (
    LOCATION = 'abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest',
    CREDENTIAL = [PublicCredential]
);

Úplný seznam zdrojů dat a odpovídajících předpon najdete v tématu VYTVOŘENÍ EXTERNÍHO ZDROJE DAT.
Další informace o veřejné datové sadě najdete v tématu Bing COVID-19 .

Dotazování dat pomocí OPENROWSET

K přístupu a prozkoumání dat můžete použít OPENROWSET. OPENROWSET je optimalizovaný pro scénáře ad hoc úloh a zkoumání dat.

Hodnoty OPENROWSET:

BULK: Název a přípona souboru. Funkce BULK automaticky přidává ke zdroji dat informace, takže úplné umístění souboru je abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
FORMÁT: PARQUET
DATA_SOURCE: Informace o připojení, v tomto případě váš nový zdroj dat Public_Covid

SELECT TOP 1000 *
FROM OPENROWSET 
     (BULK 'bing_covid-19_data.parquet'
     , FORMAT = 'PARQUET'
     , DATA_SOURCE = 'Public_Covid')
     AS [COVID_Dataset]

Následující příklad používá flexibilitu T-SQL k dotazování souboru Parquet v reálném čase, stejně jako běžná tabulka. Pokud chcete vrátit počet potvrzených případů v sestupném pořadí pro každý stav USA, spusťte následující dotaz:

SELECT [COVID_Dataset].admin_region_1, 
       SUM(CAST([COVID_Dataset].confirmed AS BIGINT)) AS Confirmed
FROM OPENROWSET 
     (BULK 'bing_covid-19_data.parquet'
     , FORMAT = 'PARQUET'
     , DATA_SOURCE = 'Public_Covid')
     AS [COVID_Dataset]
WHERE [COVID_Dataset].country_region = 'United States' AND 
      [COVID_Dataset].admin_region_1  IS NOT NULL
GROUP BY [COVID_Dataset].admin_region_1 
ORDER BY confirmed DESC

Vytvoření a dotazování externí tabulky

OPENROWSET je optimalizovaný pro ad hoc spouštění a zkoumání dat. Externí tabulky jsou vhodnější pro opakovaný přístup, protože můžou také používat statistiky.

Zjištění schématu externí tabulky

Pokud chcete vytvořit externí tabulku, nejprve určete sloupce a typ. Schéma pochází z externího souboru, takže může být časově náročné přesně určit datové typy a rozsahy. K urychlení tohoto procesu můžete naštěstí použít uloženou proceduru sp_describe_first_result_set (Transact-SQL).

DECLARE @tsql NVARCHAR(MAX) = 'SELECT TOP 1000 *
FROM OPENROWSET 
    (BULK ''bing_covid-19_data.parquet''
    , FORMAT = ''PARQUET''
    , DATA_SOURCE = ''Public_Covid'')
    AS [COVID_Dataset]';
EXEC sys.sp_describe_first_result_set @tsql;

Obrázek výsledků schématu externího zdroje dat Parquet v aplikaci SQL Server Management Studio

Můžete vidět, že sp_describe_first_result_set vrátil názvy sloupců, typy, délku, přesnost a dokonce i kolaci zdroje dat.

Vytvoření formátu externího souboru

Vzhledem k tomu, že musíte odkazovat na soubor Parquet na externí tabulku, musíte nejprve spustit CREATE EXTERNAL FILE FORMAT pro přidání formátu souboru Parquet. Definice formátu souboru je důležitá pro externí tabulky, protože určuje skutečný typ rozložení a komprese.

Spusťte následující příkaz:

IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = N'ParquetFileFormat')
      DROP EXTERNAL FILE FORMAT ParquetFileFormat;

CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);

Vytvoření externí tabulky

Nakonec s veškerými informacemi, které jste právě získali, a vytvořeným formátem externího souboru můžete vytvořit externí tabulku pomocí následujícího skriptu:

IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = N'ParquetFileFormat')
      DROP EXTERNAL FILE FORMAT ParquetFileFormat;
 
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
 
-- 8.3 CREATE EXTERNAL TABLE
IF OBJECT_ID(N'ext_covid_data', N'ET') IS NOT NULL
      DROP EXTERNAL TABLE ext_covid_data;
 
CREATE EXTERNAL TABLE ext_covid_data
(
id                            int,
updated                       date,
confirmed               int,
confirmed_change  int,
deaths                        int,
deaths_change           smallint,
recovered               int,
recovered_change  int,
latitude                float,
longitude               float,
iso2                    varchar(8000),
iso3                    varchar(8000),
country_region          varchar(8000),
admin_region_1          varchar(8000),
iso_subdivision         varchar(8000),
admin_region_2          varchar(8000),
load_time                     datetime2(7)
)
WITH
(
LOCATION = 'bing_covid-19_data.parquet'
     , FILE_FORMAT = ParquetFileFormat
     , DATA_SOURCE = Public_Covid
);
 
CREATE STATISTICS [Stats_ext_covid_data_updated] ON ext_covid_data([updated]);
 
SELECT TOP 1000 * FROM ext_covid_data;

Poznámka

Názvy sloupců musí odpovídat sloupcům uloženým v souboru Parquet nebo SQL Server nemůže identifikovat sloupce a vrátit NULL.

Po vytvoření externí tabulky ext_covid_datamůžete přidat statistiky o aktualizovaných sloupcích pro zajištění efektivity. Další informace o statistikách vnější tabulky naleznete v tématu CREATE STATISTICS (Transact-SQL).

V této lekci jste použili PolyBase k připojení k externímu zdroji dat a k dotazování souboru Parquet použili OPENROWSET nebo externí tabulku. V dalším cvičení použijete služby PolyBase k připojení a vytvoření externí tabulky z databáze v Azure SQL Database.

Váš názor

Byla tato stránka užitečná?