Parquet dosyalarını sorgulama

Şunlar için geçerlidir:✅ Microsoft Fabric'te SQL analiz uç noktası ve Ambarı

Bu makalede, Doku Veri Ambarı'nı veya SQL analiz uç noktasını kullanarak Parquet dosyalarından okuma verilerini sorgulamayı öğreneceksiniz.

Doku Veri Ambarı'ndaki OPENROWSET veya SQL analiz uç noktasındaki işlevini kullanarak dosyanın URL'sini sağlayarak Parquet dosyasının içeriğini sorgulayabilirsiniz. İşlevin OPENROWSET söz dizimi şöyledir:

  OPENROWSET(BULK {{parquet-file-url}})
  [ WITH ( {{result set column schema}} ) ]

OPENROWSET şemayı nasıl işler?

varsayılan olarak işlev, doğrudan başvurulan OPENROWSET Parquet dosyalarından sütun adlarını ve veri türlerini tanımlamak için otomatik şema çıkarımı kullanır.

İşlev OPENROWSET , dosyadan sütun meta verilerini okur, böylece verileri açıkça bir şema tanımlamadan sorgulayabilirsiniz.

Birden çok Parquet dosyasına başvurdığınızda, OPENROWSET işlev şemayı eriştiği ilk dosyadan çıkartır. Sonuç olarak, bu sütunlar başka dosyalarda mevcut olsa bile, çıkarsanan şema bu dosyada bulunmayan sütunları atlayabilir. Bu davranış sorgu sonucunda eksik sütunlara veya beklenmeyen veri türlerine yol açabilir.

Tahmin edilebilir ve belirlenimci bir şema sağlamak için işlevdeki yan tümcesini WITHOPENROWSET kullanarak sütun adlarını ve veri türlerini açıkça tanımlayın. Birden çok dosyayı sorgularken veya tüm dosyalar arasında tutarlı sütun yapısı gerektiğinde açık şema tanımını kullanın.

OPENROWSET ile parquet dosyasını oku

Parquet dosyasının içeriğini görüntülemenin en basit yolu, dosya URL'sini doğrudan OPENROWSET işlevine sağlamaktır.

Bu örneklerde kullanılan veri kümeleri şunlardır:

Aşağıdaki örnekte, OPENROWSET deyiminde tam URL'sini belirterek Parquet dosyasının nasıl okunduğu gösterilmektedir.

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet'
);

Bu sorguda:

  • BULK bağımsız değişkeni, depolamadaki Parquet dosyasının tam URI'sini belirtir.
  • OPENROWSET dosya içeriğini okur ve tablo sonuç kümesi olarak döndürür.
  • Sütun adları ve veri türleri Parquet dosya meta verilerinden otomatik olarak çıkarılır.
  • yan tümcesi TOP 10 , verilerin daha kolay incelenmesi için sonuç kümesini sınırlar.

Bu yaklaşım, açık şema tanımı, joker karakter tabanlı dosya erişimi veya dış veri kaynağı yapılandırması gibi daha gelişmiş sorgu teknikleri uygulamadan önce tek tek Parquet dosyalarını hızla keşfetmek veya dosya içeriğini doğrulamak için kullanışlıdır.

Veri kaynağı kullanımı

Önceki örneklerde, dosyanın tam URL'si doğrudan OPENROWSET ifadesinde belirtilmiştir. Alternatif olarak, depolama alanınızın kök konumuna işaret eden bir dış veri kaynağı tanımlayabilir ve ardından göreli yolları kullanarak dosyalara başvurabilirsiniz.

Dış veri kaynağı kullanmak sorguları basitleştirir, okunabilirliği artırır ve depolama konumunu merkezi olarak yönetmenizi sağlar. Depolama uç noktası değişirse, yalnızca veri kaynağı tanımını güncelleştirmeniz gerekir, buna başvuran her sorguyu güncelleştirmeniz gerekmez.

Aşağıdaki örnek, COVID-19 verilerini içeren bir klasöre işaret eden bir dış veri kaynağı oluşturur:

CREATE EXTERNAL DATA SOURCE covid
WITH ( LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );

