نحوه استفاده از R با BigQuery

آیا برای استفاده از BigQuery باید کارت اعتباری وارد کنم؟

خیر، برای شروع استفاده از BigQuery نیازی به کارت اعتباری ندارید. اما بدون فعال کردن صورت‌حساب، حساب شما یک «جعبه ایمنی» BigQuery است و همه درخواست‌ها کار نمی‌کنند. من قویاً پیشنهاد می‌کنم یک منبع صورت‌حساب را به حساب خود اضافه کنید، حتی اگر بعید است که از سهمیه تجزیه و تحلیل رایگان BigQuery خود فراتر بروید.

اکنون – بالاخره! – بیایید نحوه استفاده از BigQuery با R.

را بررسی کنیم

به مجموعه داده BigQuery در R

متصل شوید

برای جستجوی داده‌های BigQuery با R و bigrquery، ابتدا باید یک اتصال به مجموعه داده با استفاده از این نحو راه‌اندازی کنید:

library(bigrquery)  
con <- dbConnect(
  bigquery(),
  project = project_id_containing_the_data,
  dataset = database_name
  billing = your_project_id_with_the_billing_source
)

اولین آرگومان تابع bigquery() از بسته bigrquery است که به dbConnect می گوید که می خواهید به منبع داده BigQuery متصل شوید. سایر آرگومان ها شناسه پروژه، نام مجموعه داده و شناسه پروژه صورتحساب را مشخص می کنند.

(اشیاء اتصال را می توان تقریباً هر چیزی نامید، اما طبق قرارداد اغلب آنها را con نامگذاری می کنند.)

کد زیر کتابخانه‌های bigrquery و dplyr را بارگیری می‌کند و سپس یک اتصال به جدول زمان‌بندی در مجموعه داده‌های بیسبال ایجاد می‌کند.

bigquery-public-data آرگومان پروژه است زیرا مجموعه داده در آنجا زندگی می کند. my_project_id آرگومان صورت‌حساب است زیرا سهمیه پروژه من برای درخواست‌ها «صورت‌حساب» می‌شود.

library(bigrquery)
library(dplyr)
con <- dbConnect(
  bigrquery::bigquery(),
  project = "bigquery-public-data",
  dataset = "baseball",
  billing = "my_project_id"
)

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

به عنوان مثال، برای فهرست کردن همه جداول موجود در مجموعه داده های بیسبال، این کد را اجرا می کنم:

dbListTables(con)
# You will be asked to authenticate in your browser 

چگونه از جدول BigQuery در R

پرس و جو کنیم

برای پرس و جو از یک جدول BigQuery خاص در R، از تابع dplyr  tbl() برای ایجاد یک شی جدول که به جدول ارجاع می دهد استفاده کنید، مانند این برای جدول زمانبندی با استفاده از اتصال تازه ایجاد شده من به مجموعه داده بیسبال:

skeds <- tbl(con, "schedules")

اگر از دستور پایه R str() برای بررسی ساختار skeds استفاده کنید، یک لیست خواهید دید، نه یک قاب داده:

str(skeds)
List of 2
 $ src:List of 2
  ..$ con  :Formal class 'BigQueryConnection' [package "bigrquery"] with 7 slots
  .. .. ..@ project       : chr "bigquery-public-data"
  .. .. ..@ dataset       : chr "baseball"
  .. .. ..@ billing       : chr "do-more-with-r-242314"
  .. .. ..@ use_legacy_sql: logi FALSE
  .. .. ..@ page_size     : int 10000
  .. .. ..@ quiet         : logi NA
  .. .. ..@ bigint        : chr "integer"
  ..$ disco: NULL
  ..- attr(*, "class")= chr [1:4] "src_BigQueryConnection" "src_dbi" "src_sql" "src"
 $ ops:List of 2
  ..$ x   : 'ident' chr "schedules"
  ..$ vars: chr [1:16] "gameId" "gameNumber" "seasonId" "year" ...
  ..- attr(*, "class")= chr [1:3] "op_base_remote" "op_base" "op"
 - attr(*, "class")= chr [1:5] "tbl_BigQueryConnection" "tbl_dbi" "tbl_sql" "tbl_lazy" ...

خوشبختانه، توابع dplyr مانند glimpse() اغلب با این نوع شی (کلاس tbl_BigQueryConnection) کاملاً یکپارچه کار می کنند.

اجرای glimpse(skeds) بیشتر آنچه را که انتظار دارید برمی‌گرداند – به جز اینکه نمی‌داند چند ردیف در داده‌ها وجود دارد.

