व्यायाम - चयन के रूप में बाहरी तालिका बनाएं

Complete

इस अभ्यास में, आप CREATE EXTERNAL TABLE AS SELECT (CETAS) का उपयोग करें:

• लकड़ी की छत के रूप में एक तालिका निर्यात करें।
• ठंडे डेटा को डेटाबेस से स्टोरेज में ले जाएं।
• निर्यात किए गए बाह्य डेटा तक पहुँचने के लिए कोई बाह्य तालिका बनाएँ.
• क्वेरी रणनीतियों के रूप में दृश्यों या वाइल्डकार्ड खोज का उपयोग करें.
• प्रदर्शन को बेहतर बनाने के लिए फ़ोल्डर उन्मूलन और मेटाडेटा जानकारी का उपयोग करके क्वेरीज़ सीमित करें।

पूर्वावश्यकताएँ

• इंटरनेट कनेक्टिविटी के साथ एक SQL Server 2025 इंस्टेंस और बाहरी डेटा सुविधा के लिए पॉलीबेस क्वेरी सेवा पिछले अभ्यासों के लिए स्थापित और सक्षम है।
• AdventureWorks2022 नमूना डेटाबेस नमूना डेटा के लिए उपयोग करने के लिए आपके सर्वर पर पुनर्स्थापित किया गया है।
• बनाया गया ब्लॉब संग्रहण कंटेनर data के साथ एक Azure संग्रहण खाता। संग्रहण बनाने के लिए, Quickstart: Azure पोर्टल के साथ ब्लॉब्स अपलोड करें, डाउनलोड करें और सूचीबद्ध करें देखें.
• Azure में असाइन की गई Azure भूमिका-आधारित पहुँच नियंत्रण (RBAC) संग्रहण ब्लॉब डेटा योगदानकर्ता भूमिका. अधिक जानकारी के लिए, ब्लॉब डेटा तक पहुँच के लिए Azure भूमिका असाइन करना देखें.
• CETAS के लिए उपयोग की जाने वाली READ, WRITE, LIST, और CREATE अनुमतियों के साथ एक ब्लॉब कंटेनर SAS टोकन। एसएएस टोकन बनाने के लिए, अपने स्टोरेज कंटेनर के लिए साझा एक्सेस हस्ताक्षर (एसएएस) टोकन बनाएं देखें।

लकड़ी की छत के रूप में एक तालिका को निर्यात करने के लिए CETAS का उपयोग करें

कल्पना कीजिए कि आप किसी ऐसे व्यवसाय विश्लेषिकी टीम के साथ काम करते हैं जो किसी SQL Server तालिका से किसी Azure ब्लॉब संग्रहण कंटेनर में 2012 से पुराना डेटा निर्यात करना चाहता है. वे सीधे SQL सर्वर क्वेरी करने के बजाय इस निर्यात किए गए डेटा पर अपनी रिपोर्ट क्वेरीज़ चलाना चाहते हैं।

1. SQL सर्वर आवृत्ति पर CETAS सक्षम करें।

   EXEC SP_CONFIGURE @CONFIGNAME = 'ALLOW POLYBASE EXPORT', @CONFIGVALUE = 1;
   

   

2. यह समझने के लिए कि आप कौन सा डेटा निर्यात करना चाहते हैं, निम्न डेटा अन्वेषण क्वेरी निष्पादित करें. इस मामले में, आप 2012 या उससे पहले के डेटा की तलाश कर रहे हैं। आप 2011 और 2012 के सभी डेटा निर्यात करना चाहते हैं।

   -- RECORDS BY YEARS
   SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
   GROUP BY DATEPART(YYYY, [DUEDATE])
   ORDER BY [YEAR]
   

   

3. डेटाबेस के लिए एक डेटाबेस मास्टर कुंजी बनाएँ, जैसा कि पिछले अभ्यासों में है।

   Use AdventureWorks2022
   
   DECLARE @randomWord VARCHAR(64) = NEWID();
   DECLARE @createMasterKey NVARCHAR(500) = N'
   IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
       CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
   EXEC sp_executesql @createMasterKey;
   
   SELECT * FROM sys.symmetric_keys;
   

4. डेटाबेस स्कोप किए गए क्रेडेंशियल्स और बाहरी डेटा स्रोत बनाएँ. Azure में आपके द्वारा बनाए गए स्टोरेज खाते और SAS टोकन के साथ और प्लेसहोल्डर्स <storageccount> को बदलें<sas_token>।

   -- DATABASE SCOPED CREDENTIAL
   CREATE DATABASE SCOPED CREDENTIAL blob_storage
         WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
         SECRET = '<sas_token>';
   
   -- AZURE BLOB STORAGE DATA SOURCE
   CREATE EXTERNAL DATA SOURCE ABS_Data
   WITH
   (
    LOCATION = 'abs://<storageaccount>.blob.core.windows.net/data/chapter3'
   ,CREDENTIAL = blob_storage
   );
   