Dış veri kaynağını oluşturduktan sonra işlevde OPENROWSET buna başvurun ve veri kaynağı konumuna göre bir yol belirtin:

SELECT TOP 10 *
FROM OPENROWSET(
        BULK 'latest/ecdc_cases.parquet',
        DATA_SOURCE = 'covid'
    ) as rows;

Bu örnekte parametresi, DATA_SOURCE dosya yolunun dış veri kaynağına göre çözümlenmesi talimatını OPENROWSET vermektedir.

Aynı depolama hesabından veya kapsayıcıdan sık sık dosya sorgularken dış veri kaynaklarını kullanın. Bu yaklaşım yeniden kullanımı teşvik eder, sürdürülebilirliği artırır ve sorgu tanımlarını kısa tutar.

Şemayı açıkça belirtin

Varsayılan olarak, OPENROWSET kaynak dosyadan sütun adlarını ve veri türlerini belirlemek için otomatik şema çıkarımı kullanır. Ancak birçok senaryoda, birden çok dosyayı sorgularken, kararlı bir şema sağlarken veya yalnızca bir sütun alt kümesi seçerken şemayı açıkça tanımlamak isteyebilirsiniz.

Tam olarak hangi sütunların WITH okunacağını ve sonuç kümesindeki veri türlerini belirtmek için OPENROWSET içindeki yan tümceyi kullanın.

Aşağıdaki örnekte, bir Parquet dosyasından veri okurken şemanın açıkça nasıl tanımlanacağı gösterilmektedir:

SELECT TOP 10 *
FROM OPENROWSET(
        BULK 'latest/ecdc_cases.parquet',
        DATA_SOURCE = 'covid'
    ) WITH ( date_rep date, cases int, geo_id varchar(6) );

Bu örnekte yan tümcesi WITH aşağıdakini belirterek sonuçta elde edilen şemayı tanımlar:

  • Sütun adları (date_rep, cases, geo_id)
  • Sorgu sonucundaki her sütuna uygulanacak veri türleri

Parquet dosyasında fazladan sütunlar olsa bile, WITH koşulunda listelenen sütunlar döndürülür. Bu yaklaşım, okunan veri miktarını azaltır ve sonuç kümesinin şeklini denetler.

Açık şema tanımı özellikle aşağıdaki durumlarda önemlidir:

  • Aynı sütun kümelerine sahip olmayan birden çok dosyayı sorgulama
  • Sorgu yürütmeleri arasında belirlenimci ve kararlı bir şemaya ihtiyacınız var
  • Çıkarsanan veri türlerini geçersiz kılmak veya belirli veri türlerini zorunlu kılmak istiyorsunuz
  • Kaynak dosyalar, eklenen veya isteğe bağlı sütunlarla zaman içinde gelişebilir

Bir şemayı açıkça tanımladığınızda, OPENROWSET şema çıkarımına güvenmez. Bunun yerine, yan tümcesinde WITH sağladığınız sütun tanımlarını uygulayarak tutarlı ve öngörülebilir sorgu sonuçları sağlar.

OPENROWSET ile bir parquet dosya kümesini sorgulama

işlevi, OPENROWSET joker karakter (*) içeren bir dosya yolu belirtmenize olanak tanıyarak tek bir deyimde birden çok dosya sorgulamayı destekler.

Joker karakterler, tek bir dosya yerine bir dosya veya klasör kümesini temsil eden bir URI deseni tanımlamanızı sağlar. Dosya yolunda joker karakter kullanıldığında, belirtilen URI deseni ile eşleşen tüm dosyalar okunur ve tek bir sonuç kümesinde birleştirilir.

Bu yaklaşım genellikle tarihe, bölgeye veya diğer hiyerarşik klasör yapılara göre düzenlenmiş dosyalar gibi bölümlenmiş veriler sorgulanırken kullanılır.

Örneğin, joker karakterleri kullanarak şunları yapabilirsiniz:

  • Belirli bir klasördeki tüm dosyaları sorgulama
  • Dosyaları birden çok alt klasör arasında sorgulama
  • Ortak adlandırma deseni paylaşan sorgu dosyaları
