SYNAPSE SQL ile dış tabloları kullanma

dış tablo Hadoop, Azure Depolama blob veya Azure Data Lake Storage bulunan verilere işaret eder. dış tablolar, dosyalardaki verileri okumak veya Azure Depolama dosyalara veri yazmak için kullanılır. Synapse SQL ile dış tabloları, adanmış SQL havuzunu veya sunucusuz SQL havuzunu kullanarak okumak için kullanabilirsiniz.

Dış veri kaynağının türüne bağlı olarak, iki tür dış tablo kullanabilirsiniz:

• CSV, Parquet ve ORC gibi çeşitli veri biçimlerinde veri okumak ve dışarı aktarmak için kullanabileceğiniz Hadoop dış tabloları. Hadoop dış tabloları adanmış SQL havuzlarda mevcuttur, ancak sunucusuz SQL havuzlarda kullanılamaz.
• CSV ve Parquet gibi çeşitli veri biçimlerinde veri okumak ve dışarı aktarmak için kullanabileceğiniz yerel dış tablolar. yerel dış tablolar sunucusuz SQL havuzlarında kullanılabilir ve adanmış SQL havuzlarında genel önizlemededir .

Hadoop ve yerel dış tablolar arasındaki temel farklılıklar aşağıdaki tabloda sunulmuştur:

adanmış SQL havuzunda ve sunucusuz SQL havuzunda dış tablolar

Dış tabloları şunları yapmak için kullanabilirsiniz:

• Azure Blob Depolama ve Azure Data Lake Gen2 Transact-SQL deyimleriyle sorgulayın.
• sorgu sonuçlarını Azure Blob Depolama veya cetaskullanarak Azure Data Lake Storage depolayın.
• Azure Blob Depolama ve Azure Data Lake Storage verileri içeri aktarın ve ayrılmış bir SQL havuzunda saklayın (yalnızca adanmış havuzda Hadoop tabloları).

aşağıdaki adımlar aracılığıyla Synapse SQL havuzlarda dış tablolar oluşturabilirsiniz:

1. Dış bir Azure depolama alanına başvurmak için dış VERI kaynağı oluşturun ve depolama alanına erişmek için kullanılması gereken kimlik bilgisini belirtin.
2. CSV veya Parquet dosyalarının biçimini tanımlayacak HARICI dosya biçimi oluşturun .
3. Aynı dosya biçimiyle veri kaynağına yerleştirilmiş dosyaların üstünde dış tablo oluşturun .

Güvenlik

Kullanıcının SELECT , verileri okumak için bir dış tabloda izni olması gerekir. Dış tablolar, aşağıdaki kurallar kullanılarak veri kaynağında tanımlanan veritabanı kapsamlı kimlik bilgilerini kullanarak temel alınan Azure Storage 'a erişir:

• Kimlik bilgisi olmayan veri kaynağı, dış tabloların Azure Storage 'da genel kullanıma açık dosyalara erişmesine olanak sağlar.
• Veri kaynağı, dış tabloların yalnızca SAS belirtecini veya çalışma alanı yönetilen kimliğini kullanarak Azure Storage 'daki dosyalara erişmesini sağlayan bir kimlik bilgisine sahip olabilir. örnekler Için bkz. depolama dosyalarını geliştirme depolama erişim denetimi makalesi.

CREATE EXTERNAL DATA SOURCE

Dış veri kaynakları, depolama hesaplarına bağlanmak için kullanılır. Tüm belgeler buradaözetlenmiştir.

Dış VERI kaynağı oluşturma söz dizimi

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
     , TYPE = HADOOP
)
[;]

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
)
[;]

Dış VERI kaynağı oluşturma için bağımsız değişkenler

data_source_name

Veri kaynağı için Kullanıcı tanımlı adı belirtir. Ad, veritabanı içinde benzersiz olmalıdır.

Konum

LOCATION = '<prefix>://<path>' -dış veri kaynağının bağlantı protokolünü ve yolunu sağlar. Şu desenler konumda kullanılabilir:

https: ön ek, yoldaki alt klasörü kullanmanıza olanak sağlar.

Kimlik Bilgisi

CREDENTIAL = <database scoped credential> Azure depolamada kimlik doğrulaması için kullanılacak isteğe bağlı kimlik bilgileridir. Kimlik bilgisi olmayan dış veri kaynağı, ortak depolama hesabına erişebilir veya depolamadaki dosyalara erişmek için çağıranın Azure AD kimliğini kullanabilir.

