Azure Synapse Analytics'te sunucusuz SQL havuzuyla depolama dosyalarını sorgulama

Makale
06/01/2023

Sunucusuz SQL havuzu, veri gölünüzdeki verileri sorgulamanızı sağlar. Yarı yapılandırılmış ve yapılandırılmamış veri sorgularını barındıran bir T-SQL sorgu yüzeyi alanı sunar. Sorgulama için aşağıdaki T-SQL özellikleri desteklenir:

SQL işlevlerinin ve işleçlerinin çoğu dahil olmak üzere tam SELECT yüzey alanı.
CREATE EXTERNAL TABLE AS SELECT (CETAS), bir dış tablo oluşturur ve ardından Transact-SQL SELECT deyiminin sonuçlarını paralel olarak Azure Depolama'ya aktarır.

Şu anda desteklenmeyenler ile ilgili daha fazla bilgi için sunucusuz SQL havuzuna genel bakış makalesini veya aşağıdaki makaleleri okuyun:

Depolamadan veri okumak için Dış tablo ve OPENROWSET işlevini kullanmayı öğrenebileceğiniz depolama erişimi geliştirin.
Synapse SQL'in SAS kimlik doğrulamasını veya çalışma alanının Yönetilen Kimliğini kullanarak depolamaya erişmesini nasıl etkinleştireceğinizi öğrenebileceğiniz depolama erişimini denetleyin.

Genel Bakış

Azure Depolama dosyalarında bulunan verilerin yerinde sorgulanmasıyla ilgili sorunsuz bir deneyimi desteklemek için sunucusuz SQL havuzu ek özelliklere sahip OPENROWSET işlevini kullanır:

Birden çok dosya veya klasör sorgulama
PARQUET dosya biçimi
CSV'yi ve sınırlandırılmış metni sorgulama (alan sonlandırıcısı, satır sonlandırıcısı, kaçış karakteri)
DELTA LAKE biçimi
Seçilen sütun alt kümesini okuma
Şema çıkarımı
filename işlevi
filepath işlevi
Karmaşık türler ve iç içe veya yinelenen veri yapılarıyla çalışma

PARQUET dosyalarını sorgulama

Parquet kaynak verilerini sorgulamak için FORMAT = 'PARQUET' kullanın:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Kullanım örnekleri için Sorgu Parquet dosyaları makalesini gözden geçirin.

CSV dosyalarını sorgulama

CSV kaynak verilerini sorgulamak için FORMAT = 'CSV' kullanın. CSV dosyalarını sorgularken işlevin bir parçası olarak CSV dosyasının OPENROWSET şemasını belirtebilirsiniz:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Ayrıştırma kurallarını özel CSv biçimine ayarlamak için kullanılabilecek bazı ek seçenekler vardır:

ESCAPE_CHAR = 'char' Dosyadaki kendisinden ve dosyadaki tüm sınırlayıcı değerlerden kaçmak için kullanılan karakteri belirtir. Kaçış karakterinin ardından kendisinden başka bir değer veya sınırlayıcı değerlerden biri gelirse, değer okunurken kaçış karakteri bırakılır. ESCAPE_CHAR parametresi, FIELDQUOTE etkin veya etkinleştirilmemiş olsa da uygulanır. Alıntı karakterinden kaçmak için kullanılmaz. Alıntı karakteri başka bir alıntı karakteriyle birlikte kaçış karakteri olmalıdır. Alıntı karakteri, sütun değeri içinde yalnızca değer, alıntı karakterleriyle kapsüllenmişse görüntülenebilir.
FIELDTERMINATOR ='field_terminator' Kullanılacak alan sonlandırıcısını belirtir. Varsayılan alan sonlandırıcısı virgüldür (",")
ROWTERMINATOR ='row_terminator' Kullanılacak satır sonlandırıcısını belirtir. Varsayılan satır sonlandırıcısı yeni satır karakteridir: \r\n.

DELTA LAKE biçimini sorgulama

Delta Lake kaynak verilerini sorgulamak için FORMAT = 'DELTA' kullanın ve Delta Lake dosyalarınızı içeren kök klasöre başvurun.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Kök klasör adlı _delta_logbir alt klasör içermelidir. Kullanım örnekleri için Delta Lake biçimli sorgu makalesini gözden geçirin.

Dosya şeması

Synapse SQL'de SQL dili, işlevin bir parçası OPENROWSET olarak dosyanın şemasını tanımlamanızı ve sütunların tümünü veya alt kümesini okumanızı sağlar ya da şema çıkarımı kullanarak dosyadan sütun türlerini otomatik olarak belirlemeye çalışır.

Seçilen sütun alt kümesini okuma