SELECT *
FROM OPENROWSET(
        BULK '/Files/puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi'
     );

Bu örnekte, joker karakterler (*) hizmete belirtilen klasör ve dosya adı deseni ile eşleşen tüm Parquet dosyalarını okumasını ister.

Joker karakter tabanlı dosya desenleri, tam dosya adlarını bilmenize gerek kalmadan büyük dosya koleksiyonlarını doğrudan depolamadan sorgulamak için esnek ve ölçeklenebilir bir yol sağlar.

Uyarı

Bu örnek, Lakehouse'unuzda SQL analiz uç noktası aracılığıyla dosyaları sorgularken çalışan veri kaynağı olmayan göreli bir yol kullanır. Fabric Veri Ambarı'nda şunlardan birini yapmanız gerekir:

  • Dosyanın mutlak yolunu kullanın veya
  • Dış veri kaynağında bir ana URL belirtin ve OPENROWSET ifadesinde DATA_SOURCE seçeneğini kullanarak buna başvurun.

OPENROWSET ile bölümlenmiş verileri sorgulama

Bir veri gölünde depolanan veri kümelerinin alt klasörlere bölümlenmesi performansı ve kuruluşu geliştirir.

Yaygın bir desen, klasör adlarının yıl, ay veya bölge gibi bölüm değerlerini kodladığı Hive veya Hadoop stili bölümlemedir.

Bu örnekte kullanılan örnek veri kümesi, teslim alma yılına () ve teslim alma ayınapuYear (puMonth) göre ayrı alt klasörlere ayrılmıştır. İşlev, OPENROWSET bu bölümleme düzenini kullanarak düzenlenmiş verileri doğrudan okuyabilir.

Aşağıdaki örnek bölümlenmiş Parquet verilerini sorgulamayı ve dosya yolundan bölüm değerlerini ayıklamayı gösterir. 2017'nin ilk üç ayı için seyahat verilerini döndürür:

SELECT
        CAST( nyc.filepath(2) AS INT), *
FROM  
    OPENROWSET(
        BULK '/Files/puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi'
    ) AS nyc
WHERE
    nyc.filepath(1) = '2017'
    AND nyc.filepath(2) IN (1, 2, 3);

OPENROWSET ile karmaşık türleri okuma

Fabric Veri Ambarı ve SQL analitik uç noktası, yapı veya kayıt, dizi veya yinelenen alanlar gibi karmaşık türler içeren Parquet sütunlarını okuyabilir. Bu sütunları aracılığıyla OPENROWSETyansıttığınızda, sorgu bu karmaşık sütunlardaki değerleri JSON metni olarak döndürür ve bunları varchar sütunları olarak gösterir:

  • Yapılar (kayıtlar) JSON nesneleri olarak döndürülür, örneğin, {"a":1,"b":"x"}.
  • Diziler (yinelenen alanlar) JSON dizileri olarak döndürülür; örneğin, [1,2,3].

Aşağıdaki örnekte, aynı Parquet dosyasından bir yapıyı ve diziyi okuyup JSON metni olarak döndürme gösterilmektedir. JSON nesnesi ve dizi sütunları varchar(max)olarak yansıtılır.

SELECT
 StructCol,  -- JSON object (for example, {"id":123,"name":"Ada","tags":["ml","sql"]})
 ArrayCol    -- JSON array  (for example, [11,12,13])
FROM    
 OPENROWSET(
  BULK '/Files/parquet/nested/complexExample.parquet',
  DATA_SOURCE = 'MyDataSource')
 WITH ( -- Project complex Parquet columns as VARCHAR to receive JSON text.
       StructCol VARCHAR(MAX),
       ArrayCol  VARCHAR(MAX)    );

İç içe her değerin içeriği JSON metni olarak döndürülür.

