১. ভূমিকা
এই কোডল্যাবে, আপনি BigQuery Studio-এর পরিচিত ইন্টারফেসের মধ্যেই ডেটা ট্রান্সফরমেশনের জন্য Apache Spark-এর শক্তিকে কীভাবে কাজে লাগাতে হয় তা শিখবেন। আপনি BigQuery থেকে ডেটা পড়বেন, PySpark ব্যবহার করে ডেটা ক্লিনিং ও ট্রান্সফরমেশন করবেন এবং ফলাফলগুলো একটি নতুন BigQuery টেবিলে পুনরায় লিখবেন—এই সবকিছুই একটিমাত্র নোটবুক থেকে করা যাবে।
কোডল্যাবের মাধ্যমে, আপনি নিম্নলিখিত ধাপে ধাপে পদ্ধতিটি অনুসরণ করবেন:
- আপনার গুগল ক্লাউড প্রজেক্টটি প্রস্তুত করুন এবং এতে প্রয়োজনীয় সকল এপিআই (API) সক্রিয় করুন।
- অস্থায়ী ফোল্ডারের জন্য GCS বাকেট তৈরি করুন
- অ্যাপাচি স্পার্ক চালানোর জন্য প্রয়োজনীয় লাইব্রেরিগুলো ইম্পোর্ট করুন।
- BigQuery কানেক্টর ব্যবহার করে Spark সেশন শুরু করুন
- BigQuery পাবলিক ডেটাসেট থেকে Google Analytics-এর ডেটা নমুনা পড়ুন
- ডিভাইস ব্রাউজার অনুযায়ী সমষ্টিগত ডেটা (মৌলিক মেট্রিক্স) দিয়ে ডেটা রূপান্তর করুন।
- ট্র্যাফিক উৎস বিশ্লেষণ এবং রাজস্ব গণনার মাধ্যমে ডেটা রূপান্তর করুন।
- ভৌগোলিক বিশ্লেষণের মাধ্যমে ডেটা রূপান্তর করুন।
- রূপান্তরের পর ডেটা BigQuery টেবিলে লিখুন
স্থাপত্যের সংক্ষিপ্ত বিবরণ
পূর্বশর্ত
- বিলিং সক্ষম একটি গুগল ক্লাউড প্ল্যাটফর্ম (GCP) প্রজেক্ট।
- আপনার GCP প্রোজেক্টে BigQuery API এবং BigQuery Connection API সক্রিয় করা হয়েছে।
- SQL ও Python-এর প্রাথমিক জ্ঞান।
আপনি যা শিখবেন
- BigQuery Studio Notebook-এ Apache Spark ব্যবহার করে কীভাবে ডেটা এক্সট্র্যাক্ট করবেন
- BigQuery Studio Notebook-এ Apache Spark ব্যবহার করে কীভাবে ডেটা রূপান্তর বা একত্রিত করবেন
- BigQuery Studio Notebook-এ Apache Spark ব্যবহার করে ডেটা ট্রান্সফর্ম বা অ্যাগ্রিগেট করার পর কীভাবে ডেটা লিখতে হয়
আপনার যা যা লাগবে
- ক্রোম ওয়েব ব্রাউজার
- একটি জিমেইল অ্যাকাউন্ট
- বিলিং সক্ষম একটি ক্লাউড প্রজেক্ট
২. প্রাথমিক সেটআপ এবং প্রয়োজনীয়তা
স্ব-গতিতে পরিবেশ সেটআপ
- Google Cloud Console- এ সাইন-ইন করুন এবং একটি নতুন প্রজেক্ট তৈরি করুন অথবা বিদ্যমান কোনো প্রজেক্ট পুনরায় ব্যবহার করুন। যদি আপনার আগে থেকে Gmail বা Google Workspace অ্যাকাউন্ট না থাকে, তবে আপনাকে অবশ্যই একটি তৈরি করতে হবে।
- প্রজেক্টের নামটি হলো এই প্রজেক্টের অংশগ্রহণকারীদের প্রদর্শিত নাম। এটি একটি ক্যারেক্টার স্ট্রিং যা গুগল এপিআই ব্যবহার করে না। আপনি যেকোনো সময় এটি আপডেট করতে পারেন।
- প্রজেক্ট আইডি সমস্ত গুগল ক্লাউড প্রজেক্ট জুড়ে অনন্য এবং অপরিবর্তনীয় (একবার সেট করার পর এটি পরিবর্তন করা যায় না)। ক্লাউড কনসোল স্বয়ংক্রিয়ভাবে একটি অনন্য স্ট্রিং তৈরি করে; সাধারণত এটি কী তা নিয়ে আপনার মাথা ঘামানোর দরকার নেই। বেশিরভাগ কোডল্যাবে, আপনাকে আপনার প্রজেক্ট আইডি উল্লেখ করতে হবে (যা সাধারণত
PROJECT_IDহিসাবে চিহ্নিত করা হয়)। তৈরি করা আইডিটি আপনার পছন্দ না হলে, আপনি এলোমেলোভাবে আরেকটি তৈরি করতে পারেন। বিকল্পভাবে, আপনি আপনার নিজের আইডি দিয়ে চেষ্টা করে দেখতে পারেন যে সেটি উপলব্ধ আছে কিনা। এই ধাপের পরে এটি পরিবর্তন করা যাবে না এবং প্রজেক্টের পুরো সময়কাল জুড়ে এটি অপরিবর্তিত থাকবে। - আপনার অবগতির জন্য জানানো যাচ্ছে যে, তৃতীয় একটি ভ্যালু রয়েছে, যা হলো প্রজেক্ট নাম্বার এবং কিছু এপিআই এটি ব্যবহার করে। ডকুমেন্টেশনে এই তিনটি ভ্যালু সম্পর্কে আরও বিস্তারিত জানুন।
- এরপর, ক্লাউড রিসোর্স/এপিআই ব্যবহার করার জন্য আপনাকে ক্লাউড কনসোলে বিলিং চালু করতে হবে। এই কোডল্যাবটি সম্পন্ন করতে খুব বেশি খরচ হবে না, এমনকি আদৌ কোনো খরচ নাও হতে পারে। এই টিউটোরিয়ালের পর বিলিং এড়াতে রিসোর্সগুলো বন্ধ করার জন্য, আপনি আপনার তৈরি করা রিসোর্সগুলো অথবা প্রজেক্টটি ডিলিট করে দিতে পারেন। নতুন গুগল ক্লাউড ব্যবহারকারীরা ৩০০ মার্কিন ডলারের ফ্রি ট্রায়াল প্রোগ্রামের জন্য যোগ্য।
৩. শুরু করার আগে
এপিআই সক্ষম করুন
BigQuery Studio Notebooks ব্যবহার করার আগে, আমাদের অবশ্যই নিম্নলিখিত API গুলি সক্রিয় করতে হবে:
- কম্পিউট ইঞ্জিন এপিআই
- ডেটাফর্ম এপিআই
- ভার্টেক্স এআই এপিআই
ম্যানুয়ালি চালু করার জন্য, Bigquery-তে যান। এডিটর পেনের ট্যাব বারে, + চিহ্নের পাশের অ্যারো ড্রপ-ডাউনে ক্লিক করুন, Notebook-এর উপর পয়েন্টারটি ধরে রাখুন এবং তারপর BigQuery টেমপ্লেট অথবা Empty Notebook অথবা Spark টেমপ্লেট নির্বাচন করুন।
Enable Core feature API উইন্ডোতে, BigQuery Unified API-তে Enable-এ ক্লিক করুন।
কাজ শেষ হলে, সক্ষম করুন এবং তারপর বন্ধ করুন-এ ক্লিক করুন। আরও বিস্তারিত জানতে অনুগ্রহ করে অ্যাসেট ম্যানেজমেন্টের জন্য BigQuery Studio সক্ষম করা দেখুন।
৪. একটি পাবলিক ডেটাসেট পড়ুন
প্রথমে, BigQuery Studio নোটবুকগুলিতে স্পার্ক চালানোর জন্য আমরা অস্থায়ী ব্যবহারের উদ্দেশ্যে একটি GCS বাকেট তৈরি করব।
- Google Cloud Console-এ BigQuery-তে যান।
- এডিটর পেনের ট্যাব বারে, + চিহ্নের পাশের অ্যারো ড্রপ-ডাউনে ক্লিক করুন, নোটবুক (Notebook) -এর উপর পয়েন্টারটি ধরে রাখুন এবং তারপরে এম্পটি নোটবুক (Empty Notebook) নির্বাচন করুন।
- কোড সেলে ক্লিক করে GCS বাকেট তৈরি করার জন্য নিচের cli স্ক্রিপ্টটি টাইপ করুন, তারপর রান সেল বোতামে ক্লিক করুন অথবা Shift + Enter চাপুন।
!gsutil mb -p <your_project_id> -c STANDARD -l US gs://ioxid2025-<your_project_id>
গুগল ক্লাউড প্রজেক্ট তৈরি করার সময় আপনি যা নির্বাচন করেছেন, সেই অনুযায়ী <your_project_id>-এর মানগুলি আপডেট করুন। GCS ইউনিক বাকেট নেম তৈরি করার জন্য আপনার প্রজেক্ট আইডি দিয়ে <your_project_id>-এর মানগুলি আপডেট করুন। এরপর, কোড সেলটি রান করার জন্য রান সেল বোতামে ক্লিক করুন অথবা Shift + Enter চাপুন।
এরপরে, আমরা একটি স্পার্ক সেশন শুরু করব। এই কোডল্যাবে আমরা SparkSession লাইব্রেরি ব্যবহার করব, যদিও BigQuery Studio Notebook-এ স্পার্ক চালানোর জন্য Dataproc-এর সক্ষমতা কাজে লাগাতে আমরা DataprocSession ব্যবহার করতে পারি।
- স্পার্ক সেশন শুরু করতে কোড সেলে ক্লিক করে নিচের cli স্ক্রিপ্টটি টাইপ করুন। রান সেল বোতামে ক্লিক করুন অথবা Shift + Enter চাপুন।
# Import required libraries
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum, count, countDistinct, when, expr, date_format
from pyspark.sql.types import DecimalType
# Initialize Spark session with BigQuery connector
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum, count, countDistinct, when, expr, date_format
from pyspark.sql.types import DecimalType
# Initialize Spark session with BigQuery connector
spark = SparkSession.builder \
.appName("Google Analytics ETL with Apache Spark") \
.config("spark.jars.packages", "com.google.cloud.spark:spark-bigquery-with-dependencies_2.12:0.32.0") \
.getOrCreate()
spark
প্রত্যাশিত আউটপুট :
SparkSession - in-memory SparkContext Spark UI Version v3.5.4 Master local[*] AppName Google Analytics ETL with Apache Spark
- GCP প্রজেক্ট এবং GCS টেম্পোরারি বাকেট সেট করার জন্য কোড সেলে ক্লিক করে নিচের cli স্ক্রিপ্টটি টাইপ করুন।
# Set GCP project and temporary bucket
project_id = "your-gcp-project-id" # Replace with your GCP project ID
bucket = "your-gcs-bucket" # Replace with your GCS bucket for temporary files spark.conf.set("temporaryGcsBucket", bucket)
গুগল অ্যানালিটিক্স নমুনা ডেটাবেস
গুগল অ্যানালিটিক্স স্যাম্পল ডেটাবেসটি গুগল ক্লাউডের পাবলিক ডেটাসেট প্রোগ্রামের মাধ্যমে বিগকোয়েরিতে সরবরাহ করা হয়। এই ডেটাসেটটি বিগকোয়েরিতে গুগল মার্চেন্ডাইজ স্টোর থেকে সংগৃহীত ১২ মাসের (আগস্ট ২০১৬ থেকে আগস্ট ২০১৭) অস্পষ্ট গুগল অ্যানালিটিক্স ৩৬০ ডেটা প্রদান করে। গুগল মার্চেন্ডাইজ স্টোর হলো একটি বাস্তব ই-কমার্স স্টোর যা গুগল-ব্র্যান্ডের পণ্য বিক্রি করে। এটি ব্যবসায়িক ডেটা বিশ্লেষণ করার এবং অ্যানালিটিক্স ৩৬০ ডেটা বিশ্লেষণে বিগকোয়েরি ব্যবহারের সুবিধাগুলো জানার একটি চমৎকার উপায় । ডেটা সম্পর্কে আরও জানুন।
ডেটাটি সাধারণত একটি ই-কমার্স ওয়েবসাইটে যা দেখা যায় তার অনুরূপ এবং এতে নিম্নলিখিত তথ্যগুলো অন্তর্ভুক্ত রয়েছে:
- ট্র্যাফিক উৎস ডেটা: ওয়েবসাইট পরিদর্শকরা কোথা থেকে আসেন সে সম্পর্কিত তথ্য, যার মধ্যে অর্গানিক ট্র্যাফিক, পেইড সার্চ ট্র্যাফিক এবং ডিসপ্লে ট্র্যাফিকের ডেটা অন্তর্ভুক্ত।
- কন্টেন্ট ডেটা: সাইটে ব্যবহারকারীদের আচরণ সম্পর্কিত তথ্য, যেমন পরিদর্শকদের দেখা পৃষ্ঠাগুলির ইউআরএল, তারা কন্টেন্টের সাথে কীভাবে মিথস্ক্রিয়া করে, ইত্যাদি।
- লেনদেন সংক্রান্ত তথ্য: গুগল মার্চেন্ডাইজ স্টোর ওয়েবসাইটে সংঘটিত লেনদেন সম্পর্কিত তথ্য।
অ্যাপাচি স্পার্কে নমুনা শীর্ষ ৫টি ডেটা প্রদর্শন করতে নিচের কোডটি চালান।
# EXTRACT: Read data from BigQuery
print("Extracting data from BigQuery...")
ga_df = spark.read.format("bigquery") \
.option("table", "bigquery-public-data.google_analytics_sample.ga_sessions_20170801") \
.load()
# Show schema sample data
print("Sample data:")
ga_df.show(5, truncate=False)
প্রত্যাশিত আউটপুট :
Extracting data from BigQuery...
Sample data:
+---------+-----------+----------+--------------+--------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+------+--------+---------------+--------------------+
|visitorId|visitNumber|visitId |visitStartTime|date |totals |trafficSource |device |geoNetwork |customDimensions |hits |fullVisitorId |userId|clientId|channelGrouping|socialEngagementType|
+---------+-----------+----------+--------------+--------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+------+--------+---------------+--------------------+
|NULL |1 |1501591568|1501591568 |20170801|{1, 1, 1, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, 1} |{NULL, (not set), (direct), (none), NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL} |{Chrome, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop} |{Europe, Southern Europe, Greece, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, tellas.gr, not available in demo dataset, not available in demo dataset, not available in demo dataset} |[] |[{1, 0, 5, 46, NULL, true, true, true, https://www.google.gr/, {/google+redesign/bags/google+zipper+front+sports+bag.axd, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /bags/, /google+zipper+front+sports+bag.axd, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/bags/google+zipper+front+sports+bag.axd, shop.googlemerchandisestore.com/google+redesign/bags/google+zipper+front+sports+bag.axd, shop.googlemerchandisestore.com/google+redesign/bags/google+zipper+front+sports+bag.axd, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No, : }, NULL, NULL, {(not set), Bags, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}] |3418334011779872055|NULL |NULL |Organic Search |Not Socially Engaged|
|NULL |2 |1501589647|1501589647 |20170801|{1, 1, 1, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1}|{/analytics/web/, (not set), analytics.google.com, referral, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL}|{Chrome, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop} |{Asia, Southern Asia, India, Maharashtra, (not set), Mumbai, not available in demo dataset, unknown.unknown, not available in demo dataset, not available in demo dataset, not available in demo dataset} |[{4, APAC}] |[{1, 0, 5, 14, NULL, true, true, true, https://analytics.google.com/analytics/web/, {/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /shop+by+brand/, /youtube, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No, : }, NULL, NULL, {(not set), Brands, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}] |2474397855041322408|NULL |NULL |Referral |Not Socially Engaged|
|NULL |1 |1501616621|1501616621 |20170801|{1, 1, 1, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, 1} |{/analytics/web/, (not set), analytics.google.com, referral, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL}|{Chrome, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop} |{Europe, Northern Europe, United Kingdom, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, as9105.com, not available in demo dataset, not available in demo dataset, not available in demo dataset} |[{4, EMEA}] |[{1, 0, 12, 43, NULL, true, true, true, https://analytics.google.com/analytics/web/?utm_source=demoaccount&utm_medium=demoaccount&utm_campaign=demoaccount, {/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /shop+by+brand/, /youtube, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No, : }, NULL, NULL, {(not set), Brands, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}]|5870462820713110108|NULL |NULL |Referral |Not Socially Engaged|
|NULL |1 |1501601200|1501601200 |20170801|{1, 1, 1, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, 1} |{/analytics/web/, (not set), analytics.google.com, referral, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL}|{Firefox, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop}|{Americas, Northern America, United States, Texas, Dallas-Ft. Worth TX, Dallas, not available in demo dataset, h5colo.com, not available in demo dataset, not available in demo dataset, not available in demo dataset} |[{4, North America}]|[{1, 0, 8, 26, NULL, true, true, true, https://analytics.google.com/analytics/web/, {/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /shop+by+brand/, /youtube, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No, : }, NULL, NULL, {(not set), Brands, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}] |9397809171349480379|NULL |NULL |Referral |Not Socially Engaged|
|NULL |1 |1501615525|1501615525 |20170801|{1, 1, 1, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, 1} |{/analytics/web/, (not set), adwords.google.com, referral, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL} |{Chrome, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop} |{Americas, Northern America, United States, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, (not set), not available in demo dataset, not available in demo dataset, not available in demo dataset}|[{4, North America}]|[{1, 0, 12, 25, NULL, true, true, true, https://adwords.google.com/analytics/web/?__o=cues&authuser=0, {/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /shop+by+brand/, /youtube, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No, : }, NULL, NULL, {(not set), Brands, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}] |6089902943184578335|NULL |NULL |Referral |Not Socially Engaged|
+---------+-----------+----------+--------------+--------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+------+--------+---------------+--------------------+
only showing top 5 rows
৫. ডিভাইস ব্রাউজার অনুযায়ী ডেটা একত্রিত করুন (মৌলিক মেট্রিক)
এই কোডটি ডিভাইস ব্রাউজার অনুযায়ী গুগল অ্যানালিটিক্স ডেটা একত্রিত করে এবং মোট সেশন, ভিজিট, হিট, পেজভিউ, বাউন্স, সাইটে কাটানো সময়, ইউনিক ভিজিটর, ও প্রতি ভিজিটে গড় সময়ের মতো বিভিন্ন মেট্রিক গণনা করে, তারপর একটি কলামের নাম পরিবর্তন করে এবং ফলাফলগুলো সাজিয়ে নেয়।
print("Transformation 1: Aggregating by device browser...")
device_agg = ga_df.groupBy("device.browser") \
.agg(
count("*").alias("total_sessions"),
sum("totals.visits").alias("total_visits"),
sum("totals.hits").alias("total_hits"),
sum("totals.pageviews").alias("total_pageviews"),
sum("totals.bounces").alias("total_bounces"),
sum("totals.timeOnSite").alias("total_time_on_site"),
countDistinct("fullVisitorId").alias("unique_visitors"),
(sum("totals.timeOnSite")/sum("totals.visits")).alias("avg_time_per_visit")
) \
.withColumnRenamed("browser", "device_browser") \
.orderBy("total_sessions", ascending=False)
# Show sample transformed data
print("\nDevice Aggregation Sample:")
device_agg.show(5)
প্রত্যাশিত আউটপুট :
Transformation 1: Aggregating by device browser... Device Aggregation Sample: +-----------------+--------------+------------+----------+---------------+-------------+------------------+---------------+------------------+ | device_browser|total_sessions|total_visits|total_hits|total_pageviews|total_bounces|total_time_on_site|unique_visitors|avg_time_per_visit| +-----------------+--------------+------------+----------+---------------+-------------+------------------+---------------+------------------+ | Chrome| 1900| 1900| 10896| 8956| 870| 354691| 1689|186.67947368421054| | Safari| 397| 397| 1260| 1137| 218| 43150| 364|108.69017632241814| | Firefox| 101| 101| 390| 343| 59| 10659| 95|105.53465346534654| |Internet Explorer| 54| 54| 107| 102| 36| 5589| 50| 103.5| | Edge| 23| 23| 63| 55| 12| 2623| 23|114.04347826086956| +-----------------+--------------+------------+----------+---------------+-------------+------------------+---------------+------------------+ only showing top 5 rows
৬. রাজস্ব গণনার মাধ্যমে ট্র্যাফিকের উৎস বিশ্লেষণ
এই কোডটি ট্র্যাফিকের উৎস এবং মাধ্যম অনুসারে গুগল অ্যানালিটিক্স ডেটা বিশ্লেষণ করে সেশন সংখ্যা, লেনদেন, মোট আয়, প্রতি সেশনে আয় এবং অনন্য ভিজিটরের মতো মেট্রিকগুলো গণনা করে, তারপর কলামগুলোর নাম পরিবর্তন করে এবং মোট আয় অনুসারে ফলাফলগুলো সাজায়।
print("Transformation 2: Analyzing traffic sources...")
traffic_source_agg = ga_df.groupBy("trafficSource.source", "trafficSource.medium") \
.agg(
count("*").alias("session_count"),
sum(when(col("totals.transactions").isNotNull(), 1).otherwise(0)).alias("transactions"),
sum("totals.totalTransactionRevenue").cast(DecimalType(20,2)).alias("total_revenue"),
(sum("totals.totalTransactionRevenue")/count("*")).cast(DecimalType(10,2)).alias("revenue_per_session"),
countDistinct("fullVisitorId").alias("unique_visitors")
) \
.withColumnRenamed("source", "traffic_source") \
.withColumnRenamed("medium", "traffic_medium") \
.orderBy("total_revenue", ascending=False)
print("\nTraffic Source Aggregation Sample:")
traffic_source_agg.show(5)
প্রত্যাশিত আউটপুট :
Transformation 2: Analyzing traffic sources... Traffic Source Aggregation Sample: +--------------------+--------------+-------------+------------+-------------+-------------------+---------------+ | traffic_source|traffic_medium|session_count|transactions|total_revenue|revenue_per_session|unique_visitors| +--------------------+--------------+-------------+------------+-------------+-------------------+---------------+ | (direct)| (none)| 2166| 42|8872040000.00| 4096048.01| 1943| | mail.google.com| referral| 2| 1| 17960000.00| 8980000.00| 2| | google.com.tw| referral| 1| 0| NULL| NULL| 1| |analytics.google.com| referral| 57| 0| NULL| NULL| 53| | quora.com| referral| 6| 0| NULL| NULL| 5| +--------------------+--------------+-------------+------------+-------------+-------------------+---------------+ only showing top 5 rows
৭. ভৌগোলিক বিশ্লেষণ
এই কোডটি দেশ ও অঞ্চল অনুযায়ী গুগল অ্যানালিটিক্স ডেটার ভৌগোলিক বিশ্লেষণ করে, সেশন সংখ্যা, মোট পেজভিউ, সাইটে কাটানো মোট সময়, প্রতি সেশনে গড় সময় এবং ইউনিক ভিজিটর গণনা করে, তারপর কলামগুলোর নাম পরিবর্তন করে এবং সেশন সংখ্যা অনুসারে ডেটা সাজায়।
print("Transformation 3: Geographic analysis...")
geo_agg = ga_df.groupBy("geoNetwork.country", "geoNetwork.region") \
.agg(
count("*").alias("session_count"),
sum("totals.pageviews").alias("total_pageviews"),
sum("totals.timeOnSite").alias("total_time_on_site"),
(sum("totals.timeOnSite")/count("*")).alias("avg_time_per_session"),
countDistinct("fullVisitorId").alias("unique_visitors")
) \
.withColumnRenamed("country", "country") \
.withColumnRenamed("region", "region") \
.orderBy("session_count", ascending=False)
print("\nGeographic Aggregation Sample:")
geo_agg.show(5)
প্রত্যাশিত আউটপুট :
Transformation 3: Geographic analysis... Geographic Aggregation Sample: +--------------+--------------------+-------------+---------------+------------------+--------------------+---------------+ | country| region|session_count|total_pageviews|total_time_on_site|avg_time_per_session|unique_visitors| +--------------+--------------------+-------------+---------------+------------------+--------------------+---------------+ | United States|not available in ...| 564| 2326| 97829| 173.45567375886526| 494| | United States| California| 420| 3102| 116563| 277.5309523809524| 347| | United States| New York| 109| 845| 39976| 366.7522935779817| 84| |United Kingdom|not available in ...| 82| 161| 7791| 95.01219512195122| 79| | India|not available in ...| 62| 139| 2869| 46.274193548387096| 61| +--------------+--------------------+-------------+---------------+------------------+--------------------+---------------+ only showing top 5 rows
৮. সময়-ভিত্তিক বিশ্লেষণ
এই কোডটি visitStartTime কলাম থেকে ঘন্টা বের করে সময়-ভিত্তিক বিশ্লেষণ করে, তারপর প্রতিটি ঘন্টার জন্য সেশন সংখ্যা, লেনদেন, মোট আয় এবং মোট পেজভিউ গণনা করার জন্য ডেটাগুলোকে ঘন্টা অনুসারে গ্রুপ করে এবং সবশেষে ফলাফলগুলোকে ঘন্টা অনুযায়ী সাজায়।
print("Transformation 4: Time-based analysis...")
hourly_agg = ga_df.withColumn("hour", date_format(col("visitStartTime").cast("timestamp"), "H")) \
.groupBy("hour") \
.agg(
count("*").alias("session_count"),
sum("totals.transactions").alias("transactions"),
sum("totals.totalTransactionRevenue").cast(DecimalType(20,2)).alias("total_revenue"),
sum("totals.pageviews").alias("total_pageviews")
) \
.orderBy("hour")
print("\nHourly Aggregation Sample:")
hourly_agg.show(5)
প্রত্যাশিত আউটপুট :
Transformation 4: Time-based analysis... Hourly Aggregation Sample: +----+-------------+------------+-------------+---------------+ |hour|session_count|transactions|total_revenue|total_pageviews| +----+-------------+------------+-------------+---------------+ | 0| 87| NULL| NULL| 372| | 1| 102| NULL| NULL| 494| | 10| 67| NULL| NULL| 149| | 11| 73| NULL| NULL| 167| | 12| 99| NULL| NULL| 313| +----+-------------+------------+-------------+---------------+ only showing top 5 rows
৯. ফলাফল BigQuery টেবিলে লিখুন
এই কোডটি একটি ডাইরেক্ট রাইট মেথড ব্যবহার করে চারটি অ্যাগ্রিগেটেড ডেটাফ্রেম ( device_agg , traffic_source_agg , geo_agg , এবং hourly_agg ) গুগল বিগকোয়েরির আলাদা আলাদা টেবিলে এক্সপোর্ট করে এবং বিদ্যমান টেবিল থাকলে সেগুলোকে ওভাররাইট করে।
# Write to BigQuery tables
print("\nLoading data to BigQuery...")
# Set output tables
device_output_table = f"{project_id}.analytics_sample.device_aggregation"
traffic_output_table = f"{project_id}.analytics_sample.traffic_source_aggregation"
geo_output_table = f"{project_id}.analytics_sample.geo_aggregation"
hourly_output_table = f"{project_id}.analytics_sample.hourly_aggregation"
dataset_id = "demo" # Replace with your BigQuery dataset ID
# Set BigQuery output table
device_output_table = f"{project_id}.{dataset_id}.device_aggregation"
traffic_output_table = f"{project_id}.{dataset_id}.traffic_source_aggregation"
geo_output_table = f"{project_id}.{dataset_id}.geo_aggregation"
hourly_output_table = f"{project_id}.{dataset_id}.hourly_aggregation"
# Write each DataFrame to BigQuery
device_agg.write \
.format("bigquery") \
.option("table", device_output_table) \
.option("writeMethod", "direct") \
.mode("overwrite") \
.save()
traffic_source_agg.write \
.format("bigquery") \
.option("table", traffic_output_table) \
.option("writeMethod", "direct") \
.mode("overwrite") \
.save()
geo_agg.write \
.format("bigquery") \
.option("table", geo_output_table) \
.option("writeMethod", "direct") \
.mode("overwrite") \
.save()
hourly_agg.write \
.format("bigquery") \
.option("table", hourly_output_table) \
.option("writeMethod", "direct") \
.mode("overwrite") \
.save()
কিছু রূপান্তর করার পর ডেটা সফলভাবে সংরক্ষিত হয়েছে কিনা তা নিশ্চিত করতে BigQuery-তে আউটপুট টেবিলটি পরীক্ষা করুন।
ডিভাইস_এগ্রিগেশন টেবিল
ভূ-একত্রীকরণ সারণী
ঘণ্টাভিত্তিক সমষ্টিগত সারণী
ট্র্যাফিক_উৎস_একত্রীকরণ
১০. BigQuery Studio নোটবুক কোড সমন্বয় করুন (ঐচ্ছিক)
আপনি নিম্নলিখিত উপায়ে BigQuery Studio নোটবুক কোড পরিচালনা করতে পারেন:
- গুগল ক্লাউড কনসোল থেকে নোটবুক কোড শিডিউল করুন ( নোটবুকের মূল্য প্রযোজ্য)।
- নোটবুক কোডকে ডেটাপ্রোক সার্ভারলেস ব্যাচ ওয়ার্কলোড হিসেবে চালান ( ডেটাপ্রোক সার্ভারলেস-এর মূল্য প্রযোজ্য)।
এই কোডল্যাবে আমরা গুগল ক্লাউড কনসোল থেকে শিডিউল নোটবুক কোড ব্যবহার করব।
- নোটবুক টুলবারে, শিডিউল- এ ক্লিক করুন।
- শিডিউল নোটবুক প্যানে, শিডিউল নেম ফিল্ডে শিডিউলটির জন্য একটি নাম লিখুন।
- প্রমাণীকরণ বিভাগে, আপনার গুগল অ্যাকাউন্ট ব্যবহারকারীর পরিচয়পত্র অথবা একটি পরিষেবা অ্যাকাউন্ট দিয়ে নোটবুকটিকে অনুমোদন করুন।
- আপনার গুগল অ্যাকাউন্টের ব্যবহারকারীর পরিচয়পত্র ( প্রিভিউ ) ব্যবহার করতে, ‘আমার ব্যবহারকারীর পরিচয়পত্র দিয়ে চালান’ নির্বাচন করুন।
- একটি সার্ভিস অ্যাকাউন্ট ব্যবহার করতে, ‘Execute with selected service account’ নির্বাচন করুন, তারপর একটি সার্ভিস অ্যাকাউন্ট নির্বাচন করুন।
- নোটবুক অপশনস সেকশনের রানটাইম টেমপ্লেট ফিল্ডে, একটি কোলাব নোটবুক রানটাইম টেমপ্লেট অথবা ডিফল্ট রানটাইম স্পেসিফিকেশন নির্বাচন করুন। কোলাব নোটবুক রানটাইম টেমপ্লেট তৈরি করার বিস্তারিত তথ্যের জন্য, ‘একটি রানটাইম টেমপ্লেট তৈরি করুন’ দেখুন।
- ক্লাউড স্টোরেজ বাকেট ফিল্ডে, ব্রাউজ-এ ক্লিক করুন এবং একটি ক্লাউড স্টোরেজ বাকেট নির্বাচন বা তৈরি করুন। নির্বাচিত সার্ভিস অ্যাকাউন্টটিকে অবশ্যই নির্বাচিত বাকেটটিতে স্টোরেজ অ্যাডমিন (
roles/storage.admin) IAM রোল প্রদান করতে হবে। আরও তথ্যের জন্য, নোটবুক শিডিউলিং সক্ষম করুন দেখুন। - শিডিউল ফ্রিকোয়েন্সি বিভাগে, নিম্নলিখিতগুলি করুন:
- রিপিটস মেনুতে, নির্ধারিত নোটবুক চালানোর পৌনঃপুনিকতা নির্বাচন করুন।
- 'At time' ফিল্ডে, নির্ধারিত নোটবুক রানের জন্য সময় লিখুন।
- টাইমজোন মেনু থেকে শিডিউলের জন্য টাইমজোন নির্বাচন করুন।
- শিডিউল তৈরি করুন- এ ক্লিক করুন। আপনি যদি আপনার প্রমাণীকরণ পদ্ধতি হিসেবে ‘আমার ব্যবহারকারীর পরিচয়পত্র দিয়ে চালান’ নির্বাচন করে থাকেন, তাহলে আপনাকে অবশ্যই আপনার গুগল অ্যাকাউন্ট অনুমোদন করতে হবে ( প্রিভিউ )।
১১. পরিষ্কার করা
এই কোডল্যাবে ব্যবহৃত রিসোর্সগুলির জন্য আপনার গুগল ক্লাউড অ্যাকাউন্টে চার্জ হওয়া এড়াতে, এই ধাপগুলি অনুসরণ করুন:
- গুগল ক্লাউড কনসোলে, রিসোর্স পরিচালনা (Manage resources) পৃষ্ঠায় যান।
- প্রজেক্ট তালিকা থেকে, আপনি যে প্রজেক্টটি মুছতে চান সেটি নির্বাচন করুন এবং তারপর ডিলিট বোতামে ক্লিক করুন।
- ডায়ালগ বক্সে প্রজেক্ট আইডি টাইপ করুন এবং তারপর প্রজেক্টটি মুছে ফেলার জন্য 'শাট ডাউন'-এ ক্লিক করুন।
১২. অভিনন্দন
আপনি BigQuery Studio Notebooks-এ Apache Spark Serverless ব্যবহার করে ডেটা রূপান্তর এবং বিশ্লেষণের কাজ শেষ করেছেন। এই প্রক্রিয়ার মধ্যে আপনি Google Cloud-এর পাবলিক ডেটাসেটগুলো অন্বেষণ করেছেন, BigQuery Studio Notebook-এ Apache Spark ব্যবহার করে সার্ভারলেস ETL সম্পাদন করেছেন এবং BigQuery Studio Notebook-কে অর্কেস্ট্রেট করেছেন। চমৎকার কাজ!
পরবর্তী পদক্ষেপ :
- স্বয়ংক্রিয়করণের উদ্দেশ্যে সার্ভিস অ্যাকাউন্ট ব্যবহার করে নোটবুক পরিচালনা করুন।
- ETL জব চালানোর সময় এর সময়কাল নিরীক্ষণের জন্য স্ক্রিপ্ট যোগ করুন।
- নোটবুকগুলো স্থাপন করুন।
- BigQuery Studio নোটবুকগুলিতে Serverless Dataproc-এর সাথে প্রকৃত ডিস্ট্রিবিউটেড অ্যাপাচি স্পার্ক সক্ষমতা কাজে লাগাতে DataprocSparkSession ব্যবহার করুন।
- BigQuery Studio-তে Apache Spark-এর জন্য স্টোরড প্রসিডিউর তৈরি করুন। এর মাধ্যমে আপনি আপনার PySpark কোডকে আরও ভালোভাবে সংগঠিত, পুনঃব্যবহারযোগ্য এবং রক্ষণাবেক্ষণযোগ্য করার জন্য OOP নীতি প্রয়োগ করতে পারেন।
তথ্যসূত্র :