Okumak istediğiniz sütunları belirtmek için deyiminizin içinde isteğe bağlı bir WITH yan tümcesi sağlayabilirsiniz OPENROWSET .

CSV veri dosyaları varsa, tüm sütunları okumak için sütun adlarını ve bunların veri türlerini sağlayın. Sütunların bir alt kümesini istiyorsanız, sıralı sayıları kullanarak kaynak veri dosyalarındaki sütunları sıralı olarak seçin. Sütunlar sıralı atamaya bağlı olacaktır.
Parquet veri dosyaları varsa, kaynak veri dosyalarındaki sütun adlarla eşleşen sütun adları sağlayın. Sütunlar ada göre bağlanır.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows

Her sütun için sütun adını belirtmeniz ve yan tümcesi yazmanız WITH gerekir. Örnekler için tüm sütunları belirtmeden CSV dosyalarını okuma bölümüne bakın.

Şema çıkarımı

deyiminden OPENROWSET WITH yan tümcesini atlayarak, hizmete şemayı temel alınan dosyalardan otomatik olarak algılamasını (çıkarmasını) belirtebilirsiniz.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

En iyi performans için uygun çıkarsanan veri türlerinin kullanıldığından emin olun.

Birden çok dosya veya klasör sorgulama

Bir klasör veya klasör kümesi içindeki bir dosya kümesi üzerinde T-SQL sorgusu çalıştırmak ve bunları tek bir varlık veya satır kümesi olarak ele almak için, bir dosya veya klasör kümesi üzerinde bir klasöre veya desene (joker karakterler kullanarak) bir yol sağlayın.

Aşağıdaki kurallar geçerlidir:

Desenler dizin yolunun bir bölümünde veya dosya adında görünebilir.
Aynı dizin adımında veya dosya adında çeşitli desenler görünebilir.
Birden çok joker karakter varsa, eşleşen tüm yollardaki dosyalar sonuçta elde edilen dosya kümesine eklenir.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Kullanım örnekleri için Sorgu klasörlerine ve birden çok dosyaya bakın.

Dosya meta veri işlevleri

Filename işlevi

Bu işlev, satırın kaynaklandığı dosya adını döndürür.

Belirli dosyaları sorgulamak için Belirli dosyaları sorgulama makalesinin Dosya adı bölümünü okuyun.

Dönüş veri türü nvarchar(1024) değeridir. En iyi performans için filename işlevinin sonucunu her zaman uygun veri türüne dönüştürebilirsiniz. Karakter veri türü kullanıyorsanız uygun uzunluğun kullanıldığından emin olun.

Filepath işlevi

Bu işlev tam yolu veya yolun bir bölümünü döndürür:

Parametresiz çağrıldığında, bir satırın kaynaklandığı tam dosya yolunu döndürür.
parametresiyle çağrıldığında, yolun parametresinde belirtilen konumda joker karakterle eşleşen bir bölümünü döndürür. Örneğin, parametre değeri 1, yolun ilk joker karakterle eşleşen bir bölümünü döndürür.

Ek bilgi için Belirli dosyaları sorgulama makalesinin Dosya yolu bölümünü okuyun.

Dönüş veri türü nvarchar(1024) değeridir. En iyi performans için dosya yolu işlevinin sonucunu her zaman uygun veri türüne dönüştürebilirsiniz. Karakter veri türü kullanıyorsanız uygun uzunluğun kullanıldığından emin olun.

Karmaşık türler ve iç içe veya yinelenen veri yapılarıyla çalışma

Parquet dosyaları gibi iç içe veya yinelenen veri türlerinde depolanan verilerle sorunsuz bir deneyim sağlamak için sunucusuz SQL havuzu aşağıdaki uzantıları eklemiştir.

İç içe veya yinelenen verileri yansıtma

Verileri yansıtmak için, iç içe veri türlerinin sütunlarını içeren Parquet dosyası üzerinde bir SELECT deyimi çalıştırın. Çıktıda iç içe değerler JSON olarak seri hale getirilir ve varchar(8000) SQL veri türü olarak döndürülür.

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Daha ayrıntılı bilgi için Sorgu Parke iç içe türleri makalesinin Proje iç içe veya yinelenen veriler bölümüne bakın.

İç içe sütunlardan öğelere erişme

Yapı gibi iç içe yerleştirilmiş bir sütundaki iç içe öğelere erişmek için alan adlarını yola birleştirmek için "nokta gösterimi" kullanın. yolu işlevin WITH yan tümcesinde OPENROWSET column_name olarak belirtin.

Söz dizimi parçası örneği aşağıdaki gibidir:

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ({'column_name' 'column_type',})
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