yapısütun ArrayCol
{"id":123,"name":"Ada","tags":["ml","sql"]} [11,12,13]
{"id":456,"name":"Nik","tags":["dw","ai"]} [21,22]
  • Karmaşık Parquet sütunlarından döndürülen daha büyük JSON yüklerini güvenli bir şekilde barındırmak için varchar(8000) veya varchar(max) gibi daha büyük bir tür kullanın.
  • Daha fazla varchar sütunu ekleyerek, iç içe yapılar veya yapı dizileri gibi ek karmaşık alanları cümlenin WITH bölümünde yansıtabilirsiniz. Her karmaşık alan JSON metni olarak döndürür.

İç içe türlerin özelliklerine veya öğelerine erişme

Çoğu durumda, karmaşık türün tamamına JSON olarak ihtiyacınız yoktur. Filtreleme, birleştirme veya analiz için belirli özelliklere veya öğelere ihtiyacınız vardır.

Bu özelliklere veya öğelere üç şekilde erişebilirsiniz:

  • JSON işlevlerini kullanın - karmaşık sütunlardan döndürülen JSON metni üzerinde JSON_VALUE (skaler) veya JSON_QUERY (nesne/dizi) uygulayın.
  • Noktalı yol ile belirtilen sütun adları (yapılar) - bir yapının iç içe geçmiş alanını doğrudan skaler bir sütuna yansıtmak için . ayracı ile ayrılmış sütun adını (örneğin, [Struct.prop] TYPE) WITH ifadesinde belirtin.
  • Türden sonra JSON yolu - İç içe geçmiş bir skaler değeri ayıklamak için WITH cümlesinde türden hemen sonra bir JSON yol ifadesi (örneğin, Col INT '$.Struct.prop') sağlayın.

Aşağıdaki örnekte Parquet'de iç içe yerleştirilmiş alanlara nasıl erişileceği gösterilmektedir:

SELECT
 -- Raw complex values (JSON text)
 UserStruct,                 -- for example, {"id":123,"name":"Ada","scores":[11,12,13]}
 ScoresArray,                -- for example, [11,12,13]

 -- Access struct fields via JSON_VALUE
 JSON_VALUE(UserStruct, '$.id')   AS UserId_json,
 JSON_VALUE(UserStruct, '$.name') AS UserName_json,

 -- Access array elements via JSON_VALUE (0-based)
 JSON_VALUE(ScoresArray, '$[0]')  AS Score1,
 JSON_VALUE(ScoresArray, '$[1]')  AS Score2,
 JSON_VALUE(ScoresArray, '$[2]')  AS Score3

FROM
 OPENROWSET(
    BULK '/Files/parquet/nested/complexExample.parquet',
    DATA_SOURCE = 'MyDataSource',
    FORMAT = 'PARQUET'
 )
 WITH (
    -- Complex columns as JSON text
    UserStruct  VARCHAR(8000) '$.UserStruct',
    ScoresArray VARCHAR(8000) '$.ScoresArray',

    -- Option A: JSON path after type
    UserId      INT           '$.UserStruct.id',
    UserName    VARCHAR(200)  '$.UserStruct.name',

    -- Option B: Dot-path column names
    [UserStruct.id]    INT,
    [UserStruct.name]  VARCHAR(200)
) AS r;

Aşağıdaki tabloda, farklı yaklaşımlar kullanarak hem ham karmaşık sütunları hem de ayıklanan özelliklerin projelendiği sorgu tarafından döndürülen örnek sonuçlar gösterilmektedir:

UserStruct ScoresArray UserId_fromJsonValue UserName_fromJsonValue UserId UserName UserStruct.id UserStruct.name
{"id":123,"name":"Ada","scores":[11,12,13]} [11,12,13] 123 Ada 123 Ada 123 Ada
{"id":456,"name":"Nik","scores":[21,22,23]} [21,22,23] 456 Nik 456 Nik 456 Nik

Dizileri genişletme

Parquet dosyası, sabit dizinlerle değerlere ulaşmanın iyi bir çözüm olmadığı bir dizide değişken sayıda öğe içerebilir. Bu parquet dosyası türüne bir örnek aşağıdaki tabloda gösterilmiştir:

Kimlik Array
1 [11,12,13]
2 [21,22]

Bu durumda, kullanarak OPENJSON diziyi genişletebilir ve öğelerini üst satırla birleştirebilirsiniz. Parquet sütunu bir dizi içerdiğinde (örneğin, [1,2,3]), öğelerini düzleştirmeyi (genişletmeyi) ve böylece her dizi öğesinin özgün üst satır verilerinin yanında ayrı bir satırda görünmesini isteyebilirsiniz.

Bunu, dizi sütunundan döndürülen JSON metnine uygulayarak CROSS APPLY OPENJSON(...) yapabilirsiniz.

SELECT
  r.Id,      -- Example parent column (if present in your file)
  r.Array,   -- Raw JSON array text
  a.Element  -- Flattened scalar value from the array
FROM
  OPENROWSET(
        BULK '/Files/parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'MyDataSource'
        ) 
    WITH ( Id INT, Array VARCHAR(MAX) ) 
    AS r
CROSS APPLY OPENJSON(r.Array) 
  WITH (Element INT '$' ) AS a;

OPENROWSET dizi sütununu (Array) JSON dizisi olarak işler ve dizi öğesi başına bir satır döndürür; böylece dizi değerlerini üst sütunlarla birleştirebilirsiniz (örneğin, Id). Bu sorgunun sonucu şöyle görünebilir:

Kimlik Array Öğe
1 [11,12,13] 11
1 [11,12,13] 12
1 [11,12,13] 13
2 [21,22,23] 21
2 [21,22,23] 22

Nesne dizileri için, nesne özelliklerini eşlemek için içindeki WITH yan tümcesini OPENJSON değiştirin, örneğin:

CROSS APPLY OPENJSON(r.Array)
WITH (
    ItemId   INT,
    ItemName VARCHAR(200) ) AS a

Tip eşlemesi

Parquet dosyaları her sütun için tür açıklamaları içerir. Aşağıdaki tabloda Parquet türlerinin işlevin döndürdüğü SQL yerel türleriyle nasıl eşleneceği OPENROWSET açıklanmaktadır.

Parquet türü Parquet mantıksal türü (ek açıklama) SQL veri türü
BOOLEAN bit
İKİLİ / BYTE_ARRAY varbinary
ÇİFT float
FLOAT Gerçek
INT32 int
INT64 bigint
INT96 datetime2
Sabit_Uzunluk_Bayt_Dizisi (FIXED_LEN_BYTE_ARRAY) binary
BINARY UTF8 Varchar *
BINARY STRING Varchar *
BINARY ENUM Varchar *
FIXED_LEN_BYTE_ARRAY UUID uniqueidentifier
BINARY ONDALIK decimal
BINARY JSON varchar(MAX) *
BINARY BSON Desteklenmez
Sabit_Uzunlukta_Bayt_Dizisi ONDALIK decimal
BYTE_DİZİSİ INTERVAL Desteklenmez
INT32 INT(8, doğru) smallint
INT32 INT(16, true) smallint
INT32 INT(32, true) int
INT32 INT(8, false) tinyint
INT32 INT(16, yanlış) int
INT32 INT(32, false) bigint
INT32 DATE date
INT32 ONDALIK decimal
INT32 ZAMAN (MİLİSANİYE) time
INT64 INT(64, doğru) bigint
INT64 INT(64, yanlış) decimal(20,0)
INT64 ONDALIK decimal
INT64 ZAMAN (MICROS) time
INT64 ZAMAN (NANOS) Desteklenmez
INT64 TIMESTAMP (utc olarak normalleştirilmiş) (MILLIS / MICROS) datetime2
INT64 TIMESTAMP (utc'ye normalleştirilmemiş) (MILLIS / MICROS) Bigint **
INT64 ZAMAN DAMGASı (NANO'LAR) Desteklenmez
Karmaşık tür LİSTE varchar(max), JSON olarak serileştirilmiş
Karmaşık tür MAP varchar(max), JSON olarak serileştirilmiş

* (UTF8 harmanlama)

** Bigint değerini tarih saat değerine dönüştürmeden önce saat dilimi uzaklığıyla açıkça ayarlamanız gerekir.

İlgili içerik

  • Last updated on