BigQuery গ্রাফের মাধ্যমে সাপ্লাই চেইন ট্রেসেবিলিটি

১. ভূমিকা

এই কোডল্যাবে আপনি শিখবেন কীভাবে BigQuery Graph ব্যবহার করে জটিল সাপ্লাই চেইন এবং লজিস্টিকস সমস্যার সমাধান করা যায়।

আপনি খাদ্য নিরাপত্তা এবং গুণমান নিয়ন্ত্রণের উপর মনোযোগ দিয়ে একটি রেস্তোরাঁর সরবরাহ শৃঙ্খল নেটওয়ার্কের মডেল তৈরি করবেন। যখন খাদ্য নিরাপত্তার কোনো সমস্যা দেখা দেয়—যেমন সরবরাহকারীর কাছ থেকে আসা কোনো দূষিত উপাদান—তখন সময় অত্যন্ত গুরুত্বপূর্ণ হয়ে ওঠে। ঝুঁকির পরিধি চিহ্নিত করা এবং দ্রুততার সাথে সুপরিকল্পিতভাবে পণ্য প্রত্যাহার করা খরচ বাঁচাতে পারে এবং গ্রাহকদের সুরক্ষা নিশ্চিত করতে পারে।

রেস্তোরাঁর খাবার নিয়ে আতঙ্ক

প্রচলিত রিলেশনাল মডেলগুলিতে একাধিক ধাপের (সাপ্লায়ার -> ডিসি -> কমিসারি -> স্টোর -> ফিনিশড আইটেম) মধ্য দিয়ে আইটেমগুলির গতিপথ অনুসরণ করতে জটিল, বহু-ধাপের JOIN অপারেশনের প্রয়োজন হয়। BigQuery Graph-এর মাধ্যমে, আমরা এই সংযোগগুলিকে সরাসরি মডেল করি, যা ISO GQL (গ্রাফ কোয়েরি ল্যাঙ্গুয়েজ) স্ট্যান্ডার্ড ব্যবহার করে স্বজ্ঞাত এবং দ্রুত কোয়েরি সক্ষম করে।

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

  • বিদ্যমান BigQuery টেবিলের উপর কীভাবে একটি গ্রাফ মডেল সংজ্ঞায়িত করা যায়।
  • BigQuery-এর ভিতরে কীভাবে একটি প্রপার্টি গ্রাফ তৈরি করবেন
  • আপস্ট্রিম এবং ডাউনস্ট্রিম প্রভাব সনাক্ত করতে কীভাবে ট্র্যাভার্সাল কোয়েরি চালানো যায়।

আপনার যা যা লাগবে

  • বিলিং সক্ষম একটি গুগল ক্লাউড প্রজেক্ট।
  • গুগল ক্লাউড শেল।

খরচের আনুমানিক হিসাব

এই ল্যাবটির BigQuery বিশ্লেষণ ফি বাবদ খরচ ৫ মার্কিন ডলারের কম হবে বলে আশা করা হচ্ছে, যা নতুন ব্যবহারকারীদের জন্য ফ্রি টিয়ার বরাদ্দের মধ্যেই রয়েছে।

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

ওপেন ক্লাউড শেল

আপনি বেশিরভাগ কাজ ক্লাউড শেল- এ করবেন, যা গুগল ক্লাউড ব্যবহারের জন্য প্রয়োজনীয় সবকিছুসহ একটি পরিপূর্ণ পরিবেশ।

  1. গুগল ক্লাউড কনসোলে যান।
  2. উপরের ডানদিকের টুলবারে থাকা অ্যাক্টিভেট ক্লাউড শেল আইকনটিতে ক্লিক করুন।
  3. অনুরোধ করা হলে ' চালিয়ে যান'-এ ক্লিক করুন।

পরিবেশ ভেরিয়েবল সেট আপ করুন

ভবিষ্যতের কমান্ডগুলো সহজ করার জন্য ক্লাউড শেলে আপনার প্রজেক্ট আইডি সেট করুন।

export PROJECT_ID=$(gcloud config get-value project)

BigQuery API সক্রিয় করুন

BigQuery API সক্রিয় আছে কিনা তা নিশ্চিত করুন। এটি সাধারণত ডিফল্টরূপে সক্রিয় থাকে, কিন্তু সতর্কতা অবলম্বন করাই শ্রেয়।

