Azure Synapse Analytics'te sunucusuz SQL havuzunu kullanarak Parquet dosyalarını sorgulama

Makale
10/18/2023

Bu makalede, Parquet dosyalarını okuyacak sunucusuz SQL havuzunu kullanarak sorgu yazmayı öğreneceksiniz.

Hızlı başlangıç örneği

OPENROWSET işlevi, dosyanızın URL'sini sağlayarak parquet dosyasının içeriğini okumanızı sağlar.

Parquet dosyasını okuma

Dosyanızın PARQUET içeriğini görmenin en kolay yolu, işlev için dosya URL'si OPENROWSET sağlamak ve parquet belirtmektir FORMAT. Dosya genel kullanıma açıksa veya Microsoft Entra kimliğiniz bu dosyaya erişebiliyorsa, aşağıdaki örnekte gösterildiği gibi sorguyu kullanarak dosyanın içeriğini görebilmeniz gerekir:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

Bu dosyaya erişebildiğinizden emin olun. Dosyanız SAS anahtarı veya özel Azure kimliği ile korunuyorsa SQL oturum açma bilgileri için sunucu düzeyinde kimlik bilgilerini ayarlamanız gerekir.

Önemli

PARQUET dosyalarındaki dize değerleri UTF-8 kodlaması kullanılarak kodlandığından, UTF-8 veritabanı harmanlaması (örneğin Latin1_General_100_BIN2_UTF8) kullandığınızdan emin olun. PARQUET dosyasındaki metin kodlaması ile harmanlama arasındaki uyuşmazlık beklenmeyen dönüştürme hatalarına neden olabilir. Aşağıdaki T-SQL deyimini kullanarak geçerli veritabanının varsayılan harmanlamasını kolayca değiştirebilirsiniz: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Harmanlamalar hakkında daha fazla bilgi için bkz . Synapse SQL için desteklenen harmanlama türleri.

Harmanlamayı kullanırsanız Latin1_General_100_BIN2_UTF8 diğer harmanlamalarla karşılaştırıldığında ek bir performans artışı elde edersiniz. Harmanlama Latin1_General_100_BIN2_UTF8 , parquet dizesi sıralama kurallarıyla uyumludur. SQL havuzu, sorgularda gerekli verileri (dosya/sütun segment ayıklama) içermeyecek parquet dosyalarının bazı bölümlerini ortadan kaldırabiliyor. Diğer harmanlamaları kullanırsanız, parquet dosyalarındaki tüm veriler Synapse SQL'e yüklenir ve filtreleme SQL işlemi içinde gerçekleştirilir. Harmanlama, Latin1_General_100_BIN2_UTF8 yalnızca parquet ve Cosmos DB için çalışan ek performans iyileştirmesine sahiptir. Dezavantajı, büyük/küçük harf duyarsızlığı gibi ayrıntılı karşılaştırma kurallarını kaybetmenizdir.

Veri kaynağı kullanımı

Önceki örnek dosyanın tam yolunu kullanır. Alternatif olarak, depolamanın kök klasörüne işaret eden konumuyla bir dış veri kaynağı oluşturabilir ve bu veri kaynağını ve işlevdeki OPENROWSET dosyanın göreli yolunu kullanabilirsiniz:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

Bir veri kaynağı SAS anahtarı veya özel kimlikle korunuyorsa, veri kaynağını veritabanı kapsamlı kimlik bilgileriyle yapılandırabilirsiniz.

Şemayı açıkça belirtin

OPENROWSET , yan tümcesini kullanarak WITH dosyadan hangi sütunları okumak istediğinizi açıkça belirtmenizi sağlar:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Önemli

Yan tümcedeki WITH tüm dize sütunları için utf-8 harmanlaması (örneğinLatin1_General_100_BIN2_UTF8) belirtirken açık olduğunuzdan emin olun veya veritabanı düzeyinde bazı UTF-8 harmanlaması ayarlayın. Dosyadaki metin kodlaması ile dize sütunu harmanlaması arasındaki uyuşmazlık beklenmeyen dönüştürme hatalarına neden olabilir. Aşağıdaki T-SQL deyimini kullanarak geçerli veritabanının varsayılan harmanlamasını kolayca değiştirebilirsiniz: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Harmanlama türlerini kolayca ayarlayabilirsiniz, örneğin: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 Harmanlamalar hakkında daha fazla bilgi için bkz . Synapse SQL için desteklenen harmanlama türleri.

Aşağıdaki bölümlerde, çeşitli PARQUET dosyası türlerini sorgulamayı görebilirsiniz.

Ön koşullar

İlk adımınız, NYC Yellow Taxi depolama hesabına başvuran bir veri kaynağına sahip bir veritabanı oluşturmaktır. Ardından bu veritabanında kurulum betiğini yürüterek nesneleri başlatın. Bu kurulum betiği, bu örneklerde kullanılan veri kaynaklarını, veritabanı kapsamlı kimlik bilgilerini ve dış dosya biçimlerini oluşturur.

Veri kümesi

Bu örnekte NYC Yellow Taxi veri kümesi kullanılmıştır. Parquet dosyalarını CSV dosyalarını okuduğunuz gibi sorgulayabilirsiniz. Tek fark, parametresinin FILEFORMAT olarak PARQUETayarlanması gerektiğidir. Bu makaledeki örneklerde Parquet dosyalarını okumanın özellikleri gösterilmektedir.

Parquet dosyalarının sorgu kümesi

Parquet dosyalarını sorgularken yalnızca ilgilendiğiniz sütunları belirtebilirsiniz.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Otomatik şema çıkarımı

Parquet dosyalarını okurken OPENROWSET WITH yan tümcesini kullanmanız gerekmez. Sütun adları ve veri türleri Parquet dosyalarından otomatik olarak okunur.

Aynı anda dosya sayısını okuyorsanız şemanın, sütun adlarının ve veri türlerinin depolama alanından alınan ilk dosya hizmetinden çıkarılacağını unutmayın. Bu, beklenen bazı sütunların atlanacağı anlamına gelebilir, çünkü şemayı tanımlamak için hizmet tarafından kullanılan dosya bu sütunları içermiyor. Şemayı açıkça belirtmek için lütfen OPENROWSET WITH yan tümcesini kullanın.

Aşağıdaki örnekte Parquet dosyaları için otomatik şema çıkarımı özellikleri gösterilmektedir. Eylül 2018'de bir şema belirtmeden satır sayısını döndürür.

Dekont

Parquet dosyalarını okurken OPENROWSET WITH yan tümcesinde sütunları belirtmeniz gerekmez. Bu durumda sunucusuz SQL havuzu sorgu hizmeti Parquet dosyasında meta verileri kullanır ve sütunları ada göre bağlar.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

Bölümlenmiş verileri sorgulama

Bu örnekte sağlanan veri kümesi ayrı alt klasörlere bölünür (bölümlenir). Dosya yolu işlevini kullanarak belirli bölümleri hedefleyebilirsiniz. Bu örnekte, 2017'nin ilk üç ayı için yıla, aya ve payment_type göre ücret tutarları gösterilmektedir.

Dekont

Sunucusuz SQL havuzu sorgusu Hive/Hadoop bölümleme düzeniyle uyumludur.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Tür eşlemesi

SQL yerel türüne parquet türü eşlemesi için Parquet için tür eşlemesini denetleyin.

Sonraki adımlar

Parquet iç içe türleri sorgulamayı öğrenmek için sonraki makaleye ilerleyin.

Aracılığıyla paylaş