1. Wprowadzenie
BigQuery to ekonomiczna, w pełni zarządzana analityczna baza danych Google typu NoOps. Dzięki BigQuery możesz wykonywać zapytania obejmujące terabajty danych bez pomocy administratora bazy danych czy infrastruktury. BigQuery korzysta ze znanego języka SQL i modelu naliczania opłat tylko za to, czego używasz. Pracując w BigQuery, możesz skoncentrować się na analizowaniu danych i wyciąganiu z nich znaczących wniosków. W ramach tego ćwiczenia w programie użyjesz narzędzia wiersza poleceń bq, aby wczytać lokalny plik CSV do nowej tabeli BigQuery.
Czego się nauczysz
- Jak używać narzędzia wiersza poleceń bq w BigQuery
- Jak wczytać lokalne pliki danych do tabeli BigQuery
Czego potrzebujesz
- Projekt Google Cloud
- przeglądarki, np. Google Chrome;
2. Konfiguracja
Włączanie BigQuery
Jeśli nie masz jeszcze konta Google, musisz je utworzyć.
- Zaloguj się w konsoli Google Cloud i otwórz BigQuery. Możesz też otworzyć interfejs internetowy BigQuery bezpośrednio, wpisując w przeglądarce ten adres URL.
https://console.cloud.google.com/bigquery
- Zaakceptuj Warunki korzystania z usługi.
- Aby korzystać z BigQuery, musisz utworzyć projekt. Utwórz nowy projekt, postępując zgodnie z instrukcjami.
Wybierz nazwę projektu i zanotuj jego identyfikator.
Identyfikator projektu to unikalna nazwa we wszystkich projektach Google Cloud. W dalszej części tego ćwiczenia w Codelabs będzie ona określana jako PROJECT_ID
.
W tym ćwiczeniu w programowaniu wykorzystywane są zasoby BigQuery z limitami piaskownicy BigQuery. Konto rozliczeniowe nie jest wymagane. Jeśli zechcesz później usunąć limity piaskownicy, możesz dodać konto rozliczeniowe, rejestrując się w bezpłatnym okresie próbnym Google Cloud.
Cloud Shell
Użyjesz Cloud Shell – środowiska wiersza poleceń działającego w Google Cloud.
Aktywowanie Cloud Shell
- W konsoli Cloud kliknij Aktywuj Cloud Shell .
Jeśli dopiero zaczynasz korzystać z Cloud Shell, wyświetli się ekran pośredni (w części strony widocznej po przewinięciu) z opisem tej funkcji. W takim przypadku kliknij Dalej (nie zobaczysz go więcej). Tak wygląda ten jednorazowy ekran:
Uzyskanie dostępu do Cloud Shell i połączenie się z nim powinno zająć tylko kilka chwil.
Ta maszyna wirtualna ma wszystkie potrzebne narzędzia dla programistów. Zawiera stały katalog domowy o pojemności 5 GB i działa w Google Cloud, co znacznie zwiększa wydajność sieci i uwierzytelnianie. Większość czynności z tego ćwiczenia z programowania można wykonać w przeglądarce lub na Chromebooku.
Po nawiązaniu połączenia z Cloud Shell powinno pojawić się informacja, że użytkownik jest już uwierzytelniony i że projekt jest już ustawiony na identyfikator Twojego projektu.
- Uruchom to polecenie w Cloud Shell, aby potwierdzić, że jesteś uwierzytelniony:
gcloud auth list
Dane wyjściowe polecenia
Credentialed Accounts ACTIVE ACCOUNT * <my_account>@<my_domain.com> To set the active account, run: $ gcloud config set account `ACCOUNT`
- Uruchom to polecenie w Cloud Shell, aby sprawdzić, czy polecenie gcloud zna Twój projekt:
gcloud config list project
Dane wyjściowe polecenia
[core] project = <PROJECT_ID>
Jeśli tak nie jest, możesz go ustawić za pomocą tego polecenia:
gcloud config set project <PROJECT_ID>
Dane wyjściowe polecenia
Updated property [core/project].
3. Tworzenie zbioru danych
utworzyć zbiór danych zawierający tabele,
Co to jest zbiór danych?
Zbiór danych BigQuery to zbiór tabel. Wszystkie tabele w zbiorze danych są przechowywane w tej samej lokalizacji danych. Możesz też dołączyć niestandardowe kontrole dostępu, aby ograniczyć dostęp do zbioru danych i jego tabel.
Tworzenie zbioru danych
W Cloud Shell użyj polecenia bq mk, aby utworzyć zbiór danych o nazwie „bq_load_codelab
”.
bq mk bq_load_codelab
Wyświetlanie właściwości zbioru danych
Sprawdź, czy zbiór danych został utworzony, wyświetlając jego właściwości za pomocą polecenia bq show.
bq show bq_load_codelab
Zostaną wyświetlone dane wyjściowe podobne do tych:
Dataset my-project:bq_load_codelab Last modified ACLs Labels ----------------- -------------------- -------- 15 Jun 14:12:49 Owners: projectOwners, your-email@example.com Writers: projectWriters Readers: projectReaders
4. Tworzenie pliku danych
BigQuery może wczytywać dane z kilku formatów danych, w tym z plików JSON rozdzielanych znakami nowego wiersza, Avro i CSV. Dla uproszczenia użyj pliku CSV.
Tworzenie pliku CSV
Utwórz w Cloud Shell pusty plik CSV.
touch customer_transactions.csv
Otwórz plik CSV w edytorze kodu w Cloud Shell, uruchamiając polecenie „edit” w Cloud Shell. Spowoduje to otwarcie nowego okna przeglądarki z edytorem kodu i panelem Cloud Shell.
cloudshell edit customer_transactions.csv
W edytorze kodu wpisz wartości oddzielone przecinkami, aby wczytać je do BigQuery.
ID,Zipcode,Timestamp,Amount,Feedback,SKU c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5 c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0
Zapisz plik CSV, klikając Plik > Edytuj.
5. Wczytaj dane
Użyj polecenia bq load, aby wczytać plik CSV do tabeli BigQuery.
bq load \ --source_format=CSV \ --skip_leading_rows=1 \ bq_load_codelab.customer_transactions \ ./customer_transactions.csv \ id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string
Użyto tych opcji:
- Podczas analizowania pliku danych
--source_format=CSV
używa formatu danych CSV. - Funkcja
--skip_leading_rows=1
pomija pierwszy wiersz w pliku CSV, ponieważ jest to wiersz nagłówka. Bq_load_codelab.customer_transactions—the first positional argument—
określa, do której tabeli mają zostać wczytane dane../customer_transactions.csv
– drugi argument pozycjonowania – określa, który plik ma zostać wczytany. Oprócz plików lokalnych polecenie bq load umożliwia wczytywanie plików z Cloud Storage za pomocą dodatkugs://my_bucket/path/to/file URIs
.- Schemat, który można zdefiniować w pliku schematu JSON lub w postaci listy rozdzielanej przecinkami. Dla uproszczenia, dla uproszczenia użyto listy rozdzielanej przecinkami.
Użyto tego schematu w tabeli customer_transactions:
Id:string
: identyfikator klientaZip:string
: kod pocztowy w Stanach Zjednoczonych.Ttime:timestamp
: data i godzina dokonania transakcji.Amount:numeric
: kwota transakcji (kolumna numeryczna zawiera dane w postaci dziesiętnej, co jest przydatne w przypadku wartości pieniężnych).Fdbk:float
: ocena z ankiety dotyczącej transakcji.Sku:string
: identyfikator zakupionego produktu.
Pobieranie szczegółów tabeli
Sprawdź, czy tabela została wczytana, wyświetlając jej właściwości.
bq show bq_load_codelab.customer_transactions
Dane wyjściowe:
Table my-project:bq_load_codelab.customer_transactions Last modified Schema Total Rows Total Bytes ----------------- --------------------- ------------ ------------- 15 Jun 15:13:55 |- id: string 3 159 |- zip: string |- ttime: timestamp |- amount: numeric |- fdbk: float |- sku: string
6. Wysyłanie zapytania dotyczącego danych
Po wczytaniu danych możesz utworzyć dla nich zapytanie za pomocą interfejsu internetowego BigQuery, polecenia bq lub interfejsu API. Zapytania mogą złączać Twoje dane z dowolnym zbiorem danych (lub zbiorem danych, o ile znajdują się w tej samej lokalizacji), do której masz uprawnienia do odczytu.
Uruchom standardowe zapytanie SQL, które złącza Twój zbiór danych ze zbiorem danych kodów pocztowych w Stanach Zjednoczonych i podsumowuje transakcje według stanu w USA. Użyj polecenia bq query, aby wykonać zapytanie.
bq query --nouse_legacy_sql ' SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code FROM `bq_load_codelab.customer_transactions` c JOIN `bigquery-public-data.utility_us.zipcode_area` z ON c.zip = z.zipcode GROUP BY state_code '
Powinno ono wyświetlić mniej więcej takie polecenie:
Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE +--------------+------------+ | amount_total | state_code | +--------------+------------+ | 53.6 | NY | | 7.18 | TX | +--------------+------------+
Utworzone zapytanie korzystało z publicznego zbioru danych i Twojego prywatnego zbioru danych. Aby dowiedzieć się więcej, przeczytaj tę skomentowaną wersję tego samego zapytania:
#standardSQL SELECT /* Total of all transactions in the state. */ SUM(c.amount) AS amount_total, /* State corresponding to the transaction's zipcode. */ z.state_code AS state_code /* Query the table you just constructed. * Note: If you omit the project from the table ID, * the dataset is read from your project. */ FROM `bq_load_codelab.customer_transactions` c /* Join the table to the zipcode public dataset. */ JOIN `bigquery-public-data.utility_us.zipcode_area` z /* Find the state corresponding to the transaction's zipcode. */ ON c.zip = z.zipcode /* Group over all transactions by state. */ GROUP BY state_code
7. Czyszczenie danych
Usuń zbiór danych utworzony za pomocą polecenia bq rm. Aby usunąć zawarte w niej tabele, użyj flagi „-r”.
bq rm -r bq_load_codelab
8. Gratulacje!
Udało Ci się przesłać tabelę do BigQuery i wysłać dotyczące jej zapytanie.
Omawiane tematy
- Korzystanie z narzędzia wiersza poleceń
bq
do interakcji z BigQuery. - złączanie danych z publicznym zbiorem danych za pomocą zapytania BigQuery,
Co dalej?
Więcej informacji:
- narzędzia wiersza poleceń
bq
, - Inne sposoby wczytywania danych do BigQuery.
- innych publicznych zbiorów danych dostępnych w BigQuery.
- Informacje o pogodzie, przestępczości i inne rodzaje danych w TIL z BigQuery.