ম্যানেজড ইউডিএফ-এর মাধ্যমে বিগকোয়েরিতে পাইথনের শক্তি নিয়ে আসা

১. ভূমিকা

স্ট্রাকচার্ড কোয়েরি ল্যাঙ্গুয়েজ (SQL) ডেটা ওয়্যারহাউস বিশ্লেষণের জন্য ইন্ডাস্ট্রির স্ট্যান্ডার্ড। তবে, জটিল প্রসিডিউরাল লজিক, গাণিতিক গণনা, টেক্সট ক্লিনিং, বা মেশিন লার্নিং প্রস্তুতির ওয়ার্কফ্লোকে শুধুমাত্র SQL-এ প্রকাশ করা অত্যন্ত চ্যালেঞ্জিং হতে পারে।

ঐতিহাসিকভাবে, যখন জটিল কাস্টম পাইথন প্রসেসিংয়ের প্রয়োজন হতো, তখন ডেটা টিমগুলো BigQuery থেকে বিশাল ডেটাসেট এক্সট্র্যাক্ট করত, সেগুলোকে বাহ্যিক কাস্টম ভার্চুয়াল মেশিন বা ক্লাস্টারে প্রসেস করত এবং ফলাফল আবার লোড করত। এই পদ্ধতিটি উচ্চ নেটওয়ার্ক ল্যাটেন্সি তৈরি করে, ডেটা স্থানান্তরের মাধ্যমে কমপ্লায়েন্স ঝুঁকি বাড়ায় এবং ইনফ্রাস্ট্রাকচার ম্যানেজমেন্টের অতিরিক্ত বোঝা সৃষ্টি করে।

BigQuery পরিচালিত পাইথন ইউজার-ডিফাইন্ড ফাংশন (UDF) সার্ভারলেস রিসোর্সে কাস্টম কোড চালানোর মাধ্যমে এই সমস্যাগুলো সমাধান করে, যা স্বয়ংক্রিয়ভাবে লক্ষ লক্ষ সারি পর্যন্ত স্কেল করতে পারে। গুগল ক্লাউড কম্পাইলেশন, ইমেজ তৈরি, নিরাপত্তা প্যাচিং এবং এক্সিকিউশন পরিচালনা করে, যার ফলে আপনি আপনার ডেটা যেখানে থাকে সেখানেই সরাসরি কাস্টম গণনা চালাতে পারেন।

এই কোডল্যাবে, আপনি স্ট্যাকওভারফ্লো কমিউনিটির ডেটার উপর একটি বিশ্লেষণাত্মক এবং টেক্সট-প্রিপ্রসেসিং পাইপলাইন তৈরি করবেন, যা পরবর্তী রিপোর্টিং এবং মেশিন লার্নিংয়ের জন্য ডেটাটিকে প্রস্তুত করবে।

পূর্বশর্ত

• বিলিং সক্ষম একটি গুগল ক্লাউড প্রজেক্ট।
• SQL, IAM, এবং BigQuery-এর ধারণা সম্পর্কে প্রাথমিক জ্ঞান।

আপনি যা শিখবেন

• ডেটা ডিস্ট্রিবিউশন বিশ্লেষণ করার জন্য একটি পাবলিক ডেটাসেটে কীভাবে একটি প্রি-কম্পাইলড পাবলিক পাইথন UDF কল করতে হয়।
• অসংগঠিত ডেটা পরিষ্কার করার জন্য beautifulsoup4 ব্যবহার করে কীভাবে আপনার নিজস্ব কাস্টম পাইথন UDF স্থাপন করবেন।
• ইন-মেমরি কন্টেইনার ক্যাশিং ব্যবহার করে হাগিং ফেস ট্রান্সফর্মারস লাইব্রেরির সাহায্যে নিরাপদে মেশিন লার্নিং অ্যাসেট ডাউনলোড করতে এবং স্থানীয় এমএল টোকেনাইজেশন সম্পাদন করতে কীভাবে একটি বিগকোয়েরি ক্লাউড রিসোর্স সংযোগ কনফিগার করতে হয়।
• কীভাবে এই ধাপগুলোকে একত্রিত করে একটি একক, উচ্চ-পারফরম্যান্স SQL পাইপলাইন তৈরি করা যায়।

২. সেটআপ এবং প্রয়োজনীয়তা

ক্লাউড শেল শুরু করুন

যদিও গুগল ক্লাউড আপনার ল্যাপটপ থেকে দূরবর্তীভাবে পরিচালনা করা যায়, এই কোডল্যাবে আপনি গুগল ক্লাউড শেল ব্যবহার করবেন, যা ক্লাউডে চালিত একটি কমান্ড লাইন পরিবেশ।

1. গুগল ক্লাউড কনসোলে যান, তারপর একটি গুগল ক্লাউড প্রজেক্ট নির্বাচন করুন বা তৈরি করুন ।
2. ⚠️ প্রজেক্ট আইডিটি লিখে রাখুন। এই ল্যাব জুড়ে আপনাকে এটি ব্যবহার করতে হবে।

3. একটি নতুন ট্যাবে ক্লাউড শেল খুলুন: https://shell.cloud.google.com/ ।
4. অনুরোধ করা হলে, অনুমোদন করুন-এ ক্লিক করুন।
5. PROJECT_ID প্রতিস্থাপন করুন এবং নিম্নলিখিত কমান্ডটি টার্মিনালে পেস্ট করুন:

cat << 'EOF' > env.sh
#!/bin/bash
# env.sh: Environment variables for BigQuery Python UDFs codelab

# ⚠️ Replace 'YOUR_PROJECT_ID' with your actual Google Cloud Project ID
export PROJECT_ID="YOUR_PROJECT_ID"
export REGION="us"
export BQ_DATASET="python_udfs"
export BQ_RESOURCE_CONN="external_api_connection"
EOF

আপনার সক্রিয় সেশনে ভেরিয়েবলগুলো প্রয়োগ করুন:

source ./env.sh

💡 "আমাদের" অঞ্চল কেন?

আমরা যে bigquery-public-data.stackoverflow পাবলিক ডেটাসেটটি ব্যবহার করব, সেটি us মাল্টি-রিজিওনে অবস্থিত। ক্রস-রিজিওন ত্রুটি ছাড়া আপনার কাস্টম পাইথন UDF ব্যবহার করে এটি কোয়েরি করার জন্য, আপনার টার্গেট ডেটাসেট ( python_udfs ) অবশ্যই একই অঞ্চলে ( us ) অবস্থিত হতে হবে।

এপিআই সক্রিয় করুন এবং বিগকোয়েরি ডেটাসেট তৈরি করুন

আপনার প্রোজেক্টে প্রয়োজনীয় গুগল ক্লাউড পরিষেবাগুলো সক্রিয় করুন এবং টার্গেট ডেটাসেট তৈরি করুন:

# Enable API Services
gcloud services enable \
  bigquery.googleapis.com \
  bigqueryconnection.googleapis.com --quiet

# Create BigQuery Dataset
bq mk --location=${REGION} --dataset ${PROJECT_ID}:${BQ_DATASET}

৩. একটি পাবলিক পাইথন UDF ব্যবহার করে ডেটা ডিস্ট্রিবিউশন অন্বেষণ করুন

কাস্টম কোড ডেপ্লয় করার আগে, ডেটাসেটটি ভালোভাবে দেখে নেওয়া এবং নিম্নমানের অপ্রয়োজনীয় ডেটা ফিল্টার করে বাদ দেওয়া সহায়ক। এই ধাপে, আপনি সক্রিয় ব্যবহারকারীদের খুঁজে বের করতে এবং তাদের প্রশ্নের স্কোরের পরিসংখ্যানগত বিন্যাস বুঝতে স্ট্যাকওভারফ্লো-এর প্রশ্নগুলো বিশ্লেষণ করবেন।

এর জন্য পাইথন ইউডিএফ কেন ব্যবহার করবেন?

বিশুদ্ধ SQL-এ ডেটার গ্রুপ করা অ্যারের উপর একাধিক, সুনির্দিষ্ট পার্সেন্টাইল (যেমন ২৫তম, ৫০তম, ৭৫তম এবং ৯৫তম পার্সেন্টাইল) গণনা করা জটিল এবং রিসোর্স-হেভি। PERCENTILE_CONT মতো স্ট্যান্ডার্ড SQL অ্যানালিটিক ফাংশনগুলো নেস্টেড অ্যারের পরিবর্তে সারিগুলোর ফ্ল্যাট কলাম আশা করে। প্রতি সারির জন্য প্রি-অ্যাগ্রিগেটেড অ্যারের সুনির্দিষ্ট পার্সেন্টাইল গণনা করতে, আপনাকে প্রতিটি পার্সেন্টাইল মেট্রিকের জন্য বিশদ সাবকোয়েরি লিখতে হবে যা ডেটাকে আননেস্ট, সর্ট এবং পুনরায় অ্যাগ্রিগেট করে, যা অদক্ষ।

একটি UDF-এর ভিতরে পাইথনের অত্যন্ত অপ্টিমাইজ করা বৈজ্ঞানিক লাইব্রেরি NumPy ব্যবহার করে, আপনি এক লাইনের কোডেই সংখ্যার একটি অ্যারের উপর সঠিক গাণিতিক পার্সেন্টাইল গণনা করতে পারেন।

মৃত্যুদণ্ড

গুগল ক্লাউডে বেশ কিছু প্রি-কম্পাইলড পাবলিক UDF রয়েছে ( রুটিনস ট্যাবে ক্লিক করুন)। যেহেতু BigQuery-তে সুস্পষ্ট টাইপ ম্যাচিং প্রয়োজন হয়, তাই আমরা ডেটা প্রি-অ্যাগ্রিগেট করার জন্য একটি কমন টেবিল এক্সপ্রেশন (CTE) ব্যবহার করব এবং একটি UNNEST এক্সপ্রেশনের মাধ্যমে ইন্টিজার অ্যারেগুলোকে ফ্লোটিং-পয়েন্ট অ্যারেতে কাস্ট করব।

BigQuery Studio কনসোলে নিম্নলিখিত কোয়েরিটি চালান:

WITH raw_user_scores AS (
  -- 1. Pre-aggregate user scores into an array
  SELECT 
    owner_user_id, 
    ARRAY_AGG(score) AS scores
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`
  WHERE 
    owner_user_id IS NOT NULL
  GROUP BY 
    owner_user_id
  HAVING 
    ARRAY_LENGTH(scores) >= 5
  LIMIT 5
)
SELECT 
  owner_user_id,
  scores,
  -- 2. Cast arrays to FLOAT64 and call the public percentile Python UDF
  `bigquery-public-data.python_udfs.percentiles`(
    ARRAY(SELECT CAST(s AS FLOAT64) FROM UNNEST(scores) AS s), 
    [25.0, 50.0, 75.0, 95.0]
  ) AS score_percentiles
FROM 
  raw_user_scores;

এর মাধ্যমে আপনি আগে থেকে পারমিশন কনফিগার করা বা কাস্টম পাইথন কোড লেখার প্রয়োজন ছাড়াই তাৎক্ষণিকভাবে ব্যবহারকারীর পারফরম্যান্স বুঝতে পারবেন।

ফলাফল যাচাই করা

যেহেতু এই কোয়েরিটি নেস্টেড অ্যারে টাইপ ( scores এবং score_percentiles ) রিটার্ন করে, BigQuery Studio-এর ডিফল্ট টেবুলার Results ট্যাবে আউটপুটটি ফ্ল্যাট বা ট্রাঙ্কেটেড অবস্থায় প্রদর্শিত হতে পারে, যার ফলে অ্যারের এলিমেন্টগুলো পরীক্ষা করা কঠিন হয়ে পড়ে।

কাঠামোগত, নেস্টেড আউটপুট দেখতে:

1. কোয়েরি ফলাফল প্যানে, ট্যাব বারটি খুঁজুন (যেটি ডিফল্টরূপে ' Results' থাকে)।
2. JSON ট্যাবে ক্লিক করুন।

আপনি সারিগুলো উপস্থাপনকারী একটি কাঠামোগত JSON অ্যারে দেখতে পাবেন, যা নিম্নলিখিতটির অনুরূপ হবে:

[{
  "owner_user_id": "533463",
  "scores": ["0", "0", "-1", "0", "0", "2", "-1", "1", "0", "0", "-1", "0", "-3", "1", "1", "0", "1", "2", "3", "1", "0", "0", "1", "0", "0", "3", "6", "11", "0", "1", "0", "0", "3", "17", "0", "1", "1", "3", "5", "-2", "1", "-1", "-1", "2", "3", "0", "0", "0", "5", "0", "4", "0", "0", "0", "3", "3", "0", "140", "0", "1", "3", "0", "0", "-2", "-1", "0", "0", "2", "0", "9", "9", "0", "0", "1", "0", "0", "1", "-1", "0", "0", "0", "0"],
  "score_percentiles": ["0.0", "0.0", "1.75", "8.8500000000000085"]
}, {
  "owner_user_id": "13502536",
  "scores": ["0", "1", "0", "-5", "0", "1", "0", "1", "0", "0", "-2", "0", "1", "0", "1", "0", "0", "1", "0", "1", "0", "0"],
  "score_percentiles": ["0.0", "0.0", "1.0", "1.0"]
}, {
  "owner_user_id": "1170153",
  "scores": ["1", "0", "1", "0", "1", "0", "2", "0", "0", "0", "10", "5", "1", "0", "0", "2", "0", "2", "3", "-1", "1", "0", "1", "0", "0", "1", "0", "2", "0", "4", "0", "3", "0", "0", "2", "0", "0", "1", "0"],
  "score_percentiles": ["0.0", "0.0", "1.5", "4.1000000000000014"]
}, {
  "owner_user_id": "8558174",
  "scores": ["0", "0", "-1", "1", "2", "0"],
  "score_percentiles": ["0.0", "0.0", "0.75", "1.75"]
}, {
  "owner_user_id": "1073044",
  "scores": ["0", "1", "0", "0", "2", "2", "2", "1", "1", "1", "2", "1", "0", "2", "3", "1"],
  "score_percentiles": ["0.75", "1.0", "2.0", "2.25"]
}]

আউটপুট বোঝা

• scores : প্রতিটি স্বতন্ত্র ব্যবহারকারীর পোস্ট করা প্রশ্নগুলোর মোট স্কোরের সম্পূর্ণ তালিকা।
• score_percentiles : একটি অ্যারে যাতে চারটি গণনাকৃত ফ্লোটিং-পয়েন্ট মান থাকে। এগুলি অনুরোধকৃত পার্সেন্টাইলগুলির সাথে হুবহু মিলে যায়: [25th, 50th, 75th, and 95th] পার্সেন্টাইল। উদাহরণস্বরূপ, ব্যবহারকারী 533463 এর জন্য, তার প্রশ্নগুলির ৯৫তম পার্সেন্টাইল স্কোর প্রায় 8.85 , যা নির্দেশ করে যে তার সেরা প্রশ্নগুলি উচ্চ স্কোর পেয়েছে।

৪. একটি কাস্টম UDF তৈরি করে নেটিভভাবে টেক্সট পরিষ্কার করুন

একবার লক্ষ্য ব্যবহারকারীদের শনাক্ত করা হয়ে গেলে, আমরা তাদের পোস্টের বিষয়বস্তু বিশ্লেষণ করতে চাই। তবে, ফোরামের সাধারণ পোস্টগুলোতে প্রায়শই অগোছালো HTML ট্যাগ এবং এনটিটি থাকে। পাঠযোগ্যতা বাড়াতে এবং পরবর্তী মডেলের খরচ কমাতে আমাদের এগুলো বাদ দিতে হবে।

কেন এটি প্রয়োজনীয় তা বোঝার জন্য, আসুন প্রথমে দেখি স্ট্যাক ওভারফ্লো পোস্টের মূল, ফরম্যাটবিহীন অংশটি আসলে কেমন দেখতে হয়। BigQuery Studio কনসোলে নিম্নলিখিত কোয়েরিটি চালান:

SELECT
  id,
  title,
  body AS raw_html_body
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
  -- Check specific questions that we will use in our final pipeline
WHERE
  id IN (9, 17, 33969)
ORDER BY
  id ASC;

আউটপুটটি পরীক্ষা করলে আপনি দেখবেন যে, টেক্সটের ভেতরে <p>, <b>, <code> এবং অন্যান্য ফরম্যাটিং ট্যাগের মিশ্রণ এমবেড করা আছে। ডাউনস্ট্রিম মেশিন লার্নিং টোকেনাইজার ব্যবহার করে সরাসরি এগুলো প্রসেস করলে অপ্রয়োজনীয় নয়েজ তৈরি হবে এবং টোকেন ইনজেশন খরচ কৃত্রিমভাবে বেড়ে যাবে।

এর জন্য পাইথন ইউডিএফ কেন ব্যবহার করবেন?

বিশুদ্ধ SQL-এ রেগুলার এক্সপ্রেশন (Regex) ব্যবহার করে নির্ভরযোগ্যভাবে HTML পার্স করা ভঙ্গুর এবং পার্সিং ত্রুটির ঝুঁকিপূর্ণ। আপনার কোয়েরির মধ্যে সরাসরি beautifulsoup4 মতো একটি শক্তিশালী পাইথন লাইব্রেরি চালানো ট্যাগ বাদ দেওয়ার একটি নির্ভরযোগ্য উপায়।

আপনার ডেটাসেটে স্থায়ী clean_html ফাংশনটি স্থাপন করতে নিম্নলিখিত DDL কোয়েরিটি চালান:

⚠️ SQL নির্বাহ সংক্রান্ত দ্রষ্টব্য:

1. BigQuery কনসোলে SQL কোয়েরি কপি করার সময়, YOUR_PROJECT_ID-এর জায়গায় আপনার আসল Google Cloud প্রজেক্ট আইডি বসাতে মনে রাখবেন।
2. যেহেতু BigQuery অ্যাসিঙ্ক্রোনাসভাবে কন্টেইনার এনভায়রনমেন্ট প্রস্তুত করে, তাই একটি নতুন UDF কল করা প্রথম কোয়েরিটি কোল্ড-স্টার্ট বিলম্বের সম্মুখীন হতে পারে। আপনি এক্সপ্লোরার প্যানে আপনার ডেটাসেটের রুটিন তালিকায় ডেপ্লয়মেন্ট স্ট্যাটাস নিরীক্ষণ করতে পারেন।

CREATE OR REPLACE FUNCTION `YOUR_PROJECT_ID.python_udfs.clean_html`(html_content STRING)
RETURNS STRING
LANGUAGE python
OPTIONS (
  runtime_version = 'python-3.11',
  entry_point = 'strip_tags',
  packages = ['beautifulsoup4>=4.12.0']
) AS r'''
from bs4 import BeautifulSoup

def strip_tags(html_content):
    if not html_content:
        return ""
    soup = BeautifulSoup(html_content, "html.parser")
    return soup.get_text(separator=" ")
''';

একটি সাধারণ কোয়েরি দিয়ে ফাংশনের আউটপুট যাচাই করুন:

SELECT `YOUR_PROJECT_ID.python_udfs.clean_html`('<p>Hello <b>world</b>!</p>') AS cleaned_text;

আপনার এইচটিএমএল উপাদান ছাড়া মূল লেখাটি দেখা উচিত:

+----------------+
| cleaned_text   |
+----------------+
| Hello  world ! |
+----------------+

৫. সুরক্ষিত বাহ্যিক ইন্টিগ্রেশন এবং উন্নত এমএল প্রসেসিং

এখন যেহেতু আমাদের কাছে পরিমার্জিত টেক্সট আছে, এটিকে মেশিন লার্নিং মডেল বা জেমার মতো লার্জ ল্যাঙ্গুয়েজ মডেল (এলএলএম)-এর জন্য প্রস্তুত করতে হবে। এলএলএম সরাসরি কাঁচা টেক্সট পড়তে পারে না; এগুলো সাংখ্যিক টোকেন আইডি প্রক্রিয়াকরণ করে।

আমাদের ক্লিন টেক্সটকে টোকেনে রূপান্তর করতে, আমরা হাগিং ফেস-এর transformers লাইব্রেরি ইম্পোর্ট করব এবং একটি প্রি-ট্রেইনড গুগল টি৫ টোকেনাইজার সরাসরি আমাদের ডেটাবেসের ভেতরে লোড করব।

💡 আমাদের সুরক্ষিত সংযোগ কেন প্রয়োজন?

ডিফল্টরূপে, BigQuery Python UDF-গুলো একটি সুরক্ষিত, বিচ্ছিন্ন কন্টেইনারের ভিতরে চলে, যেখানে কোনো বহির্গামী নেটওয়ার্ক অ্যাক্সেস থাকে না। যেহেতু আমাদের পাবলিক Hugging Face Hub থেকে প্রি-ট্রেইনড T5 টোকেনাইজার ফাইলগুলো ডাউনলোড করতে হবে, তাই আমাদের অবশ্যই একটি BigQuery ক্লাউড রিসোর্স সংযোগ স্থাপন করতে হবে। এই সংযোগটি একটি সুরক্ষিত গেটওয়ে হিসেবে কাজ করে, যা প্রয়োজনীয় অ্যাসেটগুলো আনার জন্য কন্টেইনারটিকে বহির্গামী নেটওয়ার্ক ব্যবহারের অনুমতি দেয়।

এছাড়াও, এই সংযোগটি তৈরি করলে স্বয়ংক্রিয়ভাবে একটি অনন্য গুগল ক্লাউড সার্ভিস অ্যাকাউন্ট তৈরি হয়ে যায়। যদি আপনার পাইথন UDF-কে সীমাবদ্ধ বাহ্যিক API বা সুরক্ষিত ক্লাউড রিসোর্সের সাথে ইন্টারঅ্যাক্ট করার প্রয়োজন হয়, তবে আপনি নিরাপদে অথেনটিকেশন পরিচালনা করার জন্য এই সার্ভিস অ্যাকাউন্টটিকে নির্দিষ্ট IAM পারমিশন প্রদান করতে পারেন।

ক্লাউড রিসোর্স সংযোগ তৈরি করুন

একটি সুরক্ষিত সংযোগ স্থাপন করতে BigQuery Studio Console- এ নিম্নলিখিত কোয়েরিটি চালান:

CREATE CONNECTION IF NOT EXISTS `YOUR_PROJECT_ID.us.external_api_connection`
OPTIONS (
  connection_type = "CLOUD_RESOURCE",
  friendly_name = "Hugging Face Hub Egress Connection",
  description = "Connection used to securely download model configs from public ML hubs"
);

টোকেনাইজার UDF তৈরি করুন

এখন, কাস্টম টোকেনাইজার UDF-টি ডিপ্লয় করুন। লক্ষ্য করুন get_tokenizer() হেল্পার ফাংশনটি ডাউনলোড করার চেষ্টা করার আগে গ্লোবাল ভ্যারিয়েবল tokenizer আগে থেকেই ইনিশিয়ালাইজ করা আছে কিনা তা পরীক্ষা করে দেখে:

CREATE OR REPLACE FUNCTION `YOUR_PROJECT_ID.python_udfs.tokenize`(text STRING)
RETURNS ARRAY<INT64>
LANGUAGE python
WITH CONNECTION `YOUR_PROJECT_ID.us.external_api_connection`
OPTIONS (
  runtime_version = 'python-3.11',
  entry_point = 'tokenize',
  packages = ['transformers', 'sentencepiece']
) AS r'''
from transformers import T5TokenizerFast

# Initialize global variable for in-memory container caching
tokenizer = None

def get_tokenizer():
    global tokenizer
    if tokenizer is None:
        # Securely download T5 tokenizer config from Hugging Face Hub (runs once per warm container)
        tokenizer = T5TokenizerFast.from_pretrained("t5-base")
    return tokenizer

def tokenize(text):
    if not text:
        return []
    try:
        t = get_tokenizer()
        # Convert raw clean text into integer token IDs
        return [int(x) for x in t.encode(text)]
    except Exception:
        return []
''';

একটি সাধারণ কোয়েরি দিয়ে টোকেনাইজারটি পরীক্ষা করে দেখুন যে এটি সফলভাবে অ্যাসেটটি ডাউনলোড করে এবং পূর্ণসংখ্যার আইডি-সহ একটি অ্যারে ফেরত দেয় কিনা:

SELECT `YOUR_PROJECT_ID.python_udfs.tokenize`('Hello world!') AS token_ids;

স্ট্রাকচার্ড অ্যারেটি দেখতে আপনার কোয়েরি রেজাল্ট প্যানেলের JSON ট্যাবে যান:

[
  {
    "token_ids": ["8774", "296", "55", "1"]
  }
]

৬. এন্ড-টু-এন্ড প্রিপ্রসেসিং পাইপলাইনটি চালান।

এখন যেহেতু আমাদের পাইপলাইনের তিনটি ধাপই প্রস্তুত, আমরা কমন টেবিল এক্সপ্রেশন (CTE) ব্যবহার করে সেগুলোকে একত্রিত করে একটি একক SQL কোয়েরি তৈরি করতে পারি।

এই পাইপলাইনটি একটি আধুনিক ডেটা ইঞ্জিনিয়ারিং কর্মপ্রবাহের প্রতিনিধিত্ব করে:

1. পাবলিক পার্সেন্টাইল ইউডিএফ ব্যবহার করে সক্রিয় ব্যবহারকারী এবং তাদের সর্বোচ্চ স্কোর করা প্রশ্নগুলো চিহ্নিত করুন।
2. আমাদের clean_html UDF ব্যবহার করে স্থানীয়ভাবে টেক্সট থেকে মূল HTML ফরম্যাটিং বাদ দিন।
3. আমাদের ক্যাশ করা টোকেনাইজ ইউডিএফ (tokenize UDF) ব্যবহার করে পরিমার্জিত টেক্সটকে টোকেন অ্যারেতে রূপান্তর করুন।

BigQuery Studio কনসোলে নিম্নলিখিত পাইপলাইন কোয়েরিটি চালান:

WITH raw_user_scores AS (
  -- Step 1: Pre-aggregate scores to safely run percentiles with deterministic ordering
  SELECT 
    owner_user_id, 
    ARRAY_AGG(score ORDER BY id ASC) AS scores
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`
  WHERE 
    owner_user_id IS NOT NULL
  GROUP BY 
    owner_user_id
  HAVING 
    ARRAY_LENGTH(scores) >= 5
  ORDER BY 
    owner_user_id ASC
  LIMIT 3
),
active_users AS (
  -- Step 1: Extract exact percentile limits using the public UDF)
  SELECT 
    owner_user_id,
    percentiles_arr AS score_percentiles,
    -- Extract the 95th percentile score from the array's 4th element (OFFSET 3) directly
    percentiles_arr[OFFSET(3)] AS p95_score
  FROM (
    SELECT 
      owner_user_id,
      `bigquery-public-data.python_udfs.percentiles`(
        ARRAY(SELECT CAST(s AS FLOAT64) FROM UNNEST(scores) AS s), 
        [25.0, 50.0, 75.0, 95.0]
      ) AS percentiles_arr
    FROM 
      raw_user_scores
  )
),
target_questions AS (
  -- Isolate high-scoring questions from active users
  SELECT 
    q.id,
    q.owner_user_id,
    q.title,
    q.body AS raw_body,
    u.score_percentiles
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions` q
  JOIN 
    active_users u ON q.owner_user_id = u.owner_user_id
  WHERE 
    -- Explicit cast for robust comparison
    q.score >= CAST(u.p95_score AS FLOAT64)
),
cleaned_data AS (
  -- Step 2: Clean HTML tags natively
  SELECT 
    id,
    owner_user_id,
    title,
    score_percentiles,
    `YOUR_PROJECT_ID.python_udfs.clean_html`(raw_body) AS cleaned_body
  FROM 
    target_questions
),
tokenized_data AS (
  -- Step 3: Perform local ML tokenization on the clean preview text
  SELECT 
    id,
    owner_user_id,
    title,
    score_percentiles,
    SUBSTR(cleaned_body, 1, 120) AS cleaned_body_preview,
    `YOUR_PROJECT_ID.python_udfs.tokenize`(SUBSTR(cleaned_body, 1, 120)) AS token_ids
  FROM 
    cleaned_data
)
SELECT 
  id,
  owner_user_id,
  title,
  score_percentiles,
  cleaned_body_preview AS cleaned_body,
  token_ids,
  ARRAY_LENGTH(token_ids) AS token_count
FROM 
  tokenized_data
ORDER BY 
  id ASC;

কাঠামোগত আউটপুট পরীক্ষা করার জন্য BigQuery Studio-এর JSON ট্যাবে যান।

[{
  "id": "9",
  "owner_user_id": "1",
  "title": "How do I calculate someone\u0027s age based on a DateTime type birthday?",
  "score_percentiles": ["22.5", "61.5", "346.75", "1762.0"],
  "cleaned_body": "Given a DateTime representing a person\u0027s birthday, how do I calculate their age in years?",
  "token_ids": ["9246", "3", "9", "7678", "13368", "9085", "3", "9", "568", "31", "7", "3591", "6", "149", "103", "27", "11837", "70", "1246", "16", "203", "58", "1"],
  "token_count": "23"
}, {
  "id": "17",
  "owner_user_id": "2",
  "title": "Binary Data in MySQL",
  "score_percentiles": ["3.5", "10.0", "90.0", "184.09999999999997"],
  "cleaned_body": "How do I store binary data in MySQL ?",
  "token_ids": ["571", "103", "27", "1078", "14865", "331", "16", "27563", "3", "58", "1"],
  "token_count": "11"
}, {
  "id": "33969",
  "owner_user_id": "3",
  "title": "Best way to implement request throttling in ASP.NET MVC?",
  "score_percentiles": ["3.25", "14.0", "24.75", "175.25"],
  "cleaned_body": "We\u0027re experimenting with various ways to throttle user actions in a given time period : Limit question/answer posts Limi",
  "token_ids": ["101", "31", "60", "3", "26718", "28", "796", "1155", "12", "28731", "1139", "2874", "16", "3", "9", "787", "97", "1059", "3", "10", "18185", "822", "87", "3247", "3321", "3489", "10908", "23", "1"],
  "token_count": "29"
}]

৭. পরিশিষ্ট: পাইপলাইন কীভাবে কাজ করে এবং কার্যসম্পাদন ব্যয়ের নিরীক্ষা

এই বিভাগে এন্ড-টু-এন্ড প্রিপ্রসেসিং কোয়েরির সুনির্দিষ্ট কার্যপ্রণালী গভীরভাবে আলোচনা করা হয়েছে এবং আপনার এক্সিকিউশনের সঠিক স্লট-ব্যবহার ও ম্যানেজড কন্টেইনার খরচ কীভাবে নিরীক্ষণ করতে হয় তা দেখানো হয়েছে।

পাইপলাইন স্থাপত্যগত বিভাজন

WITH raw_user_scores AS (
  SELECT 
    owner_user_id, 
    ARRAY_AGG(score ORDER BY id ASC) AS scores
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`
  WHERE 
    owner_user_id IS NOT NULL
  GROUP BY 
    owner_user_id
  HAVING 
    ARRAY_LENGTH(scores) >= 5
  ORDER BY 
    owner_user_id ASC
  LIMIT 3
)

এই প্রথম কোয়েরি সেগমেন্টটি স্ট্যাক ওভারফ্লো-এর সক্রিয় অবদানকারীদের প্রশ্নের কাঁচা স্কোর সংগ্রহ করে। এটি প্রতিটি ব্যবহারকারীর স্কোরকে একটি একক অ্যারেতে ( ARRAY_AGG ) একত্রিত করে এবং একটি নির্দিষ্ট সর্টিং অর্ডার ( ORDER BY id ) প্রয়োগ করে। একটি বৈধ পরিসংখ্যানগত বেসলাইন স্থাপনের জন্য ডেটাসেটটি ফিল্টার করে শুধুমাত্র সেইসব ব্যবহারকারীদের অন্তর্ভুক্ত করা হয় যাদের কমপক্ষে পাঁচটি প্রশ্ন রয়েছে।

active_users AS (
  SELECT 
    owner_user_id,
    percentiles_arr AS score_percentiles,
    -- Extract the 95th percentile score from the array's 4th element (OFFSET 3) directly
    percentiles_arr[OFFSET(3)] AS p95_score
  FROM (
    SELECT 
      owner_user_id,
      `bigquery-public-data.python_udfs.percentiles`(
        ARRAY(SELECT CAST(s AS FLOAT64) FROM UNNEST(scores) AS s), 
        [25.0, 50.0, 75.0, 95.0]
      ) AS percentiles_arr
    FROM 
      raw_user_scores
  )
)

শীর্ষ অবদানকারীদের শনাক্ত করতে, এই অংশটি সঠিক স্কোর ডিস্ট্রিবিউশন (২৫তম, ৫০তম, ৭৫তম, এবং ৯৫তম পার্সেন্টাইল) খুঁজে বের করার জন্য পাবলিক percentiles পাইথন UDF ব্যবহার করে। এই গণনা-নিবিড় UDF-টি একাধিকবার চালানো এড়াতে, গণনাটি একটি নেস্টেড সাবকোয়েরির মধ্যে রাখা হয়। এরপর ৯৫তম পার্সেন্টাইল বেঞ্চমার্কটি সরাসরি ফলাফল অ্যারের তিন নম্বর ইনডেক্স পজিশন থেকে ( OFFSET(3) ) নেওয়া হয়।

target_questions AS (
  -- Isolate high-scoring questions from active users
  SELECT 
    q.id,
    q.owner_user_id,
    q.title,
    q.body AS raw_body,
    u.score_percentiles
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions` q
  JOIN 
    active_users u ON q.owner_user_id = u.owner_user_id
  WHERE 
    -- Explicit cast for robust comparison
    q.score >= CAST(u.p95_score AS FLOAT64)
)

মূল প্রশ্নগুলোকে সক্রিয় ব্যবহারকারীদের তালিকার সাথে যুক্ত করে সেইসব পোস্ট পুনরুদ্ধার করা হয় যেগুলো ৯৫তম পার্সেন্টাইল থ্রেশহোল্ড পূরণ করেছে বা অতিক্রম করেছে। ডাটাবেস টাইপ তুলনা ত্রুটি এড়াতে, মূল্যায়নের আগে বেঞ্চমার্ক স্কোরকে একটি CAST অপারেশনের মাধ্যমে স্পষ্টভাবে FLOAT64 টাইপে রূপান্তর করা হয়।

cleaned_data AS (
  -- Clean HTML tags natively
  SELECT 
    id,
    owner_user_id,
    title,
    score_percentiles,
    `YOUR_PROJECT_ID.python_udfs.clean_html`(raw_body) AS cleaned_body
  FROM 
    target_questions
)

কাঁচা পোস্ট বডিতে প্রায়শই অগোছালো মার্কআপ এবং এইচটিএমএল বয়লারপ্লেট থাকে, যা পরবর্তী মেশিন লার্নিং ইনপুটের মান কমিয়ে দেয়। জটিল রেগুলার এক্সপ্রেশন ব্যবহার করার পরিবর্তে, পাইপলাইনটি আমাদের নিজস্ব clean_html পাইথন ইউডিএফ (UDF) কল করে। এটি BeautifulSoup লাইব্রেরি ব্যবহার করে একটি বিচ্ছিন্ন কন্টেইনারের ভিতরে ডায়নামিকভাবে একটি পাইথন রানটাইম চালু করে, যা উপাদানগুলোকে পরিচ্ছন্নভাবে বাদ দিয়ে সাধারণ, পাঠযোগ্য টেক্সট আউটপুট করে।

tokenized_data AS (
  -- Perform local ML tokenization on the clean preview text (called only once)
  SELECT 
    id,
    owner_user_id,
    title,
    score_percentiles,
    SUBSTR(cleaned_body, 1, 120) AS cleaned_body_preview,
    `YOUR_PROJECT_ID.python_udfs.tokenize`(SUBSTR(cleaned_body, 1, 120)) AS token_ids
  FROM 
    cleaned_data
)

জেনারেটিভ মডেল ইনজেশনের জন্য ক্লিন টেক্সট প্রিভিউ প্রস্তুত করতে, পাইপলাইনটি একটি ১২০-ক্যারেক্টারের স্লাইসের উপর আমাদের কাস্টম tokenize পাইথন UDF-কে কল করে। UDF-টি নিরাপদে হাগিং ফেস হাবের সাথে যোগাযোগ করে গুগল T5 টোকেনাইজার প্যারামিটারগুলো ডাউনলোড করে। যেহেতু টোকেনাইজার ইনস্ট্যান্সটি একটি গ্লোবাল ভেরিয়েবলে লোড করা হয়, তাই ওয়ার্ম কন্টেইনারটি কনফিগারেশনটি ক্যাশ করে রাখে, ফলে পরবর্তী সারিগুলো কোনো নেটওয়ার্ক ল্যাটেন্সি ছাড়াই দ্রুত ইন-মেমরি টোকেনাইজেশনের মধ্য দিয়ে যেতে পারে।

SELECT 
  id,
  owner_user_id,
  title,
  score_percentiles,
  cleaned_body_preview AS cleaned_body,
  token_ids,
  ARRAY_LENGTH(token_ids) AS token_count
FROM 
  tokenized_data
ORDER BY 
  id ASC;

চূড়ান্ত কোয়েরি ব্লকটি প্রক্রিয়াকৃত ডেটাসেটটি আউটপুট করে। তৈরি হওয়া টোকেনগুলো গণনা করার জন্য টোকেনাইজেশন UDF-টি দ্বিতীয়বার এক্সিকিউট করার পরিবর্তে, BigQuery-এর নিজস্ব ARRAY_LENGTH ফাংশনটি সরাসরি আগে থেকে গণনা করা token_ids অ্যারের উপর প্রয়োগ করা হয়। এই কৌশলটি অপ্রয়োজনীয় সিপিইউ সাইকেল, কন্টেইনার অপারেশন এবং সার্বিক এক্সিকিউশন খরচ কমিয়ে আনে।

স্লট-ব্যবহার নিরীক্ষা এবং পরিচালিত UDF খরচ

BigQuery সরাসরি Google Cloud Console UI-তে খরচের বিস্তারিত তথ্য দেখানোর ড্যাশবোর্ড চালু করলেও, ইঞ্জিনিয়াররা BigQuery জব আইডি ব্যবহার করে প্রোগ্রাম্যাটিকভাবে যেকোনো কোয়েরির সঠিক স্লট-ব্যবহার এবং ম্যানেজড কন্টেইনার এক্সিকিউশন খরচ নিরীক্ষা করতে পারবেন।

আপনার কোয়েরি এক্সিকিউশন নিরীক্ষা করতে, আপনার জব আইডিটি সনাক্ত করুন।

1. BigQuery Studio-তে, কনসোলের নিচের দিকে থাকা Query history ট্যাবে গিয়ে আপনি এটি খুঁজে পেতে পারেন।
2. আপনার এক্সিকিউটেড পাইপলাইন কোয়েরিতে ক্লিক করুন
3. চাকরির তথ্যের বিবরণ প্যানেলে, জব আইডি ফিল্ডটি খুঁজুন।

💡 জব আইডি ফরম্যাট বোঝা

গুগল ক্লাউড কনসোলে, একটি সম্পূর্ণ BigQuery জব আইডি নিম্নলিখিত বিন্যাসে গঠিত হয়:

{YOUR_PROJECT_ID}:{REGION}.{JOB_ID}

উদাহরণস্বরূপ, বাস্তব জগতের একটি সম্পূর্ণ জব আইডি দেখতে এইরকম হতে পারে:

my-gcp-project:us.bquxjob_2d801c6d_19ecde92e15

আঞ্চলিক মেটাডেটা টেবিল ( INFORMATION_SCHEMA.JOBS ) কোয়েরি করার সময়, আপনাকে অবশ্যই শুধুমাত্র বিশুদ্ধ {JOB_ID} অংশটি আলাদা করে ব্যবহার করতে হবে (যেমন, bquxjob_2d801c6d_19ecde92e15 )। এর আগের প্রজেক্ট আইডি বা রিজিওন প্রিফিক্স অন্তর্ভুক্ত করবেন না, কারণ তা করলে মেটাডেটা ভিউ আপনার এক্সিকিউশন রেকর্ডের সাথে মিলবে না।

একবার আপনার আসল জব আইডি শনাক্ত করার পর, নিচের কোয়েরিতে আপনার JOB_ID প্রতিস্থাপন করুন এবং BigQuery Studio-তে এটি চালান:

SELECT 
  job_id,
  total_slot_ms,
  external_service_costs
FROM 
  `YOUR_PROJECT_ID.region-us`.INFORMATION_SCHEMA.JOBS
WHERE 
  job_id = "JOB_ID";

স্ট্রাকচার্ড আউটপুট পরীক্ষা করার জন্য BigQuery Studio-এর JSON ট্যাবে যান। আপনি নিম্নলিখিতের মতো একটি পেলোড পাবেন:

[{
  "job_id": "bquxjob_1234f5a_67ea8c9051a",
  "total_slot_ms": "815459",
  "external_service_costs": [{
    "external_service": "MANAGED_ROUTINE_EXECUTION",
    "bytes_processed": null,
    "bytes_billed": null,
    "slot_ms": "3000",
    "reserved_slot_count": null,
    "billing_method": "SERVICES_SKU"
  }]
}]

আউটপুট বোঝা:

• total_slot_ms : সমস্ত কোয়েরি পর্যায় জুড়ে ব্যবহৃত মোট গণনার সময় (মিলিসেকেন্ডে)। এই সমন্বিত পাইপলাইনের জন্য, এক্সিকিউশনের গড় সময় সাধারণত প্রায় ৮১৫ হাজার স্লট মিলিসেকেন্ড হয়ে থাকে।
• external_service_costs : একটি অ্যারে যা স্ট্যান্ডার্ড BigQuery অ্যানালাইসিস ইঞ্জিনের বাইরে ব্যবহৃত রিসোর্সগুলোর বিস্তারিত বিবরণ দেয়।
• external_service : " MANAGED_ROUTINE_EXECUTION " মানটি নিশ্চিত করে যে এই খরচটি বিশেষভাবে সেই সার্ভারলেস কন্টেইনার এক্সিকিউশনের অন্তর্গত, যা আমাদের কাস্টম পাইথন UDF এনভায়রনমেন্ট হোস্ট করছে।
• slot_ms : " 3000 " মানটি ওয়ার্ম কন্টেইনার রানটাইমের ভিতরে পাইথন লজিক কার্যকর করার জন্য ব্যবহৃত বিশেষায়িত কম্পিউট রিসোর্সের সঠিক মিলিসেকেন্ডকে বোঝায়।
• billing_method : " SERVICES_SKU " মানটি নির্দেশ করে যে এই স্থানীয় কন্টেইনার চার্জগুলি কন্টেইনার এক্সিকিউশন সময়কাল এবং মেমরি ওভারহেডের উপর ভিত্তি করে বিশেষায়িত BigQuery Services SKU-এর মাধ্যমে ডায়নামিকভাবে বিল করা হয়। প্রতি স্লট-ঘন্টায় $0.06-এর স্ট্যান্ডার্ড ইউএস মাল্টি-রিজিওন কম্পিউট প্রাইসিং অনুসারে ( BigQuery Services Pricing Page দেখুন), 3,000 স্লট-মিলিসেকেন্ডের প্রকৃত এক্সিকিউশন খরচ গণনা করা হয় (3,000ms / 3,600,000ms) * $0.06 = $0.00005 USD হিসাবে, যা একটি ব্যয়-সাশ্রয়ী কর্মপ্রবাহ প্রদর্শন করে।

⚠️ দ্রষ্টব্য: আপনার বিলিং স্টেটমেন্টে প্রকৃত লেনদেন খরচ কিছুটা বেশি হতে পারে, কারণ এই অঙ্কটি শুধুমাত্র ওয়ার্ম কন্টেইনারের এক্সিকিউশনকে অন্তর্ভুক্ত করে। এতে পাবলিক স্ট্যাক ওভারফ্লো ডেটাসেট পড়ার জন্য স্ট্যান্ডার্ড অন-ডিমান্ড ডেটা স্ক্যানিং ফি, অথবা কোল্ড স্টার্টের সময় BeautifulSoup / Transformers প্যাকেজ করার এককালীন রুটিন বিল্ড খরচ অন্তর্ভুক্ত নয়।

৮. ক্লাউড রিসোর্স পরিষ্কার করুন

ক্রমাগত চার্জ এড়ানো বা প্রজেক্ট কোটা খরচ হওয়া থেকে বাঁচতে, ক্লাউড শেল থেকে আপনার BigQuery ডেটাসেট এবং কানেকশনগুলো ডিলিট করে দিন:

# Cleanup BigQuery routines
bq rm -f --routine ${PROJECT_ID}:${BQ_DATASET}.clean_html
bq rm -f --routine ${PROJECT_ID}:${BQ_DATASET}.tokenize

# Cleanup connection
bq rm -f --connection --location=${REGION} ${PROJECT_ID}.${REGION}.${BQ_RESOURCE_CONN}

# Cleanup BigQuery Dataset
bq rm -r -f -d ${PROJECT_ID}:${BQ_DATASET}

৯. অভিনন্দন!

আপনি BigQuery Serverless রানটাইমের মধ্যে পাইথন UDF তৈরি এবং সুরক্ষিত করার কোডল্যাবটি সম্পন্ন করেছেন।

এই কোডল্যাবে, আপনি শিখেছেন কীভাবে:

• পাবলিক UDF ব্যবহার করে ডেটা অন্বেষণ করুন: অ্যাগ্রিগেটেড অ্যারেতে গাণিতিক পার্সেন্টাইল অপারেশন সম্পাদন করতে স্ট্যাক ওভারফ্লো ডেটাসেটে থাকা প্রি-কম্পাইলড পাবলিক পাইথন UDF কল করুন।
• তৃতীয় পক্ষের প্যাকেজ একীভূত করুন: আপনার SQL কোয়েরির ভেতর থেকে সরাসরি HTML ট্যাগ বাদ দেওয়ার জন্য স্ট্যান্ডার্ড পাইথন রানটাইম এবং beautifulsoup4 লাইব্রেরি ব্যবহার করে একটি কাস্টম স্থায়ী UDF স্থাপন করুন।
• নিরাপদ বাহ্যিক সংযোগ কনফিগার করুন: ক্রেডেনশিয়াল হার্ডকোড না করেই বাহ্যিক অ্যাসেট আনার জন্য বিচ্ছিন্ন UDF কন্টেইনারগুলিকে নিরাপদে আউটবাউন্ড নেটওয়ার্ক অ্যাক্সেস দিতে একটি BigQuery ক্লাউড রিসোর্স সংযোগ তৈরি করুন।
• ইন-মেমরি ক্যাশিং সহ লোকাল টোকেনাইজেশন প্রয়োগ করুন: একটি T5 টোকেনাইজার লোড করতে হাগিং ফেস transformers লাইব্রেরিটি ইম্পোর্ট করুন, যা গ্লোবাল ভেরিয়েবল ব্যবহার করে কনফিগারেশন ফাইল ক্যাশ করে এবং ওয়ার্ম কন্টেইনারের ভিতরে সারিগুলো প্রসেস করে।
• অডিট এক্সিকিউশন পারফরম্যান্স এবং খরচ: স্লট-ব্যবহার ( total_slot_ms ) এবং কন্টেইনার ব্যবহারের খরচ ( external_service_costs ) ট্র্যাক করতে BigQuery জব আইডি ব্যবহার করে প্রোগ্রাম্যাটিকভাবে আঞ্চলিক INFORMATION_SCHEMA.JOBS ভিউ কোয়েরি করুন।

এরপর কী?

• সর্বোত্তম অনুশীলন ও কোটা পর্যালোচনা করুন: BigQuery Python UDF লিমিটস গাইড থেকে এক্সিকিউশন লিমিট, কনকারেন্সি এবং মেমরি সংক্রান্ত বিবেচ্য বিষয়গুলো সম্পর্কে জানুন।
• ব্যয় অপ্টিমাইজেশন সম্পর্কে বিস্তারিত জানুন: BigQuery প্রাইসিং ডকুমেন্টেশন থেকে কন্টেইনারাইজড UDF এনভায়রনমেন্ট এবং এক্সটার্নাল কানেকশন কলের জন্য বিলিং কীভাবে কাজ করে তা বুঝুন।
• BigQuery-এর ভেতরে AI-চালিত অ্যাপ্লিকেশন তৈরি করুন: সাধারণ টেক্সট ম্যানিপুলেশনের বাইরে যান এবং এন্ড-টু-এন্ড মাল্টিমোডাল সিস্টেম নির্মাণ করতে শিখুন। আপনার ডেটা ওয়্যারহাউসের ভেতরে সরাসরি সিমান্টিক সার্চ এবং জেমিনি মডেল ব্যবহার করতে 'Build an AI-Powered Vehicle Marketplace with BigQuery and Gemini Models' কোডল্যাবটি সম্পন্ন করুন।