Varsayılan olarak işlev, OPENROWSET kaynak alan adı ve yolu ile WITH yan tümcesinde sağlanan sütun adlarıyla eşleşir. Aynı kaynak Parquet dosyası içinde farklı iç içe yerleştirme düzeylerinde bulunan öğelere WITH yan tümcesi aracılığıyla erişilebilir.

Dönüş değerleri

İşlev, belirtilen öğeden ve belirtilen yolda, İç İçe Tür grubunda olmayan tüm Parquet türleri için int, ondalık ve varchar gibi bir skaler değer döndürür.
Yol, İç İçe Türündeki bir öğeyi işaret ederse, işlev belirtilen yoldaki en üst öğeden başlayan bir JSON parçası döndürür. JSON parçası varchar(8000) türündedir.
Özelliği belirtilen column_name bulunamazsa işlev bir hata döndürür.
Özellik belirtilen column_path bulunamazsa, Yol moduna bağlı olarak, işlev katı moddayken veya lax modunda null olduğunda bir hata döndürür.

Sorgu örnekleri için Sorgu Parquet iç içe türler makalesinin İç içe sütunlardan öğelere erişme bölümünü gözden geçirin.

Yinelenen sütunlardan öğelere erişme

Dizi veya Eşlem öğesi gibi yinelenen bir sütundaki öğelere erişmek için yansıtmanız ve sağlamanız gereken her skaler öğe için JSON_VALUE işlevini kullanın:

İlk parametre olarak iç içe veya yinelenen sütun
İkinci parametre olarak erişecek öğeyi veya özelliği belirten JSON yolu

Yinelenen bir sütundan skaler olmayan öğelere erişmek için yansıtmanız ve sağlamanız gereken skaler olmayan her öğe için JSON_QUERY işlevini kullanın:

İlk parametre olarak iç içe veya yinelenen sütun
İkinci parametre olarak erişecek öğeyi veya özelliği belirten JSON yolu

Aşağıdaki söz dizimi parçasına bakın:

    SELECT
       { JSON_VALUE (column_name, path_to_sub_element), }
       { JSON_QUERY (column_name [ , path_to_sub_element ]), )
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Yinelenen sütunlardan öğelere erişmek için sorgu örneklerini Sorgu Parquet iç içe türler makalesinde bulabilirsiniz.

Sorgu örnekleri

Örnek sorguları kullanarak çeşitli veri türlerini sorgulama hakkında daha fazla bilgi edinebilirsiniz.

Araçlar

Sorgu göndermek için ihtiyacınız olan araçlar: - Azure Synapse Studio - Azure Data Studio - SQL Server Management Studio

Tanıtım kurulumu

İlk adımınız sorguları yürüteceğiniz bir veritabanı oluşturmaktır . Ardından, bu veritabanında kurulum betiğini yürüterek nesneleri başlatacaksınız.

Bu kurulum betiği, bu örneklerdeki verileri okumak için kullanılan veri kaynaklarını, veritabanı kapsamlı kimlik bilgilerini ve dış dosya biçimlerini oluşturur.

Not

Veritabanları yalnızca meta verileri görüntülemek için kullanılır, gerçek veriler için kullanılmaz. Kullandığınız veritabanı adını bir yere yazın; daha sonra ihtiyacınız olacak.

CREATE DATABASE mydbname;

Sağlanan tanıtım verileri

Tanıtım verileri aşağıdaki veri kümelerini içerir:

NYC Taksi - Sarı Taksi Seyahat Kayıtları - CSV ve Parquet biçiminde genel NYC veri kümesinin bir parçası
CSV biçimindeki popülasyon verileri kümesi
İç içe sütunlar içeren örnek Parquet dosyaları
JSON biçiminde kitaplar

Klasör yolu	Açıklama
/Csv/	CSV biçimindeki veriler için üst klasör
/csv/population/ /csv/population-unix/ /csv/population-unix-hdr/ /csv/population-unix-hdr-escape /csv/population-unix-hdr-quoted	Farklı CSV biçimlerinde Population veri dosyalarını içeren klasörler.
/csv/taxi/	NYC ortak veri dosyalarını CSV biçiminde içeren klasör
/Parke/	Parquet biçimindeki veriler için üst klasör
/parquet/taksi	Hive/Hadoop bölümleme şeması kullanılarak yıla ve aya göre bölümlenmiş, Parquet biçiminde NYC genel veri dosyaları.
/parquet/iç içe/	İç içe sütunlar içeren örnek Parquet dosyaları
/Json/	JSON biçimindeki veriler için üst klasör
/json/books/	Kitap verileriyle JSON dosyaları

Sonraki adımlar

Farklı dosya türlerini sorgulama ve görünümleri oluşturma ve kullanma hakkında daha fazla bilgi için aşağıdaki makalelere bakın:

Aracılığıyla paylaş