gcloud services enable bigquery.googleapis.com

৩. স্কিমা এবং টেবিল তৈরি করা

আপনি আপনার সাপ্লাই চেইনের উপাদানগুলো উপস্থাপন করে একটি ডেটাসেট এবং টেবিল তৈরি করবেন:

  • item : আইটেমের সাধারণ সংজ্ঞা (যেমন, টমেটো, চিকেন)।
  • location : সুবিধাসমূহ (সরবরাহকারী, বিতরণ কেন্দ্র, ক্যাফে)।
  • itemlocation : ইনভেন্টরি অবস্থানসমূহ উপস্থাপনকারী ইন্টারসেকশন টেবিল।
  • bom : উপকরণ তালিকা (ওজনের সম্পর্ক নির্ধারণ করে, যেমন—পণ্য A-এর মধ্যে পণ্য B যাবে)।
  • makes : itemlocation item সাথে সংযুক্ত করে।
  • stored_at : itemlocation নির্দিষ্ট location সাথে সংযুক্ত করে।

ডেটাসেট তৈরি করুন

এই ল্যাবে আপনি ক্লাউড শেল অথবা বিগকোয়েরি কনসোল ব্যবহার করে SQL কমান্ডগুলো চালাতে পারেন।

BigQuery কনসোল ব্যবহার করতে:

  1. একটি নতুন ট্যাবে BigQuery কনসোল খুলুন।
  2. এই ল্যাবের প্রতিটি SQL কোড এডিটরে পেস্ট করুন, তারপর সেটি চালানোর জন্য রান বাটনে ক্লিক করুন।

বিগকোয়েরি এডিটর

স্কিমা তৈরি করার জন্য ক্লাউড শেল-এ নিম্নলিখিত কমান্ডটি চালান অথবা বিগকোয়েরি কনসোল ব্যবহার করুন। আপনি আপনার SQL-এ নোড ভেরিয়েবল ব্যবহার করবেন।

বিগকোয়েরি ডেটা স্কিমা

দ্রষ্টব্য: (1) গুগল কোলাবে এটি কার্যকর করতে, আপনি BigQuery ম্যাজিক কমান্ডও ব্যবহার করতে পারেন: %%bigquery। নিম্নলিখিত কোড স্নিপেটটি আপনার গ্রাফ ডেটা রাখার জন্য আপনার প্রোজেক্টের মধ্যে রেস্তোরাঁ স্কিমা তৈরি করে। (2) আপনি যদি গুগল কোলাব থেকে চালান তবে আপনাকে %%bigquery –project <PROJECT_ID> ব্যবহার করতে হবে। নিশ্চিত করুন যে PROJECT_ID ফিল্ডটি আপনার ব্যবহার করতে ইচ্ছুক উপযুক্ত প্রোজেক্টের সাথে ম্যাপ করা আছে: PROJECT_ID = "argolis-project-340214" # @param {"type":"string"} (3) আপনি যদি কোলাব ব্যবহার করেন, তবে আপনার প্রয়োজন অনুসারে কিছু লাইব্রেরি ইনস্টল করতে হবে। আপনি যদি গ্রাফ ভিজ্যুয়ালাইজেশন ব্যবহার করতে যাচ্ছেন তবে নিশ্চিত করুন যে আপনি pip install করেছেন: spanner-graph-notebook==1.1.5

কোলাবে BigQuery-এর জাদু

%%bigquery --project=$PROJECT_ID
CREATE SCHEMA IF NOT EXISTS restaurant ;

টেবিল তৈরি করুন

টেবিলগুলো তৈরি করতে নিম্নলিখিত SQL কোডটি চালান।

%%bigquery --project=$PROJECT_ID
-- 1. Item Table
DROP TABLE IF EXISTS `restaurant.item`;
CREATE TABLE `restaurant.item` (
  itemKey STRING,
  itemName STRING,
  itemCategory STRING,
  shelfLifeDays INT64,
  PRIMARY KEY (itemKey) NOT ENFORCED
);