5. Parquet के लिए बाहरी फ़ाइल स्वरूप बनाएँ।

   -- PARQUET FILE FORMAT
   CREATE EXTERNAL FILE FORMAT ffParquet
   WITH (FORMAT_TYPE = PARQUET);
   

6. CETAS का उपयोग करके बाहरी तालिका बनाएं। निम्न क्वेरी नाम की एक ext_data_2011_2012 बाहरी तालिका बनाती है और 2011 और 2012 से सभी डेटा को डेटा स्रोत ABS_Dataद्वारा निर्दिष्ट स्थान पर निर्यात करती है।

   CREATE EXTERNAL TABLE ex_data_2011_2012
   WITH(
           LOCATION = 'data_2011_20122',
           DATA_SOURCE = ABS_Data,
           FILE_FORMAT = ffParquet
   )AS
   SELECT
   	[PurchaseOrderID]
         ,[PurchaseOrderDetailID]
         ,[DueDate]
         ,[OrderQty]
         ,[ProductID]
         ,[UnitPrice]
         ,[LineTotal]
         ,[ReceivedQty]
         ,[RejectedQty]
         ,[StockedQty]
         ,[ModifiedDate]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
   WHERE YEAR([DUEDATE]) < 2013
   GO
   

7. Azure पोर्टल में अपने Azure ब्लॉब संग्रहण की जाँच करें. आपको निम्नलिखित संरचना बनाई गई देखनी चाहिए। SQL Server 2025 स्वचालित रूप से फ़ाइल नाम बनाता है जो इस आधार पर बनाता है कि यह कितना डेटा निर्यात करता है और फ़ाइल स्वरूप।

   

8. अब आप बाहरी तालिका को नियमित तालिका की तरह एक्सेस कर सकते हैं।

   SELECT * FROM ex_data_2011_2012
   

   

डेटा अब लकड़ी की छत में निर्यात किया जाता है और बाहरी तालिका के माध्यम से आसानी से सुलभ है। व्यवसाय विश्लेषिकी टीम बाहरी तालिका को क्वेरी कर सकती है या अपने रिपोर्टिंग टूल को लकड़ी की छत फ़ाइल पर इंगित कर सकती है।

ठंडे डेटा को डेटाबेस से बाहर ले जाने के लिए CETAS का उपयोग करें

डेटा को प्रबंधनीय रखने के लिए, आपकी कंपनी SQL Server डेटाबेस से 2014 से पुराने डेटा को ले जाने का निर्णय लेती है. हालाँकि, सभी डेटा अभी भी सुलभ होना चाहिए।

इस उदाहरण के लिए, आप CETAS के माध्यम से डेटा निर्यात करते हैं और कई बाहरी तालिकाएँ जनरेट करते हैं जिन्हें आप बाद में क्वेरी कर सकते हैं। आप डेटा को क्वेरी करने के लिए UNION कथनों के साथ एक दृश्य का उपयोग कर सकते हैं, या एक एकल बाह्य तालिका बना सकते हैं और निर्यात किए गए डेटा के सबफ़ोल्डर्स के माध्यम से खोज करने के लिए वाइल्डकार्ड का उपयोग कर सकते हैं।

सबसे पहले, मूल तालिका को क्लोन करें, क्योंकि आप डेटा को निर्यात करने और निकालने का अनुकरण करना चाहते हैं, लेकिन आवश्यक रूप से वर्तमान डेटा स्रोत को हटाना नहीं चाहते हैं। निम्न कथन चलाएँ:

-- CLONE TABLE
SELECT * INTO [PURCHASING].[PURCHASEORDERDETAIL_2] FROM [PURCHASING].[PURCHASEORDERDETAIL]

पहली डेटा एक्सप्लोरेशन क्वेरी से, आप जानते हैं कि 2014 से 5551 रिकॉर्ड हैं। 2014 से पहले की हर चीज को वर्ष के अनुसार पहचाने गए फ़ोल्डर में निर्यात किया जाना चाहिए। 2011 का डेटा एक फ़ोल्डर में जाता है जिसे कहा जाता 2011है, और इसी तरह।

