Wczytywanie danych i wykonywanie na nich zapytań za pomocą narzędzia wiersza poleceń bq dla BigQuery

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

2. Konfiguracja

Włączanie BigQuery

Jeśli nie masz jeszcze konta Google, musisz je utworzyć.

  1. 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
  1. Zaakceptuj Warunki korzystania z usługi.
  2. Aby korzystać z BigQuery, musisz utworzyć projekt. Utwórz nowy projekt, postępując zgodnie z instrukcjami.

Wybierz nazwę projektu i zanotuj jego identyfikator. 1884405a64ce5765.png

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

  1. W konsoli Cloud kliknij Aktywuj Cloud Shell 4292cbf4971c9786.png.

bce75f34b2c53987.png

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:

70f315d7b402b476.png

Uzyskanie dostępu do Cloud Shell i połączenie się z nim powinno zająć tylko kilka chwil.

fbe3a0674c982259.png

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.

  1. 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`
  1. 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ą dodatku gs://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 klienta
  • Zip: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: