BigQuery-এর সাথে প্রচারাভিযানের অর্থের দিকে তাকিয়ে

এই কোডল্যাবে, আপনি শিখবেন কীভাবে BigQuery-এর কিছু উন্নত বৈশিষ্ট্য ব্যবহার করতে হয়, যার মধ্যে রয়েছে:

• জাভাস্ক্রিপ্টে ব্যবহারকারী-সংজ্ঞায়িত ফাংশন
• বিভাজিত টেবিল
• Google ক্লাউড স্টোরেজ এবং Google ড্রাইভে থাকা ডেটার বিরুদ্ধে সরাসরি প্রশ্ন।

আপনি US ফেডারেল নির্বাচন কমিশন থেকে ডেটা নেবেন, এটি পরিষ্কার করবেন এবং BigQuery-এ লোড করবেন। আপনি সেই ডেটাসেটের কিছু আকর্ষণীয় প্রশ্ন জিজ্ঞাসা করার সুযোগও পাবেন।

যদিও এই কোডল্যাবটি BigQuery-এর সাথে কোনো পূর্ব অভিজ্ঞতা গ্রহণ করে না, তবে SQL এর কিছু বোঝাপড়া আপনাকে এর থেকে আরও বেশি কিছু পেতে সাহায্য করবে।

আপনি কি শিখবেন

• এসকিউএল-এ করা কঠিন কাজগুলি সম্পাদন করতে JavaScript ব্যবহারকারী সংজ্ঞায়িত ফাংশনগুলি কীভাবে ব্যবহার করবেন।
• Google ক্লাউড স্টোরেজ এবং Google ড্রাইভের মতো অন্যান্য ডেটা স্টোরে থাকা ডেটাতে ETL (এক্সট্র্যাক্ট, ট্রান্সফর্ম, লোড) অপারেশন করতে BigQuery কীভাবে ব্যবহার করবেন।

আপনি কি প্রয়োজন হবে

• বিলিং সক্ষম সহ একটি Google ক্লাউড প্রকল্প৷
• একটি Google ক্লাউড স্টোরেজ বাকেট
• Google Cloud SDK ইনস্টল করা হয়েছে

আপনি কিভাবে এই টিউটোরিয়াল ব্যবহার করবেন?

শুধুমাত্র মাধ্যমে এটি পড়ুন এটি পড়ুন এবং অনুশীলনগুলি সম্পূর্ণ করুন

BigQuery-এর সাথে আপনার অভিজ্ঞতার স্তরকে কীভাবে রেট করবেন?

নবজাতক মধ্যবর্তী দক্ষ

স্ব-গতিসম্পন্ন পরিবেশ সেটআপ

আপনার যদি ইতিমধ্যে একটি Google অ্যাকাউন্ট না থাকে (Gmail বা Google Apps), তাহলে আপনাকে অবশ্যই একটি তৈরি করতে হবে। Google ক্লাউড প্ল্যাটফর্ম কনসোলে সাইন-ইন করুন ( console.cloud.google.com ) এবং একটি নতুন প্রকল্প তৈরি করুন:

প্রজেক্ট আইডিটি মনে রাখবেন, সমস্ত Google ক্লাউড প্রকল্প জুড়ে একটি অনন্য নাম (উপরের নামটি ইতিমধ্যে নেওয়া হয়েছে এবং আপনার জন্য কাজ করবে না, দুঃখিত!)। এটি পরে এই কোডল্যাবে PROJECT_ID হিসাবে উল্লেখ করা হবে।

এর পরে, Google ক্লাউড সংস্থানগুলি ব্যবহার করার জন্য আপনাকে ক্লাউড কনসোলে বিলিং সক্ষম করতে হবে৷

এই কোডল্যাবের মাধ্যমে চালানোর জন্য আপনার কয়েক ডলারের বেশি খরচ করা উচিত নয়, তবে আপনি যদি আরও সংস্থান ব্যবহার করার সিদ্ধান্ত নেন বা আপনি সেগুলিকে চলমান রেখে দেন তবে এটি আরও বেশি হতে পারে (এই নথির শেষে "পরিষ্কার" বিভাগটি দেখুন)।

Google ক্লাউড প্ল্যাটফর্মের নতুন ব্যবহারকারীরা $300 বিনামূল্যের ট্রায়ালের জন্য যোগ্য৷

গুগল ক্লাউড শেল

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

এই ডেবিয়ান-ভিত্তিক ভার্চুয়াল মেশিনটি আপনার প্রয়োজনীয় সমস্ত বিকাশের সরঞ্জামগুলির সাথে লোড করা হয়েছে। এটি একটি ক্রমাগত 5GB হোম ডিরেক্টরি অফার করে এবং Google ক্লাউডে চলে, নেটওয়ার্ক কর্মক্ষমতা এবং প্রমাণীকরণকে ব্যাপকভাবে উন্নত করে। এর মানে হল এই কোডল্যাবের জন্য আপনার যা দরকার তা হল একটি ব্রাউজার (হ্যাঁ, এটি একটি Chromebook এ কাজ করে)।

Google ক্লাউড শেল সক্রিয় করতে, বিকাশকারী কনসোল থেকে উপরের ডানদিকের বোতামটি ক্লিক করুন (এটি পরিবেশের সাথে সংযুক্ত হতে এবং সরবরাহ করতে কয়েক মুহূর্ত সময় নিতে হবে):

"স্টার্ট ক্লাউড শেল" বোতামে ক্লিক করুন:

একবার ক্লাউড শেলের সাথে সংযুক্ত হয়ে গেলে, আপনি দেখতে পাবেন যে আপনি ইতিমধ্যেই প্রমাণীকরণ করেছেন এবং প্রকল্পটি ইতিমধ্যেই আপনার PROJECT_ID এ সেট করা আছে :

gcloud auth list

কমান্ড আউটপুট

Credentialed accounts:
 - <myaccount>@<mydomain>.com (active)

দ্রষ্টব্য: gcloud হল Google ক্লাউড প্ল্যাটফর্মের জন্য শক্তিশালী এবং একীভূত কমান্ড-লাইন টুল। সম্পূর্ণ ডকুমেন্টেশন https://cloud.google.com/sdk/gcloud থেকে পাওয়া যায়। এটি ক্লাউড শেল-এ প্রাক-ইনস্টল করা আছে এবং আপনি অবশ্যই ট্যাব-সম্পূর্ণতার জন্য এর সমর্থন উপভোগ করবেন।

gcloud config list project

কমান্ড আউটপুট

[core]
project = <PROJECT_ID>

ক্লাউড শেল ডিফল্টরূপে কিছু এনভায়রনমেন্ট ভেরিয়েবল সেট করে যা আপনি ভবিষ্যতের কমান্ড চালানোর সময় কার্যকর হতে পারে।

echo $GOOGLE_CLOUD_PROJECT

কমান্ড আউটপুট

<PROJECT_ID>

যদি কোনো কারণে প্রকল্পটি সেট করা না থাকে, তাহলে কেবল নিম্নলিখিত কমান্ডটি জারি করুন:

gcloud config set project <PROJECT_ID>

আপনার PROJECT_ID খুঁজছেন? সেটআপের ধাপে আপনি কোন আইডি ব্যবহার করেছেন তা দেখুন বা কনসোল ড্যাশবোর্ডে দেখুন:

গুরুত্বপূর্ণ: অবশেষে, ডিফল্ট জোন এবং প্রকল্প কনফিগারেশন সেট করুন:

gcloud config set compute/zone us-central1-f

আপনি বিভিন্ন জোন বিভিন্ন চয়ন করতে পারেন. অঞ্চল ও অঞ্চল ডকুমেন্টেশনে আরও জানুন।

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

এই কোডল্যাবে BigQuery কোয়েরি চালানোর জন্য, আপনার নিজের ডেটাসেটের প্রয়োজন হবে। এটির জন্য একটি নাম বাছুন, যেমন campaign_funding । আপনার শেলে নিম্নলিখিত কমান্ডগুলি চালান (উদাহরণস্বরূপ CloudShell):

$ DATASET=campaign_funding
$ bq mk -d ${DATASET}
Dataset 'bq-campaign:campaign_funding' successfully created.

আপনার ডেটাসেট তৈরি হওয়ার পরে, আপনাকে যেতে প্রস্তুত থাকতে হবে। এই কমান্ডটি চালানোর ফলে আপনি bq কমান্ড লাইন ক্লায়েন্ট সেটআপ সঠিকভাবে পেয়েছেন, প্রমাণীকরণ কাজ করছে এবং আপনি যে ক্লাউড প্রকল্পের অধীনে কাজ করছেন তাতে আপনার লেখার অ্যাক্সেস রয়েছে তা যাচাই করতে সহায়তা করবে। আপনার যদি একাধিক প্রকল্প থাকে, তাহলে আপনাকে একটি তালিকা থেকে আপনার পছন্দের একটি নির্বাচন করতে বলা হবে।

ইউএস ফেডারেল ইলেকশন কমিশনের ক্যাম্পেইন ফাইন্যান্স ডেটাসেট ডিকম্প্রেস করা হয়েছে এবং GCS বাকেট gs://campaign-funding/ এ কপি করা হয়েছে।

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

$ gsutil cp gs://campaign-funding/indiv16.txt .
$ tail indiv16.txt

এটি পৃথক অবদান ফাইলের বিষয়বস্তু প্রদর্শন করা উচিত. এই কোডল্যাবের জন্য আমরা তিন ধরনের ফাইল দেখব: স্বতন্ত্র অবদান ( indiv*.txt ), প্রার্থী ( cn*.txt ), এবং কমিটি ( cm*.txt )। আপনি যদি আগ্রহী হন তবে সেই অন্যান্য ফাইলগুলিতে কী আছে তা পরীক্ষা করতে একই প্রক্রিয়াটি ব্যবহার করুন।

আমরা সরাসরি BigQuery-এ কাঁচা ডেটা লোড করতে যাচ্ছি না; পরিবর্তে, আমরা Google ক্লাউড স্টোরেজ থেকে এটি অনুসন্ধান করতে যাচ্ছি। এটি করার জন্য, আমাদের স্কিমা এবং এটি সম্পর্কে কিছু তথ্য জানতে হবে।

ডেটাসেট এখানে ফেডারেল নির্বাচনের ওয়েবসাইটে বর্ণনা করা হয়েছে। আমরা যে টেবিলগুলি দেখব তার স্কিমাগুলি হল:

• ব্যক্তিগত অবদান
• কমিটি
• প্রার্থী

টেবিলের সাথে লিঙ্ক করার জন্য, আমাদের তাদের জন্য একটি টেবিল সংজ্ঞা তৈরি করতে হবে যাতে স্কিমাগুলি অন্তর্ভুক্ত থাকে। পৃথক টেবিল সংজ্ঞা তৈরি করতে নিম্নলিখিত কমান্ডগুলি চালান:

$ bq mkdef --source_format=CSV \
    gs://campaign-funding/indiv*.txt \
"CMTE_ID, AMNDT_IND, RPT_TP, TRANSACTION_PGI, IMAGE_NUM, TRANSACTION_TP, ENTITY_TP, NAME, CITY, STATE, ZIP_CODE, EMPLOYER, OCCUPATION, TRANSACTION_DT, TRANSACTION_AMT:FLOAT, OTHER_ID, TRAN_ID, FILE_NUM, MEMO_CD, MEMO_TEXT, SUB_ID" \
> indiv_def.json

আপনার প্রিয় পাঠ্য সম্পাদকের সাথে indiv_dev.json ফাইলটি খুলুন এবং বিষয়বস্তুগুলি দেখুন; এটিতে json থাকবে যা বর্ণনা করে কিভাবে FEC ডেটা ফাইলকে ব্যাখ্যা করতে হয়।

আমাদের csvOptions বিভাগে দুটি ছোট সম্পাদনা করতে হবে। "|" এর একটি fieldDelimiter মান যোগ করুন এবং "" (খালি স্ট্রিং) এর একটি quote মান। এটি প্রয়োজনীয় কারণ ডেটা ফাইলটি আসলে কমা-বিচ্ছিন্ন নয়, এটি পাইপ-বিচ্ছিন্ন:

$ sed -i 's/"fieldDelimiter": ","/"fieldDelimiter": "|"/g; s/"quote": "\\""/"quote":""/g' indiv_def.json

indiv_dev.json ফাইলটি এখন পড়া উচিত:

    "fieldDelimiter": "|", 
    "quote":"", 

যেহেতু কমিটি এবং প্রার্থী টেবিলের জন্য টেবিলের সংজ্ঞা তৈরি করা একই রকম, এবং স্কিমাটিতে একটি শালীন বিট বয়লারপ্লেট রয়েছে, আসুন কেবল সেই ফাইলগুলি ডাউনলোড করি।

$ gsutil cp gs://campaign-funding/candidate_def.json .
Copying gs://campaign-funding/candidate_def.json...
/ [1 files][  945.0 B/  945.0 B]                                                
Operation completed over 1 objects/945.0 B. 

$ gsutil cp gs://campaign-funding/committee_def.json .
Copying gs://campaign-funding/committee_def.json...
/ [1 files][  949.0 B/  949.0 B]                                                
Operation completed over 1 objects/949.0 B.  

এই ফাইলগুলো দেখতে indiv_dev.json ফাইলের মতো হবে। মনে রাখবেন আপনি indiv_def.json ফাইলটিও ডাউনলোড করতে পারেন, যদি আপনার সঠিক মান পেতে সমস্যা হয়।

এর পরে, আসুন আসলে এই ফাইলগুলির সাথে একটি BigQuery টেবিল লিঙ্ক করি৷ নিম্নলিখিত কমান্ড চালান:

$ bq mk --external_table_definition=indiv_def.json -t ${DATASET}.transactions 
Table 'bq-campaign:campaign_funding.transactions' successfully created.

$ bq mk --external_table_definition=committee_def.json -t ${DATASET}.committees 
Table 'bq-campaign:campaign_funding.committees' successfully created.

$ bq mk --external_table_definition=candidate_def.json -t ${DATASET}.candidates 
Table 'bq-campaign:campaign_funding.candidates' successfully created.

এটি তিনটি বিগকোয়েরি টেবিল তৈরি করবে: লেনদেন, কমিটি এবং প্রার্থী। আপনি এই সারণিগুলিকে জিজ্ঞাসা করতে পারেন যেমন সেগুলি সাধারণ BigQuery টেবিল, কিন্তু সেগুলি আসলে BigQuery-এ সংরক্ষিত নয়, সেগুলি Google ক্লাউড স্টোরেজে রয়েছে৷ আপনি যদি অন্তর্নিহিত ফাইলগুলি আপডেট করেন, আপডেটগুলি অবিলম্বে আপনার চালানো অনুসন্ধানগুলিতে প্রতিফলিত হবে।

দ্রষ্টব্য: Google ক্লাউড স্টোরেজের ফাইলগুলির উপর ক্যোয়ারী কার্যক্ষমতা নেটিভ BigQuery টেবিলের তুলনায় উল্লেখযোগ্যভাবে ধীর হতে পারে, তাই আপনি আপনার সমস্ত প্রশ্নের জন্য এটি ব্যবহার করতে নাও পারেন৷

এর পরে, আসুন আসলে কয়েকটি প্রশ্ন চালানোর চেষ্টা করি। BigQuery ওয়েব UI খুলুন।

বাম নেভিগেশন ফলকে আপনার ডেটাসেট খুঁজুন (আপনাকে উপরের বাম কোণে প্রকল্পের ড্রপডাউন পরিবর্তন করতে হতে পারে), বড় লাল 'কম্পোজ ক্যোয়ারী' বোতামে ক্লিক করুন এবং বাক্সে নিম্নলিখিত ক্যোয়ারীটি লিখুন:

SELECT * FROM [campaign_funding.transactions] 
WHERE EMPLOYER contains "GOOGLE" 
ORDER BY TRANSACTION_DT DESC
LIMIT 100

এটি Google-এর কর্মীদের দ্বারা সাম্প্রতিক 100টি প্রচারাভিযানের অনুদান খুঁজে পাবে। আপনি যদি চান, আশেপাশে খেলার চেষ্টা করুন এবং আপনার জিপ কোডের বাসিন্দাদের কাছ থেকে প্রচারাভিযানের দান খোঁজার চেষ্টা করুন বা আপনার শহরের সবচেয়ে বড় দান খুঁজে নিন।

ক্যোয়ারী এবং ফলাফল এই মত কিছু দেখতে হবে:

একটি জিনিস আপনি লক্ষ্য করতে পারেন, যাইহোক, আপনি সত্যিই বলতে পারবেন না যে এই অনুদানের প্রাপক কে ছিল। সেই তথ্য পেতে আমাদের কিছু অভিনব প্রশ্ন নিয়ে আসতে হবে।

দ্রষ্টব্য : BigQuery জানে না কত বড় টেবিল যদি তারা BigQuery স্টোরেজে না থাকে, তাই এই টেবিলগুলি দেখে মনে হতে পারে যে আপনি সেগুলিকে জিজ্ঞাসা করলে তারা 0 বাইট প্রক্রিয়া করবে৷ এটি এমন নয়, আপনি ক্যোয়ারী চালানোর আগে পর্যন্ত কতটা ডেটা প্রক্রিয়া করা হয়েছে তা আপনি জানতে পারবেন না।

বাম ফলকে লেনদেন টেবিলে ক্লিক করুন এবং স্কিমা ট্যাবে ক্লিক করুন। এটি নীচের স্ক্রিনশটের মতো হওয়া উচিত:

আমরা পূর্বে উল্লেখিত টেবিলের সংজ্ঞার সাথে মেলে এমন ক্ষেত্রগুলির একটি তালিকা দেখতে পাচ্ছি। আপনি লক্ষ্য করতে পারেন কোন প্রাপকের ক্ষেত্র নেই, বা কোন প্রার্থীকে অনুদান সমর্থিত তা বের করার কোন উপায় নেই। যাইহোক, CMTE_ID নামে একটি ক্ষেত্র আছে। এটি আমাদের সেই কমিটিকে লিঙ্ক করতে দেবে যেটি অনুদানের প্রাপক ছিল অনুদানের সাথে। এই এখনও সব যে দরকারী নয়.

এরপরে, এর স্কিমা চেক করতে কমিটির টেবিলে ক্লিক করুন। আমরা একটি CMET_ID পেয়েছি, যা আমাদের সাথে লেনদেনের টেবিলে যোগ দিতে পারে৷ আরেকটি ক্ষেত্র হল CAND_ID ; এটি প্রার্থীদের টেবিলে একটি CAND_ID টেবিলের সাথে যোগ করা যেতে পারে। অবশেষে, কমিটি টেবিলের মাধ্যমে আমরা লেনদেন এবং প্রার্থীদের মধ্যে একটি লিঙ্ক করেছি।

মনে রাখবেন যে GCS-ভিত্তিক টেবিলের জন্য কোনো পূর্বরূপ ট্যাব নেই। কারণ ডেটা পড়ার জন্য, BigQuery-কে একটি বাহ্যিক ডেটা উৎস থেকে পড়তে হবে। প্রার্থীদের টেবিলে একটি সাধারণ ' SELECT * ' ক্যোয়ারী চালিয়ে ডেটার নমুনা পাওয়া যাক।

SELECT * FROM [campaign_funding.candidates]
LIMIT 100

ফলাফল এই মত কিছু দেখতে হবে:

আপনি একটি জিনিস লক্ষ্য করতে পারেন, প্রার্থীর নামগুলি সমস্ত ক্যাপস এবং "শেষ নাম, প্রথম নাম" ক্রমে উপস্থাপন করা হয়েছে৷ এটা একটু বিরক্তিকর, যেহেতু আমরা প্রার্থীদের সম্পর্কে চিন্তা করি তা সত্যিই নয়; আমরা "ওবামা, বারাক" এর চেয়ে "বারাক ওবামা" দেখতে চাই। তাছাড়া, লেনদেনের সারণীতে লেনদেনের তারিখগুলি ( TRANSACTION_DT ) কিছুটা বিশ্রী। তারা YYYYMMDD বিন্যাসে স্ট্রিং মান। আমরা পরবর্তী বিভাগে এই quirks সম্বোধন করব.

এখন যেহেতু প্রার্থীদের সাথে লেনদেনগুলি কীভাবে সম্পর্কিত তা আমরা বুঝতে পেরেছি, আসুন কে কাকে টাকা দিচ্ছে তা খুঁজে বের করার জন্য একটি অনুসন্ধান চালাই। কম্পোজ বক্সে নিম্নলিখিত ক্যোয়ারীটি কাট এবং পেস্ট করুন:

SELECT affiliation, SUM(amount) AS amount
FROM (
  SELECT *
  FROM (
    SELECT
      t.amt AS amount,
      t.occupation AS occupation,
      c.affiliation AS affiliation,
    FROM (
      SELECT
        trans.TRANSACTION_AMT AS amt,
        trans.OCCUPATION AS occupation,
        cmte.CAND_ID AS CAND_ID
      FROM [campaign_funding.transactions] trans
      RIGHT OUTER JOIN EACH (
        SELECT
          CMTE_ID,
          FIRST(CAND_ID) AS CAND_ID
        FROM [campaign_funding.committees]
        GROUP EACH BY CMTE_ID ) cmte
      ON trans.CMTE_ID = cmte.CMTE_ID) AS t
    RIGHT OUTER JOIN EACH (
      SELECT
        CAND_ID,
        FIRST(CAND_PTY_AFFILIATION) AS affiliation,
      FROM [campaign_funding.candidates]
      GROUP EACH BY CAND_ID) c
    ON t.CAND_ID = c.CAND_ID )
  WHERE occupation CONTAINS "ENGINEER")
GROUP BY affiliation
ORDER BY amount DESC

এই প্রশ্নটি লেনদেনের টেবিলে কমিটির টেবিলে এবং তারপর প্রার্থীদের টেবিলে যোগ দেয়। এটি শুধুমাত্র তাদের পেশার শিরোনামে " ENGINEER " শব্দ সহ লোকেদের থেকে লেনদেন দেখে। ক্যোয়ারী দলীয় অধিভুক্তির দ্বারা ফলাফল একত্রিত করে; এটি আমাদের ইঞ্জিনিয়ারদের মধ্যে বিভিন্ন রাজনৈতিক দলকে দেওয়ার বণ্টন দেখতে দেয়।

আমরা দেখতে পাচ্ছি যে ইঞ্জিনিয়াররা একটি সুন্দর ভারসাম্যপূর্ণ দল, যা কমবেশি ডেমোক্র্যাট এবং রিপাবলিকানদের সমানভাবে দেয়। কিন্তু 'ডিএফএল' দল কী? শুধু একটি তিন অক্ষরের কোড না করে আসলেই কি পুরো নাম পাওয়া ভালো হবে না?

পার্টি কোড FEC ওয়েবসাইটে সংজ্ঞায়িত করা হয়। একটি টেবিল আছে যা পার্টি কোডের সাথে পুরো নামের সাথে মিলে যায় (এটি দেখা যাচ্ছে যে 'DFL' হল 'গণতান্ত্রিক-কৃষক-শ্রমিক')। যদিও আমরা আমাদের ক্যোয়ারীতে ম্যানুয়ালি অনুবাদগুলি সম্পাদন করতে পারি, এটি অনেক কাজের বলে মনে হয় এবং সিঙ্কে রাখা কঠিন৷

যদি আমরা প্রশ্নের অংশ হিসাবে HTML পার্স করতে পারি? সেই পৃষ্ঠার যে কোনও জায়গায় ডান ক্লিক করুন এবং "পৃষ্ঠা উত্স দেখুন" দেখুন। উৎসে অনেক শিরোনাম/বয়লারপ্লেট তথ্য রয়েছে, তবে <table> ট্যাগটি খুঁজুন। প্রতিটি ম্যাপিং সারি একটি HTML <tr> উপাদানে থাকে, নাম এবং কোড উভয়ই <td> উপাদানে মোড়ানো থাকে। প্রতিটি সারি এই মত কিছু দেখাবে:

এইচটিএমএল এই মত কিছু দেখায়:

<tr bgcolor="#F5F0FF">
    <td scope="row"><div align="left">ACE</div></td>
    <td scope="row">Ace Party</td>
    <td scope="row"></td>
</tr>

মনে রাখবেন যে BigQuery ওয়েব থেকে সরাসরি ফাইল পড়তে পারে না; কারণ বিগকুয়েরি হাজার হাজার কর্মী থেকে একযোগে একটি উৎসকে আঘাত করতে সক্ষম। যদি এটি র্যান্ডম ওয়েব পৃষ্ঠাগুলির বিরুদ্ধে চালানোর অনুমতি দেওয়া হয় তবে এটি মূলত পরিষেবা আক্রমণের একটি বিতরণ অস্বীকার (DDoS) হবে৷ FEC ওয়েব পৃষ্ঠা থেকে html ফাইলটি gs://campaign-funding এ সংরক্ষিত আছে।

ক্যাম্পেইন ফান্ডিং ডেটার উপর ভিত্তি করে আমাদের একটি টেবিল তৈরি করতে হবে। এটি আমাদের তৈরি করা অন্যান্য GCS-সমর্থিত টেবিলের মতো হবে। এখানে পার্থক্য হল যে আমাদের আসলে একটি স্কিমা নেই; আমরা প্রতি সারিতে একটি মাত্র ক্ষেত্র ব্যবহার করব এবং এটিকে ' data ' বলব। আমরা ভান করব যে এটি একটি CSV ফাইল, কিন্তু কমা-সীমাবদ্ধকরণের পরিবর্তে, আমরা একটি জাল বিভাজনকারী ( ` ) ব্যবহার করব এবং কোনও উদ্ধৃতি অক্ষর নেই৷

পার্টি লুকআপ টেবিল তৈরি করতে, কমান্ড লাইন থেকে নিম্নলিখিত কমান্ডগুলি চালান:

$ echo '{"csvOptions": {"allowJaggedRows": false, "skipLeadingRows": 0, "quote": "", "encoding": "UTF-8", "fieldDelimiter": "`", "allowQuotedNewlines": false}, "ignoreUnknownValues": true, "sourceFormat": "CSV", "sourceUris": ["gs://campaign-funding/party_codes.shtml"], "schema": {"fields": [{"type": "STRING", "name": "data"}]}}' > party_raw_def.json
$ bq mk --external_table_definition=party_raw_def.json \
   -t ${DATASET}.raw_party_codes 
Table 'bq-campaign:campaign_funding.raw_party_codes' successfully created.

আমরা এখন ফাইল পার্স করতে javascript ব্যবহার করব। BigQuery ক্যোয়ারী এডিটরের উপরের ডানদিকে "UDF Editor" লেবেলযুক্ত একটি বোতাম থাকতে হবে। একটি জাভাস্ক্রিপ্ট UDF সম্পাদনা করতে স্যুইচ করতে এটিতে ক্লিক করুন৷ UDF সম্পাদককে কিছু মন্তব্য করা বয়লারপ্লেট দিয়ে পরিপূর্ণ করা হবে।

এগিয়ে যান এবং এতে থাকা কোডটি মুছুন এবং নিম্নলিখিত কোডটি লিখুন:

function tableParserFun(row, emitFn) {
  if (row.data != null && row.data.match(/<tr.*<\/tr>/) !== null) {
    var txt = row.data
    var re = />\s*(\w[^\t<]*)\t*<.*>\s*(\w[^\t<]*)\t*</;
    matches = txt.match(re);
    if (matches !== null && matches.length > 2) {
        var result = {code: matches[1], name: matches[2]};
        emitFn(result);
    } else {
        var result = { code: 'ERROR', name: matches};
        emitFn(result);
    }
  }
}

bigquery.defineFunction(
  'tableParser',               // Name of the function exported to SQL
  ['data'],                    // Names of input columns
  [{'name': 'code', 'type': 'string'},  // Output schema
   {'name': 'name', 'type': 'string'}],
  tableParserFun // Reference to JavaScript UDF
);

এখানে জাভাস্ক্রিপ্ট দুটি ভাগে বিভক্ত; প্রথমটি একটি ফাংশন যা একটি সারি ইনপুট নেয় একটি পার্সড আউটপুট নির্গত করে। অন্যটি হল একটি সংজ্ঞা যা সেই ফাংশনটিকে একটি User Defined Function (UDF) হিসাবে tableParser নামের সাথে নিবন্ধিত করে এবং নির্দেশ করে যে এটি ' data ' নামে একটি ইনপুট কলাম নেয় এবং দুটি কলাম, কোড এবং নাম আউটপুট করে। কোড কলামটি হবে তিন অক্ষরের কোড, নামের কলামটি দলের পুরো নাম।

"ক্যোয়ারী এডিটর ট্যাবে" ফিরে যান এবং নিম্নলিখিত ক্যোয়ারী লিখুন:

SELECT code, name FROM tableParser([campaign_funding.raw_party_codes])
ORDER BY code

এই ক্যোয়ারীটি চালানোর ফলে কাঁচা এইচটিএমএল ফাইল পার্স হবে এবং স্ট্রাকচার্ড ফরম্যাটে ফিল্ডের মান আউটপুট হবে। বেশ চটকদার, তাই না? আপনি 'DFL' এর অর্থ কী তা বুঝতে পারেন কিনা দেখুন।

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

SELECT
  candidate,
  election_year,
  FIRST(candidate_affiliation) AS affiliation,
  SUM(amount) AS amount
FROM (
  SELECT 
    CONCAT(REGEXP_EXTRACT(c.candidate_name,r'\w+,[ ]+([\w ]+)'), ' ',
      REGEXP_EXTRACT(c.candidate_name,r'(\w+),')) AS candidate,
    pty.candidate_affiliation_name AS candidate_affiliation,
    c.election_year AS election_year,
    t.amt AS amount,
  FROM (
    SELECT
      trans.TRANSACTION_AMT AS amt,
      cmte.committee_candidate_id AS committee_candidate_id
    FROM [campaign_funding.transactions] trans
    RIGHT OUTER JOIN EACH (
      SELECT
        CMTE_ID,
        FIRST(CAND_ID) AS committee_candidate_id
      FROM [campaign_funding.committees]
      GROUP BY CMTE_ID ) cmte
    ON trans.CMTE_ID = cmte.CMTE_ID) AS t
  RIGHT OUTER JOIN EACH (
    SELECT
      CAND_ID AS candidate_id,
      FIRST(CAND_NAME) AS candidate_name,
      FIRST(CAND_PTY_AFFILIATION) AS affiliation,
      FIRST(CAND_ELECTION_YR) AS election_year,
    FROM [campaign_funding.candidates]
    GROUP BY candidate_id) c
  ON t.committee_candidate_id = c.candidate_id
  JOIN (
    SELECT
      code,
      name AS candidate_affiliation_name
    FROM (tableParser([campaign_funding.raw_party_codes]))) pty
  ON pty.code = c.affiliation )
GROUP BY candidate, election_year
ORDER BY amount DESC
LIMIT 100

এই ক্যোয়ারীটি দেখাবে কোন প্রার্থীরা প্রচারাভিযানে সবচেয়ে বেশি অনুদান পেয়েছেন এবং তাদের দলীয় সংশ্লিষ্টতা প্রকাশ করবে।

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

"বিকল্পগুলি দেখান" বোতামে ক্লিক করুন এবং তারপর " Destination Table " লেবেলের পাশের 'সিলেক্ট টেবিল' বোতামে ক্লিক করুন। আপনার campaign_funding ডেটাসেট বাছুন, এবং ' summary ' হিসাবে টেবিল আইডি লিখুন। ' allow large results দিন' চেকবক্স নির্বাচন করুন।

এখন নিম্নলিখিত ক্যোয়ারী চালান:

SELECT 
CONCAT(REGEXP_EXTRACT(c.candidate_name,r'\w+,[ ]+([\w ]+)'), ' ', REGEXP_EXTRACT(c.candidate_name,r'(\w+),')) 
  AS candidate,
pty.candidate_affiliation_name as candidate_affiliation,
INTEGER(c.election_year) as election_year,
c.candidate_state as candidate_state,
c.office as candidate_office,
t.name as name,
t.city as city,
t.amt as amount,
c.district as candidate_district,
c.ici as candidate_ici,
c.status as candidate_status,

t.memo as memo,
t.state as state,
LEFT(t.zip_code, 5) as zip_code,
t.employer as employer,
t.occupation as occupation,
USEC_TO_TIMESTAMP(PARSE_UTC_USEC(
CONCAT(RIGHT(t.transaction_date, 4), "-", 
      LEFT(t.transaction_date,2), "-", 
      RIGHT(LEFT(t.transaction_date,4), 2),
      " 00:00:00"))) as transaction_date,
t.committee_name as committee_name,
t.committe_designation as committee_designation,
t.committee_type as committee_type,
pty_cmte.committee_affiliation_name as committee_affiliation,
t.committee_org_type as committee_organization_type,
t.committee_connected_org_name as committee_organization_name,
t.entity_type as entity_type,
FROM (
SELECT 
trans.ENTITY_TP as entity_type,
trans.NAME as name,
trans.CITY as city,
trans.STATE as state,
trans.ZIP_CODE as zip_code,
trans.EMPLOYER as employer,
trans.OCCUPATION as occupation,
trans.TRANSACTION_DT as transaction_date,
trans.TRANSACTION_AMT as amt,
trans.MEMO_TEXT as memo,
cmte.committee_name as committee_name,
cmte.committe_designation as committe_designation,
cmte.committee_type as committee_type,
cmte.committee_affiliation as committee_affiliation,
cmte.committee_org_type as committee_org_type,
cmte.committee_connected_org_name as committee_connected_org_name,
cmte.committee_candidate_id as committee_candidate_id
FROM [campaign_funding.transactions] trans
RIGHT OUTER JOIN EACH (
SELECT
CMTE_ID,
FIRST(CMTE_NM) as committee_name,
FIRST(CMTE_DSGN) as committe_designation,
FIRST(CMTE_TP) as committee_type,
FIRST(CMTE_PTY_AFFILIATION) as committee_affiliation,
FIRST(ORG_TP) as committee_org_type,
FIRST(CONNECTED_ORG_NM) as committee_connected_org_name,
FIRST(CAND_ID) as committee_candidate_id
FROM [campaign_funding.committees]
GROUP BY CMTE_ID
) cmte 
ON trans.CMTE_ID = cmte.CMTE_ID) as t
RIGHT OUTER JOIN EACH 
  (SELECT CAND_ID as candidate_id,
      FIRST(CAND_NAME) as candidate_name,
      FIRST(CAND_PTY_AFFILIATION) as affiliation,
      INTEGER(FIRST(CAND_ELECTION_YR)) as election_year,
      FIRST(CAND_OFFICE_ST) as candidate_state,
      FIRST(CAND_OFFICE) as office,
      FIRST(CAND_OFFICE_DISTRICT) as district,
      FIRST(CAND_ICI) as ici,
      FIRST(CAND_STATUS) as status,
   FROM  [campaign_funding.candidates]
   GROUP BY candidate_id) c 
ON t.committee_candidate_id = c.candidate_id
JOIN (
SELECT code, name as candidate_affiliation_name 
FROM (tableParser([campaign_funding.raw_party_codes]))) pty
ON pty.code = c.affiliation
JOIN (
SELECT code, name as committee_affiliation_name 
FROM (tableParser([campaign_funding.raw_party_codes]))) pty_cmte
ON pty_cmte.code = t.committee_affiliation
WHERE t.amt > 0.0 and REGEXP_MATCH(t.state, "^[A-Z]{2}$") and t.amt < 1000000.0

এই প্রশ্নটি উল্লেখযোগ্যভাবে দীর্ঘ, এবং কিছু অতিরিক্ত পরিচ্ছন্নতার বিকল্প রয়েছে৷ উদাহরণ স্বরূপ, যেখানে পরিমাণ $1M-এর চেয়ে বেশি সেখানে এটি এমন কিছুকে উপেক্ষা করে। এটি " LASTNAME, FIRSTNAME " কে " FIRSTNAME LASTNAME " এ পরিণত করতে নিয়মিত অভিব্যক্তিও ব্যবহার করে৷ আপনি যদি দুঃসাহসিক বোধ করেন তবে আরও ভাল করার জন্য একটি UDF লেখার চেষ্টা করুন এবং ক্যাপিটালাইজেশন ঠিক করুন (যেমন " Firstname Lastname ")।

পরিশেষে, আপনার campaign_funding.summary টেবিলের বিরুদ্ধে কয়েকটি প্রশ্ন চালানোর চেষ্টা করুন যাতে যাচাই করা যায় যে সেই টেবিলের বিরুদ্ধে করা প্রশ্নগুলি দ্রুততর। প্রথমে গন্তব্য টেবিলের ক্যোয়ারী অপশনটি মুছে ফেলতে ভুলবেন না, অথবা আপনি আপনার সারাংশ টেবিল ওভাররাইট করতে পারেন!

আপনি এখন FEC ওয়েবসাইট থেকে BigQuery-এ ডেটা পরিষ্কার এবং আমদানি করেছেন!

আমরা কভার করেছি কি

• BigQuery-এ GCS-সমর্থিত টেবিল ব্যবহার করা।
• BigQuery-এ ব্যবহারকারী-নির্ধারিত ফাংশন ব্যবহার করা।

পরবর্তী পদক্ষেপ

• এই নির্বাচনী চক্র কে কাকে টাকা দিচ্ছে তা খুঁজে বের করার জন্য কিছু আকর্ষণীয় প্রশ্নের চেষ্টা করুন।

আরও জানুন

• ব্যবহারকারী সংজ্ঞায়িত ফাংশনগুলির সাথে আপনি কী করতে পারেন সে সম্পর্কে আরও জানুন।
• ফেডারেটেড ডেটা উত্স সম্পর্কে পড়ুন (GCS সহ)।
• প্রশ্ন পোস্ট করুন এবং google-bigquery ট্যাগের অধীনে Stackoverflow-এ উত্তর খুঁজুন।

আপনার মতামত জানান

• সমস্যাগুলি ফাইল করতে বা মতামত শেয়ার করতে এই পৃষ্ঠার নীচে বাম দিকের লিঙ্কটি ব্যবহার করতে বিনা দ্বিধায়!