arrow_back

Tworzenie i optymalizowanie hurtowni danych przy użyciu BigQuery: Challenge Lab

Tworzenie i optymalizowanie hurtowni danych przy użyciu BigQuery: Challenge Lab

1 godz. 30 godz. Punkty: 5

GSP340

Moduły Google Cloud do samodzielnego ukończenia

Podsumowanie

Przed Tobą seria zadań, które należy wykonać w określonym czasie. Zamiast instrukcji krok po kroku otrzymasz scenariusz i zestaw zadań. Musisz rozwiązać je samodzielnie. Automatyczny system oceniania (widoczny na tej stronie) poinformuje Cię, czy zadanie zostało wykonane poprawnie.

Aby zdobyć 100% punktów, musisz ukończyć wszystkie zadania w określonym czasie.

W Challenge Lab (module-wyzwaniu) nie będziemy uczyć Cię pojęć związanych z Google Cloud. Aby rozwiązać zadania, użyj umiejętności nabytych w poszczególnych modułach kursu, którego częścią jest niniejsze wyzwanie. Podczas wykonywania zadań rozwiniesz nabyte umiejętności i będziesz mieć okazję zmienić wartości domyślne – nie będziemy jednak wprowadzać nowych zagadnień.

Zagadnienia, z których będzie sprawdzana wiedza

  • Używanie BigQuery w celu uzyskania dostępu do publicznych zbiorów danych dotyczących COVID-19 i innych demograficznych zbiorów danych.
  • Tworzenie nowego zbioru danych BigQuery, w którym będą przechowywane tabele.
  • Dodawanie do zbioru danych nowej tabeli partycjonowanej według daty.
  • Dodawanie do tabeli nowych kolumn z odpowiednimi typami danych.
  • Uruchamianie kilku poleceń JOIN w celu wypełnienia nowych kolumn danymi pobranymi z innych tabel.

Konfiguracja

Zanim klikniesz przycisk Start Lab (Rozpocznij moduł)

Zapoznaj się z tymi instrukcjami. Moduły mają limit czasowy i nie można ich zatrzymać. Po kliknięciu Start Lab (Rozpocznij moduł) na liczniku wyświetli się informacja o tym, na jak długo udostępniamy Ci zasoby Google Cloud.

W tym praktycznym module Qwiklabs możesz spróbować swoich sił w wykonywaniu opisywanych działań w prawdziwym środowisku chmury, a nie w jego symulacji lub wersji demonstracyjnej. Otrzymasz nowe, tymczasowe dane logowania, dzięki którym zalogujesz się i uzyskasz dostęp do Google Cloud na czas trwania modułu.

Wymagania

Do ukończenia modułu potrzebne będą:

  • dostęp do standardowej przeglądarki internetowej (zalecamy korzystanie z przeglądarki Chrome),
  • czas wymagany do ukończenia modułu.

Uwaga: jeśli masz już osobiste konto lub projekt w Google Cloud, nie używaj go w tym module.

Uwaga: jeśli używasz Pixelbooka, uruchom ten moduł w oknie incognito.

Scenariusz

Należysz do międzynarodowej organizacji zajmującej się zdrowiem publicznym, której zadaniem jest stworzenie modelu systemów uczących się do przewidywania dziennej liczby przypadków w danym kraju podczas pandemii COVID-19. Jako młodszy członek zespołu ds. badania danych masz za zadanie wykorzystać umiejętności gromadzenia danych, aby utworzyć tabelę zawierającą funkcje modelu systemów uczących się.

Zakładamy, że dysponujesz umiejętnościami oraz wiedzą potrzebnymi do wykonania tych zadań, dlatego nie będziesz mieć do dyspozycji przewodników krok po kroku.

Twoje wyzwanie

Pierwszym krokiem będzie utworzenie nowego zbioru danych i tabeli. Punktem początkowym modelu systemów uczących się będzie tabela oxford_policy_trackerpublicznym zbiorze danych COVID 19 Government Response (Odpowiedź rządów na COVID-19). Zbiór ten zawiera szczegóły różnych działań podejmowanych przez rządy w celu ograniczenia rozprzestrzeniania się COVID-19 w ich jurysdykcjach. Wiedząc, że modele będą opierać się na zakresach czasowych, musisz utworzyć nowy zbiór danych, a w nim – partycjonowaną według daty wersję tabeli oxford_policy_tracker z wygaśnięciem ustawionym na 90 dni. Musisz również wykluczyć Wielką Brytanię (alpha_3_code='GBR') i Stany Zjednoczone (alpha_3_code='USA), ponieważ zostaną one poddane dogłębniejszej analizie (według krajów i stanów).

Jeśli chodzi o inne wymagane informacje, polecono Ci dodać kolumny population (populacja), country_area (obszar_geograficzny_kraju) oraz zawierającą rekordy kolumnę mobility (mobilność) o 6 polach do wprowadzania danych. W polach tych mają znaleźć się uśrednione dane o mobilności zawarte w ostatnich 6 kolumnach tabeli mobility_report dostępnej w publicznym zbiorze danych Google COVID 19 Mobility (Mobilność a COVID-19).

Współpracownik wykonujący zadanie pomocnicze udostępnił Ci kod SQL, którego użyto do aktualizowania danych w tabeli dotyczącej nowych przypadków zakażenia w poszczególnych dniach – jest to podobna tabela, partycjonowana według daty za pomocą polecenia JOIN przy użyciu tabeli covid_19_geographic_distribution_worldwidepublicznego zbioru danych European Center for Disease Control COVID 19 (Europejskie Centrum ds. Kontroli Chorób COVID-19). Ta użyteczna tabela zawiera szereg danych, w tym najnowsze dane o populacji poszczególnych państw. Użyjesz jej w swojej tabeli do wypełnienia kolumny dotyczącej populacji.

UPDATE
    `covid.oxford` t0
SET
    ecdc_new_cases = t2.daily_confirmed_cases
FROM 
    covid.oxford t1
    LEFT JOIN 
    (SELECT DISTINCT country_territory_code, daily_confirmed_cases FROM `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`) AS t2
     ON t1.alpha_3_code = t2.country_territory_code
WHERE CONCAT(t0.country_name, t0.date) = CONCAT(t1.country_name, t1.date);

Powyższy szablon aktualizuje kolumnę dotyczącą nowych przypadków zakażenia w poszczególnych dniach, musisz go więc zmodyfikować, aby móc z jego pomocą wprowadzić do tabeli dane o populacji znajdujące się w publicznym zbiorze danych European Center for Disease Control COVID 19 – powstałe zapytanie będzie jednak bardzo podobne.

Oprócz danych dotyczących populacji musisz dodać do tabeli dane związane z obszarem geograficznym poszczególnych państw. Dane te znajdziesz w tabeli country_names_areapublicznego zbioru danych Census Bureau International (Międzynarodowe Biuro Ewidencji Ludności).

Ostatnie zadanie dotyczące pozyskiwania danych wymaga od Ciebie wyodrębnienia średnich wartości z 6 pól, które tworzą rekord danych mobility z tabeli mobility_report dostępnej w publicznym zbiorze danych Google COVID 19 Mobility. Pamiętaj, że w przypadku niektórych państw dane o mobilności mogą być rozbite na podregiony, więc dla danego państwa może istnieć więcej niż 1 dzienny rekord. Ponieważ jednak model systemów uczących się, nad którym pracujesz, będzie działać tylko na poziomie państwa, musisz wyodrębnić dzienną średnią z tych pól dotyczących mobilności, w których znajdują się zagregowane dzienne rekordy dla każdego państwa, i wpisać te dane jako pojedynczą średnią dla każdego rekordu mobilności.

Aby mieć pewność, że Twoja praca będzie przebiegać w zgodzie z pracą reszty zespołu, określono wyszczególnione niżej nazwy kolumn i typy danych. Musisz użyć ich, aktualizując schemat swojej tabeli.

New Column Name          SQL Data Type 
population               INTEGER
country_area             FLOAT
mobility                 RECORD
mobility.avg_retail      FLOAT
mobility.avg_grocery     FLOAT
mobility.avg_parks       FLOAT
mobility.avg_transit     FLOAT
mobility.avg_workplace   FLOAT
mobility.avg_residential FLOAT

Współpracownik udostępnił Ci też fragment kodu SQL, który obecnie wykorzystywany jest do analizowania trendów w danych Google dotyczących dziennych wzorców mobilności. Powinno być możliwe użycie go jako części zapytania, które doda do rekordu mobilności w Twojej tabeli dzienne dane krajowe.

 SELECT country_region, date, 
      AVG(retail_and_recreation_percent_change_from_baseline) as avg_retail,
      AVG(grocery_and_pharmacy_percent_change_from_baseline)  as avg_grocery,
      AVG(parks_percent_change_from_baseline) as avg_parks,
      AVG(transit_stations_percent_change_from_baseline) as avg_transit,
      AVG( workplaces_percent_change_from_baseline ) as avg_workplace,
      AVG( residential_percent_change_from_baseline)  as avg_residential
      FROM `bigquery-public-data.covid19_google_mobility.mobility_report`
      GROUP BY country_region, date

Wykonując polecenia JOIN pomiędzy różnymi tabelami, musisz użyć kolumny alpha_3_code, czyli trzyliterowego kodu kraju, lub kolumny country_name, która zawiera pełną, oficjalną nazwę kraju, ze swojej tabeli. Odpowiednie nazwy kolumn w dodatkowych tabelach danych mogą się różnić.

Twoje ostatnie zadanie polega na zidentyfikowaniu problemów, które dotyczą danych (i które będzie musiał rozwiązać inny członek Twojego zespołu). Gdy kolumny będą już wypełnione, uruchom zapytanie, które zwróci połączoną, posortowaną według nazwy listę ODRĘBNYCH krajów – zarówno tych, dla których nie ma danych o populacji, jak i tych, dla których nie ma informacji o obszarze geograficznym. Jeśli dla któregoś kraju nie ma danych ani o populacji, ani o obszarze geograficznym, powinien on pojawić się 2 razy. Dzięki temu zyskasz wgląd w problematyczne przypadki.

Zadanie 1. Utwórz tabelę partycjonowaną według daty

Utwórz nowy zbiór danych, a w nim tabelę partycjonowaną według daty – z wygaśnięciem ustawionym na 90 dni. Tabela powinna początkowo korzystać ze schematu określonego dla tabeli oxford_policy_trackerpublicznym zbiorze danych COVID 19 Government Response.

Musisz też uzupełnić tabelę danymi z tabeli źródłowej dotyczącymi wszystkich państw oprócz Wielkiej Brytanii (GBR) i Stanów Zjednoczonych (USA).

Sprawdź, czy na podstawie tabeli oxford_policy_tracker została utworzona tabela partycjonowana według daty. Jeśli zielona ikona potwierdzenia się nie pojawi, w prawym górnym rogu kliknij wysuwane menu Wynik, a następnie przy odpowiednim kroku kliknij Sprawdź postępy. Otworzy się wyskakujące okienko z kolejnymi instrukcjami.

Zadanie 2. Dodaj nowe kolumny do tabeli

Zaktualizuj tabelę, by dodać w niej nowe kolumny z odpowiednimi typami danych w celu zapewnienia zgodności z udostępnioną Ci specyfikacją.

New Column Name          SQL Data Type 
population               INTEGER
country_area             FLOAT
mobility                 RECORD
mobility.avg_retail      FLOAT
mobility.avg_grocery     FLOAT
mobility.avg_parks       FLOAT
mobility.avg_transit     FLOAT
mobility.avg_workplace   FLOAT
mobility.avg_residential FLOAT
Sprawdź, czy wszystkie nowe kolumny zostały poprawnie dodane. Jeśli zielona ikona potwierdzenia się nie pojawi, w prawym górnym rogu kliknij wysuwane menu Wynik, a następnie przy odpowiednim kroku kliknij Sprawdź postępy. Otworzy się wyskakujące okienko z kolejnymi instrukcjami.

Zadanie 3. Dodaj dane dotyczące populacji poszczególnych krajów w kolumnie population

Uzupełnij kolumnę population dotyczącą populacji poszczególnych krajów danymi z tabeli covid_19_geographic_distribution_worldwide dostępnej w publicznym zbiorze danych European Center for Disease Control COVID 19.

Sprawdź, czy kolumna population została poprawnie wypełniona. Jeśli zielona ikona potwierdzenia się nie pojawi, w prawym górnym rogu kliknij wysuwane menu Wynik, a następnie przy odpowiednim kroku kliknij Sprawdź postępy. Otworzy się wyskakujące okienko z kolejnymi instrukcjami.

Zadanie 4. Dodaj dane dotyczące obszaru geograficznego poszczególnych krajów w kolumnie country_area

Uzupełnij kolumnę country_area dotyczącą obszaru geograficznego poszczególnych krajów danymi z tabeli country_names_area dostępnej w publicznym zbiorze danych Census Bureau International.

Sprawdź, czy kolumna country_area została poprawnie wypełniona. Jeśli zielona ikona potwierdzenia się nie pojawi, w prawym górnym rogu kliknij wysuwane menu Wynik, a następnie przy odpowiednim kroku kliknij Sprawdź postępy. Otworzy się wyskakujące okienko z kolejnymi instrukcjami.

Zadanie 5. Wypełnij dane rekordu mobilności

W tabeli wypełnij rekord mobilności danymi z publicznego zbioru danych Google COVID 19 Mobility.

Sprawdź, czy rekord mobilności został poprawnie wypełniony. Jeśli zielona ikona potwierdzenia się nie pojawi, w prawym górnym rogu kliknij wysuwane menu Wynik, a następnie przy odpowiednim kroku kliknij Sprawdź postępy. Otworzy się wyskakujące okienko z kolejnymi instrukcjami.

Zadanie 6. Wyślij zapytanie o brakujące dane w kolumnach population i country_area

Uruchom zapytanie, by znaleźć brakujące kraje w danych dotyczących populacji i obszaru geograficznego kraju. Zapytanie powinno zwrócić listę krajów, dla których nie ma danych o populacji lub obszarze geograficznym, uporządkowaną według nazwy. Jeśli dla któregoś kraju nie ma danych ani o populacji, ani o obszarze geograficznym, musi on pojawić się 2 razy. Sprawdź, czy zapytanie o brakujące dane zostało poprawnie uruchomione.

Jeśli zielona ikona potwierdzenia się nie pojawi, w prawym górnym rogu kliknij wysuwane menu Wynik, a następnie przy odpowiednim kroku kliknij Sprawdź postępy. Otworzy się wyskakujące okienko z kolejnymi instrukcjami.

Wypełnij sekcję Śledzenie aktywności, gdy scenariusz zostanie zaakceptowany.

Porady i wskazówki

  • Wskazówka 1. Pamiętaj, aby wykluczyć z tabeli początkowej dane dotyczące Wielkiej Brytanii (GBR) i Stanów Zjednoczonych (USA).

  • Wskazówka 2. Gdy aktualizujesz schemat do tabeli BigQuery, możesz użyć konsoli, by dodać kolumny i elementy rekordu. Możesz też skorzystać z narzędzia wiersza poleceń bq, by zaktualizować schemat przy użyciu pliku JSON zawierającego wszystkie definicje pola zgodnie z dokumentacją BigQuery Standard SQL.

  • Wskazówka 3. Tabela covid19_ecdcpublicznym zbiorze danych European Center for Disease Control COVID 19 zawiera kolumnę population, której możesz użyć do uzupełnienia kolumny population w Twoim zbiorze danych.

  • Wskazówka 4. Tabela country_names_areapublicznego zbioru danych Census Bureau International nie zawiera kolumny z trzyliterowym kodem kraju, ale możesz dołączyć taką kolumnę do swojej tabeli przy użyciu pełnotekstowej kolumny country_name obecnej w obu tabelach.

  • Wskazówka 5. Podczas aktualizowania rekordu mobilności pamiętaj, że musisz wybrać (i uśrednić) liczbę rekordów dla każdego kraju oraz kombinację daty, by otrzymać pojedynczą średnią z każdej kolumny podrzędnej w rekordzie mobilności. Aby uzyskać unikalne mapowanie między uśrednionymi wynikami źródłowej tabeli mobilności a rekordami w Twojej tabeli, która zawiera pojedyncze pozycje dla każdej kombinacji kraju i daty, musisz dołączyć otrzymane dane do swojej tabeli roboczej przy użyciu tej samej kombinacji nazwy kraju i daty, której użyto do pogrupowania źródłowych rekordów mobilności.

  • Wskazówka 6. Opcja UNION, po której następuje słowo kluczowe ALL, łączy rezultaty obu zapytań. Każde zapytanie tworzy listę odrębnych wyników bez łączenia zduplikowanych rezultatów, które powstały w wyniku złączenia w 1 wiersz.

Gratulacje!

Szkolenia i certyfikaty Google Cloud

…pomogą Ci wykorzystać wszystkie możliwości technologii Google Cloud. Nasze zajęcia obejmują umiejętności techniczne oraz sprawdzone metody, które ułatwią Ci szybką naukę i umożliwią jej kontynuację. Oferujemy szkolenia na poziomach od podstawowego po zaawansowany prowadzone w trybach wirtualnym, na żądanie i na żywo, dzięki czemu możesz dopasować program szkoleń do swojego napiętego harmonogramu. Certyfikaty umożliwią udokumentowanie i potwierdzenie umiejętności i doświadczenia uzyskanego w zakresie technologii Google Cloud.

Ostatnia aktualizacja instrukcji: 2 października 2020 r.
Ostatni test modułu: 2 października 2020 r.

Copyright 2020 Google LLC. Wszelkie prawa zastrzeżone. Google i logo Google są znakami towarowymi Google LLC. Wszelkie inne nazwy firm i produktów mogą być znakami towarowymi odpowiednich podmiotów, z którymi są powiązane.