-- 2. Location Table
DROP TABLE IF EXISTS `restaurant.location`;
CREATE TABLE `restaurant.location` (
  locationKey STRING,
  locationType STRING,
  locationCity STRING,
  locationState STRING,
  dunsNumber INT64,
  PRIMARY KEY (locationKey) NOT ENFORCED
);
-- 3. ItemLocation Table
DROP TABLE IF EXISTS `restaurant.itemlocation`;
CREATE TABLE `restaurant.itemlocation` (
  itemLocationKey STRING,
  itemKey STRING,
  locationKey STRING,
  variants INT64,
  PRIMARY KEY (itemLocationKey) NOT ENFORCED,
  -- Foreign Key Definitions
  FOREIGN KEY (itemKey) REFERENCES `restaurant.item`(itemKey) NOT ENFORCED,
  FOREIGN KEY (locationKey) REFERENCES `restaurant.location`(locationKey) NOT ENFORCED
);

-- 4. BOM Table
DROP TABLE IF EXISTS `restaurant.bom`;
CREATE TABLE `restaurant.bom` (
  bomKey INT64,
  parentItemLocation STRING,
  childItemLocation STRING,
  childQuantity FLOAT64,
  PRIMARY KEY (bomKey) NOT ENFORCED
);

-- 5. Makes Table
DROP TABLE IF EXISTS `restaurant.makes`;
CREATE TABLE `restaurant.makes` (
  itemLocationKey STRING,
  itemKey STRING,
  locationKey STRING,
  variants INT64,
  PRIMARY KEY (itemLocationKey) NOT ENFORCED
);

DROP TABLE IF EXISTS `restaurant.stored_at`;
CREATE TABLE `restaurant.stored_at` (
  itemLocationKey STRING,
  itemKey STRING,
  locationKey STRING,
  variants INT64,
  PRIMARY KEY (itemLocationKey) NOT ENFORCED
);

৪. নমুনা ডেটা লোড করা

এই ল্যাবটিকে সম্পূর্ণরূপে স্বয়ংসম্পূর্ণ করার জন্য, আপনি খাঁটি SQL LOAD DATA স্টেটমেন্ট ব্যবহার করে টেবিলগুলিতে নমুনা ডেটা পূরণ করবেন। এটি এমন একটি নেটওয়ার্ককে উপস্থাপন করে যা একজন সরবরাহকারী (Supplier) থেকে শুরু হয়ে, একটি বিতরণ কেন্দ্র (DC) এবং কমিসারি কিচেন (Commissary Kitchen) -এর মধ্য দিয়ে গিয়ে একটি রিটেইল ক্যাফে (Retail Café)-তে শেষ হয়।

ডেটা লোড করতে নিম্নলিখিত SQL কোয়েরিগুলো চালান:

বিগকোয়েরি ডেটা লোডিং

দ্রষ্টব্য: আপনি যদি সরাসরি bigquery studio-তে চালান, তাহলে %%bigquery বাদ দিতে পারেন।

%%bigquery --project=$PROJECT_ID
-- Load Item
LOAD DATA OVERWRITE `restaurant.item`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/item2.csv'], skip_leading_rows = 1);

-- Load Location
LOAD DATA OVERWRITE `restaurant.location`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/location.csv'], skip_leading_rows = 1);

-- Load ItemLocation
LOAD DATA OVERWRITE `restaurant.itemlocation`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/itemlocation.csv'], skip_leading_rows = 1);

-- Load BOM
LOAD DATA OVERWRITE `restaurant.bom`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/bom2.csv'], skip_leading_rows = 1);

-- Load Makes
LOAD DATA OVERWRITE `restaurant.makes`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/makes.csv'], skip_leading_rows = 1);

-- Load StoredAt
LOAD DATA OVERWRITE `restaurant.stored_at`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/itemlocation.csv'], skip_leading_rows = 1);

৫. সীমাবদ্ধতা যোগ করা এবং গ্রাফ নির্ধারণ করা

গ্রাফ তৈরি করার আগে, আপনাকে স্ট্যান্ডার্ড SQL প্রাইমারি কী এবং ফরেন কী কনস্ট্রেইন্ট ব্যবহার করে সিমান্টিক সম্পর্কগুলো ঘোষণা করতে হয়। এগুলো BigQuery-কে নোড আইডেন্টিফায়ার বুঝতে এবং এজ টেবিলগুলোকে নোড টেবিলের সাথে সংযুক্ত করতে নির্দেশনা দেয়।

প্রপার্টি গ্রাফ তৈরি করুন

