Aracılığıyla paylaş

JSON belgelerini SQL Server'a aktarma

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümler Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Bu makalede JSON dosyalarının SQL Server'a nasıl aktarıldığı açıklanır. JSON belgeleri, uygulama günlükleri, algılayıcı verileri vb. gibi birçok veri türünü depolar. Dosyalarda depolanan JSON verilerini okuyabilmek, verileri SQL Server'a yükleyebilmek ve analiz edebilmek önemlidir.

Bu makaledeki örneklerde, kitap listesi içeren bir GitHub örneği JSON dosyası kullanılmaktadır.

Permissions

Örnek düzeyinde bu özellik, bulkadmin sabit sunucu rolünün veya ADMINISTER BULK OPERATIONS izinlerinin üyeliğini gerektirir.

Veritabanı düzeyinde bu özellik için ADMINISTER DATABASE BULK OPERATIONS izinleri gerekir.

Azure Blob Depolama'ya erişmek için okuma-yazma erişimi gerekir.

JSON belgesini tek bir sütuna aktarma

OPENROWSET(BULK), SQL Server'ın bu konuma okuma erişimi varsa yerel sürücüdeki veya ağdaki herhangi bir dosyadan veri okuyabilen tablo değerli bir işlevdir. Dosyanın içeriğini içeren tek sütunlu bir tablo döndürür. OPENROWSET(BULK) işleviyle kullanabileceğiniz ayırıcılar gibi çeşitli seçenekler vardır. Ancak en basit durumda, bir dosyanın içeriğinin tamamını metin değeri olarak yükleyebilirsiniz. (Bu tek büyük değer, tek karakterli büyük nesne veya SINGLE_CLOB olarak bilinir.)

Bir JSON dosyasının içeriğini okuyan ve kullanıcıya tek bir değer olarak döndüren OPENROWSET(BULK) işlevinin bir örneği aşağıda verilmiştir:

SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j;

OPENJSON(BULK) dosyanın içeriğini okur ve BulkColumndöndürür.

Aşağıdaki örnekte gösterildiği gibi dosyanın içeriğini yerel bir değişkene veya tabloya da yükleyebilirsiniz:

-- Load file contents into a variable
DECLARE @json NVARCHAR(MAX);
SELECT @json = BulkColumn
 FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

-- Load file contents into a table
SELECT BulkColumn
INTO #temp
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

JSON dosyasının içeriğini yükledikten sonra, JSON metnini bir tabloya kaydedebilirsiniz.

Azure Dosya Depolama'dan JSON belgelerini içeri aktarma

Sql Server'ın erişebileceği diğer dosya konumlarından JSON dosyalarını okumak için daha önce açıklandığı gibi OPENROWSET(BULK) de kullanabilirsiniz. Örneğin, Azure Dosya Depolama SMB protokollerini destekler. Sonuç olarak aşağıdaki yordamı kullanarak yerel bir sanal sürücüyü Azure Dosya depolama paylaşımına eşleyebilirsiniz:

  1. Azure portalını veya Azure PowerShell'i kullanarak bir dosya depolama hesabı (örneğin, mystorage), bir dosya paylaşımı (örneğin, sharejson) ve Azure Dosya Depolama'da bir klasör oluşturun.

  2. Bazı JSON dosyalarını dosya depolama paylaşımına yükleyin.

  3. Bilgisayarınızda Windows Güvenlik Duvarı'nda port 445'e izin veren bir giden güvenlik duvarı kuralı oluşturun. İnternet servis sağlayıcınız bu bağlantı noktasını engelleyebilir. Aşağıdaki adımda bir DNS hatası (hata 53) alırsanız 445 numaralı bağlantı noktası açık değildir veya ISS'niz bunu engelliyordur.

  4. Azure Dosya Depolama paylaşımını yerel sürücü olarak bağlayın (örneğin T:).

    Komut söz dizimi şöyledir:

    net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]

    Azure Dosya Depolama paylaşımına yerel sürücü harfi T: atayan bir örnek aşağıda verilmiştir:

    net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==

    Depolama hesabı anahtarını ve birincil veya ikincil depolama hesabı erişim anahtarını Azure portalındaki Ayarlar'ın Anahtarlar bölümünde bulabilirsiniz.

  5. Artık aşağıdaki örnekte gösterildiği gibi eşlenmiş sürücüyü kullanarak Azure Dosya Depolama paylaşımından JSON dosyalarınıza erişebilirsiniz:

    SELECT book.*
