Aracılığıyla paylaş

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

  • Makale

Sunucusuz SQL havuzu, veri gölünüzdeki verileri sorgulamanıza olanak tanır. 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 nelerin desteklendiği hakkında 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.
  • Sas kimlik doğrulamasını veya çalışma alanının Yönetilen Kimliğini kullanarak Synapse SQL'in depolamaya erişmesini nasıl etkinleştirebileceğinizi öğrenebileceğiniz depolama erişimini denetleyin.

Genel bakış

Sunucusuz SQL havuzu, Azure Depolama dosyalarında bulunan verilerin yerinde sorgulanması konusunda sorunsuz bir deneyimi desteklemek için OPENROWSET işlevini ek özelliklerle kullanır:

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' Dosyasındaki kendisinden ve dosyadaki tüm sınırlayıcı değerlerden kaçmak için kullanılan karakteri belirtir. Kaçış karakterinin ardından kendi dışında 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'nin etkin olup olmadığı fark etmese de uygulanır. Alıntı karakterinden kaçmak için kullanılmaz. Alıntılama karakteri başka bir alıntı karakteriyle 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ünebilir.
  • 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, dosyanın şemasını işlevin OPENROWSET bir parçası olarak tanımlamanıza ve sütunların tümünü veya alt kümesini okumanıza olanak tanır 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, deyiminizde 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ı atamayla bağlanır.
  • Parquet veri dosyaları varsa, kaynak veri dosyalarındaki sütun adlarla eşleşen sütun adlarını 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 WITH yazmanız gerekir. Örnekler için tüm sütunları belirtmeden CSV dosyalarını okuma bölümüne bakın.

Şema çıkarımı

deyiminden WITH yan tümcesini OPENROWSET 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 çıkarımlı 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 klasöre veya bir dizi dosya veya klasör üzerinden bir desene (joker karakterler kullanarak) bir yol sağlayın.

Geçerli olan kurallar şunlardır:

  • 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üntülenebilir.
  • 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 makalesindeki Dosya adı bölümünü okuyun.

Dönüş veri türü nvarchar(1024) değeridir. En iyi performans için dosya adı 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, parametresinde belirtilen konumda joker karakterle eşleşen yolun 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ı ile ç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.

proje iç içe veya yinelenen veriler

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 Parquet iç içe türler 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ütundan iç içe öğelere erişmek için alan adlarını yola birleştirmek için "nokta gösterimi" kullanın. İşlevin WITH yan tümcesinde OPENROWSET yolu column_name olarak sağlayın.

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, OPENROWSET işlev kaynak alan adı ve yolu 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.
  • Özellik belirtilen column_name bulunamazsa işlev bir hata döndürür.
  • Özellik, Yol moduna bağlı olarak belirtilen column_path bulunamazsa, işlev katı moddayken veya boş moddayken bir hata döndürür.

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

Yinelenen sütunlardan öğelere erişme

Dizi veya Eşleme öğ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:

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:

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.

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: