<jupyter_start><jupyter_text>Data Definition Language - DDLLet us create a database `post`<jupyter_code>%load_ext sql
%sql hive://hadoop@localhost:10000/
%%sql
CREATE DATABASE IF NOT EXISTS post
%sql USE post<jupyter_output><empty_output><jupyter_text>PLZ Verzeichnis Let's focus first on the PLZ dataset<jupyter_code>!head -n3 /data/dataset/post/plz_verzeichnis_v2.csv<jupyter_output><empty_output><jupyter_text>We see that|REC_ART|ONRP|BFSNR|PLZ_TYP|POSTLEITZAHL|PLZ_ZZ|GPLZ|ORTBEZ18|ORTBEZ27|KANTON|SPRACHCODE|SPRACHCODE_ ABW|BRIEFZ_DURCH|GILT_AB_DAT|PLZ_BRIEFZUST|PLZ_COFF|Geo Shape|Geokoordinaten|| ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- ||01|111|5586|80|1000|07|1000|Lausanne St-Paul|Lausanne St-Paul|VD|2||130|1993-09-28|100060||||1. The separator is a `;`.2. "Kanton" would be a great partition. We will create one when we convert it to parquet.Remember, the different types `hive` can use are [here](https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=82706456LanguageManualTypes-date).Die Post gave additional information on the entire dataset [here](https://swisspost.opendatasoft.com/api/datasets/1.0/plz_verzeichnis_v2/attachments/strassenverzeichnis_mit_sortierdaten_de_pdf/). E.g. for the PLZ dataset:| Field name | Field type (length) | Mandatory field | Source | Observations || ------ | ------- | ------ | ------- | ------ || REC_ART | VARCHAR(2) | YES | “01” |Record type: Designates the record type. || ONRP | NUMBER(5) | YES | ASDP | Swiss Post classification number: This number (ONRP) is the primary key designating a postcode/location in accordance with the Swiss Post postcode database and the unique, unalterable key term of the postcode. || BFSNR | NUMBER(5) | YES | ASDP | Foreign key for BFSNR (refers to NEW_COM)|etc.<jupyter_code>%%sql
CREATE TABLE IF NOT EXISTS plz_csv (
    REC_ART VARCHAR(2),
    ONRP INT,
    BFSNR INT,
    PLZ_TYP SMALLINT,
    POSTLEITZAHL SMALLINT,
    PLZ_ZZ VARCHAR(2), 
    GPLZ SMALLINT,
    ORTBEZ18 VARCHAR(18),
    ORTBEZ27 VARCHAR(27),
    KANTON VARCHAR(2),
    SPRACHCODE TINYINT,
    SPRACHCODE_ABW TINYINT,
    BRIEFZ_DURCH INT,
    GILT_AB_DAT DATE,
    PLZ_BRIEFZUST INT,
    PLZ_COFF VARCHAR(1),
    Geo_Shape STRING,
    Geokoordinaten STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
tblproperties ("skip.header.line.count"="1")
%%sql
LOAD DATA LOCAL INPATH '/data/dataset/post/plz_verzeichnis_v2.csv' INTO TABLE plz_csv
%%sql 
SELECT
    REC_ART,
    ONRP,
    BFSNR,
    PLZ_TYP,
    POSTLEITZAHL,
    PLZ_ZZ, 
    GPLZ,
    ORTBEZ18,
    ORTBEZ27,
    SPRACHCODE,
    SPRACHCODE_ABW,
    BRIEFZ_DURCH,
    GILT_AB_DAT,
    PLZ_BRIEFZUST,
    PLZ_COFF,
    Kanton
from plz_csv limit 10<jupyter_output><empty_output><jupyter_text>Converting PLZ_CSV as a Parquet Table with Partitions1. We remove `Geo_Shape` and `Geokoordinaten`.2. We use compression.3. We use `Kanton` as a partition. We use `String` as the partition type.<jupyter_code>%%sql
CREATE TABLE IF NOT EXISTS plz (
    REC_ART VARCHAR(2),
    ONRP INT,
    BFSNR INT,
    PLZ_TYP SMALLINT,
    POSTLEITZAHL SMALLINT,
    PLZ_ZZ VARCHAR(2), 
    GPLZ SMALLINT,
    ORTBEZ18 VARCHAR(18),
    ORTBEZ27 VARCHAR(27),
    SPRACHCODE TINYINT,
    SPRACHCODE_ABW TINYINT,
    BRIEFZ_DURCH INT,
    GILT_AB_DAT DATE,
    PLZ_BRIEFZUST INT,
    PLZ_COFF VARCHAR(1)
)
PARTITIONED BY(KANTON string)
STORED AS Parquet
TBLPROPERTIES("parquet.compression"="SNAPPY")<jupyter_output><empty_output><jupyter_text>The partition `Kanton` needs to be the last column in the `insert` statement.<jupyter_code>%%sql
INSERT INTO TABLE plz 
    SELECT
        REC_ART,
        ONRP,
        BFSNR,
        PLZ_TYP,
        POSTLEITZAHL,
        PLZ_ZZ, 
        GPLZ,
        ORTBEZ18,
        ORTBEZ27,
        SPRACHCODE,
        SPRACHCODE_ABW,
        BRIEFZ_DURCH,
        GILT_AB_DAT,
        PLZ_BRIEFZUST,
        PLZ_COFF,
        Kanton 
    FROM plz_csv
%sql select * from plz limit 2<jupyter_output><empty_output><jupyter_text>Strassenbezeichnungen  Can you do the same for `strassenbezeichnungen_v2`?<jupyter_code>!head -n5 /data/dataset/post/strassenbezeichnungen_v2.csv
%%sql
CREATE TABLE IF NOT EXISTS streets_csv (
    REC_ART VARCHAR(2),
    ...
%%sql
LOAD DATA LOCAL ...
%%sql
CREATE TABLE IF NOT EXISTS streets (
    REC_ART VARCHAR(2),
    ...
%%sql
INSERT INTO TABLE ...
%sql select * from streets limit 2<jupyter_output><empty_output><jupyter_text>Bevölkerung  Add `bevoelkerung_proplz.csv` to the tables `bevoelkerung_csv` and `bevoelkerung` respectively.<jupyter_code>...
%sql select * from bevoelkerung limit 3<jupyter_output><empty_output><jupyter_text>Nachnamen  Finally, add `nachnamen_proplz.csv` to the tables `nachnamen_csv` and `nachnamen`. Use a patition on the gender for the parquet table `nachnamen`: `PARTITIONED BY(Geschlecht string)`<jupyter_code>...<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12579491255961844, 'fraction_numerical': 0.022655007949125595, 'mean_word_length': 3.5179533213644523, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '456628', 'license': 'MIT', 'n_tokens_mistral': 2042, 'n_tokens_neox': 1888, 'n_words': 426, 'path': 'V6/v6_exercises_material/2_DDL_Questions.ipynb', 'repo_name': 'fred1234/BDLC_FS22', 'size': '5032'}