FROM OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book

Azure Dosya Depolama hakkında daha fazla bilgi için bkz. dosya depolama .

Azure Blob Depolama'dan JSON belgelerini içeri aktarma

Şunlar için geçerlidir: SQL Server 2017 (14.x) ve sonraki sürümleri ve Azure SQL

T-SQL BULK INSERT komutu veya OPENROWSET işleviyle Azure Blob Depolama'dan doğrudan Azure SQL Veritabanı'na dosya yükleyebilirsiniz.

İlk olarak, aşağıdaki örnekte gösterildiği gibi bir dış veri kaynağı oluşturun.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
    CREDENTIAL = MyAzureBlobStorageCredential
);

Ardından, DATA_SOURCE seçeneğiyle bir BULK INSERT komutu çalıştırın.

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

JSON belgelerini satırlara ve sütunlara ayrıştırma

Bir JSON dosyasının tamamını tek bir değer olarak okumak yerine ayrıştırmak ve dosyadaki kitapları ve bunların özelliklerini satır ve sütunlarda döndürmek isteyebilirsiniz.

Örnek 1

En basit örnekte, dosyanın tüm listesini yükleyebilirsiniz.

SELECT value
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn);

Yukarıdaki OPENROWSET dosyadan tek bir metin değeri okur. OPENROWSET değeri BulkColumn olarak döndürür ve BulkColumn'ı OPENJSON işlevine geçirir. OPENJSON, BulkColumn dizisindeki JSON nesneleri dizisi boyunca iterasyon yapar ve her satırda bir kitabı döndürür. Her satır JSON olarak biçimlendirilir ve ardından gösterilir.

{"id":"978-0641723445", "cat":["book","hardcover"], "name":"The Lightning Thief", ... }
{"id":"978-1423103349", "cat":["book","paperback"], "name":"The Sea of Monsters", ... }
{"id":"978-1857995879", "cat":["book","paperback"], "name":"Sophie's World : The Greek", ... }
{"id":"978-1933988177", "cat":["book","paperback"], "name":"Lucene in Action, Second", ... }

Örnek 2

OPENJSON işlevi JSON içeriğini ayrıştırabilir ve bir tabloya veya sonuç kümesine dönüştürebilir. Aşağıdaki örnek içeriği yükler, yüklenen JSON'ı ayrıştırıp beş alanı sütun olarak döndürür:

SELECT book.*
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book;

Bu örnekte, OPENROWSET(BULK) dosyanın içeriğini okur ve bu içeriği çıkış için tanımlanmış bir şemayla OPENJSON işlevine geçirir. OPENJSON, sütun adlarını kullanarak JSON nesnelerindeki özellikleri eşleştirir. Örneğin, price özelliği bir price sütunu olarak döndürülür ve float veri türüne dönüştürülür. Sonuçlar şunlardır:

Id Name price pages_i Author
978-0641723445 Şimşek Hırsızı 12.5 384 Rick Riordan
978-1423103349 Canavarlar Denizi 6.49 304 Rick Riordan
978-1857995879 Sophie'nin Dünyası : Yunan Filozofları 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Second Edition 30.5 475 Michael McCandless

Artık bu tabloyu kullanıcıya döndürebilir veya verileri başka bir tabloya yükleyebilirsiniz.

İlgili içerik

  • Last updated on