Spark.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/olympus999/Carvana-Image-Masking-Challenge/blob/master/Spark.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "jlo-OQZJziQz",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "de71cce4-d8b9-4993-9ddc-ca18493c5eaf"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting ijson\n",
            "  Downloading ijson-3.2.3-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (111 kB)\n",
            "\u001b[?25l     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m0.0/111.8 kB\u001b[0m \u001b[31m?\u001b[0m eta \u001b[36m-:--:--\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[91m╸\u001b[0m\u001b[90m━━━\u001b[0m \u001b[32m102.4/111.8 kB\u001b[0m \u001b[31m3.2 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m111.8/111.8 kB\u001b[0m \u001b[31m2.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: ijson\n",
            "Successfully installed ijson-3.2.3\n"
          ]
        }
      ],
      "source": [
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "!wget -q https://dlcdn.apache.org/spark/spark-3.5.0/spark-3.5.0-bin-hadoop3.tgz\n",
        "!tar xf spark-3.5.0-bin-hadoop3.tgz\n",
        "!pip install -q findspark\n",
        "!pip install ijson"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "2tspqUYq1RA-"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "import urllib.request\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.5.0-bin-hadoop3\"\n",
        "import findspark\n",
        "findspark.init()\n",
        "from pyspark.sql import SparkSession\n",
        "from pyspark import SparkContext\n",
        "from pyspark import SparkConf\n",
        "from pyspark.sql.types import *\n",
        "from os.path import isfile\n",
        "import zipfile\n",
        "import json\n",
        "import ast\n",
        "import time\n",
        "import ijson"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "vVmqsSvMCNW3"
      },
      "outputs": [],
      "source": [
        "conf = SparkConf().set(\"spark.driver.memory\", \"10g\")\\\n",
        "                  .set(\"spark.executor.memory\", \"10g\")\n",
        "spark = SparkSession.builder.config(conf=conf).master(\"local[*]\").appName('avaandmed_ariregister').getOrCreate()\n",
        "spark.conf.set(\"spark.sql.repl.eagerEval.enabled\", True) # Property used to format output tables better\n",
        "\n",
        "sc = SparkContext.getOrCreate(SparkConf().setMaster(\"local[*]\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "dlqlpHlRKrzu"
      },
      "outputs": [],
      "source": [
        "# spark.sparkContext.getConf().getAll()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "oD3ljdcHRUSo"
      },
      "outputs": [],
      "source": [
        "folder_data = 'data'\n",
        "folder_data_unpacked = os.path.join(folder_data, 'unpacked')\n",
        "folder_data_spark_json = os.path.join(folder_data_unpacked, 'spark_json')\n",
        "\n",
        "# Data is downloaded from here: https://avaandmed.ariregister.rik.ee/et/avaandmete-allalaadimine#ettepanekud-ja-tagasiside\n",
        "generic_data = {\n",
        "    'lihtandmed': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__lihtandmed.csv.zip',\n",
        "    # 'yldandmed': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__yldandmed.json.zip',\n",
        "    # 'registrikaardid': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__registrikaardid.json.zip',\n",
        "    # 'kaardile_kantud_isikud': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__kaardile_kantud_isikud.json.zip',\n",
        "    # 'kandevalised_isikud': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__kandevalised_isikud.json.zip',\n",
        "    # 'osanikud': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__osanikud.json.zip',\n",
        "    # 'kasusaajad': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__kasusaajad.json.zip',\n",
        "    'kommertspandiandmed': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__kommertspandid.json.zip',\n",
        "    # 'maarused': 'https://avaandmed.ariregister.rik.ee/sites/default/files/avaandmed/ettevotja_rekvisiidid__maarused.json.zip'\n",
        "}\n",
        "\n",
        "yearly_data = {\n",
        "    # 'aruannete_yldandmed': 'https://avaandmed.ariregister.rik.ee/sites/default/files/1.aruannete_yldandmed_kuni_31102023.zip',\n",
        "    'emtak_myygitulu_jaotus': 'https://avaandmed.ariregister.rik.ee/sites/default/files/2.EMTAK_myygitulu_kuni_31102023.zip',\n",
        "    # 'myygitulu_geograafiline_jaotus': 'https://avaandmed.ariregister.rik.ee/sites/default/files/3.myygitulu_geograafiline_kuni_31102023.zip',\n",
        "    # 'pohinaitajad_2020_aasta_aruannetest': 'https://avaandmed.ariregister.rik.ee/sites/default/files/4.2020_aruannete_elemendid_kuni_31102023.zip',\n",
        "    # 'pohinaitajad_2021_aasta_aruannetest': 'https://avaandmed.ariregister.rik.ee/sites/default/files/4.2021_aruannete_elemendid_kuni_31102023.zip',\n",
        "    # 'pohinaitajad_2022_aasta_aruannetest': 'https://avaandmed.ariregister.rik.ee/sites/default/files/4.2022_aruannete_elemendid_kuni_31102023.zip',\n",
        "}\n",
        "\n",
        "# Data is downloaded to Google Drive as they only keep the last 3 years of data available on their site.\n",
        "# Data is downloaded from here: https://www.emta.ee/ariklient/amet-uudised-ja-kontakt/uudised-pressiinfo-statistika/statistika-ja-avaandmed#tasutud-maksud-failid\n",
        "\n",
        "paid_taxes = {\n",
        "    'tasutud_maksud_2020_i_kvartal': 'https://drive.google.com/uc?id=1h1-hW7sTCMlOxxvz4258W403_HC5QTJV',\n",
        "    'tasutud_maksud_2020_ii_kvartal': 'https://drive.google.com/uc?id=1qDBr9U8WtSsFucuyEkJy-Hmhx2222zpY',\n",
        "    'tasutud_maksud_2020_iii_kvartal': 'https://drive.google.com/uc?id=1LFo2bueoIcedmDUdbN6YLZN0_53HWxFY',\n",
        "    'tasutud_maksud_2020_iv_kvartal': 'https://drive.google.com/uc?id=1OIU9IlYQlRtzsccOSqB6uEHLBnq4Axst',\n",
        "    'tasutud_maksud_2021_i_kvartal': 'https://drive.google.com/uc?id=15t26vju1g3wR1Bqw7QCe7VFSIUXpgAWu',\n",
        "    'tasutud_maksud_2021_ii_kvartal': 'https://drive.google.com/uc?id=1Rz6KmBzw61T98noX4_aRWiZEiAjGUSa3',\n",
        "    'tasutud_maksud_2021_iii_kvartal': 'https://drive.google.com/uc?id=1aOJ_-KQDCs-oIFZMLqJql4IkICo5P-lk',\n",
        "    'tasutud_maksud_2021_iv_kvartal': 'https://drive.google.com/uc?id=10XyphD7LCub2B9EySdLG47KXhrJr9ipD',\n",
        "    'tasutud_maksud_2022_i_kvartal': 'https://drive.google.com/uc?id=1sU5wguPYaY2odNMM8aQRwopwm-sjL1oa',\n",
        "    'tasutud_maksud_2022_ii_kvartal': 'https://drive.google.com/uc?id=1diLKS7NhlLZg2e-c_wRYPLTTUAeTdJxP',\n",
        "    'tasutud_maksud_2022_iii_kvartal': 'https://drive.google.com/uc?id=1j0aeqXmzG3gdmcJkAZOvC_T_TfXnjNhA',\n",
        "    'tasutud_maksud_2022_iv_kvartal': 'https://drive.google.com/uc?id=1TGiL1zc_WPSTai8DrbxXSLaoVWXg11Qm',\n",
        "    'tasutud_maksud_2023_i_kvartal': 'https://drive.google.com/uc?id=1gsCyYuKYjN-nP-NdYWCYuMQZXRU8hpV6',\n",
        "    'tasutud_maksud_2023_ii_kvartal': 'https://drive.google.com/uc?id=1sskcbN0cAIaD8H4_34AyRxv2AwJliF1Q',\n",
        "    'tasutud_maksud_2023_iii_kvartal': 'https://drive.google.com/uc?id=1DPVmFwe6BkjD_j_VegXMJaLoyR7z9pcu',\n",
        "}\n",
        "\n",
        "all_data = {**generic_data, **yearly_data, **paid_taxes}"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# customSchema = StructType([\n",
        "#     StructField(\"IDGC\", StringType(), True),\n",
        "#     StructField(\"SEARCHNAME\", StringType(), True),\n",
        "#     StructField(\"PRICE\", DoubleType(), True)\n",
        "# ])\n",
        "\n",
        "key_taxes = 'tasutud_maksud'\n",
        "\n",
        "schemas = {\n",
        "  key_taxes :\n",
        "    StructType([\n",
        "        StructField(\"Riiklikud Maksud\", DoubleType(), True)\n",
        "    ])\n",
        "}"
      ],
      "metadata": {
        "id": "Ga3bJCwTViVx"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "id": "bumlg17oX590"
      },
      "outputs": [],
      "source": [
        "def mkdir(p):\n",
        "  if not os.path.isdir(p):\n",
        "    os.makedirs(p)\n",
        "\n",
        "def is_key_taxes(key: str):\n",
        "  return key.startswith(key_taxes)\n",
        "\n",
        "def get_filename(url: str, key: str = None) -> str:\n",
        "  if key is not None and is_key_taxes(key):\n",
        "    return key + '.csv'\n",
        "  return url.split('/')[-1]\n",
        "\n",
        "def save_avaandmed(key: str, url: str, overwrite=False) -> str:\n",
        "  \"CSV files can be saved to folder ´unpacked´\"\n",
        "  file_name = get_filename(url, key)\n",
        "  if file_name.endswith('.csv'):\n",
        "    file_path_name = f\"{folder_data_unpacked}/{file_name}\"\n",
        "  else:\n",
        "    file_path_name = f\"{folder_data}/{file_name}\"\n",
        "  if overwrite or not os.path.isfile(file_path_name):\n",
        "    urllib.request.urlretrieve(url, file_path_name)\n",
        "\n",
        "  return file_path_name\n",
        "\n",
        "def file_path_unpacked(key: str) -> str:\n",
        "  url = all_data[key]\n",
        "  file_name = get_filename(url, key)\n",
        "  head, _sep, tail = file_name.rpartition('.zip')\n",
        "  path = os.path.join(folder_data_unpacked, (head + tail))\n",
        "\n",
        "  if not is_json_or_csv(path):\n",
        "    path = path + '.csv' # ´.csv´ is missing in some cases\n",
        "\n",
        "  return path\n",
        "\n",
        "def file_path_spark_json(key: str) -> str:\n",
        "  url = all_data[key]\n",
        "  file_name = get_filename(url)\n",
        "  head, _sep, tail = file_name.rpartition('.zip')\n",
        "\n",
        "  return os.path.join(folder_data_spark_json, (head + tail))\n",
        "\n",
        "def create_dirs():\n",
        "  mkdir(folder_data)\n",
        "  mkdir(folder_data_unpacked)\n",
        "  mkdir(folder_data_spark_json)\n",
        "\n",
        "def download_files():\n",
        "  for key, value in all_data.items():\n",
        "    print('downloaded', key, '-', save_avaandmed(key, value))\n",
        "\n",
        "def unpack_files():\n",
        "  for f in os.listdir(folder_data):\n",
        "    p = os.path.join(folder_data, f)\n",
        "    if not os.path.isdir(p):\n",
        "      with zipfile.ZipFile(p, \"r\") as zip_ref:\n",
        "        zip_ref.extractall(folder_data_unpacked)\n",
        "        print('unpacked', '-', p, 'to', folder_data_unpacked)\n",
        "\n",
        "def json_to_table(path: str, key: str):\n",
        "  df = spark.read.json(path)\n",
        "  create_table(df, key)\n",
        "\n",
        "def csv_to_table(path: str, key: str):\n",
        "  kwargs = {\n",
        "      'path': path,\n",
        "      'encoding':'iso8859-1',\n",
        "      'header':True,\n",
        "      'inferSchema':True,\n",
        "      'sep':';'\n",
        "  }\n",
        "\n",
        "  if is_key_taxes(key):\n",
        "    pass\n",
        "    # kwargs['schema'] = schemas[key_taxes]\n",
        "  df = spark.read.csv(**kwargs)\n",
        "  create_table(df, key)\n",
        "\n",
        "def create_table(df, key):\n",
        "  print(f'Trying to create table ´{key}´ - ', end=\"\")\n",
        "  df.createOrReplaceTempView(key)\n",
        "  print(f'Done!')\n",
        "\n",
        "def is_json(path: str) -> bool:\n",
        "  return path.endswith('.json')\n",
        "\n",
        "def is_csv(path: str) -> bool:\n",
        "  return path.endswith('.csv')\n",
        "\n",
        "def is_json_or_csv(path: str) -> bool:\n",
        "  return is_json(path) or is_csv(path)\n",
        "\n",
        "def modify_json_for_spark(path: str, path_spark: str):\n",
        "  # Spark expectes certain JSON format.\n",
        "  # The function changes the following:\n",
        "  ## [\n",
        "  ##   { [record] },\n",
        "  ##   { [record] }\n",
        "  ## ]\n",
        "  # to\n",
        "  ## { [record] }\n",
        "  ## { [record] }\n",
        "\n",
        "  if is_json(path):\n",
        "    print('Starting to modify JSON ', path, end=\"\")\n",
        "    start_time = time.time()\n",
        "    temp_path = 'temp.json'\n",
        "\n",
        "    # Parsing with ijson because some files are quite big in size.\n",
        "    with open(path, 'r') as f:\n",
        "      with open(path_spark, 'w') as json_objects:\n",
        "        for n in ijson.items(f, 'item', use_float=True):\n",
        "          json_objects.write(json.dumps(n) + '\\n')\n",
        "\n",
        "    time_it_took = time.time() - start_time\n",
        "    print(' - done! - Time:', round(time_it_took, 1), 'seconds')\n",
        "  else:\n",
        "    print('NOT JSON path:', path)\n",
        "\n",
        "def modify_downloaded_jsons():\n",
        "  for key in all_data.keys():\n",
        "    path = file_path_unpacked(key)\n",
        "    path_spark = file_path_spark_json(key)\n",
        "    modify_json_for_spark(path, path_spark)\n",
        "\n",
        "def create_temp_tables():\n",
        "  for key in all_data.keys():\n",
        "    path = file_path_unpacked(key)\n",
        "    if path.endswith('.json'):\n",
        "      path = file_path_spark_json(key)\n",
        "      json_to_table(path, key)\n",
        "    elif path.endswith('.csv'):\n",
        "      csv_to_table(path, key)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "key = 'tasutud_maksud_2020_i_kvartal'\n",
        "path = file_path_unpacked(key)\n",
        "csv_to_table(path, key)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vPgwlye3YUTO",
        "outputId": "0d86f746-035a-4ec6-e1ec-7a1ba580728c"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Trying to create table ´tasutud_maksud_2020_i_kvartal´ - Done!\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# create_dirs()\n",
        "\n",
        "# download_files()\n",
        "\n",
        "# unpack_files()"
      ],
      "metadata": {
        "id": "uPZrQnMHiN0_"
      },
      "execution_count": 10,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "L8EAKa8gDSjc",
        "outputId": "b3596aaa-a923-41cb-c76c-843f9b3c8a35"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "downloaded lihtandmed - data/ettevotja_rekvisiidid__lihtandmed.csv.zip\n",
            "downloaded kommertspandiandmed - data/ettevotja_rekvisiidid__kommertspandid.json.zip\n",
            "downloaded emtak_myygitulu_jaotus - data/2.EMTAK_myygitulu_kuni_31102023.zip\n",
            "downloaded tasutud_maksud_2020_i_kvartal - data/unpacked/tasutud_maksud_2020_i_kvartal.csv\n",
            "downloaded tasutud_maksud_2020_ii_kvartal - data/unpacked/tasutud_maksud_2020_ii_kvartal.csv\n",
            "downloaded tasutud_maksud_2020_iii_kvartal - data/unpacked/tasutud_maksud_2020_iii_kvartal.csv\n",
            "downloaded tasutud_maksud_2020_iv_kvartal - data/unpacked/tasutud_maksud_2020_iv_kvartal.csv\n",
            "downloaded tasutud_maksud_2021_i_kvartal - data/unpacked/tasutud_maksud_2021_i_kvartal.csv\n",
            "downloaded tasutud_maksud_2021_ii_kvartal - data/unpacked/tasutud_maksud_2021_ii_kvartal.csv\n",
            "downloaded tasutud_maksud_2021_iii_kvartal - data/unpacked/tasutud_maksud_2021_iii_kvartal.csv\n",
            "downloaded tasutud_maksud_2021_iv_kvartal - data/unpacked/tasutud_maksud_2021_iv_kvartal.csv\n",
            "downloaded tasutud_maksud_2022_i_kvartal - data/unpacked/tasutud_maksud_2022_i_kvartal.csv\n",
            "downloaded tasutud_maksud_2022_ii_kvartal - data/unpacked/tasutud_maksud_2022_ii_kvartal.csv\n",
            "downloaded tasutud_maksud_2022_iii_kvartal - data/unpacked/tasutud_maksud_2022_iii_kvartal.csv\n",
            "downloaded tasutud_maksud_2022_iv_kvartal - data/unpacked/tasutud_maksud_2022_iv_kvartal.csv\n",
            "downloaded tasutud_maksud_2023_i_kvartal - data/unpacked/tasutud_maksud_2023_i_kvartal.csv\n",
            "downloaded tasutud_maksud_2023_ii_kvartal - data/unpacked/tasutud_maksud_2023_ii_kvartal.csv\n",
            "downloaded tasutud_maksud_2023_iii_kvartal - data/unpacked/tasutud_maksud_2023_iii_kvartal.csv\n",
            "unpacked - data/2.EMTAK_myygitulu_kuni_31102023.zip to data/unpacked\n",
            "unpacked - data/ettevotja_rekvisiidid__kommertspandid.json.zip to data/unpacked\n",
            "unpacked - data/ettevotja_rekvisiidid__lihtandmed.csv.zip to data/unpacked\n",
            "NOT JSON path: data/unpacked/ettevotja_rekvisiidid__lihtandmed.csv\n",
            "Starting to modify JSON  data/unpacked/ettevotja_rekvisiidid__kommertspandid.json - done! - Time: 0.5 seconds\n",
            "NOT JSON path: data/unpacked/2.EMTAK_myygitulu_kuni_31102023.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2020_i_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2020_ii_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2020_iii_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2020_iv_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2021_i_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2021_ii_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2021_iii_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2021_iv_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2022_i_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2022_ii_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2022_iii_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2022_iv_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2023_i_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2023_ii_kvartal.csv\n",
            "NOT JSON path: data/unpacked/tasutud_maksud_2023_iii_kvartal.csv\n",
            "Trying to create table ´lihtandmed´ - Done!\n",
            "Trying to create table ´kommertspandiandmed´ - Done!\n",
            "Trying to create table ´emtak_myygitulu_jaotus´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2020_i_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2020_ii_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2020_iii_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2020_iv_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2021_i_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2021_ii_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2021_iii_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2021_iv_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2022_i_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2022_ii_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2022_iii_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2022_iv_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2023_i_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2023_ii_kvartal´ - Done!\n",
            "Trying to create table ´tasutud_maksud_2023_iii_kvartal´ - Done!\n"
          ]
        }
      ],
      "source": [
        "create_dirs()\n",
        "\n",
        "download_files()\n",
        "\n",
        "unpack_files()\n",
        "\n",
        "modify_downloaded_jsons()\n",
        "\n",
        "create_temp_tables()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# def fix_file_endings():\n",
        "#   \"Some files are missing endings. All of them are .csv so we need to fix it\"\n",
        "\n",
        "#   for key in all_data.keys():\n",
        "#     path = file_path_unpacked(key)\n",
        "#     if not is_json_or_csv(path):\n",
        "#       new_path = path + '.csv'\n",
        "#       print('Fixing file ending for', path)\n",
        "#       !mv \"$path\" \"$new_path\"\n",
        "#       # os.rename(path, new_path)\n",
        "#       print('New file', new_path)"
      ],
      "metadata": {
        "id": "r6cSH7Ir6v4x"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "for key in all_data.keys():\n",
        "  path = file_path_unpacked(key)\n",
        "  print(path, os.path.isfile(path))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ztWG3LiKXSTf",
        "outputId": "2b5d1526-a034-4493-e26c-46b1b4821b0b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "data/unpacked/ettevotja_rekvisiidid__lihtandmed.csv True\n",
            "data/unpacked/ettevotja_rekvisiidid__yldandmed.json True\n",
            "data/unpacked/ettevotja_rekvisiidid__registrikaardid.json True\n",
            "data/unpacked/ettevotja_rekvisiidid__kaardile_kantud_isikud.json True\n",
            "data/unpacked/ettevotja_rekvisiidid__kandevalised_isikud.json True\n",
            "data/unpacked/ettevotja_rekvisiidid__osanikud.json True\n",
            "data/unpacked/ettevotja_rekvisiidid__kasusaajad.json True\n",
            "data/unpacked/ettevotja_rekvisiidid__kommertspandid.json True\n",
            "data/unpacked/ettevotja_rekvisiidid__maarused.json True\n",
            "data/unpacked/1.aruannete_yldandmed_kuni_31102023.csv True\n",
            "data/unpacked/2.EMTAK_myygitulu_kuni_31102023.csv True\n",
            "data/unpacked/3.myygitulu_geograafiline_kuni_31102023.csv True\n",
            "data/unpacked/4.2020_aruannete_elemendid_kuni_31102023.csv True\n",
            "data/unpacked/4.2021_aruannete_elemendid_kuni_31102023.csv True\n",
            "data/unpacked/4.2022_aruannete_elemendid_kuni_31102023.csv True\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fix_file_endings()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "u3daE4K47jOZ",
        "outputId": "18f06d37-2a91-44cd-f9c7-efdd8d8a044e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Fixing file ending for data/unpacked/1.aruannete_yldandmed_kuni_31102023\n",
            "mv: cannot stat 'data/unpacked/1.aruannete_yldandmed_kuni_31102023': No such file or directory\n",
            "New file data/unpacked/1.aruannete_yldandmed_kuni_31102023.csv\n",
            "Fixing file ending for data/unpacked/2.EMTAK_myygitulu_kuni_31102023\n",
            "mv: cannot stat 'data/unpacked/2.EMTAK_myygitulu_kuni_31102023': No such file or directory\n",
            "New file data/unpacked/2.EMTAK_myygitulu_kuni_31102023.csv\n",
            "Fixing file ending for data/unpacked/3.myygitulu_geograafiline_kuni_31102023\n",
            "mv: cannot stat 'data/unpacked/3.myygitulu_geograafiline_kuni_31102023': No such file or directory\n",
            "New file data/unpacked/3.myygitulu_geograafiline_kuni_31102023.csv\n",
            "Fixing file ending for data/unpacked/4.2020_aruannete_elemendid_kuni_31102023\n",
            "mv: cannot stat 'data/unpacked/4.2020_aruannete_elemendid_kuni_31102023': No such file or directory\n",
            "New file data/unpacked/4.2020_aruannete_elemendid_kuni_31102023.csv\n",
            "Fixing file ending for data/unpacked/4.2021_aruannete_elemendid_kuni_31102023\n",
            "mv: cannot stat 'data/unpacked/4.2021_aruannete_elemendid_kuni_31102023': No such file or directory\n",
            "New file data/unpacked/4.2021_aruannete_elemendid_kuni_31102023.csv\n",
            "Fixing file ending for data/unpacked/4.2022_aruannete_elemendid_kuni_31102023\n",
            "mv: cannot stat 'data/unpacked/4.2022_aruannete_elemendid_kuni_31102023': No such file or directory\n",
            "New file data/unpacked/4.2022_aruannete_elemendid_kuni_31102023.csv\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "for key in all_data.keys():\n",
        "  path = file_path_unpacked(key)\n",
        "  if not is_json_or_csv(path):\n",
        "    print(path)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "W4IwkkXY0TJw",
        "outputId": "98f67428-2403-452f-bf42-92e849acbb76"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "data/unpacked/1.aruannete_yldandmed_kuni_31102023\n",
            "data/unpacked/2.EMTAK_myygitulu_kuni_31102023\n",
            "data/unpacked/3.myygitulu_geograafiline_kuni_31102023\n",
            "data/unpacked/4.2020_aruannete_elemendid_kuni_31102023\n",
            "data/unpacked/4.2021_aruannete_elemendid_kuni_31102023\n",
            "data/unpacked/4.2022_aruannete_elemendid_kuni_31102023\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### EMTAK codes"
      ],
      "metadata": {
        "id": "VIMyMm3iEylG"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "q = \"\"\"\n",
        "  WITH extracted_emtak AS (\n",
        "    SELECT\n",
        "      element_at(yldandmed.teatatud_tegevusalad.emtak_kood, 1) AS emtak_kood,\n",
        "      element_at(yldandmed.teatatud_tegevusalad.emtak_tekstina, 1) AS emtak_tekstina\n",
        "    FROM yldandmed\n",
        "    WHERE array_size(yldandmed.teatatud_tegevusalad) == 1),\n",
        "  unique_emtak AS (\n",
        "    SELECT DISTINCT emtak_kood, emtak_tekstina, count(*) as count\n",
        "    FROM extracted_emtak\n",
        "    GROUP BY emtak_kood, emtak_tekstina)\n",
        "  SELECT * FROM unique_emtak ORDER BY count DESC\n",
        "      \"\"\"\n",
        "emtak_codes = spark.sql(q).cache()"
      ],
      "metadata": {
        "id": "mWDCDdFVEt63"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "emtak_codes.show(20, truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TeFsMu-cGZQR",
        "outputId": "6aca6324-6bed-49a0-fdfe-dee9bc4597c3"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------------------------------------------------------------------------+-----+\n",
            "|emtak_kood|emtak_tekstina                                                               |count|\n",
            "+----------+-----------------------------------------------------------------------------+-----+\n",
            "|70221     |Ärinõustamine jm juhtimisalane nõustamine                                    |12671|\n",
            "|68321     |Hoonete ja üürimajade haldus (korteriühistud, elamuühistud, hooneühistud jms)|12289|\n",
            "|68201     |Enda või renditud kinnisvara üürileandmine ja käitus                         |11986|\n",
            "|41201     |Elamute ja mitteeluhoonete ehitus                                            |9989 |\n",
            "|62011     |Programmeerimine                                                             |9715 |\n",
            "|96021     |Juuksuri- ja muu iluteenindus                                                |8178 |\n",
            "|47911     |Jaemüük posti või Interneti teel                                             |6743 |\n",
            "|69202     |Raamatupidamine, maksualane nõustamine                                       |6147 |\n",
            "|82991     |Muud mujal liigitamata äritegevust abistavad tegevused                       |5657 |\n",
            "|49411     |Kaubavedu maanteel                                                           |4206 |\n",
            "|62021     |Arvutialased konsultatsioonid                                                |4060 |\n",
            "|62091     |Muud infotehnoloogia- ja arvutialased tegevused                              |4009 |\n",
            "|46901     |Spetsialiseerimata hulgikaubandus                                            |3915 |\n",
            "|90031     |Kunstialane loometegevus                                                     |3789 |\n",
            "|49321     |Taksovedu                                                                    |3655 |\n",
            "|74901     |Muu mujal liigitamata kutse-, teadus- ja tehnikaalane tegevus                |3637 |\n",
            "|56101     |Restoranid jm toitlustuskohad                                                |3409 |\n",
            "|01501     |Segapõllumajandus                                                            |3356 |\n",
            "|96099     |Muu  teenindus                                                               |3326 |\n",
            "|73111     |Reklaamiagentuurid                                                           |3115 |\n",
            "+----------+-----------------------------------------------------------------------------+-----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Filter companies and get details"
      ],
      "metadata": {
        "id": "N7KftNwnE7Cs"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "q = \"\"\"\n",
        "  WITH selected_companies AS (\n",
        "    SELECT\n",
        "      yldandmed.ariregistri_kood as ariregistri_kood,\n",
        "      yldandmed.teatatud_tegevusalad.emtak_kood as emtak_kood,\n",
        "      yldandmed.teatatud_tegevusalad.emtak_tekstina as emtak_tekstina,\n",
        "      yldandmed.aadressid[0].ehak_nimetus as ehak_nimetus, -- As of currently ´yldandmed.aadressid´ always contains 1 address\n",
        "      yldandmed.aadressid[0].riik as riik,\n",
        "      yldandmed.aadressid[0].riik_tekstina as riik_tekstina,\n",
        "      array_compact(\n",
        "        TRANSFORM(yldandmed.sidevahendid, elem ->\n",
        "          CASE\n",
        "            WHEN elem.liik_tekstina = 'Mobiiltelefon' THEN elem.sisu\n",
        "            ELSE NULL\n",
        "          END\n",
        "      )) AS mobiiltelefon, -- In some cases there are multiple contact numbers for example.\n",
        "      array_compact(\n",
        "        TRANSFORM(yldandmed.sidevahendid, elem ->\n",
        "          CASE\n",
        "            WHEN elem.liik_tekstina = 'Elektronposti aadress' THEN elem.sisu\n",
        "            ELSE NULL\n",
        "          END\n",
        "      )) AS email\n",
        "    FROM yldandmed\n",
        "    WHERE array_contains(yldandmed.teatatud_tegevusalad.emtak_kood, '62011')\n",
        "    and array_size(yldandmed.teatatud_tegevusalad.emtak_kood) = 1\n",
        "    limit 10\n",
        "  )\n",
        "  SELECT * FROM selected_companies\n",
        "\"\"\"\n",
        "\n",
        "filtered_companies = spark.sql(q).cache()\n",
        "filtered_companies.show(20, truncate=False)"
      ],
      "metadata": {
        "id": "h9sbL6Z1HBWm",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "62d0f53e-7c48-4750-dccd-b53274aa0225"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------------+----------+------------------+--------------------------------------------+----+-------------+---------------+--------------------------+\n",
            "|ariregistri_kood|emtak_kood|emtak_tekstina    |ehak_nimetus                                |riik|riik_tekstina|mobiiltelefon  |email                     |\n",
            "+----------------+----------+------------------+--------------------------------------------+----+-------------+---------------+--------------------------+\n",
            "|11728851        |[62011]   |[Programmeerimine]|Kesklinna linnaosa, Tallinn, Harju maakond  |EST |Eesti        |[]             |[jeepld@gmail.com]        |\n",
            "|16739552        |[62011]   |[Programmeerimine]|Kesklinna linnaosa, Tallinn, Harju maakond  |EST |Eesti        |[]             |[info@044.ai]             |\n",
            "|14809610        |[62011]   |[Programmeerimine]|Haabneeme alevik, Viimsi vald, Harju maakond|NULL|NULL         |[+372 5541173] |[0x00@bolshakov.ee]       |\n",
            "|16508930        |[62011]   |[Programmeerimine]|Lasnamäe linnaosa, Tallinn, Harju maakond   |NULL|NULL         |[+372 53650088]|[ou@0xff.me]              |\n",
            "|16634675        |[62011]   |[Programmeerimine]|Rakvere linn, Lääne-Viru maakond            |EST |Eesti        |[]             |[alexzkhrdev@gmail.com]   |\n",
            "|16526891        |[62011]   |[Programmeerimine]|Kesklinna linnaosa, Tallinn, Harju maakond  |NULL|NULL         |[]             |[100best.guide@gmail.com] |\n",
            "|14696258        |[62011]   |[Programmeerimine]|Pirita linnaosa, Tallinn, Harju maakond     |EST |Eesti        |[]             |[info@eumeedia.com]       |\n",
            "|16050939        |[62011]   |[Programmeerimine]|Kesklinna linnaosa, Tallinn, Harju maakond  |NULL|NULL         |[]             |[toomas.thetloff@eesti.ee]|\n",
            "|16759342        |[62011]   |[Programmeerimine]|Kesklinna linnaosa, Tallinn, Harju maakond  |EST |Eesti        |[]             |[38804140489@eesti.ee]    |\n",
            "|16550854        |[62011]   |[Programmeerimine]|Kesklinna linnaosa, Tallinn, Harju maakond  |EST |Eesti        |[]             |[ar@amconsoft.com]        |\n",
            "+----------------+----------+------------------+--------------------------------------------+----+-------------+---------------+--------------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### paid taxes"
      ],
      "metadata": {
        "id": "S13y_vGRKXRH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "q = \"\"\"\n",
        "  SELECT * FROM tasutud_maksud_2020_i_kvartal limit 10\n",
        "\"\"\"\n",
        "res = spark.sql(q).cache()\n",
        "res.show(10, truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6LivnLbFKa4H",
        "outputId": "99a4ff51-d144-40e1-e180-69de2c1e7175"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------------+----------------------------+--------+--------------------------------------------------+-----------------------------------------------------------------------+--------------------------+----------------+----------------------+------------+---------+\n",
            "|Registrikood|Nimi                        |Liik    |Registreeritud käibemaksukohustuslaste registrisse|EMTAK tegevusvaldkond, mis on EMTAKi struktuuris tähistatud tähtkoodiga|Maakond                   |Riiklikud Maksud|Tööjõumaksud Ja Maksed|Kaive       |Tootajaid|\n",
            "+------------+----------------------------+--------+--------------------------------------------------+-----------------------------------------------------------------------+--------------------------+----------------+----------------------+------------+---------+\n",
            "|10000018    |AMSERV AUTO AS              |Äriühing|jah                                               |HULGI- JA JAEKAUBANDUS; MOOTORSÕIDUKITE JA MOOTORRATASTE REMONT        |Harju ( Tallinn )         |635 586,56      |669 944,03            |NULL        |208      |\n",
            "|10000024    |EESTI RAAMAT, OÜ            |Äriühing|jah                                               |INFO JA SIDE                                                           |Harju ( Tallinn )         |22 847,67       |14 375,33             |129 993,24  |10       |\n",
            "|10000062    |ALDO KOPPEL                 |FIE     |jah                                               |PÕLLUMAJANDUS, METSAMAJANDUS JA KALAPÜÜK                               |Ida-Viru ( Lüganuse vald )|NULL            |NULL                  |185,00      |NULL     |\n",
            "|10000127    |ARAVETE APTEEK, TÜ          |Äriühing|jah                                               |HULGI- JA JAEKAUBANDUS; MOOTORSÕIDUKITE JA MOOTORRATASTE REMONT        |Järva ( Järva vald )      |8 717,01        |4 503,35              |116 415,20  |2        |\n",
            "|10000165    |KIVIÕLI KAUBAHOOV, AS       |Äriühing|jah                                               |HULGI- JA JAEKAUBANDUS; MOOTORSÕIDUKITE JA MOOTORRATASTE REMONT        |Ida-Viru ( Lüganuse vald )|90 795,72       |57 953,73             |842 647,07  |45       |\n",
            "|10000219    |YEAR, AS                    |Äriühing|jah                                               |TÖÖTLEV TÖÖSTUS                                                        |Harju ( Anija vald )      |18 012,18       |16 675,63             |78 022,66   |15       |\n",
            "|10000248    |ITW CONSTRUCTION PRODUCTS OÜ|Äriühing|jah                                               |HULGI- JA JAEKAUBANDUS; MOOTORSÕIDUKITE JA MOOTORRATASTE REMONT        |Harju ( Tallinn )         |131 666,20      |33 369,53             |1 026 334,96|7        |\n",
            "|10000356    |ESTIKO, AS                  |Äriühing|jah                                               |KUTSE-, TEADUS- JA TEHNIKAALANE TEGEVUS                                |Tartu ( Tartu linn )      |218 391,83      |52 986,11             |2 506 039,81|19       |\n",
            "|10000372    |SAARIOINEN EESTI OÜ         |Äriühing|jah                                               |TÖÖTLEV TÖÖSTUS                                                        |Rapla ( Rapla vald )      |546 482,98      |326 338,53            |4 530 556,80|164      |\n",
            "|10000395    |KUUMTRÜKK, OÜ               |Äriühing|jah                                               |TÖÖTLEV TÖÖSTUS                                                        |Harju ( Tallinn )         |15 395,74       |10 359,80             |34 739,42   |6        |\n",
            "+------------+----------------------------+--------+--------------------------------------------------+-----------------------------------------------------------------------+--------------------------+----------------+----------------------+------------+---------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "res.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zD7LpDdwnshy",
        "outputId": "c771d6f7-8c0e-47ff-bbb0-421e33122b4c"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Registrikood: string (nullable = true)\n",
            " |-- Nimi: string (nullable = true)\n",
            " |-- Liik: string (nullable = true)\n",
            " |-- Registreeritud käibemaksukohustuslaste registrisse: string (nullable = true)\n",
            " |-- EMTAK tegevusvaldkond, mis on EMTAKi struktuuris tähistatud tähtkoodiga: string (nullable = true)\n",
            " |-- Maakond: string (nullable = true)\n",
            " |-- Riiklikud Maksud: string (nullable = true)\n",
            " |-- Tööjõumaksud Ja Maksed: string (nullable = true)\n",
            " |-- Kaive: string (nullable = true)\n",
            " |-- Tootajaid: integer (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "schemas = {\n",
        "  key_taxes :\n",
        "    StructType([\n",
        "        StructField(\"Riiklikud Maksud\", DoubleType(), True)\n",
        "    ])\n",
        "}"
      ],
      "metadata": {
        "id": "cAxfYqJXo3W7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "customSchema = StructType([\n",
        "    StructField(\"Riiklikud Maksud\", DoubleType(), True)\n",
        "])"
      ],
      "metadata": {
        "id": "_0iX_YxRV70H"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "spark.sql(\"SHOW TABLES\").show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "84WDVyt7Qofu",
        "outputId": "99544820-a42e-487a-d719-760dc1f48dfe"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------+-----------------------------+-----------+\n",
            "|namespace|tableName                    |isTemporary|\n",
            "+---------+-----------------------------+-----------+\n",
            "|         |emtak_myygitulu_jaotus       |true       |\n",
            "|         |kommertspandiandmed          |true       |\n",
            "|         |lihtandmed                   |true       |\n",
            "|         |tasutud_maksud_2020_i_kvartal|true       |\n",
            "+---------+-----------------------------+-----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "spark.sql(\"DESCRIBE kaardile_kantud_isikud\").show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pSBPapJrQwre",
        "outputId": "ec4999d3-fe53-492f-c830-e4059660c81b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+\n",
            "|col_name                           |data_type                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |comment|\n",
            "+-----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+\n",
            "|ariregistri_kood                   |bigint                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |NULL   |\n",
            "|esindusoiguse_eritingimused        |array<struct<algus_kpv:string,esinduse_sisu:string,esinduse_tyyp:string,esinduse_tyyp_tekstina:string,kaardi_nr:bigint,kaardi_piirkond:bigint,kaardi_tyyp:string,kande_nr:bigint,kirje_id:bigint,lopp_kpv:string>>                                                                                                                                                                                                                                                                                                                                                                                                                                                                |NULL   |\n",
            "|esindusoiguse_normaalregulatsioonid|array<struct<roll:string,sisu:string>>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |NULL   |\n",
            "|hooneyhistu_liikmed                |array<struct<algus_kpv:string,kirje_id:bigint,kitsendused:array<struct<algus_kpv:string,kirje_id:bigint,lopp_kpv:string,sisu:string,tyyp:string,tyyp_tekstina:string>>,liikmed:array<string>,liikmesuse_nr:string,lopp_kpv:string,maksumaara_lugeja:bigint,maksumaara_nimetaja:bigint,pindala:double>>                                                                                                                                                                                                                                                                                                                                                                            |NULL   |\n",
            "|kaardile_kantud_isikud             |array<struct<aadress_ads__adob_id:string,aadress_ads__adr_id:bigint,aadress_ads__ads_normaliseeritud_taisaadress:string,aadress_ads__ads_normaliseeritud_taisaadress_tapsustus:string,aadress_ads__ads_oid:string,aadress_ads__koodaadress:string,aadress_ads__tyyp:string,aadress_ehak:string,aadress_ehak_tekstina:string,aadress_postiindeks:string,aadress_riik:string,aadress_riik_tekstina:string,aadress_tanav_maja_korter:string,algus_kpv:string,eesnimi:string,email:string,isiku_roll:string,isiku_roll_tekstina:string,isiku_tyyp:string,isikukood_registrikood:string,kaardi_nr:bigint,kaardi_piirkond:bigint,kaardi_tyyp:string,kande_nr:bigint,... 11 more fields>>|NULL   |\n",
            "|nimi                               |string                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |NULL   |\n",
            "+-----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "q = \"\"\"\n",
        "  SELECT * FROM registrikaardid limit 1\n",
        "\"\"\"\n",
        "schema = spark.sql(q).cache()\n",
        "schema.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tminEqTWNRud",
        "outputId": "49d82e02-ee18-44ca-a8bd-0331dfe04133"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- ariregistri_kood: long (nullable = true)\n",
            " |-- nimi: string (nullable = true)\n",
            " |-- registrikaardid: array (nullable = true)\n",
            " |    |-- element: struct (containsNull = true)\n",
            " |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |-- kanded: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kandeliik: string (nullable = true)\n",
            " |    |    |    |    |-- kandeliik_tekstina: string (nullable = true)\n",
            " |    |    |    |    |-- kpv: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "schema.show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vZJjfRQ-Ulf0",
        "outputId": "2d156f07-98d3-4a77-888d-c10a9ea47ea5"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------------+---------------+------------------------------------------------------------------------------------------------------------------+\n",
            "|ariregistri_kood|nimi           |registrikaardid                                                                                                   |\n",
            "+----------------+---------------+------------------------------------------------------------------------------------------------------------------+\n",
            "|16372442        |000 Holdings OÜ|[{1, 5, R, [{1, 2, Esmakanne, 23.11.2021}, {2, 12, Muutmiskanne, 01.02.2023}, {3, 12, Muutmiskanne, 01.09.2023}]}]|\n",
            "+----------------+---------------+------------------------------------------------------------------------------------------------------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "filtered_companies.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0FRj8ltFKr69",
        "outputId": "7642598c-ff34-48c9-a9f6-69365805d5ce"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "10562"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "filtered_companies.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TpvJJb6_MGcV",
        "outputId": "b82336c7-cc6b-42b9-e415-777ff6fd88f9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- ariregistri_kood: long (nullable = true)\n",
            " |-- emtak_kood: array (nullable = true)\n",
            " |    |-- element: string (containsNull = true)\n",
            " |-- emtak_tekstina: array (nullable = true)\n",
            " |    |-- element: string (containsNull = true)\n",
            " |-- ehak_nimetus: array (nullable = true)\n",
            " |    |-- element: string (containsNull = true)\n",
            " |-- riik: array (nullable = true)\n",
            " |    |-- element: string (containsNull = true)\n",
            " |-- mobiiltelefon: array (nullable = true)\n",
            " |    |-- element: string (containsNull = true)\n",
            " |-- email: array (nullable = true)\n",
            " |    |-- element: string (containsNull = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "q = \"\"\"\n",
        "  SELECT * FROM yldandmed limit 1\n",
        "\"\"\"\n",
        "yldandmed_schema = spark.sql(q).cache()\n",
        "yldandmed_schema.printSchema()"
      ],
      "metadata": {
        "id": "3hIBjG26Tfmk",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "717bce6c-5758-4886-c2fd-0d97c858beac"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- ariregistri_kood: long (nullable = true)\n",
            " |-- nimi: string (nullable = true)\n",
            " |-- yldandmed: struct (nullable = true)\n",
            " |    |-- aadressid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- aadress_ads__adob_id: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__adr_id: long (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress_tapsustus: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_oid: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__koodaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__tyyp: string (nullable = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- ehak: string (nullable = true)\n",
            " |    |    |    |-- ehak_nimetus: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- postiindeks: string (nullable = true)\n",
            " |    |    |    |-- riik: string (nullable = true)\n",
            " |    |    |    |-- riik_tekstina: string (nullable = true)\n",
            " |    |    |    |-- tanav_maja_korter: string (nullable = true)\n",
            " |    |-- arinimed: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- asutamise_ajad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- asutatud_sissemakset_tegemata: boolean (nullable = true)\n",
            " |    |-- eesmargid_kaardil: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- esitab_kasusaajad: boolean (nullable = true)\n",
            " |    |-- esmanimetamise_ajad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- esmaregistreerimise_kpv: string (nullable = true)\n",
            " |    |-- ettevotja_tegevuse_aeg: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- periood: string (nullable = true)\n",
            " |    |    |    |-- periood_algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- periood_lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- periood_tekstina: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- tyyp: string (nullable = true)\n",
            " |    |    |    |-- tyyp_tekstina: string (nullable = true)\n",
            " |    |-- ettevotteregistri_nr: long (nullable = true)\n",
            " |    |-- evks_registreeritud: string (nullable = true)\n",
            " |    |-- evks_registreeritud_kande_kpv: string (nullable = true)\n",
            " |    |-- info_majandusaasta_aruannetest: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- ettevotja_aadress_aruandes: string (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- majandusaasta_perioodi_algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- majandusaasta_perioodi_lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_emtak_kood: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_emtak_tekstina: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_emtak_versioon: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_emtak_versioon_tekstina: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_nace_kood: string (nullable = true)\n",
            " |    |    |    |-- tootajate_arv: string (nullable = true)\n",
            " |    |-- kapitalid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kapitali_suurus: string (nullable = true)\n",
            " |    |    |    |-- kapitali_valuuta: string (nullable = true)\n",
            " |    |    |    |-- kapitali_valuuta_tekstina: string (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |-- kinnisasjad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- aadress_ads__adob_id: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__adr_id: long (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress_tapsustus: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_oid: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__koodaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__tyyp: string (nullable = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- ehak: string (nullable = true)\n",
            " |    |    |    |-- ehak_nimetus: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kinnisasja_nr: long (nullable = true)\n",
            " |    |    |    |-- kinnistusjaoskond: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- postiindeks: string (nullable = true)\n",
            " |    |    |    |-- tanav_maja: string (nullable = true)\n",
            " |    |-- kompromissi_tahtajad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- kontaktisiku_aadressid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- aadress_ads__adob_id: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__adr_id: long (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress_tapsustus: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_oid: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__koodaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__tyyp: string (nullable = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- ehak: string (nullable = true)\n",
            " |    |    |    |-- ehak_nimetus: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- postiindeks: string (nullable = true)\n",
            " |    |    |    |-- riik: string (nullable = true)\n",
            " |    |    |    |-- riik_tekstina: string (nullable = true)\n",
            " |    |    |    |-- tanav_maja_korter: string (nullable = true)\n",
            " |    |-- kustutamise_kpv: string (nullable = true)\n",
            " |    |-- ky_kinnistu_aadressid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- adr_id: long (nullable = true)\n",
            " |    |    |    |-- ads_koodaadress: string (nullable = true)\n",
            " |    |    |    |-- ads_normaliseeritud_taisaadress: string (nullable = true)\n",
            " |    |    |    |-- ads_oid: string (nullable = true)\n",
            " |    |    |    |-- laadimise_kpv: string (nullable = true)\n",
            " |    |-- lahknevusteade_puudumisest: boolean (nullable = true)\n",
            " |    |-- loobunud_vorminouetest: boolean (nullable = true)\n",
            " |    |-- majandusaastad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- maj_aasta_algus: string (nullable = true)\n",
            " |    |    |    |-- maj_aasta_lopp: string (nullable = true)\n",
            " |    |-- markused_kaardil: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- tyyp: string (nullable = true)\n",
            " |    |    |    |-- tyyp_tekstina: string (nullable = true)\n",
            " |    |    |    |-- veerg_nr: long (nullable = true)\n",
            " |    |-- mkr_reg_kpv: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kehtiv_kpv: string (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- mps_koodid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- nimivaartuseta_aktsiad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- nimivaartuseta_aktsiate_arv: string (nullable = true)\n",
            " |    |-- oigusjargsused: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- tyyp: string (nullable = true)\n",
            " |    |    |    |-- tyyp_tekstina: string (nullable = true)\n",
            " |    |-- oiguslik_vorm: string (nullable = true)\n",
            " |    |-- oiguslik_vorm_nr: long (nullable = true)\n",
            " |    |-- oiguslik_vorm_tekstina: string (nullable = true)\n",
            " |    |-- oigusliku_vormi_alaliigid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- sisu_tekstina: string (nullable = true)\n",
            " |    |-- oigusliku_vormi_alaliik: string (nullable = true)\n",
            " |    |-- oigusliku_vormi_alaliik_tekstina: string (nullable = true)\n",
            " |    |-- oiguslikud_vormid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- sisu_nr: long (nullable = true)\n",
            " |    |    |    |-- sisu_tekstina: string (nullable = true)\n",
            " |    |-- on_raamatupidamiskohustuslane: boolean (nullable = true)\n",
            " |    |-- piirkond: long (nullable = true)\n",
            " |    |-- piirkond_tekstina: string (nullable = true)\n",
            " |    |-- piirkond_tekstina_pikk: string (nullable = true)\n",
            " |    |-- pohikirjad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kinnitamise_kpv: string (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- muutmise_kpv: string (nullable = true)\n",
            " |    |    |    |-- selgitus: string (nullable = true)\n",
            " |    |    |    |-- sisaldab_erioigusi: boolean (nullable = true)\n",
            " |    |-- raamatupidamiskohustused: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kehtiv_kpv: string (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: boolean (nullable = true)\n",
            " |    |-- seisundi_muutused: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- tyyp: string (nullable = true)\n",
            " |    |    |    |-- tyyp_tekstina: string (nullable = true)\n",
            " |    |-- sidevahendid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- liik: string (nullable = true)\n",
            " |    |    |    |-- liik_tekstina: string (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- staatus: string (nullable = true)\n",
            " |    |-- staatus_tekstina: string (nullable = true)\n",
            " |    |-- staatused: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- staatus: string (nullable = true)\n",
            " |    |    |    |-- staatus_tekstina: string (nullable = true)\n",
            " |    |-- taiendavad_nimed: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- teatatud_tegevusalad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- emtak_kood: string (nullable = true)\n",
            " |    |    |    |-- emtak_tekstina: string (nullable = true)\n",
            " |    |    |    |-- emtak_versioon: long (nullable = true)\n",
            " |    |    |    |-- emtak_versioon_tekstina: string (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- nace_kood: string (nullable = true)\n",
            " |    |    |    |-- on_pohitegevusala: boolean (nullable = true)\n",
            " |    |-- tegevusalad_kaardil: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- tegutseb: string (nullable = true)\n",
            " |    |-- tegutseb_tekstina: string (nullable = true)\n",
            " |    |-- tegutsemise_tahtajad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- valislepingud: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- avaldamismarge: string (nullable = true)\n",
            " |    |    |    |-- joustumise_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |-- valismaa_ariyhing: struct (nullable = true)\n",
            " |    |    |-- arinimi: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- asukoht: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- aadress: string (nullable = true)\n",
            " |    |    |    |    |-- aadress_riik: string (nullable = true)\n",
            " |    |    |    |    |-- aadress_riik_tekstina: string (nullable = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- postiindeks: string (nullable = true)\n",
            " |    |    |-- maa_mille_seaduse_alusel_tegutseb: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- maa_mille_seaduse_alusel_tegutseb_markus: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- majandusaasta_aruande_avalikustamise_kohustus: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- oiguslik_vorm: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- register: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- registreerimisnumber: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- sarnane_eesti_oiguslik_vorm_markus: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- sarnane_eesti_oigvorm: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |    |-- sisu_tekstina: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "r.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 178
        },
        "id": "cv5P0oFoFlvM",
        "outputId": "6ec088d4-577a-4cff-e582-7129c1c68026"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "NameError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-15-613129c48a4d>\u001b[0m in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mprintSchema\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m: name 'r' is not defined"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "yldandmed_schema.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BajPa04rH5Za",
        "outputId": "90273c60-4f96-4dc7-e269-2b9bb24d1a47"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- ariregistri_kood: long (nullable = true)\n",
            " |-- nimi: string (nullable = true)\n",
            " |-- yldandmed: struct (nullable = true)\n",
            " |    |-- aadressid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- aadress_ads__adob_id: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__adr_id: long (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress_tapsustus: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_oid: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__koodaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__tyyp: string (nullable = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- ehak: string (nullable = true)\n",
            " |    |    |    |-- ehak_nimetus: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- postiindeks: string (nullable = true)\n",
            " |    |    |    |-- riik: string (nullable = true)\n",
            " |    |    |    |-- riik_tekstina: string (nullable = true)\n",
            " |    |    |    |-- tanav_maja_korter: string (nullable = true)\n",
            " |    |-- arinimed: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- asutamise_ajad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- asutatud_sissemakset_tegemata: boolean (nullable = true)\n",
            " |    |-- eesmargid_kaardil: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- esitab_kasusaajad: boolean (nullable = true)\n",
            " |    |-- esmanimetamise_ajad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- esmaregistreerimise_kpv: string (nullable = true)\n",
            " |    |-- ettevotja_tegevuse_aeg: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- periood: string (nullable = true)\n",
            " |    |    |    |-- periood_algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- periood_lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- periood_tekstina: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- tyyp: string (nullable = true)\n",
            " |    |    |    |-- tyyp_tekstina: string (nullable = true)\n",
            " |    |-- ettevotteregistri_nr: long (nullable = true)\n",
            " |    |-- evks_registreeritud: string (nullable = true)\n",
            " |    |-- evks_registreeritud_kande_kpv: string (nullable = true)\n",
            " |    |-- info_majandusaasta_aruannetest: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- ettevotja_aadress_aruandes: string (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- majandusaasta_perioodi_algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- majandusaasta_perioodi_lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_emtak_kood: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_emtak_tekstina: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_emtak_versioon: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_emtak_versioon_tekstina: string (nullable = true)\n",
            " |    |    |    |-- tegevusala_nace_kood: string (nullable = true)\n",
            " |    |    |    |-- tootajate_arv: string (nullable = true)\n",
            " |    |-- kapitalid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kapitali_suurus: string (nullable = true)\n",
            " |    |    |    |-- kapitali_valuuta: string (nullable = true)\n",
            " |    |    |    |-- kapitali_valuuta_tekstina: string (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |-- kinnisasjad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- aadress_ads__adob_id: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__adr_id: long (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress_tapsustus: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_oid: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__koodaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__tyyp: string (nullable = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- ehak: string (nullable = true)\n",
            " |    |    |    |-- ehak_nimetus: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kinnisasja_nr: long (nullable = true)\n",
            " |    |    |    |-- kinnistusjaoskond: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- postiindeks: string (nullable = true)\n",
            " |    |    |    |-- tanav_maja: string (nullable = true)\n",
            " |    |-- kompromissi_tahtajad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- kontaktisiku_aadressid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- aadress_ads__adob_id: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__adr_id: long (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_normaliseeritud_taisaadress_tapsustus: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__ads_oid: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__koodaadress: string (nullable = true)\n",
            " |    |    |    |-- aadress_ads__tyyp: string (nullable = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- ehak: string (nullable = true)\n",
            " |    |    |    |-- ehak_nimetus: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- postiindeks: string (nullable = true)\n",
            " |    |    |    |-- riik: string (nullable = true)\n",
            " |    |    |    |-- riik_tekstina: string (nullable = true)\n",
            " |    |    |    |-- tanav_maja_korter: string (nullable = true)\n",
            " |    |-- kustutamise_kpv: string (nullable = true)\n",
            " |    |-- ky_kinnistu_aadressid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- adr_id: long (nullable = true)\n",
            " |    |    |    |-- ads_koodaadress: string (nullable = true)\n",
            " |    |    |    |-- ads_normaliseeritud_taisaadress: string (nullable = true)\n",
            " |    |    |    |-- ads_oid: string (nullable = true)\n",
            " |    |    |    |-- laadimise_kpv: string (nullable = true)\n",
            " |    |-- lahknevusteade_puudumisest: boolean (nullable = true)\n",
            " |    |-- loobunud_vorminouetest: boolean (nullable = true)\n",
            " |    |-- majandusaastad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- maj_aasta_algus: string (nullable = true)\n",
            " |    |    |    |-- maj_aasta_lopp: string (nullable = true)\n",
            " |    |-- markused_kaardil: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- tyyp: string (nullable = true)\n",
            " |    |    |    |-- tyyp_tekstina: string (nullable = true)\n",
            " |    |    |    |-- veerg_nr: long (nullable = true)\n",
            " |    |-- mkr_reg_kpv: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kehtiv_kpv: string (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- mps_koodid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- nimivaartuseta_aktsiad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- nimivaartuseta_aktsiate_arv: string (nullable = true)\n",
            " |    |-- oigusjargsused: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- tyyp: string (nullable = true)\n",
            " |    |    |    |-- tyyp_tekstina: string (nullable = true)\n",
            " |    |-- oiguslik_vorm: string (nullable = true)\n",
            " |    |-- oiguslik_vorm_nr: long (nullable = true)\n",
            " |    |-- oiguslik_vorm_tekstina: string (nullable = true)\n",
            " |    |-- oigusliku_vormi_alaliigid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- sisu_tekstina: string (nullable = true)\n",
            " |    |-- oigusliku_vormi_alaliik: string (nullable = true)\n",
            " |    |-- oigusliku_vormi_alaliik_tekstina: string (nullable = true)\n",
            " |    |-- oiguslikud_vormid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- sisu_nr: long (nullable = true)\n",
            " |    |    |    |-- sisu_tekstina: string (nullable = true)\n",
            " |    |-- on_raamatupidamiskohustuslane: boolean (nullable = true)\n",
            " |    |-- piirkond: long (nullable = true)\n",
            " |    |-- piirkond_tekstina: string (nullable = true)\n",
            " |    |-- piirkond_tekstina_pikk: string (nullable = true)\n",
            " |    |-- pohikirjad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kinnitamise_kpv: string (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- muutmise_kpv: string (nullable = true)\n",
            " |    |    |    |-- selgitus: string (nullable = true)\n",
            " |    |    |    |-- sisaldab_erioigusi: boolean (nullable = true)\n",
            " |    |-- raamatupidamiskohustused: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kehtiv_kpv: string (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: boolean (nullable = true)\n",
            " |    |-- seisundi_muutused: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |-- tyyp: string (nullable = true)\n",
            " |    |    |    |-- tyyp_tekstina: string (nullable = true)\n",
            " |    |-- sidevahendid: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- liik: string (nullable = true)\n",
            " |    |    |    |-- liik_tekstina: string (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- staatus: string (nullable = true)\n",
            " |    |-- staatus_tekstina: string (nullable = true)\n",
            " |    |-- staatused: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- staatus: string (nullable = true)\n",
            " |    |    |    |-- staatus_tekstina: string (nullable = true)\n",
            " |    |-- taiendavad_nimed: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- teatatud_tegevusalad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- emtak_kood: string (nullable = true)\n",
            " |    |    |    |-- emtak_tekstina: string (nullable = true)\n",
            " |    |    |    |-- emtak_versioon: long (nullable = true)\n",
            " |    |    |    |-- emtak_versioon_tekstina: string (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- nace_kood: string (nullable = true)\n",
            " |    |    |    |-- on_pohitegevusala: boolean (nullable = true)\n",
            " |    |-- tegevusalad_kaardil: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- tegutseb: string (nullable = true)\n",
            " |    |-- tegutseb_tekstina: string (nullable = true)\n",
            " |    |-- tegutsemise_tahtajad: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |-- valislepingud: array (nullable = true)\n",
            " |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |-- avaldamismarge: string (nullable = true)\n",
            " |    |    |    |-- joustumise_kpv: string (nullable = true)\n",
            " |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |-- valismaa_ariyhing: struct (nullable = true)\n",
            " |    |    |-- arinimi: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- asukoht: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- aadress: string (nullable = true)\n",
            " |    |    |    |    |-- aadress_riik: string (nullable = true)\n",
            " |    |    |    |    |-- aadress_riik_tekstina: string (nullable = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- postiindeks: string (nullable = true)\n",
            " |    |    |-- maa_mille_seaduse_alusel_tegutseb: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- maa_mille_seaduse_alusel_tegutseb_markus: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- majandusaasta_aruande_avalikustamise_kohustus: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- oiguslik_vorm: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- register: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- registreerimisnumber: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- sarnane_eesti_oiguslik_vorm_markus: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |-- sarnane_eesti_oigvorm: array (nullable = true)\n",
            " |    |    |    |-- element: struct (containsNull = true)\n",
            " |    |    |    |    |-- algus_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- kaardi_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_piirkond: long (nullable = true)\n",
            " |    |    |    |    |-- kaardi_tyyp: string (nullable = true)\n",
            " |    |    |    |    |-- kande_nr: long (nullable = true)\n",
            " |    |    |    |    |-- kirje_id: long (nullable = true)\n",
            " |    |    |    |    |-- lopp_kpv: string (nullable = true)\n",
            " |    |    |    |    |-- sisu: string (nullable = true)\n",
            " |    |    |    |    |-- sisu_tekstina: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "r.show(10, truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BoxjqpfQEt-Q",
        "outputId": "759334eb-f1e4-4b0d-c4db-b2c446c9d419"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------------------------------------------------+-----+\n",
            "|emtak_kood|emtak_tekstina                                       |count|\n",
            "+----------+-----------------------------------------------------+-----+\n",
            "|8622      |Eriarstiabi                                          |36   |\n",
            "|01501     |Segapõllumajandus                                    |3356 |\n",
            "|73201     |Turu-uuringud ja avaliku arvamuse küsitlused         |225  |\n",
            "|46422     |Jalatsite hulgimüük                                  |17   |\n",
            "|2932      |Mootorsõidukite  muude osade ja lisaseadmete tootmine|1    |\n",
            "|46901     |Spetsialiseerimata hulgikaubandus                    |3913 |\n",
            "|2229      |Muude plasttoodete tootmine                          |7    |\n",
            "|66191     |Finantsnõustamine                                    |664  |\n",
            "|10841     |Maitseainete ja -kastmete tootmine                   |28   |\n",
            "|35221     |Maagaasi ülekanne ja  jaotus maagaasivõrgu  kaudu    |8    |\n",
            "+----------+-----------------------------------------------------+-----+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "for key in list(generic_data.keys()):\n",
        "  path = file_path_unpacked(generic_data[key])\n",
        "  s = \"SELECT * FROM {key} limit 1\".format(key=key)\n",
        "  r = spark.sql(s)\n",
        "  print(key)\n",
        "  print(r.show())"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rF-Axmp2oSxM",
        "outputId": "f950f62e-4be5-4a37-e390-0ec115a58f9c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "lihtandmed\n",
            "+---------------+----------------+-----------------------+---------------------------------+-------+-----------------+--------------------------+-----------------------+-----------------+---------------------------+-----------------+---------------------+--------------------------+----------+-----------+-------------------------------+--------------------+\n",
            "|           nimi|ariregistri_kood|ettevotja_oiguslik_vorm|ettevotja_oigusliku_vormi_alaliik|kmkr_nr|ettevotja_staatus|ettevotja_staatus_tekstina|ettevotja_esmakande_kpv|ettevotja_aadress|asukoht_ettevotja_aadressis|asukoha_ehak_kood|asukoha_ehak_tekstina|indeks_ettevotja_aadressis|ads_adr_id|ads_ads_oid|ads_normaliseeritud_taisaadress|  teabesysteemi_link|\n",
            "+---------------+----------------+-----------------------+---------------------------------+-------+-----------------+--------------------------+-----------------------+-----------------+---------------------------+-----------------+---------------------+--------------------------+----------+-----------+-------------------------------+--------------------+\n",
            "|000 Holdings OÜ|        16372442|               Osaühing|                             NULL|   NULL|                R|        Registrisse kantud|             23.11.2021|             NULL|       313A/133 GOULBURN...|             NULL|                 NULL|                      2010|      NULL|       NULL|                           NULL|https://ariregist...|\n",
            "+---------------+----------------+-----------------------+---------------------------------+-------+-----------------+--------------------------+-----------------------+-----------------+---------------------------+-----------------+---------------------+--------------------------+----------+-----------+-------------------------------+--------------------+\n",
            "\n",
            "None\n",
            "yldandmed\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|ariregistri_kood|esindusoiguse_eritingimused|esindusoiguse_normaalregulatsioonid|hooneyhistu_liikmed|kaardile_kantud_isikud|           nimi|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|        16372442|                         []|               [{JUHL, Osaühingu...|                 []|  [{NULL, 2113048, ...|000 Holdings OÜ|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "\n",
            "None\n",
            "registrikaardid\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|ariregistri_kood|esindusoiguse_eritingimused|esindusoiguse_normaalregulatsioonid|hooneyhistu_liikmed|kaardile_kantud_isikud|           nimi|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|        16372442|                         []|               [{JUHL, Osaühingu...|                 []|  [{NULL, 2113048, ...|000 Holdings OÜ|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "\n",
            "None\n",
            "kaardile_kantud_isikud\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|ariregistri_kood|esindusoiguse_eritingimused|esindusoiguse_normaalregulatsioonid|hooneyhistu_liikmed|kaardile_kantud_isikud|           nimi|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|        16372442|                         []|               [{JUHL, Osaühingu...|                 []|  [{NULL, 2113048, ...|000 Holdings OÜ|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "\n",
            "None\n",
            "kandevalised_isikud\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|ariregistri_kood|esindusoiguse_eritingimused|esindusoiguse_normaalregulatsioonid|hooneyhistu_liikmed|kaardile_kantud_isikud|           nimi|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|        16372442|                         []|               [{JUHL, Osaühingu...|                 []|  [{NULL, 2113048, ...|000 Holdings OÜ|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "\n",
            "None\n",
            "osanikud\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|ariregistri_kood|esindusoiguse_eritingimused|esindusoiguse_normaalregulatsioonid|hooneyhistu_liikmed|kaardile_kantud_isikud|           nimi|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|        16372442|                         []|               [{JUHL, Osaühingu...|                 []|  [{NULL, 2113048, ...|000 Holdings OÜ|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "\n",
            "None\n",
            "kasusaajad\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|ariregistri_kood|esindusoiguse_eritingimused|esindusoiguse_normaalregulatsioonid|hooneyhistu_liikmed|kaardile_kantud_isikud|           nimi|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|        16372442|                         []|               [{JUHL, Osaühingu...|                 []|  [{NULL, 2113048, ...|000 Holdings OÜ|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "\n",
            "None\n",
            "kommertspandiandmed\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|ariregistri_kood|esindusoiguse_eritingimused|esindusoiguse_normaalregulatsioonid|hooneyhistu_liikmed|kaardile_kantud_isikud|           nimi|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|        16372442|                         []|               [{JUHL, Osaühingu...|                 []|  [{NULL, 2113048, ...|000 Holdings OÜ|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "\n",
            "None\n",
            "maarused\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|ariregistri_kood|esindusoiguse_eritingimused|esindusoiguse_normaalregulatsioonid|hooneyhistu_liikmed|kaardile_kantud_isikud|           nimi|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "|        16372442|                         []|               [{JUHL, Osaühingu...|                 []|  [{NULL, 2113048, ...|000 Holdings OÜ|\n",
            "+----------------+---------------------------+-----------------------------------+-------------------+----------------------+---------------+\n",
            "\n",
            "None\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "spark.stop()"
      ],
      "metadata": {
        "id": "ZEXSQQl7CalQ"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyN8AiifTKsntLHiLMd23Pt0",
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}