1. बाह्य तालिकाएँ बनाने के लिए, निम्न आदेश चलाएँ:

   CREATE EXTERNAL TABLE ex_2011
   WITH(
           LOCATION = '2011',
           DATA_SOURCE = ABS_Data,
           FILE_FORMAT = ffParquet
   )AS
   SELECT
       [PurchaseOrderID]
         ,[PurchaseOrderDetailID]
         ,[DueDate]
         ,[OrderQty]
         ,[ProductID]
         ,[UnitPrice]
         ,[LineTotal]
         ,[ReceivedQty]
         ,[RejectedQty]
         ,[StockedQty]
         ,[ModifiedDate]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
   WHERE YEAR([DUEDATE]) = 2011;
   

   CREATE EXTERNAL TABLE ex_2012
   WITH(
           LOCATION = '2012',
           DATA_SOURCE = ABS_Data,
           FILE_FORMAT = ffParquet
   )AS
   SELECT
       [PurchaseOrderID]
         ,[PurchaseOrderDetailID]
         ,[DueDate]
         ,[OrderQty]
         ,[ProductID]
         ,[UnitPrice]
         ,[LineTotal]
         ,[ReceivedQty]
         ,[RejectedQty]
         ,[StockedQty]
         ,[ModifiedDate]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
   WHERE YEAR([DUEDATE]) = 2012;
   

   CREATE EXTERNAL TABLE ex_2013
   WITH(
           LOCATION = '2013',
           DATA_SOURCE = ABS_Data,
           FILE_FORMAT = ffParquet
   )AS
   SELECT
       [PurchaseOrderID]
         ,[PurchaseOrderDetailID]
         ,[DueDate]
         ,[OrderQty]
         ,[ProductID]
         ,[UnitPrice]
         ,[LineTotal]
         ,[ReceivedQty]
         ,[RejectedQty]
         ,[StockedQty]
         ,[ModifiedDate]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
   WHERE YEAR([DUEDATE]) = 2013;
   

2. आप इन आदेशों को निष्पादित करने के बाद, SSMS ऑब्जेक्ट Explorer ताज़ा करें। फिर बाहरी तालिकाओं को देखने के लिए डेटाबेस>एडवेंचरवर्क्स2022>टेबल्स>एक्सटर्नल टेबल खोलें।

   

3. पुष्टि करें कि निम्न फ़ोल्डर्स Azure संग्रहण कंटेनर में प्रकट होते हैं:

   

4. ठंडे डेटा को निर्यात करने के बाद, आप इसे मूल तालिका स्थान से हटा सकते हैं।

   DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
   WHERE YEAR([DUEDATE]) < 2014
   

क्वेरी डेटा जिसमें बाहरी तालिका शामिल है

आप निर्यात किए गए बाह्य डेटा को क्वेरी करने के लिए किसी दृश्य या वाइल्डकार्ड खोज का उपयोग कर सकते हैं. प्रत्येक विधि के फायदे और नुकसान हैं। दोहराए जाने वाले अनुरोधों के लिए दृश्य विधि की अनुशंसा की जाती है क्योंकि यह आमतौर पर बेहतर प्रदर्शन करती है, और इसे भौतिक तालिकाओं के साथ भी जोड़ा जा सकता है। वाइल्डकार्ड खोज विधि अधिक लचीली और अन्वेषण उद्देश्यों के लिए उपयोग में आसान है।

डेटा को क्वेरी करने के लिए किसी दृश्य का उपयोग करना

अब जब पुराना डेटा डेटाबेस से निर्यात और हटा दिया गया है, तो आप एक ऐसा दृश्य बनाने के लिए टी-एसक्यूएल का उपयोग कर सकते हैं जो आपके डेटाबेस में सभी बाहरी तालिकाओं और वर्तमान डेटा को क्वेरी करता है।

CREATE VIEW vw_purchaseorderdetail 
AS
SELECT * FROM ex_2011
UNION ALL
SELECT * FROM ex_2012
UNION ALL
SELECT * FROM ex_2013
UNION ALL
SELECT * FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 

आप समान परिणाम देखने के लिए, इस बार नए बनाए गए दृश्य का उपयोग करके मूल डेटा अन्वेषण क्वेरी चला सकते हैं.

SELECT  COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM vw_purchaseorderdetail 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

डेटा को क्वेरी करने के लिए वाइल्डकार्ड खोज का उपयोग करें

पिछले उदाहरण में, आप तीन बाहरी तालिकाओं में शामिल होने के लिए UNION कथनों के साथ एक दृश्य का उपयोग किया। वांछित परिणाम प्राप्त करने का दूसरा तरीका किसी विशेष प्रकार के किसी भी डेटा के लिए सबफ़ोल्डर सहित फ़ोल्डर संरचना को स्कैन करने के लिए वाइल्डकार्ड खोज का उपयोग करना है।

निम्न T-SQL उदाहरण Parquet फ़ाइलों के लिए इसके सबफ़ोल्डर्स सहित डेटा स्रोत में खोज ABS_Data करने के लिए OPENROWSET का उपयोग करता है।

SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM OPENROWSET 
    (BULK '**'
    , FORMAT = 'PARQUET'
    , DATA_SOURCE = 'ABS_Data')
    AS [cc]
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

फ़ोल्डर उन्मूलन और मेटाडेटा जानकारी

बाहरी तालिकाएँ और OPENROWSET दोनों फ़ाइल मेटाडेटा के आधार पर जानकारी एकत्र करने और फ़िल्टर करने के लिए फ़ंक्शन का filepath उपयोग कर सकते हैं। filepath फ़ंक्शन पूर्ण पथ, फ़ोल्डर नाम और फ़ाइल नाम लौटाता है। आप उस जानकारी का उपयोग बाहरी तालिका और OPENROWSET आदेश दोनों की खोज क्षमताओं को बेहतर बनाने के लिए कर सकते हैं।

SELECT
    r.filepath(1) 'folder_name'
    ,r.filepath() 'full_path'
    ,r.filepath(2) 'file_name'
FROM OPENROWSET(
        BULK '*/*.parquet',
        DATA_SOURCE = 'ABS_Data',
        FORMAT = 'parquet'
    ) as [r]
GROUP BY
    r.filepath(2),r.filepath(1), r.filepath()
ORDER BY 
    r.filepath(2)

यदि आप किसी विशेष फ़ोल्डर से डेटा पुनर्प्राप्त करना चाहते हैं और फिर भी वाइल्डकार्ड खोज विधि की कार्यक्षमता का उपयोग करना चाहते हैं, तो आप निम्न क्वेरी का उपयोग कर सकते हैं:

SELECT  *
FROM OPENROWSET(
 BULK '*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2011')

अंतिम परिणाम समान हैं, लेकिन फ़ोल्डर उन्मूलन मेटाडेटा का उपयोग करके, आपकी क्वेरी संपूर्ण डेटा स्रोत को स्कैन करने के बजाय केवल आवश्यक फ़ोल्डर्स तक पहुँचती है, जिससे बेहतर क्वेरी प्रदर्शन होता है. जब आप पॉलीबेस क्षमताओं का बेहतर उपयोग करने के लिए स्टोरेज आर्किटेक्चर डिज़ाइन करते हैं तो इस जानकारी को ध्यान में रखें।

उदाहरण के लिए, निम्नलिखित फ़ोल्डर आर्किटेक्चर दिया गया है:

आप निम्न क्वेरी का उपयोग कर सकते हैं:

SELECT  *
FROM OPENROWSET(
 BULK 'year=*/month=*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('<year>')
 r.filepath(2) IN ('<month>')

इस क्वेरी के प्रयोजनों के लिए, इससे कोई फर्क नहीं पड़ता कि डेटा स्रोत कितना बड़ा बढ़ता है। SQL सर्वर लोड, पढ़ता है, और केवल चयनित फ़ोल्डर से डेटा क्वेरीज़ करता है, अन्य सभी को छोड़ देता है।

क्योंकि डेटाबेस में कोई डेटा संग्रहीत नहीं किया जाता है, इसलिए डेटाबेस व्यवस्थापक को इस डेटा को प्रबंधित करने के लिए कोई विशिष्ट रणनीति डिज़ाइन करने की आवश्यकता नहीं है। कंपनी को अभी भी डेटा को सुरक्षित रूप से बनाए रखने के लिए सभी आवश्यक सावधानी बरतनी चाहिए, जिसमें बैकअप, उपलब्धता और अनुमतियाँ शामिल हैं, लेकिन इन्हीं तक सीमित नहीं हैं।

सारांश

इस अभ्यास में, आपने CETAS का उपयोग किसी डेटाबेस से Azure संग्रहण में कोल्ड डेटा को स्थानांतरित करने और तालिका को Parquet फ़ाइल स्वरूप के रूप में निर्यात करने के लिए किया। आपने अन्वेषण के लिए बाहरी डेटा को क्वेरी करने और प्रदर्शन को अनुकूलित करने के तरीके सीखे।

आप OPENROWSET, बाहरी तालिकाओं, दृश्यों, वाइल्डकार्ड खोज और फ़ाइलपाथ फ़ंक्शंस को संयोजित करने के लिए CETAS का उपयोग कर सकते हैं। आप SQL Server, Oracle, Teradata, और MongoDB जैसे अन्य डेटाबेस से डेटा एक्सेस और निर्यात कर सकते हैं, या Azure ब्लॉब स्टोरेज, Azure डेटा लेक स्टोरेज, या किसी भी S3-संगत ऑब्जेक्ट स्टोरेज से। CETAS आपको सभी पॉलीबेस समर्थित डेटा स्रोतों में प्रदर्शनकारी, टिकाऊ और स्केलेबल समाधान डिजाइन करने में मदद कर सकता है।