• adanmış SQL havuzunda, veritabanı kapsamlı kimlik bilgileri özel uygulama kimliği, çalışma alanı yönetilen kimliği veya SAK anahtarı belirtebilir.
• sunucusuz SQL havuzunda, veritabanı kapsamlı kimlik bilgileri çalışma alanı yönetilen kimliğini veya SAS anahtarını belirtebilir.

TÜR

TYPE = HADOOP temel alınan dosyalara erişmek Için Java tabanlı teknolojinin kullanılması gerektiğini belirten seçenektir. bu parametre yerleşik yerel okuyucu kullanan sunucusuz SQL havuzunda kullanılamaz.

Dış VERI kaynağı oluşturma örneği

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

CREATE EXTERNAL FILE FORMAT

Azure Blob Depolama'da veya Azure Data Lake Depolama depolanan dış verileri tanımlayan bir dış dosya biçimi nesnesi Depolama. Dış dosya biçimi oluşturmak, dış tablo oluşturmak için önkoşuldur. Tüm belgeler buradadır.

Dış dosya biçimi oluşturarak, bir dış tablo tarafından başvurulan verilerin gerçek düzenini belirtirsiniz.

CREATE EXTERNAL FILE FORMAT için söz dizimi

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = PARQUET  
    [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.GzipCodec'      }  
    ]);  

--Create an external file format for DELIMITED TEXT files
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT  
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ]
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
    );  

<format_options> ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter
    | First_Row = integer
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | Encoding = {'UTF8' | 'UTF16'}
    | PARSER_VERSION = {'parser_version'}
}

CREATE EXTERNAL FILE FORMAT için bağımsız değişkenler

file_format_name- Dış dosya biçimi için bir ad belirtir.

FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT]- Dış verilerin biçimini belirtir.

• PARQUET - Parquet biçimini belirtir.
• DELIMITEDTEXT - Alan sonlandırıcılar olarak da adlandırılan sütun sınırlayıcıları ile bir metin biçimi belirtir.

FIELD_TERMINATOR = field_terminator - Yalnızca sınırlandırılmış metin dosyaları için geçerlidir. Alan sonlandırıcı, metinle ayrılmış dosyadaki her alanın (sütun) sonunu işaret edecek bir veya daha fazla karakter belirtir. Varsayılan değer, kanal karakteridir (ꞌ|ꞌ).

Örnekler:

• FIELD_TERMINATOR = '|'
• FIELD_TERMINATOR = ' '
• FIELD_TERMINATOR = ꞌ\tꞌ

STRING_DELIMITER = string_delimiter - Sınırlandırılmış metin dosyasındaki dize türünde veriler için alan sonlandırıcıyı belirtir. Dize sınırlayıcı bir veya daha fazla karakter uzunluğundadır ve tek tırnak içine alınır. Varsayılan değer boş dizedir ("").

Örnekler:

• STRING_DELIMITER = '"'
• STRING_DELIMITER = '*'
• STRING_DELIMITER = ꞌ.ꞌ

FIRST_ROW = First_row_int - Önce okunan ve tüm dosyalara uygulanan satır numarasını belirtir. Değerin iki olarak ayarlanmaları, veriler yüklendiğinde her dosyadaki ilk satırın (üst bilgi satırı) atlanır. Satırlar satır sonlandırıcıların (/r/n, /r, /n) varlığına göre atlanır.

USE_TYPE_DEFAULT = { TRUE | FALSE } - Metin dosyasından veri alınırken sınırlandırılmış metin dosyalarında eksik değerlerin nasıl işley olduğunu belirtir.

TRUE - Metin dosyasından veri alarak eksik değerleri dış tablo tanımındaki ilgili sütun için varsayılan değerin veri türünü kullanarak depolarsınız. Örneğin, eksik bir değeri şu değerle değiştirin:

• Sütun sayısal sütun olarak tanımlandı ise 0. Ondalık sütunlar desteklenmiyor ve hataya neden olur.
• Sütun bir dize sütunu ise boş dize ("") .
• Sütun bir tarih sütunu ise 1900-01-01.

FALSE - Tüm eksik değerleri NULL olarak depolar. Sınırlandırılmış metin dosyasında NULL sözcüğü kullanılarak depolanan tüm NULL değerleri 'NULL' dizesi olarak içe aktarılır.

Encoding = {'UTF8' | 'UTF16'} - Sunucusuz SQL havuzu UTF8 ve UTF16 ile kodlanmış sınırlandırılmış metin dosyalarını okuyabilir.

DATA_COMPRESSION = data_compression_method - Bu bağımsız değişken dış veriler için veri sıkıştırma yöntemini belirtir.

PARQUET dosya biçimi türü aşağıdaki sıkıştırma yöntemlerini destekler:

• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

PARQUET dış tablolarından okurken bu bağımsız değişken yoksayılır, ancak CETAS kullanılarak dış tablolara yazarken kullanılır.

DELIMITEDTEXT dosya biçimi türü aşağıdaki sıkıştırma yöntemini destekler:

• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'

PARSER_VERSION = 'parser_version' CSV dosyaları okurken kullanılacak ayrıştırıcı sürümünü belirtir. Kullanılabilir ayrıştırıcı sürümleri ve 1.0 2.0 'tir. Bu seçenek yalnızca sunucusuz havuzlarda SQL kullanılabilir.

CREATE EXTERNAL FILE FORMAT örneği

Aşağıdaki örnek sayım dosyaları için bir dış dosya biçimi oluşturur:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

DıŞ TABLO OLUŞTURMA

CREATE EXTERNAL TABLE komutu, Synapse SQL Azure Blob Depolama veya Azure Data Lake Depolama.

CREATE EXTERNAL TABLE için söz dizimi

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )  
    WITH (
        LOCATION = 'folder_or_filepath',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]
        [, <reject_options> [ ,...n ] ] 
    )
[;] 

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

<reject_options> ::=  
{  
    | REJECT_TYPE = value,  
    | REJECT_VALUE = reject_value,  
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}   

Bağımsız değişkenler CREATE EXTERNAL TABLE

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

Oluşturulacak tablonun bir-üç parçalı adı. Dış tablo için Synapse SQL havuzu yalnızca tablo meta verilerini depolar. Synapse veritabanına gerçek veri taşınmaz veya SQL depolanır.

<column_definition>, ... n ]

CREATE EXTERNAL TABLE sütun adını, veri türünü ve harmanlamayı yapılandırma olanağını destekler. Dış tablolarda DEFAULT CONSTRAINT'i kullanasiniz.

Parquet dosyalarından okurken, yalnızca okumak istediğiniz sütunları belirtebilirsiniz ve kalanları atlayabilirsiniz.

LOCATION = 'folder_or_filepath'

Azure Blob Depolama'daki gerçek verilerin klasörünü veya dosya yolunu ve dosya Depolama. Konum kök klasörden başlar. Kök klasör, dış veri kaynağında belirtilen veri konumudur.