এখন, আপনি এই টেবিলগুলোকে একত্রিত করে restaurant.bombod নামক একটি একক সুসংহত গ্রাফ কাঠামো তৈরি করবেন।

আপনি সংজ্ঞায়িত করুন:

  • নোডসমূহ : item , location , itemlocation
  • Edges : makes , stored_at , and consists_of (BOM) 
%%bigquery --project=$PROJECT_ID

CREATE OR REPLACE PROPERTY GRAPH `restaurant.bombod`
NODE TABLES (
  `restaurant.item` KEY (itemKey) LABEL item PROPERTIES ALL COLUMNS,
  `restaurant.location` KEY (locationKey) LABEL location PROPERTIES ALL COLUMNS,
  `restaurant.itemlocation` KEY (itemLocationKey) LABEL itemlocation PROPERTIES ALL COLUMNS
)
EDGE TABLES (
  `restaurant.makes`
    KEY (itemLocationKey)
    SOURCE KEY (itemLocationKey) REFERENCES `restaurant.itemlocation`(itemLocationKey)
    DESTINATION KEY (itemKey) REFERENCES `restaurant.item`(itemKey)
    LABEL makes PROPERTIES ALL COLUMNS,
    
  `restaurant.bom`
    KEY (bomKey)
    SOURCE KEY (childItemLocation) REFERENCES `restaurant.itemlocation`(itemLocationKey)
    DESTINATION KEY (parentItemLocation) REFERENCES `restaurant.itemlocation`(itemLocationKey)
    LABEL consists_of PROPERTIES ALL COLUMNS,
    
  `restaurant.stored_at`
    KEY (itemLocationKey)
    SOURCE KEY (itemLocationKey) REFERENCES `restaurant.itemlocation`(itemLocationKey)
    DESTINATION KEY (locationKey) REFERENCES `restaurant.location`(locationKey)
    LABEL stored_at PROPERTIES ALL COLUMNS
);

৬. সরবরাহ শৃঙ্খলের দৃশ্যায়ন

সম্পূর্ণ সাপ্লাই চেইন নেটওয়ার্কটি দেখতে আপনি একটি টপ-ডাউন ট্র্যাভার্সাল কোয়েরি চালাতে পারেন। একটি স্ট্যান্ডার্ড নোটবুক বা সাপোর্টিভ UI-তে (যেমন %%bigquery --graph ), এটি একটি ভিজ্যুয়াল ম্যাপ রিটার্ন করে।

নোড এবং এজ সেটআপ করতে অ্যাবসোলিউট গ্রাফ কোয়েরি ব্যবহার করুন।

দ্রষ্টব্য: পূর্বে যেমন উল্লেখ করা হয়েছে, Google Colab বা Colab Enterprise Notebooks-এ এটি কার্যকর করার জন্য, আপনি BigQuery ম্যাজিক কমান্ডও ব্যবহার করতে পারেন: %%bigquery। এছাড়াও Google Colab বা Colab Enterprise Notebooks-এ গ্রাফটি ভিজ্যুয়ালাইজ করতে –graph ফ্ল্যাগটি এভাবে অন্তর্ভুক্ত করুন: %%bigquery –graph

%%bigquery  --project=$PROJECT_ID --graph output

Graph restaurant.bombod

match p=(a:itemlocation)-[c:consists_of]->(b:itemlocation)
match q=(a)-[d:stored_at]->(e:location)
optional match z=(f)-[g:makes]-(b)

return to_json(p) as ppath, to_json(q) as qpath, to_json(z) as zpath

আউটপুট:

রেস্তোরাঁর সরবরাহ গ্রাফ

৭. ব্যবহারিক ক্ষেত্র ১: উৎসস্থ অভিযোগের উৎস সন্ধান

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

ট্রাভার্সাল কোয়েরি

গ্রাফ ট্র্যাভার্সাল কোয়েরি ফরম্যাট ব্যবহার করে কোয়েরিটি চালান। এটি সেইসব consists_of এজগুলো পরীক্ষা করে, যেগুলো ডাউনস্ট্রিম অ্যাসেম্বলিগুলোকে আপস্ট্রিম ইনগ্রেডিয়েন্টের সাথে সম্পর্কিত করে।

%%bigquery --project=$PROJECT_ID --graph

GRAPH restaurant.bombod
MATCH p=(a:itemlocation)-[c:consists_of]->(b:itemlocation)
OPTIONAL MATCH q=(b)-[d:stored_at]-(e)
return to_json(p) as ppath, to_json(q) as qpath