glimpse(skeds)
Rows: ??
Columns: 16
Database: BigQueryConnection
$ gameId <chr> "e14b6493-9e7f-404f-840a-8a680cc364bf", "1f32b347-cbcb-4c31-a145-0e…
$ gameNumber <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
$ seasonId <chr> "565de4be-dc80-4849-a7e1-54bc79156cc8", "565de4be-dc80-4849-a7e1-54…
$ year <int> 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2…
$ type <chr> "REG", "REG", "REG", "REG", "REG", "REG", "REG", "REG", "REG", "REG…
$ dayNight <chr> "D", "D", "D", "D", "D", "D", "D", "D", "D", "D", "D", "D", "D", "D…
$ duration <chr> "3:07", "3:09", "2:45", "3:42", "2:44", "3:21", "2:53", "2:56", "3:…
$ duration_minutes <int> 187, 189, 165, 222, 164, 201, 173, 176, 180, 157, 218, 160, 178, 20…
$ homeTeamId <chr> "03556285-bdbb-4576-a06d-42f71f46ddc5", "03556285-bdbb-4576-a06d-42…
$ homeTeamName <chr> "Marlins", "Marlins", "Braves", "Braves", "Phillies", "Diamondbacks…
$ awayTeamId <chr> "55714da8-fcaf-4574-8443-59bfb511a524", "55714da8-fcaf-4574-8443-59…
$ awayTeamName <chr> "Cubs", "Cubs", "Cubs", "Cubs", "Cubs", "Cubs", "Cubs", "Cubs", "Cu…
$ startTime <dttm> 2016-06-26 17:10:00, 2016-06-25 20:10:00, 2016-06-11 20:10:00, 201…
$ attendance <int> 27318, 29457, 43114, 31625, 28650, 33258, 23450, 32358, 46206, 4470…
$ status <chr> "closed", "closed", "closed", "closed", "closed", "closed", "closed…
$ created <dttm> 2016-10-06 06:25:15, 2016-10-06 06:25:15, 2016-10-06 06:25:15, 201…

این به من می‌گوید glimpse() ممکن است کل مجموعه داده‌ها را تجزیه نکند – و به این معنی است که احتمال زیادی وجود دارد که هزینه‌های پرس و جو را انجام ندهد، اما در عوض فراداده را جستجو می‌کند. وقتی رابط وب BigQuery خود را پس از اجرای آن دستور بررسی کردم، در واقع هیچ هزینه درخواستی وجود نداشت.

تجزیه و تحلیل BigQuery + dplyr

شما می توانید دستورات dplyr را روی اشیاء جدول تقریباً به همان روشی که در فریم های داده معمولی انجام می دهید اجرا کنید. اما احتمالاً یک اضافه می‌خواهید: لوله‌گذاری از جریان کار معمول dplyr شما به تابع collect() حاصل می‌شود.

کد زیر از dplyr استفاده می‌کند تا ببیند چه سال‌ها و تیم‌های خانگی در شی جدول skeds هستند و نتایج را در یک تیبل ذخیره می‌کند (نوع خاصی از قاب داده که توسط مجموعه بسته‌های tidyverse استفاده می‌شود).

available_teams <- select(skeds, homeTeamName) %>%
   distinct() %>%
   collect()

Complete
Billed: 10.49 MB
Downloading 31 rows in 1 pages.

توجه به قیمت: من پرس و جوی بالا را با استفاده از بیانیه SQL که به دنبال اطلاعات مشابه بود بررسی کردم:

SELECT DISTINCT `homeTeamName`
FROM `bigquery-public-data.baseball.schedules`

وقتی این کار را انجام دادم، ویرایشگر وب BigQuery نشان داد که فقط ۲۱.۱ کیلوبایت داده پردازش شده است، نه بیشتر از ۱۰ مگابایت. چرا اینقدر بیشتر از من صورتحساب دریافت کردند؟ جستجوها حداقل ۱۰ مگابایت دارند (و به مگابایت بعدی گرد می شوند).

به‌علاوه: اگر می‌خواهید نتایج یک جستار R را به جای یک قاب داده محلی در یک جدول BigQuery موقت ذخیره کنید، می‌توانید compute(name = "my_temp_table") را اضافه کنید. code> به جای collect() به انتهای لوله خود بروید. با این حال، باید در پروژه‌ای کار کنید که در آن مجوز ایجاد جداول را دارید، و پروژه داده‌های عمومی Google قطعاً چنین نیست.

available_teams <- select(skeds, homeTeamName) %>%
 distinct() 

available_teams

select(skeds, homeTeamName) %>%
  distinct() %>%
  show_query()
<SQL>
SELECT DISTINCT `homeTeamName`
FROM `schedules`

SELECT DISTINCT `homeTeamName` from `bigquery-public-data.baseball.schedules`

sql <- "SELECT DISTINCT homeTeamName from bigquery-public-data.baseball.schedules"
library(DBI)
my_results <- dbGetQuery(con, sql)
Complete
Billed: 10.49 MB
Downloading 31 rows in 1 pages

my_results2 <- dbGetQuery(con, sql)
Complete
Billed: 0 B
Downloading 31 rows in 1 pages.