Hadoop dış tablolarının aksine, yolun sonunda /** belirtmedikçe yerel dış tablolar alt klasörlere dönüşz. Bu örnekte, sunucusuz bir havuz sorgusu olan LOCATION='/webdata/' SQL, veri kümesinden satırlar mydata.txt. Bir alt klasörde mydata2.txt mydata3.txt dosyaları ve dosyaları geri dönmez. Hadoop tabloları herhangi bir alt klasör içindeki tüm dosyaları geri dönecektir.

Hem Hadoop hem de yerel dış tablolar, alt çizgi (_) veya nokta (.) ile başlayan adlara sahip dosyaları atlar.

DATA_SOURCE = external_data_source_name

Dış verilerin konumunu içeren dış veri kaynağının adını belirtir. Dış veri kaynağı oluşturmak için CREATE EXTERNAL DATA SOURCE kullanın.

FILE_FORMAT = external_file_format_name

Dış veriler için dosya türünün ve sıkıştırma yönteminin depolandığı dış dosya biçimi nesnesinin adını belirtir. Dış dosya biçimi oluşturmak için CREATE EXTERNAL FILE FORMAT kullanın.

Reddetme Seçenekleri

Hizmetin dış veri kaynağından alınan kirli kayıtları nasıl işleyeceklerini belirleyen reddetme parametrelerini belirtebilirsiniz. Gerçek veri türleri dış tablonun sütun tanımları ile eşleşmezse veri kaydı 'kirli' olarak kabul edilir.

Reddetme seçeneklerini belirtmez veya değiştirmezken hizmet varsayılan değerleri kullanır. Create EXTERNAL TABLE deyimiyle bir dış tablo bilgisayarınızda reddetme parametreleriyle ilgili bu bilgiler ek meta veri olarak depolanır. Gelecekteki bir SELECT deyimi veya SELECT INTO SELECT deyimi dış tablodaki verileri seçerse, hizmet reddetme seçeneklerini kullanarak gerçek sorgu başarısız olmadan önce reddedilecek satır sayısını belirler. Reddetme eşiği aşılana kadar sorgu (kısmi) sonuçlar verir. Ardından uygun hata iletisiyle başarısız olur.

REJECT_TYPE = değer

Şu anda desteklenen tek değer bu. REJECT_VALUE seçeneğinin değişmez değer olarak belirlendi olduğunu netler.

değer

REJECT_VALUE bir değişmez değerdir. Reddedilen satır sayısı, sorguyu reject_value.

Örneğin, REJECT_VALUE = 5 ve REJECT_TYPE = değerine sahipse, beş satır reddedildikten sonra SELECT sorgusu başarısız olur.

REJECT_VALUE = reject_value

Sorgu başarısız olmadan önce reddedilecek satır sayısını belirtir.

REJECT_TYPE = değer için reject_value 0 ile 2.147.483.647 arasında bir tamsayı olmalıdır.

REJECTED_ROW_LOCATION = Dizin Konumu

Dış Veri Kaynağı içinde reddedilen satırların ve karşılık gelen hata dosyasının yazıldığı dizini belirtir. Belirtilen yol yoksa, hizmet sizin için bir yol oluşturacak. "rejectedrows" adıyla bir alt dizin oluşturulur. " " karakteri, konum parametresinde açıkça adlandırılmış olmadığı sürece dizinin diğer veri işleme için kaçması sağlar. Bu dizinde, yükleme gönderim zamanlarına göre oluşturulan bir klasör vardır ve bu klasör YearMonthDay_HourMinuteSecond_StatementID (Ör. 20180330-173205-559EE7D2-196D-400A-806D-3BF5D007F891). Deyimin kimliğini kullanarak klasörü oluşturan sorguyla arasında ilişki açabilirsiniz. Bu klasörde iki dosya yazılır: error.json dosyası ve veri dosyası.

error.json dosyası, reddedilen satırlarla ilgili hatalarla karşılaşan json dizisi içeriyor. Hatayı temsil eden her öğe aşağıdaki öznitelikleri içerir:

TABLE_OPTIONS

TABLE_OPTIONS = json seçenekleri - Temel alınan dosyaların nasıl okunan seçenekler kümesi olduğunu belirtir. Şu anda kullanılabilen tek seçenek, bazı tutarsız okuma işlemlerine neden olsa bile dış tabloya temel alınan dosyalarda yapılan güncelleştirmeleri "READ_OPTIONS":["ALLOW_INCONSISTENT_READS"] yoksayması talimatıdır. Bu seçeneği yalnızca sık sık dosya ekli olduğunuz özel durumlarda kullanın. Bu seçenek CSV biçimi için sunucusuz SQL havuzunda kullanılabilir.

İzinler CREATE EXTERNAL TABLE

Dış tablodan seçim yapmak için liste ve okuma izinlerine sahip düzgün kimlik bilgilerine ihtiyacınız vardır.

Örnek CREATE EXTERNAL TABLE

Aşağıdaki örnek bir dış tablo oluşturur. İlk satırı döndürür:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Azure Data Lake'te bir dosyadan dış tablolar oluşturma ve sorgulama

Data Lake keşif özelliklerini kullanarak Synapse Studio synapse SQL havuzu kullanarak dosyaya sağ tıklamayla dış tablo oluşturabilir ve sorgu gönderebilirsiniz. Tek tıklamayla depolama hesabından dış tablolar ADLS 2. Nesil hareketi yalnızca Parquet dosyaları için de kullanılabilir.

Önkoşullar

• Dosyaları sorgulamanıza olanak sağlayan ADLS 2. Nesil Hesabına veya Access Control Listelerine (ACL) erişim rolüne sahip çalışma alanına Storage Blob Data Contributor erişiminiz olması gerekir.

• Synapse havuzlarında dış tablolar (ayrılmış veya sunucusuz) oluşturmak ve sorgulamak SQL izinlere sahip olmak gerekir.

Veri panelinden dış tabloyu oluşturmak istediğiniz dosyayı seçin:

Bir iletişim kutusu penceresi açılır. Ayrılmış havuz SQL sunucusuz SQL seçin, tabloya bir ad girin ve betiği aç'ı seçin:

SQL Betiği, şemayı dosyadan çıkararak otomatik olarak uyarıldı:

Betiği çalıştırın. Betik otomatik olarak İlk 100'i Seç *.:

Dış tablo artık oluşturulur ve bu dış tablonun içeriğini daha sonra incelemeniz için kullanıcı doğrudan Veri bölmesinden sorgular:

Sonraki adımlar

Sorgu sonuçlarını Azure'daki bir dış tabloya kaydetme hakkında bilgi için CETAS makalesine Depolama. Veya dış tabloları Apache Spark sorgu Azure Synapse başlayabilirsiniz.