Rafał Gogłoza

Problem:

Jaki jest rozmiar tabeli w Databricks? Ile miejsca zajmuje mój schemat? Jak policzyć rozmiar? W jaki sposób sprawdzić ile przybyło danych od ostatniego ładowania? Ile miejsca zajmuje bronze, silver oraz gold layer? Jak to zadanie zautomatyzować? Czy można z tego wyciągnąć jeszcze jakieś wnioski?

Rozwiązanie:

W Databricks dostępne jest polecenie:

describe detail table_name

Umożliwia ono pokazanie rozmiaru w bajtach, wylistowanie ile plików zajmuje obecnie tabela. Pokazuje też kiedy zostało utworzona albo ostatnio załadowana.

Pokażę teraz w jaki sposób, wygląda skrypt, który dla schematu zbiera dane o wszystkich tabelach.

Jaki jest rozmiar tabeli w Databricks - przygotowanie

Zacznijmy od ustawienia parametrów. W widgetach ustal z jakiego schematu pobierasz dane i do jakiej tabeli je zapisujesz.

Definicja widgetów:

dbutils.widgets.text('target_schema', 'next_level_dm')
dbutils.widgets.text('user_tab_size_table_name', 'dbx_user_tab_size')

Pobranie ustawionej wartości z widetów:

in_target_schema = dbutils.widgets.get('target_schema')
in_user_tab_size = dbutils.widgets.get('user_tab_size_table_name')

Oraz import niezbędnych funkcji sparkowych do operacji na kolumnach.

from pyspark.sql.functions import col, lit

Ostatni punkt to wylistowanie wszystkich analizowanych tabel. Do tego posłuży tabela: dbx_user_tab_column, Zajrzyj do tego wpisu jeżeli jej jeszcze nie masz!

all_tables = spark.sql(f"select distinct table_name, substr(table_name,1,locate('_', table_name)-1) as table_prefix
from {in_target_schema}.dbx_user_tab_columns 
where table_name not like 'v%' and table_name not like 'dbx%' and table_name not like 'z%' ").collect()

Jaki jest rozmiar tabeli w Databricks - zbieranie wyników

Na początku definicja zmiennej, która będzie przechowywać wynikowy DataFrame oraz zaczynamy iterację po tabelach, pobranych w poleceniu powyżej.

table_size_df = None
for table in all_tables:
    table_name = table["table_name"]

Sprawdzamy czy tabela istnieje w schemacie w spark katalogu, żeby uniknąć ewentualnych błędów i wykonujemy zapytanie describe detail. Wyniki zbieramy w DataFrame.

if spark.catalog.tableExists(f"{in_target_schema}.{table_name}"):
table_details = spark.sql(f"describe detail {in_target_schema}.{table_name}")

Przyszła pora na wyliczenia dodatkowych kolumn. Liczymy liczbę wierszy w tabeli oraz rozmiar tabeli w GB oraz estymujemy rozmiar jednego pliku. Dodajemy też nazwę tabeli oraz jej prefix (to co znajduje się przed pierwszym podkreśleniem).

nbr_of_rows = spark.sql(f"select count(*) as cnt from {in_target_schema}.{table_name}").collect()[0]["cnt"]

table_details = table_details.withColumn("size_in_gb",col("sizeInBytes")/(1024*1024*1024))
.withColumn('size_per_file', col("sizeInBytes")/col("numFiles"))
.withColumn("nbr_of_rows",lit(nbr_of_rows)).withColumn("table_name",lit(table_name))
.withColumn("table_prefix",lit(table["table_prefix"]))

Ostatnia część w tej pętli to tworzenie nazw kolumn oraz tworzenie lub rozszerzanie DataFrame o właśnie wyliczone dane.

table_details = table_details.selectExpr("table_name","table_prefix","sizeInBytes as size_in_bytes","size_in_gb", "numFiles as nbr_of_files"
,"createdAt as created_at","lastModified as last_modified_at", "statistics","partitionColumns as partition_columns"
,"clusteringColumns as clustering_columns","nbr_of_rows","size_per_file")

table_size_df = table_details if table_size_df is None else table_size_df.unionAll(table_details)

Jaki jest rozmiar tabeli w Databricks - zapisywanie wyników

Teraz zostało już tylko zapisanie wyników. Najpierw stworzymy tymczasowy widok i potem dodamy do niego kolumnę audytową load_date, żeby wiedzieć, kiedy załadowana została tabela.

table_size_df.createOrReplaceTempView('v_user_tab_size')

spark.sql(f"""create or replace table {in_target_schema}.{in_user_tab_size} as 
            select us.*, current_timestamp() as load_date from v_user_tab_size us """)

Cały kod dostępny jak zawsze na GitHub:
https://github.com/rgogloza/nextlevelbi/blob/master/databricks/dbx_user_tab_size.ipynb

Jaki jest rozmiar tabeli w Databricks - dla dociekliwych

Rozmiar tabeli można sprawdzić też w inny sposób, służy do tego sparkowe polecenie:

describe table extended table_name

Pokazuje ono rozmiar tabeli w bajtach tylko, że niestety czasami ta wartość nie jest rzeczywista. Nie odpowiada temu jest w describe detail.

Dlaczego tak się dzieje? Czy da się zobaczyć poprawne wartości?

Tak oczywiście, tylko najpierw uruchom:

analyze table table_name compute statistics

To polecenia "odświeża" dane, które wyświetla describe table extended i wtedy masz spójne dane z tym co widzisz w describe detail. Z moich obserwacji wynika, że dane w describe detail są poprawne.