BigQuery'de Bölümlendirme ve Kümeleme

1. Giriş

BigQuery, analizlere yönelik tümüyle yönetilen, petabayt ölçeğinde düşük maliyetli, kurumsal bir veri ambarıdır. BigQuery sunucusuzdur. Kümeler kurup yönetmeniz gerekmez.

BigQuery veri kümesi, bir GCP projesinde bulunur ve bir veya daha fazla tablo içerir. Bu veri kümelerini SQL ile sorgulayabilirsiniz.

Bu codelab'de, BigQuery'deki bölümlendirme ve kümelemeyi anlamak için GCP Console'daki BigQuery web kullanıcı arayüzünü kullanacaksınız. BigQuery'nin tablo bölümlendirme ve kümeleme özelliği, verilerinizi yaygın veri erişim kalıplarına uygun şekilde yapılandırmanıza yardımcı olur. Bölümlendirme ve kümeleme, belirli bir veri aralığı üzerinde sorgulama yaparken BigQuery performansını ve maliyetini tamamen en üst düzeye çıkarmak açısından son derece önemlidir. Bu sayede sorgu başına daha az veri taranır ve budama işlemi, sorgu başlangıç zamanından önce belirlenir.

Not: BigQuery web kullanıcı arayüzünün yanı sıra, BigQuery veri kümelerinde işlem yapmak için bq komut satırı aracını da kullanabilirsiniz. bq komut satırı aracı hakkında daha fazla bilgi için bq Komut Satırı Aracı'na bakın.

BigQuery hakkında daha fazla bilgi için BigQuery belgelerine bakın.

Neler öğreneceksiniz?

• Bölümlendirilmiş ve kümelenmiş tablolar oluşturup sorgulama
• Bölümlendirilmiş ve kümelenmiş tablolarla sorgu performansını karşılaştırma

Gerekenler

Bu laboratuvarı tamamlamak için şunlar gerekir:

• Google Chrome'un en son sürümü
• Google Cloud Platform faturalandırma hesabı

2. Kurulum

BigQuery ile çalışmak için bir GCP projesi oluşturmanız veya mevcut bir projeyi seçmeniz gerekir.

Proje oluşturma

Yeni proje oluşturmak için şu adımları izleyin:

1. Google Hesabınız (Gmail veya Google Apps) yoksa bir hesap oluşturun.
2. Google Cloud Platform konsolunda ( console.cloud.google.com) oturum açın ve yeni bir proje oluşturun.
3. Projeniz yoksa proje oluştur düğmesini tıklayın:

Aksi takdirde, proje seçimi menüsünden yeni bir proje oluşturun:

4. Bir proje adı girin ve Create'i (Oluştur) seçin. Proje kimliğinin tüm Google Cloud projeleri için benzersiz bir ad olduğunu unutmayın.

3. Herkese açık veri kümeleriyle çalışma

BigQuery; BBC News, GitHub depoları, Stack Overflow ve ABD Ulusal Okyanus ve Atmosfer İdaresi (NOAA) veri kümeleri gibi herkese açık veri kümeleriyle çalışmanıza olanak tanır. Bu veri kümelerini BigQuery'ye yüklemeniz gerekmez. Veri kümelerine göz atmak ve bunları BigQuery'de sorgulamak için açmanız yeterlidir. Bu codelab'de Stack Overflow herkese açık veri kümesiyle çalışacaksınız.

Stack Overflow veri kümesine göz at

Stack Overflow veri kümesi yayınlar, etiketler, rozetler, yorumlar, kullanıcılar ve daha fazlasıyla ilgili bilgiler içerir. BigQuery web kullanıcı arayüzünde Stack Overflow veri kümesine göz atmak için aşağıdaki adımları izleyin:

1. Stack Overflow veri kümesini açın. GCP Console'da BigQuery web kullanıcı arayüzü açılır ve Stackoverflow veri kümesiyle ilgili bilgiler görüntülenir.
2. Gezinme panelinde bigquery-public-data'yı seçin. Menü genişletilerek herkese açık veri kümeleri listelenir. Her veri kümesi bir veya daha fazla tablodan oluşur.
3. Aşağı kaydırın ve stackoverflow'ı seçin. Menü genişleyerek Stack Overflow veri kümesindeki tabloları listeler.
4. Rozetler tablosunun şemasını görmek için rozetleri seçin. Tablodaki alanların adlarını not edin.
5. Rozetler tablosunun örnek verilerini görmek için Alan adlarının üstünde Önizle'yi tıklayın.

BigQuery'de kullanılabilen tüm herkese açık veri kümeleri hakkında daha fazla bilgi edinmek için Google BigQuery Herkese Açık Veri Kümeleri başlıklı makaleyi inceleyin.

Stackoverflow veri kümesini sorgulama

Bir veri kümesine göz atmak, üzerinde çalıştığınız verileri anlamak için iyi bir yoldur. Ancak veri kümelerini sorgulamak, BigQuery'nin asıl işlevidir. Bu bölümde BigQuery sorgularını nasıl çalıştıracağınız açıklanmaktadır. Bu aşamada herhangi bir SQL bilmeniz gerekmez. Aşağıdaki sorguları kopyalayıp yapıştırabilirsiniz.

Sorgu çalıştırmak için aşağıdaki adımları tamamlayın:

1. GCP Console'un sağ üst tarafına yakın yerde bulunan Yeni sorgu oluştur'u seçin.
2. Query editor (Sorgu düzenleyici) metin alanında aşağıdaki SQL sorgusunu kopyalayıp yapıştırın. BigQuery sorguyu doğrular ve web kullanıcı arayüzü, söz diziminin geçerli olduğunu belirtmek için metin alanının altında yeşil bir onay işareti görüntüler.

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. Çalıştır'ı seçin. Bu sorgu, her yıl yayınlanan Stack Overflow yayın veya soruların sayısını döndürür.

Not: Bu sorgu; proje adı, veri kümesi adı ve tablo adı da dahil olmak üzere posts_questions tablosu için tam tablo adını kullanır: bigquery-public-data.stackoverflow.posts_questions.

4. Yeni Tablo Oluşturma

Önceki bölümde, BigQuery'nin size sunduğu herkese açık veri kümelerini sorguladınız. Bu bölümde, mevcut bir tablodan BigQuery'de yeni bir tablo oluşturacaksınız. Stack Overflow herkese açık veri kümesi posts_questions tablosundan örneklenmiş verilerle yeni bir tablo oluşturacak ve ardından tabloyu sorgulayacaksınız.

Yeni Veri Kümesi Oluştur

Tablo verileri oluşturup BigQuery'ye yüklemek için öncelikle aşağıdaki adımları uygulayarak verilerin depolanacağı bir BigQuery veri kümesi oluşturun:

1. GCP Console gezinme panelinde, kurulumun bir parçası olarak oluşturulan proje adını seçin.
2. Sağ taraftaki ayrıntılar panelinde Veri kümesi oluştur'u seçin.

3. Veri kümesi oluştur iletişim kutusuna Veri Kümesi Kimliği için stackoverflow yazın. Diğer varsayılan ayarları değiştirmeden Tamam'ı tıklayın.

2018 StackOverflow Gönderileriyle Yeni Bir Tablo Oluştur

Artık bir BigQuery veri kümesi oluşturduğunuza göre BigQuery'de yeni bir tablo oluşturabilirsiniz. Mevcut bir tablodan veri içeren bir tablo oluşturmak için aşağıdaki adımları uygulayarak 2018 Stack Overflow yayınları veri kümesini sorgulayacak ve sonuçları yeni bir tabloya yazacaksınız:

1. GCP Console'un sağ üst tarafına yakın yerde bulunan Yeni sorgu oluştur'u seçin.

2. Sorgu düzenleyici metin alanında, DDL ifadesi olan yeni bir tablo oluşturmak için aşağıdaki SQL sorgusunu kopyalayıp yapıştırın.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Çalıştır'ı seçin. Sorgu, projenizdeki stackoverflow veri kümesinde bigquery-public-data.stackoverflow.posts_questions BigQuery Stack Overflow veri kümesinde çalıştırılan bir sorgudan elde edilen verilerle yeni bir tablo (questions_2018) oluşturur.

Not: Bu adımla yeni bir tablo oluşturmak yaklaşık 2 dakika sürer. Sonraki adıma geçmeden önce lütfen bekleyin.

2018 Stack Overflow Gönderileriyle Yeni Tabloyu Sorgulama

Artık bir BigQuery tablosu oluşturduğunuza göre şimdi bir sorgu çalıştırıp soru ve başlık içeren Stack Overflow yayınlarını ve yanıt sayısı, yorum, görüntüleme ve favoriler gibi birkaç istatistik daha döndürelim. Aşağıdaki adımları tamamlayın:

1. GCP Console'un sağ üst tarafına yakın yerde bulunan Yeni sorgu oluştur'u seçin.
2. Query editor (Sorgu düzenleyici) metin alanında aşağıdaki SQL sorgusunu kopyalayıp yapıştırın.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Çalıştır'ı seçin. Bu sorgu, 2018 Ocak ayında oluşturulan ve soru ve diğer birkaç istatistik ile birlikte android olarak etiketlenmiş Stack Overflow sorularını döndürür.
4. Varsayılan olarak BigQuery, sorgu sonuçlarını önbelleğe alır. Aynı sorguyu çalıştırdığınızda BigQuery, önbellekten sonuçlar döndürdüğü için sonuç döndürmenin çok daha az zaman aldığını görürsünüz.
5. Aynı sorguyu bu kez BigQuery önbelleğe alma özelliğini devre dışı bırakarak tekrar çalıştırın. Sonraki bölümlerde çalıştırılacak bölümlendirilmiş ve kümelenmiş tablolarla performans karşılaştırması yapmak için laboratuvarın geri kalanında önbelleği devre dışı bırakacağız. Sorgu düzenleyicide, Diğer'i tıklayın ve Sorgu ayarları'nı seçin.
6. Önbellek tercihi altında Önbelleğe alınan sonuçları kullan seçeneğinin işaretini kaldırın.
7. Sorgu sonuçlarında, sorgunun tamamlanması için geçen süreyi ve sonuçları almak için işlenen veri hacmini göreceksiniz.

5. Bölümlendirilmiş Tablo Oluşturma ve Sorgulama

Önceki bölümde, Stack Overflow herkese açık veri kümesini kullanarak posts_questions tablosundaki verilerle BigQuery'de yeni bir tablo oluşturdunuz. Bu veri kümesini önbelleğe alma devre dışı olacak şekilde sorguladık ve sorgu performansını gözlemledik. Bu bölümde, aynı Stack Overflow ortak veri kümesinin posts_questions tablosundan yeni bir bölümlendirilmiş tablo oluşturacak ve sorgu performansını gözlemleyeceksiniz.

Bölümlendirilmiş tablo, bölümlere ayrılmış ve verilerinizi yönetmeyi ve sorgulamayı kolaylaştıran segmentlere bölünmüş özel bir tablodur. Genellikle büyük tabloları veri kullanımı zamanı veya TIMESTAMP/DATE sütununu ya da bir INTEGER sütununu kullanarak çok sayıda küçük bölüme ayırabilirsiniz. DATE bölümlendirilmiş bir tablo oluşturacağız.

Bölümlendirilmiş tablolar hakkında daha fazla bilgiyi burada bulabilirsiniz.

2018 StackOverflow Yayınlarıyla Yeni Bir Bölümlendirilmiş Tablo Oluşturma

Mevcut bir tablodan veya sorgudan gelen verilerle bölümlendirilmiş bir tablo oluşturmak için 2018 Stackoverflow posts veri kümesini sorgulayıp sonuçları yeni bir tabloya yazacak, aşağıdaki adımları tamamlayın:

1. GCP Console'un sağ üst tarafına yakın yerde bulunan Yeni sorgu oluştur'u seçin.

2. Sorgu düzenleyici metin alanında, DDL ifadesi olan yeni bir tablo oluşturmak için aşağıdaki SQL sorgusunu kopyalayıp yapıştırın.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Çalıştır'ı seçin. Sorgu, projenizdeki stackoverflow veri kümesinde bigquery-public-data.stackoverflow.posts_questions BigQuery Stack Overflow veri kümesinde çalıştırılmasından kaynaklanan verilerle yeni bir tablo (questions_2018_partitioned) oluşturur

Not: Bu adımla yeni bir tablo oluşturmak yaklaşık 2 dakika sürer. Sonraki adıma geçmeden önce lütfen bekleyin.

2018 Stack Overflow Gönderileriyle Bölümlendirilmiş Tabloyu Sorgulama

BigQuery ile bölümlendirilmiş tablo oluşturduğunuza göre şimdi aynı sorguyu (bu kez bölümlendirilmiş tabloda çalıştıralım) soru ve başlıkların yanı sıra yanıt sayısı, yorum, görüntüleme ve favoriler gibi birkaç istatistik daha içeren Stack Overflow yayınlarını döndürelim. Aşağıdaki adımları tamamlayın:

1. GCP Console'un sağ üst tarafına yakın yerde bulunan Yeni sorgu oluştur'u seçin.
2. Query editor (Sorgu düzenleyici) metin alanında aşağıdaki SQL sorgusunu kopyalayıp yapıştırın.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. BigQuery önbelleği devre dışıyken Çalıştır'ı seçin (BigQuery önbelleğini devre dışı bırakmayla ilgili önceki bölüme bakın). Bu sorgu, 2018 Ocak ayında oluşturulan ve soru ve diğer birkaç istatistik ile birlikte android olarak etiketlenmiş Stack Overflow sorularını döndürür.
4. Sorgu sonuçlarında, sorgunun tamamlanması için geçen süreyi ve sonuçları almak için işlenen veri hacmini göreceksiniz.

BigQuery, bölümleri ayıkladığından, yani yalnızca daha az veri işleyen ve daha hızlı çalışan gerekli bölümleri taradığından, bölümlendirilmiş tablo içeren sorgunun performansının bölümlendirilmemiş tabloya göre daha iyi olduğunu görebilirsiniz. Böylece sorgu maliyetlerini ve sorgu performansı optimize edilir.

6. Kümelenmiş Tablo Oluşturma ve Sorgulama

Önceki bölümde, Stack Overflow herkese açık veri kümesindeki posts_questions tablosundan gelen verilerle BigQuery'de bölümlendirilmiş bir tablo oluşturdunuz. Bu tabloyu, önbelleğe alma devre dışı olacak şekilde sorguladık ve hem bölümlendirilmemiş hem de bölümlendirilmiş tablolarda sorgu performansını gözlemledik. Bu bölümde, aynı Stack Overflow ortak veri kümesinin posts_questions tablosundan yeni bir kümelenmiş tablo oluşturacak ve sorgu performansını gözlemleyeceksiniz.

Bir tablo BigQuery'de kümelendiğinde, tablo verileri tablonun şemasındaki bir veya daha fazla sütunun içeriğine göre otomatik olarak düzenlenir. Belirttiğiniz sütunlar ilgili verilerin bir arada kullanılması için kullanılır. Veriler kümelenmiş bir tabloya yazıldığında, BigQuery kümeleme sütunlarındaki değerleri kullanarak verileri sıralar. Bu değerler, verileri BigQuery depolama alanında birden fazla blok halinde düzenlemek için kullanılır. Kümelenmiş sütunların sırası, verilerin sıralama düzenini belirler. Bir tabloya veya belirli bir bölüme yeni veriler eklendiğinde BigQuery, tablonun veya bölümün sıralama özelliğini geri yüklemek için arka planda otomatik olarak yeniden kümeleme yapar.

Kümelenmiş tablolarla çalışma hakkında daha fazla bilgiye buradan ulaşabilirsiniz.

2018 Stack Overflow Gönderileriyle Yeni Bir Kümelenmiş Tablo Oluşturma

Bu bölümde, creation_date üzerinde bölümlendirilmiş ve sorgu erişim kalıbına göre tags sütununda kümelenmiş yeni bir tablo oluşturacaksınız. Mevcut bir tablodan veya sorgudan gelen verilerle kümelenmiş bir tablo oluşturmak için aşağıdaki adımları uygulayarak 2018 Stack Overflow yayınları tablosunu sorgulayıp sonuçları yeni bir tabloya yazacaksınız:

1. GCP Console'un sağ üst tarafına yakın yerde bulunan Yeni sorgu oluştur'u seçin.

2. Sorgu düzenleyici metin alanında, DDL ifadesi olan yeni bir tablo oluşturmak için aşağıdaki SQL sorgusunu kopyalayıp yapıştırın.

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Çalıştır'ı seçin. Sorgu, projenizdeki stackoverflow veri kümesinde, BigQuery Stack Overflow tablosundabigquery-public-data.stackoverflow.posts_questions bir sorgu çalıştırmasından elde edilen verilerle yeni bir tablo questions_2018_clustered oluşturur. Yeni tablo, generate_date tarihinde bölümlendirilir ve etiketler sütununda kümelenir.

Not: Bu adımla yeni bir tablo oluşturmak yaklaşık 2 dakika sürer. Sonraki adıma geçmeden önce lütfen bekleyin.

2018 Stack Overflow Gönderileriyle Kümelenmiş Tabloyu sorgulama

Kümelenmiş bir BigQuery tablosu oluşturduğunuza göre, bu sefer aynı sorguyu tekrar bölümlendirilmiş ve kümelenmiş tabloda çalıştıralım. Stack Overflow yayınlarını soru ve başlıkların yanı sıra yanıt sayısı, yorum, görüntüleme ve sık kullanılanlar gibi birkaç başka istatistik daha döndürmek için bu kez aynı sorguyu tekrar çalıştıralım. Aşağıdaki adımları tamamlayın:

1. GCP Console'un sağ üst tarafına yakın yerde bulunan Yeni sorgu oluştur'u seçin.
2. Query editor (Sorgu düzenleyici) metin alanında aşağıdaki SQL sorgusunu kopyalayıp yapıştırın.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. BigQuery önbelleği devre dışıyken Çalıştır'ı seçin (BigQuery önbelleğini devre dışı bırakmayla ilgili önceki bölüme bakın). Bu sorgu, 2018 Ocak ayında oluşturulan ve soru ve diğer birkaç istatistik ile birlikte android olarak etiketlenmiş Stack Overflow sorularını döndürür.
4. Sorgu sonuçlarında, sorgunun tamamlanması için geçen süreyi ve sonuçları almak için işlenen veri hacmini göreceksiniz.

Bölümlendirilmiş ve kümelenmiş bir tablo kullanıldığında sorgu, bölümlendirilmiş tablolara veya bölümlendirilmemiş tablolara göre daha az veri taramıştır. Verilerin bölümlendirme ve kümeleme ile düzenlenme şekli, slot çalışanları tarafından taranan veri miktarını en aza indirir. Böylece sorgu performansı iyileşip maliyetleri optimize eder.

7. Temizleme

Stackoverflow veri kümenizle çalışmaya devam etmeyi düşünmüyorsanız veri kümenizi silmeniz ve bu codelab için oluşturduğunuz projeyi silmeniz gerekir.

BigQuery veri kümesini silme

BigQuery veri kümesini silmek için aşağıdaki adımları uygulayın:

1. BigQuery'de sol taraftaki gezinme panelinden stackoverflow veri kümesini seçin .
2. Ayrıntılar panelinde Veri kümesini sil'i seçin.
3. Veri kümesini sil iletişim kutusuna stackoverflow yazın ve veri kümesini silmek istediğinizi onaylamak için Sil'i seçin.

Projeyi silme

Bu codelab için oluşturduğunuz GCP projesini silmek için aşağıdaki adımları uygulayın:

1. GCP gezinme menüsünde IAM ve Yönetici.
2. Gezinme panelinde Ayarlar'ı seçin.
3. Ayrıntılar panelinde, geçerli projenizin bu codelab için oluşturduğunuz proje olduğunu onaylayın ve Kapat'ı seçin.
4. Projeyi kapat iletişim kutusunda, projenizin proje kimliğini (proje adını değil) girin ve onaylamak için Kapat'ı seçin.

Tebrikler! Artık bu kurstan

• Mevcut tablolardan yeni bir tablo oluşturmak için BigQuery web kullanıcı arayüzünü kullanma
• Bölümlendirilmiş ve kümelenmiş tablolar oluşturup sorgulama
• Bölümlendirme ve kümeleme, sorgu performansını ve maliyetlerini nasıl optimize eder?

Veri kümeleriyle çalışmak için kümeler oluşturmanız veya kümeleri yönetmeniz gerekmediğini unutmayın.