consists_of এজ টেবিলের তীরের দিকনির্দেশের ( Ingredient -> Finished ) কারণে, উজানের দিকে প্রবাহিত একটি অনুসন্ধান দ্রুত নির্ভরশীল উপাদান এবং সংরক্ষণের স্থানগুলিকে আলাদা করার জন্য লিঙ্কগুলি খুঁজে বের করে।

আউটপুট: মুরগির উৎস খুঁজে বের করা

৮. ব্যবহারিক ক্ষেত্র ২: প্রভাব বিশ্লেষণ

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

ট্রাভার্সাল কোয়েরি

আপনি ডিস্ট্রিবিউশন সেন্টারের প্রতিনিধিত্বকারী নির্দিষ্ট location থেকে শুরু করবেন, সেখানে সংরক্ষিত ইনভেন্টরি শনাক্ত করবেন এবং দেখবেন কোন তৈরি পণ্যগুলোর জন্য সেগুলো প্রয়োজন।

# @title Impact of a storm on a DC

%%bigquery  --project=$PROJECT_ID --graph
Graph restaurant.bombod
match path1=(z:itemlocation)-[m:stored_at]->(dc:location) where dc.locationKey like '%DC-Sysco-Columbus-OH%'
match path2=(z:itemlocation)-[c:consists_of]->(b:itemlocation)
match path3=(b:itemlocation)-[n:makes]->(item:item)
optional match path4=(b)-[p:stored_at]->(q:location)
return to_json(path1) as path1, to_json(path2) as path2,to_json(path3) as path3, to_json(path4) as path4

আউটপুট: ঝড়ের প্রভাব

৯. ব্যবহারিক ক্ষেত্র ৩: ডাউনস্ট্রিম রিকল

প্রেক্ষাপট : একজন সরবরাহকারী আপনাকে একটি নির্দিষ্ট ব্যাচের দূষিত পণ্য সম্পর্কে অবহিত করেন: সরবরাহকারীর কাছ থেকে আনা গাছে পাকা টমেটো । আপনাকে ক্যাফেগুলোতে থাকা চূড়ান্ত মেনুর সমস্ত প্রভাবিত আইটেম খুঁজে বের করতে হবে।

ট্রাভার্সাল কোয়েরি

আপনি দূষিত কাঁচামালের অবস্থানটি খুঁজে বের করেন, তারপর চূড়ান্তভাবে ক্ষতিগ্রস্ত জিনিসগুলো খুঁজে পেতে স্রোতের অনুকূলে একটি পথ অনুসরণ করেন। 

%%bigquery  --project=$PROJECT_ID --graph
Graph restaurant.bombod
match path1=(a:itemlocation)-[c:consists_of]->(b:itemlocation)-[e:makes]->(f:item) where f.itemKey like '%Tomato%'
return to_json(path1) as result

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

আউটপুট: খারাপ টমেটোর নিম্নধারার প্রভাব

১০. পরিষ্কার করা

আপনার ওয়ার্কস্পেসে কোনো অবশিষ্ট চার্জ এড়াতে, ওয়াকথ্রু ধাপগুলো সম্পন্ন করার পর রিসোর্সগুলো মুছে ফেলুন। 

DROP SCHEMA `restaurant` CASCADE;

১১. উপসংহার

অভিনন্দন! আপনি BigQuery Graph ব্যবহার করে একটি সাপ্লাই চেইন মডেল তৈরি করেছেন এবং ইমপ্যাক্ট অ্যানালাইসিস সম্পন্ন করেছেন।

শেষ করুন

আপনি শিখেছেন:

  1. প্রাইমারি/ফরেন কী ব্যবহার করে গ্রাফ-কেন্দ্রিক সম্পর্ক ঘোষণা করুন।
  2. একটি সমন্বিত সম্পত্তি গ্রাফ তৈরি করুন।
  3. গ্রাফ কোয়েরি ট্র্যাভার্সাল লজিক ব্যবহার করে একাধিক নোডের সম্পর্ক দক্ষতার সাথে পরিচালনা করুন।

গ্রাফ আর্কিটেকচার সম্পর্কে আরও বিস্তারিত জানতে গুগল ক্লাউড ডক্স দেখুন।