آماده سازی بدون زحمت داده در BigQuery: راهنمای کم کدنویسی

1. مقدمه

تحلیلگران داده اغلب با داده‌های ارزشمندی مواجه می‌شوند که در قالب‌های نیمه ساختار یافته مانند بارهای JSON قفل شده‌اند. استخراج و آماده سازی این داده ها برای تجزیه و تحلیل و یادگیری ماشین به طور سنتی یک مانع فنی مهم بوده است که به طور سنتی به اسکریپت های پیچیده ETL و مداخله یک تیم مهندسی داده نیاز دارد.

این آزمایشگاه کد یک نقشه فنی برای تحلیلگران داده فراهم می کند تا به طور مستقل بر این چالش غلبه کنند. این یک رویکرد "کد پایین" برای ساخت خط لوله هوش مصنوعی سرتاسر را نشان می دهد. شما یاد خواهید گرفت که چگونه از یک فایل CSV خام در Google Cloud Storage به یک ویژگی توصیه مبتنی بر هوش مصنوعی، تنها با استفاده از ابزارهای موجود در BigQuery Studio بروید.

هدف اصلی این است که یک جریان کاری قوی، سریع و تحلیلگر پسند را نشان دهید که فراتر از فرآیندهای پیچیده و کد سنگین حرکت می کند تا ارزش واقعی کسب و کار را از داده های شما ایجاد کند.

پیش نیازها

  • درک اولیه از Google Cloud Console
  • مهارت های اولیه در رابط خط فرمان و Google Cloud Shell

چیزی که یاد خواهید گرفت

  • نحوه جذب و تبدیل فایل CSV مستقیماً از Google Cloud Storage با استفاده از BigQuery Data Preparation.
  • نحوه استفاده از تبدیل های بدون کد برای تجزیه و صاف کردن رشته های JSON تو در تو در داده های خود.
  • نحوه ایجاد یک مدل راه دور BigQuery ML که برای جاسازی متن به یک مدل پایه Vertex AI متصل می شود.
  • نحوه استفاده از تابع ML.GENERATE_TEXT_EMBEDDING برای تبدیل داده های متنی به بردارهای عددی.
  • نحوه استفاده از تابع ML.DISTANCE برای محاسبه شباهت کسینوس و یافتن مشابه ترین موارد در مجموعه داده شما.

آنچه شما نیاز دارید

  • یک حساب Google Cloud و پروژه Google Cloud
  • یک مرورگر وب مانند کروم

مفاهیم کلیدی

  • آماده سازی BigQuery Data: ابزاری در BigQuery Studio که یک رابط تعاملی و بصری برای تمیز کردن و آماده سازی داده ها فراهم می کند. این تغییرات را پیشنهاد می کند و به کاربران اجازه می دهد خطوط لوله داده را با حداقل کد ایجاد کنند.
  • مدل از راه دور BQML: یک شی ML BigQuery که به عنوان یک پروکسی برای یک مدل میزبانی شده در Vertex AI (مانند Gemini) عمل می کند. این به شما امکان می دهد با استفاده از نحو آشنای SQL، مدل های هوش مصنوعی قدرتمند و از پیش آموزش دیده را فراخوانی کنید.
  • جاسازی برداری: نمایش عددی داده ها، مانند متن یا تصاویر. در این کد لبه، ما توضیحات متنی آثار هنری را به بردار تبدیل می‌کنیم، که در آن توضیحات مشابه، بردارهایی را در فضای چند بعدی به هم نزدیک‌تر می‌کنند.
  • تشابه کسینوس: یک معیار ریاضی که برای تعیین شباهت دو بردار استفاده می شود. این هسته منطق موتور توصیه ما است که توسط تابع ML.DISTANCE برای یافتن "نزدیک ترین" (مشابه ترین) آثار هنری استفاده می شود.

2. راه اندازی و الزامات

Cloud Shell را راه اندازی کنید

در حالی که Google Cloud را می توان از راه دور از لپ تاپ شما کار کرد، در این کد لبه از Google Cloud Shell استفاده خواهید کرد، یک محیط خط فرمان که در Cloud اجرا می شود.

از Google Cloud Console ، روی نماد Cloud Shell در نوار ابزار بالا سمت راست کلیک کنید:

55efc1aaa7a4d3ad.png

تهیه و اتصال به محیط فقط چند لحظه طول می کشد. وقتی تمام شد، باید چیزی شبیه به این را ببینید:

7ffe5cbb04455448.png

این ماشین مجازی با تمام ابزارهای توسعه که شما نیاز دارید بارگذاری شده است. این یک فهرست اصلی 5 گیگابایتی دائمی را ارائه می دهد و در Google Cloud اجرا می شود و عملکرد و احراز هویت شبکه را تا حد زیادی افزایش می دهد. تمام کارهای شما در این کد لبه را می توان در یک مرورگر انجام داد. شما نیازی به نصب چیزی ندارید.

API های مورد نیاز را فعال کنید و محیط را پیکربندی کنید

در داخل Cloud Shell، دستورات زیر را اجرا کنید تا ID پروژه خود را تنظیم کنید، متغیرهای محیطی را تعریف کنید، و تمام API های لازم را برای این Codelab فعال کنید.

export PROJECT_ID=$(gcloud config get-value project)
gcloud config set project $PROJECT_ID
export LOCATION="US"
export GCS_BUCKET_NAME="met-artworks-source-${PROJECT_ID}" # Must be a globally unique name

gcloud services enable bigquery.googleapis.com \
                       storage.googleapis.com \
                       aiplatform.googleapis.com \
                       bigqueryconnection.googleapis.com

یک مجموعه داده BigQuery و یک سطل GCS ایجاد کنید

یک مجموعه داده BigQuery جدید برای قرار دادن جداول ما و یک سطل Google Cloud Storage برای ذخیره فایل CSV منبع ما ایجاد کنید.

# Create the BigQuery Dataset in the US multi-region
bq --location=$LOCATION mk --dataset $PROJECT_ID:met_art_dataset

# Create the GCS Bucket
gcloud storage buckets create gs://$GCS_BUCKET_NAME --project=$PROJECT_ID --location=$LOCATION

داده های نمونه را آماده و آپلود کنید

مخزن GitHub حاوی نمونه فایل CSV را کلون کنید و سپس آن را در سطل GCS که ایجاد کرده اید آپلود کنید.

# Clone the repository
git clone https://github.com/GoogleCloudPlatform/devrel-demos.git

# Navigate to the correct directory
cd devrel-demos/data-analytics/dataprep

# Upload the CSV file to your GCS bucket
gsutil cp dataprep-met-bqml.csv gs://$GCS_BUCKET_NAME/

3. از GCS به BigQuery با آماده سازی داده

در این بخش، ما از یک رابط بصری و بدون کد برای جذب فایل CSV خود از GCS، پاکسازی و بارگذاری آن در جدول BigQuery جدید استفاده خواهیم کرد.

آماده سازی داده را راه اندازی کنید و به منبع متصل شوید

  1. در Google Cloud Console، به استودیوی BigQuery بروید.

8825270159447e89.png

  1. در صفحه خوش آمدگویی، روی کارت آماده سازی داده ها کلیک کنید تا شروع شود.

8b7b3ce147a55647.png

  1. اگر این اولین بار است که می‌خواهید، ممکن است لازم باشد APIهای مورد نیاز را فعال کنید. برای هر دو "Gemini for Google Cloud API" و "BigQuery Unified API" روی Enable کلیک کنید. هنگامی که آنها فعال شدند، می توانید این پانل را ببندید.

e0a128b8b63137e6.png

1ab7db12bd624bff.png

  1. در پنجره اصلی آماده‌سازی داده، در بخش «انتخاب سایر منابع داده»، روی Google Cloud Storage کلیک کنید. با این کار پانل "آماده سازی داده ها" در سمت راست باز می شود.

5ef56d07d54abab4.png

  1. روی دکمه Browse کلیک کنید تا فایل منبع خود را انتخاب کنید.

95899fcbb7383967.png

  1. به سطل GCS که قبلا ایجاد کردید ( met-artworks-source-... ) بروید و فایل dataprep-met-bqml.csv را انتخاب کنید. روی Select کلیک کنید.

3590d0841677ad01.png

107797a8f134b248.png

  1. در مرحله بعد، باید یک جدول مرحله بندی را پیکربندی کنید.
  2. برای Dataset، met_art_dataset را که ایجاد کردید انتخاب کنید.
  3. برای نام جدول، نامی را وارد کنید، به عنوان مثال، temp .
  4. روی ایجاد کلیک کنید.

694a7064eb1f2109.png

تبدیل و پاکسازی داده ها

  1. آماده سازی داده BigQuery اکنون پیش نمایشی از CSV را بارگیری می کند. ستون label_details_json را که شامل رشته طولانی JSON است، پیدا کنید. برای انتخاب روی سربرگ ستون کلیک کنید.

345e09d8222ef0d6.png

  1. در پانل پیشنهادات در سمت راست، Gemini در BigQuery به طور خودکار تغییرات مرتبط را پیشنهاد می‌کند. روی دکمه Apply در کارت "Flattening column label_details_json " کلیک کنید. با این کار، فیلدهای تودرتو ( description ، score ، و غیره) در ستون‌های سطح بالای خود استخراج می‌شوند.

a432edf49f182ea3.png

  1. روی ستون object_id کلیک کنید و روی دکمه application در "تبدیل ستون object_id از نوع string به int64 کلیک کنید.

46523a5dd6b7c495.png

a190553c0a153393.png

مقصد را تعریف کنید و کار را اجرا کنید

  1. در پانل سمت راست، روی دکمه Destination کلیک کنید تا خروجی تبدیل خود را پیکربندی کنید.

90b0d1e641d6ace9.png

  1. جزئیات مقصد را تنظیم کنید:
  2. مجموعه داده باید با met_art_dataset از قبل پر شود.
  3. یک نام جدول جدید برای خروجی وارد کنید: met_art_flatten_table .
  4. روی ذخیره کلیک کنید.

cda9b07bfd5ff6a3.png

  1. روی دکمه Run کلیک کنید و منتظر بمانید تا کار آماده سازی داده ها تکمیل شود.

9be3f3baecc7ee93.png

  1. می توانید پیشرفت کار را در تب Executions در پایین صفحه مشاهده کنید. بعد از چند لحظه کار به پایان می رسد.

df820e4a5183e9b9.png

f9329c88a7fdb535.png

4. ایجاد جاسازی های برداری با BQML

اکنون که داده‌های ما تمیز و ساختار یافته هستند، از BigQuery ML برای کار اصلی هوش مصنوعی استفاده می‌کنیم: تبدیل توضیحات متنی اثر هنری به جاسازی‌های برداری عددی.

یک اتصال BigQuery ایجاد کنید

برای اینکه به BigQuery اجازه دهید با خدمات Vertex AI ارتباط برقرار کند، ابتدا باید یک اتصال BigQuery ایجاد کنید.

  1. در پانل اکسپلورر استودیو BigQuery، روی دکمه "+ افزودن داده" کلیک کنید.

eef6c5c73cf8736.png

  1. در پانل سمت راست، از نوار جستجو برای تایپ Vertex AI استفاده کنید. آن و سپس فدراسیون BigQuery را از لیست فیلتر شده انتخاب کنید.

32e9632e84dd1ae7.png

7feedffb98bb288a.png

  1. با این کار فرم منبع داده خارجی باز می شود. مشخصات زیر را پر کنید:
  • شناسه اتصال: شناسه اتصال را وارد کنید (به عنوان مثال، bqml-vertex-connection )
  • نوع مکان: مطمئن شوید که چند منطقه انتخاب شده است.
  • مکان: مکان را انتخاب کنید (به عنوان مثال، US ).

c0681e76440a18cd.png

  1. پس از ایجاد اتصال، یک گفتگوی تأیید ظاهر می شود. روی Go to Connection یا External connections در تب Explorer کلیک کنید. در صفحه جزئیات اتصال، شناسه کامل را در کلیپ بورد خود کپی کنید. این هویت حساب سرویس است که BigQuery برای فراخوانی Vertex AI استفاده می کند.

fd0d82f3265f1def.png

  1. در منوی ناوبری Google Cloud Console، به IAM & admin > IAM بروید.

de8a0fe28f8dee8f.png

  1. روی دکمه "اعطای دسترسی" کلیک کنید
  2. حساب سرویسی که در مرحله قبل کپی کرده بودید را در قسمت New principals قرار دهید.
  3. " کاربر Vertex AI " را در منوی Role اختصاص دهید و روی "ذخیره" کلیک کنید.

8b2c89b8c97e37cc.png

این مرحله مهم تضمین می‌کند که BigQuery مجوز مناسب برای استفاده از مدل‌های Vertex AI از طرف شما را دارد.

یک مدل از راه دور ایجاد کنید

در BigQuery Studio، یک برگه ویرایشگر SQL جدید باز کنید. اینجاست که مدل BQML که به Gemini متصل می شود را تعریف می کنید.

این بیانیه مدل جدیدی را آموزش نمی دهد. این به سادگی یک مرجع در BigQuery ایجاد می کند که با استفاده از اتصالی که به تازگی مجاز کرده اید به یک مدل قدرتمند و از پیش آموزش دیده gemini-embedding-001 اشاره می کند.

کل اسکریپت SQL را در زیر کپی کرده و در ویرایشگر BigQuery قرار دهید.

ba0a9c9d951c0f71.png

CREATE OR REPLACE MODEL `met_art_dataset.embedding_model`
REMOTE WITH CONNECTION `US.bqml-vertex-connection`
OPTIONS (endpoint = 'gemini-embedding-001');

ایجاد جاسازی

اکنون، ما از مدل BQML خود برای ایجاد تعبیه‌های برداری استفاده می‌کنیم. به‌جای تبدیل صرفاً یک برچسب متنی برای هر ردیف، از رویکرد پیچیده‌تری برای ایجاد «خلاصه معنایی» غنی‌تر و معنادارتر برای هر اثر هنری استفاده می‌کنیم. این منجر به تعبیه‌های با کیفیت بالاتر و توصیه‌های دقیق‌تر می‌شود.

این پرس و جو یک مرحله پیش پردازش حیاتی را انجام می دهد:

  • ابتدا از یک بند WITH برای ایجاد یک جدول موقت استفاده می کند.
  • در داخل آن، ما GROUP BY هر object_id گروه بندی می کنیم تا تمام اطلاعات مربوط به یک اثر هنری واحد را در یک ردیف ترکیب کنیم.
  • ما از تابع STRING_AGG برای ادغام تمام توضیحات متن جداگانه (مانند «پرتره»، «زن»، «روغن روی بوم») در یک رشته متنی جامع و منفرد استفاده می‌کنیم، و آنها را بر اساس امتیاز مرتبط مرتب می‌کنیم.

این متن ترکیبی به هوش مصنوعی زمینه بسیار غنی‌تری در مورد اثر هنری می‌دهد که منجر به جاسازی‌های برداری ظریف‌تر و قدرتمندتر می‌شود.

در یک تب جدید ویرایشگر SQL، کوئری زیر را پیست کرده و اجرا کنید:

CREATE OR REPLACE TABLE `met_art_dataset.artwork_embeddings` AS
WITH artwork_semantic_text AS (
  -- First, we group all text labels for each artwork into a single row.
  SELECT
    object_id,
    ANY_VALUE(title) AS title,
    ANY_VALUE(artist_display_name) AS artist_display_name,
    -- STRING_AGG combines all descriptions into one comma-separated string,
    -- ordering them by score to put the most relevant labels first.
    STRING_AGG(description, ', ' ORDER BY score DESC) AS aggregated_labels
  FROM
    `met_art_dataset.met_art_flatten_table`
  GROUP BY
    object_id
)
SELECT
  *
FROM ML.GENERATE_TEXT_EMBEDDING(
  MODEL `met_art_dataset.embedding_model`,
  (
    -- We pass the new, combined string as the content to be embedded.
    SELECT
      object_id,
      title,
      artist_display_name,
      aggregated_labels AS content
    FROM
      artwork_semantic_text
  )
);

این پرس و جو تقریباً 10 دقیقه طول خواهد کشید. پس از تکمیل پرس و جو، نتایج را تأیید کنید. در پانل Explorer، جدول artwork_embeddings جدید خود را پیدا کنید و روی آن کلیک کنید. در نمایشگر طرحواره جدول، object_id ، ستون ml_generate_text_embedding_result جدید حاوی بردارها، و همچنین ستون aggregated_labels که به عنوان متن منبع استفاده شده است را خواهید دید.

c894ad3624d710e7.png

5. یافتن آثار هنری مشابه با SQL

با جاسازی‌های برداری با کیفیت بالا و غنی از زمینه ایجاد شده، یافتن آثار هنری مشابه موضوعی به سادگی اجرای یک پرس و جوی SQL است. ما از تابع ML.DISTANCE برای محاسبه شباهت کسینوس بین بردارها استفاده می کنیم. از آنجایی که جاسازی‌های ما از متن جمع‌آوری شده ایجاد شده‌اند، نتایج شباهت دقیق‌تر و مرتبط‌تر خواهد بود.

  1. در یک تب جدید ویرایشگر SQL، پرس و جو زیر را جایگذاری کنید. این کوئری منطق اصلی یک برنامه پیشنهادی را شبیه سازی می کند:
  • ابتدا وکتور را برای یک اثر هنری خاص انتخاب می‌کند (در این مورد، سروهای ون گوگ، که دارای یک object_id 436535 است).
  • سپس فاصله بین آن بردار واحد و سایر بردارهای جدول را محاسبه می کند.
  • در نهایت، نتایج را بر اساس فاصله مرتب می کند (فاصله کوچکتر به معنای مشابه بیشتر) برای یافتن 10 نزدیکترین مسابقه برتر. 
WITH selected_artwork AS (
  SELECT text_embedding
  FROM `met_art_dataset.artwork_embeddings`
  WHERE object_id = 436535
)
SELECT
  base.object_id,
  base.title,
  base.artist_display_name,
  -- ML.DISTANCE calculates the cosine distance between the two vectors.
  -- A smaller distance means the items are more similar.
  ML.DISTANCE(base.text_embedding, (SELECT text_embedding FROM selected_artwork), 'COSINE') AS similarity_distance
FROM
  `met_art_dataset.artwork_embeddings` AS base, selected_artwork
ORDER BY
  similarity_distance
LIMIT 10;
  1. پرس و جو را اجرا کنید. نتایج، object_id s را با نزدیک‌ترین تطابق‌ها در بالا فهرست می‌کنند. اثر هنری منبع ابتدا با فاصله 0 ظاهر می شود. این منطق اصلی است که موتور توصیه AI را نیرو می دهد و شما آن را کاملاً در BigQuery فقط با استفاده از SQL ساخته اید.

6. (اختیاری) اجرای نسخه ی نمایشی در Cloud Shell

برای زنده کردن مفاهیم این نرم افزار کد، مخزنی که شبیه سازی کردید شامل یک برنامه وب ساده است. این نسخه نمایشی اختیاری از جدول artwork_embeddings که ایجاد کرده‌اید برای تقویت موتور جستجوی بصری استفاده می‌کند و به شما امکان می‌دهد توصیه‌های مبتنی بر هوش مصنوعی را در عمل ببینید.

برای اجرای دمو در Cloud Shell، مراحل زیر را دنبال کنید:

  1. Set Environment Variables: قبل از اجرای برنامه، باید متغیرهای محیطی PROJECT_ID و BIGQUERY_DATASET را تنظیم کنید. 
export PROJECT_ID=$(gcloud config get-value project)
export BIGQUERY_DATASET=met_art_dataset
export REGION='us-central1'
bq cp bigquery-public-data:the_met.images $PROJECT_ID:met_art_dataset.images
  1. وابستگی ها را نصب کنید و سرور باطن را راه اندازی کنید. 
cd ~/devrel-demos/data-analytics/dataprep/backend/ && npm install
node server.js
  1. برای اجرای برنامه frontend به تب ترمینال دوم نیاز دارید. برای باز کردن یک تب جدید Cloud Shell روی نماد "+" کلیک کنید.

bbc9ecde0ff417f4.png

  1. اکنون در تب جدید دستور زیر را برای نصب وابستگی ها و اجرای سرور frontend اجرا کنید 
cd ~/devrel-demos/data-analytics/dataprep/frontend/ && npm install
npm run dev
  1. پیش نمایش برنامه: در نوار ابزار Cloud Shell، روی نماد Web Preview کلیک کنید و Preview در پورت 5173 را انتخاب کنید. با این کار یک تب مرورگر جدید با برنامه در حال اجرا باز می شود. اکنون می توانید از برنامه برای جستجوی آثار هنری استفاده کنید و جستجوی مشابه را در عمل مشاهده کنید.

d4dc326a225354c9.png

b9956f2c29af2864.png

  1. برای اتصال این نسخه نمایشی تصویری به کاری که در ویرایشگر BigQuery SQL انجام دادید، سعی کنید "Cypresses" را در نوار جستجو تایپ کنید. این همان اثر هنری ( object_id=436535 ) است که در جستار ML.DISTANCE استفاده کردید. سپس روی تصویر Cypresses کلیک کنید وقتی در پنل سمت چپ ظاهر شد، نتایج را در سمت راست مشاهده خواهید کرد. این برنامه مشابه ترین آثار هنری را نمایش می دهد و قدرت جستجوی شباهت برداری که ساخته اید را به صورت بصری نشان می دهد.

a030b3ee20c8703d.png

7. محیط خود را تمیز کنید

برای جلوگیری از تحمیل هزینه های آتی به حساب Google Cloud خود برای منابع استفاده شده در این کد لبه، باید منابعی را که ایجاد کرده اید حذف کنید.

دستورات زیر را در ترمینال Cloud Shell خود اجرا کنید تا حساب سرویس، اتصال BigQuery، سطل GCS و مجموعه داده BigQuery حذف شود. 

# Re-run these exports if your Cloud Shell session timed out
export PROJECT_ID=$(gcloud config get-value project)
export LOCATION="US"
export GCS_BUCKET_NAME="met-artworks-source-${PROJECT_ID}"
export BQ_CONNECTION_ID="bqml-vertex-connection"

اتصال BigQuery و سطل GCS را حذف کنید 

# Delete the BigQuery connection
bq rm --connection $LOCATION.$BQ_CONNECTION_ID

# Delete the GCS bucket and its contents
gcloud storage rm --recursive gs://$GCS_BUCKET_NAME

مجموعه داده BigQuery را حذف کنید

در نهایت مجموعه داده BigQuery را حذف کنید. این دستور غیر قابل برگشت است. پرچم -f (force) مجموعه داده و تمام جداول آن را بدون درخواست تایید حذف می کند.

# Manually type this command to confirm you are deleting the correct dataset
bq rm -r -f --dataset $PROJECT_ID:met_art_dataset

8. تبریک می گویم!

شما با موفقیت یک خط لوله داده سرتاسر و مبتنی بر هوش مصنوعی ساخته اید.

شما با یک فایل CSV خام در یک سطل GCS شروع کردید، از رابط کم‌کد BigQuery Data Prep برای جذب و صاف کردن داده‌های پیچیده JSON استفاده کردید، یک مدل از راه دور BQML قدرتمند برای ایجاد جاسازی‌های برداری با کیفیت بالا با یک مدل Gemini ایجاد کردید، و یک عبارت جستجوی مشابه را برای یافتن موارد مرتبط اجرا کردید.

شما اکنون به الگوی اساسی برای ایجاد گردش‌های کاری به کمک هوش مصنوعی در Google Cloud مجهز شده‌اید که داده‌های خام را با سرعت و سادگی به برنامه‌های هوشمند تبدیل می‌کند.

بعد چه می شود؟

  • نتایج خود را در Looker Studio تجسم کنید: جدول artwork_embeddings BigQuery خود را مستقیماً به Looker Studio متصل کنید (رایگان است!). شما می توانید یک داشبورد تعاملی بسازید که در آن کاربران می توانند یک اثر هنری را انتخاب کنند و یک گالری بصری از مشابه ترین قطعات را بدون نوشتن هیچ کد ظاهری ببینند.
  • خودکارسازی با پرس و جوهای زمان بندی شده: برای به روز نگه داشتن جاسازی های خود به ابزار هماهنگ سازی پیچیده نیاز ندارید. برای اجرای خودکار درخواست ML.GENERATE_TEXT_EMBEDDING به صورت روزانه یا هفتگی از ویژگی داخلی BigQuery Scheduled Queries استفاده کنید.
  • ایجاد یک برنامه با Gemini CLI: از Gemini CLI برای ایجاد یک برنامه کامل به سادگی با توصیف نیاز خود در متن ساده استفاده کنید. این به شما امکان می دهد بدون نوشتن دستی کد پایتون، به سرعت یک نمونه اولیه برای جستجوی مشابه خود بسازید.
  • مستندات را بخوانید: