Proceduralny SQL w BigQuery – podstawowe przetwarzanie danych ze zbioru International Census Data
Artykuł w 8 krokach wprowadza czytelnika do podstawowych zagadnień związanych z proceduralnymi zapytaniami SQL w BigQuery. Wykorzystałem w nim publiczne zbiory danych United States Census Bureau, które posłużyły do określenia wielkości populacji Polski (w dostępnych zbiorach możemy zrobić to także dla innych krajów) w poszczególnych latach końcówki minionego wieku, aż po predykcję amerykańskiego biura do roku 2050.
Zapoznasz się w nim z przykładem wykorzysta instrukcji: DECLARE, SET, EXECUTE IMMEDIATE, CONCAT, CAST, SUBSTR, LENGTH, SUM, WITH i pętlą WHILE
Krok 1 - Określenie celu
Pytanie biznesowe na które w analizie będziemy się starali odpowiedzieć brzmi: Sprawdź, jak w poszczególnych latach dostępnych w zbiorze danych wyglądała liczba ludności w Polsce (możesz wybrać także inny kraj ze zbioru danych), a także jak wygląda predykcja populacji w kolejnych latach, aż do roku 2050, wykonana przez urząd USCB.
Krok 2 - Przygotowanie i analiza dostępnych danych
Do odpowiedzi na pytanie postawione w kroku pierwszym, wybrałem tabelę midyear_population_age_sex ze zbioru danych census_bureau_international.
Z metadanych tabeli wynika, że możemy znaleźć w niej pola informujące liczbie populacji w danym kraju z podziałem na poszczególne lata, płeć, kohorty wieku od 0 do 100 lat, a także maksymalny wiek członka populacji. Tabela niestety nie zawiera zbiorczych danych o populacji w danych latach zawierających zsumowane wyniki kobiet i mężczyzn w różnym wieku.
Dodatkową trudność w analizie może sprawić nam schemat tabeli, w której dla każdego rekordu mamy, aż 100 kolumn informujących o wielkości populacji w danym wieku. Ręczne wpisywanie nazw kolumn do zapytania, może stanowić nie lada wyzwania, jednocześnie być łatwym źródłem błędów. Na szczęście poszczególne nazwy kolumn tabeli z kohortami wieku składają się z prefixu: population_age_ i liczby od 0 do 100 określającej wiek jej członków.
Dzięki takiemu nazewnictwu tabeli, możemy wykorzystać pętlę, która wykorzystując inkrementację liczb od 0 do 100, będzie tworzyła nam nazwy kolumn i zapisywała je w odpowiedni sposób w zapytaniu. W tym celu będziemy korzystać z instrukcji tzw. proceduralnych SQL, które umożliwią nam tego typu działania.
Screen 1. Schemat tabeli midyear_population_age_sex w której widać tylko część dostępnych kolumn z kohortami wieku.
Krok 3 - Instrukcje DECLARE, SET, EXECUTE.
Napiszmy pierwszą podstawową wersję kodu, która pozwoli wyciągnąć nam dane z tabeli midyear_population_age_sex.
DECLARE sql_query STRING;
SET sql_query = "SELECT year, country_code, sex FROM bigquery-public-data.census_bureau_international.midyear_population_age_sex WHERE country_code = 'PL'";
EXECUTE IMMEDIATE sql_query
Kod. 01.
DECLARE sql_query STRING – linia deklaruje zmienną o nazwie sql_query z typem danych STRING. Zmienna będzie używana do przechowywania zapytania SQL jako ciągu znaków.
SET sql_query = “SELECT year, country_code, sex FROM bigquery-public-data.census_bureau_international.midyear_population_age_sex WHERE country_code = ‘PL'” – linia przypisuje wartość zmiennej sql_query jako ciąg zapytania SQL. Zapytanie pobiera dane z tabeli o nazwie midyear_population_age_sex w zbiorze danych bigquery-public-data.census_bureau_international. Wybiera kolumny: rok, kod kraju i płeć z tabeli, gdzie kod kraju to „PL” (co odnosi się do Polski).
EXECUTE IMMEDIATE sql_query – linia wykonuje zapytanie SQL zapisane w zmiennej sql_query. Instrukcja EXECUTE IMMEDIATE służy do uruchamiania dynamicznego zapytania SQL.
KROK 4 - Ustawianie wartości zmiennej w zapytaniu z wykorzystaniem instrukcji USING
DECLARE sql_query STRING;
DECLARE country_code_var STRING;
SET country_code_var = 'PL';
SET sql_query = "SELECT year, country_code, sex FROM bigquery-public-data.census_bureau_international.midyear_population_age_sex WHERE country_code = ?";
EXECUTE IMMEDIATE sql_query USING country_code_var;
Kod. 02.
W każdym z kolejnych kroków, będę opisywał tylko nowe linie jakie pojawiły się kodzie lub omawiał zmiany jakie zostały w nim wprowadzone.
DECLARE country_code_var STRING – linia deklaruje zmienną o nazwie country_code_var z typem danych STRING. Ta zmienna będzie przechowywać informacje o kodzie kraju.
SET country_code_var = ‘PL’ – linia przypisuje zmiennej country_code_var wartość ‘PL’, czyli kod kraju oznaczający Polskę.
SET sql_query = “SELECT year, country_code, sex FROM bigquery-public-data.census_bureau_international.midyear_population_age_sex WHERE country_code = ?” – linia przypisuje zmiennej sql_query konkretne zapytanie SQL. Zapytanie to wybiera kolumny year, country_code i sex z tabeli midyear_population_age_sex w zbiorze danych bigquery-public-data.census_bureau_international. Z kolei warunek WHERE country_code = ? wskazuje, że kod kraju będzie podstawiany w miejsce znaku “?”.
EXECUTE IMMEDIATE sql_query USING country_code_var – linia wykonuje zapytanie SQL przechowywane w zmiennej sql_query. Klauzula USING country_code_var wskazuje, że zmienna country_code_var ma być użyta do wstawienia wartości w miejsce znaku zapytania (?) w zapytaniu SQL.
KROK 5 - Użycie funkcji CONCAT
DECLARE sql_query_select STRING;
DECLARE sql_query_age_column STRING;
DECLARE sql_query_final STRING;
DECLARE country_code_var STRING;
SET country_code_var = 'PL';
SET sql_query_select = "SELECT year, country_code, sex,";
SET sql_query_age_column = "population_age_0 FROM bigquery-public-data.census_bureau_international.midyear_population_age_sex WHERE country_code = ?";
SET sql_query_final = CONCAT(sql_query_select,sql_query_age_column);
EXECUTE IMMEDIATE sql_query_final USING country_code_var;
Kod. 03
Ponieważ w docelowym zapytaniu, będziemy łączyć poszczególne jego elementy, a w szczególności generowane w pętli nazwy kolumn “population_age_” sprawdźmy jak należy zakodować i skorzystać z funkcji CONCAT. Rozbijamy ciąg zapytania na kolejne części i w kodzie umieszczamy ich deklaracje oraz przypisujemy wartości.
SET sql_query_final = CONCAT(sql_query_select,sql_query_age_column) – linia łączy dwie wcześniejsze części zapytania SQL (sql_query_select i sql_query_age_column) za pomocą funkcji CONCAT, tworząc ostateczne zapytanie.
KROK 6 - Zmienna typu INTEGER, funkcja CAST
DECLARE sql_query_select STRING;
DECLARE sql_query_age_column STRING;
DECLARE sql_query_end STRING;
DECLARE sql_query_final STRING;
DECLARE country_code_var STRING;
DECLARE age_group INT64;
SET age_group = 0;
SET country_code_var = 'PL';
SET sql_query_select = "SELECT year, country_code, sex,";
SET sql_query_age_column = "population_age_";
SET sql_query_age_column = CONCAT(sql_query_age_column,CAST(age_group AS STRING));
SET sql_query_end = " FROM bigquery-public-data.census_bureau_international.midyear_population_age_sex WHERE country_code = ?";
SET sql_query_final = CONCAT(sql_query_select,sql_query_age_column,sql_query_end);
EXECUTE IMMEDIATE sql_query_final USING country_code_var;
Kod. 04
DECLARE age_group INT64 – Deklarujemy zmienną age_group o typie danych INT64 (liczba całkowita). Będzie używana do przechowywania wartości wieku.
SET age_group = 0 – Przypisuje zmiennej age_group wartość początkową 0.
SET sql_query_age_column = CONCAT(sql_query_age_column,CAST(age_group AS STRING)) – Do zmiennej sql_query_age_column doklejamy wartość zmiennej age_group, zamienioną dzięki funkcji CAST z typu integer na łańcuch znaków (string). Tworzy w ten sposób nazwę kolumny wieku dla konkretnej grupy wiekowej (efekt, kolumna będzie nazywać się: population_age_0.
KROK 7 - Pętla WHILE, funkcja SUBSTR, LENGTH
DECLARE sql_query_select STRING;
DECLARE sql_query_age_column STRING;
DECLARE sql_query_age_column_prefix STRING;
DECLARE sql_query_end STRING;
DECLARE sql_query_final STRING;
DECLARE country_code_var STRING;
DECLARE age_group INT64;
SET age_group = 0;
SET country_code_var = 'PL';
SET sql_query_select = "SELECT year, country_code, sex,";
SET sql_query_age_column_prefix = "population_age_";
SET sql_query_age_column = " ";
WHILE age_group<=100 DO
SET sql_query_age_column = CONCAT(sql_query_age_column,sql_query_age_column_prefix,CAST(age_group AS STRING),"+");
SET age_group = age_group + 1;
END WHILE;
SET sql_query_age_column = SUBSTR(sql_query_age_column,0,LENGTH(sql_query_age_column)-1);
SET sql_query_age_column = CONCAT(sql_query_age_column," as population_total");
SET sql_query_end = " FROM bigquery-public-data.census_bureau_international.midyear_population_age_sex WHERE country_code = ?";
SET sql_query_final = CONCAT(sql_query_select,sql_query_age_column,sql_query_end);
EXECUTE IMMEDIATE sql_query_final USING country_code_var;
Kod. 05
W kodzie pojawiła się pętla WHILE.
WHILE age_group<=100 DO – pętla będzie się wykonywać, dopóki wartość age_group jest mniejsza lub równa 100.
SET sql_query_age_column = SUBSTR(sql_query_age_column,0,LENGTH(sql_query_age_column)-1) – linia ta usuwa ostatni znak “+” z ciągu sql_query_age_column, który pozostał po zakończeniu pętli. By dokonać tej operacji, potrzebna nam była długość ciągu, którą obliczamy za pomocą funkcji LENGHT.
KROK 8 - Grupowanie i ostateczne wyliczenie wielkości populacji w danym roku.
Gdybyśmy w tak przygotowanym zapytaniu spróbowali pogrupować dane po kolumnie “year”, by uzyskać sumę populacji mężczyzn i kobiet z danego roku, serwer SQL poinformuje nas o konieczności grupowania wszystkich 100 kolumn kohort wieku. Dlatego zamiast tworzyć skomplikowane i długie zapytanie, wykorzystamy opracowany kod do stworzenia tablicy tymczasowej, której to dane będziemy agregować w kolejnym zapytaniu:
DECLARE sql_query_select STRING;
DECLARE sql_query_age_column STRING;
DECLARE sql_query_age_column_prefix STRING;
DECLARE sql_query_end STRING;
DECLARE sql_query_final STRING;
DECLARE country_code_var STRING;
DECLARE age_group INT64;
SET age_group = 0;
SET country_code_var = 'PL';
SET sql_query_select = "SELECT year, country_code, sex,";
SET sql_query_age_column_prefix = "population_age_";
SET sql_query_age_column = " ";
WHILE age_group<=100 DO
SET sql_query_age_column = CONCAT(sql_query_age_column,sql_query_age_column_prefix,CAST(age_group AS STRING),"+");
SET age_group = age_group + 1;
END WHILE;
SET sql_query_age_column = SUBSTR(sql_query_age_column,0,LENGTH(sql_query_age_column)-1);
SET sql_query_age_column = CONCAT(sql_query_age_column," as population_total");
SET sql_query_end = " FROM bigquery-public-data.census_bureau_international.midyear_population_age_sex WHERE country_code = ?";
SET sql_query_final = CONCAT("WITH Population_Data AS(",sql_query_select,sql_query_age_column,sql_query_end,")");
SET sql_query_final = CONCAT(sql_query_final,"SELECT year,country_code, SUM(population_total) as population_all FROM Population_Data GROUP BY year, country_code");
EXECUTE IMMEDIATE sql_query_final USING country_code_var;
Kod. 06
W wyniku powyższego kodu otrzymamy wartości, które zostały wyznaczone jako cel analizy.
